Hive进行分区分桶

一、Hive建表语法

create  [external] table [ if not exists] table_name 
   [(col_name data_type [comment col_comment], ...)] 
   [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 ] 
   
   [row format row_format]  
   [stored as  file_format] 
   [location  hdfs_path]

说明：

1、 create table 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 if not exists 选项来忽略这个异常。

2、 external关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（location），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

3、like 允许用户复制现有的表结构，但是不复制数据。

4、row format

delimited [fields terminated by char]
[collection items terminated by char ]
[map keys terminated by char] 
[lines terminated by char] | serde serde_name 
[with serdeproperties (property_name=property_value, property_name=property_value, ...)]

指定行分割符，row format delimited fields terminated by “分隔符”，用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 row format或者 row format delimited，将会使用自带的 SerDe。

5、stored as

sequence | text  | rc  |orc | parquet 

如果不写，默认是 stored as textfile。如果数据需要压缩，使用 stored as sequencefile 。兼容性更好的是parquet 。

6、location

若是不指定位置，hive表会建立在user/hive/warehouse/表名，hive的表会加载user/hive/warehouse/下的所有文件。

7、clustered by

对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

把表（或者分区）组织成桶（Bucket）有两个理由：

（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。

（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

create table my_hive (
   id   int,
   name string
) 
clustered  by (id)
into 4 buchets
row format delimited fields terminated by '\t' ;

二、Hive数据类型

1、基本数据类型

2、集合数据类型

创建map类型：

create table custom(
   id int,
   name string,
   info map<string,string>
)
row format delimited fields terminated by ','
collection items terminated by '‐'
map keys terminated by ':';

创建struct类型：

create table stu(
   id int,
   stu_info struct<name:String,age:int,sex:string>
)
row format delimited fields terminated by ','
collection items terminated by ':';

创建array类型：

create table movie(
   id int,
   name string,
   actors array<string>
)
row format delimited fields terminated by ','
collection items terminated by ':';

分区针对的是数据的存储路径，分桶针对的是数据文件；

三、hive分区（partitioned by）

hive中做查询时，一般会扫描整个表内容，会消耗很多时间，因此引入分区概念，只查询关心的一部分数据。

静态分区：将数据直接导入到指定分区中： load
动态分区：根据某个字段判断，将数据插入到分区中

1、一级静态分区：

create table person (
  a int, 
  b int,
  c int
) 
partitioned by (country string)  
row format delimited fields terminated by '\t' ;

2、二级静态分区：

create table person (
  a int, 
  b int,
  c int
) 
partitioned by (country string, province string)  
row format delimited fields terminated by '\t' ;

3、动态分区：

1、创建一个普通表，加载所有数据

2、开启动态分区功能

3、创建分区表

4、通过 insert select 方式导入数据

4、修改分区

  alter table 表名 add partition(dt=’09’)   --增加分区

  alter table 表名 drop partition(dt=’09’)   --删除分区

Hive是做批量离线数据分析的，不是做数据库用的，不适合做一条一条数据的插入，没有更新数据操作，数据是直接拿过来读而不会去改。

四、hive分桶（clustered by ）

分桶就是把大表化成小表，Hive进行分桶可以提高join的效率。

1、创建一个普通表，加载数据

2、开启分桶表

set hive.enforce.bucketing = true;
set mapreduce.job.reduces=4;

3、创建分桶表

create table person (
    a int,
    b int, 
    c int
) 
clustered by (a)  
into 4 buckets 
row format delimited fields terminated by '\t' ;

4、insert 插入普通表数据到分桶表

保存select查询结果的几种方式：

1、将查询结果保存到一张新的hive表中；

create table t_tmp  as  select * from t_p;

2、将查询结果保存到一张已存在的hive表中；

insert into  table t_tmp  select * from t_p;

3、将查询结果保存到指定文件目录（本地或者hdfs）。

insert overwrite local directory '/home/hadoop/test'   select * from t_p;

五、hive基本语法

1、查看表结构

desc  表名
desc  formatted  表名    (更详细)
show create table 表名   查看建表语句

2、load导入数据

load  data[local] inpath   'filepath' [overwrite] into table  tablename [partition (partcol1=val1, partcol2=val2 ...)]

例如：

load data inpath '/user/hive/warehouse/03_basal_layer_zfybxers00.db/zfybxers00_z_rma_cost_detail_m/importdate=20210923' into table 03_basal_layer_zfybxers00.zfybxers00_z_rma_cost_detail_m  partition (importdate="20210923")  

3、数据导入导出

import table  表名  from  "filepath"    --表要求不存在，用于数据备份迁移

export table 表名  to  "filepath"

4、查询结果插入Hive表

insert  overwrite  table  tablename1 [partition (partcol1=val1,partcol2=val2 ...)] select_statement1 from  from_statement

5、shell方式

hive -e "sql语句"  >>  本地文件

hive  -f  文件 >/>> 文本文件

6、命令导出

hdfs  dfs  -get  表中文件

