HIVE

学习目标：

1、熟练掌握hive的使用

2、熟练掌握hql的编写

3、理解hive的工作原理

4、具备hive应用实战能力

1. Hive基本概念

1.1 Hive简介

1.1.1 什么是Hive

Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能。

1.1.2 为什么使用Hive

直接使用hadoop所面临的问题

人员学习成本太高
项目周期要求太短
MapReduce实现复杂查询逻辑开发难度太大

为什么要使用Hive

操作接口采用类SQL语法，提供快速开发的能力。
避免了去写MapReduce，减少开发人员的学习成本。
功能扩展很方便。

1.1.3 Hive的特点

可扩展：Hive可以自由的扩展集群的规模，一般情况下不需要重启服务。

延展性：Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。

容错：良好的容错性，节点出现问题SQL仍可完成执行。

1.2 Hive架构

1.2.1 架构图

1.2.2 基本组成

用户接口：包括 CLI、JDBC/ODBC、WebGUI。
元数据存储：通常是存储在关系数据库如 mysql , derby中。
解释器、编译器、优化器、执行器。
用户接口主要由三个：CLI、JDBC/ODBC和WebGUI。其中，CLI为shell命令行；JDBC/ODBC是Hive的JAVA实现，与传统数据库JDBC类似；WebGUI是通过浏览器访问Hive。
元数据存储：Hive 将元数据存储在数据库中。Hive 中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。
解释器、编译器、优化器完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在 HDFS 中，并在随后有 MapReduce 调用执行。

1.2.3 各组件的基本功能

1.3 Hive与Hadoop的关系

Hive利用HDFS存储数据，利用MapReduce查询分析数据

1.4 Hive与传统数据库对比
hive用于海量数据的离线数据分析

总结：hive具有关系型数据库的查询分析功能，但应用场景完全不同，hive只适合用来做批量数据统计分析，数据量大、对应的延迟较高！

1.5 Hive的数据存储

1、 Hive中所有的数据都存储在 HDFS 中，没有专门的数据存储格式（可支持Text，SequenceFile，ParquetFile，RCFILE等）

SequenceFile是hadoop中的一种文件格式：文件内容是以序列化的kv对象来组织的

2、只需要在创建表的时候告诉 Hive 数据中的列分隔符和行分隔符，Hive 就可以解析数据。

3、Hive 中包含以下数据模型：DB、Table，External Table，Partition，Bucket。

db：在hdfs中表现为${hive.metastore.warehouse.dir}目录下一个文件夹
table：在hdfs中表现所属db目录下一个文件夹
external table：与table类似，不过其数据存放位置可以在任意指定路径
partition：在hdfs中表现为table目录下的子目录
bucket：在hdfs中表现为同一个表目录下根据hash散列之后的多个文件

1.6 HIVE的安装部署

1.6.1 安装

derby版：

1、解压hive

启动

2、解决一下版本不兼容问题：替换 /var/local/hadoop/share/hadoop/yarn/lib中的老版本jline 为hive的lib中的jline-2.12.jar

3.数据存放位置:

缺点：多个地方安装hive后，每一个hive是拥有一套自己的元数据，大家的库、表就不统一；

元数据库mysql版：

Hive只在一个节点上安装即可

1.上传tar包

2.解压：tar -zxvf hive-0.9.0.tar.gz -C /cloud/

3.安装mysql数据库（切换到root用户）（装在哪里没有限制，只有能连通hadoop集群的节点）
mysql安装仅供参考，不同版本mysql有各自的安装流程
rpm -qa | grep mysql
rpm -e mysql-libs-5.1.66-2.el6_3.i686 --nodeps
rpm -ivh MySQL-server-5.1.73-1.glibc23.i386.rpm
rpm -ivh MySQL-client-5.1.73-1.glibc23.i386.rpm
修改mysql的密码
/usr/bin/mysql_secure_installation
（注意：删除匿名用户，允许用户远程连接）
登陆mysql
mysql -u root -p

4.配置hive

（a）配置HIVE_HOME环境变量

vi conf/hive-env.sh 配置其中的$hadoop_home
    
（b）配置元数据库信息   vi  hive-site.xml 
    添加如下内容：
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://itcast01:3306/hive?createDatabaseIfNotExist=true</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>root</value>
<description>username to use against metastore database</description>
</property>

<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>root123</value>
<description>password to use against metastore database</description>
</property>
</configuration>

5.安装hive和mysq完成后，将mysql的连接jar包拷贝到$HIVE_HOME/lib目录下

如果出现没有权限的问题，在mysql授权(在安装mysql的机器上执行)
mysql -uroot -p
#(执行下面的语句 *.*:所有库下的所有表 %：任何IP地址或主机都可以连接)
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root123' WITH GRANT OPTION;
FLUSH PRIVILEGES;

6.Jline包版本不一致的问题，需要拷贝hive的lib目录中jline.2.12.jar的jar包替换掉hadoop中的
/home/hadoop/app/hadoop-2.6.4/share/hadoop/yarn/lib/jline-0.9.94.jar

启动hive：bin/hive

1.6.2 使用方式

Hive交互shell：bin/hive

Hive JDBC服务

hive也可以启动为一个服务器，来对外提供

启动方式，（假如是在itcast01上）：

启动为前台：bin/hiveserver2

启动为后台：nohup bin/hiveserver2 1>/var/log/hiveserver.log 2>/var/log/hiveserver.err &

启动成功后，可以在别的节点上用beeline去连接

方式（1）

hive/bin/beeline 回车，进入beeline的命令界面，输入命令连接hiveserver2：beeline> !connect jdbc:hive2://itcast01:10000

（itcast01是hiveserver2所启动的那台主机名，端口默认是10000）

方式（2）

启动就连接：bin/beeline -u jdbc:hive2://itcast01:10000 -n root

接下来就可以做正常sql查询了

2. Hive基本操作

2.1 DDL操作

2.1.1 创建表

建表语法

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]

说明：

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

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

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

4、 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 确定表的具体的列的数据。

5、 STORED AS

SEQUENCEFILE | TEXTFILE | RCFILE

如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

6、CLUSTERED BY

对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

把表（或者分区）组织成桶（Bucket）有两个理由：

（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接（Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。

