1、建库
create database mydb;
create database if no exists mydb;
2、查询数据库
```sql
查询库列表:show databases;
查询库详细信息:desc database [extended] mydb;
hive> desc database extended myhive;
OK
myhive hdfs://hadoop0/user/hive/warehouse/myhive.db root USER
Time taken: 0.031 seconds, Fetched: 1 row(s)
查询当前正在使用的库: select current_database();
3、删除数据库
drop database mydb;
drop database mydb cascade;
drop database if exists mydb;
drop database if exists mydb [restrict|cascade];
4、先进入我们要操作的数据库/切换库
use mydb;
5、查看数据库里面的表
show tables;
show tables in mydb;
6、添加表
创建内部表(Managered_Table)
create table mingxing(id int, name string, sex string, age int, department string comment "ss") comment "table comment" row format delimited fields terminated by ',';
show create table mingxing;
创建外部表(External_Table)
create external table mingxing(id int, name string, sex string, age int, department string)
row format delimited fields terminated by ',' location '/root/hivedata';
注意:创建外部表的时候指定location的位置必须是目录,不能是单个文件
跟内部表对比:
1、在创建表的时候指定关键字: external
2、一般来说,创建外部表,都需要指定一个外部路径
删除内部表和外部表的区别
内部表和外部表的选择
不管是创建外部表还是内部表,都可以指定数据存储目录
默认的目录:
hdfs://hadoop0:9000/user/hive/warehouse/myhive.db/student/student.txt
创建分区表
create table mingxing(id int, name string, sex string, age int, department string)
partitioned by (city string)
row format delimited fields terminated by ',';
注意:partitioned里的字段不是能是表中声明的字段
分区字段是一个虚拟列。分区字段的值是不存在于数据文件中的。
分区字段的使用和普通字段没有区别
分区字段/表字段
创建分桶表
create table mingxing(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里的字段必须要是表字段中出现的字段
分桶字段和排序字段可以不一样,分桶字段和排序字段必须是表字段中的一部分
7、删除表
drop table mingxing;
drop table if exists mingxing;
8、对表进行重命名
alter table mingxing rename to student;
9、对表的字段进行操作(增加add,删除,修改change,替换replace)
增加字段:
alter table mingxing add columns (province string);
alter table mingxing add columns (province string, xxx 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;
替换字段
alter table mingxing replace columns(id int, name string, sex string);
10、对表中的分区进行操作(添加分区,删除分区,修改分区)
增加分区:
alter table mingxing add partition(city='beijing');
alter table mingxing add partition(city='beijing') location '/city_beijing';
alter table mingxing add partition(city='beijing') partition(city='tianjin');
删除分区:
alter table mingxing drop partition(city='beijing');
alter table mingxing drop partition(city='beijing'), partition(city='tianjin');
修改分区路径:
alter table mingxing partition(city="beijing") set location "/mingxing_beijing";
11、查询显示命令
查看库:show databases;
查看表:show tables;
查看建表完整语法:show create table mingxing;
查看内置函数库:show functions;
Time taken: 0.007 seconds, Fetched: 216 row(s) //216个函数
查看函数的详细手册:desc function extended concat;
查看分区:show partitions mingxing;
查看表的字段:desc mingxing;
查看表的详细信息:desc extended mingxing;
查看表的格式化了之后的详细信息:desc formatted mingxing;
12、load方式导入数据
load data ..... [overwrite] into table .....
导入本地相对路径的数据
load data local inpath './student.txt' into table mingxing;
load data local inpath './student.txt' overwrite into table mingxing;
(覆盖导入)
导入本地绝对路径数据:
load data local inpath '/home/hadoop/student.txt' into table mingxing;
导入本地数据,相当于复制或者上传
导入HDFS上的简便路径数据:
load data inpath '/home/hadoop/student.txt' into table mingxing;
导入HDFS上的全路径模式下的数据:
load data inpath 'hdfs://hadoop01:9000/root/hivedata/student.txt' into table mingxing;
导入HDFS上的数据到hive表,表示截切,移动
13、利用insert关键字往表中插入数据
insert ... values ..
insert ... select ..
最有趣最有用最常用的: hadoop fs -put /source /dst
hive是一个读模式的数据仓库
单条数据插入:
insert into table mingxing values(001,'huangbo','male',50,'MA');
单重插入模式: insert ... select ....
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';
分区插入:
需要手动的创建分区
alter table student add partition (city="zhengzhou")
load data local inpath '/root/hivedata/student.txt' into table student partition(city='zhengzhou');
CTAS(create table ... as select ...)(直接把查询出来的结果存储到新建的一张表里)
1、单条记录插入
2、单重插入
3、多重插入
4、分区插入
5、分桶插入
6、CTAS
内部表/内界表
create table student as select id,name,age,department from mingxing;
注意:自动新建的表中的字段和查询语句出现的字段的名称,类型,注释一模一样
限制:
1、不能创建外部表
2、不能创建分区表
3、不能创建分桶表
分桶插入:
创建分桶表:
create table mingxing(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方式直接往分桶表中导入数据
插入数据:
insert into table mingxing select id,name,sex,age,department from mingxing2
distribute by id sort by age desc;
注意:查询语句中的分桶信息必须和分桶表中的信息一致
14、like关键字使用:复制表结构
create [external] 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 mingxing2;
清空表只是清空该表的所有数据文件
17、select查询
order by : 全局排序
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;
cluster by 和 sort by 不能同时使用