tencent cloud

CREATE TABLE [ IF NOT EXISTS ] table_identifier
( col_name[:] col_type [ COMMENT col_comment ], ... )
[ COMMENT table_comment ]
[ PARTITIONED BY ( col_name1, transform(col_name2), ... ) ]

col_type
: primitive_type
  | nested_type
​
primitive_type
: boolean
| int/integer
| long/bigint
| float
| double
| decimal(p,s), p = maximum number of digits, s = maximum number of decimal places, s <= p <= 38
| date
| timestamp, timestamp with timezone. Does not support time and without timezone.
| string, can also correspond to Iceberg uuid type.
| binary, can also correspond to Iceberg fixed type.
​
nested_type
: struct
| list
| map

transform
: identity, supports any type, DLC does not support this transformation.
| bucket[N], hash mod N bucketing, supports col_type: int,long, decimal, date, timestamp, string, binary
| truncate[L], L-truncation bucketing, supports col_type: int,long,decimal,string
| years, year, supports col_type: date,timestamp
| months, month, supports col_type: date,timestamp
| days/date, date, supports col_type: date,timestamp
| hours/date_hour, hour, supports col_type: timestamp

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

CREATE TABLE [ IF NOT EXISTS ] table_identifier
    [ COMMENT table_comment ]
    [ PARTITIONED BY ( col_name1, transform(col_name2), ... ) ]
    [ TBLPROPERTIES ( property_name=property_value, ... ) ]
AS select_statement

CREATE TABLE dempts_copy
COMMENT 'table create as select' 
PARTITIONED BY (eno, dno) 
AS SELECT * from dempts;

CREATE [OR REPLACE] TABLE table_identifier
    [ COMMENT table_comment ]
    [ PARTITIONED BY ( col_name1, transform(col_name2), ... ) ]
AS select_statement

CREATE OR REPLACE TABLE dempts_replace
    COMMENT 'table create as replace' 
    PARTITIONED BY (eno, dno) 
AS SELECT * from dempts;

DROP TABLE [ IF EXISTS ] table_identifier

ALTER TABLE table_identifier RENAME TO new_table_identifier

-- Update attribute configuration through SET.
ALTER TABLE table_identifier 
SET TBLPROPERTIES (property_name=property_value, ...)

-- Delete attribute configuration through UNSET.
ALTER TABLE table_identifier 
UNSET TBLPROPERTIES (property_name, ...)

-- Update attribute configuration through SET.
ALTER TABLE dempts SET TBLPROPERTIES ('read.split.target-size'='268435456');

-- Delete attribute configuration through UNSET.
ALTER TABLE dempts UNSET TBLPROPERTIES ('read.split.target-size'='268435456');


ALTER TABLE table_identifier 
WRITE [LOCALLY] ORDERED BY 
{col_name [ASC|DESC] [NULLS FIRST|LAST]}[, ...]

ALTER TABLE dempts WRITE ORDERED BY category, id;

-- use optional ASC/DEC keyword to specify sort order of each field (default ASC)
ALTER TABLE dempts WRITE ORDERED BY category ASC, id DESC;

-- use optional NULLS FIRST/NULLS LAST keyword to specify null order of each field (default FIRST)
ALTER TABLE dempts WRITE ORDERED BY category ASC NULLS LAST, id DESC NULLS FIRST;

-- To order within each task, not across tasks
ALTER TABLE dempts WRITE LOCALLY ORDERED BY category, id;

ALTER TABLE table_identifier 
WRITE DISTRIBUTED BY PARTITION 
[ LOCALLY ORDERED BY 
{col_name [ASC|DESC] [NULLS FIRST|LAST]}[, ...]] 

ALTER TABLE dempts WRITE DISTRIBUTED BY PARTITION;
ALTER TABLE dempts WRITE DISTRIBUTED BY PARTITION LOCALLY ORDERED BY id;

-- Add multiple fields.
ALTER TABLE table_identifier 
ADD COLUMNS (col_name col_type [COMMENT col_comment], ...)

-- Add a single field.
ALTER TABLE table_identifier 
ADD COLUMN col_name col_type [COMMENT col_comment] 
[FIRST | AFTER target_col_name]

-- Add multiple fields.
ALTER TABLE dempts
ADD COLUMNS (
    new_column_1 string comment 'new_column_1 docs',
    new_column_2 int comment 'new_column_2 docs'
);

-- Add a single field.
ALTER TABLE dempts 
ADD COLUMN new_column_3 string comment 'new_column docs';

ALTER TABLE table_identifier 
RENAME COLUMN old_column_name TO new_column_name

ALTER TABLE table_identifier 
ALTER COLUMN col_name 
{TYPE new_col_type | COMMENT col_comment}

ALTER TABLE dempts ALTER COLUMN new_column_2 TYPE bigint;
ALTER TABLE dempts ALTER COLUMN new_column_2 comment 'alter docs';

ALTER TABLE table_identifier  DROP COLUMN column_name

ALTER TABLE table_identifier 
ADD PARTITION FIELD col_name|transform (col_name) [AS alias]

ALTER TABLE dempts ADD PARTITION FIELD new_column_1;
ALTER TABLE dempts ADD PARTITION FIELD bucket(3,new_column_2);

ALTER TABLE table_identifier 
DROP PARTITION FIELD col_name|transform (col_name);

ALTER TABLE dempts DROP PARTITION FIELD new_column_1;
ALTER TABLE dempts DROP PARTITION FIELD bucket(3,new_column_2);

ALTER TABLE dempts SET IDENTIFIER FIELDS empno, name


ALTER TABLE dempts DROP IDENTIFIER FIELDS empno, name