（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

具体实例

1、创建内部表mytable。

上传数据到hdfs：

从本地加载数据：

从hdfs加载数据：

2、创建外部表pageview。

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

3、创建分区表invites。

4、创建带桶的表student。

5、桶表抽样查询

Select * from student tablesample(bucket 1 out of 2 on id)

tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y)
y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例.

如，table总共分了64份，当y=32时，抽取(64/32=)2个bucket的数据，当y=128时，抽取(64/128=)1/2个bucket的数据。x表示从哪个bucket开始抽取。例如，table总bucket数为32，tablesample(bucket 3 out of 16)，表示总共抽取（32/16=）2个bucket的数据，分别为第3个bucket和第（3+16=）19个bucket的数据。

2.1.2 修改表

增加/删除分区

语法结构

ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...

partition_spec:

: PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)

ALTER TABLE table_name DROP partition_spec, partition_spec,...

具体实例

alter table student_p add partition(part='a') partition(part='b');

重命名表

语法结构

ALTER TABLE table_name RENAME TO new_table_name

具体实例

增加/更新列

语法结构

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

注：ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段。

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

具体实例

2.1.3 显示命令

show tables

show databases

show partitions

show functions

desc extended t_name;

desc formatted table_name;

2.2 DML操作

2.2.1 Load

语法结构

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO

TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

说明：

1、 Load 操作只是单纯的复制/移动操作，将数据文件移动到 Hive 表对应的位置。

2、 filepath：

相对路径，例如：project/data1

绝对路径，例如：/user/hive/project/data1

包含模式的完整 URI，列如：

hdfs://namenode:9000/user/hive/project/data1

3、 LOCAL关键字

如果指定了 LOCAL， load 命令会去查找本地文件系统中的 filepath。

如果没有指定 LOCAL 关键字，则根据inpath中的uri查找文件

4、 OVERWRITE 关键字

如果使用了 OVERWRITE 关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。

如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。

具体实例

1、加载相对路径数据。

2、加载绝对路径数据。

3、加载包含模式数据。

4、 OVERWRITE关键字使用。

2.2.2 Insert

将查询结果插入Hive表

语法结构

利用查询语句，将查询结果插入新的表

INSERT OVERWRITE [INTO] TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement

插入一条数据

INSERT INTO TABLE VALUES(XX,YY,ZZ);

Multi Inserts多重插入:

FROM from_statement

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select statement1

[INSERT OVERWRITE TABLE tablename2 [PARTITION ...]

Select statement2] ...

Dynamic partition inserts动态分区插入:

INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement

具体实例

1、基本模式插入。

2、多插入模式。

3、自动分区模式。

使用了非严格模式

set hive.exec.dynamic.partition.mode=nonstrict

导出表数据

语法结构

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... FROM ...

multiple inserts:

FROM from_statement

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1

[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...

具体实例

1、导出文件到本地。

说明：

数据写入到文件系统时进行文本序列化，且每列用^A来区分，\n为换行符。用more命令查看时不容易看出分割符，可以使用: sed -e 's/\x01/|/g' filename来查看。

2、导出数据到HDFS。

2.2.3 SELECT

基本的Select操作

ü 语法结构

SELECT [ALL | DISTINCT] select_expr, select_expr, ...

FROM table_reference

[WHERE where_condition]

[GROUP BY col_list [HAVING condition]]

[CLUSTER BY col_list

| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]

]

[LIMIT number]

注：1、order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。

2、sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。

3、distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。

4、Cluster by(字段) 除了具有Distribute by的功能外，还会对该字段进行排序。

因此，如果分桶和sort字段是同一个时，此时，cluster by = distribute by + sort by

分桶表的作用：最大的作用是用来提高join操作的效率；

（思考这个问题：

select a.id,a.name,b.addr from a join b on a.id = b.id;

如果a表和b表已经是分桶表，而且分桶的字段是id字段

做这个join操作时，还需要全表做笛卡尔积吗？）

注意：在hive中提供了一种“严格模式”的设置来阻止用户执行可能会带来未知不好影响的查询。

设置属性hive.mapred.mode 为strict能够阻止以下三种类型的查询：

1、除非在where语段中包含了分区过滤，否则不能查询分区了的表。这是因为分区表通常保存的数据量都比较大，没有限定分区查询会扫描所有分区，耗费很多资源。

不允许：select *from logs;

允许：select * from logs where day=20151212;

2、包含order by，但没有limit子句的查询。因为order by 会将所有的结果发送给单个reducer来执行排序，这样的排序很耗时。

3、 笛卡尔乘积；简单理解就是JOIN没带ON，而是带where的

具体实例

1、获取年龄大的3个学生。

2、查询学生信息按年龄，降序排序。

3、按学生名称汇总学生年龄。

2.3 Hive Join

语法结构

join_table:

table_reference JOIN table_factor [join_condition]

| table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition

| table_reference LEFT SEMI JOIN table_reference join_condition

Hive 支持等值连接（equality joins）、外连接（outer joins）和（left/right joins）。Hive 不支持非等值的连接，因为非等值连接非常难转化到 map/reduce 任务。

另外，Hive 支持多于 2 个表的连接。

写 join 查询时，需要注意几个关键点：

1. 只支持等值join

例如：

SELECT a.* FROM a JOIN b ON (a.id = b.id)

SELECT a.* FROM a JOIN b

ON (a.id = b.id AND a.department = b.department)

是正确的，然而:

SELECT a.* FROM a JOIN b ON (a.id>b.id)

是错误的。

2. 可以 join 多于 2 个表。

例如

SELECT a.val, b.val, c.val FROM a JOIN b

ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

如果join中多个表的 join key 是同一个，则 join 会被转化为单个 map/reduce 任务，例如：

SELECT a.val, b.val, c.val FROM a JOIN b

ON (a.key = b.key1) JOIN c

ON (c.key = b.key1)

被转化为单个 map/reduce 任务，因为 join 中只使用了 b.key1 作为 join key。

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1)

JOIN c ON (c.key = b.key2)

而这一 join 被转化为 2 个 map/reduce 任务。因为 b.key1 用于第一次 join 条件，而 b.key2 用于第二次 join。

3．join 时，每次 map/reduce 任务的逻辑：

reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统。这一实现有助于在 reduce 端减少内存的使用量。实践中，应该把最大的那个表写在最后（否则会因为缓存浪费大量内存）。例如：

SELECT a.val, b.val, c.val FROM a

JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

所有表都使用同一个 join key（使用 1 次 map/reduce 任务计算）。Reduce 端会缓存 a 表和 b 表的记录，然后每次取得一个 c 表的记录就计算一次 join 结果，类似的还有：

SELECT a.val, b.val, c.val FROM a

JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

这里用了 2 次 map/reduce 任务。第一次缓存 a 表，用 b 表序列化；第二次缓存第一次 map/reduce 任务的结果，然后用 c 表序列化。

4．LEFT，RIGHT 和 FULL OUTER 关键字用于处理 join 中空记录的情况

例如：

SELECT a.val, b.val FROM

a LEFT OUTER JOIN b ON (a.key=b.key)

对应所有 a 表中的记录都有一条记录输出。输出的结果应该是 a.val, b.val，当 a.key=b.key 时，而当 b.key 中找不到等值的 a.key 记录时也会输出:

a.val, NULL

所以 a 表中的所有记录都被保留了；

“a RIGHT OUTER JOIN b”会保留所有 b 表的记录。

Join 发生在 WHERE 子句之前。如果你想限制 join 的输出，应该在 WHERE 子句中写过滤条件——或是在 join 子句中写。这里面一个容易混淆的问题是表分区的情况：

SELECT a.val, b.val FROM a

LEFT OUTER JOIN b ON (a.key=b.key)

WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'

会 join a 表到 b 表（OUTER JOIN），列出 a.val 和 b.val 的记录。WHERE 从句中可以使用其他列作为过滤条件。但是，如前所述，如果 b 表中找不到对应 a 表的记录，b 表的所有列都会列出 NULL，包括 ds 列。也就是说，join 会过滤 b 表中不能找到匹配 a 表 join key 的所有记录。这样的话，LEFT OUTER 就使得查询结果与 WHERE 子句无关了。解决的办法是在 OUTER JOIN 时使用以下语法：

SELECT a.val, b.val FROM a LEFT OUTER JOIN b

ON (a.key=b.key AND

b.ds='2009-07-07' AND

a.ds='2009-07-07')

这一查询的结果是预先在 join 阶段过滤过的，所以不会存在上述问题。这一逻辑也可以应用于 RIGHT 和 FULL 类型的 join 中。

Join 是不能交换位置的。无论是 LEFT 还是 RIGHT join，都是左连接的。

SELECT a.val1, a.val2, b.val, c.val

FROM a

JOIN b ON (a.key = b.key)

LEFT OUTER JOIN c ON (a.key = c.key)

先 join a 表到 b 表，丢弃掉所有 join key 中不匹配的记录，然后用这一中间结果和 c 表做 join。这一表述有一个不太明显的问题，就是当一个 key 在 a 表和 c 表都存在，但是 b 表中不存在的时候：整个记录在第一次 join，即 a JOIN b 的时候都被丢掉了（包括a.val1，a.val2和a.key），然后我们再和 c 表 join 的时候，如果 c.key 与 a.key 或 b.key 相等，就会得到这样的结果：NULL, NULL, NULL, c.val

具体实例

1、获取已经分配班级的学生姓名。

2、获取尚未分配班级的学生姓名。

3、 LEFT SEMI JOIN是IN/EXISTS的高效实现。

关于hive中的各种join

准备数据
2,b
3,c
4,d
7,y
8,u

2,bb
3,cc
7,yy
9,pp



建表：
create table a(id int,name string)
row format delimited fields terminated by ',';

create table b(id int,name string)
row format delimited fields terminated by ',';

导入数据：
load data local inpath '/root/hivedata/a.txt' into table a;
load data local inpath '/root/hivedata/b.txt' into table b;


实验：
** inner join
select a.*,b.* from a inner join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id  | a.name  | b.id  | b.name  |
+-------+---------+-------+---------+--+
| 2     | b       | 2     | bb      |
| 3     | c       | 3     | cc      |
| 7     | y       | 7     | yy      |
+-------+---------+-------+---------+--+


**left join  
select * from a left join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id  | a.name  | b.id  | b.name  |
+-------+---------+-------+---------+--+
| 1     | a       | NULL  | NULL    |
| 2     | b       | 2     | bb      |
| 3     | c       | 3     | cc      |
| 4     | d       | NULL  | NULL    |
| 7     | y       | 7     | yy      |
| 8     | u       | NULL  | NULL    |
+-------+---------+-------+---------+--+


**right join
select * from a right join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id  | a.name  | b.id  | b.name  |
+-------+---------+-------+---------+--+
| 2     | b       | 2     | bb      |
| 3     | c       | 3     | cc      |
| 7     | y       | 7     | yy      |
| NULL  | NULL    | 9     | pp      |
+-------+---------+-------+---------+--+


**
select * from a full outer join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id  | a.name  | b.id  | b.name  |
+-------+---------+-------+---------+--+
| 1     | a       | NULL  | NULL    |
| 2     | b       | 2     | bb      |
| 3     | c       | 3     | cc      |
| 4     | d       | NULL  | NULL    |
| 7     | y       | 7     | yy      |
| 8     | u       | NULL  | NULL    |
| NULL  | NULL    | 9     | pp      |
+-------+---------+-------+---------+--+


**hive中的特别join
select * from a left semi join b on a.id = b.id;
+-------+---------+--+
| a.id  | a.name  |
+-------+---------+--+
| 2     | b       |
| 3     | c       |
| 7     | y       |
+-------+---------+--+
相当于
select * from a where a.id exists(select b.id from b); 在hive中效率极低

2.4 hive的数据类型

Hive的内置数据类型可以分为两大类：(1)、基础数据类型；(2)、复杂数据类型
2. hive基本数据类型
基础数据类型包括：TINYINT,SMALLINT,INT,BIGINT,BOOLEAN,FLOAT,DOUBLE,STRING,BINARY,TIMESTAMP,DECIMAL,CHAR,VARCHAR,DATE。

