Hive操作大全（原创）

1、建库

create database mydb;

create database if not exists mydb;

create database if not exists mydb location "/aa/bb";

2、查询数据库

查询库列表：show databases;

查询库详细信息：desc database [extended] mydb;

查询建库的详细信息：show create database mydb;

3、删除数据库

drop database mydb;

drop database if exists mydb; // 删除一个空库

drop database if exists mydb [restrict|cascade]; // 删除带有表的库的时候，必须后面加cascade

4、先进入我们要操作的数据库/切换库

use mydb; // 切换库

select current_database(); // 查询正在使用的库

5、查看数据库里面的表

show tables;

show tables in mydb;

6、添加表

两种不同的分类方式：

第一大类：数据存储目录来定

内部表和外部表

第二大类：按照数据的管理方式

分区表和分桶表

内部表和外部表的区别

内部表和外部表的选择

创建内部表（Managered_Table）

create table mingxing_mng(id int, name string, sex string, age int, department string) row format delimited fields terminated by ',';

show create table mingxing_mng;

创建外部表（External_Table）

create external table mingxing_ext(id int, name string, sex string, age int, department string) row format delimited fields terminated by ',' location '/home/hadoop/hivedata';

注意：创建外部表的时候指定location的位置必须是目录，不能是单个文件

跟内部表对比：

1、在创建表的时候指定关键字： external

2、一般来说，创建外部表，都需要指定一个外部路径

不管是创建外部表还是内部表，都可以指定数据存储目录

默认的目录：

hdfs://hadoop02:9000/user/hive/warehouse/myhive.db/student/student.txt

创建分区表

create table mingxing_ptn(id int, name string, sex string, age int, department string) partitioned by (city string) row format delimited fields terminated by ',';

注意：partitioned里的字段不是能是表中声明的字段,，必须是一个新字段

表字段

分区字段

创建分桶表

create table mingxing_bck(id int, name string, sex string, age int, department string) clustered by(id) sorted by(age desc) into 4 buckets row format delimited fields terminated by ',';

注意：clustered里的字段必须要是表字段中出现的字段

分桶字段

表字段

分桶字段和排序字段可以不一样，分桶字段和排序字段都必须是表字段中的一部分

分桶的原理和MapReduce的HashPartitioner的原理一致

创建表和查询语句当中的分区和分桶等等的关键字的识记：

创建表：关键字后面都加了ed

查询表： distribute by cluster by

7、删除表

drop table mingxing;

drop table if exists mingxing;

8、对表进行重命名

alter table mingxing rename to student;

9、对表的字段进行操作（增加add，删除drop，修改change，替换replace）

增加字段：

alter table mingxing add columns (province string);

alter table mingxing add columns (province string, salary bigint);

// 添加的字段的都是这个表的最后几个字段

删除字段：

drop（不支持） XXXXX

修改字段：

alter table mingxing change age newage string; // 修改字段的定义

alter table mingxing change age newage string after id; // 修改字段的定义 + 顺序

alter table mingxing change age newage string first; // 修改age字段为第一个字段

如果这个字段是string类型，不能改成int类型 hive-2.3.2中

如果这个字段是int类型，可以改成string类型

替换字段

alter table mingxing replace columns(id int, name string, sex string); // 替换所有字段

10、对表中的分区进行操作

直接往一个分区表中的一个不存在的分区导入数据；

load data local inpath "/home/hadoop/hive.txt" into table mingxing_ptn partition(city="AA");

增加分区：

alter table mingxing_ptn add partition(city='beijing');

alter table mingxing_ptn add partition(city='chongqing') partition(city='tianjin');

alter table mingxing_ptn add partition(city='beijing', email="abc@163.com");

alter table mingxing_ptn add partition(city='beijing', email="abc@163.com") partition(email="cba@163.com",city='tianjin');

// 对应的HDFS上的数据存储目录的表现形式：多级文件夹的嵌套的形式

删除分区：

alter table mingxing_ptn drop partition(city='beijing');

alter table mingxing_ptn drop partition(city='chongqing'), partition(city='tianjin');

alter table mingxing_ptn add partition(city='beijing', email="abc@163.com");

alter table mingxing_ptn add partition(city='beijing', email="abc@163.com"), partition(city='tianjin', email="cba@163.com");

修改分区路径：

alter table mingxing_ptn partition(city="beijing") set location "/mingxing_beijing";

11、查询显示命令

查看库：show databases;

查看表：show tables;

查看建表完整语法：show create table mingxing_mng;

查看内置函数库：show functions;

查看函数的详细手册：desc function extended concat;

查看分区：show partitions mingxing_ptn;

查看表的字段：desc mingxing_mng;

查看表的详细信息：desc extended mingxing_mng;

查看表的格式化了之后的详细信息：desc formatted mingxing_mng;

12、load方式导入数据

导入本地相对路径的数据

load data local inpath './student.txt' into table mingxing;

load data local inpath './student.txt' into table mingxing_ptn partition(city="AA");

load data local inpath './student.txt' overwrite into table mingxing;

(覆盖导入)

导入本地绝对路径数据：

load data local inpath '/home/hadoop/hivedata/student.txt' into table mingxing;

导入HDFS上的简便路径数据：

load data inpath '/home/hadoop/hivedata/student.txt' into table mingxing;

导入HDFS上的全路径模式下的数据：

load data inpath 'hdfs://hadoop01:9000/home/hadoop/hivedata/student.txt' into table mingxing;

