HiveQL常用操作汇总

（1）Hive数据类型。

1）基本数据类型。

INT类型主要包括TINYINT、SMALLINT、INT和BIGINT等。其中，TINYINT长度为1个字节，SMALLINT长度为2个字节，INT长度为4个字节，BIGINT长度为8个字节。布尔类型BOOLEAN包括TRUE和FALSE两个值；FLOAT类型为单精度浮点型，长度为4个字节；DOUBLE表示双精度浮点类型，长度为8个字节，STRING表示字符串类型。

2）复杂数据类型。

ARRAY表示有序字段类型；MAP表示无序字段类型；STRUCT表示一组命名的字段集合。

（2）常用的HiveQL操作命令。

Hive常用的HiveQL操作命令主要分为数据定义和数据操作两类。数据定义主要包括创建修改和删除数据库、表、视图、函数和索引等；数据操作主要包括数据导入、数据导出和数据查询等。

1）创建、修改和删除数据库。

HiveQL中创建、修改和删除数据库的命令如下。

注意，除dbproperties属性外，数据库的元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置，没有办法删除或重置数据库属性。

2）创建、修改和删除表。

①HiveQL创建内部表命令如下。

②HiveQL创建外部表命令如下。

③HiveQL创建分区表命令如下。

④HiveQL为表增加一个分区命令如下。

⑤HiveQL修改分区路径命令如下。

⑥HiveQL删除分区命令如下。

alter table usr2 drop if exists　partition(city="beijing",state="China")

⑦HiveQL修改列信息命令如下。

3）视图和索引的创建、修改和删除。

①HiveQL创建视图主要语法如下。

create view view_name as....;

②HiveQL修改视图主要语法如下。

create view view_name as....;

③HiveQL删除视图主要语法如下。

因为视图是只读的，所以对于视图只允许改变元数据中的tblproperties属性。

drop view if exists view_name;

4）索引的创建、修改和删除。

①HiveQL创建索引主要语法如下。

create index index_name on table table_name(partition_name/column_name)

as'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'with

deferred rebuild....;

这里’org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler’是一个索引处理器，即一个实现了索引接口的Java类，另外Hive还有其他的索引实现。

②HiveQL重建索引主要语法如下。

alter index index_name on table table_name partition(...)　rebulid;

③HiveQL显示索引主要语法如下。

show formatted index on table_name;

④HiveQL删除索引主要语法如下。

drop index if exists index_name on table table_name；　#删除索引

5）HiveQL数据导入。

下面介绍HiveQL常用的五种数据导入方式：load、Insert、As select、Location和Import。

①1load。

load加载linux本地文件或HDFS文件到hive表中，其中HDFS文件load方式是剪切模式，load后HDFS上文件会消失。若覆盖hive表已有数据，则需使用关键词overwrite。命令使用格式如下。

②Insert。

Insert往hive表中插入数据，或依据已有hive表，将查询结果插入目标hive表的分区中。命令insert into以追加数据的方式插入到hive表中，原有数据不会删除；insert overwrite会覆盖hive表中原有分区数据。Insert命令使用格式如下。

a.创建一张分区表，命令使用格式如下。(www.xing528.com)

create table student2(id int,name string)partitioned by(month string)row

format delimited fields terminated by'\t';

b.插入一个分区的数据，命令使用格式如下。

insert into table student2 partition(month='10')values(1,'kevin'),(2,'jay'),(3,

'daniel');

插入结果如表5-2所示。

表5-2　插入一个分区的数据表

c.通过已有hive表分区数据，插入hive表新增分区中。命令使用格式如下。

insert overwrite table student2 partition(month='11')select id,name from

student2 where month='10';

插入结果如表5-3所示。

表5-3　新增分区数据表

d.通过已有hive表分区数据，批量插入hive表新增分区中。命令使用格式如下。

插入结果如表5-4所示。

表5-4　批量插入新增分区数据表

③As select。

As select依据已有hive表的查询结果创建新表，将数据添加到新hive表中。命令格式如下。

create table if not exists student3 as select id,name from student2;

④Location。

若hdfs的某目录下本身有文件，可以通过创建外部表external的方式，指定表生成路径location‘HDFS~path’，这样外部表为虚表，查询其中数据即为HDFS指定目录下文件数据。命令格式如下。

⑤Import。

这种方式可以用sqoop将mysql等关系型数据库中表，导入hive表中；也可以从HDFS中将数据导入hive表。命令使用格式如下。

import table student2 partition(month='201709')from'/user/hive/warehouse/

export/student';

6）HiveQL数据导出。

①Hive表导出到本地文件系统，命令使用格式如下。

insert overwrite local directory'/home/wyp/wyp'select∗from wyp;

注意，Mapreduce完成该HQL命令，因此，将会在本地文件系统的/home/wyp/wyp目录下生成文件000000_0，其中，这里000000_0为文件名。

②Hive表导出到HDFS，命令使用格式如下。

insert overwrite directory'/home/wyp/hdfs'>select∗from wyp;

和导入数据到本地文件系统一样，导出来的数据将会保存在HDFS的/home/wyp/hdfs目录下。注意，此处的导出命令没有关键字“local”。

③将Hive表导出到另一个Hive表，命令使用格式如下。

insert into table test partition(age='25')select id,name,tel　from wyp;

7）HiveQL数据查询。

HiveQL数据查询分为基本查询、连接查询和子查询等。连接（join）是将两个表中在共同数据项上相互匹配的那些行合并起来，HiveQL的连接分为内连接、左向外连接、右向外连接、全外连接和半连接5种。

（3）HiveQL实例。

下面我们以词频统计算法为例，来介绍怎么在具体应用中使用Hive。词频统计算法又是最能体现MapReduce思想的算法之一，这里我们可以对比它在MapReduce中的实现，来说明使用Hive后的优势。具体步骤如下。

1）创建input目录，output目录会自动生成。其中input为输入目录，output目录为输出目录。命令如下：

1.cd/usr/local/hadoop

2.mkdir input

2）然后，在input文件夹中创建两个测试文件file1.txt和file2.txt，命令如下。

1.cd/usr/local/hadoop/input

2.echo"hello world">file1.txt

3.echo"hello hadoop">file2.txt

3）HiveQL实现词频统计功能，此时只要编写下面3条语句，HiveQL实现命令如下。

4）执行后，用select语句查看，结果如下。

由上可知，采用Hive实现最大的优势是，对于非程序员，不用学习编写Java MapReduce代码了，只需要用户学习使用HiveQL就可以了，而这对于有SQL基础的用户而言是非常容易的。