hive学习

概述

Hive产生背景

Hive是基于Hadoop的一个数据仓库工具，可以将 结构化的数据文件 映射为一张表(类似于RDBMS中的表)，并提供类SQL查询功能；Hive是由Facebook开源，用于解决海量结构化日志的数据统计。
- Hive本质是：将 SQL 转换为 MapReduce 的任务进行运算
- 底层由HDFS来提供数据存储
- 可以将Hive理解为一个：将 SQL 转换为 MapReduce 任务的工具

Hive和RDBMS对比

由于 Hive 采用了类似SQL 的查询语言 HQL(Hive Query Language)，因此很容易将Hive 理解为数据库。其实从结构上来看，Hive 和传统的关系数据库除了拥有类似的查询语言，再无类似之处。

查询语言相似。HQL <=> SQL 高度相似
由于SQL被广泛的应用在数据仓库中，因此，专门针对Hive的特性设计了类SQL的查询语言HQL。熟悉SQL开发的开发者可以很方便的使用Hive进行开发。

数据规模。Hive存储海量数据；RDBMS只能处理有限的数据集；
由于Hive建立在集群上并可以利用MapReduce进行并行计算，因此可以支持很大规模的数据；而RDBMS可以支持的数据规模较小。

执行引擎。Hive的引擎是MR/Tez/Spark/Flink；RDBMS使用自己的执行引擎
Hive中大多数查询的执行是通过 Hadoop 提供的 MapReduce 来实现的。而RDBMS通常有自己的执行引擎。

数据存储。Hive保存在HDFS上；RDBMS保存在本地文件系统 或 裸设备
Hive 的数据都是存储在 HDFS 中的。而RDBMS是将数据保存在本地文件系统或裸设备中。

执行速度。Hive相对慢（MR/数据量）；RDBMS相对快；
Hive存储的数据量大，在查询数据的时候，通常没有索引，需要扫描整个表；加之Hive使用MapReduce作为执行引擎，这些因素都会导致较高的延迟。而RDBMS对数据的访问通常是基于索引的，执行延迟较低。当然这个低是有条件的，即数据规模较小，当数据规模大到超过数据库的处理能力的时候，Hive的并行计算显然能体现出并行的优势。

可扩展性。Hive支持水平扩展；通常RDBMS支持垂直扩展，对水平扩展不友好
Hive建立在Hadoop之上，其可扩展性与Hadoop的可扩展性是一致的（Hadoop集群规模可以轻松超过1000个节点）。而RDBMS由于 ACID 语义的严格限制，扩展行非常有限。目前最先进的并行数据库 Oracle 在理论上的扩展能力也只有100台左右。

数据更新。Hive对数据更新不友好；RDBMS支持频繁、快速数据更新
Hive是针对数据仓库应用设计的，数据仓库的内容是读多写少的。因此，Hive中不建议对数据的改写，所有的数据都是在加载的时候确定好的。而RDBMS中的数据需要频繁、快速的进行更新。

hive架构

1. 用户接口:CLI(Common Line Interface)：Hive的命令行，用于接收HQL，并返回结果； JDBC/ODBC：是指Hive的java实现，与传统数据库JDBC类似；WebUI：是指可通过浏览器访问Hive；
2. Thrift Server:Hive可选组件，是一个软件框架服务，允许客户端使用包括Java、C++、Ruby和其他很多种语言，通过 编程的方式远程访问Hive；
3. 元数据管理(MetaStore) Hive将元数据存储在关系数据库中(如mysql、derby)。Hive的元数据包括：数据库名、表名及类型、字段名称及数据类型、数据所在位置等；
4. 驱动程序(Driver)
   • 解析器 (SQLParser) ：使用第三方工具（antlr）将HQL字符串转换成抽象语法树（AST）；对AST进行语法分析，比如字段是否存在、SQL语义是否有误、表是否存在；
   • 编译器 (Compiler) ：将抽象语法树编译生成逻辑执行计划；
   • 优化器 (Optimizer) ：对逻辑执行计划进行优化，减少不必要的列、使用分区等；
   • 执行器 (Executr) ：把逻辑执行计划转换成可以运行的物理计划；

hive配置文件

