Hive的DDL和DML相关理论和实战

1 数据库

（1）创建数据库

CREATE DATABASE [IF NOT EXISTS] financials;

（2）查看数据库列表

SHOW DATABASES;
SHOW DATABASES LIKE ‘h.*’;

（3）查看数据库的描述

DESCRIBE DATABASE financials;

（4）修改数据库

ALTER DATABASE financials SET DBPROPERTIES (’ edi ted-by’ = ‘Joe Dba’ ) ;

（5）删除数据库

DROP DATABASE IF EXISTS financials;

2 表

2.1 托管表和外部表

2.1.1 托管表

      把HDFS中的数据移动到Hive的仓库中，数据由Hive管理。
（1）创建表
① 创建一个新表

CREATE TABLE records (year STRING, temperature INT, quality INT) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

② 创建一个与一个存在的表的模式相同的表

CREATE TABLE new_table LIKE existing_table;

（2）加载数据

LOAD DATA LOCAL INPATH 'input/ncdc/micro-tab/sample.txt' OVERWRITE INTO TABLE records;

      注：当数据被加载至表中时，不会对数据进行任何转换。Load 操作只是将数据复制/移动至 Hive 表对应的位置。
（3）查询每个年份最大的温度

SELECT year, MAX(temperature) FROM records WHERE 
temperature != 9999 AND quality IN (0, 1, 4, 5, 9) GROUP BY year;

（4）查看表

SHOW TABLES;

（5）修改表
① 重命名

ALTER TABLE records RENAME TO records2;

② 修改列的信息

ALTER TABLE users CHANGE COLUM name names STRING COMMENT 'change names' [AFTER 子句];

③ 增加列

ALTER TABLE users ADD COLUMNS (col3 STRING);

（6）表的删除
① 删除表中所有的数据，保留表的定义

TRUNCATE TABLE users;

② 连同数据和元数据一起删除

DROP TABLE users;

2.1.2 外部表

      外部数据由HDFS管理，并不会将外部数据移动到Hive的仓库中。
（1）创建表‘’

CREATE EXTERNAL TABLE external_table (dummy STRING) LOCATION '/user/tom/external_table';

      LOCATION 后面是HDFS地址。

2.2 分区和桶

2.2.1 分区

（1）简介
      分区表是指在创建表的时候指定的partition的分区空间，这样在查找分区的数据时，就不用扫描所有数据文件，只需要扫描指定分区的数据文件。
（2）细节
      ① 分区表依据分区列的值对表进行划分，每个分区对应表的子目录，所有数据依照分区列放入不同的子目录中；
      ② 分区列是以字段的形式在表结构中存在，可以通过describe table命令查看到字段存在，但是分区字段不存放实际的数据内容，仅仅是分区的表示。
（3）优点
      ① 分区可以缩小查询范围，加快数据的检索速度；
      ② 便于数据管理，常见日期分区，业务分区等。
（4）创建分区表

CREATE TABLE logs (ts BIGINT, line STRING) PARTITIONED BY (dt STRING, country STRING);

（5）加载数据到分区表

LOAD DATA LOCAL INPATH 'input/hive/partitions/file1' INTO TABLE 
logs PARTITION (dt='2001-01-01', country='GB');

（6）查询关于GB国家的记录

·SELECT ts, dt, line FROM logs WHERE country='GB';

（7）查看分区

SHOW PARTITIONS logs;

（8）修改分区
① 添加分区

ALTER TABLE logs ADD IF NOT EXISTS PARTITION (dt='2001-01-03', country='GB') 
LOCATION ‘/input/hive/pastitions/file7’;

② 修改分区

ALTER TABLE logs PARTITION (dt='2001-01-03', country='GB') SET 
LOCATION ‘/input/hive/pastitions/file8’

③ 删除分区

ALTER TABLE logs DROP IF NOT EXISTS PARTITION (dt='2001-01-03', country='GB');

2.2.2 桶

