HiveQL 常用操作

最新推荐文章于 2024-01-13 18:01:10 发布
最新推荐文章于 2024-01-13 18:01:10 发布
阅读量581 收藏
点赞数
分类专栏： 大数据 文章标签： 大数据 hive

HiveQL 常用操作

 

1.   创建表

Create Table dept (deptno Int,dname String) Row format delimited fields terminated By'\t';
Create Table emp (empno Int,ename String,mgr Int,sal Float,deptno Int) Row format delimited fields terminated By'\t';
Create Table salgrade (grade Int,losal Int,hisal Int) Row format delimited fields terminated By'\t';

 

Hive中分托管表和外部表，以上是托管表，托管表在在数据仓库目录下，由hive管理。外部表的数据在指定位置，不在hive数据仓库中，只在元数据库中注册。

 

创建外部表：

Create External Table ext(Id Int,Name String);

 

2.   导入数据

hive>load Data Local inpath '/home/licz/data/dept' overwrite Into Table dept;

Copying data from file:/home/licz/data/dept

Copying file: file:/home/licz/data/dept

Loading data to table default.dept

Deleted hdfs://gc:9000/user/hive/warehouse/dept

Table default.dept stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 50, raw_data_size: 0]

OK

Time taken: 1.547 seconds

hive>select * from dept;

OK

10     ACCOUNTING

20     RESEARCH

30     SALES

40     OPERATIONS

Time taken: 0.184 seconds, Fetched: 4 row(s)

 

load Data Local inpath '/home/licz/data/emp' overwrite Into Table emp;
load Data Local inpath '/home/licz/data/salgrade' overwrite Into Table salgrade;

 

如果导入的数据在HDFS上，则不需要加local关键字

 

--查看托管表位置

[licz@gc data]$ Hadoop dfs -ls /user/hive/warehouse

Found 4 items

drwxr-xr-x  - licz supergroup          0 2013-12-16 13:44 /user/hive/warehouse/dept

drwxr-xr-x  - licz supergroup          0 2013-12-16 13:42 /user/hive/warehouse/emp

drwxr-xr-x  - licz supergroup          0 2013-12-16 13:07 /user/hive/warehouse/ext

drwxr-xr-x  - licz supergroup          0 2013-12-16 13:38 /user/hive/warehouse/salgrade

 

Hive导入数据时只是复制和移动文件，并不对数据模式进行检查，所以下面操作同样也成功了。这是Hive采用的“schema on read”加载方式，可以提高加载数据的效率。

hive> load Data Local inpath '/home/licz/data/salgrade' overwrite Into Tableemp;    

Copying data from file:/home/licz/data/salgrade

Copying file: file:/home/licz/data/salgrade

Loading data to table default.emp

Deleted hdfs://gc:9000/user/hive/warehouse/emp

Table default.emp stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 59, raw_data_size: 0]

OK

Time taken: 2.118 seconds

hive> select * from emp;

OK

1      700     1200    NULL    NULL

2      1201    1400    NULL    NULL

3      1401    2000    NULL    NULL

4      2001    3000    NULL    NULL

5      3001    9999    NULL    NULL

 

尽管导入数据和表结构不一致，还是导入成功了

 

 

3.   分区

--创建分区表

//注意：table中的列不能和partition中的列重合了

hive> Create Table ptest(ename String) partitioned By (deptno Int) Row format delimited fields terminated By '\t';

OK

Time taken: 0.271 seconds

hive> desc ptest;

OK

ename                  string                  None               

deptno                 int                     None               

                

# Partition Information         

# col_name             data_type               comment            

                

deptno                 int                     None               

Time taken: 0.231 seconds, Fetched: 7 row(s)

 

导入分区表数据

hive>load Data Local inpath '/home/licz/data/ptest20' overwrite Into Table ptest Partition(deptno=20);

Copying data from file:/home/licz/data/ptest20

Copying file: file:/home/licz/data/ptest20

