大数据hive篇--常用操作

hive常用操作

一、建表

1.自定义分隔符

create table t_person(
    id string,
    name string,
    salary double,
    birthday date,
    sex char(1),
    hobbies array<string>,
    cards map<string,string>,
    addr struct<city:string,zipCode:string>
) row format delimited fields terminated by ','--列的分割
collection items terminated by '-' --数组 struct的属性 map的kv和kv之间
map keys terminated by '|' -- map的k与v的分割
lines terminated by '\n'; --行数据之间的分割

导入数据：
– local 代表本地（Hive所在的机器，是linux不是windows）路径，如果不写，表示读取文件来自于HDFS
– overwrite 是覆盖的意思，如果t_person表有数据会被覆盖掉;如果不屑，表示不覆盖。
命令：load data [local] inpath ‘文件路径’ [overwrite] into table 表名;

示例：从本地(Hive所在的linux)导入数据到hive
load data local inpath ‘/opt/person1.txt’ into table t_person;
示例：从hdfs导入数据到hive
先上传文件到hdfs：hdfs dfs -put /opt/person.txt /baizhi/person1.txt
再从hdfs中导入数据到hive：load data inpath ‘/baizhi/person1.txt’ overwrite into table t_person;

2 JSON分隔符

在hive的客户端执行一下命令(临时添加jar到hive的classpath，有效期本链接内)
add jar /opt/installs/hive2.3.7/hcatalog/share/hcatalog/hive-hcatalog-core-2.3.7.jar

补充：永久添加，Hive服务器级别有效。

1. 将需要添加到hive的classpath的jar，拷贝到hive下的auxlib（需要手动在hive根目录下创建）目录下，
2. 重启hiveserver2即可。

create table t_person2(
    id string,
    name string,
    sex char(1),
    birth date,
    hobbies array<string>,
    cards map<string,string>,
    addr struct<city:string,zipCode:string>
)row format serde 'org.apache.hive.hcatalog.data.JsonSerDe';

#导入数据
load data local inpath ‘/opt/person.json’ overwrite into table t_person2;

#查询数据
select * from t_person2;

3 正则分隔符

下边列于列之间的分割符没有完全统一，这时候可以使用正则分隔符

level ip log_time app service method
以下为日志内容：
INFO 192.168.1.1 2019-10-19 QQ com.baizhi.service.IUserService#login
INFO 192.168.1.1 2019-10-19 QQ com.baizhi.service.IUserService#login
ERROR 192.168.1.3 2019-10-19 QQ com.baizhi.service.IUserService#save
WARN 192.168.1.2 2019-10-19 QQ com.baizhi.service.IUserService#login
DEBUG 192.168.1.3 2019-10-19 QQ com.baizhi.service.IUserService#login
ERROR 192.168.1.1 2019-10-19 QQ com.baizhi.service.IUserService#register

create table t_access(
    level string,
    ip string,
    log_time date,
    app string,
    service string,
    method string
)row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'--正则表达式的格式转化类
with serdeproperties("input.regex"="(.*)\\s(.*)\\s(.*)\\s(.*)\\s(.*)#(.*)");--(.*) 表示任意字符 \\s表示空格

load data local inpath ‘/opt/access.log’ into table t_access;

将查询的结果导入新表

create table user_kws as 
select cc.user_id,str_to_map(concat_ws(',',collect_set(cc.kw_w))) as wm
from(
select a.user_id, concat_ws(':',b.kw,cast (count(1) as string)) as kw_w 
from user_actions as a 
left outer JOIN (select article_id,kw from articles
lateral view outer explode(key_words) t as kw) b
on (a.article_id = b.article_id)
group by a.user_id,b.kw
) as cc 
group by cc.user_id;

表的类别

外部表

create external table t_external_person(
	id int,
    name string,
    salary double,
    birthday date,
    sex char(1),
    hobbies array<string>,
    cards map<string,string>,
    addr struct<city:string,zipCode:string>
)row format delimited 
fields terminated by ','
collection items terminated by '-'
map keys terminated by '|'
lines terminated by '\n';

内部表

去掉external即可

分区表

create external table t_user_part(
	id string,
	name string,
	birth date,
	salary double
)partitioned by(country string,city string)--指定分区列,按照国家和城市分区。
row format delimited
fields terminated by ',' 
lines terminated by '\n';

导入数据

load data local inpath '/opt/bj.txt' into table t_user_part partition(country='china',city='bj');

load data local inpath '/opt/tj.txt' into table t_user_part partition(country='china',city='tj');

使用分区表

– 查看分区信息
show partitions t_user_part;

– 使用分区查询：根据分区字段查询，只在满足条件的分区上查询，提高查询效率
select * from t_user_part where city = ‘bj’

– 分区表并不影响全表范围的查询
select * from t_user_part

声明存储格式

create table t_person_parquet(
    id string,
    name string,
    salary double,
    birthday date,
    sex char(1),
    hobbies array<string>,
    cards map<string,string>,
    addr struct<city:string,zipCode:string>
) 
stored as parquet;