3. hive集合类型
集合类型主要包括：array，map，struct等，hive的特性支持集合类型，这特性是关系型数据库所不支持的，利用好集合类型可以有效提升SQL的查询速率。
3.1 集合类型之array
(1) 先创建一张表
create table t_array(id int,name string,hobby array<string>)
row format delimited
fields terminated by ','
collection items terminated by '-';

(2) 准备数据文件 array.txt

1,zhangsan,唱歌-跳舞-游泳
2,lisi,打游戏-篮球

(3) 加载数据文件到t_array表中
load data local inpath ‘/root/array.txt’ into table t_array;
(4) 查询数据
select id ,name,hobby[0],hobby[1] from t_array；
注意：array的访问元素和java中是一样的，这里通过索引来访问。
3.2 集合类型之map
(1) 先创建一张表
create table t_map(id int,name string,hobby map<string,string>)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':' ;

(5) 准备数据文件 map.txt

1,zhangsan,唱歌:非常喜欢-跳舞:喜欢-游泳:一般般
2,lisi,打游戏:非常喜欢-篮球:不喜欢

(6) 加载数据文件到t_map表中
load data local inpath ‘/root/map.txt’ into table t_map;
(7) 查询数据
select id,name,hobby['唱歌'] from t_map;
注意：map的访问元素中的value和java中是一样的，这里通过key来访问。
3.3集合类型之struct
(1) 先创建一张表
create table t_struct(id int,name string,address struct<country:string,city:string>)
row format delimited
fields terminated by ','
collection items terminated by '-';

(8) 准备数据文件 struct.txt

1,zhangsan,china-beijing
2,lisi,USA-newyork

(9) 加载数据文件到t_struct表中
load data local inpath ‘/root/struct.txt’ into table t_struct;
(10) 查询数据
select id,name,address.country,address.city from t_struct;
总结：struct访问元素的方式是通过.符号

2.5 hive严格模式

hive提供了一个严格模式，可以防止用户执行那些可能产生意想不到的不好的效果的查询。即某些查询在严格模式下无法执行。通过设置hive.mapred.mode的值为strict，可以禁止3中类型的查询。
(1) 查询一个分区表时
如果在一个分区表执行hive，除非where语句中包含分区字段过滤条件来显示数据范围，否则不允许执行。换句话说，就是用户不允许扫描所有的分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。如果没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表：例如
hive> select * from t_patition;
FAILED: SemanticException [Error 10041]: No partition predicate found for Alias "t_patition" Table "t_patition"
注意查询的时候加上分区：
hive> select * from t_patition where country=’China’;

（2）带有order by的查询
对于使用了orderby的查询，要求必须有limit语句。因为orderby为了执行排序过程会讲所有的结果分发到同一个reducer中进行处理，强烈要求用户增加这个limit语句可以防止reducer额外执行很长一段时间：
hive> select * from student order by id;
FAILED: SemanticException 1:31 In strict mode, if ORDER BY is specified, LIMIT must also be specified. Error encountered near token 'id'
注意查询的时候加上limit：
select * from student order by id limit 10;

(3) 限制笛卡尔积的查询
对关系型数据库非常了解的用户可能期望在执行join查询的时候不使用on语句而是使用where语句，这样关系数据库的执行优化器就可以高效的将where语句转换成那个on语句。不幸的是，hive不会执行这种优化，因此，如果表足够大，那么这个查询就会出现不可控的情况：
hive> SELECT * FROM fracture_act JOIN fracture_ads
> WHERE fracture_act.planner_id = fracture_ads.planner_id;
FAILED: Error in semantic analysis: In strict mode, cartesian product
is not allowed. If you really want to perform the operation,
+set hive.mapred.mode=nonstrict+
注意查询的时候使用join和on语句的查询：
hive> SELECT * FROM fracture_act JOIN fracture_ads
> ON (fracture_act.planner_id = fracture_ads.planner_id);

3 Hive Shell参数

3.1 Hive命令行

语法结构

hive [-hiveconf x=y]* [<-i filename>]* [<-f filename>|<-e query-string>] [-S]

说明：

1、 -i 从文件初始化HQL。

2、 -e从命令行执行指定的HQL

3、 -f 执行HQL脚本

4、 -v 输出执行的HQL语句到控制台

5、 -p <port> connect to Hive Server on port number

6、 -hiveconf x=y Use this to set hive/hadoop configuration variables.

具体实例

1、运行一个查询。

2、运行一个文件。

3、运行参数文件。

3.2 Hive参数配置方式

Hive参数大全：

https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties

开发Hive应用时，不可避免地需要设定Hive的参数。设定Hive的参数可以调优HQL代码的执行效率，或帮助定位问题。然而实践中经常遇到的一个问题是，为什么设定的参数没有起作用？这通常是错误的设定方式导致的。

对于一般参数，有以下三种设定方式：

配置文件

命令行参数

参数声明

配置文件：Hive的配置文件包括

用户自定义配置文件：$HIVE_CONF_DIR/hive-site.xml

默认配置文件：$HIVE_CONF_DIR/hive-default.xml

用户自定义配置会覆盖默认配置。

另外，Hive也会读入Hadoop的配置，因为Hive是作为Hadoop的客户端启动的，Hive的配置会覆盖Hadoop的配置。

配置文件的设定对本机启动的所有Hive进程都有效。

命令行参数：启动Hive（客户端或Server方式）时，可以在命令行添加-hiveconf param=value来设定参数，例如：

bin/hive -hiveconf hive.root.logger=INFO,console

这一设定对本次启动的Session（对于Server方式启动，则是所有请求的Sessions）有效。

参数声明：可以在HQL中使用SET关键字设定参数，例如：

set mapred.reduce.tasks=100;

这一设定的作用域也是session级的。

每个reducer task任务处理的数据量。默认为1G

set hive.exec.reducers.bytes.per.reducer=<number>

设置reducers task的最大个数

set hive.exec.reducers.max=<number>

设置reduce task的任务数

set mapred.reduce.tasks=<number>

总结：

上述三种设定方式的优先级依次递增。即参数声明覆盖命令行参数，命令行参数覆盖配置文件设定。注意某些系统级的参数，例如log4j相关的设定，必须用前两种方式设定，因为那些参数的读取在Session建立以前已经完成了。

4. Hive函数

