[大数据技术] Hive的常用HiveQL操作

1.Hive基本数据类型

Hive支持基本数据类型和复杂类型，基本数据类型主要有数值类型（INT、FLOAT、DOUBLE）、布尔型和字符串，复杂类型有三种：ARRAY、MAP和STRUCT。
（1）基本数据类型

• TINYINY：1个字节
• SMALLINT：2个字节
• INT：4个字节
• BIGINT：8个字节
• BOOLEAN：TRUE/FALSE
• FLOAT：4个字节，单精度浮点型
• DOUBLE：8个字节，双精度浮点型
• STRING：字符串

（2）复杂数据类型

• ARRAY：有序字段
• MAP：无序字段
• STRUCT：一组命名的字段

2.常用的HiveQL操作命令

2.1 数据定义：创建修改和删除数据库、表、视图、函数和索引

2.1.1 创建、修改和删除数据库

create database if not exists hive;       #创建数据库
show databases;                           #查看Hive中包含数据库
show databases like 'h.*';                #查看Hive中以h开头数据库
describe databases;                       #查看hive数据库位置等信息
alter database hive set dbproperties;     #为hive设置键值对属性
use hive;                                 #切换到hive数据库下
drop database if exists hive;             #删除不含表的数据库
drop database if exists hive cascade;     #删除数据库和它中的表

2.1.2 创建、修改和删除表

创建内部表（管理表）
create table if not exists hive.usr(
      name string comment 'username',
      pwd string comment 'password',
      address struct<street:string,city:string,state:string,zip:int>
      comment  'home address',
      identify map<int,tinyint> comment 'number,sex') 
      comment 'description of the table'  
     tblproperties('creator'='me','time'='2016.1.1'); 
     
创建外部表
create external table if not exists usr2(
      name string,
      pwd string,
  address struct<street:string,city:string,state:string,zip:int>,
      identify map<int,tinyint>) 
      row format delimited fields terminated by ','
     location '/usr/local/hive/warehouse/hive.db/usr'; 
     
创建分区表
create table if not exists usr3(
      name string,
      pwd string,
      address struct<street:string,city:string,state:string,zip:int>,
      identify map<int,tinyint>) 
      partitioned by(city string,state string);    
      
复制usr表的表模式  
create table if not exists hive.usr1 like hive.usr;
 
show tables in hive;  
show tables 'u.*';        #查看hive中以u开头的表
describe hive.usr;        #查看usr表相关信息
alter table usr rename to custom;      #重命名表
 
为表增加一个分区
alter table usr2 add if not exists 
     partition(city=”beijing”,state=”China”) 
     location '/usr/local/hive/warehouse/usr2/China/beijing'; 
     
修改分区路径
alter table usr2 partition(city=”beijing”,state=”China”)
     set location '/usr/local/hive/warehouse/usr2/CH/beijing';
     
删除分区
alter table usr2 drop if exists  partition(city=”beijing”,state=”China”)

修改列信息
alter table usr change column pwd password string after address;
 
alter table usr add columns(hobby string);                  #增加列
alter table usr replace columns(uname string);              #删除替换列
alter table usr set tblproperties('creator'='liming');      #修改表属性
alter table usr2 partition(city=”beijing”,state=”China”)    #修改存储属性
set fileformat sequencefile;             
use hive;                                                   #切换到hive数据库下
drop table if exists usr1;                                  #删除表
drop database if exists hive cascade;                       #删除数据库和它中的表

2.1.3 视图和索引的创建、修改和删除

create view view_name as....;                #创建视图
alter view view_name set tblproperties(…);   #修改视图

#删除视图
drop view if exists view_name;
#创建索引
create index index_name on table table_name(partition_name/column_name)  
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild....; 

alter index index_name on table table_name partition(...)  rebulid;   #重建索引

show formatted index on table_name;                       #显示索引
drop index if exists index_name on table table_name;      #删除索引

2.1.4 用户自定义函数

show functions; # 显示hive中的内置函数

describe function abs; # 查看具体的函数使用方法

add jar <jar文件的绝对路径>;                        #创建函数
create temporary function function_name;
drop temporary function if exists function_name;    #删除函数

3.数据操作

3.1 向表中装载数据

创建两个表 stu 和 course

create table if not exists hive.stu(id int,name string) 
row format delimited fields terminated by '\t';
create table if not exists hive.course(cid int,sid int) 
row format delimited fields terminated by '\t';

3.1.1 从文件中导入

假如这个表中的记录存储于文件stu.txt中，该文件的存储路径为/usr/local/hadoop/examples/stu.txt，内容如下。

stu.txt：

1 xiapi 
2 xiaoxue 
3 qingqing

下面我们把这个文件中的数据装载到表stu中，操作如下：

load data local inpath '/usr/local/hadoop/examples/stu.txt' overwrite into table stu;

如果stu.txt文件存储在HDFS 上，则不需要 local 关键字。

3.1.2 通过查询语句插入

使用如下命令，创建stu1表，它和stu表属性相同，我们要把从stu表中查询得到的数据插入到stu1中：

create table stu1 as select id,name from stu;

上面是创建表，并直接向新表插入数据；若表已经存在，向表中插入数据需执行以下命令：

insert overwrite table stu1 select id,name from stu where（条件）;

这里关键字overwrite的作用是替换掉表（或分区）中原有数据，换成into关键字，直接追加到原有内容后。

3.2 从表中导出数据

3.2.1 可以简单拷贝文件或文件夹

hadoop  fs -cp source_path target_path;

3.2.2 写入临时文件

insert overwrite local directory '/usr/local/hadoop/tmp/stu'  select id,name from stu;

3.3 查询操作

（1）case when

select id, name,case when id=1 then 'first' when id=2 then 'second' else 'third' end from stu;

（2）内连接

select stu.*, course.* from stu join course on(stu .id=course .sid);

（3）左连接

select stu.*, course.* from stu left outer join course on(stu .id=course .sid); 

（4）右连接

select stu.*, course.* from stu right outer join course on(stu .id=course .sid); 

（5）全连接

select stu.*, course.* from stu full outer join course on(stu .id=course .sid); 

（6）半连接

select stu.* from stu left semi join course on(stu .id=course .sid); 

4.Hive简单编程实践

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

$ cd /usr/local/hadoop
$ mkdir input

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

$ cd  /usr/local/hadoop/input
$ echo "hello world" > file1.txt
$ echo "hello hadoop" > file2.txt

（3）上传文件至hdfs

$ ./bin/hdfs dfs -mkdir input
$ ./bin/hdfs dfs -put ./input/*.txt input

复制完成后，可以通过如下命令查看文件列表

$ ./bin/hdfs dfs -ls input

（4）执行如下hadoop命令

$ hadoop jar share/hadoop/mapreduce/hadoop-mapreduce-examples-2.7.1.jar wordcount input output

查看运行结果

$ ./bin/hdfs dfs -cat output/*

删除output文件夹

./bin/hdfs dfs -rm -r output 

（5）通过HiveQL实现词频统计功能

create table docs(line string);
load data inpath 'input' overwrite into table docs;
create table word_count as 
select word, count(1) as count from
(select explode(split(line,' '))as word from docs) w
group by word
order by word;

执行后，用select语句查看，结果如下：