TABLE

SHOW TABLES

获取指定Schema下的Table列表。

• 语法说明

SHOW TABLES FROM <catalogPath>.<schemaPath>

• 使用示例

show tables from air_demo.tpcds;

SHOW TABLE PROPERTIES

查看表的属性信息，返回该表的基本信息列表

• 语法说明

SHOW TABLE PROPERTIES <catalogName>.<schemaName>.<tableName>

• 使用示例

show table properties "catalog"."schema"."tableName"

• 返回结果

property |     value 
---------+-----------
   aaa   |    bbb

DESCRIBE TABLE

查看表的描述，索引，外键等基本信息。AIR 2.7 版本后支持

• 语法说明

DESCRIBE TABLE <catalogName>.<schemaName>.<tableName>

• 使用示例

DESCRIBE TABLE "catalog"."schema"."tableName"

• 返回结果

 Table Info          |   Values 
---------------------+-----------
 Table Comment       | 
 Table Columns       |   
 Table Indexes       |
 Table Foreign Keys  | 

• 返回字段说明

关键字描述	类型	说明
Table Comment	Varchar	表的描述信息。
Table Columns	JSON 字符串	表的列信息列表描述。
Table Indexes	JSON 字符串	表的索引信息描述。
Table Foreign Keys	JSON 字符串	表的外键信息描述。

SHOW TABLE INDEX

查看表的索引信息，返回该表的索引信息列表。AIR 2.7 版本后支持

• 语法说明

SHOW (INDEX | INDEXES ) FFROM <catalogName>.<schemaName>.<tableName>

• 使用示例

show indexes from "mysql"."schema"."tableName";
show index   from "mysql"."schema"."tableName";

• 返回结果

       Index Name         |   Columns    | Index Type | Index Order | Is Unique | Index Comment 
--------------------------+--------------+------------+-------------+-----------+---------------
 PRIMARY                  | [col1]       | HASH       | ASC         | false     | comment 1          
 a_uindex                 | [col2]       | HASH       | ASC         | false     | comment 2          
 b_pk                     | [col2, col1] | HASH       | ASC         | false     | comment 3          

• 返回字段说明

列名	类型	说明
Index Name	Varchar	索引名称。
Columns	Varchar	索引包含的列集合。
Index Type	Varchar	索引类型。索引类型包括说明：STATISTIC： 静态索引CLUSTER：聚合索引HASH：哈希索引OTHER： 其他索引
Index Order	Varchar	索引排序规则。ASC: 升序排序DESC：降序排序
Is Unique	Boolean	是否为唯一索引。
Index Comment	Varchar	索引注释。

SHOW TABLE FOREIGN KEYS

查看表的外键信息列表。AIR 2.7 版本后支持

• 语法说明

SHOW (FOREIGN KEYS | FOREIGN KEY | FK ) FROM <catalogName>.<schemaName>.<tableName>

• 使用示例

show foreign keys from "mysql"."schema"."tableName";
show foreign key from "mysql"."schema"."tableName";
show fk from "mysql"."schema"."tableName";

• 返回结果

 Key Name | Column Name |     Reference Table     | Reference Column 
----------+-------------+-------------------------+------------------
 cip_user | col2        | [mysql, cing, cip_user] | id               
 qaz      | col2        | [mysql, cing, qaz]      | asdsa            

• 返回字段说明

列名	类型	说明
Key Name	Varchar	外键名称。
Column Name	Varchar	该表的外键列名称。
Reference Table	Varchar	外键参考的其他的表路径。表路径的第一个值为catalog名称,最后一个值为table名称，其余的部分为schema 名称。
Reference Column	Varchar	外键参考的表列名称。

SET TABLE PROPERTIES

设置表的属性信息。

• 语法说明

ALTER TABLE <catalog>.<schema>.<tableName> SET TABLE PROPERTIES ('key1'='value1', 'key2'='value2', ...);

• 使用示例

ALTER TABLE postgres.public.tb_order 
SET TABLE PROPERTIES 
(
  'partitionColumn':'id',
  'numPartitions':10 -- numPartitions 用于设置Spark爬取数据时的并发数量
); 

• 返回结果

property |     value 
---------+-----------
         |

• 给PDS增加拉取数据分片，提升查询、构建时远程拉取数据的并行度，（注意：需选择一个数值类型的字段 ）

ALTER TABLE mysql_demo.tpcds.reason SET TABLE PROPERTIES ('partitionColumn':'id',  -- 分片的字段
'numPartitions':'20'); -- 分片数量

TABLE PROJECTION

为表设置投影更新配置,包含PDS的调度信息。

• 语法说明

