Hive DML&分区表

1.INSERT

官网说明：

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> create table ruozedata_emp4 like ruozedata_emp;

hive> INSERT OVERWRITE TABLE ruozedata_emp4 select * FROM ruozedata_emp;

hive> select * from ruozedata_emp4;

hive> INSERT INTO TABLE ruozedata_emp4 select * FROM ruozedata_emp;

hive> select * from ruozedata_emp4;

数据被追加进去

上边两个命令都是select * ，两表都是一一对应的，如果insert的表的列数与原表不等或者列的顺序与原表不同会怎样呢？

hive> INSERT OVERWRITE TABLE ruozedata_emp4

    > SELECT empno,ename from ruozedata_emp;

列的数量不同无法插入

hive> INSERT INTO TABLE ruozedata_emp4

    > select empno,job, ename,mgr, hiredate, salary, comm, deptno from ruozedata_emp;

（列的顺序与原表不同）

hive> select * from ruozedata_emp4;

数据混乱

2.Writing data into the filesystem from queries

官网说明：

INSERT OVERWRITE [LOCAL] DIRECTORY directory1

[ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive0.11.0)

  SELECT ... FROM ...

3.where语句

hive> select * from ruozedata_emp where deptno=10;

hive> select * from ruozedata_emp where empno>=8000;

hive> select * from ruozedata_emp where salary between 800 and 1500;（左闭右闭）

hive> select * from ruozedata_emp limit 5;

hive> select * from ruozedata_emp where ename in ("SMITH","KING");

hive> select * from ruozedata_emp where ename not in ("SMITH","KING");

hive> select * from ruozedata_emp where comm is null;

4.聚合函数

max/min/count/sum/avg   特点：多进一出

hive> select count(1) from ruozedata_emp where deptno=10;

hive>select max(salary), min(salary), avg(salary), sum(salary) from ruozedata_emp;

5.分组函数 group by

1）

hive> select deptno,avg(salary) from ruozedata_emp group by deptno;（求部门平均工资）

hive> select ename,deptno,avg(salary) from ruozedata_emp group by deptno;

FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key 'ename'

报错原因：select中出现的字段，如果没有出现在组函数/聚合函数中，必须出现在group by里面

2）求每个部门(deptno)、工作岗位(job)的最高工资(salary)

hive> select deptno,job,max(salary) from ruozedata_emp group by deptno,job;

3）求平均工资大于2000的部门

hive> select deptno,avg(salary) from ruozedata_emp group by deptno having avg(salary)>2000;

不能用where，对于分组之后的结果进行筛选只能用having；where是用来对单条数据进行筛选的，而且where需要写在group by之前的

4）case when then

select ename,salary,

case

when salary>1 and salary<=1000 then 'lower'

when salary>1000 and salary<=2000 then 'middle'

when salary>2000 and salary<=4000 then 'high'

else 'highest'

end

from ruozedata_emp;

5）join

hive> select * from a join b;

笛卡尔积，非常耗性能，一定要规避

inner join=join

outer join：left join，right join，full join

hive> select a.id,a.name,b.age from a join b on a.id=b.id;

hive> select a.id,a.name,b.age from a left join b on a.id=b.id;

hive> select a.id,a.name,b.age from a full join b on a.id=b.id;

6.分区表   partition

1）静态分区

hive> create table order_partition(

    > ordernumber string,

    > eventtime string

    > )

    > partitioned by (event_month string)

    > row format delimited fields terminated by '\t';

hive> LOAD DATA LOCAL INPATH '/home/hadoop/data/order.txt'

    > OVERWRITE INTO TABLE order_partition

    > PARTITION(event_month='2014-05');

hive> select * from order_partition;

OK

10703007267488  2014-05-01      2014-05

10101043505096  2014-05-01      2014-05

10103043509747  2014-05-01      2014-05

10103043501575  2014-05-01      2014-05

10104043514061  2014-05-01      2014-05

hive> desc formatted order_partition;

[hadoop@hadoop001 data]$ hdfs dfs -ls hdfs://192.168.137.141:9000/user/hive/warehouse/ruozedata.db/order_partition

