Hive实战

创建数据库

在创建表之前，通常会先创建数据库。Hive中数据库的概念，本质上仅仅是表存放的目录或命名空间。使用数据库名称的好处是可以避免表命名冲突，如果用户没有指明具体的数据库，Hive会使用默认的数据库default。创建数据库语法如下：

CREATE DATABASE test;

数据库创建后，Hive会在HDFS文件系统中建立一个对应的目录，数据库中的表将会以这个数据库目录的子目录形式存储。数据库目录的默认位置，在hive-site.conf文件中hive.metastore.warehouse.dir属性所对应的目录下。

数据库的文件目录名是以.db结尾的。用户也可以在创建数据库时，通过指定LOCATION参数来修改这个默认位置。

如果想查看Hive中都有哪些数据库目录，可以使用SHOW DATABASES命令：

SHOW DATABASES

如果想使用哪个数据库，通过use命令切换数据库：

USE test;

Hive表的操作

Hive是一个数据仓库，它可以将结构化的数据文件映射为一张数据库表，并具有SQL语言的查询功能，在这里需要再次强调的是对于数据仓库来说，往往存放的是历史数据，它的作用是完成查询分析，用于企业高层决策参考，往往不会完成单条记录的增加、修改和删除操作。

创建Hive表

首先我们来创建一张普通的Hive表，表名为person，包含id、name、age、fav和addr这5个字段，数据类型分别为int、String、int、String数组和Map<String，String>，支持的数据文件格式为txt。建表语句如下：

CREATE TABLE person(
id INT, 
name STRING,
age  INT,
fav  ARRAY<STRING>,
addr MAP<STRING,STRING>
)
//表中的字段和数据类型
COMMENT 'This is the person table' 
//表的简介，有无皆可
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'  
COLLECTION ITEMS TERMINATED BY '-'  
MAP KEYS TERMINATED BY ':' 
STORED AS TEXTFILE;
//定义这张表使用的数据文件格式，这里指定为txt类型

ROW FORMAT DELIMITED FIELDS TERMINATED BY’\t’是定义每一个字段的分隔符，这里的’\t’表示以Tab键作为分隔符分隔每行字段。
COLLECTION ITEMS TERMINATED BY’-‘是定义集合类型中每个对象的分隔符，fav字段是String类型的数组，这里定义以’-‘为分隔符，分隔每个字符串。
MAP KEYS TERMINATED BY’：’是定义Map类型键值对的分隔符，这里以’：’为分隔符，分隔键与值。
STORED AS TEXTFILE是定义这张表使用的数据文件格式，这里指定为txt类型。

对于以上DDL语句，需要注意如下几点。

数据类型 Hive表字段支持的数据类型很丰富，分为基本数据类型和集合数据类型。基本数据类型分别和Java的数据类型对应，有String、Float、Int等；集合数据类型包括ARRAY、MAP、STRUCT。
键关系数据库通常使用唯一键和索引来存储数据集，然而Hive表中没有传统关系数据库中键的概念，也没有自增键的概念，在Hive中可以定义索引来加速某些字段的操作，一个表的索引数据存放在另一个表中，Hive的索引功能比较有限。避免主键、外键等标准化的主要原因是为了提高检索效率，优化I/O性能。但是，非标准化数据可能会导致数据重复或数据不一致。
表分区表分区的概念由来已久，它是用来提升数据存取性能的有效手段，通过使用分区来水平分散压力，将数据从物理上转移到和使用最频繁的用户更接近的地方。在Hive中同样有分区的概念，可以指定表中的多个字段组合在一起，形成分区字段，每个分区中的数据，在HDFS中存放在同一个表名下不同的子目录中。选择哪些字段作为分区，要根据不同的业务场景区别对待。除了分区之外，在Hive中对表数据分割的方法还有Bucket（桶），它是比PARTITION（分区）粒度更细的数据划分方法，可以使特定的查询效率更高。
行和列分隔符 ROW FORMAT DELIMITED是指定数据行之间的分隔符，该语句必须写在其他子句之前。本例中没有指定行分隔符，完整的语法应该是：
```
ROW FORMAT DELIMITED  LINES TERMINATED BY '\n'
```
目前，Hive仅支持回车作为行分隔符，因此LINES TERMINATED BY语句可省略。列分隔符使用语法“FIELDS TERMINATED BY ‘\001’”，字符’\001’是^A的八进制数，是一个不可见字符。
存储方式建表语句最后出现的STORED AS TEXTFILE定义了表文件在HDFS中的存放格式，TEXTFILE是文本格式，这也是默认的文件格式。除此之外，还有SequenceFile、RCFile格式，这两种都是以二进制形式存储的，比文本格式的存储占用空间少。

