提前必备

1、内部表和外部表的区别

概念讲解：

外部表： 	1、存放他人给予自己的数据 	2、当我们删除表操作时，会将表的元数据删除，保留数据文件 内部表： 	1、存放已有的数据 	2、当我们删除表操作时，会将表的元数据以及数据文件都删除掉 

2、公共查询语句

with：一次查询内可无数次调用 temporary table：一次会话内可无数次调用【临时】 view与table：何时都可无数次调用【永久】 

一：内部表

概念

• 内部表是由 Hive 管理数据和元数据的一种表类型，通常包含表的名称、列定义、存储格式等信息。

• 【默认创建的表】就是【内部表】

基本形式

create table if not exists inner_table_employee( 	name string, 	places array, 	info struct, 	scores map, 	dept_pos map ) row format delimited fields terminated by '|' collection items terminated by ',' map keys terminated by ':' lines terminated by '\n' stored as textfile; 

二：外部表

概念

• 创建表时，带有【external】关键字的表即为【外部表】。

• 外部表允许在 Hive 中定义一个表结构，并对外部存储系统中的数据进行查询和分析，而不会对数据本身进行移动或修改。

基本形式

数据准备

{"name":"henry","age":22,"gender":"male","phone":"18014499655"} {"name":"pola","age":18,"gender":"female","phone":"18014499656"} 

外部表创建

create external table if not exists hive_ext_json_family( 	name string, 	age int, 	gender string, 	phone string ) row format serde 'org.apache.hive.hcatalog.data.JsonSerDe' stored as textfile location '/hive_data/hive_cha01/json'; 

三：分区表

1、概念

将一份大的文件拆分成多份，每一份存储在hdfs上表空间内的一个专门文件夹内

文件夹的命名包含了字段名和字段值，如：year=2012 形式。

注意：year可以作为字段来使用，但本质上year不是原始表字段，是分区字段。

2、优势

hive进行查询时就是文件流读文件，即使读取一条数据也需要加载整个文件。因此分区表将文件切割成更小的粒度，当需要针对局部数据进行检索、聚合等处理时只需要加载对应的粒度即可，从而提高了处理的效率。

3、建立分区要素

1、字段频繁出现于： 	一：where... , on... 	二：group by... , distribute by ... , cluster by... 	此时，就要考虑将此字段来建立分区 2、数据的容量(大)，需要考虑建立分区 

4、基本语法形式

create [external] table TABLE_NAME(FIELD_NAME TYPE,...) partitioned by(PAR_FIELD_NAME TYPE,...)		✔ row format delimited | serde 'SERDE_CLASS' .... 

5、实际操作

一：分区的建立

创建一级分区

drop table if exists test1w_partitioned_by_year; create external table if not exists test1w_partitioned_by_year( 	user_id int, 	user_gender string, 	order_time timestamp, 	order_amount decimal(6,2) ) partitioned by (year int)	✔ row format delimited fields terminated by ';' stored as textfile; 

创建多级分区【以二级分区为例】

drop table if exists test1w_partitioned_by_year_month; create table if not exists test1w_partitioned_by_year_month(     user_id int,     user_gender string,     order_time timestamp,     order_amount decimal(6,2) ) partitioned by(year int,month int)	✔ row format delimited fields terminated by ';' stored as textfile; 

二：数据的操作

静态分区

主要用处：客户按分区级别改|插入数据

1.筛选原文件： 	一级分区：         cat test1w.log | awk '/2012-/{print $0}'>test2012.log         cat test1w.log | awk '/2013-/{print $0}'>test2013.log 	多级分区【以二级分区为例】： 		cat test1w.log | awk '/2012-7/{print $0}'>test20127.log 		cat test1w.log | awk '/2012-8/{print $0}'>test20128.log 		 2.装到两分区内： 	一级分区： 		test2012.log: 			load data local inpath '/root/file/test2012.log'  			overwrite into table hive_internal_par_regex_test1w partition(year=2012); 										 		test2013.log: 			load data local inpath '/root/file/test2013.log'  			overwrite into table hive_internal_par_regex_test1w partition(year=2013); 								 	多级分区【以二级分区为例】： 		test20127.log: 			load data local inpath '/root/file/test20127.log' 			overwrite into table zhou.test1w_partitioned_by_year_month partition(year=2012,month=7); 									 		test20128.log: 			load data local inpath '/root/file/test20128.log' 			overwrite into table zhou.test1w_partitioned_by_year_month partition(year=2012,month=8); 

动态分区

主要用处：项目初期导入数据

准备工作[动态配置]

