1. 建表 & 拉取表
建表
create table if not exists student(
name string,
age BIGINT,
subject array<string>, -- 学科
score map<string, float>, -- 学科对应成绩
address struct<houseNumber: int, street: string>
)
row format delimited
fields terminated by "\t"; -- 列间隔符
加载数据
-- local 从本地加载,省略则从HDFS加载
load data local inpath '/root/covid/2020-02.csv' into table covid2020;
-- 加载数据到分区
load data inpath '/data/covid/2020-02.csv' into table covid2020 partition(dt='2020-02');
2. 插入数据 insert + select
-- overwrite 覆盖
-- into 追加
insert overwrite/into table table1
select id
,name
from table2;
-- 一次查询,插入多个表或分区
from table2
insert into/overwrite table table1 partition(dt="01")
select_value1
insert into/overwrite table table1 partition(dt="02")
select_value2
;
- 实例
create table if not exists fanchengwen_test_save
(
cfrnid string,
cr_cnt array<array<double>>
)
PARTITIONED BY (`pt` string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
;
insert overwrite table fanchengwen_test_save partition(pt='2023-09-15')
select
cfrnid
,concat(respon, lender, crtype, ricomm) as cr_cnt
-- , pt
from fcw_dev.mx_circulo_creditos_cnt_di
where pt = '2023-09-15'
3. 查询
3.2 关系操作符
-- <==> 都为null或都不为null,返回true
where A <==> B
-- 判空,必须用is (not) null
-- 若string类型为"",则hive的is null判断反回为False
where job is null
-- in 集合
where job(col_name) in('研发', ‘销售’)
3.3 聚合函数
多行数据一起计算,返回一行值
count(*) 统计行数,包含null
count(col_name) 统计指定列的行数,不包含null
max() 不包含null
min()
sum()
avg()
select count(*) from table1;
3.4 where
-- where 中可以使用函数
select * from table1 where length(name) > 5;
-- where中不能使用聚合函数
3.5 分组聚合
选择分组后,select字段只能选择 分组的字段(job)、聚合函数。
-- 分组后, 组内count(*)
select job
,count(*)
from emp
group by job
;
3.6 having 筛选分组后结果
select job
,count(*) cnt
from emp
group by job
having cnt >= 2;
-- 相当于
select job
,cnt
from
(
select job
,count(*) cnt
from emp
group by job
) t
where cnt >= 2;
4. join 横向拼接
4.1 等值连接 & 不等值连接
-- 等值连接
select
*
from table1
join table2
on table1.id = table2.id
;
-- 不等值连接
on 中不是"=", 早期版本hive 不支持不等值连接
4.2 两表连接
4.2.1 内连接
join
或inner join
;
两表连接字段的交集,不能join的行不显示。
select
tb1.name,
tb2.name
from table1 tb1
join / inner join table2 tb2
on tb1.id = tb2.id;
4.2.2 左外连接 & 右外连接
left join
或left outer join
;
保留左表所有数据,右表补空。
右外连接 相反。
select
tb1.name,
tb2.name
from table1 tb1
left/right join table2 tb2
on tb1.id = tb2.id;
4.2.3 满外连接
full join
或full outer join
保留两表左右,不能连接的字段补空。
select
tb1.name,
tb2.name
from table1 tb1
full join table2 tb2
on tb1.id = tb2.id;
4.3 多表连接
select
*
from table1
join table2
on table1.id = table2.id
join table3
on table2.name = table3.name
(
select
id, name
from stu_info
where course_id = '01'
) t1
full outer join
(
select
id, name
from stu_info
where course_id = '02'
) t2
on t1.id = t2.id
full outer join
(
select
id, name
from stu_info
where course_id = '03'
) t3
-- 如果某id 不在t1中在t2中
-- 如果t1.id 为空则返回t2.id,如果不为空则返回t1.id
on nvl(t1.id, t2.id) = t3.id
4.4 笛卡尔积
select
*
from table1
join table2
-- 或
select
*
from table1, table2
5. union 纵向拼接
- 两表上下拼接,对应字段的数量、类型都必须相同;
- 对应字段名不一样,能连接上;最终字段名以第一个表的字段名为准;
union
连接的必须是select
查询语句;- 连接完后,当成一个
select
查询使用就行; union all
不会对相同数据去重,union
会对上下两部分相同部分去重。
select
*
from stu
where score = 30
union
select
*
from stu
where score = 40
;
6. 排序
6.1 order by 全局排序
默认升序(asc), desc 降序descend
hive 最终执行reduce时,只能一个reduce以实现全局排序,数据量大时order by不合适;
可以使用order by + limit n ,每个Map取出n个,减少了reduce时的压力
select
*
from table1
order by col_name desc
;
6.2 sort by reduce内部排序
保证每个reduce内有序,全局不保证有序。
-- 设置reduce个数
set mapreduce.job.reduces=3;
-- 查看reduce个数
set mapreduce.job.reduces;
-- reduce内部排序
select
*
from emp
sort by col_1 desc;
7. 其他
7.1 select中判断筛选出null
7.1.1 MYSQL ifnull
筛选出第二大的,但可能初筛排序后只有一个,再筛第二大为null
ifnull 指定为null时,替换为什么值。
select
ifnull(
(
select
salary
from Employee
order by salary desc
limit 1
offset 1
)
,null
) as No2_highest_salary
7.1.2 Hive SQL if( , , )
如果column是null,返回第二个值,不是则返回第三个值
if(column is null, 'IS NULL', 'IS NOT NULL')
7.2 字段拼接 concat、concat_ws
- concat_ws 指定拼接的间隔字符
select
concat_ws(',', 'hello', 'world!') as col1
'''
+------------+
| col1|
+------------+
|hello,world!|
+------------+
'''
- concat 直接拼接, 也可以用来合并array
query_sql = """
select
concat('hello', 'world!') as col1
"""
spark.sql(query_sql).show()
'''
+-----------+
| col1|
+-----------+
|helloworld!|
+-----------+
'''
7.3 时间(字符串)字段处理
7.3.1 获取月份
# 拼接年和月
query_sql = """
select
concat_ws('-', split(pt, '-')[0], month(pt)) as month
,*
from jinrongshiyebu_international.mx_circulo_creditos_windows_diff_v1_di
where pt < '2024-01-01'
"""
d = spark.sql(query_sql)
d.select(d.month).show()
'''
+-------+
| month|
+-------+
|2022-11|
|2022-11|
|2022-11|
|2022-11|
|2022-11|
|2022-11|
+-------+
'''
7.3.2 获取一年中第几周 weekofyear
select
weekofyear(pt) as week
,pt
from jinrongshiyebu_international.mx_circulo_creditos_windows_diff_v1_di
where pt < '2024-01-01'
'''
+----+----------+
|week| pt|
+----+----------+
| 11|2023-03-14|
| 11|2023-03-14|
| 36|2022-09-07|
| 36|2022-09-07|
+----+----------+
'''
7.4 HiveQL 自定义函数 transform
- 类似pyspark的udf
- 用于处理array, 处理完返回的也为array
select
transform(split('2023-01-01', '-'), x->cast(x as double)) as time_list
'''
+------------------+
| time_list|
+------------------+
|[2023.0, 1.0, 1.0]|
+------------------+
'''
- 使用python脚本文件
py_udf.py
import sys
for line in sys.stdin:
line = line.strip()
team,state,rank = line.split()
state = state.upper()
print( ' '.join([team,state,str(rank)]))
python文件上传hdfs ;
添加到hive add file hdfs://xxxxx
select
transform(x, y, z) using 'py_udf.py' as (x, y, z)
from ...
7.5 显式类型转换 cast
select
cast('none' as int) as none_col2
显示类型转换
cast(‘100’ as int)
select cast(12.1 as int); >>> 12
7.6 select特征转为json/dict
select
to_json(struct(*)) as feas
-- 值为None的自动不转为key:val
from my_table
where pt = '2023-07-01'
8. SQL语句结构
8.1 语法顺序
6. select all/distinct expr1
,expr2
1. from tb1 inner join tb2
2. on tb1.key=tb2.key
3. where condition -- 过滤
4. group by condition -- 分组查询
5. having condition -- 分组后组内过滤
7. order by col_list -- 最终查询结果排序
8. limit number
offset number
;
8.2 执行顺序
执行过程中,每个步骤都会为下个步骤生成一个虚拟表,作为下一步骤的输入。
- from 查表,若涉及到join两张表,会执行笛卡尔乘积,生成vt1;(join前先对两张表进行select筛选,生成较小的表)
- on筛选,在vt1的基础上筛选,一般为tb1.key = tb2,key,生成vt2;
- 若join是out join,会添加外部行,例如left join将左表在2过滤的行添加进来;
- 若继续join多个表,则继续进行笛卡尔积、筛选操作,生成vt3;
- where筛选,不像out join那样筛选后再添加外部行,是最终筛选。生成vt4;
- group by 分组,后期select字句中avg、ifnull等操作均是以组为单位计算,生成vt5;
- having筛选,唯一一个应用于分组后筛选,生成vt6;
- select 筛选字段,子句对字段进行操作,生成vt8;
- distinct 按某字段去重,生成vt9;
- order by 排序,生成vt10;
- limit 对表分页,生成vt11;