Load Data with Fivetran

Data can be ingested into SingleStore from multiple sources using Fivetran to power data applications. Fivetran's built-in connectors automatically synchronize any changes to the schema and API, ensuring consistent and reliable access to data.

Refer to Implementation & Setup Documentation for related information.

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:

    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,

  1. On the Fivetran Dashboard, select Destinations > + Add Destination.

  2. In the Destination Name field, enter a name for the Destination, and then select Add.

  3. From the list of destinations, select SingleStore.

  4. 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.

  5. 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:

  1. Creates a new column with the updated data type.

  2. Copies the data from the existing column to the new column.

  3. Deletes the existing column.

  4. 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.

Last modified: October 1, 2024

Was this article helpful?