（1）简介
      对于每一个Hive表（包括分区表），Hive可以进一步对数据进行分桶，桶是更细粒度的数据范围划分。分区是针对文件目录，分桶则是针对数据文件。桶是通过对指定列进行哈希计算来实现的，通过哈希值将一个列名下的数据切分为一组桶，对哈希值除以桶的个数求余数的方式决定该记录存放在哪个桶中，每个桶对应于该列名下的一个存储文件。
（2）优点
      ① 更高的查询效率；
      ② 连接两个在（包含连接列的）相同的列上划分了桶的表，可以使用map端连接（map-side join）高效地实现；即当需要关联的表的列上都做了分桶，会有更高的连接效率；
      ③ 取样或采样更高效。
（3）注意
      ① 当前不支持对已有的表进行分桶，只能新建分桶表，然后倒入数据；
      ② 需加入设置自动分桶：set hive.enforce.bucketing = true。
（4）创建分桶表

CREATE TABLE bucketed_users (id INT, name STRING) CLUSTERED BY (id) 
SORTED BY (id ASC) INTO 4 BUCKETS;

      ASC：升序
（5）插入数据

INSERT OVERWRITE TABLE bucketed_users SELECT * FROM users;

（6）查询数据
① 查询1/4的数据

SELECT * FROM bucketed_users TABLESAMPLE(BUCKET 1 OUT OF 4 ON id);

② 查询1/2的数据

SELECT * FROM bucketed_users TABLESAMPLE(BUCKET 1 OUT OF 2 ON id);

③ 随机读取小部分数据

SELECT * FROM users TABLESAMPLE(BUCKET 1 OUT OF 4 ON rand());

3 存储格式

      Hive从两个维度对表的存储进行管理，分别是行格式和文件格式。
      行格式指行和一行中的字段如何存储。行格式由SerDe定义。SerDe是“序列化和反序列化”的合成词。当作为反序列化工具进行使用时，也就是查询表时，SerDe 将把文件中字节形式的数据行反序列化为Hive 内部操作数据行时所使用的对象形式。使用序列化工具时，也就是执行INSERT或CTAS时，表的SerDe会把Hive 的数据行内部表示形式序列化成字节形式并写到输出文件中。
      文件格式指一行中字段容器的格式。最简单的格式纯文本格式，但是也可以使用面向行的和面向列的二进制格式。

3.1 默认存储格式：分隔的文本

（1）没有使用ROW FORMAT或STORED AS子句，默认格式为分隔的文本，每行存储一个数据行。
（2）默认的行内分隔符不是制表符，而是ASCII控制码集合中的Control-A（它的ASCII码为1）。
（3）Hive无法对分隔符转义，因此挑选一个不会在数据字段中用到的字符作为分隔符尤为重要。
（4）集合类元素的默认分隔符为字符Control-B，它用于分隔ARRAY或STRUCT或MAP的键值对中的元素。默认的键值对分隔符为Control-C。
（5）各行之间用换行符分隔。
（6）Hive内部有一个LazySimpleSerDe的SerDe来处理分割格式和面向行的MapReduce文本输入和输出格式。“Lazy”的原因是这个SerDe对字段的反序列化是延迟处理的，只有在访问字段时才进行序列化。

3.2 二进制存储格式：顺序文件、Avro数据文件、Parquet文件、RCFile与ORCFile

      二进制格式可分为两大类：面向行的格式和面向列的格式。面向列的格式对于那些只访问表中一小部分列的查询边角有效。面向行的格式适合同时处理一行中很多列的情况。
（1）通过CREATE TABLE语句中的STORED AS子句做相应声明。不需要指定ROW FORMAT，因为其格式由底层的二进制文件格式来控制。
（2）创建表时指定存储格式
      以Parquet文件存储格式为例

CREATE TABLE users_parquet STORED AS PARQUET AS SELECT * FROM users;

3.3 使用定制的SerDe: RegexSerDe

CREATE TABLE stations (usaf STRING, wban STRING, name STRING) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe' 
WITH SERDEPROPERTIES (
"input.regex" = "(\\d{6}) (\\d{5}) (.{29}) .*"
);

