星尘

7. Hive-1

发表于 更新于 分类于

介绍

描述

Hive 是一个数据仓库软件，可以使用 SQL 来促进对已经存在在分布式设备中的数据进行读，写和管理等操作。Hive 在使用时，需要对已经存储的数据进行结构的投影(映射)，Hive 提供了一个命令行和 JDBC 的方式，让用户可以连接到 Hive 。注意：Hive 只能分析结构化的数据，Hive 在 Hadoop 之上，使用hive的前提是先要安装 Hadoop

特点

  1. Hive 并不是一个关系型数据库

  2. 不是基于OLTP(在线事务处理。OLTP 设计的软件，侧重点在事务的处理，和在线访问。一般 RDMS 都是基于OLTP设计)设计的

  3. Hive无法做到实时查询，不支持行级别更新(update,delete)
  4. Hive 要分析的数据存储在 HDFS 中，Hive 为数据创建的表结构(schema)，存储在 RDMS 中
  5. Hive 基于OLAP(在线分析处理。OLAP 设计的软件，侧重点在数据的分析上，不追求分析的效率)设计的
  6. Hive 使用类 SQL，称为 HQL 对数据进行分析
  7. Hive 容易使用，可扩展，有弹性

安装

保证有 JAVA_HOME, HADOOP_HOME

将 bin 配置到 PATH 中，在环境变量中提供 HIVE_HOME

1
2
3
4
5
6
7
JAVA_HOME=/opt/module/jdk1.8.0_121
HADOOP_HOME=/opt/module/hadoop-2.7.2
HIVE_HOME=/opt/module/apache-hive-1.2.1-bin

PATH=$PATH:$JAVA_HOME/bin:$HADOOP_HOME/bin:$HADOOP_HOME/sbin:$HIVE_HOME/bin

export JAVA_HOME PATH HADOOP_HOME HIVE_HOME

简单操作

创建表和插入数据

创建一张表

1
create table person(name varchar(20), age int);

向表中插入数据。hive 执行 HQL 其实是将 HQL 翻译为 MR 程序在 Hadoop 上运行。

1
2
3
insert into person values("张三", 20);
insert into person values("李四", 20);
insert into person values("王五", 20);

查询插入的数据信息

1
select * from person;

在hdfs 的 /user/hive/warehouse/person 下，有很多文件，文件中存储的就是插入的数据信息，路径中的 person 代表表名。路径下的文件为存储的数据文件

自定义数据文件

创建表的时候如果没有指定数据的分割符，那么结构化的数据分割符默认是 ^ 。还可以在创建表的时候直接指定数据的分隔符，这里使用 \t

