ApsaraDB for ClickHouse:Use SQL statements to write Flink data to an ApsaraDB for ClickHouse cluster

Limits

You can use SQL statements to write Flink data to ApsaraDB for ClickHouse only when the compute engine of Realtime Compute for Apache Flink is Ververica Runtime (VVR) 3.0.2 or later.

Prerequisites

• A table is created in ApsaraDB for ClickHouse. For more information, see Create a table.

• A whitelist is configured in ApsaraDB for ClickHouse. For more information, see Configure a whitelist.

• Fully managed Flink is activated. For more information, see Procedure for activating the service.

Procedure

1. Log on to the console of fully managed Flink and create an SQL draft.

   1. Log on to the Realtime Compute for Apache Flink console.

   2. On the Fully Managed Flink tab, find the workspace that you want to manage and click Console in the Actions column.

   3. In the left-side navigation pane, click SQL Editor. In the upper-left corner of the SQL Editor page, click New.

   4. On the SQL Scripts tab of the New Draft dialog box, click Blank Stream Draft.

      Fully managed Flink provides various SQL code templates and supports data synchronization. Each code template provides specific scenarios, code samples, and instructions for you. You can click the desired template to quickly learn about the features and related syntax of Flink and implement your business logic. For more information, see Code templates and Data synchronization templates.

   5. Click Next.

   6. In the New Draft dialog box, configure the parameters of the draft. The following table describes the parameters.

      Parameter	Description
      Name	The name of the draft that you want to create. Note The draft name must be unique in the current project.
      Location	The folder in which the code file of the draft is saved. You can also click the icon to the right of an existing folder to create a subfolder.
      Engine Version	The engine version of Flink that is used by the draft. For more information about engine versions, version mappings, and important time points in the lifecycle of each version, see Engine version.

   7. Click Create.

2. On the Draft Editor page, write and run the code.

   1. Create a source table and a result table. Then, insert data from the source table into the result table.

      -- Create a source table named sls_test_single_local. 
      CREATE TEMPORARY TABLE sls_test_single_local (
        id INT,
        name VARCHAR,
        age BIGINT,
        rate FLOAT
      ) WITH (
        'connector' = 'datagen',
        'rows-per-second' = '50'
      );
      -- Create a result table named clickhouse_output. 
      CREATE TEMPORARY TABLE clickhouse_output (
        id INT,
        name VARCHAR,
        age BIGINT,
        rate FLOAT
      ) WITH (
        'connector' = 'clickhouse',
        'url' = 'jdbc:clickhouse://demo.aliyuncs.com:8123',
        'userName' = 'test',
        'password' = '280226Ck',
        'tableName' = 'sls_test_single_local'
      );
      -- Insert data from the source table into the result table. 
      INSERT INTO clickhouse_output
      SELECT 
        id,
        name,
        age,
        rate
      FROM sls_test_single_local;

      Note

      For more information about the syntax for creating a result table, see Create a ClickHouse result table.

   2. Click Save.

   3. Click Validate.

   4. Click Publish.

   5. In the Publish the draft dialog box, click OK to publish the code to the production environment.

   6. In the Draft is published successfully, please check operations for details message, click operations.

3. On the Deployments page, start the job.

   1. Click Start in the upper-right corner.

   2. On the Deployment Starting Configuration page, click Start.

4. Query tables in ApsaraDB for ClickHouse.

   1. Log on to the ApsaraDB for ClickHouse console.

   2. In the upper-left corner, select the region where the cluster deployed with the data synchronization job is located.

   3. On the Clusters page, click the Default Instances tab. Find the cluster deployed with the data synchronization job and click the cluster ID.

   4. On the Cluster Details page, click Log On to Database in the upper-right corner.

   5. In the Log on to Database Instance dialog box, enter the username and password of your database account and click Login.

   6. Enter a query statement and click Execute(F8). In this example, the following sample statement is executed:

      select * from db01.sls_test_single_local;

      The following result is returned.