06 Hive QL的基础功能

Hive QL是Hive支持的类似SQL的查询语言。Hive QL大体可以分成DDL、DML、UDF共3种类型。

• DDL ( Data Definition Language）可以创建数据库(`createdatabase) 、创建表(create table)，进行数据库和表的删除;
• DML ( DataManipulation Language)可以进行数据的添加、查询;
• UDF ( User DefinedFunction)还支持用户自定义查询函数。

一、操作数据库

• 创建数据库

create dababase qiu;

• 显示数据库

describe database qiu;
-- 或者
desc database qiu;

• 打开数据库

use qiu;

• 删除数据库

drop database qiu;

二、创建表

CREATE TABLE创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用IF NOT EXIST选项来忽略这个异常。

在/usr/local/hive/examples/files路径下，有很多实例数据表，创建表可以使用其中的数据

• 创建emp和dept两张表

-- 创建表emp，以“|”分割字段
create table emp (ename string, deptid int, degree int) row format delimited fields terminated by '|';
-- 导入本地文件数据到emp表中
load data local inpath '/usr/local/hive/examples/files/emp.txt' into table emp;
-- 创建表dept，以“|”分割字段
create table dept (deptid int, dname string) row format delimited fields terminated by '|';
-- 导入本地文件数据到dept表中
load data local inpath '/usr/local/hive/examples/files/emp.txt' into table dept;

• 查看表命令

show tables;
show tables in qiu;
show tables '*e*';

Hive在HDFS上创建了hdfs://hadoop0:9000/user/hive/warehouse/qiu.db目录，用于存放数据文件
可以使用hadoop fs命令进行查看

Hive在hdfs://hadoop0:9000/user/hive/warehouse/qiu.db目录中又创建了dept和emp两个子目录，并且将dept.txt数据文件上传到了hdfs://hadoop0:9000/user/hive/warehouse/qiu.db/dept目录中，将emp.txt数据文件上传到了hdfs://hadoop0:9000/user/hive/warehouse/qiu.db/emp目录中。

三、数据准备

• 加载数据，local字段表示是本机目录；
• 如果不加，则表示是HDFS上的目录；

load data local inpath '/usr/local/hive/examples/files/dept.txt' into table dept;
load data local inpath '/usr/local/hive/examples/files/dept.txt' into table dept;

连续执行两次加载数据操作，结果用查询语句查看结果

select * from dept;

• overwrite关键字表示删除目标目录，当没有时则保留，但会覆盖同名旧目录。

load data local inpath '/usr/local/hive/examples/files/dept.txt' overwrite into table dept;

• 创建一个与dept结构一样的表dept2

create table dept2 (deptid int, dname string) row format delimited fields terminated by '|';
-- 将dept的数据导入dept2
insert into dept2 select * from dept;
-- 查看dept2的数据
select * from dept2;
-- 再次将dept的数据导入dept2
insert into dept2 select * from dept;
-- 查看dept2的数据（数据重复）
select * from dept2;
-- 使用overwrite导入
insert overwrite table dept2 select * from dept;
-- 查看dept2的数据(重复数据已删除)
select * from dept2;
-- 创建表的时候同时加载数据
create table dept3 as select * from dept;
-- 导出数据到指定路径
insert overwrite local directory '/root/dept' select * from dept;

# 另开一个命令行，查看在目录/root/dept下生成的000000_0文件内容
cat /root/dept/000000_0

• 查询emp表员工的全部数据，导入的原始数据中存在大量重复，先备份原始数据后修改原始数据

cp /usr/local/hive/examples/files/emp.txt /usr/local/hive/examples/files/emp_bak.txt
vim /usr/local/hive/examples/files/emp.txt
# 只保留前6行
cat /usr/local/hive/examples/files/emp.txt

• 使用overwrite对emp数据重写载入

load data local inpath '/usr/local/hive/examples/files/emp.txt' overwrite into table emp;
select * from emp;