- Product Introduction
- Purchase Guide
- Getting Started
- Operation Guide
- Development Guide
- Client Access
- Practical Tutorial
- SQL Statement
- SuperSQL Statement
- Overview of SuperSQL Statement
- Unified Statement
- Common Data Types
- DDL Statement
- CREATE DATABASE
- SHOW DATABASES
- DESCRIBE DATABASE
- ALTER DATABASE
- DROP DATABASE
- CREATE TABLE
- REPLACE TABLE AS SELECT
- SHOW TABLES
- SHOW CREATE TABLE
- SHOW TBLPROPERTIES
- DESCRIBE TABLE
- SHOW COLUMNS IN TABLE
- ALTER TABLE
- ALTER TABLE ADD COLUMNS
- ALTER TABLE ADD COLUMN AFTER/FIRST
- ALTER TABLE DROP COLUMN
- ALTER TABLE ADD PARTATION
- SHOW PARTITIONS
- ALTER TABLE DROP PARTITION
- ALTER TABLE ADD PARTITION FIELD
- ALTER TABLE DROP PARTITION FIELD
- ALTER TABLE ... RENAME COLUMN
- ALTER TABLE SET TBLPROPERTIES
- ALTER TABLE SET LOCATION
- ALTER TABLE ... WRITE ORDERED BY
- ALTER TABLE ... WRITE DISTRIBUTED BY PARTITION
- ALTER TABLE ... SET IDENTIFIER FIELDS
- ALTER TABLE ... DROP IDENTIFIER FIELDS
- MSCK REPAIR TABLE
- ANALYZE TABLES
- DROP TABLE
- EXPLAIN
- CALL STATEMENT
- CREATE VIEW AS
- SHOW VIEWS
- DESCRIBE VIEW
- SHOW CREATE VIEW
- SHOW COLUMNS IN VIEW
- ALTER VIEW
- DROP VIEW
- CREATE FUNCTION
- SHOW FUNCTION
- DROP FUNCTION
- DML Statement
- DQL Statement
- Iceberg Table Statement
- Materialized View Statement
- SQL Implicit Conversion
- Functions
- Overview of Standard Spark Statement
- Overview of Standard Presto Statement
- Reserved Words
- SuperSQL Statement
- API Documentation
- History
- Introduction
- API Category
- Making API Requests
- Data Table APIs
- Task APIs
- CreateSparkSessionBatchSQL
- CancelSparkSessionBatchSQL
- CancelTask
- CreateResultDownload
- CreateSparkAppTask
- CreateTask
- CreateTasks
- DeleteSparkApp
- DescribeEngineUsageInfo
- DescribeResultDownload
- DescribeSparkAppTasks
- DescribeTaskResult
- DescribeTasks
- QueryTaskCostDetail
- CreateSparkApp
- DescribeSparkAppJob
- DescribeSparkSessionBatchSqlLog
- ModifySparkApp
- DescribeSparkAppJobs
- ModifySparkAppBatch
- DescribeTaskStatistics
- DescribeQuery
- DescribeJobs
- DescribeJob
- Metadata APIs
- Service Configuration APIs
- CreateCHDFSBindingProduct
- DeleteCHDFSBindingProduct
- DescribeOtherCHDFSBindingList
- CreateStoreLocation
- DescribeStoreLocation
- ModifyDataEngineDescription
- RollbackDataEngineImage
- SwitchDataEngine
- SwitchDataEngineImage
- UpgradeDataEngineImage
- DeleteThirdPartyAccessUser
- DescribeDataEngineImageVersions
- DescribeSubUserAccessPolicy
- DescribeThirdPartyAccessUser
- RegisterThirdPartyAccessUser
- RestartDataEngine
- UpdateUserDataEngineConfig
- UpdateDataEngineConfig
- Permission Management APIs
- AddUsersToWorkGroup
- AttachUserPolicy
- AttachWorkGroupPolicy
- BindWorkGroupsToUser
- CreateUser
- CreateWorkGroup
- DeleteUser
- DeleteUsersFromWorkGroup
- DeleteWorkGroup
- DescribeUserInfo
- DescribeUserRoles
- DescribeUserType
- DescribeUsers
- DescribeWorkGroupInfo
- DescribeWorkGroups
- DetachUserPolicy
- DetachWorkGroupPolicy
- ModifyUser
- ModifyUserType
- ModifyWorkGroup
- UnbindWorkGroupsFromUser
- UpdateRowFilter
- CheckGrantedPermission
- Database APIs
- Data Source Connection APIs
- Data Optimization APIs
- Data Engine APIs
- Data Types
- Error Codes
- General Reference
- DLC Policy
- Service Level Agreement
- Contact Us
- Product Introduction
- Purchase Guide
- Getting Started
- Operation Guide
- Development Guide
- Client Access
- Practical Tutorial
- SQL Statement
- SuperSQL Statement
- Overview of SuperSQL Statement
- Unified Statement
- Common Data Types
- DDL Statement
- CREATE DATABASE
- SHOW DATABASES
- DESCRIBE DATABASE
- ALTER DATABASE
- DROP DATABASE
- CREATE TABLE
- REPLACE TABLE AS SELECT
- SHOW TABLES
- SHOW CREATE TABLE
- SHOW TBLPROPERTIES
- DESCRIBE TABLE
- SHOW COLUMNS IN TABLE
- ALTER TABLE
- ALTER TABLE ADD COLUMNS
- ALTER TABLE ADD COLUMN AFTER/FIRST
- ALTER TABLE DROP COLUMN
- ALTER TABLE ADD PARTATION
- SHOW PARTITIONS
- ALTER TABLE DROP PARTITION
- ALTER TABLE ADD PARTITION FIELD
- ALTER TABLE DROP PARTITION FIELD
- ALTER TABLE ... RENAME COLUMN
- ALTER TABLE SET TBLPROPERTIES
- ALTER TABLE SET LOCATION
- ALTER TABLE ... WRITE ORDERED BY
- ALTER TABLE ... WRITE DISTRIBUTED BY PARTITION
- ALTER TABLE ... SET IDENTIFIER FIELDS
- ALTER TABLE ... DROP IDENTIFIER FIELDS
- MSCK REPAIR TABLE
- ANALYZE TABLES
- DROP TABLE
- EXPLAIN
- CALL STATEMENT
- CREATE VIEW AS
- SHOW VIEWS
- DESCRIBE VIEW
- SHOW CREATE VIEW
- SHOW COLUMNS IN VIEW
- ALTER VIEW
- DROP VIEW
- CREATE FUNCTION
- SHOW FUNCTION
- DROP FUNCTION
- DML Statement
- DQL Statement
- Iceberg Table Statement
- Materialized View Statement
- SQL Implicit Conversion
- Functions
- Overview of Standard Spark Statement
- Overview of Standard Presto Statement
- Reserved Words
- SuperSQL Statement
- API Documentation
- History
- Introduction
- API Category
- Making API Requests
- Data Table APIs
- Task APIs
- CreateSparkSessionBatchSQL
- CancelSparkSessionBatchSQL
- CancelTask
- CreateResultDownload
- CreateSparkAppTask
- CreateTask
- CreateTasks
- DeleteSparkApp
- DescribeEngineUsageInfo
- DescribeResultDownload
- DescribeSparkAppTasks
- DescribeTaskResult
- DescribeTasks
- QueryTaskCostDetail
- CreateSparkApp
- DescribeSparkAppJob
- DescribeSparkSessionBatchSqlLog
- ModifySparkApp
- DescribeSparkAppJobs
- ModifySparkAppBatch
- DescribeTaskStatistics
- DescribeQuery
- DescribeJobs
- DescribeJob
- Metadata APIs
- Service Configuration APIs
- CreateCHDFSBindingProduct
- DeleteCHDFSBindingProduct
- DescribeOtherCHDFSBindingList
- CreateStoreLocation
- DescribeStoreLocation
- ModifyDataEngineDescription
- RollbackDataEngineImage
- SwitchDataEngine
- SwitchDataEngineImage
- UpgradeDataEngineImage
- DeleteThirdPartyAccessUser
- DescribeDataEngineImageVersions
- DescribeSubUserAccessPolicy
- DescribeThirdPartyAccessUser
- RegisterThirdPartyAccessUser
- RestartDataEngine
- UpdateUserDataEngineConfig
- UpdateDataEngineConfig
- Permission Management APIs
- AddUsersToWorkGroup
- AttachUserPolicy
- AttachWorkGroupPolicy
- BindWorkGroupsToUser
- CreateUser
- CreateWorkGroup
- DeleteUser
- DeleteUsersFromWorkGroup
- DeleteWorkGroup
- DescribeUserInfo
- DescribeUserRoles
- DescribeUserType
- DescribeUsers
- DescribeWorkGroupInfo
- DescribeWorkGroups
- DetachUserPolicy
- DetachWorkGroupPolicy
- ModifyUser
- ModifyUserType
- ModifyWorkGroup
- UnbindWorkGroupsFromUser
- UpdateRowFilter
- CheckGrantedPermission
- Database APIs
- Data Source Connection APIs
- Data Optimization APIs
- Data Engine APIs
- Data Types
- Error Codes
- General Reference
- DLC Policy
- Service Level Agreement
- Contact Us
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 icebergCOMMENT '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_copyCOMMENT 'table create as select'PARTITIONED BY (eno, dno)AS SELECT * from dempts;
External Tables
Statement
CREATE TABLE [ IF NOT EXISTS ] table_identifierUSING 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_copyUSING icebergCOMMENT '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_replaceCOMMENT 'table create as replace'PARTITIONED BY (eno, bucket(10, num))AS SELECT * from dempts;
External Tables
Statement
CREATE [OR REPLACE] TABLE table_identifierUSING 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_replaceUSING icebergCOMMENT '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'));
Yes
No
Was this page helpful?