目錄
一、建立文法
文法
描述
1. column_definition
2. ENGINE 類型
3. key_desc
4. partition_desc
5. distribution_desc
6. PROPERTIES
二、表建立執行個體
1. 建立一個 olap 表,使用 HASH 分桶,使用列存,相同key的記錄進行聚合
2. 建立一個 olap 表,使用 Hash 分桶,使用列存,相同key的記錄進行覆寫設定初始存儲媒體和冷卻時間
3. 建立一個 olap 表,使用 Key Range 分區,使用Hash分桶,預設使用列存, 相同key的記錄同時存在,設定初始存儲媒體和冷卻時間
4. 建立一個 mysql 表
5. 建立一個資料檔案存儲在HDFS上的 broker 外部表, 資料使用 "|" 分割,"\n" 換行
6. 建立一張含有HLL列的表
使用Mysql Client 運作 HELP CREATE TABLE 做的筆記。
一、建立文法
文法
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [database.]table_name
(column_definition1[, column_definition2, ...])
[ENGINE = [olap|mysql|broker]]
[key_desc]
[partition_desc]
[distribution_desc]
[PROPERTIES ("key"="value", ...)];
[BROKER PROPERTIES ("key"="value", ...)];
描述
1. column_definition
表字段。
文法:
col_name col_type [agg_type] [NULL | NOT NULL] [DEFAULT "default_value"]
說明:
col_name:列名稱
col_type:列類型
TINYINT(1位元組)
範圍:-2^7 + 1 ~ 2^7 - 1
SMALLINT(2位元組)
範圍:-2^15 + 1 ~ 2^15 - 1
INT(4位元組)
範圍:-2^31 + 1 ~ 2^31 - 1
BIGINT(8位元組)
範圍:-2^63 + 1 ~ 2^63 - 1
LARGEINT(16位元組)
範圍:0 ~ 2^127 - 1
FLOAT(4位元組)
支援科學計數法
DOUBLE(12位元組)
支援科學計數法
DECIMAL[(precision, scale)] (40位元組)
保證精度的小數類型。預設是 DECIMAL(10, 0)
precision: 1 ~ 27
scale: 0 ~ 9
其中整數部分為 1 ~ 18
不支援科學計數法
DATE(3位元組)
範圍:1900-01-01 ~ 9999-12-31
DATETIME(8位元組)
範圍:1900-01-01 00:00:00 ~ 9999-12-31 23:59:59
CHAR[(length)]
定長字元串。長度範圍:1 ~ 255。預設為1
VARCHAR[(length)]
變長字元串。長度範圍:1 ~ 65533
HLL (1~16385個位元組)
hll列類型,不需要指定長度和預設值、長度根據資料的聚合
程度系統内控制,并且HLL列隻能通過配套的hll_union_agg、Hll_cardinality、hll_hash進行查詢或使用
agg_type:聚合類型
- 如果不指定,則該列為 key 列。否則,該列為 value 列
- SUM、MAX、MIN、REPLACE、HLL_UNION(僅用于HLL列,為HLL獨有的聚合方式)
- 該類型隻對聚合模型(key_desc的type為AGGREGATE KEY)有用,其它模型不需要指定這個。
是否允許為NULL: 預設不允許為 NULL。NULL 值在導入資料中用 \N 來表示
2. ENGINE 類型
指定表類型,預設為 olap。可選 mysql, broker。
1) 如果是 mysql,則需要在 properties 提供以下資訊:
PROPERTIES (
"host" = "mysql_server_host",
"port" = "mysql_server_port",
"user" = "your_user_name",
"password" = "your_password",
"database" = "database_name",
"table" = "table_name"
)
- "table" 條目中的 "table_name" 是 mysql 中的真實表名。而 CREATE TABLE 語句中的 table_name 是該 mysql 表在 Palo 中的名字,可以不同。
- 在 Palo 建立 mysql 表的目的是可以通過 Palo 通路 mysql 資料庫。而 Palo 本身并不維護、存儲任何 mysql 資料。
2) 如果是 broker,表示表的通路需要通過指定的broker, 需要在 properties 提供以下資訊:
PROPERTIES (
"broker_name" = "broker_name",
"paths" = "file_path1[,file_path2]",
"column_separator" = "value_separator"
"line_delimiter" = "value_delimiter"
)
另外還需要提供Broker需要的Property資訊,通過BROKER PROPERTIES來傳遞,例如HDFS需要傳入
BROKER PROPERTIES(
"username" = "name",
"password" = "password"
)
- 這個根據不同的Broker類型,需要傳入的内容也不相同
- "paths" 中如果有多個檔案,用逗号[,]分割
- 如果檔案名中包含逗号,那麼使用 %2c 來替代
- 如果檔案名中包含 %,使用 %25 代替
- 現在檔案内容格式支援CSV,支援GZ,BZ2,LZ4,LZO(LZOP) 壓縮格式。
3. key_desc
指定資料模型,資料模型概念。
文法:
key_type(k1[,k2 ...])
資料按照指定的key列進行排序,且根據不同的key_type具有不同特性。
key_type支援一下類型:
- AGGREGATE KEY :key列相同的記錄,value列按照指定的聚合類型進行聚合,适合報表、多元分析等業務場景。
- UNIQUE KEY:key列相同的記錄,value列按導入順序進行覆寫,适合按key列進行增删改查的點查詢業務。
- DUPLICATE KEY:key列相同的記錄,同時存在于Palo中,适合存儲明細資料或者資料無聚合特性的業務場景。
注意: 除AGGREGATE KEY外,其他key_type在建表時,value列不需要指定聚合類型。
4. partition_desc
指定分區。
1) Range 分區
文法:
PARTITION BY RANGE (k1)
(
PARTITION partition_name VALUES LESS THAN MAXVALUE|("value1")
PARTITION partition_name VALUES LESS THAN MAXVALUE|("value2")
...
)
說明:
使用指定的 key 列和指定的數值範圍進行分區。
1) 分區名稱僅支援字母開頭,字母、數字和下劃線組成
2) 目前僅支援以下類型的列作為 Range 分區列,且隻能指定一個分區列
TINYINT, SMALLINT, INT, BIGINT, LARGEINT, DATE, DATETIME
3) 分區為左閉右開區間,首個分區的左邊界為做最小值
4) NULL 值隻會存放在包含最小值的分區中。當包含最小值的分區被删除後,NULL 值将無法導入。
注意:
1) 分區一般用于時間次元的資料管理
2) 有資料回溯需求的,可以考慮首個分區為空分區,以便後續增加分區
5. distribution_desc
Hash 分桶
文法:
DISTRIBUTED BY HASH (k1[,k2 ...]) [BUCKETS num]
說明:
使用指定的 key 列進行哈希分桶。預設分區數為10
建議:建議使用Hash分桶方式
6. PROPERTIES
1) 如果 ENGINE 類型為 olap,則可以在 properties 中指定列存(目前僅支援列存)
PROPERTIES (
"storage_type" = "[column]",
)
2) 如果 ENGINE 類型為 olap,可以在 properties 設定該表資料的初始存儲媒體、存儲到期時間和副本數。
PROPERTIES (
"storage_medium" = "[SSD|HDD]",
["storage_cooldown_time" = "yyyy-MM-dd HH:mm:ss"],
["replication_num" = "3"]
)
- storage_medium: 用于指定該分區的初始存儲媒體,可選擇 SSD 或 HDD。預設為 HDD。
- storage_cooldown_time: 當設定存儲媒體為 SSD 時,指定該分區在 SSD 上的存儲到期時間。預設存放 7 天。格式為:"yyyy-MM-dd HH:mm:ss"。
- replication_num: 指定分區的副本數。預設為 3。
- 當表為單分區表時,這些屬性為表的屬性。
- 當表為兩級分區時,這些屬性為附屬于每一個分區。
- 如果希望不同分區有不同屬性。可以通過 ADD PARTITION 或 MODIFY PARTITION 進行操作
3) 如果 Engine 類型為 olap, 并且 storage_type 為 column, 可以指定某列使用 bloom filter 索引
bloom filter 索引僅适用于查詢條件為 in 和 equal 的情況,該列的值越分散效果越好
目前隻支援以下情況的列:除了 TINYINT FLOAT DOUBLE 類型以外的 key 列及聚合方法為 REPLACE 的 value 列
PROPERTIES (
"bloom_filter_columns"="k1,k2,k3"
)
4) 如果希望使用Colocate Join 特性,需要在 properties 中指定
PROPERTIES (
"colocate_with"="table1"
)
二、表建立執行個體
1. 建立一個 olap 表,使用 HASH 分桶,使用列存,相同key的記錄進行聚合
CREATE TABLE example_db.table_hash
(
k1 TINYINT,
k2 DECIMAL(10, 2) DEFAULT "10.5",
v1 CHAR(10) REPLACE,
v2 INT SUM
)
ENGINE=olap
AGGREGATE KEY(k1, k2)
DISTRIBUTED BY HASH(k1) BUCKETS 32
PROPERTIES ("storage_type"="column");
2. 建立一個 olap 表,使用 Hash 分桶,使用列存,相同key的記錄進行覆寫設定初始存儲媒體和冷卻時間
CREATE TABLE example_db.table_hash
(
k1 BIGINT,
k2 LARGEINT,
v1 VARCHAR(2048) REPLACE,
v2 SMALLINT SUM DEFAULT "10"
)
ENGINE=olap
UNIQUE KEY(k1, k2)
DISTRIBUTED BY HASH (k1, k2) BUCKETS 32
PROPERTIES(
"storage_type"="column",
"storage_medium" = "SSD",
"storage_cooldown_time" = "2015-06-04 00:00:00"
);
3. 建立一個 olap 表,使用 Key Range 分區,使用Hash分桶,預設使用列存,
相同key的記錄同時存在,設定初始存儲媒體和冷卻時間
CREATE TABLE example_db.table_range
(
k1 DATE,
k2 INT,
k3 SMALLINT,
v1 VARCHAR(2048),
v2 DATETIME DEFAULT "2014-02-04 15:36:00"
)
ENGINE=olap
DUPLICATE KEY(k1, k2, k3)
PARTITION BY RANGE (k1)
(
PARTITION p1 VALUES LESS THAN ("2014-01-01"),
PARTITION p2 VALUES LESS THAN ("2014-06-01"),
PARTITION p3 VALUES LESS THAN ("2014-12-01")
)
DISTRIBUTED BY HASH(k2) BUCKETS 32
PROPERTIES(
"storage_medium" = "SSD", "storage_cooldown_time" = "2015-06-04 00:00:00"
);
說明:
這個語句會将資料劃分成如下3個分區:
( { MIN }, {"2014-01-01"} )
[ {"2014-01-01"}, {"2014-06-01"} )
[ {"2014-06-01"}, {"2014-12-01"} )
不在這些分區範圍内的資料将視為非法資料被過濾
4. 建立一個 mysql 表
CREATE TABLE example_db.table_mysql
(
k1 DATE,
k2 INT,
k3 SMALLINT,
k4 VARCHAR(2048),
k5 DATETIME
)
ENGINE=mysql
PROPERTIES
(
"host" = "127.0.0.1",
"port" = "8239",
"user" = "mysql_user",
"password" = "mysql_passwd",
"database" = "mysql_db_test",
"table" = "mysql_table_test"
)
5. 建立一個資料檔案存儲在HDFS上的 broker 外部表, 資料使用 "|" 分割,"\n" 換行
CREATE EXTERNAL TABLE example_db.table_broker (
k1 DATE,
k2 INT,
k3 SMALLINT,
k4 VARCHAR(2048),
k5 DATETIME
)
ENGINE=broker
PROPERTIES (
"broker_name" = "hdfs",
"path" = "hdfs://hdfs_host:hdfs_port/data1,hdfs://hdfs_host:hdfs_port/data2,hdfs://hdfs_host:hdfs_port/data3%2c4",
"column_separator" = "|",
"line_delimiter" = "\n"
)
BROKER PROPERTIES (
"username" = "hdfs_user",
"password" = "hdfs_password"
)
6. 建立一張含有HLL列的表
CREATE TABLE example_db.example_table
(
k1 TINYINT,
k2 DECIMAL(10, 2) DEFAULT "10.5",
v1 HLL HLL_UNION,
v2 HLL HLL_UNION
)
ENGINE=olap
AGGREGATE KEY(k1, k2)
DISTRIBUTED BY HASH(k1) BUCKETS 32
PROPERTIES ("storage_type"="column");