DataWorks:ClickHouse SQL

Prerequisites

• EMR engine types include DataLake and Hadoop. Each engine type requires different preparations before you create a node. Based on your scenario, complete the required preparations in EMR and DataWorks.

  • DataLake: For more information, see Configure a DataLake cluster and Configure DataWorks.

  • Hadoop: For more information, see Prepare a Hadoop cluster for development.

• You have created a ClickHouse data source and bound it to a workspace.

  To access ClickHouse data for development, create a ClickHouse data source in DataWorks and bind it to Data Studio. For more information, see Bind a ClickHouse compute resource.

• You have created a ClickHouse SQL node. For more information, see Create a scheduled workflow node.

Procedure

1. On the ClickHouse SQL node editing page, follow these steps:

   Develop SQL code

   In the SQL editor, write your task code. You can define variables using the ${variable_name} format. Assign values to these variables under Scheduling Parameters in the Scheduling Settings pane on the right to enable dynamic parameter passing during scheduled runs. For more information about scheduling parameters, see Sources and expressions for scheduling parameters. Example:

   CREATE DATABASE IF NOT EXISTS ck_test;
   CREATE TABLE IF NOT EXISTS  ck_test.first_table  (
   `product_code` String,
   `package_name` String
   ) ENGINE = MergeTree ORDER BY package_name SETTINGS index_granularity = 8192;
   INSERT INTO ck_test.first_table (product_code, package_name) VALUES ('1', ${var});
   SELECT * FROM ck_test.first_table;

   Note

   In this example, the ${var} parameter can be set to 1.

   Run SQL task

   1. In the Run Configuration pane, configure the Compute Resource and Resource Group.

      1. For Computing Resource, select the name of the CDH cluster that you registered in DataWorks.

      2. For Resource Group, select a scheduling resource group that has a confirmed connection to the data source. For more information, see Network connectivity solutions.

   2. On the toolbar, click the Select a data source drop-down menu. In the dialog box, select the ClickHouse data source that you created, and click Run to run the SQL task.

2. To run the node periodically, configure its scheduling properties based on your business requirements. For details, see Node scheduling configuration.

3. After configuring the node, deploy it. For more information, see Node and workflow deployment.

4. After you deploy the task, view its run status in Operation Center. For more information, see Get started with Operation Center.