tencent cloud

Differences in Statement Between Iceberg External Tables and Native Tables
Download PDF
Last updated: 2024-08-07 17:29:26
Differences in Statement Between Iceberg External Tables and Native Tables
Last updated: 2024-08-07 17:29:26
Download PDF
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'));


Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No

Feedback