4.1 内置运算符，内置函数

内容较多，见《Hive官方文档》

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

https://www.cnblogs.com/dengrenning/articles/9503314.html

测试各种内置函数的快捷方法：

1、创建一个dual表

create table dual(id string);

2、load一个文件（一行，一个空格）到dual表

3、select substr('angelababy',2,3) from dual;

4.3 Hive自定义函数和Transform

当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。

4.3.1 自定义函数类别

UDF 作用于单个数据行，产生一个数据行作为输出。（数学函数，字符串函数）

UDAF（用户定义聚集函数）：接收多个输入数据行，并产生一个输出数据行。（count，max）

4.3.2 UDF开发实例

l 简单UDF示例

1、先开发一个java类，继承UDF，并重载evaluate方法

package cn.itcast.bigdata.udf

import org.apache.hadoop.hive.ql.exec.UDF;

import org.apache.hadoop.io.Text;

public final class Lower extends UDF{

public Text evaluate(final Text s){

if(s==null){return null;}

return new Text(s.toString().toLowerCase());

}

2、打成jar包上传到服务器

3、将jar包添加到hive的classpath

hive>add JAR /home/hadoop/udf.jar;

4、创建临时函数与开发好的java class关联

Hive>create temporary function tolowercase as 'cn.itcast.bigdata.udf.ToProvince';

5、即可在hql中使用自定义的函数tolowercase ip

Select tolowercase(name),age from t_test;

1：临时函数

a）自定义UDF需要继承org.apache.hadoop.hive.ql.UDF。
b）需要实现evaluate函数，evaluate函数支持重载。(注意：在一个库中创建的UDF函数，不能在另一个库中使用 )
c）把程序打包放到目标机器上去；
d）进入hive客户端，添加jar包：hive>add jar /run/jar/udf_test.jar;
e）创建临时函数：hive>CREATE TEMPORARY FUNCTION add_example AS 'hive.udf.Add';
f）查询HQL语句：
SELECT add_example(8, 9) FROM scores;
SELECT add_example(scores.math, scores.art) FROM scores;
SELECT add_example(6, 7, 8, 6.8) FROM scores;
g)销毁临时函数：hive> DROP TEMPORARY FUNCTION add_example;
注：UDF只能实现一进一出的操作，如果需要实现多进一出，则需要实现UDAF

2：永久函数
a）自定义UDF需要继承org.apache.hadoop.hive.ql.UDF。（注意该类的包名必须是org.apache.hadoop.hive.ql.udf）
b）需要实现evaluate函数，evaluate函数支持重载。
c）修改FunctionRegistry这个类，注册定义的udf
d) 把udf函数编译成class放到hive-exec-0.12.0-cdh5.0.0.jar中org\apache\hadoop\hive\ql\udf 路径下面
g) 将新的FunctionRegistry编译后的class文件替换hive-exec-0.12.0-cdh5.0.0.jar中的org.apache.hadoop.hive.ql.exec

Json数据解析UDF开发

作业：

有原始json数据如下：

{"movie":"1193","rate":"5","timeStamp":"978300760","uid":"1"}

{"movie":"661","rate":"3","timeStamp":"978302109","uid":"1"}

{"movie":"914","rate":"3","timeStamp":"978301968","uid":"1"}

{"movie":"3408","rate":"4","timeStamp":"978300275","uid":"1"}

{"movie":"2355","rate":"5","timeStamp":"978824291","uid":"1"}

{"movie":"1197","rate":"3","timeStamp":"978302268","uid":"1"}

{"movie":"1287","rate":"5","timeStamp":"978302039","uid":"1"}

需要将数据导入到hive数据仓库中

我不管你中间用几个表，最终我要得到一个结果表：

movie	rate	timestamp	uid
1197	3	978302268	1

注：全在hive中完成，可以用自定义函数

package cn.itcast.bigdata.hive.udf;

//{"movie":"1721","rate":"3","timeStamp":"965440048","uid":"5114"}

