This topic describes how to use SQL statements to write Flink data to ApsaraDB ClickHouse.

Limits

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

Prerequisites

Procedure

  1. Log on to the console of fully managed Flink and create a job.
    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 Draft Editor.
    4. Click New.
    5. In the New Draft dialog box, configure the parameters of the job. The following table describes the parameters.
      Parameter Description
      Name The name of the job.
      Note The job name must be unique in the current project.
      Type You must set the value of this parameter to STREAM / SQL.
      Streaming jobs and batch jobs support the following file types:
      • SQL
      • JAR
      • PYTHON
      Note Only Realtime Compute for Apache Flink that uses Ververica Runtime (VVR) 3.0.1 or later supports batch jobs.
      Deployment Target The cluster in which the job is deployed. You must select a cluster type before you can select a cluster. The following cluster types are supported:
      • Per-Job Clusters: suitable for jobs that consume a large number of resources or jobs that run in a continuous and stable manner. This is the default value. Each job requires an independent JobManager to achieve resource isolation between jobs. Therefore, the resource utilization of JobManagers for jobs that involve a small amount of data is low.
      • Session Clusters: suitable for jobs that consume few resources or jobs that start and stop frequently. Multiple jobs can reuse the same JobManager. This improves the resource utilization of the JobManager.
      Note If you need to enable the SQL preview feature, you must select Session Clusters and turn on Use for SQL Editor previews. For more information, see Debug a job and Configure a development and test environment (session cluster).
      Location The folder in which the code file of the job is saved.

      You can click the Create Folder icon to the right of an existing folder to create a subfolder.

    6. Click OK.
  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 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. Use Realtime Compute for Apache Flink to insert data into a table in ApsaraDB for ClickHouse