Hive基础之Hive表常用操作

本案例使用的数据均来源于Oracle自带的emp和dept表

创建表

语法：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name

  [(col_name data_type [COMMENT col_comment], ...)]

  [COMMENT table_comment]

  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]

  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]

  [SKEWED BY (col_name, col_name, ...) ON ([(col_value, col_value, ...), ...|col_value, col_value, ...]) [STORED AS DIRECTORIES] (Note: Only available starting with Hive 0.10.0)]

  [

   [ROW FORMAT row_format] [STORED AS file_format]

   | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] (Note: Only available starting with Hive 0.6.0)

  ]

  [LOCATION hdfs_path]

  [TBLPROPERTIES (property_name=property_value, ...)] (Note: Only available starting with Hive 0.6.0)

  [AS select_statement] (Note: Only available starting with Hive 0.5.0, and not supported when creating external tables.)

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' lines terminated by '\n'

stored as textfile;



create table dept(

deptno int,

dname string,

loc string

)

row format delimited fields terminated by '\t' lines terminated by '\n'

stored as textfile;

注：创建表时默认列分割符是\001，行分隔符是\n

加载数据到hive表

Hive操作的数据源：文件、其他表、其他数据库

1）load：加载本地/HDFS文件到hive表

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename

[PARTITION (partcol1=val1, partcol2=val2 ...)]

默认表数据存储在HDFS上的/user/hive/warehouse目录下，该目录可以在hive-site.xml中配置。

load data inpath 加载hdfs文件到hive表中；

load data local inpath 加载本地文件到hive表中；

overwrite 是否会覆盖表里已有的数据

load data local inpath '/home/spark/software/data/emp.txt' overwrite into table emp;

load data local inpath '/home/spark/software/data/dept.txt' overwrite into table dept;

2）insert：导入数据到表里/从表里导出到HDFS或者本地目录

Standard syntax:

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;

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] ...;

Standard syntax:

INSERT OVERWRITE [LOCAL] DIRECTORY directory1

  [ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0)

  SELECT ... FROM ...

Hive extension (multiple inserts):

FROM from_statement

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1

