tencent cloud

Feedback

Differences in Statement Between Iceberg External Tables and Native Tables

Last updated: 2024-08-07 17:29:26
    Data Lake Compute (DLC) uses Iceberg statement version 0.13.1. For detailed statement description, see the Iceberg Official Documentation.
    When you use Iceberg external tables, the SQL statement differs from that of native Iceberg tables in the following ways.

    CREATE TABLE

    Native Tables

    Statement
    CREATE TABLE [ IF NOT EXISTS ] table_identifier
    ( col_name[:] col_type [ COMMENT col_comment ], ... )
    [ COMMENT table_comment ]
    [ PARTITIONED BY ( col_name1, transform(col_name2), ... ) ]
    Example
    CREATE TABLE dempts(
    id bigint COMMENT 'id number',
    num int,
    eno float,
    dno double,
    cno decimal(9,3),
    flag boolean,
    data string,
    ts_year timestamp,
    date_month date,
    bno binary,
    point struct<x: double, y: double>,
    points array<struct<x: double, y: double>>,
    pointmaps map<struct<x: int>, struct<a: int>>
    )
    COMMENT 'table documentation'
    PARTITIONED BY (bucket(16,id), years(ts_year), months(date_month), identity(bno), bucket(3,num), truncate(10,data));

    External Tables

    Statement
    CREATE TABLE [ IF NOT EXISTS ] table_identifier
    ( col_name[:] col_type [ COMMENT col_comment ], ... )
    USING iceberg
    [ COMMENT table_comment ]
    [ PARTITIONED BY ( col_name1, transform(col_name2), ... ) ]
    [ LOCATION path ]
    [ TBLPROPERTIES ( property_name=property_value, ... ) ]
    Example
    CREATE TABLE dempts(
    id bigint COMMENT 'id number',
    num int,
    eno float,
    dno double,
    cno decimal(9,3),
    flag boolean,
    data string,
    ts_year timestamp,
    date_month date,
    bno binary,
    point struct<x: double, y: double>,
    points array<struct<x: double, y: double>>,
    pointmaps map<struct<x: int>, struct<a: int>>
    )
    USING iceberg
    COMMENT 'table documentation'
    PARTITIONED BY (bucket(16,id), years(ts_year), months(date_month), identity(bno), bucket(3,num), truncate(10,data))
    LOCATION 'cosn://rickytest-1305424723/channing-test/loc'
    TBLPROPERTIES ('write.format.default'='orc');

    CREATE TABLE AS SELECT

    Native Tables

    Statement
    CREATE TABLE [ IF NOT EXISTS ] table_identifier
    [ COMMENT table_comment ]
    [ PARTITIONED BY ( col_name1, transform(col_name2), ... ) ]
    AS select_statement
    Example
    CREATE TABLE IF NOT EXISTS dempts_copy
    COMMENT 'table create as select'
    PARTITIONED BY (eno, dno)
    AS SELECT * from dempts;

    External Tables

    Statement
    CREATE TABLE [ IF NOT EXISTS ] table_identifier
    USING iceberg
    [ COMMENT table_comment ]
    [ PARTITIONED BY ( col_name1, transform(col_name2), ... ) ]
    [ LOCATION path ]
    [ TBLPROPERTIES ( property_name=property_value, ... ) ]
    AS select_statement
    Example
    CREATE TABLE dempts_copy
    USING iceberg
    COMMENT 'table create as select'
    PARTITIONED BY (eno, dno)
    LOCATION 'cosn://rickytest-1305424723/channing-test/loc'
    TBLPROPERTIES ('write.format.default'='avro')
    AS SELECT * from dempts;
    

    REPLACE TABLE AS SELECT

    Native Tables

    Statement
    CREATE OR REPLACE TABLE table_identifier
    [ COMMENT table_comment ]
    [ PARTITIONED BY ( col_name1, transform(col_name2), ... ) ]
    AS select_statement
    Example
    CREATE OR REPLACE TABLE dempts_replace
    COMMENT 'table create as replace'
    PARTITIONED BY (eno, bucket(10, num))
    AS SELECT * from dempts;

    External Tables

    Statement
    CREATE [OR REPLACE] TABLE table_identifier
    USING iceberg
    [ COMMENT table_comment ]
    [ PARTITIONED BY ( col_name1, transform(col_name2), ... ) ]
    [ LOCATION path ]
    [ TBLPROPERTIES ( property_name=property_value, ... ) ]
    AS select_statement
    Example
    CREATE OR REPLACE TABLE dempts_replace
    USING iceberg
    COMMENT 'table create as replace'
    PARTITIONED BY (eno, dno)
    LOCATION 'cosn://rickytest-1305424723/channing-test/loc'
    TBLPROPERTIES ('write.format.default'='avro')
    AS SELECT * from dempts;

    Procedure

    Caution
    The original table for migration must be a Hive table or a Spark table.

    Native Tables

    Not supported currently.

    External Tables

    snapshot Create lightweight temporary tables based on the original tables. The temporary tables directly reuse the snapshots of the original tables. Statement
    CALL `Catalog`.`system`.snapshot(source_table, table, [location], [properties]);
    Example
    CALL `DataLakeCatalog`.`system`.snapshot('validation.table_01', 'validation.snap');
    CALL `DataLakeCatalog`.`system`.snapshot('validation.table_01', 'validation.snap2', 'cosn://channingdata-1305424723/example3/');
    call Update and replace table attributes. Statement
    CALL `Catalog`.`system`.migrate(table, [properties]);
    Example
    CALL `DataLakeCatalog`.`system`.migrate('validation.table_01');
    CALL `DataLakeCatalog`.`system`.migrate('validation.table_01', map('data', 'name'));
    
    add_files Load data files directly from Hive, and you can specify data files to a specific partition. Statement
    CALL `Catalog`.`system`.add_files(table, source_table, [partition_filter]);
    Example
    CALL `DataLakeCatalog`.`system`.add_files(`table`=>'validation.table_02', `source_table`=>'validation.table_01');
    CALL `DataLakeCatalog`.`system`.add_files(`table`=>'validation.table_02', `source_table`=>'validation.table_01', `partition_filter`=>map('part_col', 'A'));
    
    
    Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support