0%

大数据存储框架之Hive(1) 概述

之前有写到HBase,那是老夫在开发过程中实际使用的一个存储数据库,HBase和Hive同样作为大数据存储中最优秀的两个存储框架,都有着彼此的优势,HBase更适合实时,Hive更适合离线。这里呢,就先简单的介绍一下HIve的基础架构以及Hive的一些基本安装步骤。

认识我们的主角Hive

什么是Hive

Hive:由Facebook开源用于解决海量结构化日志的数据统计。
Hive是基于Hadoop的一个数据仓库工具,可以将结构化的数据文件映射为一张表,并提供类SQL查询功能(HQL)。
本质是:将HQL转化成MapReduce程序
如图所示:
sql-mapreduce.png

1) Hive处理的数据存储在HDFS
2) Hive分析数据底层的实现是MapReduce
3) 执行程序运行在Yarn上

Hive的优缺点

优点

1) 操作接口采用类SQL语法,提供快速开发的能力(简单、容易上手)。
2) 避免了去写MapReduce,减少开发人员的学习成本。
3) Hive的执行延迟比较高,因此Hive常用于数据分析,对实时性要求不高的场合。
4) Hive优势在于处理大数据,对于处理小数据没有优势,因为Hive的执行延迟比较高。
5) Hive支持用户自定义函数,用户可以根据自己的需求来实现自己的函数。

缺点

  1. Hive的HQL表达能力有限
    (1)迭代式算法无法表达
    (2)数据挖掘方面不擅长
  2. Hive的效率比较低
    (1)Hive自动生成的MapReduce作业,通常情况下不够智能化
    (2)Hive调优比较困难,粒度较粗

    Hive架构

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

    Hive和数据库比较

    由于 Hive 采用了类似SQL 的查询语言 HQL(Hive Query Language),因此很容易将 Hive 理解为数据库。其实从结构上来看,Hive 和数据库除了拥有类似的查询语言,再无类似之处。本文将从多个方面来阐述 Hive 和数据库的差异。数据库可以用在 Online 的应用中,但是Hive 是为数据仓库而设计的,清楚这一点,有助于从应用角度理解 Hive 的特性。

    查询语言

    由于SQL被广泛的应用在数据仓库中,因此,专门针对Hive的特性设计了类SQL的查询语言HQL。熟悉SQL开发的开发者可以很方便的使用Hive进行开发。

    数据存储位置

    Hive 是建立在 Hadoop 之上的,所有 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进行并行计算,因此可以支持很大规模的数据;对应的,数据库可以支持的数据规模较小。

Hive安装

安装过程很简单,主要修改几个配置:

1
mv hive-env.sh.template hive-env.sh

配置hive-env.sh

1
2
export HADOOP_HOME=/opt/module/hadoop-2.7.2
export HIVE_CONF_DIR=/opt/module/apache-hive-1.2.1/conf

随后,我们需要保证我们的HDFS上创建 /tmp 和 /user/hive/warehouse 两个目录,并修改他们的同组权限可写

1
2
3
4
5
bin/hadoop fs -mkdir /tmp
bin/hadoop fs -mkdir -p /user/hive/warehouse

bin/hadoop fs -chmod g+w /tmp
bin/hadoop fs -chmod g+w /user/hive/warehouse

随后我们启动我们的Hive

1
bin/hive

剩下的操作基本上和MySQL操作一致了,就不做太多的赘述了。
这里,当我们再打开一个hive窗口的时候,会产生java.sql.SQLException异常:我么试一下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Exception in thread "main" java.lang.RuntimeException: java.lang.RuntimeException:
Unable to instantiate
org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient
at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:522)
at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:677)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:621)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
Caused by: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient
at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1523)
at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.<init>(RetryingMetaStoreClient.java:86)
at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:132)
at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:104)
at org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:3005)
at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:3024)
at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:503)
... 8 more

原因是,Metastore默认存储在自带的derby数据库中,推荐使用MySQL存储Metastore;

Hive元数据配置到MySQL

