SQL Configuration File

Structure of SQL Configuration File

The SQL configuration file appears as follows:

SQL

/* config
env {
  parallelism = 1
  job.mode = "BATCH"
}
*/

CREATE TABLE source_table WITH (
  'connector'='jdbc',
  'type'='source',
  'url' = 'jdbc:mysql://localhost:3306/seatunnel',
  'driver' = 'com.mysql.cj.jdbc.Driver',
  'user' = 'root',
  'password' = '123456',
  'query' = 'select * from source',
  'properties'= '{
    useSSL = false,
    rewriteBatchedStatements = true
  }'
);

CREATE TABLE sink_table WITH (
  'connector'='jdbc',
  'type'='sink',
  'url' = 'jdbc:mysql://localhost:3306/seatunnel',
  'driver' = 'com.mysql.cj.jdbc.Driver',
  'user' = 'root',
  'password' = '123456',
  'generate_sink_sql' = 'true',
  'database' = 'seatunnel',
  'table' = 'sink'
);

INSERT INTO sink_table SELECT id, name, age, email FROM source_table;

Explanation of SQL Configuration File

General Configuration in SQL File

/* config
env {
  parallelism = 1
  job.mode = "BATCH"
}
*/

In the SQL file, common configuration sections are defined using /* config */ comments. Inside, common configurations like env can be defined using HOCON format.

SOURCE SQL Syntax

CREATE TABLE source_table WITH (
  'connector'='jdbc',
  'type'='source',
  'url' = 'jdbc:mysql://localhost:3306/seatunnel',
  'driver' = 'com.mysql.cj.jdbc.Driver',
  'user' = 'root',
  'password' = '123456',
  'query' = 'select * from source',
  'properties' = '{
    useSSL = false,
    rewriteBatchedStatements = true
  }'
);

• Using CREATE TABLE ... WITH (...) syntax creates a mapping for the source table. The TABLE name is the name of the source-mapped table, and the WITH syntax contains source-related configuration parameters.
• There are two fixed parameters in the WITH syntax: connector and type, representing connector plugin name (such as jdbc, FakeSource, etc.) and source type (fixed as source), respectively.
• Other parameter names can reference relevant configuration parameters of the corresponding connector plugin, but the format needs to be changed to 'key' = 'value',.
• If 'value' is a sub-configuration, you can directly use a string in HOCON format. Note: if using a sub-configuration in HOCON format, the internal property items must be separated by ,, like this:

'properties' = '{
  useSSL = false,
  rewriteBatchedStatements = true
}'

• If using ' within 'value', it needs to be escaped with '', like this:

'query' = 'select * from source where name = ''Joy Ding'''

SINK SQL Syntax

CREATE TABLE sink_table WITH (
  'connector'='jdbc',
  'type'='sink',
  'url' = 'jdbc:mysql://localhost:3306/seatunnel',
  'driver' = 'com.mysql.cj.jdbc.Driver',
  'user' = 'root',
  'password' = '123456',
  'generate_sink_sql' = 'true',
  'database' = 'seatunnel',
  'table' = 'sink'
);

• Using CREATE TABLE ... WITH (...) syntax creates a mapping for the target table. The TABLE name is the name of the target-mapped table, and the WITH syntax contains sink-related configuration parameters.
• There are two fixed parameters in the WITH syntax: connector and type, representing connector plugin name (such as jdbc, console, etc.) and target type (fixed as sink), respectively.
• Other parameter names can reference relevant configuration parameters of the corresponding connector plugin, but the format needs to be changed to 'key' = 'value',.

INSERT INTO SELECT Syntax

INSERT INTO sink_table SELECT id, name, age, email FROM source_table;

• The SELECT FROM part is the table name of the source-mapped table.
• The INSERT INTO part is the table name of the target-mapped table.
• Note: This syntax does not support specifying fields in INSERT, like this: INSERT INTO sink_table (id, name, age, email) SELECT id, name, age, email FROM source_table;

INSERT INTO SELECT TABLE Syntax

INSERT INTO sink_table SELECT source_table;

• The SELECT part directly uses the name of the source-mapped table, indicating that all data from the source table will be inserted into the target table.
• Using this syntax does not generate related transform configurations. This syntax is generally used in multi-table synchronization scenarios. For example:

CREATE TABLE source_table WITH (
  'connector'='jdbc',
  'type' = 'source',
  'url' = 'jdbc:mysql://127.0.0.1:3306/seatunnel',
  'driver' = 'com.mysql.cj.jdbc.Driver',
  'user' = 'root',
  'password' = '123456',
  'table_list' = '[
      {
        table_path = "source.table1"
      },
      {
        table_path = "source.table2",
        query = "select * from source.table2"
      }
    ]'
);

CREATE TABLE sink_table WITH (
  'connector'='jdbc',
  'type' = 'sink',
  'url' = 'jdbc:mysql://127.0.0.1:3306/seatunnel',
  'driver' = 'com.mysql.cj.jdbc.Driver',
  'user' = 'root',
  'password' = '123456',
  'generate_sink_sql' = 'true',
  'database' = 'sink'
);

INSERT INTO sink_table SELECT source_table;

CREATE TABLE AS Syntax

CREATE TABLE temp1 AS SELECT id, name, age, email FROM source_table;

• This syntax creates a temporary table with the result of a SELECT query, used for INSERT INTO operations.
• The syntax of the SELECT part refers to: SQL Transform query configuration item

CREATE TABLE temp1 AS SELECT id, name, age, email FROM source_table;

INSERT INTO sink_table SELECT * FROM temp1;

Example of SQL Configuration File Submission

./bin/seatunnel.sh --config ./config/sample.sql