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;