hive SQL

发表于 | 分类于

Hive SQL, 或者叫HQL，和其他SQL语言相似。
这篇文章是本人在hive学习和开发中的整理和总结。

1. 表操作

1.1. 建表

1.1.1. 默认建表

1
2
3
4
5
6
7
8
9
10
create table if not exists db_name.table_name (
    uid bigint comment 'uid',
    value bigint comment 'value'
)
comment "table description"
partitioned by (pt string)
row format delimited
fields terminated by '\t'
lines terminated by '\n'
stored as orc;  -- 或者是textfile

1.1.2. AS建表

只能建内部表，而且不能建分区表。

1
2
3
4
create table if not exists db_name.table_name as
select
...
;

1.2.3. LIKE建表

复制表结构，不复制数据。

1
2
CREATE TABLE if not exists db_name.table_name
LIKE db_name.origin_table_name;

1.2. 删表

1
drop table db_name.table_name;

1.3. 修改表

1.3.1. 修改表名

1
ALTER TABLE db_name.table_name rename TO db_name.table_name_new;

1.3.2. 增加列

1
alter table db_name.table_name add columns (col int comment 'col');

1.3.3. 删除列

1
alter table db_name.table_name drop col;

1.3.4. 修改列名、列类型

1
alter table db_name.table_name change col col_name_new new_type;

1.3.5. 修改列顺序

放到第一列

1
alter table db_name.table_name change col first;

放到某一列后

1
alter table db_name.table_name change col after col2;

2. 插入数据

2.1. SELECT数据

略

2.2. 从本地文件获取数据

1
2
load data local inpath './file_name'
overwrite into table db_name.table_name partition (pt = '${bizdate}');

2.3. 从HDFS上获取数据

1
2
load data inpath 'hdfs://bigdata/user/yourname/proj1/file1'
overwrite into table db_name.table_name partition (pt = '${bizdate}');

3. 查询

3.1. 聚合和拆分

3.1.1. 一行拆成多行

1
2
3
4
5
6
7
select
    uid,
    item
from
    db_name.table_name
    lateral view explode(split(item_list, ",")) t as item
;
1
2
3
4
5
6
7
8
9
-- 附带位置信息
select
    uid,
    idx,
    item
from
    db_name.table_name
    lateral view posexplode(split(item_list, ",")) t as idx, item
;

3.1.2. 多行聚合成一行

聚合函数

3.2. JOIN

3.2.1. left outer join

结果表的键值域等于左表；
左表键值不在右表中时，结果表的右表键置NULL。

3.2.2. full outer join

结果表的键值域是左右表键的并集；
哪一边表的键值缺失就置NULL。

3.3. UNION

注意字段名要完全一致

1
2
3
4
5
select
...
union all
select
...

4. 内置函数

4.1. 字符串函数

4.1.1. 字符串截取函数

1
2
substr(string A, int start, int len)
substring(string A, int start, int len)

4.1.2. JSON字符串

将字符串类型的数据读取为json类型，并得到其中的元素key的值
第一个参数填写json对象变量，第二个参数使用$表示json变量标识，然后用.读取对象或数组；

1
2
3
select
    get_json_object("{\"key\":1}", '$.key')
;

4.2. 时间函数

4.2.1. 日期转为时间戳

返回值: bigint

1
2
3
4
5
6
7
8
9
10
-- 1. 获取本地时区下的时间戳
unix_timestamp()

-- 2. 时间字符串转换成时间戳
unix_timestamp(string date)
unix_timestamp('2009-03-20 11:30:01')

-- 3. 指定时间字符串格式字符串转换成Unix时间戳
unix_timestamp(string date, string pattern)
unix_timestamp('2009-03-20', 'yyyy-MM-dd')

4.2.2. 时间戳转为日期

返回值: string

1
2
from_unixtime(bigint unixtime[, string format])
from_unixtime(gmt_create,'yyyy/MM/dd HH:mm:ss')

4.2.3. date_add

从开始时间startdate加上days
返回 string

1
2
date_add(string startdate, int days)
date_add("2019-01-01", 1)

4.2.4. date_sub

从开始时间startdate减去days
返回 string

1
2
date_sub(string startdate, int days)
date_sub("2019-01-01", 1)

4.3. 正则函数

4.3.1. regexp_extract

抽取字符串subject中符合正则表达式pattern的第index个部分的子字符串
pattern转义字符需要两个斜杠
返回string

1
regexp_extract(string subject, string pattern, int index)

4.3.2. regexp_replace

按照Java正则表达式PATTERN将字符串INTIAL_STRING中符合条件的部分成REPLACEMENT所指定的字符串
返回string

1
regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)

4.3.3. regexp

是否匹配正则
语法: A REGEXP B
操作类型: string

1
select count(*) from olap_b_dw_hotelorder_f where create_date_wid not regexp '\\d{8}'

4.4. 条件函数

4.4.1. IF

如果满足条件，则返回A， 否则返回B

1
if(val = 10, 1, 0)

4.4.2. CASE

1
case val when 1 then 1 when 0 then 0 else -1 end

4.5. 中位数函数

整型中位数

1
percentile(int_val, 99)

浮点中位数

1
percentile_approx(double_val, 99)

5. 其他语法

5.1. 调用python脚本

1
2
3
4
5
6
7
8
9
10
11
12
add file hdfs://bigdata/user/yourname/python.py;

select
    transform(
        col1,
        col2
    )
    using 'python python.py' as
    (col1 string, col2 bigint)
from
    db_name.table_name
;

5.2. 设置局部常量

1
2
set hivevar:val1 = 1;
select ${val1};

5.3. 设置Reduce个数

1
set mapred.reduce.tasks=400;

5.3. 类型转换

1
cast(col as bigint)

6. 复合数据类型

6.1. array

1
2
3
4
5
6
7
8
9
10
11
12
create table person(name string,work_locations array<string>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';
-- 数据
-- biansutao beijing,shanghai,tianjin,hangzhou
-- linan changchu,chengdu,wuhan
-- 入库数据
LOAD DATA LOCAL INPATH '/home/hadoop/person.txt' OVERWRITE INTO TABLE person;
select * from person;
# biansutao       ["beijing","shanghai","tianjin","hangzhou"]
# linan           ["changchu","chengdu","wuhan"]

6.2. map

1
2
3
4
5
6
7
8
9
10
11
12
13
create table score(name string, score map<string,int>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':';
-- 数据
-- biansutao '数学':80,'语文':89,'英语':95
-- jobs '语文':60,'数学':80,'英语':99
-- 入库数据
LOAD DATA LOCAL INPATH '/home/hadoop/score.txt' OVERWRITE INTO TABLE score;
select * from score;
# biansutao       {"数学":80,"语文":89,"英语":95}
# jobs            {"语文":60,"数学":80,"英语":99}

6.3. struct

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE test(id int,course struct<course:string,score:int>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';
-- 数据
-- 1 english,80
-- 2 math,89
-- 3 chinese,95
-- 入库
LOAD DATA LOCAL INPATH '/home/hadoop/test.txt' OVERWRITE INTO TABLE test;
-- 查询
select * from test;
# 1       {"course":"english","score":80}
# 2       {"course":"math","score":89}
# 3       {"course":"chinese","score":95}