hive> dfs -get -ls 

7、select查询

select [all   | distinct] select_expr, select_expr, ... 
from  table_reference
[where where_condition] 
[group by col_list [having condition]] 
[cluster by  col_list  | [ distribute by   col_list] [sort by | order by  col_list] 
] 
[limit number]

1、order by： 会对输入做全局排序，会导致当输入规模较大时，需要较长的计算时间。
2、sort by： 区内排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。
3、distribute by ： 指定分区字段。
4、Cluster by：当分区字段和排序字段，是同一个字段做升序时，cluster by = distribute by + sort by

8、where和having区别

where 放在from之后，执行的是原始数据；
having 放在group by之后，执行的是分组后数据

六、hive中各种join

申请表

00001,p01
00002,p02
00003,p03
00004,p04

人员表

p01,00001,张丽,副高
p02,00002,李三,处长
p04,00002,王六,专责
p03,00003,赵五,员工
p05,00003,钱七,员工
p06,00003,周九,员工
p07,00005,孙二,员工

1、join on（内连接）

select 
   a.apply_id, a.person_id,
   b.person_name,b.person_level
from 
   base_apply a  join  renyuan b on (a.apply_id=b.apply_id)

查询结果：

00001   p01     张丽    副高
00002   p02     李三    处长
00002   p02     王六    专责
00003   p03     赵五    员工
00003   p03     钱七    员工
00003   p03     周九    员工

2、inner join on(内连接)

select 
   a.apply_id, a.person_id,
   b.person_name,b.person_level
from 
   base_apply a  inner join  renyuan b on (a.apply_id=b.apply_id)

查询结果：

00001   p01     张丽    副高
00002   p02     李三    处长
00002   p02     王六    专责
00003   p03     赵五    员工
00003   p03     钱七    员工
00003   p03     周九    员工

3、left join on(左连接)

select 
   a.apply_id, a.person_id,
   b.person_name,b.person_level
from 
   base_apply a  left join  renyuan b on (a.apply_id=b.apply_id)

查询结果：

00001   p01     张丽    副高
00002   p02     李三    处长
00002   p02     王六    专责
00003   p03     赵五    员工
00003   p03     钱七    员工
00003   p03     周九    员工
00004   p04     NULL    NULL

4、full outer join on（两边数据都会出来）

select 
   a.apply_id, a.person_id,
   b.person_name,b.person_level
from 
   base_apply a  full outer join   renyuan b on (a.apply_id=b.apply_id)

查询结果：

00001   p01     张丽    副高
00002   p02     王六    专责
00002   p02     李三    处长
00003   p03     周九    员工
00003   p03     钱七    员工
00003   p03     赵五    员工
00004   p04     NULL    NULL
NULL    NULL    孙二    员工

5、left semi join on (in/exists 子查询)

select 
   a.apply_id, a.person_id
from 
   base_apply a  left semi join  renyuan b on (a.apply_id=b.apply_id)

查询结果：

00001   p01
00002   p02
00003   p03

因为 left semi join 是 in(keySet) 的关系，遇到右表重复记录，左表会跳过，而 join 则会一直遍历。这就导致右表有重复值得情况下 left semi join 只产生一条，join 会产生多条，也会导致 left semi join 的性能更高。

6、union

union:        自动去除重复数据
union all :   所有数据

七、 Hive自定义函数（UDF）

1、导人hive-exec包

2、继承UDF，并重载evaluate

package cn.itcast.bigdata.udf;
import java.util.HashMap;
import org.apache.hadoop.hive.ql.exec.UDF;

public class ToLowerCase extends UDF {

	// 必须是public
	public String evaluate(String field) {
		String result = field.toLowerCase();
		return result;
	}
}

3、打成jar包上传到服务器

4、将jar包添加到hive的classpath

hive>add JAR /home/hadoop/udf.jar;

5、创建临时函数与开发好的java class关联

Hive>create temporary function tolowercase as 'cn.itcast.bigdata.udf.ToProvince';

6、在hql中使用自定义的函数

7、hive实现号码归属地

1、编写ToLowerCase类

package cn.itcast.bigdata.udf;
import java.util.HashMap;
import org.apache.hadoop.hive.ql.exec.UDF;
	
public class ToLowerCase extends UDF {
	public static HashMap<String, String> provinceMap = new HashMap<String, String>();
		static {
			provinceMap.put("136", "beijing");
			provinceMap.put("137", "shanghai");
			provinceMap.put("138", "shenzhen");
		}
	
	// 必须是public
	public String evaluate(int phonenbr) {
			
			String pnb = String.valueOf(phonenbr);
			return provinceMap.get(pnb.substring(0, 3)) == null ? "huoxing":provinceMap.get(pnb.substring(0,3));
	
		}
}

2、打成jar包上传到服务器

3、将jar包添加到hive的classpath

hive>add JAR /home/hadoop/udf.jar;

4、创建临时函数与开发好的java class关联

Hive>create temporary function tolowercase as 'cn.itcast.bigdata.udf.ToProvince';

