目录
- 1. 创建、更新、删除schema
- 2. 创建table
-
- 2.1 表和列with子句的使用
- 2.2 复制现有的表结构
- 2.3 从查询结果中新建表
- 2.4 修改表和删除表
- 3. 视图
1. 创建、更新、删除schema
下面以Hive例如，解释
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> 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中没有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举例说明
- external_location：Hive存储外表数据的文件位置
- 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将数据源中的视图当作表一样处理。这样就可以将一些复杂的查询下推到数据源进行处理