Hive简介

数据仓库

数据仓库是一个支持管理决策的数据集合。数据仓库是面向主题的、集成的、随时间变化、信息本身相对稳定。数据仓库是所有操作环境和外部数据源的快照集合。

Hive

由Facebook开源用于解决海量结构化日志的数据统计工具。
Hive可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。

Hive架构原理

组成：
用户接口：Client
CLI（hive shell）、JDBC/ODBC(java访问hive)、WEBUI（浏览器访问hive）
元数据：Metastore
元数据包括：表名、表所属的数据库（默认是default）、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等；
默认存储在自带的derby数据库中。
Hadoop
使用HDFS进行存储，使用MapReduce进行计算。
驱动器：Driver
解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST，这一步一般都用第三方工具库完成，比如antlr；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。
编译器（Physical Plan）：将AST编译生成逻辑执行计划。
优化器（Query Optimizer）：对逻辑执行计划进行优化。
执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MR/Spark。
原理：
Hive通过给用户提供的一系列交互接口，接收到用户的指令(SQL)，使用自己的Driver，结合元数据(MetaStore)，将这些指令翻译成MapReduce，提交到Hadoop中执行，最后，将执行返回的结果输出到用户交互接口。

Hive中基本数据类型

Hive数据类型 | Java数据类型 | 长度 | 例子

• | :-: | :-: | -: | -:
  TINYINT| byte| 1byte有符号整数| 20
  SMALINT| short| 2byte有符号整数| 20
  INT| int |4byte有符号整数 |20
  BIGINT| long |8byte有符号整数| 20
  BOOLEAN |boolean |布尔类型|true或者false |TRUE FALSE
  FLOAT| float |单精度浮点数 |3.14159
  DOUBLE |double| 双精度浮点数| 3.14159
  STRING(长度可变) |string| 字符系列。可以指定字符集。可以使用单引号或者双引号。 |‘now’ “for ”
  TIMESTAMP | |时间类型 |
  BINARY | |字节数组 |

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]
  [STORED AS file_format]
  [LOCATION hdfs_path]

• CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。

• EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive创建内部表时，会将数据移动到数据仓库指定的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

• COMMENT：为表和列添加注释。

• PARTITIONED BY创建分区表

• CLUSTERED BY创建分桶表

• SORTED BY不常用
  ROW FORMAT DELIMITED
  [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
  [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)]

• 用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。
  SerDe是Serialize/Deserilize的简称，目的是用于序列化和反序列化。

• STORED AS指定存储文件类型
  常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）。
  如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

• LOCATION ：指定表在HDFS上的存储位置。

• LIKE允许用户复制现有的表结构，但是不复制数据。

内部表(管理表)与外部表

外部表：Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。
内部表：默认创建的表是内部表。Hive控制着数据的生命周期，当我们删除一个内部表时，Hive也会删除这个表中数据(元数据和表数据都会删除掉)。内部表不适合和其他工具共享数据。

分区表

Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式

上传数据后修复

上传数据：dfs -put 本地路径 HDFS路径;
执行修复命令：msck repair table 表名;
再次查询数据：select * from 表名 where 条件;

上传数据后添加分区

上传数据： dfs -put 本地路径 HDFS路径;
执行添加分区： alter table 表名 add partition(month=‘202008’);
查询数据： select * from 表名 where month=‘202008’;

上传数据后load数据到分区

上传数据：load data local inpath ‘本地路径’ into table 表名 partition(month=‘202008’);
查询数据：select * from 表名 where month=‘202008’;

DML

语法：load data [local] inpath ‘路径’ [overwrite] into table 表名;
load data:表示加载数据
local:表示从本地加载数据到hive表(复制);否则从HDFS加载数据到hive表(移动)
inpath:表示加载数据的路径
overwrite into:表示覆盖表中已有数据，否则表示追加
into table:表示加载到哪张表

DQL

语法：SELECT [ALL | DISTINCT] select_expr, select_expr, …
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[
CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]

Like和Rlike

LIKE字句通过 % 代表零个或多个字符(任意个字符)，_ 代表一个字符来指定匹配条件。
RLIKE子句通过Java的正则表达式来指定匹配条件。

join

Hive支持通常的SQL JOIN语句，但是只支持等值连接(等值连接是内连接的子集)，不支持非等值连接。

分区排序（Distribute By）

Distribute By：类似MR中Partition，进行分区，结合Sort by使用。
对于Distribute by进行测试，一定要分配多Reduce进行处理，否则无法看到Distribute by的效果。

Cluster By

当Distribute by和Sorts by字段相同时，可以使用Cluster by方式。Cluster by除了具有Distribute by的功能外还兼具Sort by的功能。但是排序只能是倒序排序(DESC)，不能指定排序规则为ASC或者DESC。

分桶

分区针对的是数据的存储路径；分桶针对的是数据文件。
分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。
分桶是将数据集分解成更容易管理的若干部分的另一个技术。

分桶抽样查询

select * from 表名 tablesample(bucket 1 out of 4 on id);
TABLESAMPLE(BUCKET x OUT OF y) ：
x表示从哪个bucket开始抽取，如果需要取多个桶，以后的桶号为当前桶号加上y。
例如，table总bucket数为4，tablesample(bucket 1 out of 2)，表示总共抽取（4/2=）2个bucket的数据，抽取第1(x)个和第3(x+y)个bucket的数据。
注意：x的值必须小于等于y的值，否则报错：

其他常用查询函数

空字段赋值

NVL( string1, replace_with)
如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值。

CASE WHEN

行转列

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

列转行

EXPLODE(col)：将Hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

Hive简单调优

Fetch抓取

Hive中对某些情况的查询可以不必使用MapReduce计算。例如：SELECT * FROM 表名;在这种情况下，Hive可以简单地读取表对应的存储目录下的文件，然后输出查询结果到控制台。

本地模式

Hive的输入数据量是非常小的。在这种情况下，为查询触发执行任务消耗的时间可能会比实际job的执行时间要多的多。对于大多数这种情况，Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间可以明显被缩短。

严格模式

可以防止用户执行那些可能意向不到的不好的影响的查询。
例如：
1.对于使用了order by语句的查询，要求必须使用limit语句。因为order by为了执行排序过程会将所有的结果数据分发到同一个Reducer中进行处理，强制要求用户增加这个LIMIT语句可以防止Reducer额外执行很长一段时间。
2.限制笛卡尔积的查询。