Hive数据定义语言(DDL)
Hive的官方提供的建表语法如下所示:LanguageManual DDL
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name -- (Note: TEMPORARY available in Hive 0.14.0 and later)
[(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[SKEWED BY (col_name, col_name, ...) -- (Note: Available in Hive 0.10.0 and later)]
ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
[STORED AS DIRECTORIES]
[
[ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] -- (Note: Available in Hive 0.6.0 and later)
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)] -- (Note: Available in Hive 0.6.0 and later)
[AS select_statement]; -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
LIKE existing_table_or_view_name
[LOCATION hdfs_path];
data_type
: primitive_type
| array_type
| map_type
| struct_type
| union_type -- (Note: Available in Hive 0.7.0 and later)
primitive_type
: TINYINT
| SMALLINT
| INT
| BIGINT
| BOOLEAN
| FLOAT
| DOUBLE
| DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
| STRING
| BINARY -- (Note: Available in Hive 0.8.0 and later)
| TIMESTAMP -- (Note: Available in Hive 0.8.0 and later)
| DECIMAL -- (Note: Available in Hive 0.11.0 and later)
| DECIMAL(precision, scale) -- (Note: Available in Hive 0.13.0 and later)
| DATE -- (Note: Available in Hive 0.12.0 and later)
| VARCHAR -- (Note: Available in Hive 0.12.0 and later)
| CHAR -- (Note: Available in Hive 0.13.0 and later)
array_type
: ARRAY < data_type >
map_type
: MAP < primitive_type, data_type >
struct_type
: STRUCT < col_name : data_type [COMMENT col_comment], ...>
union_type
: UNIONTYPE < data_type, data_type, ... > -- (Note: Available in Hive 0.7.0 and later)
row_format
: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
[NULL DEFINED AS char] -- (Note: Available in Hive 0.13 and later)
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
file_format:
: SEQUENCEFILE
| TEXTFILE -- (Default, depending on hive.default.fileformat configuration)
| RCFILE -- (Note: Available in Hive 0.6.0 and later)
| ORC -- (Note: Available in Hive 0.11.0 and later)
| PARQUET -- (Note: Available in Hive 0.13.0 and later)
| AVRO -- (Note: Available in Hive 0.14.0 and later)
| JSONFILE -- (Note: Available in Hive 4.0.0 and later)
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
constraint_specification:
: [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ]
[, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE
1.创建内部表语句
CREATE TABLE employees(
name string,
salary float,
subordinates array<string>,
deductions map<string,float>,
address struct<street:string,city:string,state:string,zip:int>)
PARTITIONED BY ( country string, state string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
COLLECTION ITEMS TERMINATED BY '\002'
MAP KEYS TERMINATED BY '\003'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;
- ROW FORMAT DELIMITED: 保留关键字
- FIELDS TERMINATED BY: 列分隔符(^A,一般写成‘\001’)
- COLLECTION ITEMS TERMINATED BY : 元素间分隔符(^B,一般写成‘\002’)
- MAP KEYS TERMINATED BY: key/value对间的分隔符(^C,一般写成‘\003’)
- LINES TERMINATED BY: 行分隔符(\n)
2.创建外部表
- 文件存储在/user/hivetest/logs目录下:
10180,2019-01-01 20:30:10,2019-01-01,1241,1101003
10180,2019-01-02 20:40:10,2019-01-02,1221,1101123
10180,2019-01-03 20:50:16,2019-01-03,1341,1101014
10180,2019-01-04 20:40:20,2019-01-04,1051,1101869
- 创建外部表,指定文件存储路径:
CREATE EXTERNAL TABLE tmp_logs(
domain_id INT ,
log_time STRING ,
log_date STRING,
log_type INT,
uin BIGINT )
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',‘
STORED AS textfile
LOCATION '/user/hivetest/logs';
3.使用不同的文件格式
- 在create/alter表的时候,可以为表以及分区的文件指定不同的格式
- Storage Formats
- stored as file_format
- stored as parquet(不需要指定分隔符,直接解析)
- stored as orc(不需要指定分隔符,直接解析)
- stored as sequencefile
- stored as avro
- stored as textfile(对于文本文件需要指定分隔符)
- stored by ‘storage.handler.class.name’ with serdeproperties(…)
- 自定义的format,支持更多的存储格式
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler’ WITH SERDEPROPERTIES ("hbase.columns.mapping" =":key,a:b,a:c,d:e");
- 自定义的format,支持更多的存储格式
- stored as file_format
- Row Formats
- SerDe
- 对于文本格式还需要指定分隔符
- 行分隔符,列分隔符
- 集合元素分隔符,kv分隔符
4.行存储与列存储
行式存储 | 列式存储 | |
---|---|---|
优点 | 数据被保存在一起 INSERT/UPDATE容易 | 查询时只有涉及到的列会被读取 任何列都能作为索引 |
缺点 | 选择时即使只涉及某几列,所有数据都会被读取 | 选择完成时,被选择的列要重新组装 INSERT/UPDATE麻烦 |
-
传统行式存储(text、sequence file)
- 数据是按行存储的
- 没有索引的查询需要使用大量IO
- 建立索引和物化视图需要花费大量的时间和资源
- 面向查询的需求,数据库必须被大量膨胀才能满足性能要求
-
列式存储(ORCFile、Parquet)
- 数据是按列存储-每一列单独存放
- 数据即是索引
- 只访问查询涉及的列-大量降低系统IO
- 每一列由一个线索来处理-查询的并发处理
- 数据类型一致,数据特征相似-高效压缩
-
各个存储类型的存储空间对比
5.如何创建带压缩的ORC表:步骤
-
创建临时表
- 文件格式如下所示:
10180,2019-01-01 20:30:10,2019-01-01,1241,1101003 10180,2019-01-02 20:40:10,2019-01-02,1221,1101123 10180,2019-01-03 20:50:16,2019-01-03,1341,1101014 10180,2019-01-04 20:40:20,2019-01-04,1051,1101869
- 创建临时表
CREATE EXTERNAL TABLE tmp_logs( domain_id INT , log_time STRING , log_date STRING, log_type INT, uin BIGINT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',‘ STORED AS textfile LOCATION '/user/hivetest/logs';
- 文件格式如下所示:
-
创建ORC表
-
将临时表中的数据导入到ORC格式的表中
LOAD DATA INPATH ‘/ngnix/logs/2016011206’ OVERWRITE INTO TABLE tmp_logs;
-
将临时表中数据导入到orc格式表中
CREATE TABLE logs( domain_id INT , log_date STRING, log_type INT, uin BIGINT ) PARTITIONED BY(log_time string) STORED AS ORC tblproperties ("orc.compress"="SNAPPY");
INSERT INTO TABLE logs PARTITION(dt=‘2016-01-12-06’) SELECT * FROM tmp_logs;
-
-
常用的压缩算法