二 常用函数

开窗函数

开窗函数：根据某个开窗依据在原始表上开多个窗口（每个窗口可以包含原始表中任意行的数据，等同于分组后的一组数据），对每个窗口的数据执行统计操作（就是应用组函数）。

开窗函数类似于分组统计，也是对原表数据进行分组（这里就是一个窗口）聚合统计。但2者有以下区别

分组对一组数据聚合后只返回一个值，开窗函数为一个窗口的每行都返回一个值。
组函数不能和非分组字段联合使用，但开窗函数可以

语法：
组函数 over(partition by 分组依据 [order by 排序字段])

开窗函数常用的函数

– row_number() 不关注重复的，直接排名，1-2-3-4-5-6
– rank() 重复排名,会跳过，1-2-3-4-4-6
– dense_rank() 重复排名,不跳过，1-2-3-4-4-5

炸裂函数

炸裂函数：作用于数组字段，将数组元素炸裂成多行展示

– explode(array|map) 将数组或Map中的元素炸裂成多行展示,且不允许再select其它字段
– 查询所有的爱好
select explode(hobbies) as 爱好 from t_person;
select explode(cards) as (卡号,银行) from t_person;

lateral view：配合炸裂函数使用，将炸裂函数生成的多行数据做成一个临时表，并将临时表自动的拼接到原表后
– lateral view：为表的拼接一个列(炸裂结果)
– 语法：from 原表 lateral view explode(数组字段) 临时表别名 as 临时表的字段名;

– 查看id，name，爱好，并要求：一个爱好一条信息。
select id,name,hobby
from t_person lateral view explode(hobbies) t_hobby as hobby

列转行

collect_set() collect_list() sort_array() 对数组排序

case when

语法：
case
when 条件1 then 结果1
when 条件2 then 结果2
when 条件3 then 结果3
…
else 其他结果
end
类似于java中if（条件）{结果}else

示例：
select id,name,salary,
case
when salary >= 9000 then ‘高薪’
when salary >= 7000 then ‘中等’
when salary >= 5000 then ‘一般’
else ‘垃圾’
end 薪资等级
from t_person;

字符串函数

字符串拼接

concat_ws(“:”,“10”,“00”)
select concat(“10:”,“00”)

字符串内容替换

修改字符串的内容
select regexp_replace(‘acd’,‘cd’,‘bc’)

字符串转map集合

str_to_map(“kw1:1,kw3:1,kw6:1,kw7:1,kw8:1”)

解析字符串为时间类型数据

select date_format(“2023-11-11 04:30:40”,“yyyy-MM-dd HH:mm:ss”)
select to_date(“2023-11-11 08:30:45”)

时间类型函数

解析时间戳为时间类型数据
select from_unixtime(itime,‘yyyy/MM/dd HH:mm:ss’)

hive字符串的分割，截取，正则提取与替换。

一，字符串的分割。

split函数（分割字符串）
语法: split(string str, string pat)
返回值: array
说明: 按照pat字符串分割str，会返回分割后的字符串数组
举例：
1.基本用法

hive> select split('abcdef', 'c') from test;

["ab", "def"]

2.截取字符串中的某个值

hive> select split('abcdef', 'c')[0] from test;

ab

3.特殊字符
如正则表达式中的特殊符号作为分隔符时，需做转义 (前缀加上)

 hive> select split('ab_cd_ef', '\_')[0] from test;
 
ab

hive> select split('ab?cd_ef', '\\?')[0] from test;

ab

如果是在shell中运行，则(前缀加上)

hive -e "select split('ab?cd_ef', '\\\\?')[0] from test" 

注：有些特殊字符转义只需\，而有些需\，eg.?。可能在语句翻译过程中经历经历几次转义。

二，字符串的截取。

substr函数格式 (俗称：字符截取函数)

格式1： substr(string string, int a, int b);

格式2：substr(string string, int a) ;

解释：

格式1：
1、string 需要截取的字符串
2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
3、b 要截取的字符串的长度

格式2：
1、string 需要截取的字符串
2、a 可以理解为从第a个字符开始截取后面所有的字符串。

当有三个参数时。

1、select substr(‘HelloWorld’,0,3) value from dual; //返回结果：Hel，截取从“H”开始3个字符
2、select substr(‘HelloWorld’,1,3) value from dual; //返回结果：Hel，截取从“H”开始3个字符
3、select substr(‘HelloWorld’,2,3) value from dual; //返回结果：ell，截取从“e”开始3个字符
4、select substr(‘HelloWorld’,0,100) value from dual; //返回结果：HelloWorld，100虽然超出预处理的字符串最长度，但不会影响返回结果，系统按预处理字符串最大数量返回。
5、select substr(‘HelloWorld’,5,3) value from dual; //返回结果：oWo
6、select substr(‘Hello World’,5,3) value from dual; //返回结果：o W (中间的空格也算一个字符串，结果是：o空格W)
7、select substr(‘HelloWorld’,-1,3) value from dual; //返回结果：d （从后面倒数第一位开始往后取1个字符，而不是3个。原因：下面红色 第三个注解）
8、select substr(‘HelloWorld’,-2,3) value from dual; //返回结果：ld （从后面倒数第二位开始往后取2个字符，而不是3个。原因：下面红色 第三个注解）
9、select substr(‘HelloWorld’,-3,3) value from dual; //返回结果：rld （从后面倒数第三位开始往后取3个字符）
10、select substr(‘HelloWorld’,-4,3) value from dual; //返回结果：orl （从后面倒数第四位开始往后取3个字符）