默认配置文件：hive-default.xml
自定义配置文件: $HIVE_HOME/conf/hive-site.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
    <!-- hive元数据的存储位置 -->
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://linux123:3306/hivemetadata?createDatabaseIfNotExist=true&amp;useSSL=false</value>
        <description>JDBC connect string for a JDBC metastore</description>
    </property>
    <!-- 指定驱动程序 -->
    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.jdbc.Driver</value>
        <description>Driver class name for a JDBC metastore</description>
    </property>
    <!-- 连接数据库的用户名 -->
    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>hive</value>
        <description>username to use against metastore database</description>
    </property>
    <!-- 连接数据库的口令 -->
    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>12345678</value>
        <description>password to use against metastore database</description>
    </property>

    <property>
        <!-- 数据默认的存储位置(HDFS) -->
        <name>hive.metastore.warehouse.dir</name>
        <value>/user/hive/warehouse</value>
        <description>location of default database for the warehouse</description>
    </property>

    <property>
        <!-- 在命令行中，显示当前操作的数据库 -->
        <name>hive.cli.print.current.db</name>
        <value>true</value>
        <description>Whether to include the current database in the Hive prompt.</description>
    </property>
    <property>
        <!-- 在命令行中，显示数据的表头 -->
        <name>hive.cli.print.header</name>
        <value>true</value>
    </property>

    <property>
        <!-- 操作小规模数据时，使用本地模式，提高效率 -->
        <name>hive.exec.mode.local.auto</name>
        <value>true</value>
        <description>Let Hive determine whether to run in local mode automatically</description>
    </property>
</configuration>

初始化元数据库:schematool -dbType mysql -initSchema
Hive的日志文件:Hive的log默认存放在 /tmp/root 目录下（root为当前用户名）；这个位置可以修改。

hive命令

1. -e：不进入hive交互窗口，执行sql语句

   hive -e "select * from users"
   

   -f：执行脚本中sql语句

   # 创建文件hqlfile1.sql，内容：select * from users
   
   # 执行文件中的SQL语句
   hive -f hqlfile1.sql
   
   # 执行文件中的SQL语句，将结果写入文件
   hive -f hqlfile1.sql >> result1.log
   

2. 退出Hive命令行
   exit; quit;
3. 在命令行执行 shell 命令 / dfs 命令

   hive> ! ls;
   hive> ! clear;
   hive> dfs -ls / ;
   

数据类型与文件格式

基本数据类型及转换

大类	类型
Integers(整型)	TINYINT – 1字节的有符号整数 SMALLINT – 2字节的有符号整数 INT – 4字节的有符号整数 BIGINT – 8字节的有符号整数
Floating pointnumbers(浮点数)	FLOAT – 单精度浮点数 DOUBLE – 双精度浮点数
Fixed pointnumbers(定点数)	DECIMAL – 17字节，任意精度数字。通常用户自定义decimal(12, 6)
String(字符串)	STRING – 可指定字符集的不定长字符串 VARCHAR – 1-65535长度的不定长字符串 CHAR – 1-255定长字符串
Datetime(时间日期类型)	TIMESTAMP – 时间戳（纳秒精度） DATE – 时间日期类型
Boolean(布尔类型)	BOOLEAN – TRUE / FALSE
Binary types(二进制类型)	BINARY – 字节序列

显示类型转换

select cast('1111' as int);

集合数据类型

Hive支持集合数据类型，包括array、map、struct、union

类型	描述	字面量示例
ARRAY	有序的相同数据类型的集合	array(1,2)
MAP	key-value对。key必须是基本数据类型，value不限	map(‘a’, 1, ‘b’,2)
STRUCT	不同类型字段的集合。类似于C语言的结构体	struct(‘1’,1,1.0),named_struct(‘col1’, ‘1’, ‘col2’, 1,‘clo3’, 1.0)
UNION	不同类型的元素存储在同一字段的不同行中	create_union(1, ‘a’, 63)

hive> select array(1,2,3);
OK
[1,2,3]

-- 使用 [] 访问数组元素
hive> select arr[0] from (select array(1,2,3) arr) tmp;

hive> select map('a', 1, 'b', 2, 'c', 3);
OK
{"a":1,"b":2,"c":3}

-- 使用 [] 访问map元素
hive> select mymap["a"] from (select map('a', 1, 'b', 2, 'c',3) as mymap) tmp;

-- 使用 [] 访问map元素。 key 不存在返回 NULL
hive> select mymap["x"] from (select map('a', 1, 'b', 2, 'c',3) as mymap) tmp;
NULL