set hive.exec.dynamic.partition=true;				-- 1、会话 set hive.exec.dynamic.partition.mode=nonstrict; hive-site.xml										-- 2、个性化配置 hive-default.xml									-- 3、为所有配置项提供默认配置 

具体代码：

一级分区： 	insert overwrite table test1w_partitioned_by_year partition (`year`) 	select *,year(order_time) from test1w; 	 多级分区【以二级分区为例】： 	insert overwrite table zhou.test1w_partitioned_by_year_month partition (`year`,`month`) 	select * ,year(order_time),month(order_time) from test1w where year(order_time)<=2012; 

三：分区的其他操作【查删改】

– 查看分区信息

show partitions 表名; 

– 手动添加分区

一级分区： 	alter table zhou.test1w_partitioned_by_year add partition (year=2014); 多级分区【以二级分区为例】： 	alter table zhou.test1w_partitioned_by_year_month add partition (year=2012,month=7); 

– 手动删除分区

一级分区： 	alter table zhou.test1w_partitioned_by_year drop partition (year=2014); 多级分区【以二级分区为例】： 	alter table zhou.test1w_partitioned_by_year_month drop partition (year=2012,month=7); 

四：分桶表

1、概念

分桶表时将一个表或分区内的数据，拆分成更小的文件片段，使抽样更加高效。

2、必知点

1、分桶字段必须是表中已存在的原始字段 2、默认采用：原始字段值的hashcode%分桶数列 => 决定当前行数据会被拆分到几号桶 3、优势：数据采样 4、采样率：10% -> 桶数定义为10 5、一般是在 【分区】 的基础上进行 【分桶】,更好地优化查询性能。 

3、实际应用场景

1.抽样【数据采样】

在开发中，数据量大的情况下，我们为了针对开发做测试，就可以采用分桶来进行数据采样，采样得到的结果是一个具有代表性的查询结果，可以达到快速开发的目的。

2.拉链表【便于修改】

修改某行数据时，无需将整个文件都读取出来，只需将小份文件导出进行修改即可。

4、实际操作

一：创建分桶表

在根据year分区的基础上，对每个year内部进行了分桶，分为4份数据，便于抽样|修改

drop table if exists test1w_partitioned_SeparateBarrel; create table if not exists test1w_partitioned_SeparateBarrel(     user_id int,     user_gender string,     order_time timestamp,     order_amount decimal(6,2) ) partitioned by(year int) clustered by(order_time) into 4 buckets	✔	=> 此时采样率：25% row format delimited fields terminated by ';' stored as textfile; 

二：数据的操作

准备工作[动态配置]

set hive.exec.dynamic.partition=true;				-- 1、会话 set hive.exec.dynamic.partition.mode=nonstrict; hive-site.xml										-- 2、个性化配置 hive-default.xml									-- 3、为所有配置项提供默认配置 

具体代码

insert into table zhou.test1w_partitioned_SeparateBarrel partition (year) select *,year(order_time) from test1w; 

三：实际应用【数据采集】

–随机抽样【基于整行数据】

基本解释：

• 取每个桶中 四分之三的数据【很少用】
• 进行随机抽样，不考虑数据的顺序或时间等因素，可以使用类似 bucket 3 out of 4 on rand()形式，这样每次抽样的结果可能会有所不同，适合需要随机性的分析或实验。

select * from test1w_partitioned_SeparateBarrel tablesample(bucket 3 out of 4 on rand())s; 

–分桶字段抽样【基于指定列】✔

基本解释：

• 取每个桶中 四分之一的数据[桶]【随机】 => 推荐使用，使用分桶列更高效

• 从【有序的数据】中抽样，例如按照时间排序的订单数据，可以使用类似于 bucket 3 out of 4 on order_time形式，这样可以保证抽样数据具有一定的顺序性和连续性。

最终结果分析：最后获取的数据是在每个分区【文件夹】内随机抽取指定数量【如：四分之一]的数据[桶]】=> 抽到的数据[桶]是具有随机性的。

select year,count(*) as order_count from test1w_partitioned_SeparateBarrel tablesample ( bucket 1 out of 4 on order_time)s group by year; 

五：临时表(temporary)

1、概念

• 一次链接（会话session）内临时创建的表格，会话结束后自动删除

默认hdfs路径：/tmp/hive/root 内根据时间寻找临时表		 idea中：show tables; 才可看到临时表。 

2、实际操作

create temporary table if not exists test1w_gender as select user_gender,count(*) as gender_cnt from zhou.test1w group by user_gender; 

六：视图(view)

1、概念

• 本质：一条较为复杂的共用的查询语句

2、实际操作

create view if not exists hive_view_test1w_Girl as select * from test1w where user_gender = "女"; 

