用户手册

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](CREATE TABLE AS)

防止表已存在创建表报错使用IF NOT EXISTS。

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

SELECT * FROM system.metadata.table_properties;

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

SELECT * FROM system.metadata.column_properties;

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

如果指定了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 bk_t2(
  view_time VARCHAR,
  user_id BIGINT,
  page_url VARCHAR,
  ds DATE,
  country VARCHAR
)WITH (
  format = 'ORC',
  partitioned_by = ARRAY[ 'country'],
  bucketed_by = ARRAY['ds'],
  bucket_count = 10
);

--分桶
create table bk_t3(
  view_time VARCHAR,
  user_id BIGINT,
  page_url VARCHAR,
  ds DATE,
  country VARCHAR
)WITH (
  format = 'ORC',  
  bucketed_by = ARRAY['ds'],
  bucket_count = 10
)

创建表带有多个分隔符：

	
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查询结果的新表。使用[CREATE TABLE](CREATE TABLE)创建空表。

如果该表已经存在，可选的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_orderkey分组统计l_quantity的表：

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的新视图。该视图是一个逻辑表，可供将来的查询引用。视图不包含任何数据。相反，每次视图被另一个查询引用时，都会执行该视图存储的查询。

如果视图已经存在，则可选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;

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 VIEW [ IF EXISTS ] view_name

描述

删除一个已存在的视图

例子

删除视图view_order

DROP VIEW view_order;

删除视图如果已存在view_orders

DROP VIEW IF EXISTS view_orders;

USE

规则

USE catalog.database
USE database

描述

更新session以指定catalog和database。

例子

指定catalog为hubble，库为tpcds

use hubble.tpcds;

指定catalog为hubble，库为tpch

use hubble.tpch

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 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 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所有的OLAPMETA缓存数据
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']
);