Hive教程
Hive是基于Hadoop的一个数据仓库工具
- 可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能。
- 其本质是将SQL转换为MapReduce的任务进行运算，底层由HDFS来提供数据的存储支持，也可以说hive就是一个MapReduce的客户端
Hive与数据库的区别
Hive的优缺点
- 优点
- 操作接口采用类SQL语法，提供快速开发的能力（简单、容易上手）。避免了去写MapReduce，减少开发人员的学习成本。
- Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。
- 缺点
- Hive将SQL转换为MapReduce任务，Hive 的查询延迟很严重
- Hive 不支持事务
Hive原理
- 用户接口：Client
- CLI（hive shell）
- JDBC/ODBC(java访问hive)
- WEBUI（浏览器访问hive）
- 元数据：Metastore
- 元数据包括：表名、表所属的数据库（默认是default）、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等；
- 默认存储在自带的derby数据库中，推荐使用MySQL存储Metastore
- Hadoop集群
- 使用HDFS进行存储，使用MapReduce进行计算。
- Driver：驱动器
- 解析器（SQL Parser） ：将SQL字符串转换成抽象语法树AST，对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误
- 编译器（Physical Plan）：将AST编译生成逻辑执行计划
- 优化器（Query Optimizer）：对逻辑执行计划进行优化
- 执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。
- 对于Hive来说默认就是mapreduce任务
Hive的交互方式
使用Hive前需要的准备工作
- 启动hadoop集群：因为hql语句会被编译成MR任务提交到集群运行；hive表数据一般存储在HDFS上
- mysql服务：因为对hive操作过程中，需要访问mysql中存储元数据的库及表
- Hive交互shell
- 在任意路径运行hive
- Hive JDBC服务，beeline方式
- 启动hiveserver2服务，前台启动与后台启动方式二选一
hive --service hiveserver2 #前台启动hiveserver2，进行beeline登录时需要克隆一个新的窗口 或 nohup hive --service hiveserver2 & #后台启动hiveserver2- beeline连接hiveserver2服务
beeline --color=true #启动beeline beeline> !connect jdbc:hive2://hadoop02:10000 #使用JDBC登录Hive - Hive的命令
- hive -e hql语句，使用 –e 参数来直接执行hql语句
hive -e "show databases"- hive -f sql文件，使用 –f参数执行包含hql语句的文件
cd /bigdata/install/hive-3.1.4/ vi hive.sql #在hive.sql中输入以下HQL语句，保存退出 create database if not exists myhive; #执行脚本 hive -f /bigdata/install/hive-3.1.4/hive.sql
Hive的数据类型
- 基本数据类型
类型名称 描述 举例 boolean true/false true tinyint 1字节的有符号整数 1 smallint 2字节的有符号整数 1 int 4字节的有符号整数 1 bigint 8字节的有符号整数 1 float 4字节单精度浮点数 1.0 double 8字节单精度浮点数 1.0 string 字符串(不设长度) “abc” varchar 字符串（1-65355长度，超长截断） “abc” timestamp 时间戳 1563157873 date 日期 20190715 - 复合数据类型
类型名称 描述 举例 array 一组有序的字段，字段类型必须相同 array(元素1，元素2) Array（1,2,3） map 一组无序的键值对 map(k1,v1,k2,v2) Map(‘a’,1,'b',2) struct 一组命名的字段，字段类型可以不同 struct(元素1，元素2) Struct('a',1,2,0) - array类型的字段的元素访问方式
- 通过下标获取元素，下标从0开始，eg:如获取第一个元素 array[0]
- map类型字段的元素访问方式
- 通过键获取值，eg: 如获取a这个key对应的value map['a']
- struct类型字段的元素获取方式
- 定义一个字段c的类型为struct{a int, b string}
- 获取a和b的值，使用c.a 和c.b 获取其中的元素值
- array类型的字段的元素访问方式
注意hive就是一个构建数据仓库的工具，只需要在一台服务器上安装就可以了，不需要在多台服务器上安装。
使用hadoop普通用户操作
前提条件
安装好对应版本的hadoop集群，并启动hadoop的HDFS以及YARN服务
hadoop02上安装了MySQL服务，并启动MySQL的服务
下载安装包
进入到/bigdata/soft目录下，使用清华大学的镜像地址下载hive安装包
wget -i -c https://mirrors.tuna.tsinghua.edu.cn/apache/hive/hive-3.1.2/apache-hive-3.1.2-bin.tar.gz
解压
##解压到/bigdata/install目录
tar -zxf apache-hive-3.1.2-bin.tar.gz -C /bigdata/install
cd /bigdata/install
## 修改文件夹名称
mv apache-hive-3.1.2-bin.tar.gz hive-3.1.2
配置
####进入到配置目录，下边查找一下是否有hive-site.xml，如果没有就创建一个
cd hive-3.1.2/conf
vi hive-site.xml
##配置一个hive-site.xml里面会有mysql相关的一些具体信息，配置如下
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://haodoop02:3306/metastore?useSSL=false</value>
</property>
<!--驱动名称-->
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>123456</value>
</property>
<property>
<name>hive.metastore.schema.verification</name>
<value>false</value>
</property>
<property>
<name>hive.metastore.event.db.notification.api.auth</name>
<value>false</value>
</property>
<property>
<name>hive.cli.print.current.db</name>
<value>true</value>
</property>
<property>
<name>hive.cli.print.header</name>
<value>true</value>
</property>
<property>
<name>hive.server2.thrift.bind.host</name>
<value>hadoop02</value>
</property>
<property>
<name>hive.server2.thrift.port</name>
<value>10000</value>
</property>
</configuration>
####在复制一个hive-env.sh文件，里面有hadoop相关的配置信息
####修改HADOOP_HOME的路径
# Set HADOOP_HOME to point to a specific hadoop install directory
HADOOP_HOME=/bigdata/install/hadoop-3.1.4
#####修改hive的配置路径
# Hive Configuration Directory can be controlled by:
export HIVE_CONF_DIR=/bigdata/install/hive-3.1.2/conf
####配置hive的log4j日志输出路径
mkdir -p /bigdata/install/hive-3.1.2/logs
####复制一份模板文件进行修改
cp hive-log4j2.properties.template hive-log4j2.properties
vi hive-log4j2.properties
####修改此文件的hive.log.dir属性的值
#更改以下内容，设置我们的hive的日志文件存放的路径，便于排查问题
property.hive.log.dir=/bigdata/install/hive-3.1.2/logs
配置其他项
上传mysql驱动包，如mysql-connector-java-5.1.38.jar到/bigdata/install/hive-3.1.2/lib目录中
由于运行hive时，需要向mysql数据库中读写元数据，所以需要将mysql的驱动包上传到hive的lib目录下
####下载mysql驱动
wget -i -c https://cdn.mysql.com/archives/mysql-connector-java-5.1/mysql-connector-java-5.1.7.tar.gz
cp mysql-connector-java-5.1.38.jar /bigdata/install/hive-3.1.2/lib
####解决日志jar包冲突，屏蔽掉自带的log4j的jar包
cd /bigdata/install/hive-3.1.2/lib
mv log4j-slf4j-impl-2.10.0.jar log4j-slf4j-impl-2.10.0.jar.bak
环境变量等配置
####切换到root用户下
su - root
####打开`/etc/profile`文件,末尾添加如下内容
export HIVE_HOME=/kkb/install/apache-hive-3.1.2
export PATH=$PATH:$HIVE_HOME/bin
####切换回hadoop用户，并source
su - hadoop
source /etc/profile
初始化元数据库
####新建一个hadoop02连接, 登录MySQL，用户名root, 密码123456
mysql -uroot -p123456
####创建hive元数据, 需要和hive-site.xml中配置的一致，数据库名为: metastore
create database metastore;
show databases;
exit;
####初始化元数据信息
schematool -initSchema -dbType mysql -verbose
(看到有schemaTool completed 表示初始化成功)
启动验证安装
hadoop集群已启动
mysql服务已启动
在hadoop02上任意目录启动hive cli命令行客户端，并执行show databases; 查看输出结果
上一篇文章已经写了配置hive的步骤，也已经通过启动hive cli进去。现在使用代理服务器的方式操作hive
- hadoop01修改hadoop的core-site.xml文件
cd /bigdata/install/hadoop-3.1.4/etc/hadoop
vi core-site.xml
- 在core-site.xml中添加以下配置信息
hadoop.proxyuser.{username}.hosts 和 hadoop.proxyuser.{username}.groups
其中的{username}需要修改为自己的用户名
<property>
<name>hadoop.proxyuser.hadoop.hosts</name>
<value>*</value>
</property>
<property>
<name>hadoop.proxyuser.hadoop.groups</name>
<value>*</value>
</property>
- 将core-site.xml文件同步到hadoop02和hadoop03
xsync core-site.xml
- 在hadoop02上启动hiveserver2
cd /bigdata/install/hive-3.1.2/bin
hiveserver2 #启动hiveserver2
- 新建会话hadoop02连接, 连接hiveserver2
cd /bigdata/install/hive-3.1.2/bin
beeline --color=true
- 通过jdbc方式连接hiveserver2, 输入用户名和密码
beeline> !connect jdbc:hive2://hadoop02:10000
- 输入用户名: hadoop、密码: 123456
Enter username for jdbc:hive2://hadoop02:10000: hadoop
Enter password for jdbc:hive2://hadoop02:10000: 123456
- 测试
0: jdbc:hive2://hadoop02:10000> show databases;
注意采坑：
- 在提交任务的过程中，可能任务会运行失败，会看到由于集群节点虚拟内存不足导致的，解决办法很简单，直接关闭虚拟内存检测就可以了
- 修改
yarn-site.xml文件
- 修改
<property>
<name>yarn.nodemanager.vmem-check-enabled</name>
<value>false</value>
</property>
- 将
yarn-site.xml文件分发到hadoop02, hadoop03 - 重启hadoop集群, 重启hiveserver2
数据仓库概述
今天我们来聊一下大数据里的一个概念，数据仓库，数据仓库的分层结构，ETL
-
什么是数据仓库
- 数据仓库的英文名称为Data Warehouse，可简写为DW或DWH。
- 数据仓库的目的是构建面向分析的集成化数据环境，为企业提供决策支持（Decision Support）。它出于分析性报告和决策支持的目的而创建。
- 数据仓库本身并不“生产”任何数据，同时自身也不需要“消费”任何的数据，数据来源于外部，并且开放给外部应用，这也是为什么叫“仓库”，而不叫“工厂”的原因。
-
数据仓库的主要特征
- 数据仓库是面向主题的（Subject-Oriented）、集成的（Integrated）、非易失的（Non-Volatile）和时变的（Time-Variant ）数据集合，用以支持管理决策。
-
数据仓库与数据库区别
- 什么是OLTP\OLAP?
- OLTP：联机事务处理 （On-Line Transaction Processing），也可以称面向交易的处理系统，它是针对具体业务在数据库联机的日常操作，通常对少数记录进行查询、修改。
用户较为关心操作的响应时间、数据的安全性、完整性和并发支持的用户数等问题。 - OLAP：联机分析处理 OLAP（On-Line Analytical Processing），一般针对某些主题的历史数据进行分析，支持管理决策。
- OLTP：联机事务处理 （On-Line Transaction Processing），也可以称面向交易的处理系统，它是针对具体业务在数据库联机的日常操作，通常对少数记录进行查询、修改。
- 数据仓库和数据库的区别有哪些？
- 首先要明白，数据仓库的出现，并不是要取代数据库。
- 数据仓库，是在数据库已经大量存在的情况下，为了进一步挖掘数据资源、为了决策需要而产生的，它决不是所谓的“大型数据库”。
区别 数据库 数据仓库 设计模型 面向事务的设计 面向主题设计 设计目的 捕获数据而设计 分析数据而设计 设计规范 尽量避免冗余 有意引入冗余，依照分析需求，分析维度、分析指标进行设计 存储数据类型 一般存储业务数据 一般是历史数据 处理类型 OLTP OLAP
- 什么是OLTP\OLAP?
-
数据仓库分层架构
- 按照数据流入流出的过程，数据仓库架构可分为三层——源数据层、数据仓库层、数据应用层
- 源数据层（ODS）：此层数据无任何更改，直接沿用外围系统数据结构和数据，不对外开放；为临时存储层，是接口数据的临时存储区域，为后一步的数据处理做准备。
- 数据仓库层（DW）：也称为细节层，DW层的数据应该是一致的、准确的、干净的数据，即对源系统数据进行了清洗（去除了杂质）后的数据。
- 数据应用层（DA或APP）：前端应用直接读取的数据源；根据报表、专题分析需求而计算生成的数据。
- 按照数据流入流出的过程，数据仓库架构可分为三层——源数据层、数据仓库层、数据应用层
-
为什么要对数据仓库分层？
- 用空间换时间，通过大量的预处理来提升应用系统的用户体验（效率），因此数据仓库会存在大量冗余的数据；
- 不分层的话，如果源业务系统的业务规则发生变化将会影响整个数据清洗过程，工作量巨大。
- 通过数据分层管理可以简化数据清洗的过程，因为把原来一步的工作分到了多个步骤去完成，相当于把一个复杂的工作拆成了多个简单的工作，
把一个大的黑盒变成了一个白盒，每一层的处理逻辑都相对简单和容易理解，这样我们比较容易保证每一个步骤的正确性，
当数据发生错误的时候，往往我们只需要局部调整某个步骤即可。
-
什么是ETL？
- Extra 抽取， Transfer 转化， Load 装载 的过程，取三个大些首字母
- 数据仓库从各数据源获取数据及在数据仓库内的数据转换和流动都可以认为是ETL，ETL是数据仓库的流水线，也可以认为是数据仓库的血液，它维系着数据仓库中数据的新陈代谢，而数据仓库日常的管理和维护工作的大部分精力就是保持ETL的正常和稳定。
Hive DDL操作
数据库DDL操作
1.创建数据库
hive > create database db_hive;
# 或者
hive > create database if not exists db_hive;
数据库在HDFS上的默认存储路径是/usr/hive/warehouse/数据库名.db
- 显示所有数据库
hive > show databases;
- 查询数据库
hive > show databases like 'db_hive*';
- 查询数据库详情
hive > desc database db_hive;
#或者
hive > desc database extended db_hive;
- 切换当前数据库
hive > use db_hive;
- 删除数据库
#删除空数据库
hive > drop database db_hive;
#如果删除数据库不存在，最好采用 if exists 判断数据库是否存在
hive > drop database if exists db_hive;
#如果数据库中有表存在，这里需要使用cascade强制删除数据库
hive > drop database if exists db_hive cascade;
表DDL操作
- 创建表语法
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 分区
[CLUSTERED BY (col_name, col_name, ...) 分桶
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format] row format delimited fields terminated by “分隔符”
[STORED AS file_format]
[LOCATION hdfs_path]
- 字段解释说明
- create table 创建一个指定名字的表
- external 创建一个外部表，在建表的同时指定一个指向实际数据的路径（location),指定表的数据保存在哪里
- comment 为表和列添加注释
- partitioned by 创建分区表
- clustered by 创建分桶表
- sorted by 按照字段排序（一般不常用）
- row format 指定每一行中字段的分隔符
- row format delimited fields terminated by '\t'
- stored as 指定存储文件类型
- 常用的存储文件类型：sequencefile、textfile、orcfile
- 如果文件数据是纯文本，可以使用stored as textfile。如果数据需要压缩，使用stored as sequencefile
- location 指定表在HDFS上的存储位置
- 创建内部表
- 使用标准建表语句创建
use myhive;
create table stu(id int,name string);
insert into stu(id,name) values(1,"zhangsan");
#查询
select * from stu;
- 子查询语句建表
create table if not exists myhive.stu1 as select id,name from stu;
#查询
select * from stu1;
- 根据已经存在的表结构创建表
create table if not exists myhive2.stu2 like stu;
#查询
select * from stu2;
* hql标准语句创建表
```shell
use myhive;
create table if not exists stu3(
id int,
name string
)row format delimited fields terminated by '\t'
stored as textfile
location '/user/stu3'
- 创建外部表
外部表特点：指定其他的hdfs路径的数据加载到表当中，所以hive表会认为自己不完全独占这份数据，所以删除hive表的时候，数据任然存在hdfs当中，不会删除
HQL建表语句中要加上external关键字，location字段可以指定，也可以不指定
create external table myhive.teacher(t_id string,t_name string)
row format delimited fields terminated by '\t';
- 内部表和外部表的互相转换
#内部表转换为外部表
alter table stu set tblproperties('EXTERNAL'='TRUE');
#外部表转换为内部表
alter table teacher set tblproperties('EXTERNAL'='TRUE');
-
内部表和外部表的区别
- 创建语句的区别
外部表在创建的时候需要加上external关键字 - 删除表之后的区别
内部表删除后，表的元数据和真实数据都被删除了
外部表删除后，仅仅只是把该表的元数据删除了，真实数据还在，后期还是可以恢复出来
- 创建语句的区别
-
内部表和外部表的使用时机
- 内部表由于删除表时会同步删除HDFS的数据文件，所以确定如果一个表仅删除是你独占使用，其他人不使用的时候就可以创建内部表，如果一个表的文件数据，其他人也要使用，那么就创建外部表
- 一般外部表都是用在数据仓库的ODS层
- 内部表都是用在数据仓库的DW层
HIVE的复合类型使用说明和实战
- 参数说明
创建表的时候可以指定每行数据的格式,如果使用的是复合数据类型，还需要指定复合数据类型中的元素分割符
ROW FORMAT DELIMITED
[FIELDS TERMINATED BY char [ESCAPED BY char]]
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char]
[LINES TERMINATED BY char]
其中这里
FIELDS TERMINATED BY char 指定每一行记录中字段的分割符
COLLECTION ITEMS TERMINATED BY char 指定复合类型中多元素的分割符
MAP KEYS TERMINATED BY char 指定map集合类型中每一个key/value之间的分隔符
LINES TERMINATED BY char 指定每行记录的换行符，一般有默认 就是\n
- Array类型
- array中的数据为相同类型，例如：array A中元素['a','b','c']，则A[1]的值为'b'
- 建表语句
create table t_array(
id string,
name string,
locations array<string>
) row format delimated fields terminated by ' '
collection items terminated by ',';
- 准备数据文件t_array.txt(使用空格分隔符)
1 zhangsan beijing,shanghai
2 lisi shanghai,tianjin
- 加载数据
load data local inpath '/bigdata/logs/t_array.txt' into table t_array;
- 查询数据
select id,name,locations[0],locations[1] from t_array;
- Map类型
- map类型中存储key/value类型的数据，后期可以通过["指定key名称"]访问
- 建表语句
create table t_map(
id string,
info map<string,string>
) row format delimited fields terminated by ' '
collection items terminated by '#'
map keys terminated by ':';
- 准备数据t_map.txt(字段以空格分割)
1 name:zhangsan#age:30
2 name:lisi#age:40
- 加载数据
load data local inpath '/bigdata/logs/t_map.txt' into table t_map;
- 查询语句
select id info['name'],info['age'] from t_map;
- Struct类型
- 可以存储不同类型的数据，eg:c的类型为struct{a int;b int},我们可以通过c.a来访问域a
- 建表语句
create table t_struct(
id string,
info struct<name:string,age:int,address:String>
) row format delimited fields terminated by ' '
collection items terminated by ':';
- 准备数据t_struct.txt(字段以空格分割)
1 zhangsan:30:beijing
2 lisi:40:shanghai
- 加载数据
load data local inpath '/bigdata/logs/t_struct.txt' into table t_struct;
- 查询数据
select id,info.name,info.age,info.address from t_struct;HIVE的分区表和分桶表
分区表
hive可以转化成MR计算程序，当数据量多时，读取一整个目录下的所有文件来进行计算，因为数据量太大，所以就会变得特别慢。
在实际工作当中，我们一般有计算前一天的数据的需求，我们可以将前一天的数据放在一个文件夹下，专门来计算前一天的数据
hive的分区表大概也是通过分文件夹的形式，将每一天数据都分成一个文件夹，然后去查询数据的时候就可以查询一个文件夹下的数据，
减小数据范围，加快查询效率
- 创建分区表语法
create table score(s_id string,c_id string,s_score int) partitioned by (month string) row format delimited fields terminated by '\t';
- 创建一个表带有多个分区
create table score2(s_id string,c_id string,s_score int)
partitioned by (year string,month string,day string)
row format delimited fields terminated by '\t';
- 加载数据到分区表当中去
load data local inpath '/bigdata/logs/score.csv' into table score partition(month='201806');
- 查看分区
show partitions score;
- 添加一个分区
alter table score add partition(month='201805');
- 同时添加多个分区
alter table score add partition(month='201804') partition(month='201803');
- 删除分区
alter table score drop partition(month='201806');
分桶表
分桶是相对分区进行更细粒度的划分，hive表或分区表可进一步分桶
原理：将整个数据内容按照某列取hash值，对桶的个数取模的方式决定该条记录存放在哪个桶中；具有相同hash值的数据进入到同一个桶，形成同一个文件
eg:比如按照name属性分3个桶，就是对name属性值的hash值对3取模，按照取模结果对数据分桶。
作用：提高某些查询操作效率
- 创建分桶表
set hive.enforce.bucketing=true;
set mapreduce.job.reduces=4; ##分4个桶
##创建分桶表
create table user_buckets_demo(id int,name string)
clustered by(id) into 4 buckets
row format delimited fields terminated by '\t';
##创建普通表
create table user_demo(id int, name string)
row format delimited fields terminated by '\t';
- 准备数据文件user_bucket.txt
cd /bigdata/logs/
vi user_bucket.txt
1 anzhulababy1
2 AngleBaby2
3 xiaoxuanfeng1
4 heixuanfeng1
5 jingmaoshu1
6 dongjingshu1
7 dianwanxiaozi1
8 aiguozhe1
- 加载数据到普通表user_buckets_demo中
load data local inpath '/bigdata/logs/user_bucket.txt'
overwrite into table user_buckets_demo;
hive3.x版本之后，可以直接向分桶表中load数据，不需要通过查询普通表数据插入到分桶表中。所以hive3.x以下版本插入数据需要两步
加载数据到普通表中
load data local inpath '/bigdata/logs/user_bucket.txt' overwrite into table user_demo;
从普通表中查询数据插入到分桶表中
insert into table user_bucket_demo select * from user_demo;
分区和分桶表案例
-
分区表
创建一个分区表，包括字段有(姓名，性别，年龄)，并以年龄作为分区- 建表语句（分区字段不能和表字段同名）
create table if not exists student1( id int, name string, sex string, age int) partitioned by (age1 int) row format delimited fields terminated by ' ';- 准备数据 t_student.txt，t_student1.txt
vi t_student.txt zhangsan 男 20 lisi 男 20 wangwu 男 20 vi t_student1.txt lilei 男 21 Lucy 女 21 hanmeimei 女 21- 加载数据到分区表
load data local inpath '/bigdata/logs/t_student.txt' into table student1 partition (age=20) ; load data local inpath '/bigdata/logs/t_student1.txt' into table student1 partition (age=21) ; -
分桶表
创建一个分桶表，包括字段(姓名，性别，年龄)，并以性别分桶- 建表语句
set hive.enforce.bucketing=true; set mapreduce.job.reduces=2; create table if not exists student2( id int, name string, sex string, age int) clustered by(sex) into 2 buckets row format delimited fields terminated by ' ';- 准备数据 t_student2.txt
vi t_student2.txt zhangsan 男 31 lisi 男 32 wangwu 男 33 lilei 男 35- 加载数据到分桶表中
load data local inpath '/bigdata/logs/t_student2.txt' overwrite into table student2;
HIVE的静态分区和动态分区
静态分区
- 表的分区字段的值需要开发人员手动指定
eg: 创建分区表create table order_partition( order_number string, order_price double, order_time string ) partitioned BY(month string) row format delimited fields terminated by '\t';- 准备数据
cd /bigdata/logs/ vim order.txt 10001 100 2019-03-02 10002 200 2019-03-02 10003 300 2019-03-02 10004 400 2019-03-03 10005 500 2019-03-03 10006 600 2019-03-03 10007 700 2019-03-04 10008 800 2019-03-04 10009 900 2019-03-04- 加载数据到分区表
load data local inpath '/bigdata/logs/order.txt' overwrite into table order_partition partition(month='2019-03');- 查询结果数据
select * from order_partition where month='2019-03'; 结果为： 10001 100.0 2019-03-02 2019-03 10002 200.0 2019-03-02 2019-03 10003 300.0 2019-03-02 2019-03 10004 400.0 2019-03-03 2019-03 10005 500.0 2019-03-03 2019-03 10006 600.0 2019-03-03 2019-03 10007 700.0 2019-03-04 2019-03 10008 800.0 2019-03-04 2019-03 10009 900.0 2019-03-04 2019-03
动态分区
- 根据分区字段不同的值，自动将数据导入到分区表不同的分区中，不需要手动指定分区字段的值
eg: 创建表-- 创建普通表 create table t_order( order_number string, order_price double, order_time string )row format delimited fields terminated by '\t'; -- 创建目标分区表 create table order_dynamic_partition( order_number string, order_price double )partitioned BY(order_time string) row format delimited fields terminated by '\t';- 准备数据
cd /bigdata/logs/ vim order_partition.txt 10001 100 2019-03-02 10002 200 2019-03-02 10003 300 2019-03-02 10004 400 2019-03-03 10005 500 2019-03-03 10006 600 2019-03-03 10007 700 2019-03-04 10008 800 2019-03-04 10009 900 2019-03-04- 向普通表t_order加载数据
load data local inpath '/bigdata/logs/order_partition.txt' overwrite into table t_order;- 动态加载数据到分区表中
-- 要想进行动态分区，需要设置参数 -- 开启动态分区功能 set hive.exec.dynamic.partition=true; -- 设置hive为非严格模式 set hive.exec.dynamic.partition.mode=nonstrict; insert into table order_dynamic_partition partition(order_time) select order_number, order_price, order_time from t_order;- 查看分区
show partitions order_dynamic_partition;
hive3.x之后, 可以直接通过load的方式导入hdfs上的文件完成动态分区, 并且不需要做任何属性设置, 动态分区会根据最后一个字段来进行分区
load data inpath '/user/hive/warehouse/myhive.db/t_order' overwrite into table myhive.order_dynamic_partition;
HIVE查询语法
SQL语言大小写不敏感
SQL可以写在一行或者多行
关键字不能被缩写也不能分行
各子句一般要分行写
使用缩进提高语句的可读性
常用函数
- 求总数(count)
select count(*) cnt from score;
- 求最大值(max)
select max(s_score) from score;
- 求最小值(min)
select min(s_score) from score;
- 求总和(sum)
select sum(s_score) from score;
- 求平均值(avg)
select avg(s_score) from score;
- 分页查询(limit)
select * from score limit 5;
运算符
- 算术运算符
运算符 描述 A+B A和B 相加 A-B A减去B A*B A和B 相乘 A/B A除以B A%B A对B取余 A&B A和B按位取与 A|B A和B按位取或 A^B A和B按位取异或 ~A A按位取反 - 比较运算符
操作符 支持的数据类型 描述 A=B 基本数据类型 如果A等于B则返回true，反之返回false A<=>B 基本数据类型 如果A和B都为NULL，则返回true，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL A<>B, A!=B 基本数据类型 A或者B为NULL则返回NULL；如果A不等于B，则返回true，反之返回false A<B 基本数据类型 A或者B为NULL，则返回NULL；如果A小于B，则返回true，反之返回false A<=B 基本数据类型 A或者B为NULL，则返回NULL；如果A小于等于B，则返回true，反之返回false A>B 基本数据类型 A或者B为NULL，则返回NULL；如果A大于B，则返回true，反之返回false A>=B 基本数据类型 A或者B为NULL，则返回NULL；如果A大于等于B，则返回true，反之返回false A [NOT] BETWEEN B AND C 基本数据类型 如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为true，反之为false。如果使用NOT关键字则可达到相反的效果。 A IS NULL 所有数据类型 如果A等于NULL，则返回true，反之返回false A IS NOT NULL 所有数据类型 如果A不等于NULL，则返回true，反之返回false IN(数值1, 数值2) 所有数据类型 使用 IN运算显示列表中的值 A [NOT] LIKE B STRING 类型 B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回true；反之返回false。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。like不是正则，而是通配符 A RLIKE B, A REGEXP B STRING 类型 B是一个正则表达式，如果A与其匹配，则返回true；反之返回false。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。 - 逻辑运算符
操作符 操作 描述 A AND B 逻辑并 如果A和B都是true则为true，否则false A OR B 逻辑或 如果A或B或两者都是true则为true，否则false NOT A 逻辑否 如果A为false则为true,否则false
分组
- Group by 语句通常会和 聚合函数 一起使用，按照一个或者多个列对结果进行分组，然后对每个组执行聚合操作。
- 查询非分组字段会报错
-- 计算每个学生的平均分数
select s_id, avg(s_score) from score group by s_id;
- Having 语句 针对查询结果中的列 发挥作用，筛选数据，只用于group by 分组统计语句
- having 后边可以使用聚合函数
select s_id, avg(s_score) as avgScore from score group by s_id having avgScore > 60;
-- 等价于
select s_id, avg(s_score) as avgScore from score group by s_id having avg(s_score) > 60;
join语句
Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接
- 内连接 inner join(join 默认使用inner join)
- 只有进行连接的两个表中都存在于连接条件相匹配的数据才会被保留下来。
select * from teacher t inner join course c on t.t_id = c.t_id;
- 左外连接 left outer join
- join操作符左边表中符合where子句的所有记录将会被返回。
- 右边表的指定字段没有符合条件的值的话，那么就使用null值替代。
select * from teacher t left outer join course c on t.t_id =c.t_id;
- 右外连接 right outer join
- join操作符右边表中符合where子句的所有记录将会被返回。
- 左边表的指定字段没有符合条件的值的话，那么就使用null值替代。
select * from teacher t right outer join course c on t.t_id = c.t_id;
- 满外连接 full outer join
- 将会返回所有表中符合where语句条件的所有记录。
- 如果任一表的指定字段没有符合条件的值的话，那么就使用null值替代。
select * from teacher t full outer join course c on t.t_id = c.t_id;
排序
- order by 全局排序
- order by 子句在selected语句的结尾
select * from score s order by s_score desc;
- sort by 局部排序
- 每个reducer内部有序排序，对全局结果集来说并非全局排序
-- 设置reduce个数
set mapreduce.job.reduces=3;
-- 查看reduce的个数
set mapreduce.job.reduces;
-- 查询成绩按照成绩降序排列
select * from score s sort by s.s_score;
-- 将查询结果导入到文件中，按照成绩降序排列
insert overwrite local directory '/bigdata/logs/sort' select * from score s sort by s.s_score;
- distribute by 分区排序
- 类似MR中的partition，采用hash算法，在map端将查询的结果中hash值相同的结果分发到对应的reduce文件中，可以结合sort by 使用。写在sort by 语句之前
-- 先按照学生sid分区，在按照学生成绩进行排序
-- 设置reduce的个数
set mapreduce.job.reduces=3;
-- 通过distribute by 进行数据的分区，将不同sid划分到对应的reduce当中
insert overwrite local directory '/bigdata/logs/distribute' select * from score distribute by s_id sort by s_score;
- cluster by
- 当distribute by 和sort by字段相同时，可以使用cluster by方式代替，即 cluster by xxx = distribute by xxx sort by xxx
-- 以下两种写法等价
insert overwrite local directory '/bigdata/logs/distribute_sort' select * from score distribute by s_score sort by s_score;
insert overwrite local directory '/bigdata/logs/cluster' select * from score cluster by s_score;Hive的常用函数
1 系统内置函数
1．查看系统自带的函数
hive> show functions;
2．显示自带的函数的用法
hive> desc function upper;
3．详细显示自带的函数的用法
hive> desc function extended upper;
2 数值计算
1、取整函数: round
- 语法: round(double a)
- 返回值: BIGINT
- 说明: 返回double类型的整数值部分 （遵循四舍五入）
hive> select round(3.1415926) from tableName;
3
hive> select round(3.5) from tableName;
4
hive> create table tableName as select round(9542.158) from tableName;
2、指定精度取整函数: round
- 语法: round(double a, int d)
- 返回值: DOUBLE
- 说明: 返回指定精度d的double类型
hive> select round(3.1415926, 4) from tableName;
3.1416
3、向下取整函数: floor
- 语法: floor(double a)
- 返回值: BIGINT
- 说明: 返回等于或者小于该double变量的最大的整数
hive> select floor(3.1415926) from tableName;
3
hive> select floor(25) from tableName;
25
4、向上取整函数: ceil
- 语法: ceil(double a)
- 返回值: BIGINT
- 说明: 返回等于或者大于该double变量的最小的整数
hive> select ceil(3.1415926) from tableName;
4
hive> select ceil(46) from tableName;
46
5、向上取整函数: ceiling
- 语法: ceiling(double a)
- 返回值: BIGINT
- 说明: 与ceil功能相同
hive> select ceiling(3.1415926) from tableName;
4
hive> select ceiling(46) from tableName;
46
6、取随机数函数: rand
- 语法: rand(), rand(int seed)
- 返回值: double
- 说明: 返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列
hive> select rand() from tableName;
0.5577432776034763
hive> select rand() from tableName;
0.6638336467363424
hive> select rand(100) from tableName;
0.7220096548596434
hive> select rand(100) from tableName;
0.7220096548596434
3 日期函数
1、UNIX时间戳转日期函数: from_unixtime
- 语法: from_unixtime(bigint unixtime[, string format])
- 返回值: string
- 说明: 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式
hive> select from_unixtime(1323308943, 'yyyyMMdd');
20111208
2、获取当前UNIX时间戳函数: unix_timestamp
- 语法: unix_timestamp()
- 返回值: bigint
- 说明: 获得当前时区的UNIX时间戳
hive> select unix_timestamp() ;
1323309615
3、日期转UNIX时间戳函数: unix_timestamp
- 语法: unix_timestamp(string date)
- 返回值: bigint
- 说明: 转换格式为"yyyy-MM-dd HH:mm:ss"的日期到UNIX时间戳。如果转化失败，则返回0。
hive> select unix_timestamp('2021-4-30 13:01:03') ;
1323234063
4、指定格式日期转UNIX时间戳函数: unix_timestamp
- 语法: unix_timestamp(string date, string pattern)
- 返回值: bigint
- 说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。
hive> select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');
1323234063
5、日期时间转日期函数: to_date
- 语法: to_date(string datetime)
- 返回值: string
- 说明: 返回日期时间字段中的日期部分。
hive> select to_date('2011-12-08 10:03:01');
2011-12-08
6、日期转年函数: year
- 语法: year(string date)
- 返回值: int
- 说明: 返回日期中的年。
hive> select year('2011-12-08 10:03:01') ;
2011
hive> select year('2021-12-08');
2012
7、日期转月函数: month
- 语法: month (string date)
- 返回值: int
- 说明: 返回date或datetime中的月份。
hive> select month('2011-12-08 10:03:01') ;
12
hive> select month('2011-08-08');
8
8、日期转天函数: day
- 语法: day (string date)
- 返回值: int
- 说明: 返回日期中的天。
hive> select day('2011-12-08 10:03:01') ;
8
hive> select day('2011-12-24');
24
9、日期转小时函数: hour
- 语法: hour (string date)
- 返回值: int
- 说明: 返回日期中的小时。
hive> select hour('2011-12-08 10:03:01') ;
10
10、日期转分钟函数: minute
- 语法: minute (string date)
- 返回值: int
- 说明: 返回日期中的分钟。
hive> select minute('2011-12-08 10:03:01') ;
3
-- second 返回秒
hive> select second('2011-12-08 10:03:01') ;
1
12、日期转周函数: weekofyear
- 语法: weekofyear (string date)
- 返回值: int
- 说明: 返回日期在当前的周数。
hive> select weekofyear('2021-04-08 10:03:01') ;
49
13、日期比较函数: datediff
- 语法: datediff(string enddate, string startdate)
- 返回值: int
- 说明: 返回结束日期减去开始日期的天数。
hive> select datediff('2012-12-08','2012-05-09') ;
213
14、日期增加函数: date_add
- 语法: date_add(string startdate, int days)
- 返回值: string
- 说明: 返回开始日期startdate增加days天后的日期。
hive> select date_add('2012-12-08',10) ;
2012-12-18
15、日期减少函数: date_sub
- 语法: date_sub (string startdate, int days)
- 返回值: string
- 说明: 返回开始日期startdate减少days天后的日期。
hive> select date_sub('2012-12-18',10) ;
2012-11-28
4 条件函数
1、If函数: if
- 语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
- 返回值: T
- 说明: 当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull
hive> select if(1=2,100,200);
200
hive> select if(1=1,100,200);
100
2、非空查找函数: COALESCE
- 语法: COALESCE(T v1, T v2, …)
- 返回值: T
- 说明: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
hive> select COALESCE(null,'100','50') ;
100
3、条件判断函数：CASE
- 语法: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
- 返回值: T
- 说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f
hive> select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end ;
mary
hive> Select case 200 when 50 then 'tom' when 100 then 'mary' else 'tim' end ;
tim
4、条件判断函数：CASE
- 语法: CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
- 返回值: T
- 说明：如果a为TRUE,则返回b；如果c为TRUE，则返回d；否则返回e
hive> select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end ;
mary
hive> select case when 1=1 then 'tom' when 2=2 then 'mary' else 'tim' end;
tom
5 字符串函数
1、字符串长度函数：length
- 语法: length(string A)
- 返回值: int
- 说明：返回字符串A的长度
hive> select length('abcedfg') ;
2、字符串反转函数：reverse
- 语法: reverse(string A)
- 返回值: string
- 说明：返回字符串A的反转结果
hive> select reverse('abcdefg') ;
gfdecba
3、字符串连接函数：concat
- 语法: concat(string A, string B…)
- 返回值: string
- 说明：返回输入字符串连接后的结果，支持任意个输入字符串
hive> select concat('abc','def','gh');
abcdefgh
4、字符串连接并指定字符串分隔符：concat_ws
- 语法: concat_ws(string SEP, string A, string B…)
- 返回值: string
- 说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符
hive> select concat_ws(',','abc','def','gh') ;
abc,def,gh
5、字符串截取函数：substr
- 语法: substr(string A, int start), substring(string A, int start)
- 返回值: string
- 说明：返回字符串A从start位置到结尾的字符串
hive> select substr('abcdeere',3) ;
cde
hive> select substring('abcde',3);
cde
hive> select substr('abcde',-2) ; （负数从后往前）
e
6、字符串截取函数：substr, substring
- 语法: substr(string A, int start, int len),substring(string A, int start, int len)
- 返回值: string
- 说明：返回字符串A从start位置开始，长度为len的字符串
hive> select substr('abcde',3,2) ;
cd
hive> select substring('abcde',3,2) ;
cd
hive>select substring('abcde',-3,2) ;
cd
7、字符串转大写函数：upper, ucase
- 语法: upper(string A) ucase(string A)
- 返回值: string
- 说明：返回字符串A的大写格式
hive> select upper('abSEd');
ABSED
hive> select ucase('abSEd');
ABSED
8、字符串转小写函数：lower, lcase
- 语法: lower(string A) lcase(string A)
- 返回值: string
- 说明：返回字符串A的小写格式
hive> select lower('abSEd') ;
absed
hive> select lcase('abSEd');
absed
9、去空格函数：trim
- 语法: trim(string A)
- 返回值: string
- 说明：去除字符串两边的空格
hive> select trim(' ab c ');
ab c
10、url解析函数 parse_url
- 语法:
parse_url(string urlString, string partToExtract [, string keyToExtract]) - 返回值: string
- 说明：返回URL中指定的部分。partToExtract的有效值为：HOST, PATH,
QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
hive> select parse_url
('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST')
;
www.tableName.com
hive> select parse_url
('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1')
;
v1
11、json解析 get_json_object
- 语法: get_json_object(string json_string, string path)
- 返回值: string
- 说明：解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。
hive> select get_json_object('{"store":{"fruit":\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}], "bicycle":{"price":19.95,"color":"red"} },"email":"amy@only_for_json_udf_test.net","owner":"amy"}','$.owner') ;
12、重复字符串函数：repeat
- 语法: repeat(string str, int n)
- 返回值: string
- 说明：返回重复n次后的str字符串
hive> select repeat('abc', 5) ;
abcabcabcabcabc
13、分割字符串函数: split
- 语法: split(string str, string pat)
- 返回值: array
- 说明: 按照pat字符串分割str，会返回分割后的字符串数组
hive> select split('abtcdtef','t');
["ab","cd","ef"]
6 集合统计函数
1、个数统计函数: count
- 语法: count(*), count(expr), count(DISTINCT expr[, expr_.])
- 返回值：Int
- 说明: count(*)统计检索出的行的个数，包括NULL值的行；count(expr)返回指定字段的非空值的个数；count(DISTINCT
expr[, expr_.])返回指定字段的不同的非空值的个数
hive> select count(*) from tableName;
20
hive> select count(distinct t) from tableName;
10
2、总和统计函数: sum
- 语法: sum(col), sum(DISTINCT col)
- 返回值: double
- 说明: sum(col)统计结果集中col的相加的结果；sum(DISTINCT col)统计结果中col不同值相加的结果
hive> select sum(t) from tableName;
100
hive> select sum(distinct t) from tableName;
70
3、平均值统计函数: avg
- 语法: avg(col), avg(DISTINCT col)
- 返回值: double
- 说明: avg(col)统计结果集中col的平均值；avg(DISTINCT col)统计结果中col不同值相加的平均值
hive> select avg(t) from tableName;
50
hive> select avg (distinct t) from tableName;
30
4、最小值统计函数: min
- 语法: min(col)
- 返回值: double
- 说明: 统计结果集中col字段的最小值
hive> select min(t) from tableName;
20
5、最大值统计函数: max
- 语法: max(col)
- 返回值: double
- 说明: 统计结果集中col字段的最大值
hive> select max(t) from tableName;
120
7 复合类型构建函数
1、Map类型构建: map
- 语法: map (key1, value1, key2, value2, …)
- 说明：根据输入的key和value对构建map类型
-- 建表
create table score_map(name string, score map<string, int>)
row format delimited fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':';
-- 创建数据内容如下并加载数据
cd /bigdata/logs/hivedatas/
vi score_map.txt
zhangsan sx:80,yw:89,zz:95
lisi sx:60,yw:80,zz:99
-- 加载数据到hive表当中去
load data local inpath '/bigdata/logs/hivedatas/score_map.txt' overwrite into table score_map;
-- map结构数据访问：
-- 获取所有的value：
select name,map_values(score) from score_map;
-- 获取所有的key：
select name,map_keys(score) from score_map;
-- 按照key来进行获取value值
select name,score["sx"] from score_map;
-- 查看map元素个数
select name,size(score) from score_map;
-- 构建一个map
select map(1, 'zs', 2, 'lisi');
2、Struct类型构建: struct
- 语法: struct(val1, val2, val3, …)
- 说明：根据输入的参数构建结构体struct类型，似于C语言中的结构体，内部数据通过X.X来获取，假设我
- 数据格式是这样的，电影ABC，有1254人评价过，打分为7.4分
-- 创建struct表
hive> create table movie_score(name string, info struct<number:int,score:float>)
row format delimited fields terminated by "\t"
collection items terminated by ":";
-- 加载数据
cd /bigdata/logs/hivedatas/
vi struct.txt
-- 电影ABC，有1254人评价过，打分为7.4分
ABC 1254:7.4
DEF 256:4.9
XYZ 456:5.4
-- 加载数据
load data local inpath '/bigdata/logs/hivedatas/struct.txt' overwrite into table movie_score;
-- hive当中查询数据
hive> select * from movie_score;
hive> select name, info.number, info.score from movie_score;
OK
1254 7.4
256 4.9
456 5.4
-- 构建一个struct
select struct(1, 'anzhulababy', 'moon', 1.68);
3、Array类型构建: array
- 语法: array(val1, val2, …)
- 说明：根据输入的参数构建数组array类型
hive> create table person(name string, work_locations array<string>)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';
-- 加载数据到person表当中去
cd /bigdata/logs/hivedatas/
vim person.txt
-- 数据内容格式如下
biansutao beijing,shanghai,tianjin,hangzhou
linan changchun,chengdu,wuhan
-- 加载数据
hive > load data local inpath '/bigdata/logs/hivedatas/person.txt' overwrite into table person;
-- 查询所有数据数据
hive > select * from person;
-- 按照下标索引进行查询
hive > select work_locations[0] from person;
-- 查询所有集合数据
hive > select work_locations from person;
-- 查询元素个数
hive > select size(work_locations) from person;
-- 构建array
select array(1, 2, 1);
select array(1, 'a', 1.0);
select array(1, 2, 1.0);
8 复杂类型长度统计函数
1、Map类型长度函数: size(Map<k .V>)
- 语法: size(Map<k .V>)
- 返回值: int
- 说明: 返回map类型的长度
hive> select size(map(1, 'zs', 2, 'anzhulababy'));
2
2、array类型长度函数: size(Array)
- 语法: size(Array)
- 返回值: int
- 说明: 返回array类型的长度
hive> select size(t) from arr_table2;
4
3、类型转换函数
- 类型转换函数: cast
- 语法: cast(expr as )
- 返回值: Expected "=" to follow "type"
- 说明: 返回转换后的数据类型
hive> select cast('1' as bigint) ;
1
9 行转列
1、相关函数说明
-
CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
-
CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。
- 第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。
- 这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
-
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。
2、数据准备
- 数据准备
| name | constellation | blood_type |
|---|---|---|
| 孙悟空 | 白羊座 | A |
| 老王 | 射手座 | A |
| 宋宋 | 白羊座 | B |
| 猪八戒 | 白羊座 | A |
| 按住啦baby | 射手座 | A |
3、需求
- 把星座和血型一样的人归类到一起。结果如下：
射手座,A 老王|按住啦baby
白羊座,A 孙悟空|猪八戒
白羊座,B 宋宋
4、创建表数据文件
- node03服务器执行以下命令创建文件，注意数据使用\t进行分割
cd /bigdata/logs/hivedatas
vim constellation.txt
孙悟空 白羊座 A
老王 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
凤姐 射手座 A
5、创建hive表并导入数据
- 创建hive表并加载数据
hive (hive_explode)> create table person_info(name string, constellation string, blood_type string) row format delimited fields terminated by "\t";
- 加载数据
hive (hive_explode)> load data local inpath '/bigdata/logs/hivedatas/constellation.txt' into table person_info;
6、按需求查询数据
hive (hive_explode)> select t1.base, concat_ws('|', collect_set(t1.name)) name
from
(select name, concat(constellation, "," , blood_type) base from person_info) t1
group by t1.base;
10 列转行
1、函数说明
-
EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
-
LATERAL VIEW
- 用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
- 解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
2、数据准备
- 数据内容如下，字段之间都是使用\t进行分割
cd /bigdata/logs/hivedatas
vim movie.txt
《疑犯追踪》 悬疑,动作,科幻,剧情
《Lie to me》 悬疑,警匪,动作,心理,剧情
《战狼2》 战争,动作,灾难
3、需求
- 将电影分类中的数组数据展开。结果如下：
《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 心理
《Lie to me》 剧情
《战狼2》 战争
《战狼2》 动作
《战狼2》 灾难
4、创建hive表并导入数据
- 创建hive表
hive (hive_explode)> create table movie_info(movie string, category array<string>)
row format delimited fields terminated by "\t"
collection items terminated by ",";
- 加载数据
load data local inpath "/bigdata/logs/hivedatas/movie.txt" into table movie_info;
5、按需求查询数据
hive (hive_explode)> select movie, category_name from movie_info
lateral view explode(category) table_tmp as category_name;