Loading data to table default.ptest partition (deptno=20)

Deleted hdfs://gc:9000/user/hive/warehouse/ptest/deptno=20

Partition default.ptest{deptno=20} stats: [num_files: 1, num_rows: 0, total_size: 29, raw_data_size: 0]

Table default.ptest stats: [num_partitions: 1, num_files: 1, num_rows: 0, total_size: 29, raw_data_size: 0]

OK

Time taken: 2.209 seconds

hive> load Data Local inpath '/home/licz/data/ptest30' Into Table ptest Partition(deptno=30);

hive> select * from ptest;

OK

SMITH  20

JONES  20

SCOTT  20

ADAMS  20

FORD   20

ALLEN  30

WARD   30

MARTIN  30

BLAKE  30

TURNER 30

JAMES  30

Time taken: 0.364 seconds, Fetched: 11 row(s)

 

创建分区后，会在相应的目录下建立以分区命名的目录，目录下是分区的数据

hive> dfs -ls /user/hive/warehouse/ptest;

Found 2 items

drwxr-xr-x  - licz supergroup          0 2013-12-16 14:17 /user/hive/warehouse/ptest/deptno=20

drwxr-xr-x  - licz supergroup          0 2013-12-16 14:33 /user/hive/warehouse/ptest/deptno=30

hive> dfs -ls /user/hive/warehouse/ptest/deptno=20;

Found 1 items

-rw-r--r--  2 licz supergroup         29 2013-12-16 14:17 /user/hive/warehouse/ptest/deptno=20/ptest20

hive> dfs -cat /user/hive/warehouse/ptest/deptno=20/ptest20;

SMITH

JONES

SCOTT

ADAMS

FORD

 

对分区进行查询

hive> select ename from ptest where deptno=20;

Total MapReduce jobs = 1

Launching Job 1 out of 1

Number of reduce tasks is set to 0 since there's no reduce operator

Starting Job = job_201312111332_0005, Tracking URL = http://gc:50030/jobdetails.jsp?jobid=job_201312111332_0005

Kill Command = /home/licz/hadoop-1.2.1/libexec/../bin/hadoop job -kill job_201312111332_0005

Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0

2013-12-16 14:27:58,298 Stage-1 map = 0%, reduce = 0%

……

MapReduce Total cumulative CPU time: 3 seconds 110 msec

Ended Job = job_201312111332_0005

MapReduce Jobs Launched:

Job 0: Map: 1  Cumulative CPU: 3.11 sec   HDFS Read: 241 HDFS Write: 29 SUCCESS

Total MapReduce CPU Time Spent: 3 seconds 110 msec

OK

SMITH

JONES

SCOTT

ADAMS

FORD

Time taken: 61.54 seconds, Fetched: 5 row(s)

 

显示分区

hive> show partitions ptest;

OK

deptno=20

deptno=30

Time taken: 0.221 seconds, Fetched: 2 row(s)

 

对分区插入数据

hive> Insert overwrite Table ptest Partition(deptno=20) Select ename From emp Where deptno=20;

Total MapReduce jobs = 3

Launching Job 1 out of 3

……

5 Rows loaded to ptest

MapReduce Jobs Launched:

Job 0: Map: 1  Cumulative CPU: 3.24 sec   HDFS Read: 568 HDFS Write: 29 SUCCESS

Total MapReduce CPU Time Spent: 3 seconds 240 msec

OK

Time taken: 68.34 seconds

 

4.   桶

可以的表或分区组织成桶，桶是用组织特定字段把行分开，每个桶对应一个reduce操作。在建立桶之前，需要设置hive.enforce.bucketing属性为true,使hive能识别桶。

hive> Create Table bemp(empno Int,ename String,mgr Int,sal Float,deptno Int)

   > clustered By (empno) Into 3 buckets

   > Row format delimited fields terminated By '\t';

OK

 

向桶中插入数据，按empno分了三个桶，在插入数据时对应三个reduce操作，输出三个文件

