Hive DDL & DML
官方文档：https://cwiki.apache.org/confluence/display/Hive/GettingStarted
一、DDL
1.1 Create/Drop/Alter/Use Database
创建数据库：
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
ps: Hive的数据（内部表）都是存储在HDFS上的，默认有一个根目录，在hive-site.xml中，由参数hive.metastore.warehouse.dir指定。默认值为/user/hive/warehouse.
例子：
CREATE DATABASE IF NOT EXISTS test_db
COMMENT '演示'
LOCATION 'hdfs://127.0.0.1:9000/user/hive/test_db.db';
删除数据库：
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
修改数据库：
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role; -- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)
ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path; -- (Note: Hive 2.2.1, 2.4.0 and later)
使用数据库：
USE database_name;
USE DEFAULT;
1.2 Create/Drop/Truncate Table
1.2.1 创建 table
//这里面 介绍了 三种 创建表的
1. 普通的 create
2. create as select
3.create like
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
[(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]
]
[LOCATION hdfs_path]
[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
===============================================================================
TEXTFILE：TextFile格式是默认文件格式。 数据不做压缩，磁盘开销大，数据解析开销也大。 可结合 Gzip、 Bzip2 使用(系统自动检查 ，执行查询时自动解压〉。
SEQUENCEFILE：SequenceFile格式是 Hadoop API提供的一种二进制文件支持，它将数据以二进制的形式序 列化到文件中 。 此文件格式具有使用方便、可分割、可压缩的特点。 SequenceFile 支持 三种压缩选择 : NONE、阻CORD、BLOCK。 阻 CORD 压缩率低 ，一般建议使用 BLOCK压缩 。
RCFILE：RCFile 格式是一种行列存储相结合的存储方式 。首先，将数据按行分块，保证同一个record存储在同一个块上，避免读一个记录需要读取多个块; 其次，块数据列式存储有利于数据压缩和快速的列存取。
ORC：ORC 的全称是 Optimized Row Columnar。 ORC 文件格式是一种 Hadoop 生态圈中的列式 存储格式。
Managed and External Tables----- 内部表 和外部表
内部表 和外部表的区别 就是 ： 内部表删除 了之后，元数据和 文件位置对应的文件都删除； 外部表删除时， 只删除源数据，文件位置对应的数据不会删除。
//内部表
create table emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
LOCATION 'hdfs://127.0.0.1:9000/user/hive/test_db.db';
//外部表：
create external table ex_emp(
shop_id int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION 'hdfs://127.0.0.1:9000/user/hive/external/';
hdfs dfs -rm /user/hive/external/
hadoop fs -put 3.txt /user/hive/external/
Storage Formats —存储格式
Row Formats & SerDe — 行格式
Partitioned Tables ----分区表
可以使用PARTITIONED BY子句创建分区表。表可以包含一个或多个分区列，并为分区列中的每个不同值组合创建单独的数据目录。
如果在创建分区表时出现此错误：“FAILED：语义分析错误：列在分区列中重复”，这意味着您尝试将分区列包含在表本身的数据中。您可能确实已经定义了列。但是，您创建的分区会生成一个可以查询的伪列，因此您必须将表列重命名为其他内容（用户 不应查询！）。
create table par_table(
id int,
dt1 string,
name string
)
PARTITIONED BY (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
load data local inpath '/data1/par_table.txt' into table par_table partition (dt='2019-08-01');
load data local inpath '/data1/par_table.txt' into table par_table partition (dt='2019-08-02');
Create Table As Select (CTAS) —另外一种创建的格式
CTAS有以下限制：
The target table cannot be an external table.
The target table cannot be a list bucketing table.
create table emp_se as select * from emp;
Create Table Like ---- 另外一种创建的格式
create table emp_like like emp;
Bucketed Sorted Tables ----分桶存储
create table bucketed_user(
id int,
name string)
clustered by(id) into 4 buckets
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
-----先创建一个临时表
create table bucketed_tmp(
id int,
name string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data local inpath "/data1/user.txt" into table bucketed_tmp;
set hive.enforce.bucketing =true;
set mapreduce.job.reduces=4;
insert into bucketed_user select id,name from bucketed_tmp distribute by id ;
桶表是对数据进行哈希取值然后放到不同文件中存储。数据加载到桶表时会对宇段取哈 希值，然后与桶的数量取模，把数据放到对应的文件中。物理上，每个桶就是表或分区目录 里的一个文件，一个作业产生的桶输出文件和 reduce 任务个数相同。
首先创建一个分桶的空表，注意：是分桶的空表
然后创建个临时表，往临时表导入数据
然后在从临时表中分桶查询出来的数据insert到分桶的空表里
分桶表是一经决定，就不能更改，所以如果要改变桶数，要重新插入分桶数据
Temporary Tables – 临时表 表只对当前session有效，session退出后，表自动删除。
1、如果创建的临时表表名已存在，那么当前session引用到该表名时实际用的是临时表，只有drop或rename临时表名才能使用原始表
2、临时表限制：不支持分区字段和创建索引
Transactional Tables —事务表
Constraints — 约束
1.2.2 drop table
DROP TABLE [IF EXISTS] table_name [PURGE];
1.2.3 truncate table
TRUNCATE TABLE table_name [PARTITION partition_spec];
二、DML
2.1 将文件加载到表中
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
2.2 从查询中将数据插入Hive表
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;
2.3从查询中将数据写入文件系统
INSERT OVERWRITE [LOCAL] DIRECTORY directory1
[ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0)
SELECT ... FROM ...
2.4从SQL插入值到表中
INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]
//例子
CREATE TABLE students (name VARCHAR(64), age INT, gpa DECIMAL(3, 2))
CLUSTERED BY (age) INTO 2 BUCKETS STORED AS ORC;
INSERT INTO TABLE students
VALUES ('fred flintstone', 35, 1.28), ('barney rubble', 32, 2.32);
扫一扫
1266
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
