用户手册

DDL

数据库模式定义语言DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言。主要由create（添加）、alter（修改）、drop（删除）关键字完成。

CREATE SCHEMA

规则

CREATE SCHEMA [ IF NOT EXISTS ] schema_name
[WITH (property_name = expression [, ...])]

描述

创建一个新的，空的库，使用关键字SCHEMA，在Hubble中SCHEMA和DATABASE是同义的，库是用来保存表、视图和其他数据对象的命名空间。如果库已经存在，可使用IF NOT EXISTS来进行判断，防止出错。

可选的WITH语句用于设置新创建库的属性，要列出所有可用的库属性，可使用语句进行查询：

SELECT * FROM system.metadata.schema_properties

例子

创建一个库user在当前的catalog下:

CREATE SCHEMA user;

在catalog hubble下创建一个tpcds库：

CREATE SCHEMA hubble.tpcds;

判断catalog hubble下如果不存在tpcds库则创建:

CREATE SCHEMA IF NOT EXISTS hubble.tpcds;

创建一个新的schema并设置所属于test用户:

CREATE SCHEMA tpcds AUTHORIZATION test;

CREATE TABLE

规则

CREATE TABLE [IF NOT EXISTS]
table_name (
  {column_name data_type [COMMENT comment] [WITH (property_name=expression [, ...])]
  | LIKE existing_table_name [{INCLUDING|EXCLUDING} PROPERTIES]}
  [, ...]
)
[COMMENT table_comment]
[WITH (property_name = expression [, ...])]

描述

创建一个空表，如果是创建有数据的表，请使用CREATE TABLE AS

防止表已存在导致的报错使用IF NOT EXISTS。

可选WITH子句可用于在新创建的表或单列上设置属性。要列出所有可用的表属性，请运行以下查询：

SELECT * FROM system.metadata.table_properties;

可选列属性请运行以下查询：

SELECT * FROM system.metadata.column_properties;

LIKE子句可用于将现有表中的所有列定义包含在新表中。可以指定多个LIKE子句，允许从多个表中复制列。

create table
  hubble.db_test.tab_l (
    idx int,
    name varchar,
    like hubble.db_test.account,
    like hubble.db_test.page
  );

如果指定了INCLUDING PROPERTIES，则所有表属性都将复制到新表中。如果WITH子句指定的属性名与复制的属性之一相同，则将使用WITH子句中的值。默认排除属性。最多可以为一个表指定INCLUDING PROPERTIES选项。

例子

在catalog hubble中创建ORC格式的itemline表：

CREATE TABLE
  hubble.tpch.lineitem (
    l_orderkey integer,
    l_partkey integer,
    l_suppkey integer,
    l_linenumber integer,
    l_quantity double,
    l_extendedprice double,
    l_discount double,
    l_tax double,
    l_returnflag varchar,
    l_linestatus varchar,
    l_shipdate varchar,
    l_commitdate varchar,
    l_receiptdate varchar,
    l_shipinstruct varchar,
    l_shipmode varchar,
    l_comment varchar
  )
WITH
  (format = 'ORC');

如果已经指定了hubble和tpch，则可以省略：

CREATE TABLE
  lineitem (
    l_orderkey integer,
    l_partkey integer,
    l_suppkey integer,
    l_linenumber integer,
    l_quantity double,
    l_extendedprice double,
    l_discount double,
    l_tax double,
    l_returnflag varchar,
    l_linestatus varchar,
    l_shipdate varchar,
    l_commitdate varchar,
    l_receiptdate varchar,
    l_shipinstruct varchar,
    l_shipmode varchar,
    l_comment varchar
  )
WITH
  (format = 'ORC');

分区表

CREATE TABLE
  p2_t2 (
    account varchar (32),
    date_time varchar (32),
    date_p varchar (32),
    biz_date integer
  )
WITH
  (
    format = 'MULTIDELIMIT',
    partitioned_by = ARRAY['date_p', 'biz_date'],
    textfile_field_separator = '|+|'
  );
   
-- 加载带分区表数据

从本地文件系统加载数据到表中的指定分区列
   
load data local inpath '/opt/zl/d_data/v3.txt' into table p2_t2 PARTITION(date_p='20230320',biz_date='3');
   
hubble方式导入数据的，执行call语句
   
call system.sync_partition_metadata (
  schema_name => 'zl',
  table_name => 'p2_t2',
  mode => 'FULL',
  case_sensitive => true
);

创建表带有多个分隔符：

CREATE TABLE
  t2 (id varchar, name varchar, age varchar)
WITH
  (
    format = 'MULTIDELIMIT', -- 多分隔符设置      
    textfile_field_separator = '|+|' -- 分隔符定义
  );

