Hive笔记_列分隔符为空dbever

Hive

1.Hive的定义

• Hive本质是：将 SQL 转换为 MapReduce 的任务进行运算

• 底层由HDFS来提供数据存储

• 可以将Hive理解为一个：将 SQL 转换为 MapReduce 任务的工具

• Hive的引擎是可以是MR/Spark/Flink

2.Hive元数据

Hive的元数据默认存储在自带的 derby 数据库中，生产中多采用MySQL存储元数据。

3.Hive数据类型

常用类型：BIGINT、DOUBLE、STRING、TIMESTAMP

其他类型：TINYINT、SMALLINT、INT、BOOLEAN、FLOAT、BINARY

集合数据类型：ARRAY、MAP、STRUCT、UNION

4.Hive默认分隔符

Hive 中没有定义专门的数据格式，数据格式可以由用户指定，用户定义数据格式需要指定三个属性：

• 列分隔符（通常为空格、"\t"、"\x001"）
• 行分隔符（"\n"）
• 读取文件数据的方法

将 Hive 数据导出到本地时，系统默认的分隔符是^A、B、^C 这些特殊字符。

使用命令

cat -A file.dat
1

Hive中数据加载过程采用"读时模式" (schema on read)，加载数据时不进行数据格式的校验，读取数据时如果不合法则显示NULL。这种模式的优点是加载数据迅速。

5.HQL操作

数据库操作

建表（内表、外表）

分区表

分桶表

修改表、删表

数据导入导出

增

删

改

查（重点）

函数（重点）

-- 数据库操作

-- 创建数据库。添加备注，指定数据库在存放hdfs位置
create database if not exists ods
comment 'ods层'
location '/user/hive/ods.db';

create database if not exists dwd
comment 'dwd层'
location '/user/hive/dwd.db';

-- 查看数据库
show databases;
desc database ods;

-- 使用数据库
use ods;

-- 删除数据库（少用）cascade表示强制删除非空数据库
drop database databasename cascade;

-- 建表（内表、外表）

-- 默认情况下，创建内部表。如果要创建外部表，需要使用关键字 external
-- 在删除内部表时，表的定义(元数据) 和 数据 同时被删除
-- 在删除外部表时，仅删除表的定义，数据被保留，一般都是建外表

drop table ods.game1_log_player_login;

create external table IF NOT EXISTS ods.game1_log_player_login
(n_uid bigint comment '用户唯一UID',
d_login_in String comment '登入时间',
d_login_out String comment '登出时间',
n_ip bigint comment 'IP地址'
)
COMMENT '游戏1登录日志表'
PARTITIONED by (dt String comment '数据日期')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ';' 
collection items terminated by ','
map keys terminated by ':'
stored as parquet
TBLPROPERTIES ('creator'='lhx', 'crate_time'='2022-02-24')
;

drop table ods.game1_player;

create external table IF NOT EXISTS ods.game1_player
(n_uid bigint comment '用户唯一UID',
d_create String comment '角色创角时间',
n_lv bigint comment '角色游戏内登记',
n_force bigint comment '角色最高战斗力',
s_phone_type String comment '登录机型'
)
COMMENT '游戏1角色表'
PARTITIONED by (dt String comment '数据录入日期')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ';' 
collection items terminated by ','
map keys terminated by ':'
stored as parquet
TBLPROPERTIES ('creator'='lhx', 'crate_time'='2022-02-24')
;

-- 分区表partition

-- 分区字段不是表中已经存在的数据，可以将分区字段看成伪列
-- 增加分区
alter table ods.game1_log_player_login add partition(dt='2022-02-01') 
										   partition(dt='2020-02-02')
										   partition(dt='2020-02-03')
										   partition(dt='2020-02-04')
										   partition(dt='2020-02-05');
-- 查看分区
show partitions ods.game1_log_player_login;

-- 删除分区
alter table ods.game1_log_player_login drop partition(dt='2020-06-03'),
									    	partition(dt='2020-06-04');
									    
-- 分桶表（不常用）

-- 修改表、删表
alter table game1_log_player_login rename to game2_log_player_login;
alter table game1_log_player_login change column n_lv n_lv_max int;						    
alter table game1_log_player_login change column n_lv_max n_lv_max String;
alter table game1_log_player_login add columns (s_phone_type string);
drop table game1_log_player_login;

-- 数据导入
--load导入数据
--import导入数据
--常用ETL工具或代码实现，详情见Sqoop、DdataX技术栈

-- 增
insert into table game1_log_player_login partition(dt='2022-02-01')
values (001,'2022-02-01 09:01:01', '2022-02-01 09:30:01',3232256121), 
	   (002,'2022-02-01 09:15:01', '2022-02-01 09:45:01',3232256121),
	   (003,'2022-02-01 09:30:01', '2022-02-01 09:35:01',3232256121),
	   (001,'2022-02-01 10:01:01', '2022-02-01 11:30:01',3232256121),
	   (001,'2022-02-01 12:01:01', '2022-02-01 13:30:01',3232256121),
	   (001,'2022-02-01 16:01:01', '2022-02-01 20:30:01',3232256121);

select * from game1_log_player_login;	  

-- 删、改
-- Attempt to do update or delete using transaction manager that does not support these operations.
-- 建表的时候需要加上TBLPROPERTIES ("transactional"="true",...)才能实现DML命令（增删改查）
-- HDFS不支持文件的修改
-- 总结：Hadoop内尽量不要改

-- 查（重点）
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]]
[LIMIT [offset,] rows]

-- 运算符
-- 比较运算符：=；!=；is [not] null；LIKE；between；REGEXP；....
-- 逻辑运算符：and、or、not