[hadoop@hadoop001 data]$ hdfs dfs -ls hdfs://192.168.137.141:9000/user/hive/warehouse/ruozedata.db/order_partition/event_month=2014-05

hive> select * from order_partition where event_month='2014-05';

分区查询的时候务必要记得带上分区

添加分区的方法

a）以上是通过建表的时候直接分区，hdfs里显示相应的分区会有相应的文件夹/order_partition/event_month=2014-05，那么可不可以通过直接在hdfs里新建一个分区文件夹来进行分区呢？

[hadoop@hadoop001 data]$ hdfs dfs -mkdir /user/hive/warehouse/ruozedata.db/order_partition/event_month=2014-06

[hadoop@hadoop001 data]$ hdfs dfs -put order.txt /user/hive/warehouse/ruozedata.db/order_partition/event_month=2014-06

新建了/event_month=2014-06文件夹，并将order.txt放入了该文件夹内

[hadoop@hadoop001 data]$ hdfs dfs -ls hdfs://192.168.137.141:9000/user/hive/warehouse/ruozedata.db/order_partition

hive> select * from order_partition where event_month='2014-06';

在hive里查询一下分区event_month=2014-06下的数据，却发现是空的：

进入mysql查看一下元数据

mysql> show databases;

mysql> use ruozedata_basic03;

mysql> show tables;

mysql> select * from partitions;

mysql> select * from partition_keys;

mysql> select * from partition_key_vals;

发现元数据里只有event_month=2014-05这一个分区，为什么呢？

查询官网的说明：

metastore没有感知到，需使用msck命令修复MySQL表的元数据信息，Hive上才能查到到相应的数据结果

hive> MSCK REPAIR TABLE order_partition;

OK

Partitions not in metastore:    order_partition:event_month=2014-06

Repair: Added partition to metastore order_partition:event_month=2014-06

再次查看mysql里的元数据，分区信息都进来了

hive> select * from order_partition where event_month='2014-06';

但是，这个命令会刷新所有的分区信息，过于简单粗暴，不建议使用

b）应该用Add Partitions

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][, PARTITION partition_spec [LOCATION 'location'], ...];

partition_spec:

  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

再来试验一下

[hadoop@hadoop001 data]$ hdfs dfs -mkdir /user/hive/warehouse/ruozedata.db/order_partition/event_month=2014-07

[hadoop@hadoop001 data]$ hdfs dfs -put order.txt /user/hive/warehouse/ruozedata.db/order_partition/event_month=2014-07

[hadoop@hadoop001 data]$ hdfs dfs -ls /user/hive/warehouse/ruozedata.db/order_partition/event_month=2014-07

Found 1 items

-rw-r--r--  1 hadoop supergroup        217 2018-06-17 21:11 /user/hive/warehouse/ruozedata.db/order_partition/event_month=2014-07/order.txt

hive> select * from order_partition where event_month='2014-07';

OK（没有数据）

使用Add Partitions命令

hive> ALTER TABLE order_partition ADD IF NOT EXISTS

    > PARTITION (event_month='2014-07') ;

hive> select * from order_partition where event_month='2014-07';

c）还有一种方法，如下：

hive> create table order_4_partition(

    > ordernumber string,

    > eventtime string

    > )

    > row format delimited fields terminated by '\t';

hive> load data local inpath '/home/hadoop/data/order.txt' overwrite into table order_4_partition;

hive> insert overwrite table order_partition

    > partition(event_month='2014-08')

    > select * from order_4_partition;

hive> select * from order_partition where event_month='2014-08';

在hive里查看当前有几个分区的方法：

hive> show partitions order_partition;

静态分区是最简单的分区，单级分区，分区与表中的字段内容没有关系，而且出现在分区内的字段内容是不能出现在表中的

d）多级分区

hive> create table order_mulit_partition(

    > ordernumber string,

    > eventtime string

    > )

    > partitioned by (event_month string,event_day string)

    > row format delimited fields terminated by '\t';

hive> desc formatted order_mulit_partition;