[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...

3）sqoop: 关系型数据库和HDFS文件导入/导出操作

详见sqoop章节介绍。

select操作

select * from emp;

7369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    20

7499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   30

7521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   30

7566    JONES   MANAGER 7839    1981-4-2        2975.0  NULL    20

7654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  30

7698    BLAKE   MANAGER 7839    1981-5-1        2850.0  NULL    30

7782    CLARK   MANAGER 7839    1981-6-9        2450.0  NULL    10

7788    SCOTT   ANALYST 7566    1987-4-19       3000.0  NULL    20

7839    KING    PRESIDENT       NULL    1981-11-17      5000.0  NULL    10

7844    TURNER  SALESMAN        7698    1981-9-8        1500.0  0.0     30

7876    ADAMS   CLERK   7788    1987-5-23       1100.0  NULL    20

7900    JAMES   CLERK   7698    1981-12-3       950.0   NULL    30

7902    FORD    ANALYST 7566    1981-12-3       3000.0  NULL    20

7934    MILLER  CLERK   7782    1982-1-23       1300.0  NULL    10



select * from dept;

10      ACCOUNTING      NEW YORK

20      RESEARCH        DALLAS

30      SALES   CHICAGO

40      OPERATIONS      BOSTON

where使用

select * from emp where deptno =10;

7782    CLARK   MANAGER 7839    1981-6-9        2450.0  NULL    10

7839    KING    PRESIDENT       NULL    1981-11-17      5000.0  NULL    10

7934    MILLER  CLERK   7782    1982-1-23       1300.0  NULL    10

select * from emp where deptno <>10;

7369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    20

7499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   30

7521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   30

7566    JONES   MANAGER 7839    1981-4-2        2975.0  NULL    20

7654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  30

7698    BLAKE   MANAGER 7839    1981-5-1        2850.0  NULL    30

7788    SCOTT   ANALYST 7566    1987-4-19       3000.0  NULL    20

7844    TURNER  SALESMAN        7698    1981-9-8        1500.0  0.0     30

7876    ADAMS   CLERK   7788    1987-5-23       1100.0  NULL    20

7900    JAMES   CLERK   7698    1981-12-3       950.0   NULL    30

7902    FORD    ANALYST 7566    1981-12-3       3000.0  NULL    20

select * from emp where ename ='SCOTT';

7788    SCOTT   ANALYST 7566    1987-4-19       3000.0  NULL    20

select ename,sal from emp where sal between 800 and 1500;

SMITH   800.0

WARD    1250.0

MARTIN  1250.0

TURNER  1500.0

ADAMS   1100.0

JAMES   950.0

MILLER  1300.0

limit使用

select * from emp limit 4;

7369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    20

7499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   30

7521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   30

7566    JONES   MANAGER 7839    1981-4-2        2975.0  NULL    20

(not) in使用

select ename,sal,comm from emp where ename in ('SMITH','KING');

SMITH   800.0   NULL

KING    5000.0  NULL

select ename,sal,comm from emp where ename not in ('SMITH','KING');

ALLEN   1600.0  300.0

WARD    1250.0  500.0

JONES   2975.0  NULL

MARTIN  1250.0  1400.0

BLAKE   2850.0  NULL

CLARK   2450.0  NULL

SCOTT   3000.0  NULL

TURNER  1500.0  0.0

ADAMS   1100.0  NULL

JAMES   950.0   NULL

FORD    3000.0  NULL

MILLER  1300.0  NULL

is (not) null使用

select ename,sal,comm from emp where comm is null;

SMITH   800.0   NULL

JONES   2975.0  NULL

BLAKE   2850.0  NULL

CLARK   2450.0  NULL

SCOTT   3000.0  NULL

KING    5000.0  NULL

ADAMS   1100.0  NULL

JAMES   950.0   NULL

FORD    3000.0  NULL

MILLER  1300.0  NULL

select ename,sal,comm from emp where comm is not null;

ALLEN   1600.0  300.0

WARD    1250.0  500.0

MARTIN  1250.0  1400.0

TURNER  1500.0  0.0

order by的使用

与关系型数据库的order by功能一致，按照某个字段或某几个字段排序输出；

与关系型数据库区别在于：当hive.mapred.mode=strict模式下，必须指定limit否则执行报错；

hive.mapred.mode默认值为nonstrict；

select * from dept;

10      ACCOUNTING      NEW YORK

20      RESEARCH        DALLAS

30      SALES   CHICAGO

40      OPERATIONS      BOSTON

select * from dept order by deptno desc;

40      OPERATIONS      BOSTON

30      SALES   CHICAGO

20      RESEARCH        DALLAS

10      ACCOUNTING      NEW YORK

select ename,sal,deptno from emp order by deptno asc,ename desc;

MILLER  1300.0  10

KING    5000.0  10

CLARK   2450.0  10

SMITH   800.0   20

SCOTT   3000.0  20

JONES   2975.0  20

FORD    3000.0  20

ADAMS   1100.0  20

WARD    1250.0  30

TURNER  1500.0  30

MARTIN  1250.0  30

JAMES   950.0   30

BLAKE   2850.0  30

ALLEN   1600.0  30

set hive.mapred.mode=strict;

select * from emp order by empno desc;

报错：FAILED: SemanticException 1:27 In strict mode, if ORDER BY is specified, LIMIT must also be specified. Error encountered near token 'empno'

正确写法：

select * from emp order by empno desc limit 4;

7934    MILLER  CLERK   7782    1982-1-23       1300.0  NULL    10

7902    FORD    ANALYST 7566    1981-12-3       3000.0  NULL    20

7900    JAMES   CLERK   7698    1981-12-3       950.0   NULL    30

7876    ADAMS   CLERK   7788    1987-5-23       1100.0  NULL    20

为什么会报错呢？

在order by状态下所有数据会分发到一个节点上进行reduce操作也就只有一个reduce作业，如果在数据量大的情况下会出现无法输出结果的情况，如果进行limit n，那就只有n*map数个记录而已，只有一个reduce也可以处理的过来。

select嵌套查询、别名

from(select ename, sal from emp) e

select e.ename, e.sal

where e.sal>1000;

等价于

select ename, sal from emp where sal>1000;

ALLEN   1600.0

WARD    1250.0

JONES   2975.0

MARTIN  1250.0

BLAKE   2850.0

CLARK   2450.0

SCOTT   3000.0

KING    5000.0

TURNER  1500.0

ADAMS   1100.0

FORD    3000.0

MILLER  1300.0

组函数：max(), min(), avg(), sum(), count()等

select count(*) from emp where deptno=10;

3

select count(ename) from emp where deptno=10;  #count某个字段,如果这个字段不为空就算一个.

3

select count(distinct deptno) from emp;

3

select sum(sal) from emp;

29025.0

group by的使用

出现在select中的字段,如果没出现在组函数中,必须出现在Group by语句中

求每个部门的平均薪水：

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

10      2916.6666666666665

20      2175.0

30      1566.6666666666667

求每个部门中每个工作最高的薪水：

select deptno,job,max(sal) from emp group by deptno,job;

10      CLERK   1300.0

10      MANAGER 2450.0

10      PRESIDENT       5000.0

20      ANALYST 3000.0

20      CLERK   1100.0

20      MANAGER 2975.0

30      CLERK   950.0

30      MANAGER 2850.0

30      SALESMAN        1600.0

having的使用

对分组结果筛选，后跟聚合函数，hive0.11版本之后才支持；where是对单条纪录进行筛选,Having是对分组结果进行筛选。

求每个部门的平均薪水大于2000的部门：

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

2916.6666666666665      10

2175.0  20

having是hive0.11后才支持的，如果不使用having而想达到having一样的功能，语句如何写？

select deptno, e.avg_sal from (select deptno, avg(sal) as avg_sal from emp group by deptno) e where e.avg_sal > 2000;

CASE...WHEN..THEN使用

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;

SMITH   800.0   LOWER

ALLEN   1600.0  MIDDLE

WARD    1250.0  MIDDLE

JONES   2975.0  HIGH

MARTIN  1250.0  MIDDLE

BLAKE   2850.0  HIGH

CLARK   2450.0  HIGH

SCOTT   3000.0  HIGH

KING    5000.0  HIGHEST

TURNER  1500.0  MIDDLE

ADAMS   1100.0  MIDDLE

JAMES   950.0   LOWER

FORD    3000.0  HIGH

MILLER  1300.0  MIDDLE

巴特西

Hive基础之Hive表常用操作

最新文章

热门文章