Spark SQL建表文法介紹 - AnalyticDB

AnalyticDB for MySQL支援使用Spark SQL建立C-Store表（即表引擎是XUANWU的表），本文主要介紹使用Spark SQL建立C-Store表的文法、樣本以及資料類型的映射關係。

背景資訊

AnalyticDB for MySQL Spark SQL建立C-Store表的文法與開源社區Spark SQL的建表文法存在差異，AnalyticDB for MySQL支援特有的表屬性，詳情請參見參數說明。AnalyticDB for MySQL Spark SQL的建庫、查詢庫和查詢表等文法與開源社區Spark一致，詳情請參見開源社區Spark SQL參考文檔。

使用限制

僅支援使用Spark SQL建立表引擎為XUANWU的表。

文法

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 for MySQL類型的表。
COMMENT		是	表注釋。
TBLPROPERTIES	distributeType	是	設定分布鍵類型。取值如下： HASH：在普通表中定義表的分布鍵，按照所選列的HASH值進行分區。 BROADCAST：定義複製表，複製表會在叢集的每個Shard儲存一份資料，因此建議複製表的資料量不宜太大。
	distributeColumns	是	定義分布鍵（一級分區）。支援將多個欄位作為分布鍵，在建表後不支援修改分布鍵。
	storagePolicy	是	指定儲存策略。取值如下： HOT（預設值）：熱儲存。所有資料分區都在SSD盤，滿足高效能訪問的需求，查詢效能最好，但儲存成本最高。 COLD：冷儲存。所有分區都在OSS，採用同城冗餘儲存（多AZ），是一種較為經濟的儲存策略。 MIXED：混合儲存。熱分區資料存放區在SSD盤，冷分區資料存放區在OSS。既保證熱分區資料的查詢效能，又節省冷分區資料的儲存成本。需要通過`hotPartitionCount`指定熱分區的分區數量。說明 HOT、COLD、MIXED大小寫相容。
	hotPartitionCount	是	指定MIXED儲存策略時熱分區的個數。表示按分區鍵的值的大小倒序排列，最大N個分區為熱分區，其他分區為冷分區。指定MIXED策略時，必須同時指定熱分區的個數；其他策略禁止指定`hotPartitionCount=N`。說明 N為正整數。
	indexName	否	設定索引名稱，多個索引用英文逗號（,）分隔。樣本：`'indexName'='index1,index2'`。
	indexType	否	設定索引類型。支援如下索引類型： FULLTEXT：全文索引。 NORMAL：普通索引。樣本：`'indexType.index1'='FULLTEXT'`。
	indexColumn	否	設定被索引的列。樣本：`'indexColumn.index1'='c1'`。
	indexAll	否	設定是否對全部列進行索引。 Y（預設值）：是。 N：否。
	primaryKey	否	定義主鍵索引。主鍵中必須包含分布鍵和分區鍵，建議將分布鍵和分區鍵放在組合主鍵的前部。
	partitionType	否	設定分區鍵類型，固定為VALUE。
	partitionColumn	否	定義分區鍵（二級分區）。
	partitionCount	否	二級分區的生命週期。當您指定了分區鍵時，必須定義生命週期`partitionCount`，否則會報錯。例如，當分區的列類型為日期時間類型時，`'partitionCount'='365'`表示每個分區（Shard）最多保留的二級分區個數為365，即如果資料儲存天數為365天，則第366天寫入資料後，系統會自動刪除第1天寫入的資料。
	tableEngineName	否	設定儲存引擎，預設值為XUANWU。

樣本

建立非分區表

 CREATE TABLE orders (
  order_id BIGINT NOT NULL COMMENT '訂單ID',
  customer_id INT NOT NULL COMMENT '顧客ID',
  order_status STRING NOT NULL COMMENT '訂單狀態',
  total_price BIGINT NOT NULL COMMENT '訂單金額',
  order_date TIMESTAMP NOT NULL COMMENT '訂單日期'
)
using adb 
    TBLPROPERTIES (
    'distributeType' = 'HASH',
    'distributeColumns' = 'order_status',
    'storagePolicy' = 'COLD'
  );

建立分區表並設定生命週期

CREATE TABLE customer (
  customer_id BIGINT NOT NULL COMMENT '顧客ID',
  customer_name STRING NOT NULL COMMENT '顧客姓名',
  phone_num BIGINT NOT NULL COMMENT '電話',
  city_name STRING NOT NULL COMMENT '所屬城市',
  sex INT NOT NULL COMMENT '性別',
  id_number STRING NOT NULL COMMENT '社會安全號碼碼',
  home_address STRING NOT NULL COMMENT '家庭住址',
  office_address STRING NOT NULL COMMENT '辦公地址',
  age INT NOT NULL COMMENT '年齡',
  login_time TIMESTAMP NOT NULL COMMENT '登入時間'
 )
using adb 
    TBLPROPERTIES (
    'distributeType' = 'HASH',
    'primaryKey'= 'customer_id,phone_num,city_name',
    'distributeColumns' = 'city_name',
    'partitionType' = 'value',
    'partitionColumn' = 'city_name',
    'partitionCount' = 100,
    '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

AnalyticDB：Spark SQL建立內表