数据仓库

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

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
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.限制笛卡尔积的查询。