hive> LOAD DATA LOCAL INPATH '/home/hadoop/data/order.txt'

    > OVERWRITE INTO TABLE order_mulit_partition

    > PARTITION(event_month='2014-05', event_day='01');

hive> select * from order_mulit_partition where event_month='2014-05' and event_day='01';

查看一下在hdfs下的目录结构

2）动态分区

hive> create table ruozedata_static_emp

    > (empno int, ename string, job string, mgr int, hiredate string, salary double, comm double,deptno string)

    > PARTITIONED by(deptno string)

    > ROW FORMAT DELIMITED

    > FIELDS TERMINATED BY '\t';

FAILED: SemanticException [Error 10035]: Column repeated in partitioning columns（报错，分区字段不能出现在表中）

hive> create table ruozedata_static_emp

    > (empno int, ename string, job string, mgr int, hiredate string, salary double, comm double)

    > PARTITIONED by(deptno string)

    > ROW FORMAT DELIMITED

    > FIELDS TERMINATED BY '\t';

hive> select * from ruozedata_static_emp;

OK（里面没有数据）

将表 ruozedata_emp中deptno=10的数据写入 ruozedata_static_emp的deptno='10'的分区内

hive> insert into table ruozedata_static_emp partition(deptno='10')

    > select empno,ename,job,mgr,hiredate,salary,comm from ruozedata_emp

    > where deptno=10;

hive> select * from ruozedata_static_emp;

以上还是静态分区的方法，如果需要分区很多（比如1000个分区），这种方式太耗时间精力

所以需要动态分区

hive> create table ruozedata_dynamic_emp

    > (empno int, ename string, job string, mgr int, hiredate string, salary double, comm double)

    > PARTITIONED by(deptno string)

    > ROW FORMAT DELIMITED

    > FIELDS TERMINATED BY '\t' ;

动态分区明确要求：分区字段写在select的最后面

hive> insert into table ruozedata_dynamic_emp partition(deptno)

    > select empno,ename,job,mgr,hiredate,salary,comm,deptno from ruozedata_emp ;

hive> set hive.exec.dynamic.partition.mode=nonstrict;（只是临时设置为 nonstrict ，重新进入hive又会自动变为strict模式）

【set hive.exec.dynamic.partition.mode=nonstrict;

这是hive中常用的设置key=value的方式

语法格式：

set key=value; 设置

set key;             取值  】

hive> insert into table ruozedata_dynamic_emp partition(deptno)

    > select empno,ename,job,mgr,hiredate,salary,comm,deptno from ruozedata_emp ;

hive> show partitions ruozedata_dynamic_emp;

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 210,914评论 6赞 490
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 89,935评论 2赞 383
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 156,531评论 0赞 345
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,309评论 1赞 282
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,381评论 5赞 384
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,730评论 1赞 289
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,882评论 3赞 404
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,643评论 0赞 266
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,095评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,448评论 2赞 325
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,566评论 1赞 339
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,253评论 4赞 328
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,829评论 3赞 312
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,715评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,945评论 1赞 264
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,248评论 2赞 360
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,440评论 2赞 348

推荐阅读更多精彩内容

  • Oracle数据库
    主流关系型数据库 关系型数据库存储数据的特点 结构化查询语言： 数据类型： select * from emp; ...
    陈先森mansplain阅读 670评论 0赞 0
  • mysql——2-18数据库
    mysql数据库中 :database : 文件夹table : 数据表(数据文件) 进入mysqlmysql -...
    赋闲阅读 558评论 0赞 0
  • Oracle数据库学习二
    5.多表查询 多表查询 目的：从多张表获取数据 前提：进行连接的多张表中有共同的列 等连接 通过两个表具有相同意义...
    乔震阅读 1,209评论 0赞 0
  • 迷雾森林历险记
    第九章 他们听了老村长的话，继续往前走，走着走着，又一个奇怪的东西，跑到了他们的面前，这次...
    齐异初阅读 151评论 0赞 0
  • 夜半归
    端正如如万物生，午夜半酣赏华灯。 安然记取今朝事，康乐可期好梦成。 【2015年6月20日】
    d03e056874dc阅读 217评论 0赞 0