OpenLake:Manage Paimon Catalogs

• Create a table using the CREATE TABLE statement

  In the Data Query text editor, enter the following command, select it, and then click Run.

  The following sample code shows how to create a partitioned table in the `my_db` database of the `my-catalog` catalog. The partition key is `dt`, the primary key consists of `dt`, `shop_id`, and `user_id`, and the number of buckets is fixed at 4.

  -- Replace my-catalog with the name of your Paimon catalog.
  -- Replace my_db with the name of the database that you want to use.
  -- You can also replace my_tbl with a custom name in English.
  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 Paimon tables, see Paimon connector and Paimon primary key tables and append-only tables.

• Create a table using the CREATE TABLE AS (CTAS) or CREATE DATABASE AS (CDAS) statement

  The CTAS and CDAS statements automatically synchronize data and table schema changes. You can use these statements to easily synchronize tables from data sources such as MySQL and Kafka to a Paimon catalog.

  To synchronize data using CTAS and CDAS statements, you must Deploy a job and then Start it. For more information, see Job development map and Start a job.

  Note

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

  • CTAS and CDAS statements support setting physical table properties in the `WITH` clause. When you create a sink table, the corresponding properties are set on the table at the same time. When the job starts, these parameters are applied to the descendant tables that require synchronization. For more information about the supported table properties, see Paimon connector.

  • Create a specific table and synchronize its data

    For example, the Apache Paimon table `my-catalog`.`my_db`.`web_sales` is automatically created based on the schema of the mysql.tpcds.web_sales table, and data from the source table is synchronized. 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 tables for an entire 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 from 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

    Paimon tables created with CTAS/CDAS statements support not only adding columns but also specific column type changes. You can choose whether to use the lenient field type mode as needed.

    • By default

      By default, the column types of a Paimon table created with a CTAS/CDAS statement are consistent with the source table's column types. The following column type changes are supported:

      • Integer types `TINYINT`, `SMALLINT`, `INT`, and `BIGINT` can be changed to an integer type with the same or higher precision. `TINYINT` has the lowest precision, and `BIGINT` has the highest.

      • Floating-point types `FLOAT` and `DOUBLE` can be changed to a floating-point type with the same or higher precision. `FLOAT` has the lowest precision, and `DOUBLE` has the highest.

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

    • Lenient field type mode

      When you create Apache Paimon tables 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 does not cause 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 to `BIGINT`.

      • `FLOAT` and `DOUBLE` are normalized to `DOUBLE`.

      • `CHAR`, `VARCHAR`, and `STRING` are normalized to `STRING`.

      • Other data types are not normalized.

      For example:

      • If `SMALLINT` is changed to `INT`, both are normalized to `BIGINT`. The modification is considered successful, and the job runs normally.

      • If `FLOAT` is changed to `BIGINT`, their normalized types are `DOUBLE` and `BIGINT`, respectively. This is an incompatible change and results in an exception.

      The data types stored in the Paimon table will be unified to the normalized type. For example, two columns in MySQL with types `SMALLINT` and `INT` are both stored as `BIGINT` in the Paimon table.

Enter the following command in the Data Query editor, select the code, and click Run.

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;

In the Data Query SQL editor, enter the following command, select it, and click Run.

-- Replace my-catalog with the name of your Paimon catalog.
-- Replace my_db with the name of the database that you want to use.
-- Replace my_tbl with the name of the Paimon catalog table that you created.
DROP TABLE `my-catalog`.`my_db`.`my_tbl`;

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