hive> Insert overwrite Table bemp Select * From emp;

Total MapReduce jobs = 1

Launching Job 1 out of 1

Number of reduce tasks determined at compile time: 3

In order to change the average load for a reducer (in bytes):

 set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

 set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

 set mapred.reduce.tasks=<number>

Starting Job = job_201312111332_0011, Tracking URL = http://gc:50030/jobdetails.jsp?jobid=job_201312111332_0011

Kill Command = /home/licz/hadoop-1.2.1/libexec/../bin/hadoop job -kill job_201312111332_0011

Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 3

2013-12-17 01:14:04,048 Stage-1 map = 0%, reduce = 0%

2013-12-17 01:14:29,182 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 8.5 sec

……

Ended Job = job_201312111332_0011

Loading data to table default.bemp

Deleted hdfs://gc:9000/user/hive/warehouse/bemp

Table default.bemp stats: [num_partitions: 0, num_files: 3, num_rows: 0, total_size: 360, raw_data_size: 0]

14 Rows loaded to bemp

MapReduce Jobs Launched:

Job 0: Map: 1 Reduce: 3   Cumulative CPU: 42.03 sec   HDFS Read: 568 HDFS Write: 360 SUCCESS

Total MapReduce CPU Time Spent: 42 seconds 30 msec

OK

Time taken: 116.398 seconds

 

查看数据仓库下的桶目录，三个桶对应三个文件

hive> dfs -ls /user/hive/warehouse/bemp

   > ;

Found 3 items

-rw-r--r--  2 licz supergroup        177 2013-12-17 01:15 /user/hive/warehouse/bemp/000000_0

-rw-r--r--  2 licz supergroup        103 2013-12-17 01:15 /user/hive/warehouse/bemp/000001_0

-rw-r--r--  2 licz supergroup         80 2013-12-17 01:15 /user/hive/warehouse/bemp/000002_0

hive> dfs -ls /user/hive/warehouse/bemp;

Found 3 items

-rw-r--r--  2 licz supergroup        177 2013-12-17 01:15 /user/hive/warehouse/bemp/000000_0

-rw-r--r--  2 licz supergroup        103 2013-12-17 01:15 /user/hive/warehouse/bemp/000001_0

-rw-r--r--  2 licz supergroup         80 2013-12-17 01:15 /user/hive/warehouse/bemp/000002_0

hive> dfs -ls /user/hive/warehouse/bemp/000000_0;

Found 1 items

-rw-r--r--  2 licz supergroup        177 2013-12-17 01:15 /user/hive/warehouse/bemp/000000_0

 

Hive使用对分桶所用的值进行hash，并用hash结果除以桶的个数取余运算的方式来分桶，保证每个桶里都有数据，但每个桶中的记录不一定相等。

hive> dfs -cat /user/hive/warehouse/bemp/000000_0;

7788   SCOTT   7566    3000.0  20

7839   KING    \N      5000.0  10

7521   WARD    7698    1250.0  30

7566   JONES   7839    2975.0  20

7902   FORD    7566    3000.0  20

7698   BLAKE   7839    2850.0  30

7782   CLARK   7839    2450.0  10

hive> dfs -cat /user/hive/warehouse/bemp/000001_0;

7369   SMITH   7902    800.0   20

7654   MARTIN  7698    1250.0  30

7876   ADAMS   7788    1100.0  20

7900   JAMES   7698    950.0   30

hive> dfs -cat /user/hive/warehouse/bemp/000002_0;

7934   MILLER  7782    1300.0  10

7844   TURNER  7698    1500.0  30

7499   ALLEN   7698    1600.0  30

 

分桶可以获得比分区更高的查询效率，同时分权也便于对全部数据数据进行采样，如下取样操作

hive> select * from bemp tablesample(bucket 1 out of 3 on empno);

Total MapReduce jobs = 1

Launching Job 1 out of 1