（1）ROW FORMAT 子句中使用DELIMITED 关键字来说明文本是如何分隔的。
（2）用SERDE关键字和用到的java完整类名来指明使用哪个SerDe。
（3）WITH SERDEPROPERTIES子句来设置额外的属性。
（4）input.regex是在反序列化期间将要使用的正则表达式模式。用来将数据行中的部分文本转化为列的集合。这个示例中，有三个捕获组：usaf（六位数的标识符）、wban（五位数的标识符）以及name（29个字符的定长列）。
（5）有时，可能要在正则表达式中使用括号，而不希望这些括号被当作捕获所用的符号。例如，模式(ab)+可用于匹配一个或多个连续的ab 字符串。这时的解决办怯是在左括号后加问号?表示"非捕获组"(noncapturing group)。用(?ab)+来避免某个文本序列被捕获。
（6）RegexSerDe的效率非常低，因此一般不用于通用存储格式，应当考虑把数据复制为二进制存储格式。

3.4 存储句柄

      用于Hive无法访问的存储系统，比如HBase。使用STORED BY子句指定。它代替了ROW FORMAT和STORED AS子句。

CREATE TABLE hbase_table_1(key int, value1 string, value2 int, value3 int) 
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' 
WITH SERDEPROPERTIES ( "hbase.columns.mapping" = ":key,a:b,a:c,d:e" );

4 导入数据

（1）INSERT语句
① 插入数据

INSERT OVERWRITE TABLE target SELECT col1, col2 FROM source;
INSERT OVERWRITE TABLE target PARTITION (dt='2001-01-01') SELECT col1, col2 FROM source;

      OVERWRITE 关键字意味着目标表(对于前面的第一个例子)或2001-01-01 分区(对于第二个例子)中的内容会被SELECT 语句的结果替换掉。
② 动态插入数据

INSERT OVERWRITE TABLE target PARTITION (dt) SELECT col1, col2, dt FROM source;

（2）多表插入
      多表插入(multitable insert)比使用多个单独的INSERT 语句效率更高，因为只需要扫描一遍源表就可以生成多个不相交的输出。

FROM records2
INSERT OVERWRITE TABLE stations_by_year
SELECT year, COUNT(DISTINCT station)
GROUP BY year
INSERT OVERWRITE TABLE records_by_year
SELECT year, COUNT(1)
GROUP BY year
INSERT OVERWRITE TABLE good_records_by_year
SELECT year, COUNT(1)
WHERE temperature != 9999 AND quality IN (0, 1, 4, 5, 9)
GROUP BY year;

（3）CREATE TABLE…AS SELECT语句

CREATE TABLE target AS SELECT col1, col2 FROM source;

      CTAS操作是原子的。失败，就不会创建新表。

5 查询数据

5.1 排序和聚集

（1）order by
      ① 启动一个reduce task；
      ② 数据全局有序；
      ③ 速度可能会非常慢；
      ④ Strict模式下，必须与limit连用。
（2）sort by
      ① 可以有多个reduce task；
      ② 每个Reduce Task内部数据有序，但全局无序；
      ③ 通常与distribute by。
（3）distribute by
      ① 相当于MapReduce中的paritioner，默认是基于hash实现的；
      ② 与sort by连用，可发挥很好的作用。
      ③ 举例：SELECT s.ymd,s.symbol,s.price_close FROM stocks s DISTRIBUTE BY s.symbol SORT BY s.symbol ASC,s.ymd ASC;
（4）cluster by
      ① 当distribute by与sort by连用，且跟随的字段相同时，可使用cluster by简写；
      ② 举例：SELECT s.ymd,s.symbol,s.price_close FROM stocks s CLUSTER BY s.symbol;

5.2 MapReduce脚本

（1）Python 代码
① Map

import re
import sys
for line in sys.stdin:
(year, temp, q) = line.strip().split()
if (temp != "9999" and re.match("[01459]", q)):
print "%s\t%s" % (year, temp)

② Reduce

import sys
(last_key, max_val) = (None, -sys.maxint)
for line in sys.stdin:
(key, val) = line.strip().split("\t")
if last_key and last_key != key:
print "%s\t%s" % (last_key, max_val)
(last_key, max_val) = (key, int(val))
else:
(last_key, max_val) = (key, max(max_val, int(val)))
if last_key:
print "%s\t%s" % (last_key, max_val)