（注：当a等于0或1时，都是从第一位开始截取(如：1和2)）
（注：假如HelloWorld之间有空格，那么空格也将算在里面(如：5和6)）
（注：虽然7、8、9、10截取的都是3个字符，结果却不是3 个字符； 只要 |a| ≤ b,取a的个数(如：7、8、9)；当 |a| ≥ b时，才取b的个数，由a决定截取位置(如：9和10)）

当有两个参数时：

11、select substr(‘HelloWorld’,0) value from dual; //返回结果：HelloWorld，截取所有字符
12、select substr(‘HelloWorld’,1) value from dual; //返回结果：HelloWorld，截取所有字符
13、select substr(‘HelloWorld’,2) value from dual; //返回结果：elloWorld，截取从“e”开始之后所有字符
14、select substr(‘HelloWorld’,3) value from dual; //返回结果：lloWorld，截取从“l”开始之后所有字符
15、select substr(‘HelloWorld’,-1) value from dual; //返回结果：d，从最后一个“d”开始 往回截取1个字符
16、select substr(‘HelloWorld’,-2) value from dual; //返回结果：ld，从最后一个“d”开始 往回截取2个字符
17、select substr(‘HelloWorld’,-3) value from dual; //返回结果：rld，从最后一个“d”开始 往回截取3个字符

（注：当只有两个参数时；不管是负几，都是从最后一个开始 往回截取(如：15、16、17)）

三，字符串的替换， hive的替换函数replace（）

REPLACE

用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。

语法

REPLACE ( ‘‘string_replace1’’ , ‘‘string_replace2’’ , ‘‘string_replace3’’ )

参数
‘‘string_replace1’’

待搜索的字符串表达式。string_replace1 可以是字符数据或二进制数据。

‘‘string_replace2’’

待查找的字符串表达式。string_replace2 可以是字符数据或二进制数据。

‘‘string_replace3’’

替换用的字符串表达式。string_replace3 可以是字符数据或二进制数据。
实例

字符串类型参数：

SELECT REPLACE(‘abcdefg bcd’,‘bcd’,‘xxx’)
结果为：axxxefg xxx

二进制类型参数：

1，SELECT REPLACE(100111001101,111,000)
2，SELECT REPLACE(100111001101,111,0)

 1， 结果为：100000001101
 2， 结果为：1000001101

注意：

1，如果参数是支持的字符数据类型之一，并且在string_replace1 中能够找到
string_replace2，那么返回替换后的字符串；反之， 返回 string_replace1；
2，如果参数是支持的 binary(二进制的一种表示格式） 数据类型之一，则返回二进制数据。
3，这个函数是全部替换。

这个函数有一点不足是不支持 text,ntext类型字段的替换。
　　
四，字符串的正则函数（替换与提取）。

1，regexp
语法: A REGEXP B
操作类型: strings
描述: 功能与RLIKE相同

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

与下面查询的效果是等效的：

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

2，正则表达式解析提取函数—regexp_extract(str, regexp， idx)

str是被解析的字符串或字段名
regexp 是正则表达式
idx是返回结果 取表达式的哪一部分 默认值为1。
0表示把整个正则表达式对应的结果全部返回
1表示返回正则表达式中第一个() 对应的结果 以此类推

注意点：idx的数字不能大于表达式中()的个数。

/ 转义符
(), (?😃, (?=), [] 圆括号和方括号
*, +, ?, {n}, {n,}, {n,m} 限定符
^, $, anymetacharacter 位置和顺序

实例：

1）  select regexp_extract('hitdecisiondlist','(i)(.*?)(e)',0) ;        itde
     
2）  select regexp_extract('hitdecisiondlist','(i)(.*?)(e)',1) ;		i
     
3）  select regexp_extract('hitdecisiondlist','(i)(.*?)(e)',2);		td

4） select regexp_extract('x=a3&x=18abc&x=2&y=3&x=4','x=([0-9]+)([a-z]+)',2) from default.dual;       abc

3，正则表达式替换函数：regexp_replace

语法: regexp_replace(string A, string B, string C)

返回值: string

说明：将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符,类似oracle中的regexp_replace函数。

例子：

hive> select regexp_replace('foobar', 'oo|ar', '') from iteblog;      fb

三 操作的问题

1 浮点数比较问题

cast(0.2 as float)