Hubble SQL STATEMENTS

DCL

USER

查看所有用户

所需权限：

需要用admin权限

SHOW USERS;

创建用户

CREATE USER 语句创建库用户，使你可以控制数据库的表的权限。

注意事项：
角色名：
不区分大小写
必须以字母或下划线开头
必须仅包含字母，数字或下划线
必须介于1到63个字符之间
创建用户后，必须授予他们对数据库和表的权限
在secure集群上，你必须为用户创建客户端证书，并且用户必须验证其对集群的访问权限

所需权限

用户必须在 system.users 表上具有insert和update权限

创建一个用户

CREATE USER beagledata;

注意
创建用户后，必须授予用户对数据库相关权限。

使用密码创建用户

CREATE USER beagledata WITH PASSWORD 'abcd1234';

删除一个或多个用户

所需权限

用户必须拥有system.users表的delete权限。
必须先撤销用户的所有权限，然后才能删除该用户

针对单个表的权限

SHOW grants ON hubble.orders FOR beagledata;

+--------+------------+------------+
| Table  |     User   | Privileges |
+--------+------------+------------+
| orders | beagledata | CREATE     |
| orders | beagledata | INSERT     |
| orders | beagledata | UPDATE     |
+--------+------------+------------+
(3 rows)

针对用户的所有权限

SHOW grants FOR beagledata;

回收表的权限（被收回的权限需是显式授权给用户的权限）

REVOKE CREATE,INSERT,UPDATE ON hubble.orders FROM beagledata;

删除用户（当用户显式授权未全部收回时，无法删除用户）

DROP USER beagledata;

修改和新增用户名密码

仅在非root用户的secure集群中支持密码创建和更改

所需权限

用户需要对system.users表有INSERT和UPDATE权限

ALTER USER beagledata WITH PASSWORD "Pass@Word";

ROLE

SHOW ROLES

查看所有数据库上的角色，需要admin权限。

SHOW ROLES;

CREATE ROLE

创建一个角色，需要超管或admin权限，root用户默认是admin权限。

角色的所有特权均由其所有成员继承。

CREATE ROLE dev;

DROP ROLE

删除一个角色，需要admin权限。

DROP ROLE dev;

PRIVILEGE

SHOW GRANTS

列出所有数据库对象的权限信息

SHOW GRANTS;

列出角色是admin的用户

SHOW GRANTS ON ROLE ADMIN;

列出admin角色的权限信息

SHOW GRANTS FOR ADMIN;

列出某个数据库的权限信息

SHOW GRANTS ON DATABASE my_hubble_database

列出beagledata用户在数据库my_hubble_database的权限信息

SHOW GRANTS ON DATABASE my_hubble_database FOR beagledata;

列出某张表的权限信息

SHOW GRANTS ON TABLE my_hubble_table;

GRANT

用于添加一个角色或者角色中的一个用户。

授予角色成员资格的用户必须是角色管理员或者是admin角色的成员

用户和角色可以作为角色成员。
root 用户会自动作为 admin 角色且对所有数据库拥有 ALL 权限。
角色成员会自动继承其角色的所有权限。

GRANT 用于控制每一个角色或者用户和指定数据库或数据表上拥有的sql权限，对于指定语句所需要的权限，请查看各自相关sql语句的文档。

权限	级别
ALL	Database,Table
CREATE	Database,Table
DROP	Database,Table
GRANT	Database,Table
SELECT	Table
INSERT	Table
DELETE	Table
UPDATE	Table

给用户授予数据库的权限

GRANT CREATE ON DATABASE hubbletest TO beagledata;

查看数据库权限

SHOW GRANTS ON DATABASE hubbletest;

+---------------+--------------------+---------+----------------+
| database_name |    schema_name     | grantee | privilege_type |
+---------------+--------------------+---------+----------------+
| database_0903 | hubble_internal    | admin   | ALL            |
| database_0903 | hubble_internal    | beagle  | CREATE         |
| database_0903 | hubble_internal    | root    | ALL            |
| database_0903 | information_schema | admin   | ALL            | 
+---------------+--------------------+---------+----------------+
(4 rows)

给角色授予数据库的权限

GRANT CREATE ON DATABASE hubbletest TO dev;

查看角色权限

SHOW GRANTS FOR dev;

给用户授予表的权限

GRANT DELETE ON TABLE hubbletest.orders TO beagledata;

查看表权限

SHOW GRANTS ON TABLE hubbletest.orders;

+---------------+-------------+------------+---------+----------------+ 
| database_name | schema_name | table_name | grantee | privilege_type |
+---------------+-------------+------------+---------+----------------+
| database_0903 | public      | person     | admin   | ALL            |
| database_0903 | public      | person     | beagle  | CREATE         |
| database_0903 | public      | person     | beagle  | DELETE         |
+---------------+-------------+------------+---------+----------------+
(3 rows)

REVOKE

用于回收一个用户的角色。

所需权限

授予角色成员资格的用户必须拥有GRANT权限。

回收给用户的数据库的权限

REVOKE CREATE ON DATABASE hubbletest FROM beagledata;

SHOW GRANTS ON DATABASE hubbletest;

+----------------------+--------------------+---------+----------------+
|    database_name     |    schema_name     | grantee | privilege_type |
+----------------------+--------------------+---------+----------------+
| hubble_database_0902 | hubble_internal    | admin   | ALL            |
| hubble_database_0902 | hubble_internal    | root    | ALL            |
+----------------------+--------------------+---------+----------------+
(2 rows)

回收给角色的数据库的权限

REVOKE CREATE ON DATABASE hubbletest FROM dev;
SHOW GRANTS ON DATABASE hubbletest FOR dev;

回收给用户授予的表的权限

REVOKE DELETE ON TABLE hubbletest.orders FROM beagledata;
SHOW GRANTS ON TABLE hubbletest.orders;

DDL

ADD COLUMN

在表内添加一个新列

所需权限

用户必须有表的CREATE权限。

参数

参数	说明
table_name	需要添加列的表
column_name	需要添加列的名称（必须满足命名规则，且在表内唯一，但可与索引、约束同名）
typename	需要添加新列的数据类型
col_qualification	列定义的可选列表，可能包括列级约束，排序规则或列族分配。需要注意，无法直接添加具有外键约束的列。可以添加没有约束的列，然后使用`CREATE INDEX`索引列，再使用`ADD CONSTRAINT`将外键约束添加到列。

添加单列

ALTER TABLE person ADD COLUMN age INT;

  column_name |  data_type  | is_nullable | column_default | generation_expression | indices   |is_hidden  
+-------------+-------------+-------------+----------------+-----------------------+-----------+--------+
  name        | STRING      |    true     | NULL           |                       | {}        |   false 
  id          | INT8        |    false    | NULL           |                       | {primary} |   false 
  email       | STRING      |    true     | NULL           |                       |           |   false 
  adress      | STRING      |    true     | NULL           |                       | {}        |   false 
  age         | INT8        |    true     | NULL           |                       | {}        |   false 
(5 rows)

添加多列

ALTER TABLE company ADD COLUMN adress STRING, ADD COLUMN zczb INT;

  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden 
+-------------+-----------+-------------+----------------+-----------------------+-----------+----------+
  id          | INT8      |    false    | NULL           |                       | {primary} |   false   
  name        | STRING    |    true     | NULL           |                       | {}        |   false   
  clrq        | DATE      |    true     | NULL           |                       | {}        |   false   
  adress      | STRING    |    true     | NULL           |                       | {}        |   false   
  zczb        | INT8      |    true     | NULL           |                       | {}        |   false   
(5 rows)

添加具有NOT NULL约束和DEFAULT值的列

ALTER TABLE company ADD COLUMN money DECIMAL NOT NULL DEFAULT (DECIMAL '123456789');

  column_name | data_type | is_nullable |        column_default        | generation_expression |  indices  | is_hidden  
+-------------+-----------+-------------+------------------------------+-----------------------+-----------+-----------+
  id          | INT8      |    false    | NULL                         |                       | {primary} |   false    
  name        | STRING    |    true     | NULL                         |                       | {}        |   false    
  clrq        | DATE      |    true     | NULL                         |                       | {}        |   false    
  adress      | STRING    |    true     | NULL                         |                       | {}        |   false    
  zczb        | INT8      |    true     | NULL                         |                       | {}        |   false    
  money       | DECIMAL   |    false    | 123456789:::DECIMAL::DECIMAL |                       | {}        |   false    
(6 rows)

添加带有UNIQUE约束列，并且值非空

ALTER TABLE company ADD COLUMN tel DECIMAL UNIQUE NOT NULL;

  column_name | data_type | is_nullable |        column_default        | generation_expression |          indices          | is_hidden  
+-------------+-----------+-------------+------------------------------+-----------------------+---------------------------+-----------+
  id          | INT8      |    false    | NULL                         |                       | {primary,company_tel_key} |   false    
  name        | STRING    |    true     | NULL                         |                       | {}                        |   false    
  clrq        | DATE      |    true     | NULL                         |                       | {}                        |   false    
  adress      | STRING    |    true     | NULL                         |                       | {}                        |   false    
  zczb        | INT8      |    true     | NULL                         |                       | {}                        |   false    
  money       | DECIMAL   |    false    | 123456789:::DECIMAL::DECIMAL |                       | {}                        |   false    
  tel         | DECIMAL   |    false    | NULL                         |                       | {company_tel_key}         |   false    
(7 rows)

添加带有排序规则的列

ALTER TABLE company ADD COLUMN email STRING COLLATE en;

  column_name |     data_type     | is_nullable |        column_default        | generation_expression |          indices          | is_hidden  
+-------------+-------------------+-------------+------------------------------+-----------------------+---------------------------+-----------+
  id          | INT8              |    false    | NULL                         |                       | {primary,company_tel_key} |   false    
  name        | STRING            |    true     | NULL                         |                       | {}                        |   false    
  clrq        | DATE              |    true     | NULL                         |                       | {}                        |   false    
  adress      | STRING            |    true     | NULL                         |                       | {}                        |   false    
  zczb        | INT8              |    true     | NULL                         |                       | {}                        |   false    
  money       | DECIMAL           |    false    | 123456789:::DECIMAL::DECIMAL |                       | {}                        |   false    
  tel         | DECIMAL           |    false    | NULL                         |                       | {company_tel_key}         |   false    
  email       | STRING COLLATE en |    true     | NULL                         |                       | {}                        |   false    
(8 rows)

添加一列并将其分给新的列族

ALTER TABLE company ADD COLUMN creat_time DATE CREATE FAMILY new_time;

SHOW CREATE TABLE company;  // 查看建表语句

  table_name |                            create_statement                              
+------------+-------------------------------------------------------------------------+
  company    | CREATE TABLE company (                                                   
             |     id INT8 NOT NULL,                                                    
             |     name STRING NULL,                                                    
             |     clrq DATE NULL,                                                      
             |     adress STRING NULL,                                                  
             |     zczb INT8 NULL,                                                      
             |     money DECIMAL NOT NULL DEFAULT 123456789:::DECIMAL::DECIMAL,         
             |     tel DECIMAL NOT NULL,                                                
             |     email STRING COLLATE en NULL,                                        
             |     creat_time DATE NULL,                                                
             |     CONSTRAINT "primary" PRIMARY KEY (id ASC),                           
             |     UNIQUE INDEX company_tel_key (tel ASC),                              
             |     FAMILY "primary" (id, name, clrq, adress, zczb, money, tel, email),  
             |     FAMILY new_time (creat_time)                                         
             | )                                                                        
(1 row)

添加一列分给现有列族

ALTER TABLE company ADD COLUMN time string FAMILY new_time;

  table_name |                            create_statement                              
+------------+-------------------------------------------------------------------------+
  company    | CREATE TABLE company (                                                   
             |     id INT8 NOT NULL,                                                    
             |     name STRING NULL,                                                    
             |     clrq DATE NULL,                                                      
             |     adress STRING NULL,                                                  
             |     zczb INT8 NULL,                                                      
             |     money DECIMAL NOT NULL DEFAULT 123456789:::DECIMAL::DECIMAL,         
             |     tel DECIMAL NOT NULL,                                                
             |     email STRING COLLATE en NULL,                                        
             |     creat_time DATE NULL,                                                
             |     "time" STRING NULL,                                                  
             |     CONSTRAINT "primary" PRIMARY KEY (id ASC),                           
             |     UNIQUE INDEX company_tel_key (tel ASC),                              
             |     FAMILY "primary" (id, name, clrq, adress, zczb, money, tel, email),  
             |     FAMILY new_time (creat_time, "time")                                 
             | )                                                                        
(1 row)

如果列族不存在，则添加列并创建列族

ALTER TABLE company ADD COLUMN new_time STRING CREATE IF NOT EXISTS FAMILY n_time;

  table_name |                            create_statement                              
+------------+-------------------------------------------------------------------------+
  company    | CREATE TABLE company (                                                   
             |     id INT8 NOT NULL,                                                    
             |     name STRING NULL,                                                    
             |     clrq DATE NULL,                                                      
             |     adress STRING NULL,                                                  
             |     zczb INT8 NULL,                                                      
             |     money DECIMAL NOT NULL DEFAULT 123456789:::DECIMAL::DECIMAL,         
             |     tel DECIMAL NOT NULL,                                                
             |     email STRING COLLATE en NULL,                                        
             |     creat_time DATE NULL,                                                
             |     "time" STRING NULL,                                                  
             |     new_time STRING NULL,                                                
             |     CONSTRAINT "primary" PRIMARY KEY (id ASC),                           
             |     UNIQUE INDEX company_tel_key (tel ASC),                              
             |     FAMILY "primary" (id, name, clrq, adress, zczb, money, tel, email),  
             |     FAMILY new_time (creat_time, "time"),                                
             |     FAMILY n_time (new_time)                                             
             | )                                                                        
(1 row)

提示：FAMILY语句见COLUMN FAMILY内容

ADD CONSTRAINT

用于为列添加约束：

Check
Foreign Keys
UNIQUE

主键的NOT NULL约束只能通过CREATE TABLE创建。DEFAULT约束通过ALTER COLUMN管理

所需权限

需要有表的CREATE权限。

添加约束：UNIQUE

添加UNIQUE约束要求该列的每一个值都是唯一的，NULL除外

ALTER TABLE shop ADD CONSTRAINT id_unique UNIQUE (id);

  column_name | data_type | is_nullable | column_default | generation_expression |       indices       | is_hidden  
+-------------+-----------+-------------+----------------+-----------------------+---------------------+-----------+
  id          | INT8      |    false    | NULL           |                       | {primary,id_unique} |   false    
(1 row)

添加约束：check

ALTER TABLE shop ADD CONSTRAINT money_check CHECK (money > 0);

  table_name | constraint_name | constraint_type |       details        | validated  
+------------+-----------------+-----------------+----------------------+-----------+
  shop       | id_unique       | UNIQUE          | UNIQUE (id ASC)      |   true     
  shop       | money_check     | CHECK           | CHECK ((money > 0))  |   true     
  shop       | primary         | PRIMARY KEY     | PRIMARY KEY (id ASC) |   true     
(3 rows)

添加FOREIGN KEY约束

注意：
在添加FOREIGN KEY约束前，该列必须已添加索引。如果该列未索引，使用CREATE INDEX来创建索引，然后再通过ADD CONSTRAINT 添加 FOREIGN KEY 约束

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name STRING,
    email STRING
);

CREATE TABLE shipments (
    tracking_number UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    carrier STRING,
    status STRING,
    customer_id INT
  );

ALTER TABLE shipments1 ADD CONSTRAINT fk_customers FOREIGN KEY (customer_id) REFERENCES customers1(id);

SHOW CONSTRAINTS FROM shipments;

CREATE DATABASE

创建数据库

CREATE DATABASE IF NOT EXISTS my_hubble_database;

CREATE TABLE

参数

参数	详情
if not exists	仅当数据库中不存在同名表时才创建新表; 如果确实存在，则不返回错误
table_name	要创建的表的名称，在其数据库中必须是唯一的，并遵循identifier rules. 如果没有设置默认父数据库，则必须将名称格式设置为`database.name`。
column_def	定义列的逗号分隔列表。每列需要名称/标识符和数据类型; 列级约束或其他列限定（例如计算列）的指定是可选项。列名在表中必须是唯一的，但可以与索引或约束具有相同的名称。
index_def	可选项，定义索引的逗号分隔列表。对于每个索引，必须指定要索引的列; 可选择指定名称。索引名称在表中必须是唯一的，并遵循标识符规则。请参阅下面的创建具有辅助索引和倒排索引的表示例。
family_def	可选项，定义列族的逗号分隔列表。列族名称在表中必须是唯一的，但可以与列，约束或索引具有相同的名称。列族是一组列，它们作为单个键值对存储在基础键值存储中。 hubble自动将列分组到列族中，以确保有效的存储和性能。但是，有时你可能希望手动将列分配给族，更多详细信息，请参见列族文档。
table_constraint	可选项，表级约束的逗号分隔列表。约束名称在表中必须是唯一的，但可以与列，列族或索引具有相同的名称
opt_interleave	你可以通过交错表来优化查询性能，这会更改hubble存储数据的方式。
opt_partition_by	允许你在行级别定义表分区。你可以按列表或按range定义表分区。

创建不定义主键的表

在hubble中，每个表都需要一个主键。如果未明确定义，则会自动添加一个INT类型的名为rowid的列作为主键，并使用unique_rowid()函数确保新行始终默认为唯一的rowid值。自动为主键创建索引。

CREATE TABLE my_table1(
  user_id INT,
  cre_date DATE
);

严格地说，主键的唯一索引并没有创建，它是由数据储存层的key来区分的，因此不需要额外的空间。但是，在使用show index等命令时，它会显示为正常的唯一索引。

SHOW INDEX FROM my_table1;

table_name|index_name|non_unique|seq_in_index|column_name|direction|storing|implicit
----------+----------+----------+------------+-----------+---------+-------+--------
my_table1 |  primary |  false   |           1|   rowid   |ASC      | false | false 
(1 row)

创建表定义主键

例如：我们创建一个包含三列的表。一列是主键，另一列是唯一约束，第三列没有约束。具有唯一约束的主键和列将自动创建索引。

CREATE TABLE my_table2 (
  user_id INT PRIMARY KEY,
  user_email STRING UNIQUE,
  logoff_date DATE
);

SHOW COLUMNS FROM my_table2;

+-------------+--------+-------+---------+---------------------------------+
|    Field    |  Type  | Null  | Default |             Indices             |
+-------------+--------+-------+---------+---------------------------------+
| user_id     | INT    | false | NULL    | {primary,logoff_user_email_key} |
| user_email  | STRING | true  | NULL    | {logoff_user_email_key}         |
| logoff_date | DATE   | true  | NULL    | {}                              |
+-------------+--------+-------+---------+---------------------------------+
(3 rows)

SHOW INDEX FROM my_table2;

+---------+---------------------+------+---+----------+--------+-------+--------+
| Table   |        Name         |Unique|Seq|  Column  |irection|Storing|Implicit|
+---------+---------------------+------+---+----------+--------+-------+--------+
|my_table2|primary              |true  |  1|user_id   | ASC    | false | false  |
|my_table2|logoff_user_email_key|true  |  1|user_email| ASC    | false | false  |
|my_table2|logoff_user_email_key|true  |  2|user_id   | ASC    | false | true   |
+---------+---------------------+------+---+----------+--------+-------+--------+
(3 rows)

CREATE TABLE AS

参数	详情
if not exists	仅当数据库中不存在同名表时，才创建新表；如果存在，不返回错误。请注意，if not exists仅检查表名，不检查现有表是否具有新表的相同列，索引，约束等。
table_name	要创建的表的名称，与其数据库中必须是唯一的，并遵循标识符规则。如果未设置默认父数据库，则必须将名称格式设置为database.name `UPSERT`和`INSERT ON CONFLICT`语句使用名为`excluded`的临时表来处理执行期间的唯一性冲突。因此，建议不要使用表名`excluded`。
name	要使用的列的名称，而不是select_stmt中列的名称
select_stmt	用于提供数据的选择查询

使用create table ... as 创建的表的主键不是从查询结果派生的；与其他表一样，创建后无法添加或更改主键；而且这些表不与其他表交错；列族的默认规则适用。

例如：

CREATE TABLE logtable ( 
    user_id INT PRIMARY KEY, 
    user_name STRING UNIQUE, 
    log_date DATE NOT NULL 
);

CREATE TABLE logtable_copy AS TABLE logtable;

SHOW CREATE TABLE logtable_copy;

   table_name   |                      create_statement
+--------------+-------------------------------------------------------------+
  logtable_copy | CREATE TABLE logtable_copy (
                |     user_id INT8 NULL,
                |     user_name STRING NULL,
                |     log_date DATE NULL,
                |     FAMILY "primary" (user_id, user_name, log_date, rowid)
                | )
(1 row)

该示例说明主键，唯一和“非空”约束不会派生到新的表。

不过可以在CREATE TABLE ... AS 之后创建二级索引 :

CREATE INDEX logtable_copy_id_idx ON logtable_copy ( user_id );
SHOW CREATE TABLE logtable_copy;

+-------------+-----------------------------------------------------------------+
|    Table    |                           CreateTable                           |
+-------------+-----------------------------------------------------------------+
| logoff_copy | CREATE TABLE logoff_copy (                                      |
|             |     user_id INT NULL,                                           |
|             |     user_email STRING NULL,                                     |
|             |     logoff_date DATE NULL,                                      |
|             |     INDEX logoff_copy_id_idx (user_id ASC),                     |
|             |     FAMILY "primary" (user_id, user_email, logoff_date, rowid)  |
|             | )                                                               |
+-------------+-----------------------------------------------------------------+

CREATE INDEX

CREATE INDEX语句为表创建索引。

PRIMARY KEY 和 UNIQUE列将会自动创建索引。

所需权限

要求拥有表的CREATE权限。

创建索引

CREATE TABLE cust_info (
    id INT DEFAULT unique_rowid(),
    name STRING,
    age INT,
    region STRING,
    address JSONB
);

创建索引

CREATE INDEX ON cust_info (age);

创建二级索引

CREATE INDEX ON cust_info (name, region);

创建唯一索引

CREATE UNIQUE INDEX ON cust_info (name);

或者

ALTER TABLE cust_info ADD CONSTRAINT cust_name_id_key UNIQUE (name, id);

JSONB类型的字段可以创建倒排索引

CREATE INVERTED INDEX ON cust_info (address);

\d cust_info  // show columns from cust_info

column_name|data_type|is_nullable|column_default|       indices         |is_hidden
-----------+---------+-----------+--------------+-----------------------+---------
id         |INT8     |   true    |unique_rowid()|{cust_info_name_id_key}|  false
name       |STRING   |   true    |NULL          |{cust_info_name_id_key}|  false
age        |INT8     |   true    |NULL          |{}                     |  false
region     |STRING   |   true    |NULL          |{}                     |  false
address    |JSONB    |   true    |NULL          |{cust_info_address_idx}|  false
rowid      |INT8     |   false   |unique_rowid()|{primary,              |  true
           |         |           |              | cust_info_name_id_key,|
           |         |           |              | cust_info_address_idx}|

CREATE VIEW

CREATE VIEW语句创建一个新视图，它是一个表示为虚拟表的存储查询。

所需权限

用户必须对父数据库具有CREATE权限，并且对视图引用的热和表具有SELECT权限

参数

参数	详情
view_name	要创建的视图的名称，该视图的名称在其数据库中必须是唯一的，并遵循标识符规则。如果未设置默认父数据库，则必须将名称格式设置为`database.name`。
`name_list`	可选项，视图的逗号分隔的列名列表。如果指定，则将在response中使用这些名称，而不是`AS select_stmt`中指定的列。
as select_stmt	在请求视图时执行的选择查询。请注意，目前无法使用`*`来从引用的表或视图中选择所有列，你必须指定特定的列。

CREATE VIEW general_view AS SELECT id, name FROM cust_info;

注意：
目前暂不支持CREATE VIEW .. AS SELECT * FROM ...,需要手动指定字段名。

查看创建视图

SHOW CREATE VIEW general_view;

 table_name |          create_statement
------------+-----------------------------------------------
general_view|CREATE VIEW general_view ( ID, NAME ) AS SELECT
            |ID,
            |NAME 
            |FROM
            |    defaultdb.PUBLIC.cust_info

CREATE SEQUENCE

所需权限

执行此操作需要用户拥有CREATE权限。

SEQUENCE支持一下函数操作

nextval('seq_name')
currval('seq_name')
lastval()
setval('seq_name',value,is_called)

查询当前数据库中的SEQUENCES情况

SELECT * FROM information_schema.sequences;

创建SEQUENCE

CREATE SEQUENCE userid_seq;

SHOW CREATE userid_seq;

table_name |    create_statement
-----------+------------------------------
userid_seq | CREATE SEQUENCE userid_seq 
           | MINVALUE 1 
           | MAXVALUE 9223372036854775807 
           | INCREMENT 1 
           | START 1

建表时指定主键根据sequence自动增长

CREATE TABLE user_table(
  user_id int PRIMARY KEY DEFAULT nextval('userid_seq'),
  phone_number string,
  cre_date date
);

插入几条测试数据

insert into user_table (phone_number, cre_date)
values
('13900010002','2018-10-01'),
('13654559807','2018-12-01'),
('18800001111','2016-10-01');

  user_id | phone_number |         cre_date           
+---------+--------------+---------------------------+
        1 | 13900010002  | 2018-10-01 00:00:00+00:00  
        2 | 13654559807  | 2018-12-01 00:00:00+00:00  
        3 | 18800001111  | 2016-10-01 00:00:00+00:00

注意
使用SEQUENCE比使用内置函数gen_random_uuid()、uuid_v4()、unique_rowid()自动生成的唯一ID要慢。

COMMENT ON

添加注释

数据库添加注释

COMMENT ON DATABASE database_0922 IS '这是测试数据库';

SHOW databases WITH COMMENT;

     database_name     |     comment       
+----------------------+------------------+
     aideng            |     NULL          
     database_0903     |     NULL          
     database_0922     |     这是测试数据库
     db2               |     NULL

表添加注释

COMMENT ON TABLE person IS '用户信息表';

SHOW tables WITH COMMENT;

 table_name | comment  
+------------+---------+
  t          |          
  t2         |          
  v2         |          
  view_test  |          
  person     | 人员表   
  company    |          
  shop       |          
  user_table |          
(8 rows)

字段添加注释

COMMENT ON COLUMN person.id IS '唯一id';

SHOW COLUMNS FROM person WITH COMMENT;

  column_name |  data_type  | is_nullable | column_default | generation_expression |          indices           | is_hidden | comment  
+-------------+-------------+-------------+----------------+-----------------------+----------------------------+-----------+---------+
  name        | STRING      |    true     | NULL           |                       | {}                         |   false   | NULL     
  comment     | VARCHAR(20) |    true     | NULL           |                       | {}                         |   false   | NULL     
  phone       | STRING      |    true     | NULL           |                       | {}                         |   false   | NULL     
  money       | INT8        |    true     | NULL           |                       | {}                         |   false   | NULL     
  id          | INT8        |    false    | NULL           |                       | {primary,person_email_key} |   false   | 唯一id       
(5 rows)

DROP DATABASE

DROP DATABASE语句会删除hubble中的一个数据库和数据库中的所有对象。

所需权限

用户必须拥有要删除数据库和数据库中所有表的DROP 权限。

删除数据库与数据库中的对象（CASCADE）
对于client端会话，DROP DATABASE默认使用CASCADE，会删除数据库中的所有表与视图，同时也会删除所有依赖表的对象，例如CONSTRAINT和views。
禁止删除一个非空的数据库（RESTRICT）
当一个数据库不是空的时候，RESTRICT会阻止数据库的删除。

DROP DATABASE hubble_database_0902 RESTRICT;

pq: database "hubbledatabase" is not empty and CASCADE was not specified

DROP TABLE

从数据库中删除一张表，包括表的数据与索引。

所需权限

用户必须对要删除的表拥有drop权限。

SHOW tables FROM hubbletest;
DROP TABLE hubbletest.branches;
DROP TABLE hubbletest.orders CASCADE;

注意
CASCADE删除依赖于表的所有对象（例如约束和视图）。CASCADE不会列出它掉落的对象，因此应谨慎使用。

DROP COLUMN

DROP COLUMN 语句是ALTER TABLE的一部分，会删除一张表中某些列。

所需权限

用户必须拥有CREATE权限。

当我们有变更表结构时，hubble会将这个操作注册成一个job，可以通过SHOW JOBS查看。

删除列

ALTER TABLE t2 DROP COLUMN customer;

pq: rejected: ALTER TABLE DROP COLUMN will remove all data in that column (sql_safe_updates = true)

sql_safe_updates会话变量必须设置为false

// 设置会话变量
SET sql_safe_updates=false;

注意：删除完成后请还原sql_safe_updates为true

删除时检查依赖：

ALTER TABLE t2 DROP COLUMN name RESTRICT;

  pq: cannot drop column "name" because view "v2" depends on it

删除有对象依赖列（CASCADE）
如果要删除有对象依赖的列，使用CASCADE

  ALTER TABLE t2 DROP COLUMN name CASCADE;

DROP INDEX

DROP INDEX 语句会删除表中的索引。

所需权限

用户必须拥有表的CREATE权限。

查看索引

SHOW INDEX FROM person;

删除索引

格式：DROP INDEX table_name@index_name , 默认生成的索引会有表名作为前缀,如果自定义的索引名,则需要按此格式

DROP INDEX person@person_email_key;

pq: index "company_tel_key" is in use as unique constraint (use CASCADE if you really want to drop it)

如果一个表字段是外键类型时，删除索引时要加关键字 CASCADE。(该索引为默认索引格式,可直接删除)

DROP INDEX cust_info_name_region_idx CASCADE;

DROP VIEW

删除数据库中的view

所需权限

用户必须拥有删除视图的权限

SELECT * FROM information_schema.tables WHERE table_type = 'VIEW';

+---------------+-------------------+--------------------+------------+---------+
| TABLE_CATALOG |   TABLE_SCHEMA    |     TABLE_NAME     | TABLE_TYPE | VERSION |
+---------------+-------------------+--------------------+------------+---------+
| def           | bank              | user_accounts      | VIEW       |       1 |
| def           | bank              | user_emails        | VIEW       |       1 |
+---------------+-------------------+--------------------+------------+---------+
(2 rows)

DROP VIEW bank.user_accounts;

pq: cannot drop view "user_accounts" because view "user_emails" depends on it

强制删除（CASCADE）

警告：
CASCADE删除所有依赖视图而不列出它们，这可能会导致意外和难以恢复的损失。

DROP VIEW bank.user_accounts CASCADE;

DROP SEQUENCE

删除数据库中的序列

所需权限

用户必须拥有要删除序列的DROP权限

DROP SEQUENCE userid_seq;

如果有表依赖此序列不允许删除：

pq: cannot drop sequence userid_seq because other objects depend on it

DROP CONSTRAINT

DROP CONSTRAINT 语法是ALTER TABLE 的一部分，会删除列CHECK和FOREIGN KEY约束。

所需权限

用户必须拥有CREATE权限。

当我们有变更表结构时，hubble会将这个操作注册成一个job，可以通过SHOW JOBS查看。

SHOW CONSTRAINTS FROM orders;

+--------+---------------------------+-------------+-----------+----------------+
| Table  |           Name            |    Type     | Column(s) |    Details     |
+--------+---------------------------+-------------+-----------+----------------+
| orders | fk_customer_ref_customers | FOREIGN KEY | customer  | customers.[id] |
| orders | primary                   | PRIMARY KEY | id        | NULL           |
+--------+---------------------------+-------------+-----------+----------------+

删除约束

ALTER TABLE orders DROP CONSTRAINT fk_customer_ref_customers;

SHOW CONSTRAINTS FROM orders;

+--------+---------+-------------+-----------+---------+
| Table  |  Name   |    Type     | Column(s) | Details |
+--------+---------+-------------+-----------+---------+
| orders | primary | PRIMARY KEY | id        | NULL    |
+--------+---------+-------------+-----------+---------+

删除唯一约束

UNIQUE constraint 不允许使用ALTER TABLE [tablename] DROP CONSTRAINT语句进行删除。通过删除索引及依赖对象方式来下降唯一约束

  table_name | constraint_name  | constraint_type |       details        | validated  
+------------+------------------+-----------------+----------------------+-----------+
  person     | person_email_key | UNIQUE          | UNIQUE (email ASC)   |   true     
  person     | primary          | PRIMARY KEY     | PRIMARY KEY (id ASC) |   true     
(2 rows)

DROP INDEX person@person_email_key CASCADE;

RENAME DATABASE

修改数据库名称

所需权限

要求拥有数据库管理员权限

注意：如果数据库被视图引用，则无法重命名数据库

ALTER DATABASE db1 RENAME TO db2;

RENAME TABLE

修改表名称

所需权限

要求拥有表管理员权限

ALTER TABLE t2 RENAME TO sys_role;

RENAME COLUMN

修改表的字段名称

所需权限

要求拥有表的CREATE权限

SHOW COLUMNS FROM user_tab;
ALTER TABLE user_tab RENAME COLUMN phone_number TO tel_no;

RENAME SEQUENCE

修改数据库的SEQUENCE名称

所需权限

要求拥有库的CREATE权限

查看当前库中的SEQUENCE：

SELECT * FROM information_schema.sequences;

修改SEQUENCE：

ALTER SEQUENCE database_0922.userid_seq RENAME TO database_0922.uid_seq;

注意：
需要确认是否有表依赖与此SEQUENCE，如果依赖不允许重命名。只有没有依赖的SEQUENCE才可以重命名。

RENAME CONSTRAINT

修改表的约束名称

所需权限

要求拥有表的CREATE权限

查询表的约束：

SHOW CONSTRAINTS FROM table_with_index;

修改表的约束名称：

ALTER TABLE table_with_index RENAME CONSTRAINT table_with_index_user_email_key TO unique_email;

SHOW DATABASES

查看集群包含的所有数据库

SHOW DATABASES;

SHOW TABLES

查看一个schema或者数据库中的表和视图

SHOW TABLES;
SHOW TABLES FROM my_hubble_database;

SHOE COLUMNS

查看一张表中列的详细信息，包括列名、类型、默认值以及是否非空

SHOW COLUMNS FROM my_hubble_table;

SHOW INDEX

查看表的索引信息

SHOW INDEX FROM my_hubble_table;

SHOW SCHEMAS

查看一个数据库中所有的schemas信息

SHOW SCHEMAS;

SHOW SEQUENCE

显示当前可用的SEQUENCE

SHOW SEQUENCES;

  sequence_name  
+---------------+
  userid_seq

SHOW CONSTRAINT

显示表的约束情况

SHOW CONSTRAINT from company;

  table_name | constraint_name | constraint_type |       details        | validated  
+------------+-----------------+-----------------+----------------------+-----------+
  company    | primary         | PRIMARY KEY     | PRIMARY KEY (id ASC) |   true     
(1 row)

SHOW PARTITIONS

检索表分区

SHOW PARTITIONS FROM { TABLE <table> | INDEX <index> | DATABASE <database> };

SHOW RANGES

查看range分区情况

SHOW RANGES FROM TABLE customers_by_range;

SHOW CREATE TABLE

显示创建表的语句

SHOW CREATE TABLE my_hubble_table;

SHOW CREATE VIEW

显示创建view的create view语句

SHOW CREATE VIEW my_hubble_viewname;

SHOW CREATE SEQUENCE

显示特定序列的创建语句

SHOW CREATE <sequencesName>;

结果

table_name|                                   create_statement                                   
----------+--------------------------------------------------------------------------------------
userid_seq|CREATE SEQUENCE userid_seq MINVALUE 1 MAXVALUE 9223372036854775807 INCREMENT 1 START 1

DML

SELECT

SELECT语句用于读取和处理现有数据。

所需权限

要求用户拥有表的SELECT权限。

检索所有列

SELECT * FROM accounts;

    id    |   name   | balance | flag  | is_disabled | is_dis  
+---------+----------+---------+-------+-------------+--------+
        6 | zack     |   66666 | false | true        |  NULL   
        8 | zhangsan | 1001.45 | NULL  | NULL        |  NULL   
       99 | feina    |     555 | false | false       |  NULL   
      123 | xuejs    |  123.45 | NULL  | NULL        |  NULL   
      321 | alian    |      76 | false | true        |  NULL   
      876 | jana     |      76 | false | true        |  NULL   
     1234 | lyp      |   88888 | NULL  | NULL        |  NULL   
    98790 | ff       |   66666 | false | true        |  true   
   123456 | xuejs    |  523.45 | NULL  | NULL        |  NULL   
  6843484 | Jack     |   88888 | false | true        |  NULL   
(10 rows)

检索特定列

SELECT name FROM accounts;

    name    
+----------+
  zhangsan  
  xuejs     
  lyp       
  xuejs     
(4 rows)

过滤

单个条件过滤

  SELECT name FROM accounts WHERE name='zhangsan';

      name    
  +----------+
    zhangsan  
 (1 row)

多个条件过滤

  SELECT name FROM accounts WHERE name='zhangsan' AND balanc1e='1001.45';

      name    
  +----------+
    zhangsan  
  (1 row)

列表过滤值

  SELECT name 
    FROM accounts 
    WHERE balance 
    IN ('1001.45', '523.45');

      name    
  +----------+
    zhangsan     
    xuejs     
  (3 rows)

DISTINCT

select DISTINCT name FROM accounts;

   name    
+----------+
  zhangsan  
  xuejs     
  lyp       
(3 rows)

ALL

select DISTINCT name FROM accounts;

    name    
+----------+
  zhangsan  
  xuejs     
  lyp       
  xuejs     
(4 rows)

GROUP BY 聚合函数

SELECT name, sum(balance) 
	FROM accounts 
	GROUP BY name;

    name   |   sum    
+----------+---------+
  feina    |     555  
  alian    |      76  
  Jack     |   88888  
  zack     |   66666  
  zhangsan | 1001.45  
  xuejs    |  646.90  
  jana     |      76  
  lyp      |   88888  
  ff       |   66666  
(9 rows)

HAVING 分组过滤

SELECT name, SUM(balance) 
  FROM accounts 
  GROUP BY name 
  HAVING name='xuejs';

  name  |  sum    
+-------+--------+
  xuejs | 646.90  
(1 row)

INSERT

执行一个单独的多行INSERT语句比多个单行的INSERT语句更快。往一张现有表中插入大量的数据时，建议使用多行INSERT语句代替多个单行INSERT语句。
往一张新表写入数据时，IMPORT语句性能比INSERT语句更好。
在传统的SQL数据库中，生成和回收唯一性的IDs需要使用INSERT和SELECT。在hubble中，用RETURNING分句和INSERT代替。

所需权限

用户必须拥有该表的INSERT权限

插入单行

INSERT INTO accounts (id, name, balance, flag, is_disabled) VALUES 
  (6, 'zack', 66666, 'false', 'true');

SELECT * FROM accounts WHERE id=6;

root@poc-hubble01:15432/xuejs> SELECT * FROM accounts;
    id   |   name   | balance | flag  | is_disabled  
+--------+----------+---------+-------+-------------+
       6 | zack     |   66666 | false | true         
       8 | zhangsan | 1001.45 | NULL  | NULL         
     123 | xuejs    |  123.45 | NULL  | NULL         
    1234 | lyp      |  123.45 | NULL  | NULL         
  123456 | xuejs    |  523.45 | NULL  | NULL         
(5 rows)

插入多行

INSERT INTO accounts (id, name, balance, flag, is_disabled) VALUES 
  (876, 'sinal', 76, 'false', 'true'), (99, 'feina', 55, 'false', 'false');

SELECT * FROM accounts WHERE id IN (876, 99);

  id  | name  | balance | flag  | is_disabled  
+-----+-------+---------+-------+-------------+
   99 | feina |     555 | false | false        
  876 | jana  |      76 | false | true         
(2 rows)

ON CONFLICT 更新值

当遇到唯一的冲突时，Hubble将该行存储在称为excluded的临时表中。以下示例演示在发生冲突时，如何使用临时表excluded中的列来应用更新。

INSERT INTO accounts (id, name, balance, flag, is_disabled) 
  VALUES (6, 'zack', 66666, 'false', 'true') 
  ON CONFLICT (id, name) 
  DO UPDATE SET balancce=excluded.balance;

使用现有值更新

INSERT INTO accounts (id, name,balance, flag, is_disabled) 
  VALUES (99, 'feina', 555, 'false', 'false') 
  ON CONFLICT (id, name) 
  DO UPDATE SET balance=accounts.balance+excluded.balance;

SELECT * FROM accounts WHERE id=99;

  id | name  | balance | flag  | is_disabled  
+----+-------+---------+-------+-------------+
  99 | feina |     555 | false | false        
(1 row)

ON CONFLICT语句下使用WHERE条件

INSERT INTO accounts (id, name,balance, flag, is_disabled) 
  VALUES (99, 'feina', 555, 'false', 'false') 
  ON CONFLICT (id, name) 
  DO UPDATE SET balance=excluded.balance 
  WHERE excluded.balance < accounts.balance;

SELECT * FROM accounts WHERE id=99;

  id | name  | balance | flag  | is_disabled  
+----+-------+---------+-------+-------------+
  99 | feina |     555 | false | false        
(1 row)

ON CONFLICT 不更新值

当约束冲突时，不操作

  INSERT INTO accounts (id, name, balance, flag, is_disabled) VALUES 
    (6, 'zack', 66666, 'false', 'true');

  pq: duplicate key value (id,name)=(6,'zack') violates unique constraint "primary"

  INSERT INTO accounts (id, name, balance, flag, is_disabled) 
    VALLUES (6, 'zack', 66666, 'false', 'true') 
    ON CONFLICT (id, name) 
    DO NOTHING;

多行插入时，不冲突的值可被插入，冲突的不被执行

  INSERT INTO accounts (id, name, balance, flag, is_disabled ) 
    VALUES (321, 'alian', 76, 'false', 'true'), (99, 'feina', 555, 'false', 'false') 
    ON CONFLICT (id, name) 
    DO NOTHING;

DELETE

DELETE语句删除一个表中行的数据

注意
如果你要删除由foreign key constraint引用的行，而且有on delete action，所有依赖这行数据的行将被删除或更新。

所需权限

要执行DELETE操作，用户必须拥有DELETE与SELECT权限

返回删除数据，为了知道删除了哪些数据，可以使用RETURNING字段取回已删除的数据。

返回所有列数据

DELETE FROM tablename WHERE colname < 12333 RETURNING * ;

返回特定列的数据

DELETE FROM tablename WHERE colname < 12333 RETURNING colnameb,colnamec;

UPDATE

UPDATE语句用于更新表中的数据。

所需权限

用户需要拥有表的SELECT和UPDATE权限。

UPDATE accounts SET balance=1234567890 WHERE ID=99;

select * from accounts WHERE id=99;

    id    |   name   |  balance   | flag  | is_disabled | is_dis  
+---------+----------+------------+-------+-------------+--------+ 
       99 | feina    | 1234567890 | false | false       |  NULL   
(1 rows)

使用SELECT语句更新

UPDATE accounts 
  SET (name, balance)=
  (SELECT name, balance FROM accounts WHERE id=123) 
  WHERE id=8;

select * from accounts WHERE id=8;

    id    | name  |  balance   | flag  | is_disabled | is_dis  
+---------+-------+------------+-------+-------------+--------+
        8 | xuejs |     123.45 | NULL  | NULL        |  NULL    
(1 rows)

更新后返回值

UPDATE accounts SET balance=83468 WHERE name='ff' RETURNING id;

   id    
+-------+
  98790  
(1 row)

TRUNCATE

TRUNCATE语句用于从表中删除所有行。

所需权限

用户需要拥有表的DROP权限

清空一张客户表

TRUVCATE dates

select * from dates;

  a | b  
+---+---+
(0 rows)

对于含有外键的表，需要使用CASCADE关键字删除数据。

TRUNCATE papers CASCADE;

select * from papers

  id | paper_key | paper_value | paper_dt  
+----+-----------+-------------+----------+
(0 rows)

UPSERT

UPSERT语句在语义上等效于INSERT ON CONFLICT,但是两者的性能特征可能略有不同。在指定值不违反唯一性约束的情况下，它会插入行，在值违反唯一性榆树的情况下，他会更新。

同INSERT类似，单个多行UPSERT语句比多个单行UPSERT语句快。尽可能使用多行UPSERT而不是多个单行UPSERT语句。

所需权限

用户需要拥有表的 INSERT , SELECT 和 UPDATE 权限。

示例数据：

SELECT * FROM customer;

          id         | name | region |        regis_date          
+--------------------+------+--------+---------------------------+
  705266450755518465 | 李三 | 朝阳   | 2020-01-22 00:00:00+00:00  
  705266450755649537 | 张三 | 海淀   | 2020-01-23 00:00:00+00:00  
  705266450755682305 | 王丽 | 通州   | 2020-01-20 00:00:00+00:00 
  705274504013611009 | 马五 | 重庆   | 2017-10-01 00:00:00+00:00
(4 rows)

INSERT INTO customer (name, region, regis_date)           
  VALUES ('王艳', '朝阳', '2020-01-22');

使用UPSERT语句操作表数据：

当唯一性冲突不在主键中的列上时，UPSERT将不会更新。

上例中id列是主键，但是name列具有UPSERT约束。下面插入name='马超'与非PRIMARY的UNIQUE字段name值冲突，UPSERT将不会生效。

UPSERT INTO customer (name, region,regis_date) 
  VALUES ('马五','安徽','2017-10-01');

pq: duplicate key value (name)=(e'\U00009A6C\U00004E94') violates unique constraint "customer_name_key"

由于name字段创建了唯一性约束，所以更新失败

SELECT * FROM customer WHERE name LIKE '马%';

          id         | name | region |        regis_date          
+--------------------+------+--------+---------------------------+
  705274504013611009 | 马五 | 重庆   | 2017-10-01 00:00:00+00:00  
(1 row)

注意：
如果非主键字段有唯一约束，不能使用UPSERT INTO语法，使用INSERT INTO ... ON CONFLICT ... DO UPDATE ...语句进行操作。

TRANSACTION

事务的优先级分为LOW 、NORMAL 和 HIGH三种。

事务模式分为只读（READ ONLY）和读写（READ WRITE）模式两种。

目前仅支持SERIALIZABLE隔离。

事务流程

流程	说明	执行
BEGIN	`BEGIN`用于启动一个事务，事务成功则执行它包含的所有语句，若失败则不执行。权限取决于事务中每个语句需要的用户权限。你也可以使用`BEGIN`/`BEGIN TRANSACTION`/`START TRANSACTION` 开启一个事务，他们是等效的	`BEGIN;`
SET TRANSACTION	`SET TRANSACTION`语句可以设定事务的优先级请在执行操作数据库的第一个语句之前设置事务优先级。	设定事务优先级： `BEGIN;` `SET TRANSACTION PRIORITY LOW;`
SAVEPOINT	`SAVEPOINT`语句用于注释在当前事务中建立保存点。事务结束后，所有保存点都自动释放。 session变量`force_savepoint_restart`设置为`true`时，可以自定义保存点名称，默认只能使用`hubble_restart`。	`SAVEPOINT trans_restart;`
RELEASE SAVEPOINT	如果事务中的语句生成了任何非重试错误，则`RELEASE SAVEPOINT`等效于`ROLLBACK`，将中止事务并丢弃事务语句中的所有更新。声明`SAVEPOINT`后，使用`RELEASE SAVEPOINT`提交事务，然后使用`COMMIT`为下一个事务准备连接。
COMMIT	`COMMIT`语句用于提交当前事务。使用高级客户端事务重试时，在发出`RELEASE SAVEPOINT`而不是`COMMIT`时，将提交在`SAVEPOINT`之后发出的语句。但是，您仍然必须发出`COMMIT`语句以清除下一个事务的连接。对于不可重试的事务，如果事务中的语句生成任何错误，则`COMMIT`等效于`ROLLBACK`，后者将中止事务并丢弃其语句进行的所有更新。
ROLLBACK	用于中止当前的事务，放弃事务中所有的更新语句。	放弃事务操作： `BEGIN;` `INSERT INTO customers1 (name,region,regis_date) VALUES ('黎明','lishui','2018-10-01');` `ROLLBACK; COMMIT;` 也可以使用`ROLLBACK TO SAVEPOINT` 语句，回退到保存点，进行事务的重新执行。 `ROLLBACK TO SAVEPOINT trans_restart;`

示例：

BEGIN;
SET TRANSACTION PRIORITY HIGH;
SAVEPOINT hubble_restart;
-- DML语句中有执行异常，该事务无法执行;下面2个DML语句不满足INSERT或者UPDATE的执行条件，无法执行成功;事务回退
UPDATE customer SET region = '浙江' WHERE name = '网田';
INSERT INTO customer (name,region,regis_date) VALUES ('郝安', '南宁', '2020-08-08');
RELEASE SAVEPOINT hubble_restart;
COMMIT;
END;

SELECT * FROM customer;

          id         |  name  | region |        regis_date          
+--------------------+--------+--------+---------------------------+
  705266450755518465 | 李三   | 湖北   | 2020-01-22 00:00:00+00:00   
  705266450755682305 | 王丽   | 通州   | 2020-01-20 00:00:00+00:00  
  705269989533876225 | 王艳   | 朝阳   | 2020-01-22 00:00:00+00:00  
  705274504013611009 | 马五   | 安徽   | 2017-10-01 00:00:00+00:00  
  705337581353402369 | 张鹏   | 上海   | 2020-02-02 00:00:00+00:00  
  705337753947865089 | 网田   | 浙江   | 2020-02-02 00:00:00+00:00  
  705339388724019201 | 安浩   | 江苏   | 2020-06-06 00:00:00+00:00  
  705339938462203905 | 郝安   | 南宁   | 2020-08-08 00:00:00+00:00  
(8 rows)

BEGIN;
SET TRANSACTION PRIORITY HIGH;
SAVEPOINT hubble_restart;
INSERT INTO customers1 (NAME,region,regis_date) VALUES ('李安', '贵阳', '2019-09-09');
RELEASE SAVEPOINT hubble_restart;
COMMIT;

SELECT * FROM customer;

          id         |  name  | region |        regis_date          
+--------------------+--------+--------+---------------------------+
  705266450755518465 | 李三   | 湖北   | 2020-01-22 00:00:00+00:00   
  705266450755682305 | 王丽   | 通州   | 2020-01-20 00:00:00+00:00  
  705269989533876225 | 王艳   | 朝阳   | 2020-01-22 00:00:00+00:00  
  705274504013611009 | 马五   | 安徽   | 2017-10-01 00:00:00+00:00  
  705337581353402369 | 张鹏   | 上海   | 2020-02-02 00:00:00+00:00  
  705337753947865089 | 网田   | 浙江   | 2020-02-02 00:00:00+00:00  
  705339388724019201 | 安浩   | 江苏   | 2020-06-06 00:00:00+00:00  
  705339938462203905 | 郝安   | 南宁   | 2020-08-08 00:00:00+00:00  
  705350799508963329 | 李安   | 贵阳   | 2019-09-09 00:00:00+00:00 
(8 rows)

SYSTEM

SHOW SESSIONS

显示会话的详细信息，包括session_id、用户名、登陆方式、ip地址、连接时长、活跃查询等等。

root用户可以查看所有当前登陆用户的session信息；其他用户只能看到自己的session信息。

你也可以使用SHOW CLUSTER SESSIONS。

返回参数说明

参数	含义
node_id	节点ID
session_id	会话ID
user_name	连接的用户名
client_adress	客户端地址和端口
application_name	客户端连接方式
active_queries	活动状态的SQL查询
last_active_query	最近完成的SQL查询
session_start	会话开始时间
oldest_query_start	当前运行时间最长的SQL查询
kv_txn	事务ID

列出集群中活动会话

SHOW SESSIONS;

SHOW CLUSTER SESSIONS;

  node_id |            session_id            | user_name |   client_address   | application_name |    active_queries     | last_active_query |          session_start           |        oldest_query_start         
+---------+----------------------------------+-----------+--------------------+------------------+-----------------------+-------------------+----------------------------------+----------------------------------+
        1 | 16b20c35abefaec10000000000000001 | root      | 192.168.1.11:11373 | $ hubble sql     | SHOW CLUSTER SESSIONS | SHOW database     | 2021-10-28 01:03:59.346244+00:00 | 2021-10-28 01:23:34.070655+00:00  
(1 row)

查询指定用户的session信息

SELECT * FROM [SHOW CLUSTER SESSIONS] WHERE user_name = 'beagledata';

SHOW QUERIES

可以使用SHOW QUERIES或SHOW CLUSTER QUERIES查询当前活跃的SQL的执行信息。

返回参数说明

列名	说明
query_id	查询ID
node_id	当前连接的节点ID
username	当前连接的用户名
start	查询开始的时间
query	查询的SQL语句
client_adress	发起这条查询的客户端地址与端口
application_name	客户端使用的具体名称
distributed	`true`：这条查询会被分布式SQL执行引擎（DistSQL）执行。 `false`：查询会通过本地的SQL引擎执行。 `null`：表示该条查询正在准备，不知道会通过哪种引擎执行。
phase	查询执行的阶段。如果是`if preparing`，表示这条查询正在被解析和生成执行计划。如果是`executing`，表示这条语句正在执行。

SHOW QUERIES;

              query_id             | node_id | user_name |              start               |        query         |   client_address   | application_name | distributed |   phase    
+----------------------------------+---------+-----------+----------------------------------+----------------------+--------------------+------------------+-------------+-----------+
  16b20ecf05fae0a60000000000000001 |       1 | root      | 2021-10-28 01:51:37.010185+00:00 | SHOW CLUSTER QUERIES | 192.168.1.11:11373 | $ hubble sql     |    false    | executing  
(1 row)

查看整个集群的SQL

SHOW CLUSTER QUERIES;

SHOW queries;

              query_id             | node_id | user_name |              start               |        query         |   client_address   | application_name | distributed |   phase    
+----------------------------------+---------+-----------+----------------------------------+----------------------+--------------------+------------------+-------------+-----------+
  16b20f390f56a7a30000000000000001 |       1 | root      | 2021-10-28 01:59:12.433748+00:00 | SHOW CLUSTER QUERIES | 192.168.1.11:11373 | $ hubble sql     |    false    | executing  
(1 row)

查看当前节点SQL

SHOW LOCAL queries;

              query_id             | node_id | user_name |              start               |       query        |   client_address   | application_name | distributed |   phase    
+----------------------------------+---------+-----------+----------------------------------+--------------------+--------------------+------------------+-------------+-----------+
  16b20f60f8928e3f0000000000000001 |       1 | root      | 2021-10-28 02:02:03.850485+00:00 | SHOW LOCAL QUERIES | 192.168.1.11:11373 | $ hubble sql     |    false    | executing  
(1 row)

查看具体的查询任务，可将SHOW QUERIES作为SELECT语句的数据源用于过滤SQL，通过WHERE条件查找。

SELECT * FROM [ SHOW QUERIES ] WHERE node_id=1;

SELECT * FROM [ SHOW QUERIES ] WHERE START < (now()-INTERVAL '3 hours');

SELECT * FROM [ SHOW QUERIES ] 
  WHERE client_address='192.168.100.113:32135' 
  AND user_name='beagledata';

SELECT * FROM [ SHOW QUERIES ] WHERE application_name='hubbleapplication';

查询指定用户的query信息

SELECT * FROM [SHOW CLUSTER QUERIES] WHERE user_name = 'beagledata';

CANCEL QUERY

用于取消正在运行的 SQL查询

所需权限

root用户可以取消任何当前状态为active的查询，而非root用户只取消他们自己当前状态为active的查询。

CANCEL QUERY '15d611a6c5a6307f0000000000000001';

CANCEL QUERY (
    SELECT query_id FROM [SHOW CLUSTER QUERIES] 
    WHERE client_address = '192.168.100.113:32135'
	AND user_name = 'beagledata'
	AND query = 'SELECT * FROM test.beagledata ORDER BY k' limit 1 );

CANCEL QUERY只能用于单个查询query id。如果使用的子查询并返回多个query id，CANCEL QUERY语句将失败。

SHOW ALL

使用SHOW ALL语句可以查看当前session中所有的变量的值：

SHOW ALL

查看一个具体变量的值时，可以使用SHOW SESSION varableName；

例如，查看编码格式：

SHOW SESSION client_encoding;

  client_encoding  
+-----------------+
  UTF8             
(1 row)

SET

使用SET语句可以修改客户端session中变量的值

参数说明

变量名称	说明	默认值	是否可用SHOW查看
application_name
database
default_transaction_isolation
default_transaction_read_only
sql_safe_updates
search_path
server_version_num
timezone
tracing	跟踪记录状态	off	Yes
transaction_isolation	当前事务的隔离级别	SERIALIZABLE	Yes
transaction_priority
transaction_read_only	访问模式
transaction_status		NoTxn	Yes
client_encoding	客户端编码	N/A	No
client_min_messages			No
extra_floatdigits			No
standard_conforming_strings			No

指定当前数据库：

SET database=database_0922;

关于时区
默认时区为UTC,如果需要修改客户端显示的时区，可以使用SET timezone='Asia/Shanghai';，设置后显示的时间即为中国时间。

SHOW CLUSTER SETTING

查看一个或多个clister setting的值，也可以通过SET CLUSTER SETTING进行配置。

所需权限

查看所有Cluster Setting的值，需要admin权限

SHOW ALL CLUSTER SETTINGS;

SHOW CLUSTER SETTING

使用SET CLUSTER SETTING语句可以修改集群中变量的默认值。

所需权限

需要用户拥有admin权限

SET CLUSTER SETTING <variable>=<value>;

例如：

SET CLUSTER SETTING trace.debug.enable = true;

EXPERIMENTAL_AUDIT

语句用来打开或关闭表的SQL审计。

所需权限

只有root用户可以打开表的审计日志

审计日志包含有关针对你的系统执行查询的详细信息，包括

查询语句全文
时间
客户端地址
应用名

ALTER TABLE customers experimental_audit SET READ WRITE;

开启后，将会把所有关于这个表的操作记录在日志中。

ALTER TABLE customers EXPERIMENTAL_AUDIT SET OFF;

JOBS

EXPLAIN

语句返回指定语句的查询计划，用户可以使用explain语句优化查询。

EXPLAIN SELECT * FROM customer;

  tree |    field    |   description     
+------+-------------+------------------+
       | distributed | true              
       | vectorized  | false             
  scan |             |                   
       | table       | customer@primary  
       | spans       | ALL               
(5 rows)

EXPORT

使用EXPORT导出表格数据到CSV文件中。

所需权限

需要用户拥有admin权限。

注意：
此语句不支持事务。
建议导出得治为每个节点都可以访问的公共文件服务。

导出数据到本地，数据存储区在数据目录的extern目录下：

EXPORT INTO 
CSV 'nodelocal:///customers' 
WITH DELIMITER ='|' 
FROM 
    SELECT * FROM users;

  filename | rows | bytes  
+----------+------+-------+
  n5.0.csv |    3 |   414  
(1 row)

说明
Tip：n1.0.csv格式说明
n1：节点id，即文件保存在n1节点
0：文件序号，从0起
csv：文件格式
例如：n3.12.csv为EXPORT任务在n3节点上生成的编号12保存为csv格式的数据

查看本地目录：

# 当前在n1节点
cat ${HUBBLE_HOME}/data1/hubble_data/extern/customers/n1.0.csv

535970470514294785|李三|123@qq.com|chaoyang|2018-10-01
535970470514360321|王二|456@qq.com|haidian|2018-12-01
535970470514524161|钱途|666@qq.com|harbin|2017-10-01
535970470514393089|赵四|789@qq.com|pudong|2016-10-01
535970470514556929|贾六|888@qq.com|shenyang|2017-10-01
535970470514458625|马超|345@qq.com|tianhe|2016-10-01
535970470514589697|刘刘|777@qq.com|tianjin|2017-10-01
535970470514425857|王石|234@qq.com|xuhui|2016-10-01
535970470514491393|张良|457@qq.com|yuexiu|2016-10-01
535970470514622465|胡杨|119@qq.com|zhengzhou|2017-10-01

IMPORT

用于将表格数据导入到单个表中，无法在事务中使用。
成功启动导入后，它将导入注册为job，你可以使用SHOW JOBS查看。
导入开会后，你可以使用PAUSE JOB,RESUME JOB和CANCEL JOB来控制它。
暂停然后恢复IMPORT作业将导致它从头开始重新启动。

所需权限

只有root用户才能运行IMPORT任务。

注意
使用http搭建文件服务，必须使各节点可以访问到待导入文件

IMPORT TABLE customers2 ( 
    ID INT, 
    NAME STRING, 
    email STRING, 
    region STRING, 
    regis_date DATE, 
    PRIMARY KEY ( ID ) 
) CSV DATA ( 'http://IP:Port/customers/n1.0.csv' ) 
WITH 
    DELIMITER = '|'
;

        job_id       |  status   | fraction_completed | rows | index_entries | system_records | bytes  
+--------------------+-----------+--------------------+------+---------------+----------------+-------+
  705884786919309313 | succeeded |                  1 |   13 |             0 |              0 |   507  
(1 row)

IMPORT INTO

可以使用IMPORT INTO语句，导入数据到已经存在的表中。

说明：本文档适用于从hubble到hubble的csv导出导入

步骤：从tt_image1到img 从hubble中导出数据

EXPORT INTO 
CSV 'nodelocal:///customers' 
WITH DELIMITER ='|' 
FROM 
SELECT * FROM tt_image1;

返回结果：

  filename | rows |   bytes    
+----------+------+-----------+
  n1.0.csv |   59 | 477301967

导入一：img表存在的情况,将数据导入到img

IMPORT into img ( a , B  
) CSV DATA ( 'nodelocal:///customers/n4.0.csv' ) 
WITH 
    DELIMITER = '|'
;

返回结果：

       job_id       |  status   | fraction_completed | rows | index_entries | system_records |   bytes    
+--------------------+-----------+--------------------+------+---------------+----------------+-----------+
  739023580166127617 | succeeded |                  1 |   59 |             0 |              0 | 238652345

导入二：img表不存在的情况

IMPORT table  img ( a int  , B  bytes
) CSV DATA ( 'nodelocal:///customers/n1.0.csv' ) 
WITH 
    DELIMITER = '|'
;

返回结果：

       job_id       |  status   | fraction_completed | rows | index_entries | system_records |   bytes    
+--------------------+-----------+--------------------+------+---------------+----------------+-----------+
  739024174094942209 | succeeded |                  1 |   59 |             0 |              0 | 238652345

查看数据量

select count(1) from img;

  count  
+-------+
     59  
(1 row)

所需权限

需要用户拥有admin权限

建表：

CREATE TABLE customers_3 (
    id INT,
    name STRING,
    email STRING,
    region STRING,
    regis_date DATE,
    PRIMARY KEY (id)
);

导入数据：

注意
使用http搭建文件服务，必须使各节点可以访问到待导入文件

IMPORT INTO customers_3 (id, name, email, region, regis_date)
CSV DATA (
    'http://IP:Port/customers/n1.0.csv'
) WITH delimiter = '|' ;

SHOW JOBS

查看集群中所有长时间运行的任务，用于获取到影响集群性能的关键任务，从而帮助用户来控制任务状态。

所需权限

仅root用户执行SHOW JOBS语句

SHOW JOBS;

可将SHOW JOBS作为SELECT语句的数据源用于过滤job，通过WHERE条件来查找需要的job信息。

SELECT * FROM [ SHOW JOBS ] 
WHERE
    job_type = 'RESTORE' 
    AND status IN ( 'running', 'succeeded' ) 
ORDER BY
    created DESC;

CANCEL JOB

用于停止长时间运行的job，包括IMPORT、BACKUP与RESTORE任务。取消RESTORE后，将清理部分还原的数据。可能会对集群性能产生短暂的较小的影响。

所需权限

默认情况下，只有root用户可以使用CANCEL JOB

停止单个任务

CANCEL JOB <job_id>;

停止多个任务

CANCEL JOBS (SELECT job_id FROM [SHOW JOBS] WHERE user_name = 'beagledata');

PAUSE JOB

PAUSE JOB语句可以暂停的IMPORT、BACKUP、RESTORE、USER-CREATED TABLESTATISTICS JOBS和AUTOMATICTABLE STATISTICS JOBS任务。

注意
无法暂停schema的修改

所需权限

用户需要root权限

暂停单个任务

PAUSE JOB <job_id>;

暂停多个任务

PAUSE JOBS (SELECT job_id FROM [SHOW JOBS] WHERE user_name = 'beagledata');

RESUME JOB

RESUME JOB语句可以恢复已暂停的IMPORT、BACKUP或RESTORE任务。

所需权限

用户需要root权限

恢复单个JOB

RESUME job <job_id>;

恢复多个JOB

RESUME JOBS (SELECT job_id FROM [SHOW JOBS] WHERE user_name = 'beagledata');