（2）Hive脚本
① 使用SELECT TRANSFORM查询符合条件的记录

ADD FILE /src/main/python/is_good_quality.py;
FROM records
SELECT TRANSFORM(year, temperature, quality)
USING 'is_good_quality.py'
AS year, temperature;

② 使用MAP和REDUCE查询每年最高气温

FROM (
FROM records2
MAP year, temperature, quality
USING 'is_good_quality.py'
AS year, temperature) map_output
REDUCE year, temperature
USING 'max_temperature_reduce.py'
AS year, temperature;

5.3 连接

      Hive不仅支持等值连接，还支持非等值连接。

hive> SELECT * FROM sales;
Joe 2
Hank 4
Ali 0
Eve 3
Hank 2

hive> SELECT * FROM things;
2 Tie
4 Coat
3 Hat
1 Scarf

（1）内连接

hive> SELECT sales.*, things.*
> FROM sales JOIN things ON (sales.id = things.id);
Joe 2 2 Tie
Hank 4 4 Coat
Eve 3 3 Hat
Hank 2 2 Tie

（2）外连接
① 左外连接

hive> SELECT sales.*, things.*
> FROM sales LEFT OUTER JOIN things ON (sales.id = things.id);
Joe 2 2 Tie
Hank 4 4 Coat
Ali 0 NULL NULL
Eve 3 3 Hat
Hank 2 2 Tie

② 右外连接

hive> SELECT sales.*, things.*
> FROM sales RIGHT OUTER JOIN things ON (sales.id = things.id);
Joe 2 2 Tie
Hank 2 2 Tie
Hank 4 4 Coat
Eve 3 3 Hat
NULL NULL 1 Scarf

③ 全外连接

hive> SELECT sales.*, things.*
> FROM sales FULL OUTER JOIN things ON (sales.id = things.id);
Ali 0 NULL NULL
NULL NULL 1 Scarf
Hank 2 2 Tie
Joe 2 2 Tie
Eve 3 3 Hat
Hank 4 4 Coat

（3）半连接
① 左半连接

hive> SELECT * FROM things LEFT SEMI JOIN sales ON (sales.id = things.id);
2 Tie
4 Coat
3 Hat

（4）map连接
1） Map-side Join（Broadcast join）
      对于普通的join操作，会在map端根据key的hash值，shuffle到某一个reduce上去，在reduce端做join连接操作，内存中缓存join左边的表，遍历右边的表，依次做join操作。所以在做join操作时候，将数据量多的表放在join的右边。当数据量比较大，并且key分布不均匀，大量的key都shuffle到一个reduce上了，就出现了数据倾斜。解决方法：在进行两个表join的过程中，由于hive都是从左向右执行，要注意讲小表在前，大表在后（小表会先进行缓存）即Map join解决数据倾斜。
      ① Join操作在map task中完成，因此无需启动reduce task；
      ② 适合一个大表，一个小表的连接操作；
      ③ 思想：小表复制到各个节点上，并加载到内存中；大表分片，与小表完成连接操作。
      ④ 举例：select /*+ mapjoin(A)*/ f.a,f.b from A t join B f on ( f.a=t.a);
2）Reduce-side Join（shuffle join）
      ① Join操作在reduce task中完成；
      ② 适合两个大表连接操作；
      ③ 同一个key对应的字段可能位于不同map中。Reduce-side join是非常低效的，因为shuffle阶段要进行大量的数据传输；
      ③ 思想：map端按照连接字段进行hash，reduce 端完成连接操作。

5.4子查询（只支持不相关子查询）

SELECT station, year, AVG(max_temperature)
FROM (
SELECT station, year, MAX(temperature) AS max_temperature
FROM records2
WHERE temperature != 9999 AND quality IN (0, 1, 4, 5, 9)
GROUP BY station, year
) mt
GROUP BY station, year;

5.5视图

CREATE VIEW valid_records
AS
SELECT *
FROM records2
WHERE temperature != 9999 AND quality IN (0, 1, 4, 5, 9);