-- 添加注解
comment on table t2 is '测试2表';
comment on column t2.id is '表id';
comment on column t2.name is '名称';
comment on column t2.age is '年龄';

format='MULTIDELIMIT' 多分隔符设置
textfile_field_separator 分隔符
textfile_field_separator_escape 分割转义

分隔符：\t

create table
  customer_address (
    ca_address_sk int,
    ca_gmt_offset decimal (5, 2),
    ca_location_type char (20)
  )
WITH
  (
    format = 'MULTIDELIMIT',
    textfile_field_separator = U&'\0009'
  );

分隔符：\001

create table
  t3 (a varchar, b varchar)
WITH
  (
    format = 'MULTIDELIMIT',
    textfile_field_separator = U&'\0001'
  );

创建事务表：

-- 事务表 用于合并更新数据  格式类型必须ORC格式
CREATE TABLE
  t_p2_t1 (
    account varchar (32),
    date_time varchar (32),
    date_p varchar (32),
    biz_date integer
  )
WITH
  (format = 'ORC', transactional = true);

transactional=true 事务
事务表用于合并更新数据格式类型必须ORC格式

创建表lineitem如果表不存在，并且可以增加comment：

CREATE TABLE
  lineitem (
    l_orderkey integer,
    l_partkey integer,
    l_suppkey integer,
    l_linenumber integer,
    l_quantity double,
    l_extendedprice double,
    l_discount double,
    l_tax double,
    l_returnflag varchar,
    l_linestatus varchar,
    l_shipdate varchar COMMENT 'ship date in lineitem.',
    l_commitdate varchar,
    l_receiptdate varchar,
    l_shipinstruct varchar,
    l_shipmode varchar,
    l_comment varchar
  ) COMMENT 'A table belong to tpch.'

创建一个从lineitem复制的表，并增加一些字段：

CREATE TABLE
  new_lineitem (
    n_l_orderkey integer,
    like lineitem,
    n_l_comment varchar
  );

CREATE TABLE AS

规则

CREATE TABLE [IF NOT EXISTS] table_name [(column_alias, ...)]
[COMMENT table_comment]
[WITH (property_name = expression [, ...])]
AS query
[WITH [NO] DATA]

描述

创建包含SELECT查询结果的新表。

防止表已存在导致的报错使用IF NOT EXISTS。

可选WITH子句可用于设置新创建的表的属性。要列出所有可用的表属性，请运行以下查询：

SELECT * FROM system.metadata.table_properties;

例子

创建一个新表lineitem_column_aliased，复制lineitem表的指定列的字段类型和数据内容：

CREATE TABLE
  lineitem_column_aliased (orderkey, quantity) AS
SELECT
  l_orderkey,
  l_quantity
FROM
  lineitem;

创建一个lineitem表，按l_quantity表的l_orderkey字段分组统计：

CREATE TABLE
  lineitem_by_orderkey COMMENT 'Summary of lineitem by orderkey'
WITH
  (format = 'ORC') AS
SELECT
  l_orderkey,
  sum(l_quantity) AS price
FROM
  lineitem
GROUP BY
  l_orderkey;

创建一个空的empty_lineitem表：

CREATE TABLE
  empty_lineitem AS
SELECT
  *
FROM
  lineitem
WITH
  NO DATA;

CREATE VIEW

规则

CREATE [OR REPLACE] VIEW view_name
[SECURITY {DEFINER | INVOKER}]
AS query

描述

使用SELECT语句创建视图。该视图是一个逻辑表，可被查询使用。视图不包含任何数据，每次查询视图时，都会再次执行创建该视图的查询语句。

可使用CREATE OR REPLACE语句避免视图已存在导致的视图创建错误。

例子

创建一个简单的test视图来访问order表：

CREATE VIEW
  test AS
SELECT
  orderkey,
  orderstatus,
  totalprice / 2 AS half
FROM
  orders;

创建一个orders_by_date视图来访问order表的聚合数据:

CREATE VIEW
  orders_by_date AS
SELECT
  orderdate,
  sum(totalprice) AS price
FROM
  orders
GROUP BY
  orderdate;

创建一个覆盖视图：

CREATE OR REPLACE VIEW
  test AS
SELECT
  orderkey,
  orderstatus,
  totalprice / 4 AS quarter
FROM
  orders;

CREATE MATERIALIZED VIEW

规则

CREATE [OR REPLACE] MATERIALIZED VIEW view_name AS query

描述

使用SELECT语句创建物化视图。物化视图是一个具有实际物理存储的表。它将复杂的关联数据转化为一张实际的表，使得数据查询可以直接从物化视图中进行，无需再执行复杂的关联操作。物化视图的数据是预先计算和存储的，它需要通过刷新来更新数据。

