万能公式
各个表的区别
万能建表语句
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS]
[<database_name>.]<table_name> 指定数据库下创表
[(<column_name> <data_type> [COMMENT '<column_comment>'] 行名 和数据类型，以及注释
[, <column_name> <data_type> ...])]
[COMMENT '<table_comment>']
[PARTITIONED BY (<part_key分区字段> <data_type> 分区
[CLUSTERED BY (<col_name> [, <col_name>...])
[SORTED BY (<col_name> [ASC|DESC] [, <col_name> [ASC|DESC]...])]
INTO <num_buckets> BUCKETS] 分桶排序
[
[ROW FORMAT DELIMITED FIELDS TERMINATED BY '分割符号'] 行分割
[STORED AS (TEXTFILE|ORC|CSVFILE)] 建表类型 如 TEXTFILE ORC CSVFILE
| STORED BY '<storage.handler.class.name>' [WITH SERDEPROPERTIES (<...>)]
] 指定使用的storage handler
[LOCATION '<hdfs_path>'] 指定hdfs上的目录一般创建外表时使用
[TBLPROPERTIES ('<property_name>'='<property_value>', ...)] 表属性，由键值对表示，在建Holodesk表，ORC事务表和CSV表时使用，具体细节请参考“ORC表”，“Holodesk表”和“CSV表”。
表名 使用场景 不适用场景 建表语句 注意事项
TEXT表 text表是文本格式的表，是inceptor的默认格式，通常用于将文本文件中的原始数据导入到inceptor中，并且针对不同的使用场景，可以将其中的数据放入到ORC表或holodesk表中。 数据量较大的情况下 CREATE [TEMPORARY] [EXTERNAL] TABLE <table_name> 表名
(<column_name> <data_type>, <column_name> <data_type>, ...) 行名及行数据类型
[PARTITIONED BY （分区字段） 字段数据类型] 分区
[CLUSTERED BY （分桶字段）INTO 桶数 BUCKETS] 分桶
[ROW FORMAT DELIMITED FIELDS TERMINATED BY '分割符号'] 行分割
[STORED AS TEXTFILE] 建表类型 如TEXTFILE类型
[LOCATION '<hdfs_path>'] 指定hdfs上的一个目录
[TBLPROPERTIES ('<property_name>'='<property_value>', ...)]; 表属性，由键值对表是，在建其他类型表时用到 Inceptor提供两种方式将文本文件中的数据导入TEXT表中
因为text表的统计和查询能力都比较低 1：建外表，直接指向hdfs上的一个目录
无压缩，行存储，只支持批量insert [LOCATION '<hdfs_path>']
主要是对导入文本数据建立过度表 2：创建表后，使用load data（不建议使用）
CSV表 CSV表的数据来源是CSV文件。CSV文件是纯文本文件，文件中包含数据以及分隔符。和TEXT表相似，CSV表最常见的使用场景是用于建外表，将CSV文件中的数据导入Inceptor 不建议再任何的计算场景使用CSV表 CREATE EXTERNAL TABLE <table_name>
(<column_name> <data_type>, <column_name> <data_type>, ...)
STORED AS CSVFILE
[LOCATION '<hdfs_path>']
[TBLPROPERTIES ( 可以指定CSV文件中的分隔符
['field.delim'='<field_delimiter>'], 指定字段分隔符，默认值是","
['line.delim'='<newline_char>'], 指定换行符，默认是“\n”
['serialization.null.format'=''], 指定 NULL值字符，默认为空字段
['quote.delim'='<quote_delimiter>'], 指定类似转译符，默认为" " ",就是出现特殊字符，如上面提及的， \n等被“”包裹，则包裹的内容作为数据处理
['mapreduce.csvinput.encoding'='<encoding_type>'], 指定CSV文件的编码类型
['<property_name>'='<property_value>'], ...)]; TBLPROPERTIES 中可以包含用户自定义属性 在 TBLPROPERTIES 中自定义分隔符和NULL字符
计算时，应该总是将CSV表中的数据用 INSERT INTO 表名 SELECT 语句插入ORC或者Holodesk表
CSV格式不支持在Map阶段对大文件进行切割，所以建议CSV单文件不要超过1G，如果单文件比较大建议使用TXT格式
• 目前，field.delim，line.delim，quote.delim 和 serialization.null.format 的值都只能是单个字符（一个CHAR），不支持多字符。
• 暂时不支持复杂数据类型MAP, STRUCT和ARRAY。
ORC表 ORC表 -- 非分区表
CREATE TABLE <table_name> (<column> <data_type>, <column> <data_type>, ...)
CLUSTERED BY (<bucket_key>) INTO <n> BUCKETS
STORED AS ORC
TBLPROPERTIES ("transactional"="true"); ORC事务表相对与Inceptor中的其他表支持更多CRUD（增删改）语法，包括：
优化的列式存储，轻量级索引，压缩比高， -- 单值分区表（Unique Value Partition）
CREATE TABLE <table_name> (<column> <data_type>, <column> <data_type>, ...)
PARTITIONED BY (<partition_key> <data_type>)
CLUSTERED BY (<bucket_key>) INTO <n> BUCKETS
STORED AS ORC
TBLPROPERTIES ("transactional"="true"); • INSERT INTO … VALUES
只支持insert，incerptor中数仓分析的主要表类型，可由TEXT表生成 -- 范围分区表（Range Partition）
CREATE TABLE <table_name> (<column> <data_type>, <column> <data_type>, ...)
PARTITIONED BY RANGE(<partition_key1> <data_type>, <partition_key2> <data_type>, ...) (
PARTITION [<partition_name_1>] VALUES LESS THAN(<key1_bound_value1>, <key2_bound_value1>, ...),
PARTITION [partition_name_2] VALUES LESS THAN(key1_bound_value2, key2_bound_value2, ...),
...
)
CLUSTERED BY (<bucket_key>) INTO <n> BUCKETS
STORED AS ORC
TBLPROPERTIES ("transactional"="true"); • UPDATE
ORC事务表 • DELETE
由ORC表衍生而出，支持insert、update、delete以及事务操作 • MERGE INTO
多版本文件存储，定期做compaction，10%~20%的性能损失
事务处理
建立事务表 ORC非事务表的建表只需在建表语句中用 STORED AS ORC • 下面将要介绍的ORC事务表CRUD操作必须在 事务处理模式（Transaction Mode）下进行。默认情况下Inceptor关闭Transaction Mode，要对ORC表进行事务处理，需要通过下面的开关打开ORC表对应的Transaction Mode：
SET transaction.type = inceptor;
• Inceptor支持常见的 BEGIN TRANSACTION/COMMIT/ROLLBACK 事务处理语言（TCL）。在不使用这些关键词的情况下默认为自动提交——每个CRUD操作都立即生效。
ORC事务表的建表
• 为表分桶：为了保证增删改过程中的性能，我们要求ORC事务表必须是部分排序或者全局排序的，但是全局排序又过于耗费计算资源，因此我们要求ORC表必须是分桶表。
• 在 TBLPROPERTIES 里需要加上 "transactional"="true"，以标识这是一个要用作事务操作的表。
• 如果表的数据量特别大，建议在分桶的基础上再分区，ORC事务表支持单值分区和范围分区。
HOLODESK表 创建普通表 它着力于交互式分析中即时查询效率的提高且能够保证扩展性与稳定性。Transwarp Holodesk 通过 Zookeeper 来管理元数据，从而避免因为单点故障而导致的数据丢失，数据checkpoint 在 HDFS 中。服务在故障恢复之后，Holodesk 能够通过 Zookeeper 中的信息自动重建数据与索引，因此有很高的可靠性
• 当机器拥有很大的内存或者部署了SSD时。 机器的内存和SSD没有时，或者比较小的时候 CREATE TABLE <holodesk_table_name> (
<column_name1> <DATATYPE1>,
<column_name2> <DATATYPE2>,
<column_name3> <DATATYPE3>,
...
) STORED AS HOLODESK;
• 过滤高的场景，包括单表扫描和多表MapJoin等。
• 聚合率高的场景，例如GROUP BY之后，信息被大量聚合。 CREATE TABLE <holodesk_table_name> STORED AS HOLODESK
AS SELECT ... FROM ...; 在使用Holodesk之前，必须在Tranwarp Manager的Inceptor设置页面中合理配置以下三个相关资源：ngmr.fastdisk.dir、ngmr.fastdisk.size以及ngmr.localdir，分别代表Holodesk的实际所位置、Holodesk占用资源的比例、Shuffle数据的本地存放位置。
过滤率高或聚合率高的场景，我们提供Cube、Index两种手段来帮助优化业务执行。在运用Index以及Cube加速的过程中，Index与Cube的定义是关键。Index字段与Cube组合字段的选取，主要来自于对业务逻辑中维度的取舍，以及对度量条件的选择等。为达到较好的优化效果，Index字段通常为过滤字段，Cube字段通常为聚合字段 如gourp by 则用Cube where的过滤条件，则用Index
基于内存的/SSD的分布式列式存储，内含索引和cube，压缩率比orc略低，，只支持insert操作 创建Index的语法。
主要用于数据交互式分析，以及报表工具的实时展现 CREATE TABLE <holodesk_table_name>(
<column_name1> <DATATYPE1>,
<column_name2> <DATATYPE2>,
...
) STORED AS HOLODESK
TBLPROPERTIES (
'holodesk.index' = '<column_name_index1>,<column_name_index2>,...'
);
创建Cube的语法。
CREATE TABLE <holodesk_table_name>(
<column_name1> <DATATYPE1>,
<column_name2> <DATATYPE2>,
...
) STORED AS HOLODESK
TBLPROPERTIES (
'holodesk.dimension' = '<column_name_a_dim1>, <column_name_a_dim2>, ... |
<column_name_b_dim1>, <column_name_b_dim2>, ...|
...'
) ;
表名 使用场景 不适用场景 建表语句 例子 注意事项
TEXT表 text表是文本格式的表，是inceptor的默认格式，通常用于将文本文件中的原始数据导入到inceptor中，并且针对不同的使用场景，可以将其中的数据放入到ORC表或holodesk表中。 数据量较大的情况下 CREATE [TEMPORARY] [EXTERNAL] TABLE <table_name> 表名
(<column_name> <data_type>, <column_name> <data_type>, ...) 行名及行数据类型
[PARTITIONED BY （分区字段） 字段数据类型] 分区
[CLUSTERED BY （分桶字段）INTO 桶数 BUCKETS] 分桶
[ROW FORMAT DELIMITED FIELDS TERMINATED BY '分割符号'] 行分割
[STORED AS TEXTFILE] 建表类型 如TEXTFILE类型
[LOCATION '<hdfs_path>'] 指定hdfs上的一个目录
[TBLPROPERTIES ('<property_name>'='<property_value>', ...)]; 表属性，由键值对表是，在建其他类型表时用到 已知有一份原始数据在HDFS上的目录下，内容如下： Inceptor提供两种方式将文本文件中的数据导入TEXT表中
因为text表的统计和查询能力都比较低 1,Alice,2013:120000|2014:125000|2015:130000
2,Bob,2014:150000|2015:160000 1：建外表，直接指向hdfs上的一个目录
数据记录了员工的ID、名字和历年的薪酬状况，列分隔符为“,”。我们需要把历年的薪酬状况放在一个MAP中，以年份为键、薪酬为值。键值对之间的分隔符为“|”；每个键值对之内，键和值之间的分隔符为“:”。以这份数据为源建表，MAP中键值对之间的分隔符由 COLLECTION ITEMS TERMINATED BY 指定，键值对内键和值之间的分隔符由 MAP KEYS TERMINATED BY 指定： [LOCATION '<hdfs_path>']
无压缩，行存储，只支持批量insert DROP TABLE IF EXISTS employee_salary;
CREATE TABLE employee_salary (
id INT, name STRING, salary MAP<STRING, DOUBLE>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '|'
MAP KEYS TERMINATED BY ':'
LOCATION '/manual_crud_tmp/employee_salary';
SELECT * FROM employee_salary; 2：创建表后，使用load data（不建议使用）
主要是对导入文本数据建立过度表
CSV表 CSV表的数据来源是CSV文件。CSV文件是纯文本文件，文件中包含数据以及分隔符。和TEXT表相似，CSV表最常见的使用场景是用于建外表，将CSV文件中的数据导入Inceptor 不建议再任何的计算场景使用CSV表 CREATE EXTERNAL TABLE <table_name>
(<column_name> <data_type>, <column_name> <data_type>, ...)
STORED AS CSVFILE
[LOCATION '<hdfs_path>']
[TBLPROPERTIES ( 可以指定CSV文件中的分隔符
['field.delim'='<field_delimiter>'], 指定字段分隔符，默认值是","
['line.delim'='<newline_char>'], 指定换行符，默认是“\n”
['serialization.null.format'=''], 指定 NULL值字符，默认为空字段
['quote.delim'='<quote_delimiter>'], 指定类似转译符，默认为" " ",就是出现特殊字符，如上面提及的， \n等被“”包裹，则包裹的内容作为数据处理
['mapreduce.csvinput.encoding'='<encoding_type>'], 指定CSV文件的编码类型
['<property_name>'='<property_value>'], ...)]; TBLPROPERTIES 中可以包含用户自定义属性 假设我们有这样一份CSV文件，在本地目录下，文件内容如下： 在 TBLPROPERTIES 中自定义分隔符和NULL字符
计算时，应该总是将CSV表中的数据用 INSERT INTO 表名 SELECT 语句插入ORC或者Holodesk表 aaa,,ccc,"",ddd
fff,,hhh,"",jjj
对这个文件这样建外表：（执行LOAD DATA之前请注意如何访问本地文件）
CSV格式不支持在Map阶段对大文件进行切割，所以建议CSV单文件不要超过1G，如果单文件比较大建议使用TXT格式 DROP TABLE IF EXISTS csv_table_2;
CREATE EXTERNAL TABLE csv_table_2
(
col1 STRING,
col2 STRING,
col3 STRING,
col4 STRING,
col5 STRING
)
STORED AS CSVFILE
TBLPROPERTIES('serialization.null.format'='');
LOAD DATA LOCAL INPATH '/LOCALWORKSPACE/manual-ut/manual_data/manual_crud_tmp/csv2.txt' OVERWRITE INTO TABLE csv_table_2;
根据这里 TBLPROPERTIES 中的设置，Inceptor会这样处理原文件中的数据： • 目前，field.delim，line.delim，quote.delim 和 serialization.null.format 的值都只能是单个字符（一个CHAR），不支持多字符。
• 文件中有两条记录，第一条记录的结尾为 ddd。 • 暂时不支持复杂数据类型MAP, STRUCT和ARRAY。
• 每条记录有五个字段：字段间用“,”隔开。
• 第一个字段后出现了两个连续的“,”，所以第二个字段为NULL。
• 第四个字段中虽然出现了NULL字符，但是NULL字符包裹在一对“"”中，所以第四个字段是空字段（长度为零的字段），但是不是NULL值。
ORC表 ORC表 -- 非分区表
CREATE TABLE <table_name> (<column> <data_type>, <column> <data_type>, ...)
CLUSTERED BY (<bucket_key>) INTO <n> BUCKETS
STORED AS ORC
TBLPROPERTIES ("transactional"="true"); SET transaction.type=inceptor; ORC事务表相对与Inceptor中的其他表支持更多CRUD（增删改）语法，包括：
优化的列式存储，轻量级索引，压缩比高， -- 单值分区表（Unique Value Partition）
CREATE TABLE <table_name> (<column> <data_type>, <column> <data_type>, ...)
PARTITIONED BY (<partition_key> <data_type>)
CLUSTERED BY (<bucket_key>) INTO <n> BUCKETS
STORED AS ORC
TBLPROPERTIES ("transactional"="true"); -- 创建非分区ORC表。
DROP TABLE IF EXISTS ta;
CREATE TABLE ta (name STRING, age INT)
CLUSTERED BY (age) INTO 2 BUCKETS
STORED AS ORC TBLPROPERTIES ("transactional"="true"); • INSERT INTO … VALUES
只支持insert，incerptor中数仓分析的主要表类型，可由TEXT表生成 -- 范围分区表（Range Partition）
CREATE TABLE <table_name> (<column> <data_type>, <column> <data_type>, ...)
PARTITIONED BY RANGE(<partition_key1> <data_type>, <partition_key2> <data_type>, ...) (
PARTITION [<partition_name_1>] VALUES LESS THAN(<key1_bound_value1>, <key2_bound_value1>, ...),
PARTITION [partition_name_2] VALUES LESS THAN(key1_bound_value2, key2_bound_value2, ...),
...
)
CLUSTERED BY (<bucket_key>) INTO <n> BUCKETS
STORED AS ORC
TBLPROPERTIES ("transactional"="true"); -- 创建非分区ORC表。
DROP TABLE IF EXISTS tg;
CREATE TABLE tg (name STRING, gpa DOUBLE) CLUSTERED BY (name) INTO 4 BUCKETS STORED AS ORC TBLPROPERTIES ("transactional"="true"); • UPDATE
ORC事务表 -- 创建单值分区ORC表。
DROP TABLE IF EXISTS test;
CREATE TABLE test (a INT, b STRING, c DOUBLE) PARTITIONED BY (date STRING) CLUSTERED BY (c) INTO 8 BUCKETS STORED AS ORC TBLPROPERTIES ("transactional"="true"); • DELETE
由ORC表衍生而出，支持insert、update、delete以及事务操作 -- 创建范围分区ORC表。
DROP TABLE IF EXISTS t5;
CREATE TABLE t5(id INT, value INT) PARTITIONED BY RANGE(amount INT)
(
PARTITION less1 VALUES LESS THAN (1),
PARTITION less10 VALUES LESS THAN (10),
PARTITION less100 VALUES LESS THAN (100)
)
CLUSTERED BY (id) INTO 5 BUCKETS STORED AS ORC TBLPROPERTIES ("transactional"="true"); • MERGE INTO
多版本文件存储，定期做compaction，10%~20%的性能损失
事务处理
• 下面将要介绍的ORC事务表CRUD操作必须在 事务处理模式（Transaction Mode）下进行。默认情况下Inceptor关闭Transaction Mode，要对ORC表进行事务处理，需要通过下面的开关打开ORC表对应的Transaction Mode：
SET transaction.type = inceptor;
建立事务表 ORC非事务表的建表只需在建表语句中用 STORED AS ORC • Inceptor支持常见的 BEGIN TRANSACTION/COMMIT/ROLLBACK 事务处理语言（TCL）。在不使用这些关键词的情况下默认为自动提交——每个CRUD操作都立即生效。
ORC事务表的建表
• 为表分桶：为了保证增删改过程中的性能，我们要求ORC事务表必须是部分排序或者全局排序的，但是全局排序又过于耗费计算资源，因此我们要求ORC表必须是分桶表。
• 在 TBLPROPERTIES 里需要加上 "transactional"="true"，以标识这是一个要用作事务操作的表。
• 如果表的数据量特别大，建议在分桶的基础上再分区，ORC事务表支持单值分区和范围分区。
HOLODESK表 • 当机器拥有很大的内存或者部署了SSD时。 机器的内存和SSD没有时，或者比较小的时候 创建普通表 假设对于holodeskEmployee表将有如下的查询语句： 它着力于交互式分析中即时查询效率的提高且能够保证扩展性与稳定性。Transwarp Holodesk 通过 Zookeeper 来管理元数据，从而避免因为单点故障而导致的数据丢失，数据checkpoint 在 HDFS 中。服务在故障恢复之后，Holodesk 能够通过 Zookeeper 中的信息自动重建数据与索引，因此有很高的可靠性
• 过滤高的场景，包括单表扫描和多表MapJoin等。 CREATE TABLE <holodesk_table_name> (
<column_name1> <DATATYPE1>,
<column_name2> <DATATYPE2>,
<column_name3> <DATATYPE3>,
...
) STORED AS HOLODESK; SELECT Sex, Region, COUNT(ID) AS cnt, AVG (Salary) AS avg_salary
FROM holodeskEmployee
WHERE Department = 'IT'
GROUP BY Sex, Region
ORDER BY Sex, Region;
• 聚合率高的场景，例如GROUP BY之后，信息被大量聚合。 如果该语句对holodeskEmployee的聚合力与过滤力较高，建议在建表时为“Department”字段建立Index，并根据（“Sex”, “Region”）建立Cube，可提升查询效率。建表语句如下：
CREATE TABLE <holodesk_table_name> STORED AS HOLODESK
AS SELECT ... FROM ...; DROP TABLE IF EXISTS holodeskEmployee;
CREATE TABLE holodeskEmployee
STORED AS HOLODESK
TBLPROPERTIES (
'holodesk.index' = 'Department',
'holodesk.dimension' = 'Sex, Region'
) AS SELECT * FROM employee1; 在使用Holodesk之前，必须在Tranwarp Manager的Inceptor设置页面中合理配置以下三个相关资源：ngmr.fastdisk.dir、ngmr.fastdisk.size以及ngmr.localdir，分别代表Holodesk的实际所位置、Holodesk占用资源的比例、Shuffle数据的本地存放位置。
过滤率高或聚合率高的场景，我们提供Cube、Index两种手段来帮助优化业务执行。在运用Index以及Cube加速的过程中，Index与Cube的定义是关键。Index字段与Cube组合字段的选取，主要来自于对业务逻辑中维度的取舍，以及对度量条件的选择等。为达到较好的优化效果，Index字段通常为过滤字段，Cube字段通常为聚合字段 如gourp by 则用Cube where的过滤条件，则用Index
创建Index的语法。
基于内存的/SSD的分布式列式存储，内含索引和cube，压缩率比orc略低，，只支持insert操作 CREATE TABLE <holodesk_table_name>(
<column_name1> <DATATYPE1>,
<column_name2> <DATATYPE2>,
...
) STORED AS HOLODESK
TBLPROPERTIES (
'holodesk.index' = '<column_name_index1>,<column_name_index2>,...'
);
主要用于数据交互式分析，以及报表工具的实时展现
创建Cube的语法。
CREATE TABLE <holodesk_table_name>(
<column_name1> <DATATYPE1>,
<column_name2> <DATATYPE2>,
...
) STORED AS HOLODESK
TBLPROPERTIES (
'holodesk.dimension' = '<column_name_a_dim1>, <column_name_a_dim2>, ... |
<column_name_b_dim1>, <column_name_b_dim2>, ...|
...'
) ;
扫一扫
1293
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