（1）视图是由从数据库的基本表中选取出来的数据组成的逻辑窗口，与基本表不同，它是一个虚表。
（2）视图可以被定义为多个表的连接，也可以被定义为只有部分列可见，也可为部分行可见。
（3）在数据库中，存放的只是视图的定义，而不存放视图包含的数据项，这些项目仍然存放在原来的基本表结构中。所以说视图是基于表来创建得到的视图。
（4）视图是只读的，不能向视图中插入或是加载数据。
（5）视图的作用：① 可以简化数据查询语句；② 可以使用户能从多角度看待同一数据；③ 通过引入视图可以提高数据的安全性； ④ 视图提提供了一定程度的逻辑独立性等。
（6）视图机制的好处：① 通过引入视图机制，用户可以将注意力集中在其关心的数据上（而非全部数据），这样就大大提高了用户效率与用户满意度，而且如果这些数据来源于多个基本表结构，或者数据不仅来自于基本表结构，还有一部分数据来源于其他视图，并且搜索条件又比较复杂时，需要编写的查询语句就会比较烦琐，此时定义视图就可以使数据的查询语句变得简单可行；② 定义视图可以将表与表之间的复杂的操作连接和搜索条件对用户不可见，用户只需要简单地对一个视图进行查询即可，故增加了数据的安全性，但不能提高查询效率。

6 用户定义函数（必须使用Java语言编写）

      扩展HQL能力的一种方式。
（1）普通UDF
      UDF 操作作用于单个数据行，且产生一个数据行作为输出（1对1）。大多数函数(例如数学函数和字符串函数)都属于这一类。
（2）用户定义聚集函数(user-defined aggregate function,UDAF)
      UDAF 接受多个输入数据行，并产生一个输出数据行（多对1）。像COUNT 和MAX这样的函数都是聚集函数。
（3）用户定义表生成函数(user-defined table-generating function , UDTF)
      UDTF 操作作用于单个数据行，且产生多个数据行(即一个表)作为输出（1对多）。

7 Hive Streaming操作值python

（1）安装包

pip3 install sasl
（若失败，离线安装，资源下载地址https://www.lfd.uci.edu/~gohlke/pythonlibs/#sasl）
pip3 install thrift
pip3 install thrift-sasl
pip3 install PyHive

（2）python代码

from pyhive import hive
# host主机ip,port：端口号，username:用户名，database:使用的数据库名称
conn = hive.Connection(host='192.168.1.120',port=10000,
					   username='hadoop',database='financials',auth='NOSASL')
cursor=conn.cursor()
cursor.execute('select * from logs limit 10')
for result in cursor.fetchall():
	print (result)

（3）遇到的问题
      ① Connection Issue: thrift.transport.TTransport.TTransportException: TSocket read 0 bytes
将hive-site.xml添加

<property>
    <name>hive.server2.authentication</name>
    <value>NOSASL</value>
    <description>
      Expects one of [nosasl, none, ldap, kerberos, pam, custom].
      Client authentication types.
        NONE: no authentication check
        LDAP: LDAP/AD based authentication
        KERBEROS: Kerberos/GSSAPI authentication
        CUSTOM: Custom authentication provider
                (Use with property hive.server2.custom.authentication.class)
        PAM: Pluggable authentication module
        NOSASL:  Raw transport
    </description>
</property>

      ② hadoop is not allowed to impersonate hadoop (state=08S01,code=0)
在core-site.xml添加

<property>
    <name>hadoop.proxyuser.hadoop.hosts</name>
    <value>*</value>
</property>
<property>
    <name>hadoop.proxyuser.hadoop.groups</name>
    <value>*</value>
</property>

      ③ 默认情况下，HiveServer2以提交查询的用户执行查询访问（true），如果hive.server2.enable.doAs设置为false，查询将以运行hiveserver2进程的用户访问。在hive-site.xml添加

<property>
  <name>hive.server2.enable.doAs</name>
  <value>true</value>
</property>

参考文章：
[1] https://www.jianshu.com/p/3ce51432981a
[2] https://www.cnblogs.com/wenBlog/archive/2019/02/11/10361404.html
[3] https://www.cnblogs.com/zlslch/p/6105243.html
[4]《Hadoop权威指南》
[5]《Hive编程指南》