可使用CREATE OR REPLACE语句避免视图已存在导致的视图创建错误。

使用物化视图时要保证缓存服务处于开启状态。

例子

基于统计分析结果创建一个物化视图：

CREATE MATERIALIZED VIEW hubble.v_test.mv_cust_return2
WITH (
  refresh_interval = '5m',
  grace_period = '10m',
  max_import_duration = '3m'
) AS
    SELECT
      sr_customer_sk ctr_customer_sk,
      sr_store_sk ctr_store_sk,
      sum(sr_return_amt) ctr_total_return
    FROM
    hubble.tpcds_external.store_returns,
    hubble.tpcds_external.date_dim
    WHERE ( (sr_returned_date_sk = d_date_sk) AND (d_year > 2000) )
    GROUP BY sr_customer_sk, sr_store_sk
;

创建一个单表映射物化视图：

CREATE OR REPLACE MATERIALIZED VIEW
  hubble.v_test.v_course2
WITH
  (
    refresh_interval = '5m',
    grace_period = '5m',
    max_import_duration = '1m'
  ) as
select
  *
from
  mysql.db_ai.course;

参数说明： refresh_interval，刷新频率。配置的最小时间为5分钟； grace_period，查询可以接受的过期快照时长。要大于等于refresh_interval； max_import_duration，刷新运行的最长时间，要小于等于refresh_interval；

创建一个定时刷新的物化视图：

CREATE MATERIALIZED VIEW hubble.v_test.mv_cust_return5
WITH (
  cron = '30 2 * * *',
  grace_period = '10m',
  max_import_duration = '3m',
  incremental_column = 'ctr_store_sk'
) AS
    SELECT
      sr_customer_sk ctr_customer_sk,
      sr_store_sk ctr_store_sk,
      sum(sr_return_amt) ctr_total_return
    FROM
      hubble.tpcds_external.store_returns,
      hubble.tpcds_external.date_dim
    WHERE ( (sr_returned_date_sk = d_date_sk) AND (d_year > 2000) )
    GROUP BY sr_customer_sk, sr_store_sk
;

cron 属性开启定时刷新；
incremental_column 属性用于标识增量刷新判断的列。

ALTER TABLE

规则


ALTER TABLE name ADD COLUMN column_name data_type [ COMMENT comment ] [ WITH ( property_name = expression [, ...] ) ]
ALTER TABLE name DROP COLUMN column_name
ALTER TABLE name RENAME COLUMN column_name TO new_column_name

描述

修改一个已存在的表的相关属性。

例子

为sales增加一个列:

ALTER TABLE sales ADD COLUMN address varchar;

删除address列:

ALTER TABLE sales DROP COLUMN address;

重命名列:

ALTER TABLE sales RENAME COLUMN id TO sale_id;

ALTER TABLE EXECUTE

增加了表存储优化功能，便于用户简单直接的对表完成小文件合并，建议此功能可以在非业务时段进行。需要注意，考虑到分桶表不存在小文件问题，此功能不对分桶表生效。

-- 必须在执行表存储优化前，设置non_transactional_optimize_enabled参数
set session hubble.non_transactional_optimize_enabled=true;
-- 默认的file_size_threshold阈值为100MB，即小于此阈值的存储文件将会被compaction
ALTER TABLE hubble.tpcds.item EXECUTE optimize;
-- 用户可根据实际情况，指定需要做compaction文件的存储大小阈值
ALTER TABLE hubble.tpcds.item EXECUTE optimize(file_size_threshold => '10MB');
-- 指定表的具体分区，进行compaction优化
ALTER TABLE hubble.tpcds.item EXECUTE optimize where batch_date = '20220610';

注意事项：

如果对当前正在优化的表运行查询，则可能会读取重复的行。
在优化操作期间发生异常的极端情况下，需要手动清理表目录。在这种情况下，请参考日志并查询失败消息，以查看需要删除哪些文件。

ALTER VIEW

规则

ALTER VIEW name RENAME TO new_name
ALTER VIEW name SET AUTHORIZATION ( user | USER user | ROLE role )

描述

更改现有视图的属性。

例子

ALTER VIEW teacher RENAME TO people;

DROP SCHEMA

规则

DROP SCHEMA [ IF EXISTS ] schema_name

描述

删除一个已存在的库，库必须是空的。

例子

删除web库

DROP SCHEMA web;

DROP TABLE

规则

DROP TABLE  [IF EXISTS] table_name

描述

删除已存在的表。

例子

删除表lineitem_by_date；如果存在表orders_by_date则进行删除。

