DocsICP FilingConsole
Documentation
Home Page DataWorks User Guide DataStudio (legacy) Node development Supported node types ClickHouse SQL

ClickHouse SQL

Updated at:
Copy as MD
Product
My Favorites

ClickHouse SQL nodes in DataWorks let you develop, periodically schedule, and integrate ClickHouse SQL tasks with other jobs, leveraging a distributed SQL engine for efficient structured-data processing.

Prerequisites

  • You have bound a ClickHouse computing resource to the workspace.

    Add your ClickHouse database to DataWorks as a ClickHouse computing resource and bind it to DataStudio so you can access ClickHouse data for development.

  • A serverless resource group is purchased and bound to the VPC where the ClickHouse cluster is located. For more information, see Use a serverless resource group.

    Note

    You can use a serverless resource group (recommended) or an exclusive resource group for scheduling to run ClickHouse SQL node tasks.

Step 1: Create a ClickHouse SQL node

  1. Log on to the DataWorks console. In the target region, click Data Development and O&M > Data Development in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Data Development.

  2. Right-click the target workflow and choose Create Node > CLICKHOUSE > ClickHouse SQL.

  3. In the Create Node dialog box, enter a Name for the node and click OK. After the node is created, you can develop and configure the task in the node editor.

Step 2: Develop a ClickHouse SQL task

(Optional) Select a ClickHouse computing resource

If multiple ClickHouse computing resources are added to your workspace, you must select the desired one on the editor page of the ClickHouse SQL node. If only one ClickHouse computing resource is added, it is used by default.

Develop SQL code

Write the ClickHouse SQL statements in the code editor. The following code provides an 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', '1');
SELECT * FROM ck_test.first_table;

Step 3: Configure scheduling

To run the task periodically, click Scheduling on the right side of the node editor page and configure its scheduling properties. For more information, see Overview of task scheduling property configuration.

Note

You must set the Rerun attribute property and specify the Parent Nodes before you can commit the node.

Step 4: Debug the task

Debug your code to verify that it works as expected.

  1. (Optional) Select a runtime resource group and assign values to custom parameters.

    • Click the 高级运行 icon in the toolbar. In the Parameter dialog box, select the scheduling resource group that you want to use for debugging.

    • If your task code uses scheduling parameter variables, you can assign values to the variables for debugging. For more information about the parameter assignment logic, see Debug tasks.

  2. Save and run the SQL statements.

    Click the 保存 icon in the toolbar to save the SQL statements that you wrote. Then, click the 运行 icon to run the SQL task.

  3. (Optional) Perform smoke testing.

    To verify that the scheduled task runs correctly in the development environment, you can perform smoke testing when you commit the node or after it is committed. For more information, see Perform smoke testing.

Step 5: Commit and deploy

Commit and deploy the node so that the task runs periodically based on your scheduling configuration.

  1. Click the 保存 icon in the toolbar to save the node.

  2. Click the 提交 icon in the toolbar to commit the node task.

    In the Submission dialog box, enter a Change Description and select whether to perform a code review after the node is committed.

    Note

    • You must set the Rerun attribute property and specify the Parent Nodes before you can commit the node.

    • Code review helps ensure code quality and prevent errors in the production environment. If you enable the code review feature, the committed code can be deployed only after it passes the review. For more information, see Code review.

If you are using a workspace in standard mode, you must click Deploy in the upper-right corner of the node editor page to deploy the task to the production environment after the task is committed. For more information, see Deploy tasks.

Next steps

After deployment, the task runs on schedule. To view its scheduling status, click O&M Personnel in the upper-right corner of the node editor page. For more information, see Manage periodic tasks.

Previous：AnalyticDB for MySQLNext：EMR