程序员经验分享-hwhale

大数据hive篇--常用操作

2023-11-19

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的数字不能大于表达式中()的个数。

/ 转义符
(), (?

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

hive

大数据hive篇--常用操作 的相关文章

随机推荐

  • for循环之斐波拉契数列

    题目大意 菲波那契数列是指这样的数列 数列的第一个和第二个数都为1 接下来每个数都等于前面2个数之和 给出一个正整数k 要求菲波那契数列中第k个数是多少 Input 输入一行 包含一个正整数k 1 lt k lt 46 Output 输出一

  • 在C++中调用OpenSSL库进行编程

    目录 OpenSSL简介 下载OpenSSL库并配置实验环境 OpenSSL库的加密函数的认识 使用 EVP 库实现 DES 和 AES 加密 EVP EncryptUpdate 函数参数详解 OpenSSL加密实践 RSA 密钥生成 RS

  • 第一次面试前端实习生心得

    今天第一次去面试前端岗位的实习生 公司规模不算大吧 不过也有好几个部门 说说我的面试心得吧 首先是hr面 如下是她问的问题 学这个多久了 大四还有没有课 是否只需完成毕业设计就行了不用上课 同学暑假都在干嘛 学校课程学了什么 同学们主要找哪

  • (转载）我们需要什么样的字段类型

    数据库定义到char类型的字段时 不知道大家是否会犹豫一下 到底选char nchar varchar nvarchar text ntext中哪一种呢 结果很可能是两种 一种是节俭人士的选择 最好是用定长的 感觉比变长能省些空间 而且处理

  • Java 两种zero-copy零拷贝技术mmap和sendfile的介绍

    详细介绍了两种zero copy零拷贝技术mmap和sendfile的概念和基本原理 目录 1 标准IO 2 零拷贝 2 1 sendfile调用 2 1 mmap调用 2 2 MQ中的应用 1 标准IO 很多软件是基于server cli

  • MES在流程和离散型制造企业的应用存在哪些差别？

    企业的生产方式 主要可以分为按定单生产 按库存生产或上述两者的组合 从生产类型上考虑 则可以分为批量生产和单件小批生产 从产品类型和生产工艺组织方式上 企业的行业类型可分为流程生产行业和离散制造行业 典型的流程生产行业有医药 石油化工 电力

  • C++ 拷贝构造函数和赋值运算符

    本文主要介绍了拷贝构造函数和赋值运算符的区别 以及在什么时候调用拷贝构造函数 什么情况下调用赋值运算符 最后 简单的分析了下深拷贝和浅拷贝的问题 拷贝构造函数和赋值运算符 在默认情况下 用户没有定义 但是也没有显式的删除 编译器会自动的隐式

  • Direct3D光照

    光照的组成 环境光 这种类型的光经其他表面反射到达物体表面 并照亮整个场景 要想以较低代价粗略模拟这类反射光 环境光是一个很好的选择 漫射光 这种类型光沿着特定的方向传播 当它到达某一表面时 将沿着各个方向均匀反射 无论从哪个方位观察 表面

  • Spring的资源访问接口---Resource

    JDK提供的访问资源的类 File等 不能很好满足各种某些资源的访问需求 比如缺少从类路径和Web容器的上下文中获取资源的资源操作类 Spring的Resource接口提供了更好用的资源访问能力 Spring使用Resource访问各种资源

  • linux 交叉编译找不到库文件

    网上大众的作法这里不做介绍 在编译一些库的时候可能某些工具会用到宿主机的 而这些工具在调用的时候会找和宿主机匹配的库 而交叉编译的库是不能用的 遇见这种情况基本是解决不了的 但是可以通过configure 的选项去掉这些过程 举个例子 我在

  • MySQL多列字段去重的案例实践

    同事提了个需求 如下测试表 有code cdate和ctotal三列 select from tt 现在要得到code的唯一值 但同时带着cdate和ctotal两个字段 提起 唯一值 想到的就是distinct distinct关键字可以

  • Nodejs版本管理工具mvn部署

    部署mvn curl o https raw githubusercontent com creationix nvm v0 34 0 install sh bash 添加环境变量 vim zshrc export NVM DIR HOME

  • Webpack中的tree-shaking

    文章目录 Webpack中的tree shaking 简单实践 副作用sideEffects sideEffects配置项 false 数组 结论 Webpack中的tree shaking tree shaking就是把js文件中无用的模

  • 【编程题】——求链表的中间节点

    题目 求链表的中间结点 如果链表中结点总数为奇数 返回中间结点 如果结点总数是偶数 返回中间两个结点的任意一个 思路 定义两个指针 一个指针一次走一步 另一个指针一次走两步 当走得快的指针到达链表末尾的时候 走得慢的指针刚好达到链表的中间节

  • 文件管理系统（操作系统）——9张思维导图

    文件管理系统 1 文件管理 1 1 一个文件的逻辑结构 比如一个文本txt文件 又或者Excel文件 在我们用户看来 它是长什么样的 这个就是逻辑结构 几个概念 逻辑结构 就是指在用户看来 单个文件内部的数据应该是如何组织起来的 物理结构

  • 黑马SpringBoot笔记

    基础篇 把Tomcat服务器更换成Jetty服务器 排除Tomcat依赖更换为Jetty

  • 【Java面试题汇总】Redis篇（2023版）

    导航 黑马Java笔记 踩坑汇总 JavaSE JavaWeb SSM SpringBoot 瑞吉外卖 SpringCloud 黑马旅游 谷粒商城 学成在线 牛客面试题 目录 1 说说你对Redis的了解 2 说说Redis的单线程架构 3

  • 7.java类中的方法

    1 类中的方法 1 实例方法 格式 访问限制修饰符 方法的返回值数据类型 方法名称 参数列表 方法体 解释 访问限制修饰符 public 缺省的 方法的返回值数据类型 就是方法的执行结果类型 有返回值时 方法的返回值数据类型一定是方法执行结

  • FPGA硬件工程师Verilog面试题（基础篇二）

    作者简介 大家好我是 嵌入式基地 是一名嵌入式工程师 希望一起努力 一起进步 个人主页 嵌入式基地 系列专栏 FPGA Verilog 习题专栏 微信公众号 嵌入式基地 FPGA硬件工程师Verilog面试题 二 习题一 多功能数据处理器

  • 大数据hive篇--常用操作

    文章目录 hive常用操作 一 建表 1 自定义分隔符 2 JSON分隔符 3 正则分隔符 将查询的结果导入新表 表的类别 外部表 内部表 分区表 导入数据 使用分区表 声明存储格式 二 常用函数 开窗函数 开窗函数常用的函数 炸裂函数 列

热门标签