Integrate TiDB Cloud with Airbyte

Airbyteis an open-source data integration engine to build Extract, Load, Transform (ELT) pipelines and consolidate your data in your data warehouses, data lakes, and databases. This document describes how to connect Airbyte to TiDB Cloud as a source or a destination.

Deploy Airbyte

You can deploy Airbyte locally with only a few steps.

1. InstallDockeron your workspace.

2. Clone the Airbyte source code.

   
                   
   
                    gitclonehttps://github.com/airbytehq/airbyte.git && \cdairbyte
   
                    
                    

3. Run the Docker images by docker-compose.

   
                   
   
                    docker-compose up
   
                    
                    

Once you see an Airbyte banner, you can go tohttp://localhost:8000用户名(airbyte) and password (password) to visit the UI.

              

               airbyte-server | ___ _ __ __ airbyte-server | / | (_)____/ /_ __ __/ /____ airbyte-server | / /| | / / ___/ __ \/ / / / __/ _ \ airbyte-server | / ___ |/ / / / /_/ / /_/ / /_/ __/ airbyte-server | /_/ |_/_/_/ /_.___/\__, /\__/\___/ airbyte-server | /____/ airbyte-server | -------------------------------------- airbyte-server | Now ready at http://localhost:8000/ airbyte-server | --------------------------------------

               
               

Set up the TiDB connector

Conveniently, the steps are the same for setting TiDB as the source and the destination.

1. ClickSourcesorDestinations在sidebar and choose TiDB type to create a new TiDB connector.

2. Fill in the following parameters.

   • Host: The endpoint of your TiDB Cloud cluster
   • Port: The port of the database
   • Database: The database that you want to sync the data
   • Username: The username to access the database
   • Password: The password of the username

   You can get the parameter values from the connection dialog of your cluster. To open the dialog, go to theClusterspage of your project, click the name of your target cluster to go to its overview page, and then clickConnect在右上角。

3. EnableSSL Connection, and set TLS protocols toTLSv1.2orTLSv1.3inJDBC URL Params.

   Note:

   • TiDB Cloud supports TLS connection. You can choose your TLS protocols inTLSv1.2andTLSv1.3, for example,enabledTLSProtocols=TLSv1.2.
   • If you want to disable TLS connection to TiDB Cloud via JDBC, you need to set useSSL tofalsein JDBC URL Params specifically and close SSL connection, for example,useSSL=false.
   • TiDB Serverless only supports TLS connections.

4. ClickSet up sourceordestinationto complete creating the connector. The following screenshot shows the configuration of TiDB as the source.

You can use any combination of sources and destinations, such as TiDB to Snowflake, and CSV files to TiDB.

For more details about the TiDB connector, seeTiDB SourceandTiDB Destination.

Set up the connection

After setting up the source and destination, you can build and configure the connection.

The following steps use TiDB as both a source and a destination. Other connectors may have different parameters.

1. ClickConnections在sidebar and then clickNew Connection.

2. Select the previously established source and destination.

3. Go to theSet upconnection panel and create a name for the connection, such as${source_name} - ${destination-name}.

4. SetReplication frequencytoEvery 24 hours, which means the connection replicates data once a day.

5. SetDestination NamespacetoCustom formatand setNamespace Custom Formattotestto store all data in thetestdatabase.

6. Choose theSync modetoFull refresh | Overwrite.

   Tip

   The TiDB connector supports both Incremental and Full Refresh syncs.

   • In Incremental mode, Airbyte only reads records added to the source since the last sync job. The first sync using Incremental mode is equivalent to Full Refresh mode.
   • In Full Refresh mode, Airbyte reads all records in the source and replicates to the destination in every sync task. You can set the sync mode for every table namedNamespacein Airbyte individually.

   

7. SetNormalization & TransformationtoNormalized tabular datato use the default normalization mode, or you can set the dbt file for your job. For more information about normalization, refer toTransformations and Normalization.

8. ClickSet up connection.

9. Once the connection is established, clickENABLEDto activate the synchronization task. You can also clickSync nowto sync immediately.

Limitations

• The TiDB connector does not support the Change Data Capture (CDC) feature.
• TiDB destination converts thetimestamptype to thevarchartype in default normalization mode. It happens because Airbyte converts the timestamp type to string during transmission, and TiDB does not supportcast ('2020-07-28 14:50:15+1:00' as timestamp).
• For some large ELT missions, you need to increase the parameters oftransaction restrictionsin TiDB.

See also

Using Airbyte to Migrate Data from TiDB Cloud to Snowflake.