HiveQL / SQL

rejudge

已于 2024-01-17 10:55:33 修改

阅读量607

点赞数 8

分类专栏： Python 文章标签： hive sql hadoop

于 2024-01-07 20:02:47 首次发布

本文链接：https://blog.csdn.net/qq_45249685/article/details/133244887

版权

Python 专栏收录该内容

43 篇文章 3 订阅

订阅专栏

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
;