Typical Scenario: Importing Data from a Relational Database to ClickHouse

Scenario

Use Loader to import data from a relational database to ClickHouse. This section uses MySQL as an example.

Prerequisites

• A role has been created on FusionInsight Manager and granted the management permission on ClickHouse logical clusters and Loader job grouping permission. A service user for Loader jobs has been created, associated with the role, and added the user group yarnviewgroup.
• A replicated table and a distributed table have been created by referring to Creating a ClickHouse Table and a user has been assigned the permission to perform operations on the tables during job execution. The replicated table has been selected when data is imported.
• You have obtained the user name and password of the MySQL database.
• No ClickHouse alarm is generated.

Procedure

Make preparations.

1. Obtain the MySQL client JAR file (for example, mysqlclient-5.8.1.jar) from the MySQL database installation path and save it to ${BIGDATA_HOME}/FusionInsight_Porter_*/install/FusionInsight-Sqoop-1.99.3/FusionInsight-Sqoop-1.99.3/server/webapps/loader/WEB-INF/ext-lib on the active and standby Loader nodes.
2. Obtain the clickhouse-jdbc-*.jar file from the ClickHouse installation directory and save it to ${BIGDATA_HOME}/FusionInsight_Porter_*/install/FusionInsight-Sqoop-1.99.3/FusionInsight-Sqoop-1.99.3/server/webapps/loader/WEB-INF/ext-lib on the active and standby Loader nodes.
3. Run the following command on the active and standby nodes as user root to modify the permission:

   cd ${BIGDATA_HOME}/FusionInsight_Porter_*/install/FusionInsight-Sqoop-1.99.3/FusionInsight-Sqoop-1.99.3/server/webapps/loader/WEB-INF/ext-lib

   chown omm:wheel JAR file name

   chmod 600 JAR file name

4. Log in to FusionInsight Manager and choose Cluster > Services > Loader. On the Dashboard tab page that is displayed, click More and select Restart Service. In the displayed dialog box, enter the administrator password to restart the Loader service.

Configure basic job information.

5. Access the Loader web UI.
   1. Log in to FusionInsight Manager.
   2. Choose Cluster > Services > Loader.
   3. Click LoaderServer(Node name, Active). The Loader web UI is displayed.
      Figure 1 Loader web UI
      

6. Click New Job to go to the Basic Information page and set basic job information.
   Figure 2 Basic Information page
   
   1. Set Name to the name of the job.
   2. Set Type to Import.
   3. Set Group to the group to which the job belongs. No group is created by default. You need to click Add to create a group and click OK to save the created group.
   4. Set Queue to the Yarn queue that executes the job. The default value is root.default.
   5. Set Priority to the priority of the Yarn queue that executes the job. The default value is NORMAL. The options are VERY_LOW, LOW, NORMAL, HIGH, and VERY_HIGH.

7. In the Connection area, click Add to create a connection, set Connector to generic-jdbc-connector, configure connection parameters according to Table 1, and click Test to verify whether the connection is available. When "Test Success" is displayed, click OK. Use the same method to select clickhouse-connector (ClickHouse dedicated database connector) for connector. For details about parameter settings, see Table 2.
   

   For connection to relational databases, general database connectors (generic-jdbc-connector) or dedicated database connectors (clickhouse-connector, oracle-connector, oracle-partition-connector, and mysql-fastpath-connector) are available. However, compared with general database connectors, dedicated database connectors perform better in data import and export because they are optimized for specific database types.

   Table 1 generic-jdbc-connector connection parameters

   Parameter	Description	Example Value
   Name	Name of a relational database connection	mysql_test
   JDBC Driver Class	Name of a JDBC driver class	com.mysql.jdbc.Driver
   JDBC Connection String	JDBC connection string, in the following format: jdbc:mysql://Database IP address/Database name?&useUnicode=true&characterEncoding=GBK	jdbc:mysql://10.10.10.10/test?&useUnicode=true&characterEncoding=GBK
   Username	Username for connecting to the database	root
   Password	Password for connecting to the database	xxxx

   Table 2 clickhouse-connector connection parameters

   Parameter	Description	Example Value
   Name	Name of a relational database connection	clickhouse_jdbc_test
   JDBC Connection String	Kerberos authentication has been enabled for the cluster. The JDBC connection string format is jdbc:clickhouse://Database IP address:Database port number/Database name?ssl=true&sslmode=none. Kerberos authentication is disabled for the cluster. The JDBC connection string format is jdbc:clickhouse://Database IP address:Database port number/Database name. NOTE: Database IP address: To obtain the IP address of the ClickHouseBalancer instance, log in to FusionInsight Manager, choose Cluster > Services > ClickHouse, and click Instance. Database port number: To obtain the port number of a cluster with Kerberos authentication enabled, log in to FusionInsight Manager, choose Cluster > Services, click Logical Cluster, view the logical cluster, and obtain the value of Ssl Port in HTTP Balancer Port. To obtain the port number of a cluster with Kerberos authentication disabled, log in to FusionInsight Manager, choose Cluster > Services, click Logical Cluster, view the logical cluster, and obtain the value of Port in HTTP Balancer Port.	Kerberos authentication has been enabled for the cluster: jdbc:clickhouse://10.10.10.10:21426/test?ssl=true&sslmode=none Kerberos authentication is disabled for the cluster: jdbc:clickhouse://10.10.10.10:21423/test?ssl=true&sslmode=none
   Username	Username for connecting to the database	root
   Password	Password for connecting to the database	xxxx

Configure data source information.

8. Click Next. On the displayed From page, configure the data source information. Currently, only Table name is supported.

   Table 3 Input parameters

   Parameter	Description	Example Value
   Schema name	Schema name of the specified database	public
   Table name	Table name	test
   Table column names	Names of the columns to be imported	id,name
   Need partition column	The partition may not be specified.	false

Configure data transformation.

9. Click Next. On the displayed Transform page, set the transformation operations in the data transformation process. For details about how to select operators and set parameters, see Operator Help and Table 4.

   Table 4 Input and output parameters of the operator

   Input Type	Output Type
   Table Input	ClickHouse Output

   Drag Table Input to the grid, double-click Table Input, and select autoRecognition.

   Figure 3 Operator input
   

   Drag ClickHouse Output to the grid, double-click ClickHouse Output, and select associate or manually edit the table to correspond to the input table.

   Figure 4 Operator output
   

Set data storage information and execute the job.

10. Click Next. On the displayed To page, set Storage type to CLICKHOUSE.

    Table 5 Output parameters

    Parameter	Description	Example Value
    Storage type	Select CLICKHOUSE.	-
    Connection	Select the ClickHouse dedicated connector configured in 7.	clickhouse_jdbc_test
    Clear data before import	Select true or false. NOTE: If you select true and the table to be imported is a ClickHouse distributed table, you need to manually delete the data from the local table corresponding to the ClickHouse distributed table before your import.	true
    BatchSize	Row data written in a batch when data is written to the ClickHouse table in batches.	10000
    Number	The value cannot be changed. The default value is 1.	-

11. Click Save and Run to save and run the job.

View the job execution result.

12. Go to the Loader web UI. When Status is Succeeded, the job is complete.
    Figure 5 Viewing job details
    

13. On the ClickHouse client, check whether the data in the ClickHouse table is the same as that in the MySQL table.