DROP TABLE lineitem_by_date;
DROP TABLE IF EXISTS orders_by_date;

DROP VIEW

规则

DROP [MATERIALIZED] VIEW [ IF EXISTS ] view_name

描述

删除一个已存在的视图。

如果删除的是一个物化视图，需要带关键字 MATERIALIZED 。

例子

删除视图view_order

DROP VIEW view_order;

删除物化视图mv_customer;

DROP MATERIALIZED VIEW mv_customer;

删除视图如果已存在view_orders

DROP VIEW IF EXISTS view_orders;

USE

规则

USE catalog.schema;
USE schema;

描述

更新session指定当前的catalog和schema。

例子

指定catalog为hubble，库为tpcds：

use hubble.tpcds;

SHOW CATALOGS

规则

SHOW CATALOGS [ LIKE pattern ]

描述

查看所有可用的catalog，可选LIKE用来限制查看的catalog名。

SHOW SCHEMAS

规则

SHOW SCHEMAS [ FROM catalog ] [ LIKE pattern ]

描述

查看指定catalog或当前catalog下所有库，可选LIKE用来限制查看的database名。

SHOW TABLES

规则

SHOW TABLES [ FROM schema ] [ LIKE pattern ]

描述

查看指定库或当前库下所有表，可选LIKE用来限制查看的表名。

SHOW TABLE LOCATION

规则

SHOW TABLE LOCATION tableName

描述

查看指定表的文件系统存储路径。

SHOW PARTITIONS

规则

SHOW PARTITIONS tableName

描述

查看指定表的分区信息列表。

SHOW COLUMNS

规则

SHOW COLUMNS [ FROM table ] [ LIKE pattern ]

描述

查看指定表的字段，可选LIKE用来限制查看的表名。

示例：

SHOW COLUMNS FROM call_center LIKE '%name';

SHOW CREATE SCHEMA

规则

SHOW CREATE SCHEMA schema_name

描述

查看指定的库的创建语句。

SHOW CREATE TABLE

规则

SHOW CREATE TABLE table_name

描述

查看指定的表的创建语句。

SHOW CREATE VIEW

规则

SHOW CREATE [MATERIALIZED] VIEW view_name

描述

查看指定的视图的创建语句。

DESCRIBE

规则

DESCRIBE table_name

描述

查看表的列，包括列名，类型等。

同时也可以使用SHOW COLUMNS FROM table语法，二者同义。

COMMENT ON

规则

COMMENT ON COLUMN table_name.column_name IS 'comment';

描述

对字段添加注释。

START TRANSACTION

规则

START TRANSACTION [ mode [, ...] ]

当模式是其中之一：
ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } READ { ONLY | WRITE }

描述

为当前会话启动一个新事务

例子

START TRANSACTION; 

START TRANSACTION ISOLATION LEVEL REPEATABLE READ;

START TRANSACTION READ WRITE;

START TRANSACTION ISOLATION LEVEL READ COMMITTED, READ ONLY;

START TRANSACTION READ WRITE, ISOLATION LEVEL SERIALIZABLE;

COMMIT

规则

 COMMIT tablename

描述

提交当前事物

例子

COMMIT;

COMMIT WORK;

ROLLBACK

规则

ROLLBACK tablename;

描述回滚当前事物

例子

ROLLBACK;    

ROLLBACK WORK;

EXPLAIN

规则

EXPLAIN [ ( option [, ...] ) ] statement

描述

显示语句的逻辑或分布式执行计划，或验证语句。默认情况下显示分布式计划。分布式计划的每个计划片段由单个或多个节点执行。

例子

EXPLAIN SELECT a, b FROM (
  SELECT a, MAX(b) AS b FROM t GROUP BY a
) AS x;

CALL

规则

CALL procedure_name ( [ name => ] expression [, ...] )

描述

调用系统表。

例子

结束某个耗资源的查询

CALL system.runtime.kill_query(query_id => '2019061408482400160NjkwT', message => 'Using too many resources');

在catalog hubble中创建新分区

CALL system.create_empty_partition(
    schema_name => 'web',
    table_name => 'page_views',
    partition_columns => ARRAY['ds', 'country'],
    partition_values => ARRAY['2019-06-09', 'CN']);

刷新缓存及表的统计信息：

-- flush所有的缓存数据
CALL hubble.system.flush_metadata_cache();
-- flush指定表的分区元信息缓存
CALL hubble.system.flush_metadata_cache(
    schema_name => 'tpcds',
    table_name => 'catalog_sales',
    partition_column => ARRAY['cs_sold_date_sk'],
    partition_value => ARRAY['2452634']
);