更新时间:2025-08-25 GMT+08:00

创建和管理表

创建表

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对于托管表的期望和约定,可能会造成未定义的错误。

Managed Table与External Table使用示例
-- 创建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;

表分布的定义

DataArtsFabric SQL支持的分布方式:哈希表(Hash)和轮询表(Roundrobin)。

策略

描述

适用场景

优势与劣势

哈希表(Hash)

数据以hash方式组织到n个桶(文件)中。

数据量较大的事实表。

  • 在读数据时可以有效利用文件hash信息进行剪枝过滤,多表join场景下,相同hash的文件会发送到相同节点执行,加快计算速度。

轮询表(Roundrobin)

数据随机地组织到多个文件中。

数据量较大的事实表,且使用Hash分布时找不到合适的分布列。

  • Roundrobin优点是保证了数据不会发生倾斜,从而提高了集群的空间利用率。
  • 一般在大表无法找到合适的分布列时,定义为Roundrobin表,如果大表能够找到合适的分布列,优先选择性能更好的Hash分布。

选择分布列

采用Hash分布方式,需要为用户表指定一个分布列(distribute key)。当插入一条记录时,系统会根据分布列的值进行hash运算后,将数据存储在对应的DN中。

所以Hash分布列选取至关重要,需要满足以下原则:

  1. 列值应比较离散。例如,考虑选择表的主键为分布列,如在人员信息表中选择身份证号码为分布列。
  2. 在满足第一条原则的情况下尽量不要选取存在常量filter的列。例如,表dwcjk相关的部分查询中出现dwcjk的列zqdh存在常量的约束(例如zqdh=’000001’),那么就应当尽量不用zqdh做分布列。
  3. 在满足前两条原则的情况,考虑选择查询中的连接条件为分布列,以便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;