1、对数据库的操作

• 创建数据库:

create database if not exists myhive;
说明：hive的表存放位置模式是由hive-site.xml当中的一个属性指定的 :hive.metastore.warehouse.dir

创建数据库并指定hdfs存储位置 :
create database myhive2 location '/myhive2';

• 修改数据库:

alter  database  myhive2  set  dbproperties('createtime'='20210329');

说明：可以使用alter database 命令来修改数据库的一些属性。但是数据库的元数据信息是不可更改的，包括数据库的名称以及数据库所在的位置

• 查看数据库详细信息

查看数据库基本信息
hive (myhive)> desc  database  myhive2;

查看数据库更多详细信息
hive (myhive)> desc database extended  myhive2;

• 删除数据库

删除一个空数据库，如果数据库下面有数据表，那么就会报错
drop  database  myhive2;

强制删除数据库，包含数据库下面的表一起删除
drop  database  myhive  cascade; 

2. 对数据表的操作

对管理表(内部表)的操作:

• 建内部表:

hive (myhive)> use myhive; -- 使用myhive数据库
hive (myhive)> create table stu(id int,name string);
hive (myhive)> insert into stu values (1,"zhangsan");
hive (myhive)> insert into stu values (1,"zhangsan"),(2,"lisi");  -- 一次插入多条数据
hive (myhive)> select * from stu;

• hive建表时候的字段类型:

对decimal类型简单解释下：

用法：decimal(11,2) 代表最多有11位数字，其中后2位是小数，整数部分是9位；如果整数部分超过9位，则这个字段就会变成null；如果小数部分不足2位，则后面用0补齐两位，如果小数部分超过两位，则超出部分四舍五入也可直接写 decimal，后面不指定位数，默认是 decimal(10,0) 整数10位，没有小数

• 创建表并指定字段之间的分隔符

create  table if not exists stu2(id int ,name string) 
row format delimited fields 
terminated by '\t' stored as textfile 
location '/user/stu2';

• 根据已经存在的表结构创建表

create table stu4 like stu2;

• 查询表的结构

只查询表内字段及属性
desc stu2;

详细查询
desc formatted  stu2;

• 查询创建表的语句

show create table stu2;

对外部表操作

外部表因为是指定其他的hdfs路径的数据加载到表当中来，所以hive表会认为自己不完全独占这份数据，所以删除hive表的时候，数据仍然存放在hdfs当中，不会删掉，只会删除表的元数据

• 构建外部表

create external table student (s_id string,s_name string) 
row format delimited fields 
terminated by '\t';

• 从本地文件系统向表中加载数据

追加操作
load data local inpath '/export/servers/hivedatas/student.csv' into table student;

覆盖操作
load data local inpath '/export/servers/hivedatas/student.csv' overwrite  into table student;

• 从hdfs文件系统向表中加载数据

load data inpath '/hivedatas/techer.csv' into table techer;

加载数据到指定分区
load data inpath '/hivedatas/techer.csv' into table techer partition(cur_date=20201210);

注意：

1.使用 load data local 表示从本地文件系统加载，文件会拷贝到hdfs上
2.使用 load data 表示从hdfs文件系统加载，文件会直接移动到hive相关目录下，注意不是拷贝过去，因为hive认为hdfs文件已经有3副本了，没必要再次拷贝了
3.如果表是分区表，load 时不指定分区会报错
4.如果加载相同文件名的文件，会被自动重命名

对分区表的操作

• 创建分区表的语法

create table score(s_id string, s_score int) partitioned by (month string);

• 创建一个表带多个分区

create table score2 (s_id string, s_score int) partitioned by (year string,month string,day string);

注意：

hive表创建的时候可以用 location 指定一个文件或者文件夹，当指定文件夹时，hive会加载文件夹下的所有文件，当表中无分区时，这个文件夹下不能再有文件夹，否则报错
当表是分区表时，比如 partitioned by (day string)， 则这个文件夹下的每一个文件夹就是一个分区，且文件夹名为 day=20201123 这种格式，然后使用：msck repair table score; 修复表结构，成功之后即可看到数据已经全部加载到表当中去了

