Hive

1. Hive 简介

• 1.1 什么是 Hive？
• 1.2 Hive 系统架构

2. Hive 配置管理

• 2.1 Hive 安装配置
• 2.2 Mysql 配置
• 2.3 Hive 运行模式
• 2.4 Hive 启动方式

3. Hive 数据类型及表结构

• 3.1 数据类型
• 3.2 数据存储
• 3.3 数据库
• 3.4 数据表

4. HQL

• 4.1 HQL 基础语法
• 4.2 数据导入与加载
• 4.3 日期格式转换

5. Beeline

 

 

1. Hive 简介

1.1 什么是 Hive？

Hive 是建立在 Hadoop 上的数据仓库基础构架。它提供了一系列的工具，可以用来进行数据的提取转化加载（ETL），这是一种可以存储、查询和分析存储在 Hadoop 中的大规模数据的机制。

Hive 定义了简单的类 SQL 查询语言，称为 HQL，它允许熟悉 SQL 的用户查询数据。同时，这个语言也允许熟悉 MapReduce 开发者开发自定义的 mapper 和 reducer 来处理内建的 MapReduce 所无法完成的复杂的分析工作。

Hive 是 SQL 解析引擎，它将 SQL 语句转译成 MapReduce Job 然后在 Hadoop 执行。

Hive 的表其实就是 HDFS 的目录/文件夹，按表名把文件夹分开，表里的数据对应的是目录里的文件。如果是分区表，则分区值是子文件夹，可以直接在 M/R Job 里使用这些数据。

1.2 Hive 系统架构

1）用户接口主要有三个：CLI、JDBC/ODBC 和 WebUI

1. CL：即 Shell 命令行。
2. JDBC/ODBC：Hive 的 Java，与使用传统数据库 JDBC 的方式类似。
3. WebGUI：通过浏览器访问 Hive。

2）Hive 将元数据存储在数据库中（metastore），目前只支持 mysql、derby。Hive 中的元数据包括表的名字、表的列和分区及其属性、表的属性（是否为外部表等）、表的数据所在目录等。metastore 默认使用内嵌的 derby 数据库作为存储引擎 。

• Derby 引擎的缺点：一次只能打开一个会话 ；在不同目录执行能打开多个会话，但已不是同个库。
• 使用 Mysql 作为外置存储引擎，则可多用户同时访问。

3）解释器、编译器、优化器完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划（Plan）的生成。生成的查询计划存储在 HDFS 中，并在随后有 MapReduce 调用执行。

4）Hive 的数据存储在 HDFS 中，大部分的查询由 MapReduce 完成（但包含 * 的查询，比如 select * from table 则不会生成 MapRedcue 任务）。

 

2. Hive 配置管理

2.1 Hive 安装配置

1. 上传并解压 tar 包：tar -zxvf hive-0.9.0.tar.gz -C /cloud/
2. 配置 mysql metastore（切换到 root 用户），以及 HIVE_HOME 环境变量
3. 自动读取环境变量 HADOOP_HOME
4. cp hive-default.xml.template hive-site.xml
5. 修改 hive-site.xml（删除所有内容，只留一个 <property></property>），添加如下内容：

    <property>
      <name>javax.jdo.option.ConnectionURL</name>
      <value>jdbc:mysql://itcast04: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>123</value>
      <description>password to use against metastore database</description>
    </property>

2.2 Mysql 配置

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

如果出现没有权限的问题，在 mysql 进行授权（在安装 mysql 的机器上执行）：

mysql -uroot -p

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;
-- *.* 表示所有库下的所有表   
-- % 表示任何IP地址或主机都可以连接

FLUSH PRIVILEGES;

2.3 Hive 运行模式

Hive 的运行模式即任务的执行环境，分为本地与集群两种。

我们可以通过 mapred.job.tracker 来指明。设置方式如下：

hive > SET mapred.job.tracker=local

2.4 Hive 启动方式

命令行模式

• 直接输入 /hive/bin/hive 的执行程序，或者输入：hive --service cli

web 界面

• hive web 界面的 (端口号 9999) 启动方式：hive --service hwi &
• 用于通过浏览器来访问 hive ：http://hadoop0:9999/hwi/