Number of reduce tasks is set to 0 since there's no reduce operator

……

Total MapReduce CPU Time Spent: 6 seconds 110 msec

OK

7788   SCOTT   7566    3000.0  20

7839   KING    NULL    5000.0  10

7521   WARD    7698    1250.0  30

7566   JONES   7839    2975.0  20

7902   FORD    7566    3000.0  20

7698   BLAKE   7839    2850.0  30

7782   CLARK   7839    2450.0  10

Time taken: 57.213 seconds, Fetched: 7 row(s)

 

 

 

5.   多表插入

多表插入是指使用一条语句，把读取的同一份数据插入到不同的表中，只需要扫描一遍数据即可完成所有表的插入操作，效率很高。

hive> Create Table mutil1 As Select deptno,ename From emp;

hive> Create Table mutil2 Like mutil1;

OK

Time taken: 0.285 seconds

 

hive> From emp

   > Insert overwrite Table mutil1 Select deptno,ename

> Insert overwrite Table mutil2 Select deptno,Count(ename) Group By deptno;

 

hive> select * from mutil1;

OK

20     SMITH

30     ALLEN

30     WARD

20     JONES

30     MARTIN

30     BLAKE

10     CLARK

20     SCOTT

10     KING

30     TURNER

20     ADAMS

30     JAMES

20     FORD

10     MILLER

Time taken: 0.502 seconds, Fetched: 14 row(s)

hive> select * from mutil2;

OK

10     3

20     5

30     6

Time taken: 0.149 seconds, Fetched: 3 row(s)

 

6.   修改表

重命名、增加字段

hive> alter table mutil1 rename to mutil01;

hive> alter table mutil01 add columns(sal int);

OK

Time taken: 0.262 seconds

hive> desc mutil01;

OK

deptno                 int                     None               

ename                  string                  None               

sal                    int                     None               

Time taken: 0.154 seconds, Fetched: 3 row(s)

 

是不是跟PL/SQL一样！！

 

7.   删除表

 

hive> drop table mutil01;

 

如果只删除表中的数据，保留表名可以HDFS上删除数据文件即可，如下：

hive> select * from mutil2;

OK

10     3

20     5

30     6

hive> dfs -ls /user/hive/warehouse/mutil2;

Found 1 items

-rw-r--r--  2 licz supergroup         15 2013-12-17 02:34 /user/hive/warehouse/mutil2/000000_0

hive> dfs -cat /user/hive/warehouse/mutil2/000000_0;

103

205

306