要执行上面的创建表脚本，可以登录Hive客户端beeline，或者使用Hive CLI界面，把每条DDL语句贴进去执行。这样做显然很烦琐，也不具备自动执行的能力。那么，有没有一种简单的方法呢？

在Hive中可以使用“-f <文件名>”参数方式从文件中执行Hive查询语句。依照惯例，一般把这些查询语句保存为*.q或*.hql（hive query language）后缀的文件。

hive -f xxx.hql

或者，在Hive shell中使用source命令执行脚本文件：

导入数据

使用上述建表语句建出的表只是一张空表，里面不包含任何数据，接下来要向表中导入数据。在Hive中没有提供行级别的数据插入、数据更新和数据删除功能，往表中“装载”大量的数据，Hive提供了两种办法，分别是从外部文件或HQL结果集中将数据批量插入至Hive表。

前面提到过Hive是将结构化的数据文件映射为一张表，所以我们先准备一个数据文件，在这里应该为一个txt格式的文件，并且这个txt文件的内容需要有一个固定的格式，这个格式就是我们建表时定义的那些字段和元素分隔符。

文件具体内容如下：

rod 18 study-game-driver std_addr:beijing-work_addr:shanghai
tom 21 study-game-driver std_addr:beijing-work_addr:beijing
jerry 33 study-game-driver std_addr:beijing-work_addr:shenzhen

上面内容中每一行代表一条记录，每行字段中间隔一个’Tab’键，fav字段中每个元素中间用’-‘隔开，addr字段中的键和值用’：’隔开。