导入本地数据和导入HDFS上的数据的区别：

1、导入HDFS上的数据到hive表，表示截切，移动

2、导入本地数据，相当于复制或者上传

13、利用insert关键字往表中插入数据

5种：

单条数据插入：

insert into table mingxing values(001,'huangbo','male',50,'MA');

insert into table mingxing values(001,'huangbo','male',50,'MA') (002,'huangbo','male',50,'MA');

单重插入模式： insert ... select ....

// 执行原理：就是把select查询的结果插入到insert语句中的表中， select查询的所有字段，必须都在insert后面的表中都有。

insert into table student select id,name,sex,age,department from mingxing;

注意：查询出的字段必须是student表中存在的字段

多重插入模式：

from mingxing

insert into table student1 select id,name,sex,age

insert into table student2 select id,department;

from mingxing2

insert into table student1 partition(department='MA') select id,name,sex ,age where department='MA'

insert into table student1 partition(department='CS') select id,name,sex ,age where department='CS';

优点：如果有多个同类型的SQL语句，那么完全可以合并成一个多重模式的SQL

可以减少这个查询操作的扫描数据的次数，从而来提高这个SQL语句的执行效率

多重模式，能用则用。！！

静态分区插入：

需要手动的创建分区

alter table student add partition (city="zhengzhou")

load data local inpath '/root/hivedata/student.txt' into table student partition(city='zhengzhou');

// 可以往一个不存在的分区导入数据

动态分区插入：

概念：按照你所指定的分区字段的值来进行自动判断，每一个不同的值就会创建一个对应的分区

打开动态分区的开关：set hive.exec.dynamic.partition = true;

设置动态分区插入模式：set hive.exec.dynamic.partition.mode = nonstrict

create table student(name string, department string) partitioned by (id int) .....

insert into table student partition(id) select name,department,id from mingxing2;

student表字段：name,department，分区字段是id

查询字段是：name,department,id，分区字段

注意：动态分区插入的分区字段必须是查询语句当中出现的字段中的最后一个

进行动态分区插入的时候的select语句中的查询字段 == 分区表的正常字段 + 动态分区字段

如果你所指定的动态分区字段不是select查询的最后一个或者多个字段，SQL语句不会报错，但是结果不对

CTAS(create table ... as select ...)(直接把查询出来的结果存储到新建的一张表里)

create table student as select id,name,age,department from mingxing;

注意：自动新建的表中的字段和查询语句出现的字段的名称，类型，注释一模一样

CTAS 和 insert ... select ...

前者会自动创建表，后者是已经创建好了的表

限制：

1、不能创建外部表

2、不能创建分区表

3、不能创建分桶表

分桶插入：

创建分桶表：

create table mingxing_bck(id int, name string, sex string, age int, department string) clustered by(id) sorted by(age desc) into 4 buckets row format delimited fields terminated by ',';

导入数据：

load data local inpath "/home/hadoop/student.txt" into table mingxing_bck; XXXXXXXXXXX

create table student(id int, name string, sex string, age int, department string) row format delimited fields terminated by ",";

load data local inpath "/home/hadoop/student.txt" into table student;

插入数据：

insert into table mingxing_bck select id,name,sex,age,department from student distribute by id sort by age desc;

注意：查询语句中的分桶信息必须和分桶表中的信息一致

14、like关键字使用：复制表结构

create table student like mingxing;

不会复制数据

新创建的表到底是内部表还是外部表跟源表没关系

15、利用insert导出数据到本地或者hdfs

单模式导出数据到本地：

insert overwrite local directory '/root/outputdata' select id,name,sex,age,department from mingxing;

多模式导出数据到本地：

from mingxing

insert overwrite local directory '/root/outputdata1' select id, name

insert overwrite local directory '/root/outputdata2' select id, name,age

简便路径模式导出到hdfs：

insert overwrite directory '/root/outputdata' select id,name,sex,age,department from mingxing;

全路径模式查询数据到hdfs：

insert overwrite directory 'hdfs://hadoop01:9000/root/outputdata1' select id,name,sex,age,department from mingxing;

local ：导出到本地目录

overwrite ：表示覆盖

16、清空数据库表中的数据

truncate table mingxing_mng;

// 清空内部表，外部表，分桶表的时候，表示把数据存储目录下的所有数据文件统统删掉

// 清空分区表的时候，只会清空所有分区下的数据文件，不会删掉所有的分区定义

17、select查询

order by : 全局排序

如果一个HQL语句当中设置了order by，那么最终在HQL语句执行过程中设置的

set mapreduce.job.reduces = 4 不起作用。！！

sort by ：局部排序

一般来说，要搭配分桶操作使用

distribute by id sort by age desc;

distribute by : 纯粹就是分桶

在使用distribute by的时候：要设置reduceTask的个数

cluster by ：既分桶，也排序

cluster by age = distribute by age sort by age;

distribute by age sort by age,id != cluster by age sort by id

cluster by 和 sort by 不能同时使用

18、join查询

限制:

支持等值连接，不支持非等值连接

支持 and 操作，不支持 or

支持超过2个表的连接

经验：

当出现多个表进行连接时，最好把小表放置在前面！！把大表放置在最后

join分类；

inner join

left outer join

right outer join

full outer join

left semi join

它是in、exists的高效实现

select a.* from a left semi join b on a.id = b.id

等价于：

select a.* from a where a.id in (select b.id from b);

最后编辑于：2018.08.09 10:48:45

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