Release Notes

Product Introduction

Overview

Strengths

Use Cases

Purchase Guide

Billing Overview

Refund

Payment Overdue

Configuration Adjustment Fees

Getting Started

Complete Process for New User Activation

DLC Data Import Guide

Quick Start with Data Analytics in Data Lake Compute

Quick Start with Permission Management in Data Lake Compute

Quick Start with Partition Table

Enabling Data Optimization

Cross-Source Analysis of EMR Hive Data

Standard Engine Configuration Guide

Configuring Data Access Policy

Operation Guide

Console Operation Introduction

Development Guide

Runtime Environment

SparkJar Job Development Guide

PySpark Job Development Guide

Query Performance Optimization Guide

UDF Function Development Guide

System Restraints

Client Access

JDBC Access

TDLC Command Line Interface Tool Access

Third-party Software Linkage

Python Access

Practical Tutorial

Accessing DLC Data with Power BI

Table Creation Practice

Using Apache Airflow to Schedule DLC Engine to Submit Tasks

Direct Query of DLC Internal Storage with StarRocks

Spark cost optimization practice

DATA + AI

Using DLC to Analyze CLS Logs

Using Role SSO to Access DLC

Resource-Level Authentication Guide

Implementing Tencent Cloud TCHouse-D Read and Write Operations in DLC

DLC Native Table

SQL Statement

SuperSQL Statement

Overview of Standard Spark Statement

Overview of Standard Presto Statement

Reserved Words

API Documentation

History

Introduction

API Category

Making API Requests

Data Table APIs

Task APIs

Metadata APIs

Service Configuration APIs

Permission Management APIs

Database APIs

Data Source Connection APIs

Data Optimization APIs

Data Engine APIs

Resource Group for the Standard Engine APIs

Data Types

Error Codes

General Reference

Error Codes

Quotas and limits

Operation Guide on Connecting Third-Party Software to DLC

FAQs

FAQs on Permissions

FAQs on Engines

FAQs on Features

FAQs on Spark Jobs

DLC Policy

Data Privacy And Security Agreement

Service Level Agreement

Differences in Statement Between Iceberg External Tables and Native Tables

PDF

Focus Mode

Font Size

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'));
﻿
﻿

Help and Support

Was this page helpful?

You can also Contact sales or Submit a Ticket for help.

Help us improve! Rate your documentation experience in 5 mins.

Feedback

tencent cloud

Data Lake Compute

Differences in Statement Between Iceberg External Tables and Native Tables

CREATE TABLE

Native Tables

External Tables

CREATE TABLE AS SELECT

Native Tables

External Tables

REPLACE TABLE AS SELECT

Native Tables

External Tables

Procedure

Native Tables

External Tables

Help and Support