Create partitioned table as select feature

You can create a partitioned Iceberg table by selecting another table. You see an example of how to use PARTITIONED BY and TBLPROPERTIES to declare the partition spec and table properties for the new table.

You see an example of using a partition transform with the PARTITIONED BY SPEC clause.

The newly created table does not inherit the partition spec and table properties from the source table in SELECT. The Iceberg table and the corresponding Hive table is created at the beginning of the query execution. The data is inserted / committed when the query finishes. So for a transient period the table exists but contains no data.

Hive syntax

CREATE [EXTERNAL] TABLE prod.db.sample
    USING iceberg
    PARTITIONED BY (part)
    TBLPROPERTIES ('key'='value')
    AS SELECT ...

Hive examples

CREATE EXTERNAL TABLE ctas STORED BY ICEBERG AS SELECT i, t, j FROM ice_1;

CREATE EXTERNAL TABLE ctas_part PARTITIONED BY(z) STORED BY ICEBERG TBLPROPERTIES ('format-version'='2')
AS SELECT x, ts, z FROM t;

CREATE EXTERNAL TABLE ctas_part_spec PARTITIONED BY SPEC (month(d)) STORED BY ICEBERG TBLPROPERTIES ('format-version'='2')
AS SELECT x, ts, d FROM source_t;

Impala examples

CREATE TABLE ctas STORED BY ICEBERG AS SELECT i, b FROM ice_11;

CREATE TABLE ctas_part PARTITIONED BY(b) STORED BY ICEBERG AS SELECT i, s, b FROM ice_11;

CREATE TABLE ctas_part_spec PARTITIONED BY SPEC (month(d)) STORED BY ICEBERG TBLPROPERTIES ('format-version'='2')
AS SELECT x, ts, d FROM source_t;