ALTER TABLE <tableName> 
SET PROJECTION PROPERTIES
(
  'refresh_mode' = 'On_Demand',
  'refresh_type' = 'Complete',
  'active_interval' = 'Infinite'
);

• 使用示例

ALTER TABLE hive2.tpcds_3g_parquet_partition.store_sales_par
SET PROJECTION PROPERTIES 
  (
    'REFRESH_MODE':'ON_DEMAND',
    'REFRESH_TYPE':'Incrementally(ss_sold_date_sk)',
    'ACTIVE_INTERVAL':'INFINITE',
    'SCHEDULE_PERIOD':'DAY'
  );

• 表属性值说明

关键字	说明
refresh_model	更新模式：On_Schedule，Every $XXX Hour/Day/Month ，周期更新On_Demand（默认），按需更新
refresh_type	更新类型Complete（默认），全量更新Incrementally($column) ，按字段增量更新
active_interval	数据有效期限Infinite（默认）,永不失效finite $XXX Hour/Day/Month，有效期限

REFRESH TABLE METADATA

刷新Table的元数据信息。AIR 2.6 版本后支持

• 语法说明

ALTER TABLE <catalogPath>.<schemaPath>.<tablePath>
REFRESH METADATA [FORCE]

• 参数说明

关键字	类型	说明
catalogPath	SQL标识符，必填	表所在的catalog的名称
schemaPath	SQL标识符，必填	表所在的schema的名称
tablePath	SQL标识符，必填	表的名称
FORCE	SQL 关键字，选填	是否强制刷新

• 使用示例

alter table "hive2"."tpcds_3g_parquet_partition"."store_sales_par" refresh metadata force;  --force是强制刷新

alter table "hive2"."tpcds_3g_parquet_partition"."store_sales_par" refresh metadata;  --非强制刷新。

CREATE TABLE

新建表。建表并且导入数据请参考 Create Table AS 语法。

• 语法说明

CREATE TABLE [ IF NOT EXISTS ]
<catalogPath>.<schemaPath>.<tablePath> (
  { <columnName> <dataType> [NOT NULL] [ COMMENT <comment> ] }
  [, ...]
)
[ COMMENT tableComment ]
[ WITH ( propertyName = expression [, ...] ) ]

• 参数说明

关键字	类型	说明
catalogPath	SQL标识符，必填	表所在的catalog的名称
schemaPath	SQL标识符，必填	表所在的schema的名称
tablePath	SQL标识符，必填	表的名称
columnName	SQL标识符，必填	表字段名字
dataType	SQL标识符，必填	字段类型
NOT NULL	SQL 关键字，选填	字段是否可为空
COMMENT	SQL 关键字，选填	表示设置字段注释信息
comment	Varchar，COMMENT 关键字存在是必填	注释信息
WITH	SQL 关键字，选填	表示添加表额外配置信息
propertyName，expression	选填	表额外配置信息，propertyName 和 expression 均无需使用双引号。
tableComment	Varchar，选填	表注释信息

• 使用示例

-- 设置字段 NOT NULL，和注释信息
CREATE TABLE catalog.schema.table_name(
    col1 varchar,
    col2 integer NOT NULL COMMENT 'col2 comment'
)

-- 设置表注释信息
CREATE TABLE catalog.schema.table_name(
    col1 varchar(50),
    col2 integer NOT NULL COMMENT 'col2 comment'
)
COMMENT 'A table to keep track of orders.'

-- 创建分区表
CREATE TABLE catalog.schema.table_name(
    col1 varchar, col2 integer NOT NULL COMMENT 'col2 comment'
)
WITH (
  partition = column_name
)
-- 创建表时指定分区字段
CREATE TABLE catalog.schema.table_name(
    col1 varchar,
    col2 integer NOT NULL COMMENT 'col2 comment'
)
WITH (
  partition = col1,
  property_name02 = property_value02
)

CREATE TABLE AS

创建表并插入数据。此语法不支持指定字段类型和添加字段注释。

• 语法说明

CREATE TABLE [ IF NOT EXISTS ] <catalogPath>.<schemaPath>.<tablePath>
  [ ( <columnAlias>, ... ) ]
[ COMMENT tableComment ]
[ WITH ( propertyName = expression [, ...] ) ]
AS {query}

• 参数说明

关键字	类型	说明
catalogPath	SQL标识符，必填	表所在的catalog的名称
schemaPath	SQL标识符，必填	表所在的schema的名称
tablePath	SQL标识符，必填	表的名称
columnAlias	SQL标识符，必填	表字段别名
COMMENT	SQL 关键字，选填	表示设置字段注释信息
tableComment	Varchar，选填	表注释信息
WITH	SQL 关键字，选填	表示添加表额外配置信息
propertyName，expression	选填	表额外配置信息，propertyName 和 expression 均无需使用双引号。
query		查询语句