hive> select struct('username1', 7, 1288.68);
OK
{"col1":"username1","col2":7,"col3":1288.68}

-- 给 struct 中的字段命名
hive> select named_struct("name", "username1", "id", 7,"salary", 12880.68);
OK
{"name":"username1","id":7,"salary":12880.68}

-- 使用 列名.字段名 访问具体信息
hive> select userinfo.id from (select named_struct("name", "username1", "id",7, "salary", 12880.68) userinfo) tmp;

-- union 数据类型
hive> select create_union(0, "zhansan", 19, 8000.88) uinfo;

文本文件数据编码

Hive表中的数据在存储在文件系统上，Hive定义了默认的存储格式，也支持用户自定义文件存储格式。

Hive默认分隔符

id name age hobby(array) score(map)
字段之间：^A
元素之间: ^B
key-value之间：^C
666^Alisi^A18^Aread^Bgame^Ajava^C97^Bhadoop^C87

读时模式

在传统数据库中，在加载时发现数据不符合表的定义，则拒绝加载数据。数据在写入数据库时对照表模式进行检查，这种模式称为"写时模式"（schema on write）。
写时模式 -> 写数据检查 -> RDBMS；

Hive中数据加载过程采用"读时模式" (schema on read)，加载数据时不进行数据格式的校验，读取数据时如果不合法则显示NULL。这种模式的优点是加载数据迅速。
读时模式 -> 读时检查数据 -> Hive；好处：加载数据快；问题：数据显示NULL

HQL操作之 – DDL命令

参考： https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL
DDL（data definition language）： 主要的命令有CREATE、ALTER、DROP等。
DDL主要是用在定义、修改数据库对象的结构 或 数据类型。

数据库操作

创建数据库语法

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
	[COMMENT database_comment]
	[LOCATION hdfs_path]
	[MANAGEDLOCATION hdfs_path]
	[WITH DBPROPERTIES (property_name=property_value, ...)];