5、写hive sql 查询

8、hive解析json数据

方式一：自定义函数解析

1、编写MovieRateBean类

package cn.itcast.bigdata.udf;
//{"movie":"1721","rate":"3","timeStamp":"965440048","uid":"5114"}
public class MovieRateBean {

	private String movie;
	private String rate;
	private String timeStamp;
	private String uid;
	public String getMovie() {
		return movie;
	}
	public void setMovie(String movie) {
		this.movie = movie;
	}
	public String getRate() {
		return rate;
	}
	public void setRate(String rate) {
		this.rate = rate;
	}
	public String getTimeStamp() {
		return timeStamp;
	}
	public void setTimeStamp(String timeStamp) {
		this.timeStamp = timeStamp;
	}
	public String getUid() {
		return uid;
	}
	public void setUid(String uid) {
		this.uid = uid;
	}
	@Override
	public String toString() {
		return movie + "\t" + rate + "\t" + timeStamp + "\t" + uid;
	}

}

2、编写JsonParser

package cn.itcast.bigdata.udf;
import org.apache.hadoop.hive.ql.exec.UDF;
import parquet.org.codehaus.jackson.map.ObjectMapper;

public class JsonParser extends UDF {

	public String evaluate(String jsonLine) {

		ObjectMapper objectMapper = new ObjectMapper();

		try {
			MovieRateBean bean = objectMapper.readValue(jsonLine, MovieRateBean.class);
			return bean.toString();
		} catch (Exception e) {

		}
		return "";
	}

}

3、打成jar包上传到服务器

4、将jar包添加到hive的classpath

hive>add JAR /home/hadoop/udf.jar;

5、创建临时函数与开发好的java class关联

Hive>create temporary function tolowercase as 'cn.itcast.bigdata.udf.ToProvince';

6、写hive sql 查询

线创建一个t_json表，再把结果放到t_reting表里，实现把结果放到四个字段里

方式二：引入插件

1、把插件json-serde-1.3.8.jar放入hive的lib下

2、建表时导入插件

DROP TABLE IF EXISTS ODS17.APP_ACTION_LOG;
CREATE EXTERNAL TABLE ODS17.APP_ACTION_LOG(
account           string ,  -- 用户登录账号
appId             string ,  -- 用户所使用的app
appVersion        string ,  -- app的版本
carrier           string ,  -- 运营商
deviceId          string ,  -- 设备id
deviceType        string ,  -- 设备型号
eventId           string ,  -- 事件id
ip                string ,  -- ip地址
latitude          double ,  -- 纬度
longitude         double ,  -- 经度
netType           string ,  -- 网络类型（4g，5g，wifi）
osName            string ,  -- 操作系统名称
osVersion         string ,  -- 操作系统版本
releaseChannel    string ,  -- app的发布下载渠道
resolution        string ,  -- 屏幕分辨率
sessionId         string ,  -- 会话id
`timeStamp`       bigint ,  -- 事件时间戳
properties        map<string,string> -- 事件详细属性
)
PARTITIONED BY (dt string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
STORED AS TEXTFILE
;

3、导入数据

load data inpath '/logdata/app/2020-10-07' into table ODS17.APP_ACTION_LOG partition (dt='2020-10-07');

方式三：使用json_tuple() 函数

例如：select json_tuple(json,'movie','rate','timeStamp','uid') as(movie,rate,ts,uid) from t_json;

8、transform案例：时间转换为星期

实现Hive中没有的功能又不想写UDF的情况

1、先加载rating.json文件到hive的一个原始表 rat_json

create table rat_json(line string) row format delimited;
load data local inpath '/home/hadoop/rating.json' into table rat_json;

2、需要解析json数据成四个字段，插入一张新的表 t_rating

insert overwrite table t_rating
select get_json_object(line,'$.movie') as moive,get_json_object(line,'$.rate') as rate  from rat_json;

3、编辑一个python脚本文件

vi weekday_mapper.py
#!/bin/python
import sys
import datetime

for line in sys.stdin:
  line = line.strip()
  movieid, rating, unixtime,userid = line.split('\t')
  weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
  print '\t'.join([movieid, rating, str(weekday),userid]) 

4、文件加入hive的classpath

hive>add FILE /home/hadoop/weekday_mapper.py; 

5、执行sql

hive>create TABLE u_data_new as
SELECT
  TRANSFORM (movieid, rate, timestring,uid)
  USING 'python weekday_mapper.py'
  AS (movieid, rate, weekday,uid)
FROM t_rating; 

6、查询

hive>select distinct(weekday) from u_data_new limit 10;

9、hive面试题（累计报表）

select A.username,A.month,max(A.salary) as salary,sum(B.salary) as accumulate
from 
(select username,month,sum(salary) as salary from t_access_times group by username,month) A 
inner join 
(select username,month,sum(salary) as salary from t_access_times group by username,month) B
on   A.username=B.username
where 
B.month <= A.month   group by A.username,A.month   order by A.username,A.month;