七：拉链表(zip tables)

1、发展流程

hive发展

• hive 0.14就已经有这一逻辑模型,名为slowly changing dimension。
• hive 2.6.0 支持merge语法，运用了 事务管理

拉链表由来

原来采用分区表，用户分区存储历史增量数据，缺点是重复数据太多
目前运用拉链表来解决这一问题

2、含义

用于解决持续增长且存在一定时间范围内重复的数据，即：合并有一定重复性【较小时间范围内】的数据。

3、优点

• 节约空间（一份订单只有一条数据）

4、应用场景

【数据规模庞大】，新数据【在有限区间(时间…)内】存在多种状态变化

5、准备工作[动态配置]

set hive.support.concurrency=true; set hive.enforce.bucketing=true; set hive.exec.dynamic.partition.mode=nonstrict; set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; set hive.compactor.initiator.on=true; -- 表合并开启 set hive.compactor.worker.threads=1; -- 表合并线程必须为一 set hive.auto.convert.join=false; -- 关闭 mapjoin set hive.merge.cardinality.check=false; -- 关闭检查数据列的基数(列值的差异性) set mapreduce.job.reduces=4; 

6、具体代码

drop table if exists zhou.hive_zipper_order; create table zhou.hive_zipper_order( 	order_id bigint, 	user_id bigint, 	order_modify_dt timestamp, 	order_money decimal(10,2), 	current_status int ) row format delimited  fields terminated by ','; --导入f F的数据至普通表hive_zipper_order中 load data local inpath '/root/file/log/order_record.log' overwrite into table zhou.hive_zipper_order;  --创建拉链表hive_zipper_pc_order	✔ drop table if exists zhou.hive_zipper_pc_order; create table zhou.hive_zipper_pc_order( 	order_id bigint, 	user_id bigint, 	order_create_dt timestamp, 	order_modify_dt timestamp, 	order_money decimal(10,2), 	current_status int ) partitioned by(year int,month int,day int) clustered by(order_create_dt) into 4 buckets row format delimited 	fields terminated by ',' stored as orc tblproperties("transactional"="true");  --操作历史全量数据用动态分区	✔ set hive.support.concurrency=true; set hive.enforce.bucketing=true; set hive.exec.dynamic.partition.mode=nonstrict; set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; set hive.compactor.initiator.on=true; set hive.compactor.worker.threads=1; set hive.auto.convert.join=false; set hive.merge.cardinality.check=false; set mapreduce.job.reduces=4;  --开启动态分区，一次性挂载至拉链表hive_zipper_pc_order中	✔ with zip_src as ( 	select order_id,user_id,order_money, 		min(order_modify_dt) as order_create_dt, 		max(order_modify_dt) as order_modify_dt, 		max(current_status) as current_status 	from zhou.hive_zipper_order 	group by order_id,user_id,order_money ) insert overwrite table zhou.hive_zipper_pc_order partition(year,month,day) select 	order_id, 	user_id, 	order_create_dt, 	order_modify_dt, 	order_money, 	current_status, 	year(order_create_dt) as year, 	month(order_create_dt) as month, 	day(order_create_dt) as day from zip_src;  -- 拉链表查询	✔ set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; set hive.support.concurrency=true; select * from zhou.hive_zipper_pc_order where to_date(order_modify_dt)='2021-02-04' order by order_modify_dt desc;  --之后每天，增量添加【在原表处新增】 load data local inpath '/root/file/log/order_record_2021_02_05.log' overwrite into table zhou.hive_zipper_order;  --拉链处理增量数据(新增新数据，修改旧数据)	✔ merge into zhou.hive_zipper_pc_order as O using ( 	select  		order_id, 		user_id, 		order_create_dt, 		order_modify_dt, 		order_money, 		current_status, 		year(order_create_dt) as year, 		month(order_create_dt) as month, 		day(order_create_dt) as day 	from ( 		select order_id,user_id,order_money, 			min(order_modify_dt) as order_create_dt, 			max(order_modify_dt) as order_modify_dt, 			max(current_status) as current_status 		from zhou.hive_zipper_order 		--where to_date(order_modify_dt)='2021-02-05' 		group by order_id,user_id,order_money 	)T ) as H on O.order_id=H.order_id when matched then  update set order_modify_dt=H.order_modify_dt,current_status=H.current_status when not matched then  insert values(H.order_id,H.user_id,H.order_create_dt,H.order_modify_dt,H.order_money,H.current_status,H.year,H.month,H.day);  --验证拉链结果	✔ select * from zhou.hive_zipper_pc_order where to_date(order_modify_dt)>to_date(order_create_dt);