public class MovieRateBean {

private String movie;

private String rate;

private String timeStamp;

private String uid;

public String getMovie() {

return movie;

}

public void setMovie(String movie) {

this.movie = movie;

}

public String getRate() {

return rate;

}

public void setRate(String rate) {

this.rate = rate;

}

public String getTimeStamp() {

return timeStamp;

}

public void setTimeStamp(String timeStamp) {

this.timeStamp = timeStamp;

}

public String getUid() {

return uid;

}

public void setUid(String uid) {

this.uid = uid;

}

@Override

public String toString() {

return movie + "\t" + rate + "\t" + timeStamp + "\t" + uid;

}

package cn.itcast.bigdata.hive.udf;

import java.io.IOException;

import org.apache.hadoop.hive.ql.exec.UDF;

import parquet.org.codehaus.jackson.JsonParseException;

import parquet.org.codehaus.jackson.map.JsonMappingException;

import parquet.org.codehaus.jackson.map.ObjectMapper;

public class JsonParser extends UDF {

public String evaluate(String jsonLine) {

ObjectMapper objectMapper = new ObjectMapper();

try {

MovieRateBean bean = objectMapper.readValue(jsonLine, MovieRateBean.class);

return bean.toString();

} catch (Exception e) {

}

return "";

}

UDF案例：
create table rat_json(line string) row format delimited;
load data local inpath '/root/rating.json' into table rat_json;

drop table if exists t_rating;
create table t_rating(movieid string,rate int,timestring string,uid string)
row format delimited fields terminated by '\t';

insert overwrite table t_rating
select split(parsejson(line),'\t')[0]as movieid,split(parsejson(line),'\t')[1] as rate,split(parsejson(line),'\t')[2] as timestring,split(parsejson(line),'\t')[3] as uid from rat_json limit 10;


-------
内置jason函数
select get_json_object(line,'$.movie') as moive,get_json_object(line,'$.rate') as rate  from rat_json limit 10;

4.3.3 Transform实现

Hive的 TRANSFORM 关键字提供了在SQL中调用自写脚本的功能

适合实现Hive中没有的功能又不想写UDF的情况

使用示例1：下面这句sql就是借用了weekday_mapper.py对数据进行了处理.

CREATE TABLE u_data_new (

movieid INT,

rating INT,

weekday INT,

userid INT)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t';

add FILE weekday_mapper.py;

INSERT OVERWRITE TABLE u_data_new

SELECT

TRANSFORM (movieid , rate, timestring,uid)

USING 'python weekday_mapper.py'

AS (movieid, rating, weekday,userid)

FROM t_rating;

其中weekday_mapper.py内容如下

#!/bin/python

import sys

import datetime

for line in sys.stdin:

line = line.strip()

movieid, rating, unixtime,userid = line.split('\t')

weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()

print '\t'.join([movieid, rating, str(weekday),userid])

4.4 HIVE特殊分隔符处理

Hive对文件中的分隔符默认情况下只支持单字节分隔符，,默认单字符是\001。当然你也可以在创建表格时指定数据的分割符号。但是如果数据文件中的分隔符是多字符的，如下图：

01||zhangsan

02||lisi

03||wangwu

补充：hive读取数据的机制

1、首先用inputformat的一个具体的实现类读取文件数据，返回一条条的记录（可以是行，或者是你逻辑中的“行”）

2、然后利用SerDe<默认：org.apache.hadoop.hive.serde2.LazySimpleSerDe>的一个具体的实现类，对上面返回的一条条记录进行字段切割

4.4.1使用RegexSerDe通过正则表达式来抽取字段

1、建表

create table t_bi_reg(id string,name string)

row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'

with serdeproperties(

'input.regex'='(.*)\\|\\|(.*)',

'output.format.string'='%1$s%2$s'

)

stored as textfile;

2、加载数据

01||zhangsan

02||lisi

03||wangwu

load data local inpath '/root/lianggang.txt' into table t_bi_reg;

3、查询

hive> select * from t_bi_reg;

01 zhangsan

02 lisi

03 wangwu

4.4.2通过自定义inputformat解决特殊分隔符问题

其原理是在inputformat读取行的时候将数据中的“多字节分隔符”替换为hive默认的分隔符（ctrl+A 亦即 \001）或用于替代的单字符分隔符。以便hive在serde操作的时候按照默认的单字节分隔符进行字段抽取

package cn.itcast.bigdata.hive.inputformat;

import java.io.IOException;

import org.apache.hadoop.io.LongWritable;

import org.apache.hadoop.io.Text;

import org.apache.hadoop.mapred.FileSplit;

import org.apache.hadoop.mapred.InputSplit;

import org.apache.hadoop.mapred.JobConf;

import org.apache.hadoop.mapred.LineRecordReader;

import org.apache.hadoop.mapred.RecordReader;

import org.apache.hadoop.mapred.Reporter;

import org.apache.hadoop.mapred.TextInputFormat;

public class BiDelimiterInputFormat extends TextInputFormat {

@Override

public RecordReader<LongWritable, Text> getRecordReader(

InputSplit genericSplit, JobConf job, Reporter reporter)

throws IOException {

reporter.setStatus(genericSplit.toString());

MyDemoRecordReader reader = new MyDemoRecordReader(new LineRecordReader(job, (FileSplit) genericSplit));

// BiRecordReader reader = new BiRecordReader(job, (FileSplit)genericSplit);

return reader;

}

public static class MyDemoRecordReader implements RecordReader<LongWritable, Text> {

LineRecordReader reader;

Text text;

public MyDemoRecordReader(LineRecordReader reader) {

this.reader = reader;

text = reader.createValue();

}

@Override

public void close() throws IOException {

reader.close();

}

@Override

public LongWritable createKey() {

return reader.createKey();

}

@Override

public Text createValue() {

return new Text();

}

@Override

public long getPos() throws IOException {

return reader.getPos();

}

@Override

public float getProgress() throws IOException {

return reader.getProgress();

}

@Override

public boolean next(LongWritable key, Text value) throws IOException {

boolean next = reader.next(key, text);

if(next){

String replaceText = text.toString().replaceAll("\\|\\|", "\\|");

value.set(replaceText);

}

return next;

}

1.打包成jar，放到$HIVE_HOME/lib下

2.建表指明自定义的inputformat

create table t_lianggang(id string,name string)

row format delimited

fields terminated by '|'

stored as inputformat 'cn.itcast.bigdata.hive.inputformat.BiDelimiterInputFormat'

outputformat 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';

3.加载数据

01||zhangsan

02||lisi

03||wangwu

load data local inpath '/root/lianggang.txt' into table t_lianggang;

4.查询

hive> select * from t_lianggang;

01 zhangsan

02 lisi

03 wangwu

5. Hive实战

Hive 实战案例1——数据ETL

需求：

ü 对web点击流日志基础数据表进行etl（按照仓库模型设计）

ü 按各时间维度统计来源域名top10

已有数据表 “t_orgin_weblog” ：

+------------------+------------+----------+--+

| col_name | data_type | comment |

+------------------+------------+----------+--+

| valid | string | |

| remote_addr | string | |

| remote_user | string | |

| time_local | string | |

| request | string | |

| status | string | |

| body_bytes_sent | string | |

| http_referer | string | |

| http_user_agent | string | |

+------------------+------------+----------+--+

数据示例：

| true|1.162.203.134| - | 18/Sep/2013:13:47:35| /images/my.jpg | 200| 19939 | "http://www.angularjs.cn/A0d9" | "Mozilla/5.0 (Windows |

| true|1.202.186.37 | - | 18/Sep/2013:15:39:11| /wp-content/uploads/2013/08/windjs.png| 200| 34613 | "http://cnodejs.org/topic/521a30d4bee8d3cb1272ac0f" | "Mozilla/5.0 (Macintosh;|

实现步骤：

1、对原始数据进行抽取转换

--将来访url分离出host path query query id

drop table if exists t_etl_referurl;

create table t_etl_referurl as

SELECT a.*,b.*

FROM t_orgin_weblog a LATERAL VIEW parse_url_tuple(regexp_replace(http_referer, "\"", ""), 'HOST', 'PATH','QUERY', 'QUERY:id') b as host, path, query, query_id

3、从前述步骤进一步分离出日期时间形成ETL明细表“t_etl_detail” day tm

drop table if exists t_etl_detail;

create table t_etl_detail as

select b.*,substring(time_local,0,11) as daystr,

substring(time_local,13) as tmstr,

substring(time_local,4,3) as month,

substring(time_local,0,2) as day,

substring(time_local,13,2) as hour

from t_etl_referurl b;

3、对etl数据进行分区(包含所有数据的结构化信息)

drop table t_etl_detail_prt;

create table t_etl_detail_prt(

valid string,

remote_addr string,

remote_user string,

time_local string,

request string,

status string,

body_bytes_sent string,

http_referer string,

http_user_agent string,

host string,

path string,

query string,

query_id string,

daystr string,

tmstr string,

month string,

day string,

hour string)

partitioned by (mm string,dd string);

导入数据

insert into table t_etl_detail_prt partition(mm='Sep',dd='18')

select * from t_etl_detail where daystr='18/Sep/2013';

insert into table t_etl_detail_prt partition(mm='Sep',dd='19')

select * from t_etl_detail where daystr='19/Sep/2013';

分个时间维度统计各referer_host的访问次数并排序

create table t_refer_host_visit_top_tmp as

select referer_host,count(*) as counts,mm,dd,hh from t_display_referer_counts group by hh,dd,mm,referer_host order by hh asc,dd asc,mm asc,counts desc;

4、来源访问次数topn各时间维度URL

取各时间维度的referer_host访问次数topn

select * from (select referer_host,counts,concat(hh,dd),row_number() over (partition by concat(hh,dd) order by concat(hh,dd) asc) as od from t_refer_host_visit_top_tmp) t where od<=3;

Hive 实战案例2——访问时长统计

需求：

从web日志中统计每日访客平均停留时间

实现步骤：

1、由于要从大量请求中分辨出用户的各次访问，逻辑相对复杂，通过hive直接实现有困难，因此编写一个mr程序来求出访客访问信息（详见代码）

启动mr程序获取结果：

[hadoop@hdp-node-01 ~]$ hadoop jar weblog.jar cn.itcast.bigdata.hive.mr.UserStayTime /weblog/input /weblog/stayout

2、将mr的处理结果导入hive表

drop table t_display_access_info_tmp;

create table t_display_access_info_tmp(remote_addr string,firt_req_time string,last_req_time string,stay_long bigint)

row format delimited fields terminated by '\t';

load data inpath '/weblog/stayout4' into table t_display_access_info_tmp;

3、得出访客访问信息表 "t_display_access_info"

由于有一些访问记录是单条记录，mr程序处理处的结果给的时长是0，所以考虑给单次请求的停留时间一个默认市场30秒

drop table t_display_access_info;

create table t_display_access_info as

select remote_addr,firt_req_time,last_req_time,

case stay_long

when 0 then 30000

else stay_long

end as stay_long

from t_display_access_info_tmp;

4、统计所有用户停留时间平均值

select avg(stay_long) from t_display_access_info;

Hive实战案例3——级联求和accumulate

需求：

有如下访客访问次数统计表 t_access_times

访客	月份	访问次数
A	2015-01	5
A	2015-01	15
B	2015-01	5
A	2015-01	8
B	2015-01	25
A	2015-01	5
A	2015-02	4
A	2015-02	6
B	2015-02	10
B	2015-02	5
……	……	……

需要输出报表：t_access_times_accumulate

访客	月份	月访问总计	累计访问总计
A	2015-01	33	33
A	2015-02	10	43
…….	…….	…….	…….
B	2015-01	30	30
B	2015-02	15	45
…….	…….	…….	…….

实现步骤

可以用一个hql语句即可实现：

select A.username,A.month,max(A.salary) as salary,sum(B.salary) as accumulate

from

(select username,month,sum(salary) as salary from t_access_times group by username,month) A

inner join

(select username,month,sum(salary) as salary from t_access_times group by username,month) B

A.username=B.username

where B.month <= A.month

group by A.username,A.month

order by A.username,A.month;

create table t_access_times(username string,month string,salary int)
row format delimited fields terminated by ',';

load data local inpath '/root/hivedata/t_access_times.dat' into table t_access_times;

A,2015-01,5
A,2015-01,15
B,2015-01,5
A,2015-01,8
B,2015-01,25
A,2015-01,5
A,2015-02,4
A,2015-02,6
B,2015-02,10
B,2015-02,5


1、第一步，先求个用户的月总金额
select username,month,sum(salary) as salary from t_access_times group by username,month

+-----------+----------+---------+--+
| username  |  month   | salary  |
+-----------+----------+---------+--+
| A         | 2015-01  | 33      |
| A         | 2015-02  | 10      |
| B         | 2015-01  | 30      |
| B         | 2015-02  | 15      |
+-----------+----------+---------+--+

2、第二步，将月总金额表 自己连接 自己连接
select A.*,B.* FROM
(select username,month,sum(salary) as salary from t_access_times group by username,month) A 
inner join 
(select username,month,sum(salary) as salary from t_access_times group by username,month) B
on
A.username=B.username
where B.month <= A.month
+-------------+----------+-----------+-------------+----------+-----------+--+
| a.username  | a.month  | a.salary  | b.username  | b.month  | b.salary  |
+-------------+----------+-----------+-------------+----------+-----------+--+
| A           | 2015-01  | 33        | A           | 2015-01  | 33        |
| A           | 2015-01  | 33        | A           | 2015-02  | 10        |
| A           | 2015-02  | 10        | A           | 2015-01  | 33        |
| A           | 2015-02  | 10        | A           | 2015-02  | 10        |
| B           | 2015-01  | 30        | B           | 2015-01  | 30        |
| B           | 2015-01  | 30        | B           | 2015-02  | 15        |
| B           | 2015-02  | 15        | B           | 2015-01  | 30        |
| B           | 2015-02  | 15        | B           | 2015-02  | 15        |
+-------------+----------+-----------+-------------+----------+-----------+--+

3、第三步，从上一步的结果中
进行分组查询，分组的字段是a.username a.month
求月累计值：  将b.month <= a.month的所有b.salary求和即可
select A.username,A.month,max(A.salary) as salary,sum(B.salary) as accumulate
from 
(select username,month,sum(salary) as salary from t_access_times group by username,month) A 
inner join 
(select username,month,sum(salary) as salary from t_access_times group by username,month) B
on
A.username=B.username
where B.month <= A.month
group by A.username,A.month
order by A.username,A.month;

查询语言。由于 SQL 被广泛的应用在数据仓库中，因此，专门针对 Hive 的特性设计了类 SQL 的查询语言 HQL。熟悉 SQL 开发的开发者可以很方便的使用 Hive 进行开发。
数据存储位置。Hive 是建立在 Hadoop 之上的，所有 Hive 的数据都是存储在 HDFS 中的。而数据库则可以将数据保存在块设备或者本地文件系统中。
数据格式。Hive 中没有定义专门的数据格式，数据格式可以由用户指定，用户定义数据格式需要指定三个属性：列分隔符（通常为空格、”\t”、”\x001″）、行分隔符（”\n”）以及读取文件数据的方法（Hive 中默认有三个文件格式 TextFile，SequenceFile 以及 RCFile）。由于在加载数据的过程中，不需要从用户数据格式到 Hive 定义的数据格式的转换，因此，Hive 在加载的过程中不会对数据本身进行任何修改，而只是将数据内容复制或者移动到相应的 HDFS 目录中。而在数据库中，不同的数据库有不同的存储引擎，定义了自己的数据格式。所有数据都会按照一定的组织存储，因此，数据库加载数据的过程会比较耗时。
数据更新。由于 Hive 是针对数据仓库应用设计的，而数据仓库的内容是读多写少的。因此，Hive 中不支持对数据的改写和添加，所有的数据都是在加载的时候中确定好的。而数据库中的数据通常是需要经常进行修改的，因此可以使用 INSERT INTO ... VALUES 添加数据，使用 UPDATE ... SET 修改数据。
索引。之前已经说过，Hive 在加载数据的过程中不会对数据进行任何处理，甚至不会对数据进行扫描，因此也没有对数据中的某些 Key 建立索引。Hive 要访问数据中满足条件的特定值时，需要暴力扫描整个数据，因此访问延迟较高。由于 MapReduce 的引入， Hive 可以并行访问数据，因此即使没有索引，对于大数据量的访问，Hive 仍然可以体现出优势。数据库中，通常会针对一个或者几个列建立索引，因此对于少量的特定条件的数据的访问，数据库可以有很高的效率，较低的延迟。由于数据的访问延迟较高，决定了 Hive 不适合在线数据查询。
执行。Hive 中大多数查询的执行是通过 Hadoop 提供的 MapReduce 来实现的，而数据库通常有自己的执行引擎。
执行延迟。之前提到，Hive 在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟较高。另外一个导致 Hive 执行延迟高的因素是 MapReduce 框架。由于 MapReduce 本身具有较高的延迟，因此在利用 MapReduce 执行 Hive 查询时，也会有较高的延迟。相对的，数据库的执行延迟较低。当然，这个低是有条件的，即数据规模较小，当数据规模大到超过数据库的处理能力的时候，Hive 的并行计算显然能体现出优势。
可扩展性。由于 Hive 是建立在 Hadoop 之上的，因此 Hive 的可扩展性是和 Hadoop 的可扩展性是一致的（世界上最大的 Hadoop 集群在 Yahoo!，2009年的规模在 4000 台节点左右）。而数据库由于 ACID 语义的严格限制，扩展行非常有限。目前最先进的并行数据库 Oracle 在理论上的扩展能力也只有 100 台左右。
数据规模。由于 Hive 建立在集群上并可以利用 MapReduce 进行并行计算，因此可以支持很大规模的数据；对应的，数据库可以支持的数据规模较小。

如果指定了 LOCAL，那么：

load 命令会去查找本地文件系统中的 filepath。如果发现是相对路径，则路径会被解释为相对于当前用户的当前路径。

load 命令会将 filepath中的文件复制到目标文件系统中。目标文件系统由表的位置属性决定。被复制的数据文件移动到表的数据对应的位置。

如果没有指定 LOCAL 关键字，如果 filepath 指向的是一个完整的 URI，hive 会直接使用这个 URI。否则：如果没有指定 schema 或者 authority，Hive 会使用在 hadoop 配置文件中定义的 schema 和 authority，fs.default.name 指定了 Namenode 的 URI。

如果路径不是绝对的，Hive 相对于/user/进行解释。

Hive 会将 filepath 中指定的文件内容移动到 table （或者 partition）所指定的路径中。

[dht3]

posted @ 2018-08-20 16:25 邓不利多阅读(478) 评论(0) 收藏举报

刷新页面返回顶部

邓不利多

HIVE

1. Hive基本概念

1.1 Hive简介

1.1.1 什么是Hive

1.1.2 为什么使用Hive

1.1.3 Hive的特点

1.2 Hive架构

1.2.1 架构图

1.2.2 基本组成

1.2.3 各组件的基本功能

1.3 Hive与Hadoop的关系

1.4 Hive与传统数据库对比 hive用于海量数据的离线数据分析

1.5 Hive的数据存储

1.6 HIVE的安装部署

1.6.1 安装

derby版：

元数据库mysql版：

1.6.2 使用方式

Hive交互shell：bin/hive

Hive JDBC服务

2. Hive基本操作

2.1 DDL操作

2.1.1 创建表

建表语法

具体实例

2.1.2 修改表

增加/删除分区

重命名表

增加/更新列

2.1.3 显示命令

2.2 DML操作

2.2.1 Load

2.2.2 Insert

2.2.3 SELECT

2.3 Hive Join

2.4 hive的数据类型

2.5 hive严格模式

3 Hive Shell参数

3.1 Hive命令行

3.2 Hive参数配置方式

4. Hive函数

4.1 内置运算符，内置函数

4.3 Hive自定义函数和Transform

4.3.1 自定义函数类别

4.3.2 UDF开发实例

4.3.3 Transform实现

4.4 HIVE特殊分隔符处理

4.4.1使用RegexSerDe通过正则表达式来抽取字段

4.4.2通过自定义inputformat解决特殊分隔符问题

5. Hive实战

Hive 实战案例1——数据ETL

需求：

数据示例：

实现步骤：

Hive 实战案例2——访问时长统计

需求：

实现步骤：

Hive实战案例3——级联求和accumulate

需求：

公告

1.4 Hive与传统数据库对比
hive用于海量数据的离线数据分析