1
create table student1(id int, name string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

创建数据文件 test_file

1
2
3
4
5
1       zhangsan
2       lisi
3       wangwu
4       liqiang
5       zhaoliu

将数据文件上传到 hdfs 中 student 目录下 

1
hadoop fs -put test_file /user/hive/warehouse/student

执行查询就能看到查询结果

1
2
3
4
5
6
7
8
hive> select * from student;
OK
1       zhangsan
2       lisi
3       wangwu
4       liqiang
5       zhaoliu
Time taken: 1.33 seconds, Fetched: 5 row(s)

元数据管理

hive 默认的元数据管理是使用 derby，在 hive 中使用 derby 管理元数据的特点是只能允许一个 hive 的连接存在。在输入 hive 命令的目录下会生成一个 metastore_db 文件夹，该文件夹中存储的就是元数据。当切换目录执行 hive 命令时，又会在新目录下生成一个新的 metastore_db 文件夹，查看之前创建的表信息时会发现不存在（因为是新的 metastore_db 文件夹）

当已经有一个 hive 连接存在时，再次创建一个新的连接出现的错误信息

1
Caused by: ERROR XSDB6: Another instance of Derby may have already booted the database /opt/module/apache-hive-1.2.1-bin/metastore_db.

安装 MySQL

查看是否安装了 mysql，如果安装了需要将其卸载

1
2
3
[rexyan@hadoop10 apache-hive-1.2.1-bin]$ rpm -qa | grep mysql
mysql-libs-5.1.73-7.el6.x86_64
[rexyan@hadoop10 apache-hive-1.2.1-bin]$ sudo rpm -e --nodeps mysql-libs-5.1.73-7.el6.x86_64

上传解压得到 mysql 安装包

1
2
3
4
5
6
7
8
9
[rexyan@hadoop10 soft]$ cd mysql-libs
[rexyan@hadoop10 mysql-libs]$ ls -la
总用量 76060
drwxrwxr-x. 3 rexyan rexyan     4096 8月  24 23:17 .
drwxr-xr-x. 3 rexyan rexyan     4096 8月  24 23:13 ..
-rw-r--r--. 1 rexyan rexyan 18509960 8月  24 23:14 MySQL-client-5.6.24-1.el6.x86_64.rpm
drwxrwxr-x. 4 rexyan rexyan     4096 8月  24 23:14 mysql-connector-java-5.1.27
-rw-r--r--. 1 rexyan rexyan  3575135 8月  24 23:15 mysql-connector-java-5.1.27.tar.gz
-rw-r--r--. 1 rexyan rexyan 55782196 8月  24 23:18 MySQL-server-5.6.24-1.el6.x86_64.rpm

安装 mysql 服务器端

1
[rexyan@hadoop10 mysql-libs]$ sudo rpm -ivh MySQL-server-5.6.24-1.el6.x86_64.rpm

查看默认密码

1
2
[rexyan@hadoop10 mysql-libs]$ sudo cat /root/.mysql_secret
# The random password set for the root user at Tue Aug 25 14:05:01 2020 (local time): eHKx4R0nvli58uB3

启动 mysql 服务端程序

1
2
3
4
5
[rexyan@hadoop10 mysql-libs]$ sudo service mysql start 
Starting MySQL.....[确定]
[rexyan@hadoop10 mysql-libs]$ sudo service mysql status 
MySQL running (24737)[确定]
[rexyan@hadoop10 mysql-libs]$

安装 mysql 客户端程序

1
[rexyan@hadoop10 mysql-libs]$ sudo rpm -ivh MySQL-client-5.6.24-1.el6.x86_64.rpm

连接登录修改密码

1
2
[rexyan@hadoop10 mysql-libs]$ mysql -uroot -peHKx4R0nvli58uB3
mysql> SET PASSWORD=PASSWORD('123456');

允许远程登录

管理元数据

拷贝 mysql 驱动 jar 包到 hive lib 目录下

1
[rexyan@hadoop10 mysql-connector-java-5.1.27]$ cp mysql-connector-java-5.1.27-bin.jar /opt/module/apache-hive-1.2.1-bin/lib/

在 hive conf 目录下创建 hive-site.xml 文件，内容如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
	<property>
	  <name>javax.jdo.option.ConnectionURL</name>
	  <value>jdbc:mysql://hadoop10:3306/metastore?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>123456</value>
	  <description>password to use against metastore database</description>
	</property>
</configuration>

手动创建库 metastore，注意编码要使用 latin1。再次进入 hive 交互终端，就可以看到在 mysql 的 metastore 库中创建了很多表，hive 中创建的表的信息都存储在 tbls 表中，通过 db_id 和 dbs 表中的库进行外键约束，hive 中库的信息都存储在 dbs 表中，hive 中的字段信息存在 column_v2 表中，通过 CD_ID 和表的主键进行外键约束。

常见属性配置

Default 数据仓库的最原始位置是在hdfs上的 /user/hive/warehouse 路径下，如果某张表属于 default 数据库，直接在数据仓库目录下创建一个文件夹。修改 default 数据仓库原始位置可以在 hive-site.xml 文件中添加如下信息。其中 value 就是存储数据的地方，该路径为 hdfs 的路径。

1
2
3
4
5
<property>
	<name>hive.metastore.warehouse.dir</name>
	<value>/hive</value>
	<description>location of default database for the warehouse</description>
</property>

如果想在 hive 交互式命令行显示当前数据库，以及查询表的头信息，那么可以在 hive-site.xml 中添加以下配置

1
2
3
4
5
6
7
8
9
<property>
	<name>hive.cli.print.header</name>
	<value>true</value>
</property>

<property>
	<name>hive.cli.print.current.db</name>
	<value>true</value>
</property>

hive 的日志默认存储在 /tmp/用户名/hive.log 中，如果要修改此目录，那么应该复制一份 hive-exec-log4j.properties.template 文件，将新文件名称修改为 hive-log4j.properties。并将其中的 hive.log.dir 值修改为存储日志的地址，重新进入交互式命令行即可。

1
hive.log.dir=/opt/module/apache-hive-1.2.1-bin/logs

交互式命令参数

在使用 hive 进入交互式操作的时候，可以在 hive 后面跟上一些参数

1
2
3
4
5
6
7
8
-d   定义一个变量，在hive启动后，可以使用${变量名}引用变量
--database   指定使用哪个库
-e   后跟一条引号引起来的sql，执行完返回结果后退出cli
-f   执行一个文件中的sql语句，执行完返回结果后退出cli
--hiveconf   在cli运行之前，定义一对属性，hive在运行时，先读取 hadoop的全部8个配置文件，读取之后，再读取  hive-default.xml再读取hive-site.xml， 如果使用--hiveconf，可以定义一组属性，这个属性会覆盖之前读到的参数的值
--hivevar   作用和-d是一致的，定义一个变量
-i   和 -e 类似，区别在于执行完返回结果后不退出 cli
-S   不打印和结果无关的信息

hive 中执行 hdfs 和 linux 命令

1
2
!ls /tmp;  # 执行 linux 命令
dfs -ls /user;   # 执行 hdfs 命令

数据类型

基本数据类型

Hive数据类型 Java数据类型 长度
TINYINT byte 1byte有符号整数
SMALINT short 2byte有符号整数
INT int 4byte有符号整数
BIGINT long 8byte有符号整数
BOOLEAN boolean 布尔类型，true或者false
FLOAT float 单精度浮点数
DOUBLE double 双精度浮点数
STRING string 字符系列。可以指定字符集。可以使用单引号或者双引号。
TIMESTAMP 时间类型
BINARY 字节数组

集合数据类型

数据类型 描述
struct 和c语言中的struct类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。
map MAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取最后一个元素
array 数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用。

创表示例

例如有如下两条数据

1
2
songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing

数据期待的 json 格式如下 ( Map和 Struct 的区别: Struct 中属性名是不变的, Map 中 key 可以变化的)

1
2
3
4
5
6
7
8
9
10
11
12
{
    "name": "songsong",
    "friends": ["bingbing" , "lili"] ,       //列表Array, 
    "children": {                      //键值Map,
        "xiao song": 18 ,
        "xiaoxiao song": 19
    },
    "address": {                      //结构Struct,
        "street": "hui long guan" ,
        "city": "beijing" 
    }
}

根据上面需求，建表语句如下

1
2
3
4
5
6
7
8
9
create table people(
name string,   -- name 字段，类型为 string
friends array<string>,   -- friends 字段，类型为 array，array 中存储 string 类型数据
children map<string,int>,  -- children 字段，类型为 map，key 为 string，value 为 int 类型数据
address struct<street:string,city:string>)  -- address 字段，类型为 struct。有两个属性 street city
row format delimited fields terminated by ','  -- 指定数据字段之间使用 “,” 进行分割
collection items terminated by '_'  -- 字段中得数据使用 '_' 进行分割
map keys terminated by ':'  -- map 中的 key 和 value 使用 ':' 进行分割
lines terminated by '\n';  -- 每行数据使用换行进行分割

将数据上传至 hdfs 指定目录下

1
[rexyan@hadoop10 test_file]$ hadoop fs -put create_table_test_data_file /hive/people

在 hive 中查询，得到如下结果

1
2
3
4
5
6
hive (default)> select * from people;
OK
people.name     people.friends  people.children people.address
songsong        ["bingbing","lili"]     {"xiao song":18,"xiaoxiao song":19}     {"street":"hui long guan","city":"beijing"}
yangyang        ["caicai","susu"]       {"xiao yang":18,"xiaoxiao yang":19}     {"street":"chao yang","city":"beijing"}
Time taken: 0.749 seconds, Fetched: 2 row(s)

可以看到 friends 列的值是 array 类型，children 是以 map 类型，address 是 struct 类型。

获取 friends 中的第一个值

1
2
3
4
5
6
hive (default)> select friends[0] from people;
OK
_c0
bingbing
caicai
Time taken: 0.166 seconds, Fetched: 2 row(s)

获取 children 中指定 key 的值

1
2
3
4
5
6
hive (default)> select children["xiao song"] from people;
OK
_c0
18
NULL
Time taken: 0.186 seconds, Fetched: 2 row(s)

获取 address 中某个属性的值

1
2
3
4
5
6
hive (default)> select address.city from people;
OK
city
beijing
beijing
Time taken: 0.163 seconds, Fetched: 2 row(s)

库操作

增

1
2
3
4
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name  // 库名
[COMMENT database_comment]  // 库的注释说明
[LOCATION hdfs_path]        // 库在hdfs上的路径
[WITH DBPROPERTIES (property_name=property_value, ...)]; // 库的属性

例如创建一个库 mydb2，指定 hdfs 路径位置，并且设置库的属性值

1
create database  if not exists mydb2 comment 'this is my db' location 'hdfs://hadoop101:9000/mydb2' with dbproperties('ownner'='jack','tel'='12345','department'='IT');

删

1
2
drop database 库名 // 只能删除空库
drop database 库名 cascade // 删除非空库

改

1
2
3
use 库名  // 切换使用的库		
dbproperties  // 修改库的属性值
例如：alter database mydb2 set dbproperties('ownner'='tom','empid'='10001'); 同名的属性值会覆盖，之前没有的属性会新增

查

1
2
3
4
show databases: 查看当前所有的库
show tables in database: 查看库中所有的表
desc database 库名： 查看库的描述信息
desc database extended 库名： 查看库的详细描述信息

表操作

增

1
2
3
4
5
6
7
8
9
10
11
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]   //表中的数据要以哪种文件格式来存储，默认为TEXTFILE（文本文件）
[LOCATION hdfs_path]  //表在hdfs上的位置

建表时，如果不带 EXTERNAL ，那么创建的表是一个内部表或者叫做管理表。如果带有 EXTERNAL，那么创建的表是一个外部表。外部表和内部表的区别是内部表(管理表)在执行删除操作时，会将表的元数据(schema, 存在 MySQL 中的)和表位置的数据一起删除，外部表在执行删除表操作时，只删除表的元数据(schema， 存在 MySQL 中的)。在企业中，创建的都是外部表，因为在hive中表是廉价的，数据是珍贵的。

内部表和外部表的相互转换：
将表改为外部表 alter table p1 set tblproperties('EXTERNAL'='TRUE');
将表改为管理表 alter table p1 set tblproperties('EXTERNAL'='FALSE');

删

1
2
drop table 表名  // 删除表
truncate table 表名 // 清空表数据，只能情况管理表

改

1
2
alter table p1 set tblproperties(属性名=属性值);  // 修改表的属性
alter table change 旧列名 新列名 新列类型;  // 修改列的名称和类型

查

1
2
desc  表名  // 查看表的描述
desc formatted 表名  // 查看表的详细描述