大数据-hive理论(2)DDL
本章分享的目录：
1：表操作之表创建
第一节：hive DDL
(1) 建表方式
(2) 普通建表
(3) 动态分区表
(4) 视图
(5) 索引
第一节：hive DDL（数据库/表的创建）
一：建表方式
(1) 第一种 creat：
CREATE TABLE person(
id INT,
name STRING,
age INT,
likes ARRAY<STRING>,
address MAP<STRING,STRING>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',' 字段分隔
COLLECTION ITEMS TERMINATED BY '-' 集合分隔
MAP KEYS TERMINATED BY ':' map分隔
LINES TERMINATED BY '\n'; 行分隔，默认\n，可不写
(2) 第二种 like：（无数据，只有表）
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
LIKE existing_table_or_view_name
[LOCATION hdfs_path];
(3) 第三种 as查询创建（有数据）
CREATE TABLE 新表
AS
SELECT columA, columB FROM key_value_store;
二：普通建表
2.1：Hive的数据类型
| array_type
| map_type
| struct_type //一个构造体，类似对象
|TINYINT
| SMALLINT
| INT
| BIGINT
| BOOLEAN
| FLOAT
| DOUBLE
| STRING
2.2：建表规则
2.2.1 create创建
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] // https://blog.csdn.net/mhtian2015/article/details/78931236
[
[ROW FORMAT row_format] //指定分隔
[STORED AS file_format] //文件格式
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
]
[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)
2.3：内部表外部表
（1）Hive 内部表
CREATE TABLE [IF NOT EXISTS] table_name LOCATION hdfs_path （默认指定位置/user/Hive/warehouse/）
删除表时，元数据与数据都会被删除
（2）Hive 外部表
CREATE EXTERNAL TABLE [IF NOT EXISTS] table_name LOCATION hdfs_path
删除外部表只删除metastore的元数据，不删除hdfs中的表数据
hive与hbase整合时候适用于外部表===============================================================
表的区别及应用：
1：hdfs_path路径下的数据，指定之后追加来的数据，或有原油数据，可读取到表中。
2：区别：外部表的安全性更高，可以不怕误删除元数据，方便共享资源。
（3）Hive 查看表描述
DESCRIBE [EXTENDED|FORMATTED] table_name
（4）特点：hive是读时检查，不是写时检查
2.4：hive分区（静态）
/**********创建表时候就直接创建了分区*************/
1：分区建表
a、单分区建表语句：
create table day_table (id int, content string) partitioned by (dt string);
单分区表，按天分区，在表结构中存在id，content，dt三列。
以dt为文件夹区分
load数据时候后面就可以指定分区
partition(age=10)
找数据时候 age=10是一个目录
b、 双分区建表语句：
create table day_hour_table (id int, content string) partitioned by (dt string, hour string);
双分区表，按天和小时分区，在表结构中新增加了dt和hour两列。
先以dt为文件夹，再以hour子文件夹区分
/**********创建表时候没有分区，后添加分区*************/
1：添加分区（原有的数据是不在分区之内的，详见3）
ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location1'] partition_spec [LOCATION 'location2'] ...;
partition_spec:
: (partition_column = partition_col_value, partition_column = partition_col_value, ...)
例：
ALTER TABLE day_table ADD PARTITION (dt='2008-08-08', hour='08')
2：删除分区
ALTER TABLE table_name DROP partition_spec, partition_spec,...
partition_spec:
: (partition_column = partition_col_value, partition_column = partition_col_value, ...)
用户可以用 ALTER TABLE DROP PARTITION 来删除分区。
内部表中、对应分区的元数据和数据将被一并删除。
例：
ALTER TABLE day_hour_table DROP PARTITION (dt='2008-08-08', hour='09');
3：创建分区表时候，原有的数据是映射不到表中的，因为他不会因为这个目录当做你的分区，解决如下：
即：如果先在分区的形式放了数据，再创建表，这个时候是读不到数据的，需要修复分区：
查看表的分区情况：
SHOW PARTITIONS tablename;
修复分区：
Msck repair table tablename
2.5：分桶（一个分区下，按照一定的条件规则分成不同的文件去存，方便查找）
（1）实用场景：（被分配到哪个桶中：该列值的hash值 除以 桶的个数）
数据抽样（samping）
分桶之后，在每个桶中取对应的多少条数据
map-join：
（小表关联大表：大表中的按照hash规则分好桶，小表中的值的hash去相应的规则的桶中去找就行了，不用遍历全数据了）
（2）分桶抽样查询的语法
桶表抽样查询
select * from 表 tablesample(bucket 1 out of 4 on 列);
tablesample语法：
TABLESAMPLE(BUCKET x OUT OF y)
x：表示从哪个bucket开始抽取数据
y：必须为该表总bucket数的倍数或因子
例：当表总bucket数为32时：（数据份数=bucket/y）
1：TABLESAMPLE(BUCKET 3 OUT OF 8)，抽取哪些数据？（y为因子时候）
a、共抽取4个bucket的数据（抽几份数据=bucket/y），
b、抽取第3、第11（3+8）、第19（11+8）、第27（19+8）个bucket的数据
2：TABLESAMPLE(BUCKET 3 OUT OF 256)，抽取哪些数据？（y为倍数时候）
a、共抽取1个bucket的1/8数据（32/256），
b、抽取第3个bucket中的(1/8)数据
（3）开启分桶
开启支持分桶
set hive.enforce.bucketing=true;
默认：false；设置为true之后，mr运行时会根据bucket的个数自动分配reduce task个数。
（用户也可以通过mapred.reduce.tasks自己设置reduce任务个数，但分桶时不推荐使用）
注意：一次作业产生的桶（文件数量）和reduce task个数一致。
（4）分桶表创建
CREATE TABLE psnbucket( id INT, name STRING, age INT)
CLUSTERED BY (age) INTO 4 BUCKETS
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
（5）插入分桶表数据
往分桶表中加载数据
insert into table bucket_table select columns from tbl;（不清除之前表中的记录）
insert overwrite table bucket_table select columns from tbl;（清除之前的表中的记录）
（6）查询分桶中的数据
select id, name, age from psnbucket tablesample(bucket 2 out of 4 on age)
三：动态分区表
（1）分区设定
开启支持动态分区（老版本默认false，新版本默认true）
set hive.exec.dynamic.partition=true;
默认：true
set hive.exec.dynamic.partition.mode=nostrict;
默认：strict（至少有一个分区列是静态分区）
相关参数
set hive.exec.max.dynamic.partitions.pernode;
每一个执行mr节点上，允许创建的动态分区的最大数量(100)
set hive.exec.max.dynamic.partitions;
所有执行mr节点上，允许创建的所有动态分区的最大数量(1000)
set hive.exec.max.created.files;
所有的mr job允许创建的文件的最大数量(100000)
（2）举例
from psn21
insert overwrite table psn22 partition(age, sex)
select id, name, likes, address, age, sex
四：视图（视图表数据的改变，会改变原表数据，除非创建只读视图）
（1）创建视图：
CREATE VIEW [IF NOT EXISTS] [db_name.]view_name
[(column_name [COMMENT column_comment], ...) ]
[COMMENT view_comment]
[TBLPROPERTIES (property_name = property_value, ...)]
AS SELECT ... ;
例子：
Create view 视图表名 as select * from 表
（2）查询视图：
select colums from view;
（3）删除视图：
DROP VIEW [IF EXISTS] [db_name.]view_name;
（4）特点：
1、不支持物化视图
2、只能查询，不支持加载,修改,删除数据
3、视图的创建，只是保存一份元数据，查询视图时才执行对应的子查询
4、view定义中若包含了ORDER BY/LIMIT语句，当查询视图时也进行ORDER BY/LIMIT语句操作，view当中定义的优先级高
5、view支持迭代视图
五：索引（每次有新数据，都要进行重建索引）
（1）目的：
优化查询以及检索性能
（2）创建索引：
create index t1_index on table psn2(name)
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild
in table t1_index_table;
解释：
as：指定索引器；
in table：指定索引表，若不指定默认生成在default__psn2_t1_index__表中
索引表：用来记录索引信息: (字段，所在位置，偏移量)，刚创建出来时候，里面是没有数据的，需要进行重建索引才会有数据。
如果有数据的增加都要进行重建索引才会生效。
create index t1_index on table psn2(name)
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild;
（3）查询索引
show index on psn2;
（4）重建索引，向指定的索引表插入数据（建立索引之后必须重建索引才能生效）
ALTER INDEX t1_index ON psn2 REBUILD;
（5）删除索引
DROP INDEX IF EXISTS t1_index ON psn2;