hive> dfs -rmr /user/hive/warehouse/mutil2/*;

Deleted hdfs://gc:9000/user/hive/warehouse/mutil2/000000_0

hive> select * from mutil2;

OK

Time taken: 0.269 seconds

hive>

 

注意：对于托管表，drop操作会把元数据和数据文件都删除掉；对于外部表，只是删除元数据。

 

8.   连接

Hive的连接操作和PL/SQL的内连接、左外连接、右外连接、全外连接基本上是一样的。

hive> select dept.*,emp.* from dept join emp on dept.deptno=emp.deptno;

10     ACCOUNTING      7839    KING    NULL    5000.0  10

10     ACCOUNTING      7782    CLARK   7839    2450.0  10

10     ACCOUNTING      7934    MILLER  7782    1300.0  10

20     RESEARCH        7369    SMITH   7902    800.0   20

20     RESEARCH        7566    JONES   7839    2975.0  20

20     RESEARCH        7876    ADAMS   7788    1100.0  20

20     RESEARCH        7902    FORD    7566    3000.0  20

20     RESEARCH        7788    SCOTT   7566    3000.0  20

30     SALES   7499    ALLEN   7698    1600.0  30

30     SALES   7844    TURNER  7698    1500.0  30

30     SALES   7900    JAMES   7698    950.0   30

30     SALES   7698    BLAKE   7839    2850.0  30

30     SALES   7654    MARTIN  7698    1250.0  30

30     SALES   7521    WARD    7698    1250.0  30

40     OPERATIONS      NULL    NULL    NULL    NULL    NULL

Time taken: 118.992 seconds, Fetched: 15 row(s)

hive> select dept.*,emp.* from dept left outer join emp on dept.deptno=emp.deptno;

hive> select dept.*,emp.* from dept right outer join emp on dept.deptno=emp.deptno;

hive> select dept.*,emp.* from dept full outer join emp on dept.deptno=emp.deptno;

//outer必需要显示地存在

 

半连接是Hive所特有的，Hive不支持in操作，替代方案是用left semi join半连接，需要注意的是连接的表不能出现在查询列中，只能出现在on子名中。

hive> select dept.* from dept left semi join emp on dept.deptno=emp.deptno;

OK

10     ACCOUNTING

20     RESEARCH

30     SALES

Time taken: 119.238 seconds, Fetched: 3 row(s)

 

9.   子查询

HiveQL对子查询支持有限，只能在from引导的子句中出现子查询，如下语句在from子句中嵌套了一个子查询

hive> Select Deptno,Max(Num) From (Select Deptno, Count(Empno) Num From Emp Group By Deptno) by group by deptno;

 

10. 创建视图

Hive只支持逻辑视图，并不支持物理视图，建立的视图可以MySQL元数据库查到，但在hive的数据仓库目录下没有相应的视图表目录

hive> create view v_test as select a.dname,count(b.empno) from dept a join emp b on a.deptno=b.deptno group by a.dname;

OK

 

mysql> select database();

+------------+

| database() |

+------------+

| hive      |

+------------+

1 row in set (0.00 sec)

 

mysql> select tbl_name from TBLS;

+---------------------+

| tbl_name           |

+---------------------+

| access_20120104_log |

| bemp               |

| dept               |

| emp                |

| ext                |

| mutil2             |

| ptest              |

| salgrade           |

| test               |

| v_test             |

+---------------------+

10 rows in set (0.00 sec)

 

我叫金角大王
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
HiveQL_UDF_函数V0.5.pdf
04-13
hiveql udf函数大合集，详细介绍函数语法、操作类型、描述和应用举例等等，非常好用，大数据开发必备
Hive的常用HiveQL操作
leoatliang的博客
10-26 2070
Hive的常用HiveQL操作
[大数据技术] Hive的常用HiveQL操作
wong_faye的博客
08-17 1339
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：无序字段 S
Hive基本使用（5）
qq_37232843的博客
07-14 786
a.在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作，distributeby子句可以做这件事。在很多情况下，并不需要全局排序，此时可以使用sortby。添加分区语法经常用到（altertable表名addpartition（分区字段=‘分区字段值’））b.distributeby类似MR中partition（自定义分区），进行分区，结合sortby使用。c.在客户端设置（非配置文件等）的时效性是本次session，会话断开，需要在设置。......
Hadoop基础教程-第11章 Hive：SQL on Hadoop（11.8 HQL：排序）（草稿）
与其临渊羡鱼 不如退而结网
07-15 238
第11章 Hive：SQL on Hadoop 11.8 HQL：排序 11.8.1 order by Hive 中的 order by与SQL 中的order by语义相同，会对查询结果进行全局排序，但是Hive 语句最终要转换为 MapReduce 程序放到 Hadoop 分布式集群上去执行，多个Mapper 后汇集到一个 Reducer 上执行，如果结果数据量大，那就会造成 Reduc...
Hive ERROR: Out of memory due to hash maps used in map-side aggregation
lixucpf的专栏
03-04 1575
在执行H-SQL:  select collect_set(messageDate)[0],count(*) from incidents_hive group by substr(messageDate,8,2); 时报以下错误： URL:   http://RDCMaster.cluster:50030/taskdetails.jsp?jobid=job_201403041024_0
HiveQL源码
02-28
HiveQL的官网中下载下来的源码. ql/src/java/org/apache/hadoop/...
PySpark SQL Recipes: With HiveQL, Dataframe and Graphframes
03-20
Carry out data analysis with PySpark SQL, graphframes, and graph data processing using a problem-solution approach. This book provides solutions to problems related to dataframes, data manipulation ...
hiveql问题.txt
02-19
hive运行前最后配置工作
Hive体系架构、安装与HiveQL
01-14
Hive体系架构、安装与HiveQL，Hive体系架构、安装与HiveQL，Hive体系架构、安装与HiveQL
【异常问题】Hive中SQL执行mapreduce.task.timeout超时问题
Insist
12-14 1万+
【异常描述】 Task with the most failures(4):  ----- Task ID:task_1479051658015_121922_r_000000 URL:http://hnn01.ns4f.hi.ipm.nokia.com:8088/taskdetails.jsp?jobid=job_1479051658015_121922&tipid=task_14790
记录一次hive优化的全过程
Bryce_Loski的博客
10-28 396
记录一次hive优化的全过程 因为涉及公司隐私，本文所有的sql被精简化，加上脱敏处理。 第一版的sql： set hive.exec.parallel=true; set mapred.max.split.size=134217728; set mapred.min.split.size.per.node=134217728; set mapred.min.split.size.per.rack=134217728; set hive.exec.reducers.bytes.per.reducer=10
HiveSQL分区的作用及创建分区表案例演示(图解)
最新发布
HGl1327401792的博客
01-13 1536
本篇文章主要介绍HiveSQL分区表的作用和如何创建单级分区表和多级分区表以及查询规则等.
数据仓库Hive编程——HiveQL的数据定义（三）：创建表
热门推荐
冯·诺依曼
08-08 1万+
CREATE TABLE语句遵从SQL语法惯例，但是Hive的这个语句中具有显著的功能扩展，使其可以具有更广泛的灵活性。例如，可以定义表的数据文件存储在什么位置、使用什么样的存储格式，等等。前面的文章我们在”中已经讨论了很多种存储格式，同时在后续的文章中我们将会再次探讨一下更加高级的格式。本文中，我们会讨论其他一些在CREATE TABLE ...
Hive综合案例实战
笑出弧度的程序员的博客
10-25 1084
一 、数据源的准备工作 首先我们去一个网站下载相关的数据,之后通过hive导入进行实验.http://grouplens.org/ 二 、内部表 创建内部表并载入数据 hadoop@hadoopmaster:~$ beeline-u jdbc:hive2://hadoopmaster:10000/ Beelineversion2.1.0byApacheHive 0:jdbc:hive2://hadoopmaster:10000/> show databases;
Hive常用HiveQL操作
cosuyo的博客
11-07 387
1.创建数据库 create database if not exists test; #如果test数据库不存在则创建 2.查看Hive中数据库 show databases; show databases like 'h.*'; #查看Hive中以h开头数据库 3.切换数据库 use test; ...
Hive学习笔记：03Hive的数据类型与HiveQL的常用操作
07-12 399
Hive的SQL“方言被称为”HiveQL，他是SQL-92、MYSQL和Oracle SQL语言的混合体。所以很多操作都和传统的数据库的操作非常类似，尤其是MySQL。Hive的基本类型和MySQL数据库的类型基本一致，但是也有些细微的区别： HiveQL 数据库DATABASE/SCHEMA Hive中也有数据库的概念，使用HiveQL的操作如下： 示例 删除数据库 语法： 示例： 查看数据库 切换数据库 语法： use + 数据库名称 示例： 表的DDL操作 创建表 语法：
hive命令行操作与关系运算
05-10
以下是一些常用的命令行操作： 1. 创建表 ``` CREATE TABLE table_name (column1 datatype, column2 datatype, column3 datatype, …) ``` 2. 加载数据 ``` LOAD DATA LOCAL INPATH 'path/to/data' OVERWRITE ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值