• 使用示例

-- 创建表并插入数据
CREATE TABLE catalog.schema.table_name AS select * from c.s.t group by c1 order by c2 limit 100

INSERT INTO

• 语法说明

INSERT INTO table_name [ ( column [, ... ] ) ] query;

INSERT INTO table_name ( column [, ... ] ) VALUES(value1, value2, value3, ...)

-- OverWrite 需要在 SQL 最前面增加以下 Hints
/*+ context(InsertType=overwrite) */
INSERT INTO table_name select * xxxx;


-- 分区插入需要在 SQL 最前面增加
/*+ context(InsertIntoPartition=columnName) */
INSERT INTO table_name select * xxxx;

• 使用示例

-- 全部字段直接插入数据
INSERT INTO catalog.schema.table_name VALUES (
  col1_value, col2_value, col3_value,... 
), (
  col1_value, col2_value, col3_value,... 
);
-- 指定字段插入数据
INSERT INTO catalog.schema.table_name (col1, col2) VALUES (
  col1_value, col2_value
), (
  col1_value, col2_value
);

-- 使用查询语句插入数据
INSERT INTO catalog.schema.table_name AS SELECT * FROM catalog.schema.other_table_name;

-- 指定列插入数据
CREATE TABLE catalog.schema.table_name(
    col1, col2
) AS 
SELECT table_col1 AS col1, table_col2 AS col2 
FROM catalog.schema.other_table_name;

ALTER TABLE

• 语法说明

修改表元数据。

-- 修改表名
ALTER TABLE [ IF EXISTS ] <tablePath> RENAME TO new_name

-- 新增列
ALTER TABLE [ IF EXISTS ] <tablePath> ADD COLUMN [ IF NOT EXISTS ] column_name data_type [ COMMENT comment ] [ WITH ( property_name = expression [, ...] ) ]

-- 修改列
ALTER TABLE [ IF EXISTS ] <tablePath> ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL

-- 删除列
ALTER TABLE [ IF EXISTS ] <tablePath> DROP COLUMN column_name
ALTER TABLE [ IF EXISTS ] <tablePath> RENAME COLUMN [ IF EXISTS ] column_name TO new_column_name

-- 添加列约束条件
ALTER TABLE [ IF EXISTS ] <tablePath> ADD [ CONSTRAINT constraint_name ] { PRIMARY KEY | UNIQUE } ( { column_name [, ...] } ) [ { ENABLED | DISABLED } ] [ [ NOT ] RELY ] [ [ NOT ] ENFORCED } ]

-- 删除列约束条件
ALTER TABLE [ IF EXISTS ] <tablePath> DROP CONSTRAINT [ IF EXISTS ] constraint_name

• 使用示例

-- 修改表名
ALTER TABLE IF EXISTS catalog.schema.name RENAME TO newCatalog.newSchema.newName;

-- 添加列
ALTER TABLE catalog.schema.name ADD COLUMN zip VARCHAR;

ALTER TABLE IF EXISTS catalog.schema.name ADD COLUMN IF NOT EXISTS zip varchar COMMENT 'this is zip comment' WITH (property_name=property_value);

-- 修改列
ALTER TABLE IF EXISTS catalog.schema.name ALTER COLUMN column_name SET NOT NULL;

-- 删除列
ALTER TABLE IF EXISTS catalog.schema.name DROP COLUMN IF EXISTS column_name;

-- 添加列约束条件
ALTER TABLE IF EXISTS catalog.schema.name ADD CONSTRAINT constraint_name  PRIMARY  ( col1, col2, col3 );

-- 删除列约束条件
ALTER TABLE IF EXISTS catalog.schema.name DROP CONSTRAINT IF EXISTS constraint_name

ALTER TABLE DROP PARTITION

删除表的分区。

• 语法说明

ALTER TABLE <tablePath>
DROP 
  PARTITION
  (
    <columnName='partition_value' 
    [, <columnName='partition_value']...
  ), 
  [,
    PARTITION
    (
      <columnName='partition_value' 
      [, <columnName='partition_value']...
    )
  ]

• 使用示例

-- 单分区表删除分区
alter table table_name
drop partition(sale_date='201312'), partition(sale_date='201313')

-- 多分区表删除分区
alter table table_name
drop partition(sale_date='201312', region='shanghai'), partition(sale_date='201313', region='beijing')

DROP TABLE

• 语法说明

DROP TABLE [IF EXISTS] <tablePath>

• 使用示例

drop table "catalogName"."schemaName"."tableName";