-- 关联查询（表连接）
--1. 内连接: [inner] join
--2. 外连接 (outer join)
--  左外连接。 left [outer] join，左表的数据全部显示
--  右外连接。 right [outer] join，右表的数据全部显示
--  全外连接。 full [outer] join，两张表的数据都显示
-- 关联查询注意连接条件，连接条件出错会出现笛卡尔积

-- 排序
--order by。执行全局排序，效率低。生产环境中慎用
--sort by。使数据局部有序(在reduce内部有序)
--distribute by。按照指定的条件将数据分组，常与sort by联用，使数据局部有序
--cluster by。当distribute by 与 sort by是同一个字段时，可使用cluster by简化语法

-- 函数

-- with as 作为子查询，增加代码可读性

-- 查看系统自带函数
show functions;
-- 显示自带函数的用法
desc function to_date;
desc function extended to_date;

-- 日期函数（常用）
-- 当前日期
select current_timestamp();
-- 时间戳转日期
select from_unixtime(1645683780);
select from_unixtime(1645683780, 'yyyyMMdd');
select from_unixtime(1645683780, 'yyyy-MM-dd HH:mm:ss');
-- 日期转时间戳
select unix_timestamp('2022-02-24 14:23:00');
-- 计算时差
select datediff('2020-04-18','2022-02-21');
-- 字符串转时间（字符串必须为：yyyy-MM-dd格式）
select to_date('2022-01-01');
select to_date('2022-01-01 12:12:12');
-- 日期、时间戳、字符串类型格式化输出标准时间格式
select date_format(current_timestamp(), 'yyyy-MM-dd HH:mm:ss');
select date_format(current_date(), 'yyyyMMdd');
select date_format('2022-02-01', 'yyyy-MM-dd HH:mm:ss');

-- 条件函数（常用）
CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END

-- 窗口函数（重点）
-- 计算uid对应手机型号的登录次数
select a.n_uid,b.s_phone_type ,count(a.n_uid) over (partition by b.s_phone_type order by b.n_force) login_cnt
from game1_log_player_login a
left join ods.game1_player b
on a.n_uid =b.n_uid ;

-- 排名函数（结合over窗口函数使用）
row_number()。排名顺序增加不会重复；如1、2、3、4、... ...
RANK()。 排名相等会在名次中留下空位；如1、2、2、4、5、... ...
DENSE_RANK()。 排名相等会在名次中不会留下空位 ；如1、2、2、3、4、... ...

-- 序列函数（难点）
lag。返回当前数据行的上一行数据
lead。返回当前数据行的下一行数据
first_value。取分组内排序后，截止到当前行，第一个值
last_value。分组内排序后，截止到当前行，最后一个值
ntile。将分组的数据按照顺序切分成n片，返回当前切片值

-- 自定义函数（难点）
UDF（User Defined Function）。用户自定义函数，一进一出
UDAF（User Defined Aggregation Function）。用户自定义聚集函数，多进一出；类似于：count/max/min
UDTF（User Defined Table-Generating Functions）。用户自定义表生成函数，一进多出；类似于：explode

-- UDF开发实例
--继承org.apache.hadoop.hive.ql.exec.UDF
--需要实现evaluate函数；evaluate函数支持重载
--UDF必须要有返回类型，可以返回null，但是返回类型不能为void
-- 身份证脱敏函数demo
select identifyencrypt('320502202111093517');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198

UDF开发：

package hive.udf;

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

public class identifyencrypt extends UDF{
    public Text evaluate(final Text t) {
        if (18 == t.getLength()) {
            String identify = t.toString();
            return new Text(identify.substring(0,4).concat("***********").concat(identify.substring(15,18)));
        }
        return new Text("不符合格式");
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

打包后将jar上传HDFS

在Hive命令行中创建永久函数

create function identifyencrypt as 'hive.udf.identifyencrypt' using jar 'hdfs:/user/hadoop/jar/hive-udf-identifyencrypt.jar';
1

6.调优思路

• 把Hive的计算引擎MapReduce换掉

• 比较大的表使用分区表，分桶表

• 存储格式用Parquet

• 参数优化（合并多个小文件）

  • 在map执行前合并小文件，减少map数

    # 缺省参数
    set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
    1
    2

  • 在Map-Reduce的任务结束时合并小文件

    # 在 map-only 任务结束时合并小文件，默认true
    SET hive.merge.mapfiles = true;
    # 在 map-reduce 任务结束时合并小文件，默认false
    SET hive.merge.mapredfiles = true;
    # 合并文件的大小，默认256M
    SET hive.merge.size.per.task = 268435456;
    # 当输出文件的平均大小小于该值时，启动一个独立的map-reduce任务进行文件merge
    SET hive.merge.smallfiles.avgsize = 16777216;
    1
    2
    3
    4
    5
    6
    7
    8

  参数优化需要仔细看参数代表的含义：

  https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties

• 查询SQL优化（常用）

7.jdbc连接Hive

• 启动hiveserver2（也可以后台启动加&）

  hive --service  hiveserver2
  1

• Linux上beeline工具测试使用jdbc方式连接

  $HIVE_HOME/bin/beeline
  !connect jdbc:hive2://192.168.80.121:10000 root 123456;
  1
  2

• hiveserver端口号默认是10000使用beeline通过jdbc连接上之后就可以像client一样操作，常用dbeaver操作hive。

• hiveserver2会同时启动一个webui，端口号默认为10002，可以通过http://localhost:10002/访问

  $HIVE_HOME/bin/beeline
  !connect jdbc:hive2://192.168.80.121:10000 root 123456;

  
  1

• hiveserver端口号默认是10000使用beeline通过jdbc连接上之后就可以像client一样操作，常用dbeaver操作hive。
  
  
• hiveserver2会同时启动一个webui，端口号默认为10002，可以通过http://localhost:10002/访问