远程服务

• hive 远程服务 (端口号10000) 启动方式：hive --service hiveserver &

 

3. Hive 数据类型及表结构

3.1 数据类型

基本数据类型

• tinyint/smallint/int/bigint
• float/double
• boolean
• string

复杂数据类型

• Array/Map/Struct
• 没有 date/datetime

3.2 数据存储

• Hive 的数据存储基于 Hadoop HDFS。
• Hive 没有专门的数据存储格式。
• 存储结构主要包括：数据库、文件、表、视图。
• Hive 默认直接加载文本文件（TextFile），还支持 Sequence file、RC file。
• 创建表时，指定 Hive 数据的列分隔符与行分隔符，Hive 即可解析数据。

3.3 数据库

• Hive 类似传统数据库的 DataBase，默认数据库“default”。
• 使用 hive 命令后，若不使用 hive>use <数据库名>，则使用系统默认的数据库。可以显式使用 hive> use default;
• 创建一个新库：hive> create database test_dw;

3.4 数据表

1. Table：内部表
2. Partition：分区表
3. External Table：外部表
4. Bucket Table：桶表

1）内部表

• 与数据库中的 Table 在概念上是类似的。
• 每一个 Table 在 Hive 中都有一个相应的目录存储数据。例如，一个表 test，它在 HDFS 中的路径为：/warehouse/test。warehouse 是在 hive-site.xml 中由 ${hive.metastore.warehouse.dir} 指定的数据仓库的目录。
• 所有的 Table 数据（不包括 External Table）都保存在这个目录中。
• 删除表时，元数据与数据都会被删除。

示例：加载数据文件 inner_table.txt

-- 创建表
create table inner_table (key string);

-- 加载本机的数据
load data local inpath '/root/inner_table.txt' into table inner_table;

-- 查看数据
select * from inner_table;
select count(*) from inner_table;

-- 删除表
drop table inner_table;

2）分区表

Partition 对应于数据库的 Partition 列的密集索引。

在 Hive 中，表中的一个 Partition 对应于表下的一个子目录，所有的 Partition 的数据都存储在对应的目录中。例如：test 表中包含 date 和 city 两个 Partition，则：

• 对应于 date=20130201, city = bj 的 HDFS 子目录为：/warehouse/test/date=20130201/city=bj
• 对应于 date=20130202, city=sh 的HDFS 子目录为：/warehouse/test/date=20130202/city=sh

示例：分区表的建表语句

CREATE TABLE tmp_table  -- 表名
(
 title   string,  -- 字段名称 字段类型
 minimum_bid     double,
 quantity        bigint,
 have_invoice    bigint
)
COMMENT '注释：XXX'  -- 表注释
PARTITIONED BY(pt STRING)  -- 分区表字段（如果文件非常之大，采用分区表可以快过滤出按分区字段划分的数据），且分区不能是已存在的字段，否则会报错。
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'   -- 字段是用什么分割开的
STORED AS SEQUENCEFILE;  -- 用哪种方式存储数据，SEQUENCEFILE是hadoop自带的文件压缩格式

示例：加载数据文件 partition_table.dat

-- 创建分区表
create table partition_table(rectime string, msisdn string) partitioned by(daytime string, city string) row format delimited fields terminated by '\t' stored as TEXTFILE;

-- 加载数据到分区
load data local inpath'/home/partition_table.dat' into table partition_table partition (daytime='2013-02-01', city='bj');

-- 查看数据
select * from partition_table;
select count(*) from partition_table;

-- 删除表 
drop table partition_table;

-- 增加分区
alter table partition_table add partition (daytime='2013-02-04',city='bj');

-- 删除分区
alter table partition_table drop partition (daytime='2013-02-04',city='bj');
-- 元数据，数据文件删除，但目录daytime=2013-02-04还在

3）外部表