• 加载数据到一个分区的表中

load data local inpath '/export/servers/hivedatas/score.csv' into table score partition (month='201806');

• 加载数据到一个多分区的表中去

load data local inpath '/export/servers/hivedatas/score.csv' into table score2 partition(year='2018',month='06',day='01');

• 查看分区

show  partitions  score;

• 添加一个分区

alter table score add partition(month='201805');

• 同时添加多个分区

alter table score add partition(month='201804') partition(month = '201803');

注意：添加分区之后就可以在hdfs文件系统当中看到表下面多了一个文件夹

• 删除分区

alter table score drop partition(month = '201806');

对分桶表操作

将数据按照指定的字段进行分成多个桶中去，就是按照分桶字段进行哈希划分到多个文件当中去
分区就是分文件夹，分桶就是分文件

分桶优点：

1. 提高join查询效率
2. 提高抽样效率

• 开启hive的捅表功能

set hive.enforce.bucketing=true;

• 设置reduce的个数

set mapreduce.job.reduces=3;

• 创建桶表

create table course (c_id string,c_name string) clustered by(c_id) into 3 buckets;

桶表的数据加载：由于桶表的数据加载通过hdfs dfs -put文件或者通过load data均不可以，只能通过insert overwrite 进行加载
所以把文件加载到桶表中，需要先创建普通表，并通过insert overwrite的方式将普通表的数据通过查询的方式加载到桶表当中去

• 通过insert overwrite给桶表中加载数据

insert overwrite table course select * from course_common cluster by(c_id);  -- 最后指定桶字段

修改表和删除表

• 修改表名称

alter  table  old_table_name  rename  to  new_table_name;

• 增加/修改列信息

查询表结构
desc score5;

添加列
alter table score5 add columns (mycol string, mysco string);

更新列
alter table score5 change column mysco mysconew int;

• 删除表操作

drop table score5;

• 清空表操作

truncate table score6;
说明：只能清空管理表，也就是内部表；清空外部表，会产生错误

注意：truncate 和 drop：
如果 hdfs 开启了回收站，drop 删除的表数据是可以从回收站恢复的，表结构恢复不了，需要自己重新创建；truncate 清空的表是不进回收站的，所以无法恢复truncate清空的表
所以 truncate 一定慎用，一旦清空将无力回天

向hive表中加载数据

• 直接向分区表中插入数据

insert into table score partition(month ='201807') values ('001','002','100');

• 通过load方式加载数据

 load data local inpath '/export/servers/hivedatas/score.csv' overwrite into table score partition(month='201806');

• 通过查询方式加载数据

insert overwrite table score2 partition(month = '201806') select s_id,c_id,s_score from score1;

• 查询语句中创建表并加载数据

create table score2 as select * from score1;

• 在创建表是通过location指定加载数据的路径

create external table score6 (s_id string,c_id string,s_score int) 
row format delimited fields 
terminated by ',' 
location '/myscore';

• export导出与import 导入 hive表数据（内部表操作）

create table techer2 like techer; --依据已有表结构创建表

export table techer to  '/export/techer';

import table techer2 from '/export/techer';

hive表中数据导出

• insert导出

将查询的结果导出到本地
insert overwrite local directory '/export/servers/exporthive' 
select * from score;

将查询的结果格式化导出到本地
insert overwrite local directory '/export/servers/exporthive' 
row format delimited fields 
terminated by '\t' collection items 
terminated by '#' 
select * from student;

将查询的结果导出到HDFS上(没有local)
insert overwrite directory '/export/servers/exporthive' 
row format delimited fields 
terminated by '\t' collection items 
terminated by '#' 
select * from score;

• Hadoop命令导出到本地

dfs -get /export/servers/exporthive/000000_0 /export/servers/exporthive/local.txt;

• hive shell 命令导出

基本语法：（hive -f/-e 执行语句或者脚本 > file）

hive -e "select * from myhive.score;" > /export/servers/exporthive/score.txt

hive -f export.sh > /export/servers/exporthive/score.txt

• export导出到HDFS上

export table score to '/export/exporthive/score';