开发者社区> 姚兴泉> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

Hive操作大全(原创)

简介: 1、建库 create database mydb; create database if not exists mydb; create database if not exists mydb location "/aa/bb"; 2、查询数据...
+关注继续查看

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);

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
原创 | 详解Hive窗口函数及10道实战练习
原创 | 详解Hive窗口函数及10道实战练习
0 0
Hbase作者为什么允许一张表可以有多个列簇?
HBase的设计目标是海量,高吞吐存储。数据在底层是基于LSMT那一套的实现(当然分了很多region,支持分布式)。简单来说,要维护一套memstore + 可分裂的filestore的存储,差不多就是: 新数据写入/更改先写入WAL,然后进入memstore memstore满了就进filestore filestore太大了就分裂 而这一套机制实现的单位是column family——每个column family有自己的memstore和filestore。尽管在高层上看起来是同一张表,但是表里每一个column family的存储都是相互独立的。如果和mysql比较的话,colum
0 0
Hive笔记
此文是学习《Hive编程指南》时所作的笔记,用于加深理解。
2134 0
《Hive编程指南》一导读
本书是一本Hive的编程指南。Hive是Hadoop生态系统中必不可少的一个工具,它提供了一种SQL(结构化查询语言)方言,可以查询存储在Hadoop分布式文件系统(HDFS)中的数据或其他和Hadoop集成的文件系统,如MapR-FS、Amazon的S3和像HBase(Hadoop数据库)和Cassandra这样的数据库中的数据。
1153 0
【原创】hive搭建
本章节主要介绍两部分内容: 1、hive搭建元数据存放于本地文件系统。 2、hive搭建元数据存放于第三方开源数据库mysql。 两者的定义大致如下: ingle User Mode:此模式连接到一个In-memory的数据库Derby,一般用于Unit Test。
844 0
【原创】hive关联hbase表后导致统计数据报错
环境说明: 搭建好的hadoop+hbase+zookeeper集群,因为hbase里面查询数据不支持select语句,所以搭建起了hive(数据仓库)。我的hive搭建过程也不做太多的介绍,用的是第三方数据库mysql存储hive的元数据。
898 0
+关注
文章
问答
文章排行榜
最热
最新
相关课程
更多
相关电子书
更多
Experiences Migrating Hive Wor
立即下载
云HBaseSQL及分析 ——Phoenix&Spark
立即下载
Cassandra CQL语法以及功能介绍
立即下载