A ClickHouse SQL node lets you run distributed SQL queries against structured data to improve job efficiency, schedule those jobs periodically, and integrate them with the rest of your DataWorks pipelines. This topic walks you through creating a node, writing SQL, configuring a schedule, testing the job, and publishing it to production.
Prerequisites
Before you begin, make sure you have:
-
A business process. Development work in Data Studio is organized by business processes. See Create a business process.
-
An EMR ClickHouse cluster or an ApsaraDB for ClickHouse cluster, with the required setup complete:
-
EMR — DataLake engine: See DataLake cluster configurations and DataWorks configurations.
-
EMR — Hadoop engine: See Preparations for developing on a Hadoop cluster.
-
-
A ClickHouse computing resource attached to your workspace. This lets Data Studio access your ClickHouse data for development.
-
A Serverless resource group purchased and attached to the virtual private cloud (VPC) where your ApsaraDB for ClickHouse cluster resides. See Use a Serverless resource group.
ClickHouse SQL node tasks run on Serverless resource groups (recommended) or exclusive resource groups for scheduling.
Step 1: Create a ClickHouse SQL node
-
Go to the DataStudio page. Log on to the DataWorks console. In the top navigation bar, select the target region. In the left-side navigation pane, choose Data Development and O&M > Data Development. Select the target workspace from the drop-down list and click Go to Data Development.
-
Right-click the target business process and choose New Node > ClickHouse > ClickHouse SQL.
-
In the Create Node dialog box, enter a Name for the node and click OK. Once the node is created, you can develop and configure the task in the node.
Step 2: Develop the ClickHouse SQL task
Select a ClickHouse computing resource (optional)
If your workspace has multiple ClickHouse computing resources, select one on the node's edit page. If only one resource exists, it is used by default.
Write the SQL code
In the code editor, write the SQL for your task. The following example creates a database and table, inserts a row, and runs a query:
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', '1');
SELECT * FROM ck_test.first_table;Step 3: Configure task scheduling
Click Scheduling Configuration on the right side of the node's edit page to set scheduling properties for the task. See Overview of task scheduling properties for the full list of options.
Set the Rerun property and dependent ancestor nodes before committing the node — both are required.
Step 4: Debug the job
Run tests to verify the task behaves as expected.
-
(Optional) Select a resource group and assign values to custom parameters.
-
Click the
icon in the toolbar. In the Parameters dialog box, select the resource group for scheduling the test run. -
If your SQL uses scheduling parameter variables, assign test values to them. See Task testing process.
-
-
Save and run the task. Click the
icon in the toolbar to save. Click the 
icon to run the task. -
(Optional) Run smoke testing. To check whether a scheduling node task runs correctly, perform smoke testing in the development environment. Run the test when committing the node or after it is committed. See Perform smoke testing.
Step 5: Commit and publish the task
-
Click the
icon in the toolbar to save the node. -
Click the
icon in the toolbar to commit the node. In the Commit dialog box, enter a Change description. Optionally, enable code review — code review helps ensure the quality of your task code and prevents errors that may occur if incorrect code is published without a review. Committed code must be approved by reviewers before it can be published. See Code review.Set the Rerun property and dependent ancestor nodes before committing.
-
If you are using a workspace in standard mode, click Publish in the upper-right corner of the node's edit page after committing. This publishes the task to the production environment. See Publish tasks.
What's next
After publishing, the task runs periodically based on its scheduling configuration. To monitor the task, click O&M in the upper-right corner of the node's edit page to open the Operation Center, where you can view the scheduling and running status of the auto triggered task.