On this page

Show all

Help Center/ MapReduce Service/ Component Operation Guide (LTS) (Ankara Region)/ Using Loader/ Importing Data/ Typical Scenario: Importing Data from a Relational Database to ClickHouse

Typical Scenario: Importing Data from a Relational Database to ClickHouse

Updated on 2024-11-29 GMT+08:00

View PDF

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.

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

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

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.

NOTE:

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.

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.

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.

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

Click Save and Run to save and run the job.

View the job execution result.

Go to the Loader web UI. When Status is Succeeded, the job is complete.

Figure 5 Viewing job details
On the ClickHouse client, check whether the data in the ClickHouse table is the same as that in the MySQL table.

Parent topic: Importing Data

Previous topic: Typical Scenario: Importing Data from HDFS or OBS to HBase

Next topic: Exporting Data

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot