Presto/Trino中schema、table、视图的操作

1. 创建、更新、删除schema

下面以Hive为例，进行讲解

创建schema

trino> create schema if not exists hive.test_db with (location = 'hdfs://nnha/user/hive/warehouse/test_db.db');
CREATE SCHEMA
trino>

• 可以添加多个键值对属性，用逗号分隔

可以查询可用的键值对属性列表

trino> 
trino> select * from system.metadata.schema_properties;
 catalog_name | property_name | default_value |  type   |          description          
--------------+---------------+---------------+---------+-------------------------------
 hive         | location      |               | varchar | Base file system location URI 
(1 row)

Query 20220209_125838_00067_jx84g, FINISHED, 2 nodes
Splits: 2 total, 2 done (100.00%)
0.49 [1 rows, 48B] [2 rows/s, 97B/s]

trino>

重命名schema

trino> alter schema hive.test_db rename to test_db_new;
Query 20220209_130228_00070_jx84g failed: Hive metastore does not support renaming schemas

trino> 

可以看出Hive是不支持database重命名的

删除schema
前提是schema中没有table

trino> 
trino> drop schema hive.test_db;
DROP SCHEMA
trino>

2. 创建table

创建表的语法如下：

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

表创建就可以通过insert into插入数据了，删除数据用delete from table where condition

2.1 表和列with子句的使用

我们以Hive为例进行讲解

Hive连接器支持的常用表属性如下

1. external_location：Hive外部表数据储存的文件位置
2. format：底层数据储存的文件格式，如ORC、AVRO、parquet等

更多表属性和列属性，可以通过如下方式进行查看

trino> 
trino> select * from system.metadata.table_properties;
 catalog_name |          property_name          | default_value |      type      |                                                          description                    
--------------+---------------------------------+---------------+----------------+-----------------------------------------------------------------------------------------
......省略部分......
 blackhole    | rows_per_page                   | 0             | integer        | Number of rows per each page generated by this table                                    
 blackhole    | split_count                     | 0             | integer        | Number of splits generated by this table                                                
 hive         | auto_purge                      |               | boolean        | Skip trash when table or partition is deleted                                           
 hive         | avro_schema_url                 |               | varchar        | URI pointing to Avro schema for the table                                               
......省略部分......
trino> 
trino> select * from system.metadata.table_properties;
 catalog_name |          property_name          | default_value |      type      |                                                          description                    
--------------+---------------------------------+---------------+----------------+-----------------------------------------------------------------------------------------
......省略部分......                              
 blackhole    | rows_per_page                   | 0             | integer        | Number of rows per each page generated by this table                                    
 blackhole    | split_count                     | 0             | integer        | Number of splits generated by this table                                                
 hive         | auto_purge                      |               | boolean        | Skip trash when table or partition is deleted                                           
 hive         | avro_schema_url                 |               | varchar        | URI pointing to Avro schema for the table   
 ......省略部分......
 trino>

Hive建表语句如下：

0: jdbc:hive2://hive1:10000> 
0: jdbc:hive2://hive1:10000> create external table test_external_tb(
. . . . . . . . . . . . . .> user_id bigint,
. . . . . . . . . . . . . .> user_name string,
. . . . . . . . . . . . . .> birthday date,
. . . . . . . . . . . . . .> country string
. . . . . . . . . . . . . .> ) location 'hdfs://nnha/user/hive/warehouse/test_db.db/test_external_tb';
0: jdbc:hive2://hive1:10000> 

Trino建表语句如下，可以看到和Hive的建表语句很相似，但Trino的SQL更符合SQL标准

trino> 
trino> create table hive.test_db.test_external_tb(
    -> user_id bigint,
    -> user_name varchar,
    -> birthday date,
    -> country varchar
    -> ) with (external_location = 'hdfs://nnha/user/hive/warehouse/test_db.db/test_external_tb');
CREATE TABLE
trino>

2.2 复制现有的表结构

表和列的属性默认不会被复制，即本示例默认不会复制test_external_tb表的external_location表属性。可以使用SQL including properties进行复制

trino> show create table hive.test_db.test_external_tb;
                                     Create Table                                      
---------------------------------------------------------------------------------------
 CREATE TABLE hive.test_db.test_external_tb (                                          
    user_id bigint,                                                                    
    user_name varchar,                                                                 
    birthday date,                                                                     
    country varchar                                                                    
 )                                                                                     
 WITH (                                                                                
    external_location = 'hdfs://nnha/user/hive/warehouse/test_db.db/test_external_tb', 
    format = 'ORC'                                                                     
 )                                                                                     
(1 row)

Query 20220209_222646_00136_jx84g, FINISHED, 1 node
Splits: 1 total, 1 done (100.00%)
0.24 [0 rows, 0B] [0 rows/s, 0B/s]

trino>
trino> 
trino> create table hive.test_db.copy_table_structure(
    -> like hive.test_db.test_external_tb including properties,
    -> comment varchar
    -> ) with (
    -> bucketed_by = array['user_id', 'user_name'],
    -> bucket_count = 50
    -> );
CREATE TABLE
trino> 
trino> show create table hive.test_db.copy_table_structure;
                                     Create Table                                      
---------------------------------------------------------------------------------------
 CREATE TABLE hive.test_db.copy_table_structure (                                      
    user_id bigint,                                                                    
    user_name varchar,                                                                 
    birthday date,                                                                     
    country varchar,                                                                   
    comment varchar                                                                    
 )                                                                                     
 WITH (                                                                                
    bucket_count = 50,                                                                 
    bucketed_by = ARRAY['user_id','user_name'],                                        
    bucketing_version = 1,                                                             
    external_location = 'hdfs://nnha/user/hive/warehouse/test_db.db/test_external_tb', 
    format = 'ORC',                                                                    
    sorted_by = ARRAY[]                                                                
 )                                                                                     
(1 row)

Query 20220209_223040_00139_jx84g, FINISHED, 1 node
Splits: 1 total, 1 done (100.00%)
0.31 [0 rows, 0B] [0 rows/s, 0B/s]

trino>

2.3 从查询结果中新建表

语法如下：

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

其中默认是with data，如果只想新建表结构，而不导入数据，则可以使用with no data

如下面示例

trino> 
trino> create table hive.test_db.create_table_insert ( 
    -> user_id,                           
    -> user_name,                        
    -> birthday,                            
    -> country                           
    -> ) as select * from hive.test_db.test_partition_tb;
CREATE TABLE: 2 rows

Query 20220209_103421_00011_jx84g, FINISHED, 2 nodes
Splits: 6 total, 6 done (100.00%)
2.42 [2 rows, 732B] [0 rows/s, 302B/s]

trino>

2.4 修改表和删除表

包含重命名表、添加列、删除列、重命名列等，语法如下：

ALTER TABLE [ IF EXISTS ] name RENAME TO new_name

ALTER TABLE [ IF EXISTS ] name ADD COLUMN [ IF NOT EXISTS ] column_name data_type
  [ NOT NULL ] [ COMMENT comment ]
  [ WITH ( property_name = expression [, ...] ) ]
  
ALTER TABLE [ IF EXISTS ] name DROP COLUMN [ IF EXISTS ] column_name

ALTER TABLE [ IF EXISTS ] name RENAME COLUMN [ IF EXISTS ] old_name TO new_name

DROP TABLE  [ IF EXISTS ] table_name

有的连接器默认不允许修改表。有的连接器删除表但是不删除数据

3. 视图

Trino不支持视图。但可以在数据源中创建视图，Trino将数据源中的视图当作表一样处理。这样就可以将一些复杂的查询下推到数据源进行处理