数据仓库技术及应用（库表操作/分区与分桶）

库表操作

1.Hive DDL操作常用命令 create、drop、alter、truncate、show、describe Hive数据库操作

#创建数据库
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name  
[COMMENT database_comment]  
[LOCATION hdfs_path]  
[WITH DBPROPERTIES (property_name=property_value, ...)]
#修改数据库
ALTER (DATABASE|SCHEMA) database_name
SET DBPROPERTIES (property_name=property_value, ...)
#删除数据库
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE]

2.Hive数据表操作

(1)创建数据表

#创建数据表
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, ...)]     
        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 (...)]  
]  
[LOCATION hdfs_path]  
[TBLPROPERTIES (property_name=property_value, ...)]

eg.1）创建电影票信息表 ticket，字段包含电影票编号、影片名称、时间、座位与价格。其中，座位与价格使用 STRUCT类型。

ticket.txt:
22,The Matrix,2018-08-01 20:21:22,5-8:33.00
24,The Matrix,2018-08-01 24:22:26,5-9:33.00
22,The Matrix,2018-08-11 19:31:58,5-1:33.00
 

create database if not exists hive_work;
use hive_work;
# 创建电影票信息表
create table if not exists tickets(
    id int,
    name string,
    ticket_date string,
    seat_price struct<seat:string,price:double>
)
row format delimited 
    fields Terminated by ',' 
    collection items terminated by ":"
    lines terminated by '\n'
stored AS textfile;

2）将本地的数据文件 load到 ticket中。

LOAD DATA LOCAL INPATH '/root/data/ticket.txt' OVERWRITE  INTO TABLE hive_work.tickets;

3）使用 HQL语句查询 ticket所有数据。 

select * from hive_work.tickets;

（2）修改数据表

#修改数据表表名
ALTER TABLE table_name RENAME TO new_table_name
#添加或替换列
ALTER TABLE table_name ADD|REPLACE COLUMNS（col_name data_type）

（3）Hive DDL其他操作

命令	实例	说明
drop	drop database database_name	删除数据库
	drop table table_name	删除表
truncate	truncate table table_name	清空表
show	show databases	显示所有数据库名
	show tables	列出当前表中所有表
	show views	列出当前库中所有视图
	show partitions table_name	列出表中所有分区
	show create table_name	显示建表语句
	show functions "a.*"	列出所有函数
describe	describe database database_name	显示数据库信息
	describe table_name	显示表信息
	describe table_name.col_name	显示表中某列的信息

（4）创建分区表

CREATE TABLE employee_partitioned(
 name string,
 work_place ARRAY<string>,
 sex_age STRUCT<sex:string,age:int>,
 skills_score MAP<STRING,ARRAY<STRING>>)
PARTITIONED BY(year INT,month INT)
CLUSTERED BY(employee_id)INTO 2 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':';

3.Hive 分区表/分桶表描述

Hive分区表描述 

• 分区列的值将表划分为文件夹
• 查询时使用“分区”列和常规列类似
• 查询时Hive自动过滤掉不用于提高性能的分区

Hive分桶表描述

• 分桶是相对分区进行更细粒度的划分
• 根据“桶列”的哈希函数将数据进行分桶
• 更高的查询处理效率

Hive分区/分桶

静态or动态

#使用动态分区需设置
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

#动态分桶
#使用动态分桶需设置
set hive.enforce.bucketing = true

随机抽样基于整行数据

SELECT * FROM table_name TABLESAMPLE(BUCKET 3 OUT OF 32 ON rand()) s;

 随机抽样基于指定列（使用分桶列更高效）

SELECT * FROM table_name TABLESAMPLE(BUCKET 3 OUT OF 32 ON id) s;

随机抽样基于block size

​SELECT * FROM table_name TABLESAMPLE(10 PERCENT) s;
SELECT * FROM table_name TABLESAMPLE(1M) s;
SELECT * FROM table_name TABLESAMPLE(10 rows) s;

4.Hive DML操作

（1）常用命令 load、insert、update、delete、merge、explain等 

（2）数据加载

load命令实现，将数据复制或移动到Hive表对应的位置。insert命令实现，执行MR作业将数据插入Hive表中。

（3）Hive更新、删除、合并数据

执行这些操作需要开启ACID（事务）支持；Hive是数据仓库解决方案，不适合做这种操作。

（4）数据导入\导出

使用import/export实现数据导入\导出。

# 数据导入
IMPORT [[EXTERNAL]TABLE new_or_original_tablename[PARTITION(partcol1=val1,……)]]
FROM 'source_path'[LOCATION 'import_target_path']
# 数据导出
EXPORT TABLE table_name [PARTITION(partcol1=val1,……)]TO 'export_target_path'