Hive DML

DML入门

DML(Data Manipulation Language)
具体操作：
load/insert/update/delete/merge, import/export, explain plan(执行计划)
网址：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML

加载数据到Hive表

加载数据到Hive表详解

• 创建表：

create table emp(
empno int, ename string, job string, mgr int, hiredate string, sal double, comm double, deptno int
)row format delimited fields terminated by '\t';

• Loading files into tables的语法：

    LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

• 几个需要注意的点：

  1. LOCAL：
     有LOCAL：从本地文件系统加载数据到Hive表
     没有LOCAL：：从HDFS加载数据到Hive表

  2. filepath：
     本地与HDFS的不同路径

  3. OVERWRITE：
     有OVERWRITE：：会覆盖
     没有OVERWRITE：：追加

  4. tablename：
     表名

加载本地的数据到Hive表

hive>load data local inpath '/opt/data/emp.txt' overwrite into table emp;

从本地加载数据成功

加载HDFS的数据到Hive表

操作之前先清空Hive表中的数据

hive>truncate table emp;
hive>load data inpath 'hdfs://hadoop003:8020/zhaotao/emp.txt' overwrite into table emp;

从HDFS加载数据成功

不使用overwrite，加载数据

hive>load data local inpath '/opt/data/emp.txt' into table emp;

发现，向原始的数据上追加了新的数据，并没有对原始的数据进行覆盖

在HDFS上的文件夹为：

再load一次，发现多了个emp_copy_2.txt：

使用overwrite，加载数据

hive>load data inpath 'hdfs://hadoop003:8020/zhaotao/emp.txt' overwrite into table emp;

因为使用了overwrite，对原始的数据进行了覆盖

因此在hdfs的文件夹为：/user/hive/warehouse/emp/emp.txt 发现copy_1与copy_2不再存在

使用从HDFS上加载数据的方式，会发现，数据加载到Hive表中后，从HDFS上所加载过来的文件数据会消失
对比图：

加载数据的另一种方式

CTAS:  CREATE TABLE AS SELECT

从query中，加载数据到Hive表

Inserting data into Hive Tables from queries的语法：
1.第一种写法：

    INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;
    INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;

2.第二种写法：

    Hive extension (multiple inserts):
    FROM from_statement
    INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1
    [INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2]
    [INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] ...;
    FROM from_statement
    INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1
    [INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2]
    [INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2] ...;

3.第一种写法示例：

        hive>INSERT OVERWRITE TABLE emp3 select * from emp;
        hive>INSERT INTO TABLE emp3 select * from emp;

这两句Hive QL 都是会去执行MapReduce的，对于何种语句会执行MapReduce任务，将在后续的文章中进行剖析

4.第二种写法示例：

hive>FROM emp INSERT OVERWRITE TABLE emp3 select *;

加载数据到Hive表的注意事项

1.column number/types
表中已经有数据的情况下，向表中插入数据
出现插入的列的数目或是类型不匹配的情况，这样会导致数据插入的失败
比如：

 hive>INSERT OVERWRITE TABLE emp3 select empno,ename from emp;

报错：

语法错误！！！
因为表中有8列，而我们写的语句是插入2列，数目匹配不上，因此会报错

如果表中有数据，需要插入数据，有以下两种做法
1) select *
2) select empno,…….. 需要把所有字段写一遍

2.插入数据的时候两个字段的顺序写反
比如：

hive>INSERT OVERWRITE TABLE emp3 select 
     empno, 
     job,
     ename, 
     mgr, 
     hiredate, 
     sal, 
     comm, 
     deptno
     from emp;

job和ename两者顺序写反了

正确数据与错误数据对比：

hive>select * from emp3 where ename='SMITH'; 

查不出结果；因为表结构的第二个字段是ename，第三个字段是job；因此当插入的时候写反了，数据也插入错了

导出Hive表数据到文件系统

第一种方法

语法结构：

INSERT OVERWRITE [LOCAL] DIRECTORY directory1
  [ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0)
  SELECT ... FROM ...

将select语句执行的结果，写入到文件系统里去

写到本地

hive>INSERT OVERWRITE LOCAL DIRECTORY '/home/hadoop/tmp/emp'
     row format delimited fields terminated by '\t'
     select * from emp;

成功写到本地：

写到HDFS：

hive>INSERT OVERWRITE  DIRECTORY '/zhaotao/emp'
row format delimited fields terminated by '\t'
select * from emp;

成功写到HDFS：

注意：
输出到HDFS，有些CDH版本不支持;
输出到本地文件系统和HDFS，取决于LOCAL关键字

第二种方法

Linux控制台下执行

$>hive -e "select * from emp limit 5"                      
$>hive -e "select * from emp limit 5" | grep SMITH

将结果直接导入到本地

$>hive -e "select * from emp limit 5" | grep SMITH > file 

运行结果：

第三种方法

创建test.sql
用来查阅有几条记录数

select count(1) from emp;       

Linux控制台下执行

$>hive -f test.sql 

执行结果：

数据导入导出 常常会使用sqoop框架

常用查询详解

hive>select * from emp;
hive>select * from emp where ename='...';
hive>select * from emp where deptno=10;
hive>select * from emp where sal between 800 and 1500;

in / not in

hive>select * from emp where ename in ('SMITH','WARD');

上述的运行结果：

聚合函数 – max、min、count、sum、avg

hive>select count(1),max(sal),min(sal),avg(sal) from emp;

上述的运行结果：

分组函数 – group by

hive>select deptno,avg(sal) from emp group by deptno;

上述的运行结果：

算每个部门 每个工作岗位的平均工资

hive>select deptno,avg(sal),job from emp group by deptno,job;  

上述的运行结果：

分组group by之后，得使用having，不能再使用where ；取了别名avg_sal

hive>select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal > 2500;  

上述的运行结果：

case when then

hive>select ename, sal, 
     case 
     when sal > 1 and sal <= 1000 then 'LOWER'
     when sal > 1000 and sal <= 2000 then 'MIDDLE'
     when sal > 2000 and sal <= 4000 then 'HIGH'
     ELSE 'HIGHEST' end
     from emp;

上述的运行结果：

注意：
1. then后面取的是名字
2. 在出报表的时候，会用得上

export&import使用详解

网址：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+ImportExport

export

语法：

EXPORT TABLE tablename [PARTITION (part_column="value"[, ...])]
  TO 'export_target_path' [ FOR replication('eventid') ]

从hive中拷出表到HDFS上

hive>EXPORT TABLE emp TO '/hive_export';

HDFS上的目录：
1.元数据信息 为JSON格式的数据 好比schema信息，下次import导入到Hive的时候有用
/hive_export/_metadata

2.为一个文件夹
/hive_export/data

import

语法：

IMPORT [[EXTERNAL] TABLE new_or_original_tablename [PARTITION (part_column="value"[, ...])]]
      FROM 'source_path'
      [LOCATION 'import_target_path']
    Replication usage

hive>IMPORT TABLE emp_imported from '/hive_export'
hive>select * from emp_imported;