本文介绍 AnalyticDB MySQL 湖仓版（3.0） Spark SQL的建表语法。

背景信息

AnalyticDB MySQL Spark SQL创建表的语法与开源社区Spark用法存在差异， AnalyticDB MySQL 支持特有的表属性，详情请参见 创建表 。 AnalyticDB MySQL Spark SQL的建库、查询库和查询表等语法与开源社区Spark一致，详情请参见 开源社区Spark SQL参考文档 。

可以通过 AnalyticDB MySQL 的Spark SQL创建和读写C-Store表和Hudi外表。详情请参见 通过Spark SQL读写C-Store表数据 和 通过Spark SQL读写Hudi外表 。

注意事项

不支持通过Spark SQL更新和删除C-Store表数据。

创建表

语法 

CREATE TABLE [ IF NOT EXISTS ] table_identifier
    [ ( column_name column_type [ COMMENT col_comment1 ], ... ) ]
    USING adb
    [ COMMENT table_comment ]
    [ TBLPROPERTIES ( key1=val1, key2=val2, ... ) ]

参数说明

参数 说明
table_identifier 表名。支持 db_name.table_identifier 格式，区分不同数据库下相同名字的表。

表名的命名规则，请参见 命名约束 。

column_name 列名。

列名的命名规则，请参见 命名约束 。

column_type 列的数据类型。

Spark SQL支持的数据类型，请参见 数据类型映射 。

USING adb 指定创建 AnalyticDB MySQL 类型的表。
COMMENT 表注释。
TBLPROPERTIES 定义表属性。支持的表属性，请参见 表属性说明 。

表属性说明

Spark SQL支持的表属性如下。
表 1. 表属性说明
表属性 说明 是否必须
indexName={indexNameValue1,indexNameValue2,...} 设置索引名称，多个索引用英文逗号（,）分隔。示例： 'indexName'='index1,index2' 否
indexType.{indexNameValue}={indexTypeValue} 设置索引类型。支持如下索引类型：
  • FULLTEXT：全文索引
  • NORMAL：普通索引

示例： 'indexType.index1'='FULLTEXT'

否
indexColumn.{indexNameValue}={column_name1,column_name2,...} 设置被索引的列。

示例： 'indexColumn.index1'='c1'

否
indexAll=[Y|N] 设置是否对全部列进行索引。
  • Y（默认值）：是
  • N：否
否
primaryKey={column_name1 [,column_name2...]} 定义主键索引。

主键中必须包含分布键和分区键，建议将分布键和分区键放在组合主键的前部。

否
distributeType=[HASH|BROADCAST] 设置分布键类型，支持HASH或BROADCAST。
  • HASH：在普通表中定义表的分布键，按照所选列的HASH值进行分片。
  • BROADCAST：定义维度表，维度表会在集群的每个节点存储一份数据，因此建议维度表的数据量不宜太大。
是
distributeColumns={column_name1[,column_name2,...]} 定义分布键（一级分区）。

AnalyticDB MySQL版 支持将多个字段作为分布键。

AnalyticDB MySQL版 不支持修改分布键。

是
partitionType=VALUE 设置分区建类型，固定为VALUE。 否
partitionColumn={column_name} 定义分区键（二级分区）。 否
partitionCount={partitionCountValue} 二级分区的生命周期。

当您指定了分区键时，必须定义生命周期 partitionCount ，否则会报错。

例如，当分区的列类型为日期时间类型时， 'partitionCount'='365' 表示每个分片（Shard）最多保留的二级分区个数为365，即如果数据保存天数为365天，则第366天写入数据后，系统会自动删除第1天写入的数据。

否
storagePolicy=[HOT|COLD|MIXED] 指定存储策略。支持如下取值：
  • HOT（默认值）：热存储，所有分区都在SSD。
  • COLD：冷存储，所有分区都在HDD。
  • MIXED：混合存储，部分分区在SSD，部分分区在HDD，需要通过 hotPartitionCount 指定存在SSD上的分区的数量。
说明 HOT、COLD、MIXED大小写兼容。

不同存储策略下数据读写性能不同，存储成本不同。为了降低数据存储成本，同时还要保证查询性能，您可以选择将查询频度高的数据（称为热数据）存储在SSD介质；将查询频度低的数据（称为冷数据）存储在HDD介质。

根据业务需求，您还可以按表粒度、表的二级分区粒度独立选择冷、热存储介质。例如，指定这个表数据全部存储在SSD，或者全部存储在HDD，或者指定这个表的一部分二级分区存储在SSD，另一部分二级分区存储在HDD。

是
hotPartitionCount=N 指定MIXED存储策略时热分区的个数。表示按分区键的值的大小倒序排列，最大N个分区为热分区，其他分区为冷分区。

指定MIXED策略时，必须同时指定热分区的个数；其他策略禁止指定 hotPartitionCount=N 。

说明 N为正整数。
否
tableEngineName=XUANWU 设置存储引擎，默认值为XUANWU。 否

示例

假设已有名为 adb_demo 的数据库。建表示例语句如下： 
USE adb_demo;
CREATE TABLE `test_data__tbl` (
    `k1` int, 
    `k2` string, 
    `part` int) 
using adb 
    TBLPROPERTIES (
    'distributeType' = 'HASH',
    'distributeColumns' = 'part',
    'partitionType' = 'value',
    'partitionColumn' = 'part',
    'partitionCount' = 4,
    'indexAll' = 'N',
    'storagePolicy' = 'COLD'
  );

数据类型映射

Spark SQL列类型 XIHE SQL列类型
BOOLEAN BOOLEAN
TINYINT TINYINT
SMALLINT SMALLINT
INT INT
INTEGER INTEGER
BIGINT BIGINT
FLOAT FLOAT
DOUBLE DOUBLE
BINARY BINARY
DATE DATE
STRING
  • VARCHAR
  • TIME
  • POINT
  • JSON
  • ARRAY
  • MAP
TIMESTAMP
  • DATETIME
  • TIMESTAMP