数据仓库(英语:Data Warehouse,简称数仓、DW),是一个用于存储、分析、报告的数据系统。
数据仓库的目的是构建面向分析的集成化数据环境,分析结果为企业提供决策支持(Decision Support)
数仓专注分析
为了分析数据而来,分析结果给企业决策提供支撑
案例:中国人手保险公司
1. 业务数据的存储问题
2. 分析型决策的制定
随着集团业务的持续运营,业务数据将会越来越多。由此也产生出许多运营相关的困惑:
为了能够正确认识这些问题,制定相关的解决措施,瞎拍桌子是肯定不行的。
最稳妥办法就是:基于业务数据开展数据分析,基于分析的结果给决策提供支撑。也就是所谓的数据驱动决策的制定。
OLTP环境开展分析可行吗?
OLTP(On-line Transaction Processing):操作型处理、称为联机事务处理,也可以称为面向交易的处理系统,它是针对具体业务在数据库联机的日常操作,通常对少数记录进行查询、修改。用户较为关心操作的响应时间、数据的安全性、完整性等问题。
可以,但是没必要
OLTP系统的核心是面向业务,支持业务,支持事务。所有的业务操作可以分为读、写两种操作,一般来说读的压力明显大于写的压力。如果在OLTP环境直接开展各种分析,有以下问题需要考虑:
数据仓库面世
OLAP(On-line Analytical Processing):分析型处理,称为联机分析处理,一般针对某些主题历史数据进行分析,支持管理决策。
数据仓库的构建
1. 面向主题性(Subject-Oriented)
主题是一个抽象的概念,是较高层次上数据综合、归类并进行分析利用的抽象
2. 集成性(Integrated)
主题相关的数据通常会分布在多个操作型系统中,彼此分散、独立、异构。需要集成到数仓主题下。
主题相关的数据通常会分布在多个操作型系统中,彼此分散、独立、异构。
因此在数据进入数据仓库之前,必然要经过统一与综合,对数据进行抽取、清理、转换和汇总,这一步是数据仓库 建设中最关键、最复杂的一步,所要完成的工作有:
要统一源数据中所有矛盾之处
进行数据综合和计算
下图说明了保险公司综合数据的简单处理过程,其中数据仓库中与“承保”主题有关的数据来自于多个不同的操作型系统。
这些系统内部数据的命名可能不同,数据格式也可能不同。把不同来源的数据存储到数据仓库之前,需要去除这些 不一致
3. 非易失性、非异变性(Non-Volatile)
也叫非易变性。数据仓库是分析数据的平台,而不是创造数据的平台。
4. 时变性
数据仓库的数据需要随着时间更新,以适应决策的需要。
数仓开发语言概述
SQL语言介绍
数仓与SQL
SQL全称叫做结构化查询语言,结构化是什么意思?
结构化数据
SQL语法分类
SQL主要语法分为两个部分:数据定义语言 (DDL)和数据操纵语言 (DML) 。
DDL语法使我们有能力创建或删除表,以及数据库、索引等各种对象,但是不涉及表中具体数据操作:
DML语法是我们有能力针对表中的数据进行插入、更新、删除、查询操作:
什么是Hive?
为什么使用Hive?
使用Hadoop MapReduce直接处理数据所面临的问题
使用Hive处理数据的好处
Hive和Hadoop关系
从功能来说,数据仓库软件,至少需要具备下述两种能力:
Apache Hive作为一款大数据时代的数据仓库软件,当然也具备上述两种能力。只不过Hive并不是自己实现了上述 两种能力,而是借助Hadoop
这样突然发现Hive没啥用,不过是套壳Hadoop罢了。其实不然,Hive的最大的魅力在于用户专注于编写HQL, Hive帮您转换成为MapReduce程序完成对数据的分析
如果让您设计Hive这款软件,要求能够实现用户只编写sql语句,Hive自动将sql转换MapReduce程序,处理位于HDFS上的结构化数据。如何实现?
案例
在HDFS文件系统上有一个文件,路径为/data/china_user.txt
需求:统计来自于上海年龄大于25岁的用户有多少个?
场景目的
重点理解下面两点:
映射信息记录
SQL语法解析、编译
对Hive的理解
Hive能将数据文件映射成为一张表,这个映射是指什么?
Hive软件本身到底承担了什么功能职责?
最终效果
Hive架构图
用户接口
元数据存储
Driver驱动程序
执行引擎
什么是元数据
Hive Metadata
Hive Metatore
metastore配置方式
metastore远程模式
安装前准备
Hadoop与Hive整合
因为Hive需要把数据存储在HDFS上,并且通过MapReduce作为执行引擎处理数据
因此需要在Hadoop中添加相关配置属性,以满足Hive在Hadoop上运行。
修改Hadoop中core-site.xml,并且Hadoop集群同步配置文件,重启生效。
Linux
命令行安装
sudo apt-get install mysql-server
查看运行状态
systemctl status mysql
# 显示running说明已经在运行
查看默认用户密码
sudo cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = XXXX
password = XXXXX
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = XXXX
password = XXXX
socket = /var/run/mysqld/mysqld.sock
使用默认用户登陆
mysql -uXXX -p
查看用户权限
use mysql;
select host, user, plugin from user;
# 可以看到root用户只能localhost登陆
设置root运行远程登陆
# 更改localhost为 %
update user set host='%' where user='root';
# 刷新权限
flush privileges;
修改Mysql加密规则和root密码
MySQL8.0之前的版本密码加密规则:mysql_native_password
MySQL8.0密码加密规则:caching_sha2_password
# 修改加密方式
ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
# 重新设置root的密码
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
FLUSH PRIVILEGES;
select host, user, plugin from user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | root | mysql_native_password |
| localhost | debian-sys-maint | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
+-----------+------------------+-----------------------+
修改bind-address开放远程登陆
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
# 修改 bind-address = 0.0.0.0
重启MySql服务
systemctl restart mysql
查看防火墙是否放行3306端口
namp -p3306 localhost
如果不是open则需要,关闭防火墙
MySQL安装后的目录结构分析
(此结构只针对于使用apt-get install 在线安装情况)
/var/lib/mysql/
/usr/share/mysql
/usr/bin
(mysqladmin mysqldump等命令)/etc/rc.d/init.d/
MySQL的卸载
# 停止进程
sudo kill $(pgrep mysql)
sudo apt-get remove --purge mysql-\*
(node1安装即可)
tar zxvf apache-hive-3.1.2-bin.tar.gz
mv apache-hive-3.1.2-bin/ hive
# 解决Hive与Hadoop之间guava版本差异
cd /export/server/apache-hive-3.1.2-bin/
rm -rf lib/guava-19.0.jar
cp /export/server/hadoop-3.3.0/share/hadoop/common/lib/guava-27.0-jre.jar \
./lib/
Step3 修改hive-env.sh
cd /export/server/apache-hive-3.1.2-bin/conf
mv hive-env.sh.template hive-env.sh
vim hive-env.sh
export HADOOP_HOME=/opt/hadoop-3.3.0
export HIVE_CONF_DIR=/opt/hive-3.1.3/conf
export HIVE_AUX_JARS_PATH=/opt/hive-3.1.3/lib
Step4 新增hive-site.xml
<configuration>
<!-- 存储元数据mysql相关配置 -->
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://XXX(hive节点名):3306/hive3?createDatabaseIfNotExist=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8</value>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>sql数据库的用户名</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>sql密码需要自己填写</value>
</property>
<!-- H2S运行绑定host -->
<property>
<name>hive.server2.thrift.bind.host</name>
<value>XXX(hive节点名)</value>
</property>
<!-- 远程模式部署metastore metastore地址 -->
<property>
<name>hive.metastore.uris</name>
<value>thrift://XXX(hive节点名):9083</value>
</property>
<!-- 关闭元数据存储授权 -->
<property>
<name>hive.metastore.event.db.notification.api.auth</name>
<value>false</value>
</property>
</configuration>
Step5 添加驱动、初始化
上传MySQL JDBC驱动到Hive安装包lib路径下 下载链接
初始化Hive的元数据
cd /export/server/apache-hive-3.1.2-bin/
bin/schematool -initSchema -dbType mysql -verbos
#初始化成功会在mysql中创建74张表
1. 自带客户端
bin/hive
, bin/beeline
$HIVE_HOME/bin/hive
, 是一个 shellUtil
。主要功能:一是可用于以交互或批处理模式运行Hive查询;二是用于Hive相关服务的启动,比如metastore服务。$HIVE_HOME/bin/beeline
,是一个JDBC客户端,是官方强烈推荐使用的Hive命令行工具,和第一代客户端相比,性能加强安全性提高。HiveServer2服务介绍
关系梳理
bin/beeline客户端使用
在hive安装的服务器上,首先启动metastore服务,然后启动hiveserver2服务
#先启动metastore服务 然后启动hiveserver2服务
nohup /opt/hive-3.1.3/bin/hive --service metastore &
nohup /opt/hive-3.1.3/bin/hive --service hiveserver2 &
在node3上使用beeline客户端进行连接访问。需要注意hiveserver2服务启动之后需要稍等一会才可以对外提供服务。
Beeline是JDBC的客户端,通过JDBC协议和Hiveserver2服务进行通信,协议的地址是: jdbc:hive2://node1:10000
[root@node3 ~]# /opt/hive-3.1.3/bin/beeline
Beeline version 3.1.2 by Apache Hive
beeline> ! connect jdbc:hive2://node1:10000
Connecting to jdbc:hive2://node1:10000
Enter username for jdbc:hive2://node1:10000: root
Enter password for jdbc:hive2://node1:10000:
Connected to: Apache Hive (version 3.1.2)
Driver: Hive JDBC (version 3.1.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://node1:10000>
2. Hive 可视化客户端
Datagrip
Hive命令行下执行set命令【仅当前会话有效】
hive> set hive.cli.print.current.db=true;
# 显示当前数据库名称
Hive脚本~/.hiverc
中配置set命令【当前用户有效】
Hive运行时日志
cd /opt/hive-3.1.3/conf
mv hive-log4j.properties.template hive-log4j.properties
# 更改以下选项,日志级别可以根据需要调整
# list of properties
property.hive.log.level = WARN
property.hive.root.logger = DRFA
# 更改路径
property.hive.log.dir = /opt/hive-3.1.3/logs
property.hive.log.file = hive.log
property.hive.perflogger.log.level = INFO
Hive任务执行日志
cd /opt/hive-3.1.3/conf
mv hive-exec-log4j.properties.template hive-exec-log4j.properties
# 更改以下选项,日志级别可以根据需要调整
# list of properties
property.hive.log.level = WARN
property.hive.root.logger = FA
property.hive.query.id = hadoop
# 更改路径
property.hive.log.dir = /opt/hive-3.1.3/logs
property.hive.log.file = ${sys:hive.query.id}.log
Hive数据模型总览
SQL中DDL语法的作用
数据库database
/user/hive/warehouse
下/user/hive/warehouse/database_name.db
下create database
create database用于创建新的数据库
COMMENT:数据库的注释说明语句
LOCATION:指定数据库在HDFS存储位置,默认/user/hive/warehouse/dbname.db
WITH DBPROPERTIES:用于指定一些数据库的属性配置
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, …)];
例子:创建数据库itcast
注意:如果需要使用location指定路径的时候,最好指向的是一个新创建的空文件夹
create database if not exists itcast
comment "this is my first db"
with dbproperties ('createdBy'='Allen');
use database
drop database
删除数据库
默认行为是RESTRICT,这意味着仅在数据库为空时才删除它
要删除带有表的数据库(不为空的数据库),我们可以使用CASCADE
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
表Table
建表语法树(基础)
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name
(col_name data_type [COMMENT col_comment], ... )
[COMMENT table_comment]
[ROW FORMAT DELIMITED …];
-- 简写
CREATE TABLE table_name (col_name data_type);
数据类型
分隔符指定语法
LazySimpleSerDe是Hive默认的,包含4种子语法,分别用于指定字段之间、集合元素之间、map映射 kv之间、 换行的分隔符号
在建表的时候可以根据数据的特点灵活搭配使用
Hive默认分隔符
实例练习
实例1
建表
create database if not exists learnsql;
use learnsql;
create table t_archer (
id int comment "ID",
name string comment "英雄名称",
hp_max int comment "最大生命",
mp_max int comment "最大法力",
attack_max int comment "最高物理攻击",
defense_max int comment "最高物理防御",
attack_range string comment "攻击范围",
role_main string comment "主要定位",
role_assist string comment "次要定位"
) comment "英雄信息"
ROW FORMAT delimited
fields terminated by '\t';
上传文件
#把文件从课程资料中首先上传到node1 linux系统上
#执行命令把文件上传到HDFS表所对应的目录下
hadoop fs -put archer.txt /user/hive/warehouse/itheima.db/t_archer
查看表
在bin/beeline客户端查看 or 可视化工具
select * from t_archer;
---
1,后羿,5986,1784,396,336,remotely,archer,
2,马可波罗,5584,200,362,344,remotely,archer,
3,鲁班七号,5989,1756,400,323,remotely,archer,
4,李元芳,5725,1770,396,340,remotely,archer,
5,孙尚香,6014,1756,411,346,remotely,archer,
6,黄忠,5898,1784,403,319,remotely,archer,
7,狄仁杰,5710,1770,376,338,remotely,archer,
8,虞姬,5669,1770,407,329,remotely,archer,
9,成吉思汗,5799,1742,394,329,remotely,archer,
10,百里守约,5611,1784,410,329,remotely,archer,assassin
内部表
外部表
检表语句中包含External的表叫做外部表
外部表在加载数据的时候,实际数据并不会移动到warehouse目录中,只是与外部数据建立一个链接(映射关系)
当删除一个外部表是,只删除元数据,不删除表中的数据,仅删除表和数据之间的链接
create external table external_table(
key string
)location '/data/external';
-- 目录是hdfs中的位置,若不存在,会自动创建
-- 添加数据会进入到此目录下,不会进入到warehouse
相互转换
-- 内部表转外部表
alter table tblName set tblproperties('external'='true');
-- 外部表转内部表
alter table tblName set tblproperties('external'='false');
分区表
分区可以理解为分类,通过分区把不同类型数据放到不同目录
分区的标准就是指定分区字段,分区字段可以有一个或多个
分区表的意义在于优化查询,查询时,尽量利用分区字段,如果不使用分区字段,就会全表扫描,最典型的一个场景就是把天作为分区字段,查询的时候指定天
create table partition_1(
id int,
name string
)partitioned by (dt string)
row format delimited
fields terminated by '\t';
-- 需要指定分区
load data local inpath '/root/Documents/partition_1.data' into table partition_1 partition(dt='20200101');
-- 展示分区情况
show partitions partition_1;
外部分区表
工作中最常见的表:外部表+分区表
删除分区时,分区数据不会被删除
桶表
桶表是对数据进行哈希取值,然后放到不同文件中存储
物理上,每个通就是表(或分区)里的一个文件
桶表的作用
数据抽样
提高某些查询效率。例如join
create table bucket_tb(
id int
)clustered by (id) into 4 buckets;
-- 根据id值,分成4个桶
-- 直接使用load命令是没有办法分桶的
-- 设置有几个桶就有几个reduce任务
set hive.enforce.bucketing=true;
-- 只能使用insert … select进行插入数据
insert into table bucket_tb select id from b_source where id is not null;
-- 抽样数据
-- tablesample(bucket x out of y on id);
-- 注意:y>=x
-- y:表示把桶表中的数据随机分为多少桶
-- x: 表示取出第几桶的数据
select * from bucket_tb tablesample(bucket 1 out of 4 on id);
-- 如果a、b表都是id的桶表,就不会产生全表的笛卡尔积,只会在同id内做笛卡尔积
select a.id,a.name,b.addr from a join b on a.id = b.id;
Show相关的语句可以帮助用户查询相关信息。
比如我们最常使用的查询当前数据库下有哪些表 show tables.
--1、显示所有数据库 SCHEMAS和DATABASES的用法 功能一样
show databases;
show schemas;
--2、显示当前数据库所有表
show tables;
SHOW TABLES [IN database_name]; --指定某个数据库
--3、查询显示一张表的元数据信息
desc formatted t_team_ace_player;
Load加载数据
不管路径在哪里,只有把数据文件移动到对应的表文件夹下面,Hive才能映射解析成功
最原始暴力的方式就是使用hadoop fs –put|-mv
等方式直接将数据移动到表文件夹下
但是,Hive官方推荐使用Load命令将数据加载到表中
因为采用hadoop fs上传文件就跨过了hive去操作底层,这不符合操作逻辑也不安全
Load语法
Load英文单词的含义为:加载、装载
所谓加载是指:将数据文件移动到与Hive表对应的位置,移动时是纯复制、移动操作
纯复制、移动指在数据load加载到表中时,Hive不会对表中的数据内容进行任何转换,任何操作
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename;
语法规则之LOCAL
指定LOCAL, 将在本地文件系统中查找文件路径。
file:///user/hive/project/data1
没有指定LOCAL关键字
filepath
指向的是一个完整的URI,会直接使用这个URIfs.default.name
指定的(不出意外,都是HDFS)LOCAL本地是哪里?
如果对HiveServer2服务运行此命令
本地文件系统指的是Hiveserver2服务所在机器的本地Linux文件系统,不是Hive客户端所在的本地文件系统
语法规则之filepath
filepath
表示待移动数据的路径。可以指向文件(在这种情况下,Hive将文件移动到表中),也可以指向目录(在 这种情况下,Hive将把该目录中的所有文件移动到表中)。filepath
文件路径支持下面三种形式,要结合LOCAL关键字一起考虑:实例1
--step1:建表
--建表student_local 用于演示从本地加载数据
create table student_local(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ',';
desc student_local;
--建表student_HDFS 用于演示从HDFS加载数据
create table student_HDFS(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ',';
desc student_HDFS;
--建议使用beeline客户端 可以显示出加载过程日志信息
--step2:加载数据
-- 从本地加载数据 数据位于HS2(node1)本地文件系统 本质是hadoop fs -put上传操作
LOAD DATA LOCAL INPATH '/home/node1/Document/students.txt' INTO TABLE student_local;
--从HDFS加载数据 数据位于HDFS文件系统根目录下 本质是hadoop fs -mv 移动操作
--先把数据上传到HDFS上 hadoop fs -put /home/node1/Document/students.txt
LOAD DATA INPATH '/students.txt' INTO TABLE student_HDFS;
慢
语法功能
Hive官方推荐加载数据的方式
也可以使用insert语法把数据插入到指定的表中,最常用的配合是把查询返回的结果插入到另一张表中
insert+select
insert+select表示:将后面查询返回的结果作为内容插入到指定表中
INSERT INTO TABLE tablename select_statement1 FROM from_statement;
Select语法树
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[LIMIT [offset,] rows];
具体语法与sql类似,可参考此文章。
查看所有内置函数
show functions;
查看指定函数信息
desc function FUNC;
查看指定函数扩展信息
desc function extended FUNC;
字符串函数
字符串长度函数:length
字符串反转函数:reverse
字符串连接函数:concat
带分隔符字符串连接函数:concat_ws
字符串截取函数:substr,substring
------------String Functions 字符串函数------------
select length("itcast");
select reverse("itcast");
select concat("angela","baby");
--带分隔符字符串连接函数:concat_ws(separator, [string | array(string)]+)
select concat_ws('.', 'www', array('itcast', 'cn'));
--字符串截取函数:substr(str, pos[, len]) 或者 substring(str, pos[, len])
select substr("angelababy",-2); --pos是从1开始的索引,如果为负数则倒着数
select substr("angelababy",2,2);
--分割字符串函数: split(str, regex)
select split('apache hive', ' ');
日期函数
----------- Date Functions 日期函数 -----------------
--获取当前日期: current_date
select current_date();
--获取当前UNIX时间戳函数: unix_timestamp
select unix_timestamp();
--日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp("2011-12-07 13:01:03");
--指定格式日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');
--UNIX时间戳转日期函数: from_unixtime
select from_unixtime(1618238391);
select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
--日期比较函数: datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2012-12-08','2012-05-09');
--日期增加函数: date_add
select date_add('2012-02-28',10);
--日期减少函数: date_sub
select date_sub('2012-01-1',10);
数学函数
----Mathematical Functions 数学函数-------------
--取整函数: round 返回double类型的整数值部分 (遵循四舍五入)
select round(3.1415926);
--指定精度取整函数: round(double a, int d) 返回指定精度d的double类型
select round(3.1415926,4);
--取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数
select rand();
--指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列
select rand(3);
条件函数
主要用于条件判断、逻辑判断转换这样的场合
-----Conditional Functions 条件函数------------------
select * from student limit 3;
--if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
select if(1=2,100,200);
select if(sex ='男','M','W') from student limit 3;
--空值转换函数: nvl(T value, T default_value)
select nvl("allen","cool");
select nvl(null,"cool");
--条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
select case sex when '男' then 'male' else 'female' end from student limit 3;
ROW_NUMBER()
:对每一行赋其行值
OVER()
:分组
取出每个学科中的前三名
-- student_score table
-- id,name,sub,score
-- 3,zs1,english,89
-- 4,zs2,chinese,60
-- 5,zs2,math,75
-- 6,zs2,english,80
-- 7,zs3,chinese,79
-- 8,zs3,math,83
-- 9,zs3,english,72
-- 10,zs4,chinese,90
-- 11,zs4,math,76
-- 12,zs4,english,80
-- 13,zs5,chinese,98
-- 14,zs5,math,80
-- 15,zs5,english,70
select *
from (
select *, row_number() over(partition by sub order by score desc) as num
from student_score
) s
where s.num<=3;
RANK():排名,存在相等的排名,比如 1、2、2、4这样的排名
DENSE_RANK():密集排名,与rank不同的是1、2、2、3
行转列
CONCAT_WS()
:连接元素
COLLECT_LIST()
:聚合元素成list
COLLECT_SET()
:聚合元素成set
-- student_favor table
-- name favor
-- zs swing
-- zs footbal
-- zs sing
-- zs codeing
-- zs swing
-- 将上面的多行,转换成单行
select name, collect_list(favor) as favor_list from student_favors group by name;
select name, collect_set(favor) as favor_list from student_favors group by name;
select name,concat_ws(',',collect_list(favor)) as favor_list from student_favors group by name;
列转行
SPLIT():分割字符串,返回数组
EXPLODE():将数组元素,转换成多行,或者将map元素转换为多行和多列
LATERAL VIEW:
1.Lateral View 用于和UDTF函数【explode,split】结合来使用。
2.首先通过UDTF函数将数据拆分成多行,再将多行结果组合成一个支持别名的虚拟表。
3..主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF,不能包含其它字段以及多个UDTF的情况。
4.语法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)
-- name favorlist
-- zs swing,footbal,sing
-- ls codeing,swing
-- 转换成下面的形式
-- zs swing
-- zs footbal
-- zs sing
-- ls codeing
-- ls swing
select name,favor_new from student_favors_2 lateral view explode(split(favorlist,',')) table1 as favor_new;
-- 这样写会直接报错,因为name的个数与explode后的行数不等了
select name,explode(split(favorlist,',')) from student_favors_2 ;
ORDER BY:全局有序
SORT BY:局部有序(单个reduce内有序)
DISTRIBUTE BY:对数据进行分区,一般和sort by结合使用
CLUSTER BY:cluster by id = distribute by id sort by id 等同于,但不支持desc
-- 统计order 表中name 去重之后的数据量
select count(distinct name) from order
-- 性能很低,要在一个reduce任务内完成
-- 分为两步,可以使用多个reduce任务完成,效率高
select count(*) from (select name from order group by name) tmp
数据倾斜小实例
之前在Hadoop三大组件中最后一个单元提到过
select a.Key, SUM(a.Cnt) as Cnt
from (
select Key, COUNT(*) as Cnt
From TableName
Group By Key,
-- 将key001占比大的数据打散,可以根据其实际比例来进行,这样可以分配到多个reduce任务中
CASE
When key = 'Key001' THEN Hash(Random()) % 50
ELSE 0
END
) a
GROUP by a.Key;
手机扫一扫
移动阅读更方便
你可能感兴趣的文章