Realtime Compute for Apache Flink:Manage Paimon catalogs

• Create an Apache Paimon table by using the CREATE TABLE statement

  You can create an Apache Paimon database by executing the following commands in the SQL editor. For detailed instructions, see Scripts.

  In the my_db database of a catalog named my-catalog, create a partitioned table in which the partition key is dt, the primary key columns are dt, shop_id, and user_id, and the number of buckets is fixed to 4. Sample code:

  -- Replace my-catalog with the name of your Apache Paimon catalog. 
  -- Replace my_db with the name of the actual database. 
  -- Replace my_tbl with a custom name. 
  CREATE TABLE `my-catalog`.`my_db`.`my_tbl` (
    dt STRING,
    shop_id BIGINT,
    user_id BIGINT,
    num_orders INT,
    total_amount INT,
    PRIMARY KEY (dt, shop_id, user_id) NOT ENFORCED
  ) PARTITIONED BY (dt) WITH (
    'bucket' = '4'
  );

  For more information about the parameters and usage of Apache Paimon tables, see Apache Paimon connector and Primary key tables and append-only tables.

• Create an Apache Paimon table by using the CREATE TABLE AS (CTAS) or CREATE DATABASE AS (CDAS) statement

  The CTAS and CDAS statements automatically synchronize data as well as table schema changes, facilitating synchronization from data sources such as MySQL and Kafka to an Apache Paimon catalog.

  To synchronize data using the CTAS and CDAS statements, you must deploy the draft and start the deployment. For more information, see Develop an SQL draft and Start a job deployment.

  Note

  • When you create an Apache Paimon table by using the CTAS or CDAS statement, you cannot specify bucket' = '-1' to use Apache Paimon primary key tables or Apache Paimon append-only tables (non-primary key tables) based on dynamic bucketing.

  • The CTAS and CDAS statements allow you to configure properties for a physical table in the WITH clause as you create the table. When the deployment starts, these properties take effect on the downstream tables to which you want to synchronize data. For more information about the table properties supported by the statements, see Apache Paimon connector.

  • Create a table and synchronize data to the table

    In the following sample code, the Apache Paimon table `my-catalog`.`my_db`.`web_sales` is automatically created based on the schema of the mysql.tpcds.web_sales table. Data in the mysql.tpcds.web_sales table is synchronized to the Apache Paimon table. The number of buckets for the Apache Paimon table is set to 4, and the input-based incremental data generation mechanism is used.

    CREATE TABLE IF NOT EXISTS `<catalog name>`.`<db name>`.`<table name>`
    WITH (
      'bucket' = '4',
      'changelog-producer' = 'input'
    ) AS TABLE mysql.tpcds.web_sales;

  • Create a database and synchronize data in all tables to the database

    In the following sample code, Apache Paimon tables are automatically created in the `my-catalog`.`my_db` database based on the schema of each table in the mysql.tpcds database. Data of all tables in the mysql.tpcds database is synchronized to the Apache Paimon tables. The input-based incremental data generation mechanism is used.

    CREATE DATABASE IF NOT EXISTS `<catalog name>`.`<db name>`
    WITH (
      'changelog-producer' = 'input'
    ) AS DATABASE mysql.tpcds INCLUDING ALL TABLES;

  • Synchronize column type changes

    The CTAS or CDAS statement allows you to add columns and change the data types of specific columns when creating Apache Paimon tables. You can determine whether to use the type normalization mode based on your business requirements.

    • Non-type normalization mode (default mode)

      The column types of Apache Paimon tables created using the CTAS or CDAS statement are the same as the column types of their source tables. The rules of changing column types are as follows:

      • Integer data types TINYINT, SMALLINT, INT, and BIGINT can be changed to integer data types with the same or higher precision. The precision of TINYINT is the lowest and the precision of BIGINT is the highest.

      • Floating-point data types FLOAT and DOUBLE can be changed to floating-point data types with the same or higher precision. The precision of FLOAT is the lowest and the precision of DOUBLE is the highest.

      • String data types CHAR, VARCHAR, and STRING can be changed to string data types with the same or higher precision.

    • Type normalization mode

      When you create Apache Paimon tables by using the CTAS or CDAS statement, you can specify 'enableTypeNormalization' = 'true' in the WITH clause to enable type normalization mode. This means changing data types in the upstream table will not lead to a deployment failure if the old and new data types can be normalized, which means they can be converted into the same data type. Type normalization rules are as follows:

      • TINYINT, SMALLINT, INT, and BIGINT are normalized into BIGINT.

      • FLOAT and DOUBLE are normalized into DOUBLE.

      • CHAR, VARCHAR, and STRING are normalized into STRING.

      • Other data types are not normalized.

      For example,

      • if you change SMALLINT to INT, the deployment runs as expected because both are normalized to BIGINT;

      • changing from FLOAT to BIGINT causes an exception because FLOAT and BIGINT cannot be not normalized.

      Apache Paimon stores data in its normalized type. For example, SMALLINT and INT from a MySQL table become BIGINT in an Apache Paimon table.

You can manage an Apache Paimon database by executing the following commands in the SQL editor. For detailed instructions, see Scripts.

ALTER TABLE my_table SET (
  'write-buffer-size' = '256 MB',
  'write-buffer-spillable' = 'true'
);

ALTER TABLE my_table RENAME TO my_table_new;

ALTER TABLE my_table RENAME c0 TO c1;

ALTER TABLE my_table DROP (c1, c2);

ALTER TABLE my_table DROP PARTITION (`dt` = '20240108', `hh` = '08'), PARTITION (`dt` = '20240109', `hh` = '07');

ALTER TABLE my_table MODIFY buy_count BIGINT COMMENT 'this is buy count';

ALTER TABLE my_table MODIFY col_a DOUBLE;

You can drop an Apache Paimon table by executing the following commands in the SQL editor. For more information, see Scripts.

-- Replace my-catalog with the name of your Apache Paimon catalog. 
-- Replace my_db with the name of your database. 
-- Replace my_tbl with the name of your Apache Paimon table. 
DROP TABLE `my-catalog`.`my_db`.`my_tbl`;

If the message The following statement has been executed successfully! appears, the Apache Paimon table is dropped.