Load Data with Fivetran

Prerequisites

An active SingleStore Helios deployment.
A Fivetran account with Create Destinations or Manage Destinations permissions. Refer to Fivetran documentation for more information.

Configure SingleStore

Allow incoming connections from Fivetran IP Addresses.
Grant the following privileges to the SingleStore database user:
SQL
```
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON <database>.* to <user>;
```
Note: Additionally, grant the CREATE DATABASE privilege if a database is not specified in the connection configuration in Fivetran.

Connect to SingleStore from Fivetran

To connect to SingleStore from Fivetran,

On the Fivetran Dashboard, select Destinations > + Add Destination.
In the Destination Name field, enter a name for the Destination, and then select Add.
From the list of destinations, select SingleStore.
On the SingleStore page, select or enter the following connection configuration information:
- Host: Hostname or IP address of the SingleStore deployment.
- Port: Port of your SingleStore deployment. Default is 3306.
- Database (Optional): Name of the SingleStore database to connect with. If specified, all of the tables are created in this database.
- Username: Username of the SingleStore database user.
- Password (Optional): Password of the SingleStore database user.
- Driver parameter (Optional): Additional connection string parameters supported by The SingleStore JDBC Driver in the 'parameter1=value1; parameter2=value2;...' format.
- Data processing location: Location where Fivetran operates and runs data computations.
- Cloud service parameter: Cloud service provider used for running Fivetran services.
- Timezone: Time zone for Fivetran sync schedule.
Select Save > Test. Fivetran connects to SingleStore and validates the specified connection configuration.

Once the connection is successful, Fivetran automatically configures a Fivetran Platform Connector that transfers the connector logs and account metadata to a table in the SingleStore destination. The Fivetran Platform Connector allows you to audit changes and monitor your connectors and usage. The Fivetran Platform Connector sends this information at the destination level.

Data Type Mapping

While ingesting data from Fivetran, SingleStore maps the Fivetran data types to SingleStore data types. Any unsupported data type is automatically cast to the closest supported SingleStore data type.

The following table maps the Fivetran data types to the corresponding SingleStore data types:

Fivetran Data Type	SingleStore Data Type
BOOLEAN	BOOLEAN
SHORT	SMALLINT
INT	INT
LONG	BIGINT
BIGDECIMAL	DECIMAL
FLOAT	FLOAT
DOUBLE	DOUBLE
LOCALDATE	DATE
LOCALDATETIME	DATETIME(6)
INSTANT	DATETIME(6)
STRING	TEXT
XML	TEXT
JSON	JSON
BINARY	BLOB

Schema Changes

The following table lists the type of supported and unsupported schema changes by the SingleStore destination in Fivetran.

Change Type	Supported	Description
Add column	✔	If a column is added to the data source, Fivetran adds that column in the SingleStore destination.
Change column type	✔	If the data type of a column in the data source is changed, Fivetran automatically changes the data type of that column in the SingleStore destination. To change the datatype of a column in SingleStore, Fivetran performs the following tasks: Creates a new column with the updated data type. Copies the data from the existing column to the new column. Deletes the existing column. Renames the new column.
Change key		SingleStore does not support changing the `PRIMARY KEY` column.
Change key column type		SingleStore does not support changing the data type of the `PRIMARY KEY` column.

Limitations

Fivetran does not support history mode for SingleStore destinations.