文档首页/ 数智融合计算服务 DataArtsFabric/ 开发指南/ 设计数据库对象/ 创建和管理表
更新时间：2025-09-18 GMT+08:00
查看PDF
分享

创建和管理表

创建表

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;
父主题： 设计数据库对象