首先,我们将数据库驱动的.jar包,放到hive目录下的lib目录下,然后我们在配置Metastore到MySQL
在/opt/module/apache-hive-1.2.1/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://hadoop101: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>000000</value>
<description>password to use against metastore database</description>
</property>
</configuration>
1
2
3
4
5
bin/hiveserver2
bin/beeline
beeline> !connect jdbc:hive2://hadoop101:10000
Enter username for jdbc:hive2://hadoop101:10000: corp
Enter password for jdbc:hive2://hadoop101:10000: # 这里直接回车就好了

然后,我们就直接进去了。剩下额度操作就和MySQL一致了。

Hive常见的属性配置

Hive数据仓库位置配置

1)Default数据仓库的最原始位置是在hdfs上的:/user/hive/warehouse路径下。
2)在仓库目录下,没有对默认的数据库default创建文件夹。如果某张表属于default数据库,直接在数据仓库目录下创建一个文件夹。
3)修改default数据仓库原始位置(将hive-default.xml.template如下配置信息拷贝到hive-site.xml文件中)。

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

配置同组用户有执行权限

1
bin/hdfs dfs -chmod g+w /user/hive/warehouse

查询后信息现实配置

1)在hive-site.xml文件中添加如下配置信息,就可以实现显示当前数据库,以及查询表的头信息配置。

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

Hive运行日志信息配置

在 /opt/module/apache-hive-1.2.1/conf 目录下,修改hive-log4j.properties,修改如下:

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

参数配置方式

查看当前所有的配置信息

1
hive>set;

参数的配置三种方式

(1) 配置文件方式
默认配置文件:hive-default.xml
用户自定义配置文件:hive-site.xml
注意:用户自定义配置会覆盖默认配置。另外,Hive也会读入Hadoop的配置,因为Hive是作为Hadoop的客户端启动的,Hive的配置会覆盖Hadoop的配置。配置文件的设定对本机启动的所有Hive进程都有效。
(2) 命令行参数设置
启动Hive时,可以在命令行添加-hiveconf param=value来设定参数。进队当前Hive有效。

1
2
3
4
bin/hive -hiveconf mapred.reduce.tasks=10;
# 进队当前hive有效
# 查看当前配置
hive (default)> set mapred.reduce.tasks;

(3) 参数声明方式
可以在HQL中使用SET关键字设定参数;

1
2
3
4
hive (default)> set mapred.reduce.tasks=100;
# 进队当前hive有效
# 查看当前配置
hive (default)> set mapred.reduce.tasks;

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

Hive数据类型

基本数据类型

Hive数据类型Java数据类型长度例子
TINYINTbyte1byte有符号整数20
SMALINTshort2byte有符号整数20
INTint4byte有符号整数20
BIGINTlong8byte有符号整数20
BOOLEANboolean布尔类型,true或者falseTRUE FALSE
FLOATfloat单精度浮点数3.14159
DOUBLEdouble双精度浮点数3.14159
STRINGstring字符系列。可以指定字符集。可以使用单引号或者双引号。‘now is the time’ “for all good men”
TIMESTAMP时间类型
BINARY字节数组
对于Hive的String类型相当于数据库的varchar类型,该类型是一个可变的字符串,不过它不能声明其中最多能存储多少个字符,理论上它可以存储2GB的字符数。

集合数据类型

数据类型描述语法示例
STRUCT和c语言中的struct类似,都可以通过“点”符号访问元素内容。例如,如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。struct()
MAPMAP是一组键-值对元组集合,使用数组表示法可以访问数据。例如,如果某个列的数据类型是MAP,其中键->值对是’first’->’John’和’last’->’Doe’,那么可以通过字段名[‘last’]获取最后一个元素map()
ARRAY数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素,每个数组元素都有一个编号,编号从零开始。例如,数组值为[‘John’, ‘Doe’],那么第2个元素可以通过数组名[1]进行引用。Array()
Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似,而STRUCT与C语言中的Struct类似,它封装了一个命名字段集合,复杂数据类型允许任意层次的嵌套。

