Hive 的DDL和DML

本文是跟着这个学习的 https://www.bilibili.com/video/av65556024?p=1

DDL

1.库

建库：

> create database if not exists 库名; 

还有一个方式：

> create database if not exists 库名 location 路径; 

指定hdfs路径

查看数据库：

> show databases;

看数据库信息：

> desc databases 库名;

想多看点：

> desc databases extended 库名;

改库：(数据库名和数据库目录位置无法修改)

> alter database 库名 set ...

删库:（想跑路？）
空库：

> drop database if exists 库名;

非空库：

> drop database 库名 cascade;

2.表

建表：

> create [external] table [if not exist] table_name [(col_name data_type [comment col_comment],...)]
[comment table_comment]
[partitioned by (col_name data_type[comment col_comment],...)
[clustered by (col_name,col_name...)
[sorted by (col_name [asc|desc],...)] into num_buckets buckets]
[row format row_format]
[stored as file_format]
[Location hdfs_path]

语句特别长，中括号里面的不是必要的，但是大多数情况下的建表都是分区表。
1.create table 就是建表基础。
2.external：关键字是可以创建于一个外部表，建表同时指定一个指向实际数据的路径(location)。
hive创建内部表时，会将数据移动到数据仓库指向的路径；
如果创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。
区别就在于删除表的时候。内部表元数据和数据都会被删除，外部表只删除元数据，不删除数据。
3.comment：添加注释(对表、对列)
4.partitioned by：创建分区表
5.clustered by：创建分桶表
6.sorted by ： 排序，基本不用
7.row format：数据切分形式
8.stored as ：指定文件存储类型
9.location：指定表在hdfs的位置
10.like ：允许用户复制现有变结构，但不复制数据

我们使用create table的时候，基本上hive帮我们补充了其他参数的默认值。

管理表

默认创建的表都是管理表，有时候也称为内部表。这种表，hive会控制数据的生命周期，我们删除表的时候，hive也会删除表中的数据。
外部表更安全一些，毕竟数据是分开的。删表之后数据会保留。

查询表的类型

> desc formatted 表名

能发现Table_type，是表的类型。可以修改

内部表改为外部表

> alert table 表名 set tblproperties ('EXTERNAL'='TRUE')

外部表改为内部表

> alert table 表名 set tblproperties ('EXTERNAL'='FALSE')

MANAGED_TABLE是内部表，EXTERNAL_TABLE是外部表。
注意！！！(‘EXTERNAL’=‘TRUE’)和 (‘EXTERNAL’=‘FALSE’)这个要区分大小写！！！

3.分区

分区的意义：分区表实际上来说，就是对应HDFS文件系统上独立的文件夹，该文件夹下是该分区所有数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要，分割成小的数据集。在查询的时候，通过where子句中的表达式，选择查询所需要的指定分区。提高查询效率(谓词下推)。

3.1 引入分区表（根据日期对日志进行管理）

/user/hive/warehouse/log_partition/20191028/20191028.log
/user/hive/warehouse/log_partition/20191029//20191029.log
/user/hive/warehouse/log_partition/20191030//20191030.log

3.2 创建分区表

> create table dept_partition(deptno int,dname string,loc string) 
partitioned by (month string)
row format delimited fields terminated by '\t';

3.3 加载数据到分区表中

> load data local inpath '/mnt/hgfs/shareOS/dept.txt' into table dept_partition partition(month='2019-06');

> load data local inpath '/mnt/hgfs/shareOS/dept.txt' into table dept_partition partition(month='2019-07');

看到生成了两个文件，select的时候发现，查询的是两个文件和并的数据。并且帮我们自动添加了month，并且它可以作为查询条件。这个条件区分了文件夹，查询的时候会被先访问。
我们去mysql看元数据。PARTITION表，增加了两条数据。这个记录了我们的分区。

3.4 查询分区表中数据

单分区查询：

> select * from dept_partition where month = '2019-06';

多分区联合查询：

> select * from dept_partition where month = '2019-06' 
    union
        select * from dept_partition where month = '2019-07' 
            union
                select * from dept_partition where month = '2019-08' ;

3.5 增加分区

创建单个分区：

> alert table dept_partition add partition(month=''2019-09);

多个分区：

> alert table dept_partition add partition(month=''2019-10) partition(month=''2019-11);

3.6 删除分区

> alter table dept_partition drop partition (month='2019-10');

删多个：

> alter table dept_partition drop partition (month='2019-10'),partition (month='2019-11');

3.7 查看分区表有多少个分区

> show partitions dept_partition;

3.8 分区表结构查看

> desc formatted dept_partition;

多了一个分区字段信息。把分区字段当普通字段使用就可以了。

4.分区表注意事项

4.1 二级分区

> create table dept_partition2(deptno int,dname string,loc string) 
partitioned by (month string，day string)
row format delimited fields terminated by '\t';

4.2 添加的时候，两个也都要填。

> load data local inpath '/mnt/hgfs/shareOS/dept.txt' into table dept_partition partition(month='2019-06',day='10');

4.3 把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式。

（1）上传数据之后修复

> msck repair table dept_partition;

（2）上传数据之后添加分区

> alter table dept_partition add partition(month'2019-11');

（3）创建文件夹之后load数据到分区。
load添加了分区信息，上传了数据，问题直接就解决了。

五.修改表

5.1 改表名

> alter table 表名 RENAME TO new_table_name;

5.3 增加、修改、替换列信息

更新列：

> alter table 表名 change [COLUMN] col_old_name col_new_name 
column_type [COMMENT col_comment] [FIRST|AFTER column_name]

增加和替换列：

> alter table 表名 add|replace COLUMNS 
(col_name data_type [COMMENT col_comment],...)

ADD是增加一个字段，REPLACE是替换表中所有字段。

接下来是部分DML

一.数据导入

###1.1.1 向表中装载数据(Load)
语法：

> load data [local] inpath '/opt/soft/文件.txt' overwrite|into 
table 表名 [partition(partcol1=val1,...)];

load data：加载数据
local：表示从本地加载到hive表；否则从HDFS加载到Hive
inpath：加载数据路径
overwrite：表示覆盖表中已有数据，否则表示追加。
into table：表示加载到哪张表
partition：上传到指定分区

1.1.2 通过查询语句插入数据(insert)

> insert into table 表名 partition(month='2019-06') value (1,'zahngsan');

into也可以变成overwrite，基本模式插入。
还有多插入模式之类的。。。不过不常用。

1.1.3 查询语句中创建并加载数据(As Select)

1.创建一张分区表

例如：

> create table if not exist 表名(id int,name string) partitioned by 
(month string) row format delimited fields terminated by '\t';

2.基本插入数据

例如：

> insert into table student partition(month='2019-07') values(1,'wangwu');

3.基本模式插入(覆盖)
例如：

> insert overwrite table student partition(month='2019-07') values(1,'wangwu');

二.数据导出

2.1 Insert导出

1.结果导出到本地

> insert overwrite local directory '/opt/module/datas/export/student'
select * from student;

哎，由于我虚拟机分配内存比较小，系统慢的要死！让我一度以为集群搭建的有问题！！！

2.将结果格式化导出到本地

>insert overwrite local directory '/opt/module/datas/export/student'  
row format delimited fields terminated by '\t'
select * from student;

3.导出到hdfs

>insert overwrite directory '/dept'  
row format delimited fields terminated by '\t'
select * from student;

2.2 Hadoop导出
2.3 Export导出到HDFS
2.4 SQOOP导出
MySQL 与 Hive(HDFS) 之间导来导去。。。