DataWorks:ClickHouse SQL node

Write SQL code

On the ClickHouse SQL node edit page, write your SQL in the editor.

To pass dynamic values at runtime, define variables in your SQL using the ${variable_name} format. Assign values to these variables in Schedule > Scheduling parameters on the right side of the node configuration page. For more information, see Sources and expressions of scheduling parameters.

The following example creates a database and table, inserts a row, and queries the result:

-- Create a database
CREATE DATABASE IF NOT EXISTS ck_test;

-- Create a table using the MergeTree engine
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 a row; ${var} is resolved at runtime from scheduling parameters
INSERT INTO ck_test.first_table (product_code, package_name) VALUES ('1', ${var});

-- Query the table
SELECT * FROM ck_test.first_table;

In this example, set ${var} to 1 in Schedule > Scheduling parameters.

Run the SQL task

1. In the Run Configuration pane, configure the compute resource and resource group:

   • Compute Resource: Select your registered CDH cluster.

   • Resource Group: Select a scheduling resource group that passes the data source connectivity test. For more information, see Network connectivity solutions.

2. On the toolbar, click the Select Data Source drop-down list. In the dialog box that appears, select the ClickHouse data source you created, then click Run.