-- 创建数据库，在HDFS上存储路径为 /user/hive/warehouse/*.db
hive (default)> create database mydb;
hive (default)> dfs -ls /user/hive/warehouse;

-- 避免数据库已经存在时报错，使用 if not exists 进行判断【标准写法】
hive (default)> create database if not exists mydb;

-- 创建数据库。添加备注，指定数据库在存放位置
hive (default)> create database if not exists mydb2
comment 'this is mydb2'
location '/user/hive/mydb2.db';

查看数据库

-- 查看所有数据库
show database;

-- 查看数据库信息
desc database mydb2;
desc database extended mydb2;
describe database extended mydb2;

使用数据库

use mydb;

删除数据库

-- 删除一个空数据库
drop database databasename;

-- 如果数据库不为空，使用 cascade 强制删除
drop database databasename cascade;

建表语法

create [external] table [IF NOT EXISTS] table_name
[(colName colType [comment 'comment'], ...)]
[comment table_comment]
[partition by (colName colType [comment col_comment], ...)]
[clustered BY (colName, colName, ...)
[sorted by (col_name [ASC|DESC], ...)] into num_buckets
buckets]
[row format row_format]
[stored as file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement];

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS]
[db_name.]table_name
LIKE existing_table_or_view_name
[LOCATION hdfs_path];

1. CREATE TABLE。按给定名称创建表，如果表已经存在则抛出异常。可使用if notexists 规避。
2. EXTERNAL关键字。创建外部表，否则创建的是内部表(管理表)。
   删除内部表时，数据和表的定义同时被删除；
   删除外部表时，仅仅删除了表的定义，数据保留；
   在生产环境中，多使用外部表；
3. comment。表的注释
4. partition by。对表中数据进行分区，指定表的分区字段
5. clustered by。创建分桶表，指定分桶字段
6. sorted by。对桶中的一个或多个列排序，较少使用
7. 存储子句
8. stored as SEQUENCEFILE|TEXTFILE|RCFILE。如果文件数据是纯文本，可以使用 STORED AS TEXTFILE（缺省）；如果数据需要压缩，使用 STORED AS SEQUENCEFILE（二进制序列文件）
9. LOCATION。表在HDFS上的存放位置
10. TBLPROPERTIES。定义表的属性
11. AS。后面可以接查询语句，表示根据后面的查询结果创建表
12. LIKE。like 表名，允许用户复制现有的表结构，但是不复制数据

内部表

创建表SQL

-- 创建内部表
create table t1(
	id int,
	name string,
	hobby array<string>,
	addr map<string, string>
)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";

-- 显示表的定义，显示的信息较少
desc t1;

-- 显示表的定义，显示的信息多，格式友好
desc formatted t1;

-- 加载数据
load data local inpath '/home/hadoop/data/t1.dat' into table t1;

-- 查询数据
select * from t1;

-- 查询数据文件
dfs -ls /user/hive/warehouse/mydb.db/t1;

-- 删除表。表和数据同时被删除
drop table t1;

外部表

-- 创建外部表
create external table t2(
	id int,
	name string,
	hobby array<string>,
	addr map<string, string>
)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";

-- 显示表的定义
desc formatted t2;

-- 加载数据
load data local inpath '/home/hadoop/data/t1.dat' into table t2;

-- 查询数据
select * from t2;

-- 删除表。表删除了，目录仍然存在
drop table t2;

内部表与外部表的转换

-- 创建内部表，加载数据，并检查数据文件和表的定义
create table t1(
	id int,
	name string,
	hobby array<string>,
	addr map<string, string>
)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";
load data local inpath '/home/hadoop/data/t1.dat' into table t1;
dfs -ls /user/hive/warehouse/mydb.db/t1;
desc formatted t1;

-- 内部表转外部表
alter table t1 set tblproperties('EXTERNAL'='TRUE');
-- 查询表信息，是否转换成功
desc formatted t1;

-- 外部表转内部表。EXTERNAL 大写，false 不区分大小
alter table t1 set tblproperties('EXTERNAL'='FALSE');
-- 查询表信息，是否转换成功
desc formatted t1;

分区表

Hive在执行查询时，一般会扫描整个表的数据。由于表的数据量大，全表扫描消耗时间长、效率低。

而有时候，查询只需要扫描表中的一部分数据即可，Hive引入了分区表的概念，将表的数据存储在不同的子目录中，每一个子目录对应一个分区。只查询部分分区数据时，可避免全表扫描，提高查询效率。

分区表创建与数据加载

-- 创建表
create table if not exists t3(
	id int
	,name string
	,hobby array<string>
	,addr map<String,string>
)
partitioned by (dt string)
row format delimited
fields terminated by ';'
collection items terminated by ','
map keys terminated by ':';

-- 加载数据。
load data local inpath "/home/hadoop/data/t1.dat" into table t3 partition(dt="2020-06-01");

**查看分区**
show partitions t3;

新增分区并设置数据

-- 增加一个分区，不加载数据
alter table t3 add partition(dt='2020-06-03');

-- 增加多个分区，不加载数据
alter table t3 add partition(dt='2020-06-05') partition(dt='2020-06-06');

-- 增加多个分区。准备数据
hdfs dfs -cp /user/hive/warehouse/mydb.db/t3/dt=2020-06-01 /user/hive/warehouse/mydb.db/t3/dt=2020-06-07
hdfs dfs -cp /user/hive/warehouse/mydb.db/t3/dt=2020-06-01 /user/hive/warehouse/mydb.db/t3/dt=2020-06-08

-- 增加多个分区。加载数据
alter table t3 add
partition(dt='2020-06-07') location '/user/hive/warehouse/mydb.db/t3/dt=2020-06-07'
partition(dt='2020-06-08') location '/user/hive/warehouse/mydb.db/t3/dt=2020-06-08';

修改分区的hdfs路径

alter table t3 partition(dt='2020-06-01') set location '/user/hive/warehouse/t3/dt=2020-06-03';

删除分区

-- 可以删除一个或多个分区，用逗号隔开
alter table t3 drop partition(dt='2020-06-03'),partition(dt='2020-06-04');

修改表&删除表

-- 修改表名。rename
alter table course_common rename to course_common1;

-- 修改列名。change column
alter table course_common1 change column id cid int;

-- 修改字段类型。change column
alter table course_common1 change column cid cid string;

-- 增加字段。add columns
alter table course_common1 add columns (common string);

-- 删除字段：replace columns
-- 这里仅仅只是在元数据中删除了字段，并没有改动hdfs上的数据文件
alter table course_common1 replace columns(id string, cname string, score int);

-- 删除表
drop table course_common1;

HQL操作之–数据操作

数据导入

装载数据

LOAD DATA [LOCAL] INPATH 'filepath'
[OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1,partcol2=val2 ...)]

• LOCAL：
  • LOAD DATA LOCAL … 从本地文件系统加载数据到Hive表中。本地文件会拷贝到Hive表指定的位置
  • LOAD DATA … 从HDFS加载数据到Hive表中。HDFS文件移动到Hive表指定的位置
• INPATH：加载数据的路径
• OVERWRITE：覆盖表中已有数据；否则表示追加数据
• PARTITION：将数据加载到指定的分区

装载数据

-- 加载本地文件到hive(tabA)
LOAD DATA LOCAL INPATH '/home/hadoop/data/sourceA.txt'

INTO TABLE tabA;
-- 检查本地文件还在

-- 加载hdfs文件到hive(tabA)
LOAD DATA INPATH 'data/sourceA.txt' INTO TABLE tabA;
-- 检查HDFS文件，已经被转移

-- 加载数据覆盖表中已有数据
LOAD DATA INPATH 'data/sourceA.txt' OVERWRITE INTO TABLE tabA;

-- 创建表时加载数据
hdfs dfs -mkdir /user/hive/tabB
hdfs dfs -put sourceA.txt /user/hive/tabB

CREATE TABLE tabB (
	id INT
	,name string
	,area string
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
Location '/user/hive/tabB';

插入数据(Insert)

-- 插入数据
insert into table tabC partition(month='202001') values (5, 'wangwu', 'BJ'), (4, 'lishi', 'SH'), (3,'zhangsan', 'TJ');

-- 插入查询的结果数据
insert into table tabC partition(month='202002')
select id, name, area from tabC where month='202001';

-- 多表（多分区）插入模式
from tabC
insert overwrite table tabC partition(month='202003')
select id, name, area where month='202002'
insert overwrite table tabC partition(month='202004')
select id, name, area where month='202002';

创建表并插入数据(as select)

-- 根据查询结果创建表
create table if not exists tabD  as select * from tabC;

使用import导入数据

import table student2 partition(month='201709') from '/user/hive/warehouse/export/student';

数据导出

-- 将查询结果导出到本地
insert overwrite local directory '/home/hadoop/data/tabC'
select * from tabC;

-- 将查询结果格式化输出到本地
insert overwrite local directory '/home/hadoop/data/tabC2'
row format delimited fields terminated by ' '
select * from tabC;

-- 将查询结果导出到HDFS
insert overwrite directory '/user/hadoop/data/tabC3'
row format delimited fields terminated by ' '
select * from tabC;

-- dfs 命令导出数据到本地。本质是执行数据文件的拷贝
dfs -get /user/hive/warehouse/mydb.db/tabc/month=202001/home/hadoop/data/tabC4

-- hive 命令导出数据到本地。执行查询将查询结果重定向到文件
hive -e "select * from tabC" > a.log

-- export 导出数据到HDFS。使用export导出数据时，不仅有数还有表的元数据信息
export table tabC to '/user/hadoop/data/tabC4';

-- export 导出的数据，可以使用 import 命令导入到 Hive 表中
-- 使用 like tname创建的表结构与原表一致。create ... as select ...结构可能不一致
create table tabE like tabc;
import table tabE from ''/user/hadoop/data/tabC4';

-- 截断表，清空数据。(注意：仅能操作内部表)
truncate table tabE;

-- 以下语句报错，外部表不能执行 truncate 操作
alter table tabC set tblproperties("EXTERNAL"="TRUE");
truncate table tabC;

HQL操作之–DQL命令

排序

1. 全局排序(order by)
2. 每个MR内部排序(sort by): 对于大规模数据而言order by效率低,在很多业务场景，我们并不需要全局有序的数据，此时可以使用sort by；

-- 设置reduce个数
set mapreduce.job.reduces=2;

-- 按照工资降序查看员工信息
select * from emp sort by sal desc;

-- 将查询结果导入到文件中（按照工资降序）。生成两个输出文件，每个文件内部数据按工资降序排列
insert overwrite local directory '/home/hadoop/output/sortsal'
select * from emp sort by sal desc;

3. 分区排序(distribute by):distribute by 将特定的行发送到特定的reducer中，便于后继的聚合 与 排序操作;distribute by 类似于MR中的分区操作，可以结合sort by操作，使分区数据有序；

-- 启动2个reducer task；先按 deptno 分区，在分区内按 sal+comm 排序
set mapreduce.job.reduces=2;

-- 将结果输出到文件，观察输出结果
insert overwrite local directory '/home/hadoop/output/distBy'
select empno, ename, job, deptno, sal + nvl(comm, 0) salcomm
from emp
distribute by deptno
sort by salcomm desc;

4. Cluster By :当distribute by 与 sort by是同一个字段时，可使用cluster by简化语法；

函数

1. 查看系统内置函数

-- 查看系统自带函数
show functions;

2. 日期函数【重要】

-- 当前前日期
select current_date;

select unix_timestamp();
-- 建议使用current_timestamp，有没有括号都可以
select current_timestamp();

-- 时间戳转日期
select from_unixtime(1505456567);
select from_unixtime(1505456567, 'yyyyMMdd');
select from_unixtime(1505456567, 'yyyy-MM-dd HH:mm:ss');

-- 日期转时间戳
select unix_timestamp('2019-09-15 14:23:00');

-- 计算时间差
select datediff('2020-04-18','2019-11-21');
select datediff('2019-11-21', '2020-04-18');

-- 查询当月第几天
select dayofmonth(current_date);

-- 计算月末:
select last_day(current_date);

-- 当月第1天:
select date_sub(current_date, dayofmonth(current_date)-1)

-- 下个月第1天:
select add_months(date_sub(current_date,dayofmonth(current_date)-1), 1)

-- 字符串转时间（字符串必须为：yyyy-MM-dd格式）
select to_date('2020-01-01');
select to_date('2020-01-01 12:12:12');

-- 日期、时间戳、字符串类型格式化输出标准时间格式
select date_format(current_timestamp(), 'yyyy-MM-dd HH:mm:ss');
select date_format(current_date(), 'yyyyMMdd');
select date_format('2020-06-01', 'yyyy-MM-dd HH:mm:ss');

-- 计算emp表中，每个人的工龄
select *, round(datediff(current_date, hiredate)/365,1) workingyears from emp;

3. 字符串函数

-- 转小写。lower
select lower("HELLO WORLD");

-- 转大写。upper
select lower(ename), ename from emp;

-- 求字符串长度。length
select length(ename), ename from emp;

-- 字符串拼接。 concat / ||
select empno || " " ||ename idname from emp;
select concat(empno, " " ,ename) idname from emp;

-- 指定分隔符。concat_ws(separator, [string | array(string)]+)
SELECT concat_ws('.', 'www', array('lagou', 'com'));
select concat_ws(" ", ename, job) from emp;

-- 求子串。substr
SELECT substr('www.lagou.com', 5);
SELECT substr('www.lagou.com', -5);
SELECT substr('www.lagou.com', 5, 5);

-- 字符串切分。split，注意 '.' 要转义
select split("www.lagou.com", "\\.");

4. 数学函数

-- 四舍五入。round
select round(314.15926);
select round(314.15926, 2);
select round(314.15926, -2);

-- 向上取整。ceil
select ceil(3.1415926);

-- 向下取整。floor
select floor(3.1415926);

5. 条件函数【重要】

-- if (boolean testCondition, T valueTrue, T valueFalseOrNull)
select sal, if (sal<1500, 1, if (sal < 3000, 2, 3)) from emp;

-- CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
-- 将emp表的员工工资等级分类：0-1500、1500-3000、3000以上
select sal, if (sal<=1500, 1, if (sal <= 3000, 2, 3)) from emp;

-- COALESCE(T v1, T v2, ...)。返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
select sal, coalesce(comm, 0) from emp;

-- isnull(a) isnotnull(a)
select * from emp where isnull(comm);
select * from emp where isnotnull(comm);

-- nvl(T value, T default_value)
select empno, ename, job, mgr, hiredate, deptno, sal + nvl(comm,0) sumsal
from emp;

-- nullif(x, y) 相等为空，否则为a
SELECT nullif("b", "b"), nullif("b", "a");

6. UDTF函数【重要】

-- explode，炸裂函数
-- 就是将一行中复杂的 array 或者 map 结构拆分成多行
select explode(array('A','B','C')) as col;
select explode(map('a', 8, 'b', 88, 'c', 888));

-- lateral view 常与 表生成函数explode结合使用
lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*fromClause: FROM baseTable (lateralView)*

-- lateral view 的基本使用
with t1 as (
	select 'OK' cola, split('www.lagou.com', '\\.') colb
)
select cola, colc
from t1
lateral view explode(colb) t2 as colc;

窗口函数

窗口函数又名开窗函数，属于分析函数的一种。用于解决复杂报表统计需求的功能强大的函数，很多场景都需要用到。窗口函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。

over 关键字
使用窗口函数之前一般要要通过over()进行开窗

-- 查询emp表工资总和
select sum(sal) from emp;

-- 不使用窗口函数，有语法错误
select ename, sal, sum(sal) salsum from emp;

-- 使用窗口函数，查询员工姓名、薪水、薪水总和
select ename, sal, sum(sal) over() salsum,concat(round(sal / sum(sal) over()*100, 1) || '%') ratiosal
from emp;

-- 注意：窗口函数是针对每一行数据的；如果over中没有参数，默认的是全部结果
集；

partition by子句
在over窗口中进行分区，对某一列进行分区统计，窗口的大小就是分区的大小

-- 查询员工姓名、薪水、部门薪水总和
select ename, sal, sum(sal) over(partition by deptno) salsum from emp;

order by 子句
order by 子句对输入的数据进行排序

-- 增加了order by子句；sum：从分组的第一行到当前行求和
select ename, sal, deptno, sum(sal) over(partition by deptno order by sal) salsum from emp;

** Window子句**

rows between ... and ...

如果要对窗口的结果做更细粒度的划分，使用window子句，有如下的几个选项：

• unbounded preceding。组内第一行数据
• n preceding。组内当前行的前n行数据
• current row。当前行数据
• n following。组内当前行的后n行数据
• unbounded following。组内最后一行数据

排名函数
都是从1开始，生成数据项在分组中的排名

• row_number()。排名顺序增加不会重复；如1、2、3、4、… …
• RANK()。 排名相等会在名次中留下空位；如1、2、2、4、5、… …
• DENSE_RANK()。 排名相等会在名次中不会留下空位 ；如1、2、2、3、4、

元数据管理与存储

Metastore

metastore三种配置方式

1. 内嵌模式
2. 本地模式:本地模式采用外部数据库来存储元数据，目前支持的数据库有：MySQL、Postgres、Oracle、MS SQL Server。教学中实际采用的是MySQL。本地模式不需要单独起metastore服务，用的是跟Hive在同一个进程里的metastore服务。也就是说当启动一个hive 服务时，其内部会启动一个metastore服务。Hive根据 hive.metastore.uris 参数值来判断，如果为空，则为本地模式
3. 远程模式:远程模式下，需要单独起metastore服务，然后每个客户端都在配置文件里配置连接到该metastore服务。远程模式的metastore服务和hive运行在不同的进程里。在生产环境中，建议用远程模式来配置Hive Metastore。

HiveServer2

HiveServer2是一个服务端接口，使远程客户端可以执行对Hive的查询并返回结果。目前基于Thrift RPC的实现是HiveServer的改进版本，并支持多客户端并发和身份验证，启动hiveServer2服务后，就可以使用jdbc、odbc、thrift 的方式连接。
Thrift是一种接口描述语言和二进制通讯协议，它被用来定义和创建跨语言的服务。它被当作一个远程过程调用（RPC）框架来使用，是由Facebook为“大规模跨语言服务开发”而开发的。

HiveServer2作用：

• 为Hive提供了一种允许客户端远程访问的服务
• 基于thrift协议，支持跨平台，跨编程语言对Hive访问
• 允许远程访问Hive

HCatalog

HCatalog 提供了一个统一的元数据服务，允许不同的工具如 Pig、MapReduce 等通过 HCatalog 直接访问存储在 HDFS 上的底层文件。HCatalog是用来访问Metastore的Hive子项目，它的存在给了整个Hadoop生态环境一个统一的定义。

数据存储格式

Hive支持的存储数的格式主要有：TEXTFILE（默认格式） 、SEQUENCEFILE、RCFILE、ORCFILE、PARQUET。

行存储与列存储

行式存储下一张表的数据都是放在一起的，但列式存储下数据被分开保存了。

行式存储

优点：数据被保存在一起，insert和update更加容易
缺点：选择（selection）时即使只涉及某几列，所有数据也都会被读取

列式存储

优点：查询时只有涉及到的列会被读取，效率高
缺点：选中的列要重新组装，insert/update比较麻烦

SQL优化

sort by 代替 order by

HiveQL中的order by与其他关系数据库SQL中的功能一样，是将结果按某字段全局排序，这会导致所有map端数据都进入一个reducer中，在数据量大时可能会长时间计算不完。
如果使用sort by，那么还是会视情况启动多个reducer进行排序，并且保证每个reducer内局部有序。为了控制map端数据分配到reducer的key，往往还要配合distribute by 一同使用。如果不加 distribute by 的话，map端数据就会随机分配到reducer。

group by 代替 count(distinct)

当要统计某一列的去重数时，如果数据量很大，count(distinct) 会非常慢。原因与order by类似，count(distinct)逻辑只会有很少的reducer来处理。此时可以用

数据采集工具 – Flume

无论数据来自什么企业，或是多大量级，通过部署Flume，可以确保数据都安全、及时地到达大数据平台，用户可以将精力集中在如何洞悉数据上。

Flume的定义

Flume由Cloudera公司开发，是一个分布式、高可靠、高可用的海量日志采集、聚合、传输的系统
Flume支持在日志系统中定制各类数据发送方，用于采集数据；

• 分布式：flume分布式集群部署，扩展性好
• 可靠性好: 当节点出现故障时，日志能够被传送到其他节点上而不会丢失
• 易用性：flume配置使用较繁琐，对使用人员专业技术要求高
• 实时采集：flume采集流模式进行数据实时采集

Flume内部原理

具体过程

1. Source接收事件，交给其Channel处理器处理事件
2. 处理器通过拦截器Interceptor，对事件一些处理,比如压缩解码，正则拦截，时间戳拦截，分类等
3. 经过拦截器处理过的事件再传给Channel选择器，将事件写入相应的Channel。Channel Selector有两种：
   • Replicating Channel Selector（默认）,会将source过来的Event发往所有Channel(比较常用的场景是，用多个Channel实现冗余副本，保证可用性)
   • Multiplexing Channel Selector，根据配置分发event。此selector会根据event中某个header对应的value来将event发往不同的channel
4. 最后由Sink处理器处理各个Channel的事件

基础应用

Source

1. avro source：监听 Avro 端口来接收外部 avro 客户端的事件流。
2. exec source：可以将命令产生的输出作为source。
3. netcat source：一个NetCat Source用来监听一个指定端口，并接收监听到的数据。
4. spooling directory source：将指定的文件加入到“自动搜集”目录中。flume会持续监听这个目录，把文件当做source来处理。注意：一旦文件被放到目录中后，便不能修改，如果修改，flume会报错。此外，也不能有重名的文件。
5. Taildir Source（1.7）：监控指定的多个文件，一旦文件内有新写入的数据，就会将其写入到指定的sink内，本来源可靠性高，不会丢失数据。

采集到的日志需要进行缓存，Flume提供了Channel组件用来缓存数据。常见的Channel 有：

6. memory channel：缓存到内存中（最常用）
7. file channel：缓存到文件中
8. JDBC channel：通过JDBC缓存到关系型数据库中
9. kafka channel：缓存到kafka中

缓存的数据最终需要进行保存，Flume提供了Sink组件用来保存数据。常见的 Sink有

1. logger sink：将信息显示在标准输出上，主要用于测试
2. avro sink：Flume events发送到sink，转换为Avro events，并发送到配置好的hostname/port。从配置好的channel按照配置好的批量大小批量获取events
3. null sink：将接收到events全部丢弃
4. HDFS sink：将 events 写进HDFS。支持创建文本和序列文件，支持两种文件类型压缩。文件可以基于数据的经过时间、大小、事件的数量周期性地滚动
5. Hive sink：该sink streams 将包含分割文本或者JSON数据的events直接传送到Hive表或分区中。使用Hive 事务写events。当一系列events提交到Hive时，它们马上可以被Hive查询到
6. HBase sink：保存到HBase中
7. kafka sink：保存到kafka中

数据迁移工具 – Sqoop

Sqoop是一款开源的工具，主要用于在Hadoop(Hive)与传统的数据库（mysql、postgresql等）间进行数据的传递。可以将关系型数据库（MySQL ,Oracle,Postgres等）中的数据导入到HDFS中，也可以将HDFS的数据导进到关系型数据库中。

• 导入是指：从关系型数据库向大数据集群（HDFS、HIVE、HBASE）传输数据；使用import关键字；
• 导出是指：从 大数据集群 向 关系型数据库 传输数据；使用export关键字；