• 外部表指向已经在 HDFS 中存在的数据，可以创建 Partition。
• 它和内部表在元数据的组织上是相同的，而实际数据的存储则有较大的差异。
• 内部表的创建过程和数据加载过程可以在同一个语句中完成。在加载数据的过程中，实际数据会被移动到数据仓库目录中；之后对数据对访问将会直接在数据仓库目录中完成。删除表时，表中的数据和元数据将会被同时删除。
• 外部表只有一个过程，加载数据和创建表同时完成，并不会移动到数据仓库目录中，只是与外部数据建立一个链接。当删除一个外部表时，仅删除该链接。

示例：创建数据文件 external_table.dat

-- 创建表
create external table external_table1 (key string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' location '/home/external';

-- 在HDFS创建目录/home/external
-- hadoop fs -put /home/external_table.dat /home/external

-- 在hive中也可直接操作hdfs
-- hive> dfs -put /home/external_table.dat /home/external

-- 加载数据
LOAD DATA INPATH '/home/external_table1.dat' INTO TABLE external_table1;

-- 查看数据
select * from external_table;
select count(*) from external_table;

-- 删除表
drop table external_table;

 

4. HQL

4.1 HQL 基础语法

进入 Hive 中直接执行 hql：

-- 查看表
SHOW TABLES;  -- 查看所有的表
SHOW TABLES '*TMP*';  -- 支持模糊查询
SHOW PARTITIONS TMP_TABLE;  -- 查看表有哪些分区
DESCRIBE TMP_TABLE;  -- 查看表结构

-- 查看数据
select * from partition_table;
select count(*) from partition_table;

Hive 命令执行 hql：

-- 执行hql
hive -f /home/arch02/hive_create_object.hql;

hive -S -hivevar hv_date="201701" -f /opt/mcb/web/lib/hql/BI_DWD_CDR_ANALYSIS_MONTH.hql;

-- 查看创建了哪些表
hive -e “show tables”;

-- 查看某个表的表结构
hive -e “describe table_name”

-- 删除分区
hive -e "ALTER TABLE BI_DWD_CDR_ANALYSIS_DAY DROP PARTITION(pt='20170101')";

-- 删除分区文件：
hadoop fs -rm -r /user/hive/warehouse/BI_DWD_CDR_ANALYSIS_DAY/pt=20170101;
创建分区：

hive -S -e "ALTER TABLE BI_DWD_CDR_ANALYSIS_DAY ADD PARTITION(pt='20170101') LOCATION '/user/hive/warehouse/BI_DWD_CDR_ANALYSIS_DAY/pt=20170101'";

Hive 生成数据文件：

-- 1）在 Hive 中执行
INSERT OVERWRITE DIRECTORY '/user/arch/BI_DW_RTS_GLOBAL_D_20170101' select * from BI_DW_RTS_GLOBAL_D a where pt='20170101';

-- 2）查看文件是否成功生成
hadoop fs -ls /user/arch/

4.2 数据导入与加载

注意：

• 表格式 orcfile 表示压缩格式，可以节约大量存储空间，但 orc 还有个特点就是不能直接 load 数据。要想 load 数据，我们要建一个存储格式为 textfile 的中间表，再把数据抽取过去。
• 导入的文本数据不需要带标题行。

1）从本地文件导入数据

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

• overwrite：可选，先删除原来表数据，然后再加载新数据。
• partition：这里是指将inpath中的所有数据加载到那个分区，并不会判断待加载的数据中每一条记录属于哪个分区。

local：

可选，从本地文件系统中加载，而非 hdfs。load data [local] inpath，是一个服务器端的指令，它是在服务器端执行。因此指定 local 时表明加载的文件为本地文件，但是这里的 local，在 hive 中指的是 hiveserver 服务所在的机器，而不是 hivecli 或 beeline 客户端所在的机器（生产环境大都是 hiveserver 和 hivecli 不在同一个机器）。
• 解决方法一：把要加载的文件上传到 hiveserver 所在的服务器（这一般是不可能的事情），然后执行 load data local inpath [path] [overwrite] into table table_name.
• 解决方法二（推荐）：先将本地文件上传至 hdfs，然后使用 load data inpath [hdfspath] [overwrite] into table table_name.
• 注意：load 完了之后，会自动把 INPATH 下面的源数据删掉，其实就是将 INPATH 下面的数据移动到 /usr/hive/warehouse 目录下了。

两种方式验证数据是否导入成功：

1. 在 Hive 中执行查询语句。
2. 查看 hdfs 文件系统中的 load_data_local 目录下面是否有刚刚上传的 load_data_local.txt 文件，查看命令为：hadoop fs -ls /usr/hive/warehouse/bigdata17.db/load_data_local。

2）从 HDFS 导入数据

1）将本地 txt 格式数据文件上传到服务器任意目录下，并在该目录下使用命令将文件上传到 HDFS 目录中：

hdfs dfs -put user_day.txt /tmp

2）查询是否上传成功：

hdfs dfs -ls /tmp

3）在Hive中执行导入命令：

LOAD DATA INPATH '/user/wangjs/user_day.txt' OVERWRITE INTO TABLE bdp_dw.dw_user_day_zxp;

注意：从 hdfs 文件系统导入数据成功后，会把 hdfs 文件系统中的 load_data_hdfs.txt 文件删除掉。

3）从其它 Hive 表中导入数据

insert into table load_data_local2 select * from load_data_local;

导入数据到分区表分为静态方式和动态方式两种方式。

静态方式

必须显式的指定分区值，如果分区有很多值，则必须执行多条 SQL，效率低下。

insert into table load_data_partition partition(age=25) select name from load_data_local;
-- 若要覆盖原有数据，则将 into 改成 overwrite

动态方式

这种方式要注意目标表的字段必须和 select 查询语句字段的顺序和类型一致，特别是分区字段的类型要一致，否则会报错。

首先，启动动态分区功能：

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

若一张表有两个以上的分区字段，如果同时使用静态分区和动态分区导入数据，静态分区字段必须写在动态分区字段之前。

insert overwrite table par_dnm partition(sex='man', dt) select name, nation, dt from par_tab;

实现 sex 为静态分区，dt 为动态分区（不指定到底是哪日，让系统自己分配决定）。

将数据插入多个表

Hive 还支持一条 sql 语句中将数据插入多个表的功能，只需将 from 关键字前置即可。

from load_data_local 
insert overwrite table load_data_partition partition (age)
  select name, age
insert overwrite table load_data_local3 
  select *
;

上面的 sql 语句同时插入到表 load_data_partition 和 load_data_local3 表中。这种方式非常高效，对于大数据量并且要将数据插入到多个表的情况下，建议用这种方式。

4）创建表的同时导入数据

create table load_data_local3 as select * from load_data_local;

新建的 load_data_local3 表没有分区，因此 create ... as 不能用来复制分区表。

5）将表数据导出成文本文件

hive -e 'select * from irbas.dw_user_day;' > dw_user_day.txt

4.3 日期格式转换

在解析数据时会遇到两种不同的日期格式：yyyymmdd 和 yyyy-mm-dd，此类型之间的转换主要有两种思路。

第一种方法：from_unixtime + unix_timestamp

--20180905 转成 2018-09-05
select from_unixtime(unix_timestamp('20180905','yyyymmdd'),'yyyy-mm-dd')
from dw.ceshi_data;
--结果：2018-09-05
 
--2018-09-05 转成 20180905
select from_unixtime(unix_timestamp('2018-09-05','yyyy-mm-dd'),'yyyymmdd')
from dw.ceshi_data;
--结果：20180905

第二种方法：substr + concat

--20180905 转成 2018-09-05 
select concat(substr('20180905',1,4),'-',substr('20180905',5,2),'-',substr('20180905',7,2)) from dw.ceshi_data
--结果：2018-09-05 
 
--2018-09-05 转成 20180905
select concat(substr('2018-09-05',1,4),substr('2018-09-05',6,2),substr('2018-09-05',9,2)) from dw.ceshi_data
--结果：20180905

 

5. Beeline

Beeline 是 Hive 2 新的命令行客户端工具（Hive 0.11 版本引入），它是基于 SQLLine CLI 的 JDBC 客户端。

Hive 客户端工具后续将使用 Beeline 替代 HiveCLI ，并且后续版本也会废弃掉 HiveCLI 客户端工具。

Beeline 支持嵌入模式（embedded mode）和远程模式（remote mode）。：

• 在嵌入式模式下，运行嵌入式的 Hive（类似 Hive CLI）；
• 而远程模式可以通过 Thrift 连接到独立的 HiveServer2 进程上。

从 Hive 0.14 版本开始，Beeline 使用 HiveServer2 工作时，它也会从 HiveServer2 输出日志信息到 STDERR。

常用参数

The Beeline CLI 支持以下命令行参数：

• --autoCommit=[true/false]：进入一个自动提交模式。如 beeline --autoCommit=true
• --autosave=[true/false]：进入一个自动保存模式。如 beeline --autosave=true
• --color=[true/false]：显示用到的颜色。如 beeline --color=true
• --delimiterForDSV= DELIMITER：分隔值输出格式的分隔符。默认是“|”字符。
• --fastConnect=[true/false] ：在连接时，跳过组建表等对象。如 beeline --fastConnect=false
• --force=[true/false]：是否强制运行脚本。如 beeline--force=true
• --headerInterval=ROWS：输出的表间隔格式，默认是100。如 beeline --headerInterval=50
• --help：帮助：beeline --help
• --hiveconf property=value ：设置属性值，以防被 hive.conf.restricted.list 重置。如 beeline --hiveconf prop1=value1
• --hivevar name=value：设置变量名。如 beeline --hivevar var1=value1
• --incremental=[true/false] ：输出增量
• --isolation=LEVEL：设置事务隔离级别。如 beeline --isolation=TRANSACTION_SERIALIZABLE
• --maxColumnWidth=MAXCOLWIDTH：设置字符串列的最大宽度。如 beeline --maxColumnWidth=25
• --maxWidth=MAXWIDTH：设置截断数据的最大宽度。如 beeline --maxWidth=150
• --nullemptystring=[true/false]：打印空字符串。如 beeline --nullemptystring=false
• --numberFormat=[pattern]：数字使用DecimalFormat。如 beeline --numberFormat="#,###,##0.00"
• --outputformat=[table/vertical/csv/tsv/dsv/csv2/tsv2]：输出格式。如 beeline --outputformat=tsv
• --showHeader=[true/false]：显示查询结果的列名。如 beeline --showHeader=false
• --showNestedErrs=[true/false]：显示嵌套错误。如 beeline --showNestedErrs=true
• --showWarnings=[true/false]：显示警告。如 beeline --showWarnings=true
• --silent=[true/false]：减少显示的信息量。如 beeline --silent=true
• --truncateTable=[true/false]：是否在客户端截断表的列。
• --verbose=[true/false]：显示详细错误信息和调试信息。如 beeline --verbose=true
• -d <driver class>：使用一个驱动类。如 beeline -d driver_class
• -e <query>：使用一个查询语句。如 beeline -e "query_string"
• -f <file>：加载一个文件。如 beeline -f filepath；多个文件则用 -e file1 -e file2
• -n <username>：加载一个用户名。如 beeline -n valid_user
• -p <password>：加载一个密码。如 beeline -p valid_password
• -u <database URL>：加载一个 JDBC 连接字符串。如 beeline -u db_URL

参数说明：

这里比较相近的两个参数是 -i 与 -f，其中这两个参数的主要区别从字面上就可以很好的区分了：

• -f 执行 SQL 文件后就直接退出 Beeline 客户端，一般编写需要执行的 HQL。
• -i  执行 SQL 文件后进入 Beeline 客户端。一般为初始化的参数设置 hive 中的变量。

使用示例

beeline 的用法与 hive-cli 用法基本相同。

方式一：以 cloudera-scm 的身份 以jdbc 的方式连接远程的 hiveserver2

beeline -u jdbc:hive2://10.180.0.26:10000  -n cloudera-scm 

方式二：类似 hive-cli 的执行脚本功能

nohup beeline 
-u jdbc:hive2://10.180.0.26:10000  -n cloudera-scm 
--color=true --silent=false  
--hivevar p_date=${partitionDate} --hivevar f_date=${fileLocDate} 
-f hdfs_add_partition_dmp_clearlog.hql  >> $logdir/load_${curDate}.log

通过 jdbc 连接到 beeline 后，就可以对 hive 进行操作了。