创建和管理表
创建表
CREATE TABLE命令创建一个表,创建表时可以定义以下内容:
- 表的列及数据类型。
- 表分布的定义,即表的分布策略,它决定DataArtsFabric SQL数据库如何在片(Segment)之间划分数据。 表存储格式。
- 分区表定义。
示例:CREATE TABLE创建了一个表web_returns_p1,并以ORC文件格式存储数据。
1 2 3 4 5 6 7 |
CREATE TABLE web_returns_p1 ( wr_returned_date_sk integer, wr_returned_time_sk integer, wr_item_sk integer, wr_refunded_customer_sk integer ) store as ORC; |
表类型
DataArtsFabric SQL支持两种表类型:托管表(Managed Table) 和外表(External Table)。
类型 |
描述 |
适用场景 |
---|---|---|
托管表(Managed Table) |
数据和元数据均由DataArtsFabric SQL管理,用户可通过DataArtsFabric SQL管理元数据及数据文件。删除表时,表的数据和元数据均会被删除。表的数据路径必须为并行文件系统。 |
|
外表(External Table) |
DataArtsFabric SQL通过元数据或表模式读取指定位置的数据文件,用户不可通过DataArtsFabric SQL变更实际数据文件。删除表时,仅删除表的元数据,数据文件不会受到影响。 |
|

对于托管表(Managed Table),数据文件仍存储在常规的文件系统(OBS并行文件系统)中,用户可以在不告知DataArtsFabric SQL的情况下对其进行变更。如果用户这样做,则违反了DataArtsFabric SQL对于托管表的期望和约定,可能会造成未定义的错误。
-- 创建Schema CREATE SCHEMA sales_schema WITH LOCATION 'obs://bucket/catalog1/sales_schema/'; -- 创建托管表 CREATE TABLE region_sales_info(id int, item varchar(128), sale_date date) partition by (region varchar(128)) store as parquet; -- 创建外表指向托管表 CREATE EXTERNAL TABLE readonly_region_sales_info(id int, item varchar(128), sale_date date) partition by (region varchar(128)) store as parquet location 'obs://bucket/catalog1/sales_schema/region_sales_info/'; -- 由托管表导入业务数据 INSERT INTO region_sales_info VALUES (0, 'apple', '2001-01-01', 'Region A'), (1, 'banana', '2001-01-02', 'Region B'), (2, 'carrot', '2001-01-03', 'Region C'), (3, 'desk', '2001-01-04', 'Region D'); SELECT * FROM region_sales_info order by 1; id | item | sale_date | region ----+--------+------------+---------- 0 | apple | 2001-01-01 | Region A 1 | banana | 2001-01-02 | Region B 2 | carrot | 2001-01-03 | Region C 3 | desk | 2001-01-04 | Region D (4 rows) -- 外表初始无分区信息,无法查询到数据 SELECT * FROM readonly_region_sales_info; id | item | sale_date | region ----+------+-----------+-------- (0 rows) -- 修复外表分区信息 MSCK REPAIR TABLE readonly_region_sales_info; SELECT * FROM readonly_region_sales_info order by 1; id | item | sale_date | region ----+--------+------------+---------- 0 | apple | 2001-01-01 | Region A 1 | banana | 2001-01-02 | Region B 2 | carrot | 2001-01-03 | Region C 3 | desk | 2001-01-04 | Region D (4 rows) -- 删除外表 不会影响数据文件 DROP TABLE readonly_region_sales_info; SELECT COUNT(1) from region_sales_info; count ------- 4 (1 row) -- 删除托管表,数据同时被删除 DROP TABLE region_sales_info;
表分布的定义
策略 |
描述 |
适用场景 |
优势与劣势 |
---|---|---|---|
哈希表(Hash) |
数据以hash方式组织到n个桶(文件)中。 |
数据量较大的事实表。 |
|
轮询表(Roundrobin) |
数据随机地组织到多个文件中。 |
数据量较大的事实表,且使用Hash分布时找不到合适的分布列。 |
|
选择分布列
采用Hash分布方式,需要为用户表指定一个分布列(distribute key)。当插入一条记录时,系统会根据分布列的值进行hash运算后,将数据存储在对应的DN中。
所以Hash分布列选取至关重要,需要满足以下原则:
- 列值应比较离散。例如,考虑选择表的主键为分布列,如在人员信息表中选择身份证号码为分布列。
- 在满足第一条原则的情况下尽量不要选取存在常量filter的列。例如,表dwcjk相关的部分查询中出现dwcjk的列zqdh存在常量的约束(例如zqdh=’000001’),那么就应当尽量不用zqdh做分布列。
- 在满足前两条原则的情况,考虑选择查询中的连接条件为分布列,以便Join任务能够下推到DN中执行,且减少DN之间的通信数据量。
对于Hash分表策略,如果分布列选择不当,可能导致数据倾斜,查询时出现部分DN的I/O短板,从而影响整体查询性能。因此在采用Hash分表策略之后需对表的数据进行数据倾斜性检查,以确保数据在各个DN上是均匀分布的。
查看表数据
- 查询当前Schema下所有表的信息。
1
SHOW TABLES;
- 查询表的属性。
1
DESCRIBE web_returns_p1;
- 执行如下命令查询表web_returns_p1的数据量。
1
SELECT count(*) FROM web_returns_p1;
- 执行如下命令查询表web_returns_p1的所有数据。
1
SELECT * FROM web_returns_p1;
- 执行如下命令只查询字段c_customer_sk的数据。
1
SELECT c_customer_sk FROM web_returns_p1;
- 执行如下命令过滤字段c_customer_sk的重复数据。
1
SELECT DISTINCT( c_customer_sk ) FROM web_returns_p1;
- 执行如下命令查询字段c_customer_sk为3869的所有数据。
1
SELECT * FROM web_returns_p1 WHERE c_customer_sk = 3869;
- 执行如下命令按照字段c_customer_sk进行排序。
1
SELECT * FROM web_returns_p1 ORDER BY c_customer_sk;
删除表数据
在使用表的过程中,可能需要删除表数据。
- 当前不支持通过DELETE命令删除数据。
- 如果执行如下命令,会删除表中所有的行。
1
TRUNCATE TABLE customer_t1;
- 删除创建的表。
1
DROP TABLE customer_t1;