LOAD DATA命令是从文件将数据加载至Hive表的。有了数据文件，就可以向表中导入数据了。下面指定导入数据文件的所在目录和要导入哪一张表，导入数据的语句如下：`

LOAD DATA LOCAL INPATH person.txt OVERWRITE INTO TABLE person;

执行LOAD DATA命令需要注意如下几点。

LOCAL INPATH 要加载的数据文件可以有两种不同的来源：LOCAL INPATH是从执行LOAD命令的本机中的某个目录下加载数据的；没有LOACL参数，只有INPATH是从Hadoop分布式文件系统中的某个目录下加载数据的。INPATH后面引号中的字符串，既可以是一个目录名，也可以是一个文件名。如果指定目录名，Hive会尝试加载该目录下的每个文件。

INPATH后面的字符串，通常都会指明一个目录，这样处理起来更加方便，但是不允许再包含子目录。另外，如果LOCAL参数存在，Hive只会复制文件到对应的Hive表目录下；如果LOCAL参数不存在，则是把HDFS中的一个目录移动到Hive表对应的目录下，原有目录和文件会被删除。

OVERWRITE 如果命令中包含OVERWRITE关键字，Hive表中对应目录下已有的数据会被先删除，然后再加载新的数据；如果没有这个关键字，仅会把INPATH后面字符串中对应的数据追加到Hive表目录下。这就好比是写文件的两种方式：一种是覆盖原有文件内容；一种是在原有文件末尾接着写。

在实际应用场景中，通常OVERWRITE和PARTITION配合使用，实现对某个分区数据的重复写入。

导入完成之后就可以使用如下语句进行查询，查看表中是否存在数据：

select * from person;

查询命令及查询结果如下：

 hive> select * from person;
OK
  1 rod  18 ["study","game","driver"] {"std_addr":"beijing",
"work_addr":"shanghai"}
  2  tom 21  ["study","game","driver"] {"std_addr":"beijing",
"work_addr":"beijing
jerry  33   ["study","game","driver"] {"std_addr":"beijing",   "work_addr":"shenzhen"}
Time taken: 0.6 seconds,Fetched: 4 row(s)

表的分区与分桶

Hive中存放的数据往往是以PB为单位的庞大的数据集，海量的数据需要耗费大量的时间去处理，若是每次查询都对全部数据进行检索，效率会极为低下。而且在许多场景下，我们并不需要对全部数据进行检索，因此引入分区和分桶的方法减少每一次扫描总数据量，这种做法可以显著地改善性能。

表的分区

把数据依照单个或多个列进行分区，通常按照时间、地域进行分区。比如一张统计了全国各地一年来不同时刻温度的表，可以按照地域来分区，也可以按照时间来分区，甚至可以将时间和地区都当做分区条件。为了达到性能表现的一致性，对不同列的划分应该让数据尽可能均匀分布。最好的情况下，分区的划分条件总是能够对应where语句的部分查询条件。

分区应当在建表时就设置好了，我们还是以前面创建的person表为例，加上分区的建表语句如下：

CREATE TABLE person(
id INT,
name STRING,
age  INT,
fav  ARRAY<STRING>,
addr MAP<STRING,STRING>
)                              
COMMENT 'This is the person table' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'  
PARTITONED BY (dt STRING)
COLLECTION ITEMS TERMINATED BY '-'  
MAP KEYS TERMINATED BY ':' 
STORED AS TEXTFILE;

以String类型的字段dt为依据进行分区。同时，导入数据的语句也有所变化，必须在导入数据时指定要导入哪一个分区中。

LOAD DATA LOCAL INPATH 'person.txt' OVERWRITE INTO TABLE person partition 
(dt='20180315'); 

除指定导入数据文件的所在目录和要导入哪一张表外，还要使用partition关键字指定导入哪一个分区，导入数据后可使用查询语句查看该分区内的数据。

select * from person where partition(dt=’20180315’);

具体操作步骤如下：

启动HDFS并进入Hive。
查看数据库并选择一个数据库启用，如果不选择数据库，默认使Deafult数据库。

输入建表语句，在已启用的数据库下建一张表。

Hives>CREATE TABLE person(
    >id INT,
    >name STRING,
    >age  INT,
    >fav  ARRAY <STRING >,
    >addr MAP<STRING ,STRING>
    >)
    >COMMENT "This is the person table'
    >PARTITIONED BY(dt STRING)
    >ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t'
    > COLLECTION ITENS TERHINATED BY'-'
    > MAP KEYS TERMINATED BY‘:"
    > STORED AS TEXTFILE;
OK
Time taken: 0 .097 seconds

编写数据文件。

向表中导入数据，注意不要输错数据文件的目录。

hive>LOAD DATA LOCAL INPATH '/root/person.txt' OVERWRITE INTO TABLE person partition(ldt='20170815');
Loading data to table hivel.person partition (dt=20180315)
Partition hivel.person{dt=20180315}stats:[numFiles=1,numRows=0.
totalSize=193,rawDataSize=0]
OK
Time taken: 0.537 seconds 

输入查询语句，查看分区内是否已插入数据。

hive> select addr['work_addr'] from person where dt=20180315
   >
OK
 shanghai 
beijing 
shenzhen
Time taken: 0.986 seconds,Fetched: 4 row(s)

关于动态分区

由于HDFS写入数据是冗余存储的，创建动态分区是一项消耗资源的操作，在默认情况下动态分区功能是关闭的。Hive同时要求即使开启动态分区，表中也不能出现所有字段都是分区字段的情况。在${HIVE_HOME}/conf/hive-site.xml中，与动态分区相关的部分，有5个配置项

set hive.exec.dynamic.partition=true
set hive.exec.dynamic.partition.mode=nonstrict
set hive.exec.max.created.files=100000
set hive.exec.max.dynamic.partitions.pernode=100
set hive.exec.max.dynamic.partitions=1000

表的分桶

分桶是相对分区进行更细粒度的划分。在分区数量过于庞大以至于可能导致文件系统崩溃时，我们就需要使用分桶来解决问题了。

分桶将整个数据内容按照某列属性值的Hash值进行区分。比如，如要按照ID属性分为4个桶，就是对ID属性值的Hash值对4取模，按照取模结果对数据分桶。例如，取模结果为0的数据记录存放到一个文件中，取模为1的数据存放到一个文件中，取模为2的数据存放到一个文件中。

分桶同样应当在建表时就建立，建表语句与之前建立分区表类似。我们还是创建表person，其建表语句如下：

CREATE TABLE person(
id INT, 
name STRING,
age  INT,
fav  ARRAY<STRING>,
addr MAP<STRING,STRING>
) 
COMMENT 'This is the person table' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'  
PARTITIONED BY(dt STRING)
CLUSTERED BY (id) into 3 buckets
COLLECTION ITEMS TERMINATED BY '-'  
MAP KEYS TERMINATED BY ':' 
STORED AS TEXTFILE;

以字段id为分桶依据，将每个分区分成3个桶，导入数据的语句与分区表时没有区别，还是向分区中导入数据。

LOAD DATA LOCAL INPATH 'person.txt' OVERWRITE INTO TABLE person partition 
(dt='20180315'); 

如果要查询3个桶中第1个桶中的全部数据，可以通过以下查询语句进行查询。

select * from person tablesample(bucket 1 out of 3 on id);

具体过程操作步骤如下（相同的步骤不再重复描述）：

启动HDFS，并进入Hive。

输入建表语句如下：

hive> CREATE TABLE person(
  >id INT,
  >name STRING,
  >age INT,
  >fav  ARRAY <STRING> ，
  >addr MAP<STRING , STRING>
  > )
  >COMHENT'This is the person table'
>PARTITIONED BY(dt STRING)
  > clustered by (id) into 4 buckets
  >ROW FORMAT DELIMITED FIELDS TERMINATED BY'\t'
> COLLECTION ITEMS TERMINATED BY': '
  >MAP KEYS TERMINATED BY':'
  > STORED AS TEXTFILE;
OK
Time taken: 0.073 seconds

编写数据文件。
向表中分区导入数据，注意不要输错数据文件的目录。

输入查询语句，查看分桶内是否成功。

Hives>select * from person tablesample(bucket 1 out of 3 on id):
OK
3 jerry 33 ["study","game","driver] {"std_addr":"beijing","work_addr":
"shanghai} 20170315
Time taken:0.087 seconds,Fetched: I row(S) 

从查询结果中可以看出，分桶中只有一条数据。

内部表与外部表

与传统的关系型数据库不同，Hive创建的表分为内部表和外部表，对于内部表来说，在创建的时候会把数据移动到数据仓库所指向的位置；如果是外部表，则仅仅记录数据所在的位置。

同时，对于内部表来说，在删除的时候会将元数据和数据一起删除，而外部表仅仅是删除了元数据，真正的数据不会删除。所以，如果在共享源数据的情况下，可以选择使用外部表；如果仅仅是Hive内部使用，则可以使用内部表。接下来分别结合实例介绍内部表和外部表。

内部表

内部表顾名思义，数据文件的全部操作都由Hive来完成，也就是说除了Hive外不会再有其他的应用使用该数据文件。前面我们所创建的表都是内部表。内部表在创建后不仅默认会在“/user/hive/warehouse/数据库名”下生成表的目录，还会在目录下生成一份表的数据文件，并且在表删除后，该目录和数据文件也会删除。

在Hive下输入dfs-ls/user/hive/warehouse/hive1.db；查看表目录：

Hives >dfs -ls/user/hive/warehouse/hivel.db
Found 1 items
Drwxrwxrwx  - root  supergroup  0 2017-09-21 13:39 /user/hive/warehouse/
h1ve1.db/person

再输入dfs-ls/user/hive/warehouse/hive1.db/person/分区名；查看表的数据文件：

Hives > dfs -1s /user/hive/warehouse/hivel.db/person/dt=20180315 
Found I items
-rwxrwxrwx 3rootsupergroup1922017-09-2113:39/user/hive/warehouse/hive.db/person/
dt-20180315/person.t
xt 

输入drop table person；删除表person，再输入dfs-ls/user/hive/warehouse/hive1.db；查看表和数据文件是否被删除掉：

Hive > drop table person;
OK
Time taken: 0.222 seconds
hives>dfs -ls/user/hive/warehouse/hivel.db;

查询结果显示没有任何内容，说明表目录和数据文件一同被删除了。

外部表

在实际应用过程中，数据文件往往并不是只由Hive来操作，其他应用或计算也会操作该文件，这种情况下往往不允许改变数据文件的格式或者位置。面对这种情况，我们一般选择创建外部表来实现Hive操作。外部表也会在“/user/hive/warehouse/数据库名”下生成表的目录，但是目录内不会生成数据文件，并且删除表不会影响到导入数据的源数据文件。

外部表的创建有两种方式，一种是创建一张空表，然后向表中导入数据，其创建语句如下：

CREATE external TABLE person(
id INT, 
name STRING,
age  INT,
fav  ARRAY<STRING>,
addr MAP<STRING,STRING>
)                              
COMMENT 'This is the person table' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'  
COLLECTION ITEMS TERMINATED BY '-'  
MAP KEYS TERMINATED BY ':' 
STORED AS TEXTFILE;

上述建表语句中，通过external关键字指明创建的表是外部表。创建外部表还有另外一种方式，在创建表的同时，指定数据文件的位置，其建表语句如下：

CREATE external TABLE person(
  id INT, 
  name STRING,
  age  INT,
  fav  ARRAY<STRING>,
  addr MAP<STRING,STRING>
)                              
COMMENT 'This is the person table' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'  
COLLECTION ITEMS TERMINATED BY '-'  
MAP KEYS TERMINATED BY ':' 
LOCATION  'hdfs://192.168.79.100:9000/root/ '
STORED AS TEXTFILE;

上述建表语句，添加external关键词创建外部表，使用location关键词指定数据文件的位置。

下面使用第一种方式创建一张外部表，首先在Hive下输入建表语句创建外部表person，建表语句如下：

hive> CREATE external TABLE person(
    >id INT,
    >name  STRING,
    >age  INT,
    >fav ARRAY<STRING>,
    >addr MAP<STRING, STRING>
    > ）
    >COMMENT'This is the person table'
    >ROW FORMAT DELIMITED FTELDS TERMINATED BY'\t'
    >COLLECTION ITEMS TERMINATED BY'-’
    >MAP KEYS TERMINATED BY’:'
    > STORED AS TEXTFILE;
OK
Time taken; :0.146 seconds

表创建完后，就可以向表中导入数据了。

Hives>LOAD DATA LOCAL INPATH '/root/person.txt' OVERWRITE INTO TABLE person;
Loading data to table hivel. person
Table hivel.person stats:[numFiles=1,numRows=0,totalsize=192,ramDataSize=0]
OK
Time taken: 0.212 seconds

在Hive下输入dfs-ls/user/hive/warehouse/hive1.db，查看表目录：

Hive>dfs -ls/user/hive/warehouse/hive.db
Found 1 items
drwxrwxrwx-root supergroup 0 2017-09-21 14:13 /user/hive/warehouse/hive1.
db/person

在Hive下输入dfs-ls/user/hive/warehouse/hive1.db/person，查看数据文件。然后删除表后再查看文件是否存在，这样就可以看出内部表和外部表的区别了。

内置函数与自定义函数

Hive中包含很多内置的函数，如果这些函数不能满足我们的业务场景时，可以通过编写用户自定义函数（User-Defined Function，UDF）来实现，并在Hive中调用。

UDF函数有3种类型，分别是UDF、UDAF和UDTF。

UDF函数的特点是作用于单条数据，并且输出一个数据行。大多数的函数如字符串函数、日期函数等都属于这一类。
UDAF函数可以接受多个数据输入，同时输出一个数据行，比如COUNT、MIN、MAX，这类函数都是聚集函数。
UDTF函数的特点是作用于单个数据行，同时产生多个输出数据行。

内置函数实例

Hive安装完成后自带了一些内置函数，使用SHOW FUNCTIONS命令可以查看当前Hive连接中所有的函数列表。

在讨论实例之前，为了便于自定义函数的演示，我们先创建一个虚拟表，创建虚拟表的过程如下：

echo'X'> dual.txt
hive> create table dual (dummy String);
hive> load data local inpath ‘dual.txt’ overwrite into table dual;

上述代码中，echo’X’>dual.txt是代表把X写入dual.txt文件中，接着创建一个名为dual的表，表字段是dummy，并把dual.txt中的X导入到表中，完成后，可以通过以下操作查看临时表的内容。

hive>select * from dual;
OK
X
Time taken:0.286 seconds,Fetched:1row(s)

临时表创建完后，我们就可以编写自定义函数了。下面分别举例来说明UDF函数、UDAF函数和UDTF函数的用法。

UDF函数实例

首先来看一个UDF函数实例，这个例子很简单，就是将’a’、’b’、’c’、’d’进行拼接起来，完成输出，需要用到concat()函数。代码如下：

hive> select concat('a','b','c','d') from dual;
OK
abcd
Time taken: 0.726 seconds,Fetched: 1row(s)

UDAF函数实例

接着再看一个UDAF函数实例，就是统计UID的总和。代码及运行结果如下：

hive> select sum(uid) from user_dimension;
Query ID = root_20170921222228_391990bb-e12f -4a58-b1e8-3e1410f2a362
Total jobs=1
Launching Job I out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes);
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers;
  set hive.exec.reducers.max=<number>
In order to set a constant nuaber of reducers:
  set mapreduce.job.reduces=<number>
StartingJob=job_1505992163946_0001,TrackingURL=http://master1:8088, 
/proxy/application_1505992163946_0001
Kill Cormand = /opt/software/hadoop-2.5.1/bin hadoop job -kill j0b_
1505992163946_0001
Hadoop job information for Stage-1:nunber of mappers: 1; nunber of reducers: 1
2017-09-21 22:22:51,222 Stage-1 map =0%，reduce=0%
2017-09-21 22:23:33,867 Stage-1 map =100%，reduce =0%,Cumulative CPU 7.11sec
2017-09-21 22:23:44,290 Stage-1 map =100% ,reduce=100%,Cumulative CPU 8.88 
sec
MapReduce Total cumulative CPU time: 8 seconds 880.msec
Ended Job=job_1505992163946_0001
MapReduce Jobs Launched:
stage-stage-1:Map: 1 Reduce: 1 Cunulative CPu:8.88sec HDFS Read: 44116 HDFS 
Write: 9 SUCCESS 
Tatal MapReduce CPU Time Spent: 8 seconds 880 msec
OK
499500.0
Tine taken: 76.774 seconds,Fetched: 1 row(s)
hive>

UDTF函数实例

接着再看一个UDTF函数实例，就是将’a，b，c，d，e’分解成’a’，’b’，’c’，’d’，’e’，并逐行输出。

hive> select explode(split('a,b,c,d,e'，',')) from dual;
OK
ａ
ｂ
ｃ
ｄ
e
Time taken: 0.105 seconds,Fetched:5  row(s)

自定义UDAF函数实例

前面介绍了Hive自带的UDF、UDAF和UDTF函数，如果系统自带的函数无法满足业务需求时，可以自己定义一个函数，并在需要的时候进行调用。

以下是一个计算一组整数中最大值的UDAF函数实例。

开发Hive自定义函数通常使用Java语言，这是因为Hive本身是用Java编写的，用户只需要简单地继承UDF类并重载evaluate方法即可。编写Java代码如下：

import org.apache.hadoop.hive.ql.exec.UDAF;
import org.apache.hadoop.hive.ql.exec.UDAFEvaluator;
import org.apache.hadoop.io.IntWritable;
public class Maximum extends UDAF {
public static class MaximumIntUDAFEvaluator implements UDAFEvaluator {
   private IntWritable result;
   public void init() {
      result = null;
   }
   public boolean iterate(IntWritable value) {
      if (value == null) {
         return true;
      }
      if (result == null) {
         result = new IntWritable(value.get());
      } else {
         result.set(Math.max(result.get(), value.get()));
      }
         return true;
      }
      public IntWritable terminatePartial() {
          return result;
       }
       public boolean merge(IntWritable other) {
          return iterate(other);
       }
       public IntWritable terminate() {
          return result;
       }
   }
}

导出JAR包，并将JAR包添加到Hive中。

hive> add jar Maximum.jar;

用JAR包生成一个函数。

hive> create function maximumtest as 'com.firsthigh.udaf.Maximum';

运行函数并检查结果。

hive> select maximumtest(price) from record_dimension;
Query ID = root_20170921233816_45023bfd-b8a2-4d71-a188-d7cf5837360a
Total jobs=1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
Starting Job=job_1505992163946_0002,TrackingURL=http://master1:8088/pxoxy/
application_1505992163946_0002
kill Comnand = /opt/software/hadoop-2 .5 .1/bin/hadoop job -kill job_
1505992163946_0002/
Hadoop job information for Stage-1:number of mappers: 1; number of reducers:1
2017-09-21 23:39:28,721 Stage-1 map= 0%，  reduce =0%
2017-09-21 23:40:01,925 Stage-1map=100%，reduce = 0%，Cumulative CPU 2.91 sec
2017-09-21 23:40:26,374 Stage-1map=100%，reduce=100%，Cumulative CPU 5.97 sec
MapReduce Total cumulative CPU time:5 seconds 970 msec.
Ended Job = job_1505992163946_0002
MapReduce Jobs Launched:
stage-stage-1: Map 1 Reduce: 1  Cumulative CPU: 5 .97 sec  HDFS Read; 70184 
HDFS Write; 5 SUCCESS
Total MapReduce CPU Time Spent: 5 seconds 970 msec
OK
1000
Tine  taken; 133.872 seconds,Fetched: 1row(s)

HQL的几种执行方式

两种运行方式，分别是使用Hive CLI（Command Line Interface，命令行接口）和beeline客户端。安装Hive后，在bin目录下有两个可执行脚本，分别是小写的hive和beeline文件名

Hive命令行界面，也就是CLI，是和Hive交互的最常见方式。使用CLI，用户可以创建表、检查模式以及运行HQL语句等。

beeline工具是Hive 0.11.0版本中增加的新的交互式CLI，它基于SQLLine，可以作为Hive JDBC客户端访问Hive中的表和数据，执行HQL语句。运行beeline的前提是要先启动HiveServer服务。beeline在使用前要先连接上HiveServer服务，有点像PL/SQL客户端工具的角色。

使用“!connect jdbc:hive://hiveServerIP地址:端口号”的格式连接HiveServer。目前Hive提供两种JDBC连接方式，分别是HiveServer和HiveServer2，两者的区别下面会讲到。

beeline在连接时要识别用户身份，可以通过配置Kerberos认证来实现；而Hive CLI连接时是不需要用户登录的。HQL在beeline中均可以正常使用，和Hive CLI的区别是不再输出MapReduce任务执行的日志信息，并且连接HiveServer后，所有的HQL语句执行均使用同一个数据库连接。

Hive Thrift服务

在使用beeline连接Hive数据仓库前，要在服务器上开启Hive Thrift服务。Hive Thrift（HiveServer）服务是Hive中的组件之一，设计目的是为了实现跨语言轻量级访问Hive数据仓库。

通过Hive CLI访问Hive是胖客户端方式，需要客户机安装JRE环境和Hive程序，就像客户端要连接Oracle数据库服务器，必须安装Oracle客户端一样。而采用JDBC方式访问Hive数据仓库，则是瘦客户端方式，前提是服务器必须先启动Hive Thrift服务。和使用JDBC方式访问Oracle数据库，要求服务器要启动LISTENER监听服务，道理是一样的。

目前，HiveThrift组件包含两个版本，分别是HiveServer（ThriftServer）和HiveServer2，后者是在Hive 0.11.0版本中增加的，原因是HiveServer不能处理多客户端的并发请求。需要注意的是，HiveServer2不会向下兼容ThriftServer，编码实现时必须分别处理。

Hive数据抽样

当数据规模不断膨胀时，我们需要找到一个数据的子集来加快数据分析效率。因此我们就需要通过筛选和分析数据集为了进行模式 & 趋势识别。目前来说有三种方式来进行抽样：随机抽样，桶表抽样，和块抽样。

随机抽样

关键词： rand()函数。

使用rand()函数进行随机抽样，limit关键字限制抽样返回的数据，其中rand函数前的distribute和sort关键字可以保证数据在mapper和reducer阶段是随机分布的。

案例如下：

select * from table_name   
where col=xxx   
distribute by rand() sort by rand()   
limit num;   

使用order 关键词:

案例如下：

select * from table_name   
where col=xxx   
order by rand()   
limit num;   

经测试对比，千万级数据中进行随机抽样 order by方式耗时更长，大约多30秒左右。

块抽样

关键词： tablesample()函数。

tablesample(n percent) 根据hive表数据的大小按比例抽取数据，并保存到新的hive表中。如：抽取原hive表中10%的数据注意：测试过程中发现，select语句不能带where条件且不支持子查询，可通过新建中间表或使用随机抽样解决。

select * from xxx tablesample(10 percent) 数字与percent之间要有空格

tablesample(nM) 指定抽样数据的大小，单位为M。

select * from xxx tablesample(20M) 数字与M之间不要有空格

tablesample(n rows) 指定抽样数据的行数，其中n代表每个map任务均取n行数据，map数量可通过hive表的简单查询语句确认（关键词：number of mappers: x)

select * from xxx tablesample(100 rows) 数字与rows之间要有空格

桶表抽样

关键词： tablesample (bucket x out of y [on colname]) 。

其中x是要抽样的桶编号，桶编号从1开始，colname表示抽样的列，y表示桶的数量。

hive中分桶其实就是根据某一个字段Hash取模，放入指定数据的桶中，比如将表table_1按照ID分成100个桶，其算法是hash(id) % 100，这样，hash(id) % 100 = 0的数据被放到第一个桶中，hash(id) % 100 = 1的记录被放到第二个桶中。创建分桶表的关键语句为：CLUSTER BY语句。

例如：将表随机分成10组，抽取其中的第一个桶的数据：

select * from table_01   
tablesample(bucket 1 out of 10 on rand())  

yangjava

Hive实战

Hive实战

创建数据库

Hive表的操作

创建Hive表

导入数据

表的分区与分桶

表的分区

关于动态分区

表的分桶

内部表与外部表

内部表

外部表

内置函数与自定义函数

内置函数实例

UDF函数实例

UDAF函数实例

UDTF函数实例

自定义UDAF函数实例

HQL的几种执行方式

Hive Thrift服务

Hive数据抽样

随机抽样

块抽样

桶表抽样

Search

微信好友

Table of Contents