案例实操

(1) 假设有表如下一行,我们使用JSON格式来存储其数据结构,在Hive下访问的格式为:

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"
}
}

(2) 基于上述数据结构,我们在Hive里创建对应的表,并导入数据。创建本地测试文件test.txt

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

注意: MAP,STRUCT和ARRAY里的元素间关系都可以用同一个字符表示,这里用“_”。
(3) Hive上创建测试表test

1
2
3
4
5
6
7
8
9
10
create table test(
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string>
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';

字段解释
row format delimited fields terminated by ‘,’ – 列分隔符
collection items terminated by ‘_’ –MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ‘:’ – MAP中的key与value的分隔符
lines terminated by ‘\n’; – 行分隔符

(4) 导入数据

1
load data local inpath ‘/opt/module/datas/test.txt’into table test

(5) 访问三种集合列里的数据,以下分别是ARRAY,MAP,STRUCT的访问方式:

1
select friends[1],children['xiao song'],address.city from test where name="songsong";

类型转化

Hive的原子数据类型是可以进行隐式转换的,类似于Java的类型转换,例如某表达式使用INT类型,TINYINT会自动转换为INT类型,但是Hive不会进行反向转化,例如,某表达式使用TINYINT类型,INT不会自动转换为TINYINT类型,它会返回错误,除非使用CAST操作。

  1. 隐式类型转换规则如下
    (1)任何整数类型都可以隐式地转换为一个范围更广的类型,如TINYINT可以转换成INT,INT可以转换成BIGINT。
    (2)所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。
    (3)TINYINT、SMALLINT、INT都可以转换为FLOAT。
    (4)BOOLEAN类型不可以转换为任何其它的类型。
  2. 可以使用CAST操作显示进行数据类型转换
    例如CAST(‘1’ AS INT)将把字符串’1’ 转换成整数1;如果强制类型转换失败,如执行CAST(‘X’ AS INT),表达式返回空值 NULL。

    DDL数据定义

    创建数据库

  3. 创建一个数据库,数据库在HDFS上的默认存储路径是/user/hive/warehouse/*.db。
    1
    hive (default)> create database db_hive;
  4. 避免要创建的数据库已经存在错误,增加if not exists判断。(标准写法)
    1
    hive (default)> create database if not exists db_hive;
  5. 创建一个数据库,指定数据库在HDFS上存放的位置
    1
    hive (default)> create database db_hive2 location '/db_hive2.db';

    查询数据库

  6. 显示数据库
    1
    2
    3
    4
    # 显示数据库
    show databases;
    # 过滤显示查询的数据库
    show databases like 'db_hive*';
  7. 查看数据库详情
    1
    2
    3
    4
    # 显示数据库信息
    desc database db_hive;
    # 显示数据库详细信息,extended
    desc database extended db_hive;
  8. 切换数据库
    1
    use db_hive
  9. 修改数据库
    用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值,来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的,包括数据库名和数据库所在的目录位置。
    1
    alter database db_hive set dbproperties('createtime'='20170830');
  10. 删除数据库
    (1) 删除空的数据库
    1
    drop database if exists db_hive ;
    (2) 删除不为空的数据库
    1
    drop database if exists db_hive cascade;
  11. 创建表
    1
    2
    3
    4
    5
    6
    7
    8
    9
    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)COMMENT:为表和列添加注释。
    (4)PARTITIONED BY创建分区表
    (5)CLUSTERED BY创建分桶表
    (6)SORTED BY不常用
    (7)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的简称,目的是用于序列化和反序列化。
    (8)STORED AS指定存储文件类型
    常用的存储文件类型:SEQUENCEFILE(二进制序列文件)、TEXTFILE(文本)、RCFILE(列式存储格式文件)
    如果文件数据是纯文本,可以使用STORED AS TEXTFILE。如果数据需要压缩,使用 STORED AS SEQUENCEFILE。
    (9)LOCATION :指定表在HDFS上的存储位置。
    (10)LIKE允许用户复制现有的表结构,但是不复制数据。

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

默认创建的表都是所谓的管理表,有时也被称为内部表。因为这种表,Hive会(或多或少地)控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如,/user/hive/warehouse)所定义的目录的子目录下。当我们删除一个管理表时,Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。
因为表是外部表,所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据,不过描述表的元数据信息会被删除掉。

内部表和外部表的转换

1
2
3
4
# 内部表-> 外部表
alter table student2 set tblproperties('EXTERNAL'='TRUE');
# 外部表-> 内部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');

注意:(‘EXTERNAL’=’TRUE’)和(‘EXTERNAL’=’FALSE’)为固定写法,区分大小写!

分区表

分区表实际上就是对应一个HDFS文件系统上的独立的文件夹,该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录,把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区,这样的查询效率会提高很多。

分区表的创建以及数据加载

1
2
3
4
5
6
7
create table dept_partition(
deptno int,
dname string,
loc string
)
partitioned by (month string)
row format delimited fields terminated by '\t';
1
load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201709');

增加分区,删除分区,查看分区表分区数,以及分区表结构

1
2
3
4
5
6
7
8
9
10
11
12
-- 创建单个分区
alter table dept_partition add partition(month='201706') ;
-- 同时创建多个分区
alter table dept_partition add partition(month='201705') partition(month='201704');
-- 删除分区
alter table dept_partition drop partition (month='201704');
-- 删除多个分区
alter table dept_partition drop partition (month='201705'), partition (month='201706');
-- 查看分区表分区数
show partitions dept_partition;
-- 查看分区表结构
desc formatted dept_partition;

分区表注意事项

  1. 创建二级分区表
    1
    2
    3
    4
    5
    6
    7
    create table dept_partition2(
    deptno int,
    dname string,
    loc string
    )
    partitioned by (month string, day string)
    row format delimited fields terminated by '\t';
  2. 正常的加载数据
    (1) 加载数据到二级分区表中
    1
    load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition2 partition(month='201709', day='13');
    (2) 查询分区数据
    1
    select * from dept_partition2 where month='201709' and day='13';
  3. 把数据直接上传到分区目录上,让分区表和数据产生关联的三种方式
    (1) 方式一: 上传数据后修复
    1
    2
    3
    4
    5
    6
    7
    8
    9
    -- 上传数据
    dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=12;
    hive (default)> dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=201709/day=12;
    -- 查询数据(查询不到刚上传的数据)
    select * from dept_partition2 where month='201709' and day='12';
    -- 执行修复命令
    msck repair table dept_partition2;
    -- 再次查询数据
    select * from dept_partition2 where month='201709' and day='12';
    (2) 方式二:上传数据后添加分区
    1
    2
    3
    4
    5
    6
    7
    -- 上传数据
    dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=11;
    dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=201709/day=11;
    alter table dept_partition2 add partition(month='201709',
    day='11');
    -- 查询数据
    select * from dept_partition2 where month='201709' and day='11';
    (3) 方式三:创建文件夹后load数据到分区
    1
    2
    3
    4
    5
    6
    -- 创建目录
    dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=10;
    -- 上传数据
    load data local inpath '/opt/module/datas/dept.txt' into table dept_partition2 partition(month='201709',day='10');
    -- 查询数据
    hive (default)> select * from dept_partition2 where month='201709' and day='10';

    修改表

  4. 重命名表
    1
    ALTER TABLE table_name RENAME TO new_table_name
  5. 增加/修改/删除表分区(参考上述的分区表操作)
  6. 增加/修改/替换列信息
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    -- 更新列
    ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
    -- 增加和替换列
    ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
    -- 添加列
    alter table dept_partition add columns(deptdesc string);
    -- 更新列
    alter table dept_partition change column deptdesc desc int;
    -- 替换列
    alter table dept_partition replace columns(deptno string, dname string, loc string);
    -- 删除表
    drop table dept_partition;

    DML数据操作(这里基本上和SQL保持一致)

    数据导入

    向表中装载数据(Load)

    1
    load data [local] inpath '/opt/module/datas/student.txt' overwrite | into table student [partition (partcol1=val1,…)];
    (1)load data:表示加载数据
    (2)local:表示从本地加载数据到hive表;否则从HDFS加载数据到hive表
    (3)inpath:表示加载数据的路径
    (4)overwrite:表示覆盖表中已有数据,否则表示追加
    (5)into table:表示加载到哪张表
    (6)student:表示具体的表
    (7)partition:表示上传到指定分区

    通过查询语句向表中插入数据(Insert)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    insert into table  student 
    partition(month='201709')
    values(1,'wangwu');

    insert overwrite table student
    partition(month='201708')
    select id, name
    from student
    where month='201709';

    查询语句中创建表并加载数据(As Select)(这部分看我前面表的创建)

创建表时通过Location指定加载数据路径

1
2
3
4
5
6
7
8
9
10
11
-- 创建表,并指定在hdfs上的位置
create table if not exists student5(
id int,
name string
)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student5';
-- 上传数据到hdfs上
dfs -put /opt/module/datas/student.txt /user/hive/warehouse/student5 ;
-- 查询数据
select * from student5;

Import数据到指定Hive表中

注意:先用export导出后,再将数据导入。

1
import table student2 partition(month='201709') from '/user/hive/warehouse/export/student';

数据导出

Insert导出

1
2
3
4
5
6
-- 将查询的结果导出到本地
insert overwrite local directory '/opt/module/datas/export/student' select * from student;
-- 将查询的结果格式化导出到本地
insert overwrite local directory '/opt/module/datas/export/student1' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student;
-- 将查询的结果导出到HDFS上(没有local)
insert overwrite directory '/user/atguigu/student2' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student;

Hadoop命令导出到本地

1
dfs -get /user/hive/warehouse/student/month=201709/000000_0 /opt/module/datas/export/student3.txt;

Hive Shell命令导出

1
bin/hive -e 'select * from default.student;' > /opt/module/datas/export/student4.txt;

Export 导出到HDFS上

1
export table default.student to '/user/hive/warehouse/export/student';

Sqoop导出(这个在Sqoop里面有提到,可以参考我的另一篇博客,还没写)

清除表中的数据(Truncate只能删除管理表,不能删除外部表中数据)

1
truncate table student;

查询(HQL的查询与SQL基本保持一致,这里着重不讲普通的)

1
2
3
4
5
6
7
8
9
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]

全表查询/特定列查询/列别名(AS)/limit n

(1)SQL 语言大小写不敏感。
(2)SQL 可以写在一行或者多行
(3)关键字不能被缩写也不能分行
(4)各子句一般要分行写。
(5)使用缩进提高语句的可读性。

算术运算符

运算符描述
A+BA和B 相加
A-BA减去B
A*BA和B 相乘
A/BA除以B
A%BA对B取余
A&BA和B按位取与
A|BA和B按位取或
A^BA和B按位取异或
~AA按位取反
## 常用的函数(COUNT()/MAX()/MIN()/SUM()/AVG())

Where语句

  1. 使用WHERE子句,将不满足条件的行过滤掉
  2. WHERE子句紧随FROM子句

    比较运算符(Between/In/ Is Null)

  3. 下面表中描述了谓词操作符,这些操作符同样可以用于JOIN…ON和HAVING语句中。
操作符支持的数据类型描述
A=B基本数据类型如果A等于B则返回TRUE,反之返回FALSE
A<=>B基本数据类型如果A和B都为NULL,则返回TRUE,其他的和等号(=)操作符的结果一致,如果任一为NULL则结果为NULL
A<>B, A!=B基本数据类型A或者B为NULL则返回NULL;如果A不等于B,则返回TRUE,反之返回FALSE
A<B基本数据类型A或者B为NULL,则返回NULL;如果A小于B,则返回TRUE,反之返回FALSE
A<=B基本数据类型A或者B为NULL,则返回NULL;如果A小于等于B,则返回TRUE,反之返回FALSE
A>B基本数据类型A或者B为NULL,则返回NULL;如果A大于B,则返回TRUE,反之返回FALSE
A>=B基本数据类型A或者B为NULL,则返回NULL;如果A大于等于B,则返回TRUE,反之返回FALSE
A [NOT] BETWEEN B AND C基本数据类型如果A,B或者C任一为NULL,则结果为NULL。如果A的值大于等于B而且小于或等于C,则结果为TRUE,反之为FALSE。如果使用NOT关键字则可达到相反的效果。
A IS NULL所有数据类型如果A等于NULL,则返回TRUE,反之返回FALSE
A IS NOT NULL所有数据类型如果A不等于NULL,则返回TRUE,反之返回FALSE
IN(数值1, 数值2)所有数据类型使用 IN运算显示列表中的值
A [NOT] LIKE BSTRING 类型B是一个SQL下的简单正则表达式,如果A与其匹配的话,则返回TRUE;反之返回FALSE。B的表达式说明如下:‘x%’表示A必须以字母‘x’开头,‘%x’表示A必须以字母’x’结尾,而‘%x%’表示A包含有字母’x’,可以位于开头,结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。
A RLIKE B, A REGEXP BSTRING 类型B是一个正则表达式,如果A与其匹配,则返回TRUE;反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的,因为正则也依据其中的规则。例如,正则表达式必须和整个字符串A相匹配,而不是只需与其字符串匹配。

Like和RLike

1)使用LIKE运算选择类似的值
2)选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
3)RLIKE子句是Hive中这个功能的一个扩展,其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。

逻辑运算符(And/Or/Not)

操作符含义
AND逻辑并
OR逻辑或
NOT逻辑否

分组

Group By语句

GROUP BY语句通常会和聚合函数一起使用,按照一个或者多个列队结果进行分组,然后对每个组执行聚合操作。

Hiving语句

(1)where针对表中的列发挥作用,查询数据;having针对查询结果中的列发挥作用,筛选数据。
(2)where后面不能写分组函数,而having后面可以使用分组函数。
(3)having只用于group by分组统计语句。

1
2
3
4
-- 求每个部门的平均工资
select deptno, avg(sal) from emp group by deptno;
-- 求每个部门的平均薪水大于2000的部门
select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

Join语句

等值Join

Hive支持通常的SQL JOIN语句,但是只支持等值连接,不支持非等值连接。

(1)使用别名可以简化查询。
(2)使用表名前缀可以提高执行效率。

1
2
-- 根据员工表和部门表中的部门编号相等,查询员工编号、员工名称和部门名称;
select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;

内/左外/右外/满外/多表连接

注意:连接 n个表,至少需要n-1个连接条件。例如:连接三个表,至少需要两个连接条件。
SQL神经大条(1)JOIN操作的那些事

笛卡尔积

笛卡尔集会在下面条件下产生
(1)省略连接条件
(2)连接条件无效
(3)所有表中的所有行互相连接

连接谓词中不支持or

排序

Order By(一个reduce)

  1. 使用 ORDER BY 子句排序

    ASC(ascend): 升序(默认)

DESC(descend): 降序
2. ORDER BY 子句在SELECT语句的结尾

1
2
3
4
5
6
-- 降序
select * from emp order by sal desc;
-- 别名
select ename, sal*2 twosal from emp order by twosal;
-- 多个列排序
select ename, deptno, sal from emp order by deptno, sal ;

Sort By(每个MapReduce内部排序)

Sort By:每个Reducer内部进行排序,对全局结果集来说不是排序。

Distribute By(分区排序)

Distribute By:类似MR中partition,进行分区,结合sort by使用。
注意,Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
对于distribute by进行测试,一定要分配多reduce进行处理,否则无法看到distribute by的效果。

1
select * from emp distribute by deptno sort by empno desc;

Cluster By

当distribute by和sorts by字段相同时,可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序,不能指定排序规则为ASC或者DESC。

1
2
3
select * from emp cluster by deptno;
-- 等价于
select * from emp distribute by deptno sort by deptno;

注意:按照部门编号分区,不一定就是固定死的数值,可以是20号和30号部门分到一个分区里面去。

分桶及抽样查询

分通表数据存储

分区针对的是数据的存储路径;分桶针对的是数据文件。
分区提供一个隔离数据和优化查询的便利方式。不过,并非所有的数据集都可形成合理的分区,特别是之前所提到过的要确定合适的划分大小这个疑虑。
分桶是将数据集分解成更容易管理的若干部分的另一个技术。

1
2
3
4
5
6
7
8
9
10
11
-- 创建一个分通表
create table stu_buck(
id int,
name string
)
clustered by(id)
into 4 buckets
row format delimited fields terminated by '\t';

-- 导入数据
load data local inpath '/opt/datas/hive/student.txt' into table stu_buck;

然后我们查看数据后,发现并没有出现我们期待的分桶结果:
hive-buck
发现并没有分成4个桶。是什么原因呢?
创建分通表时,数据通过子查询的方式导入

1
2
3
4
5
6
7
8
9
10
-- 先建立一个普通的stu表
create table stu(id int, name string) row format delimited fields terminated by '\t';
-- 向普通的Stu表里面导入数据
load data local inpath '/opt/datas/student.txt' into table stu;
-- 清空stu_buck表中数据
truncate table stu_buck;
select * from stu_buck;
-- 导入数据到分桶表,通过子查询的方式
insert into table stu_buck
select id, name from stu;

结果如下:
hive-buck_2

1
2
3
4
5
6
7
8
-- 清空stu_buck表中数据
truncate table stu_buck;
select * from stu_buck;
-- 设置两个属性
set hive.enforce.bucketing=true;
set mapreduce.job.reduces=-1;
-- 导入数据到分桶表,通过子查询的方式
insert into table stu_buck select id, name from stu;

hive-buck_3
可以看到我们的分桶可行了。

1
2
-- 查询数据
select * from stu_buck;

分桶抽样查询

对于非常大的数据集,有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive可以通过对表进行抽样来满足这个需求。

1
select * from stu_buck tablesample(bucket 1 out of 4 on id);

注:tablesample是抽样语句,语法:TABLESAMPLE(BUCKET x OUT OF y) 。
y必须是table总bucket数的倍数或者因子。hive根据y的大小,决定抽样的比例。例如,table总共分了4份,当y=2时,抽取(4/2=)2个bucket的数据,当y=8时,抽取(4/8=)1/2个bucket的数据。
x表示从哪个bucket开始抽取,如果需要取多个分区,以后的分区号为当前分区号加上y。例如,table总bucket数为4,tablesample(bucket 1 out of 2),表示总共抽取(4/2=)2个bucket的数据,抽取第1(x)个和第3(x+y)个bucket的数据。
注意:x的值必须小于等于y的值,否则

1
FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck

其他常用的查询函数

NVL(空字段赋值)

NVL:给值为NULL的数据赋值,它的格式是NVL( string1, replace_with)。它的功能是如果string1为NULL,则NVL函数返回replace_with的值,否则返回string1的值,如果两个参数都为NULL ,则返回NULL。

1
2
3
4
-- 如果员工的comm为NULL,则用-1代替
select nvl(comm,-1) from emp;
-- 如果员工的comm为NULL,则用 mgr 列的数据代替
select nvl(comm,mgr) from emp;

CASE WHEN(条件,相当于if else)

1
2
3
4
5
6
7
8
select 
dept_id,
sum(case sex when '男' then 1 else 0 end) male_count,
sum(case sex when '女' then 1 else 0 end) female_count
from
emp_sex
group by
dept_id;

行转列

CONCAT(string A/col, string B/col…): 返回输入字符串连接后的结果,支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,…): 它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL,返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
COLLECT_SET(col): 函数只接受基本数据类型,它的主要作用是将某字段的值进行去重汇总,产生array类型字段。
数据准备
| name | constellation | blood_type |
| —— | ————- | ———- |
| 孙悟空 | 白羊座 | A |
| 大海 | 射手座 | A |
| 宋宋 | 白羊座 | B |
| 猪八戒 | 白羊座 | A |
| 凤姐 | 射手座 | A |

需求
把星座和血型一样的人归类到一起。

1
2
3
射手座,A            大海|凤姐
白羊座,A 孙悟空|猪八戒
白羊座,B 宋宋
1
2
3
4
5
6
7
8
9
10
11
select
t1.base,
concat_ws('|', collect_set(t1.name)) name
from
(select
name,
concat(constellation, ",", blood_type) base
from
person_info) t1
group by
t1.base;

列转行

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

moviecategory
《疑犯追踪》悬疑,动作,科幻,剧情
《Lie to me》悬疑,警匪,动作,心理,剧情
《战狼2》战争,动作,灾难
需求
将电影分类中的数组数据展开。结果如下:
1
2
3
4
5
6
7
8
9
10
11
12
《疑犯追踪》      悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 心理
《Lie to me》 剧情
《战狼2》 战争
《战狼2》 动作
《战狼2》 灾难
1
2
3
4
5
select
movie,
category_name
from
movie_info lateral view explode(category) table_tmp as category_name;
## 窗口函数
OVER(): 指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化
CURRENT ROW: 当前行
n PRECEDING: 往前n行数据
n FOLLOWING: 往后n行数据
UNBOUNDED: 起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED FOLLOWING表示到后面的终点
LAG(col,n): 往前第n行数据
LEAD(col,n): 往后第n行数据
NTILE(n):把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,NTILE返回此行所属的组的编号。
注意:n必须为int类型。
数据准备
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# name,orderdate,cost

jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94
需求
(1)查询在2017年4月份购买过的顾客及总人数
(2)查询顾客的购买明细及月购买总额
(3)上述的场景,要将cost按照日期进行累加
(4)查询顾客上次的购买时间
(5)查询前20%时间的订单信息
1
2
3
4
5
6
7
-- 创建表,导入数据
create table business(
name string,
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath "/opt/datas/hive/business.txt" into table business;
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
28
29
30
31
32
33
34
35
36
-- 查询在2017年4月份购买过的顾客及总人数
select name,count(*)
over ()
from business
where substring(orderdate,1,7) = '2017-04'
group by name;
-- 查询顾客的购买明细及月购买总额
select name,orderdate,cost,sum(cost)
over(partition by month(orderdate))
from business;
-- 上述的场景,要将cost按照日期进行累加
select name,orderdate,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,--按name分组,组内数据相加
sum(cost) over(partition by name order by orderdate) as sample3,--按name分组,组内数据累加
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
from business;
-- 查询顾客上次的购买时间
select
name,
orderdate,
cost,
lag(orderdate,1,'1900-01-01')
over(partition by name order by orderdate ) as time1,
lag(orderdate,2)
over (partition by name order by orderdate) as time2
from business;
-- 查询前20%时间的订单信息
select * from (
select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
from business
) t
where sorted = 1;
## Rank
RANK() 排序相同时会重复,总数不会变
DENSE_RANK() 排序相同时会重复,总数会减少
ROW_NUMBER() 会根据顺序计算

数据准备

namesubjectscore
孙悟空语文87
孙悟空数学95
孙悟空英语68
大海语文94
大海数学56
大海英语84
宋宋语文64
宋宋数学86
宋宋英语84
婷婷语文65
婷婷数学85
婷婷英语78
需求
计算每门学科成绩排名。
1
2
3
4
5
6
7
select name,
subject,
score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;
到此呢,关于Hive的一些基本的介绍就结束啦,后面我会在介绍下Hive在企业级中的调优的。
这是打赏的地方...

本文标题:大数据存储框架之Hive(1) 概述

文章作者:Mr.Sun

发布时间:2019年12月10日 - 11:06:43

最后更新:2020年06月15日 - 10:05:35

原始链接:http://www.blog.sun-iot.xyz/posts/81efa9bd

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

---------Thanks for your attention---------