在hive中有数据库和数据表的概念，其实和传统数据库的概念是一样的。但是在hive中不管创建的是数据库还是数据表都是文件夹。

1 数据库的增删改查

1.1 创建数据库

创建数据库，数据在HDFS上的默认存储路径是/user/hive/warehouse/*.db

hive (default)> create database db_name;

可以使用 if exists 判断数据库是否已存在(存在则不创建)

hive (default)>create database if not exists db_name;

创建一个数据库，并指定数据在HDFS上的存放位置，使用location

hive (default)>create database if not exists db_hive location '/mydata/demo.db'

创建一个数据库，指定一个已存在的文件夹作为数据库内容的存放位置

hive (default)>create database if not exists db_hive location '/mydata/demo'

注意：如果 location 指定的目录不存在，创建的数据库会失败，但是不会显示错误信息。

1.2 查询数据库

显示所有数据库

hive> show databases;

模糊搜索

hive> show databases like 'db*';

查看信息

hive> desc database db_name;

查看详细信息，添加extended参数

hive> desc database extended db_hive;

切换当前数据库

hive> use db_hive;

1.3 修改数据库

可以修改一些其他的附加信息，但不能修改数据库元数据信息，包括数据库名和数据库所在的目录位置。即 desc展示的数据是不可更改的。

给数据库添加信息

hive> alter database db_hive set dbproperties('createtime'='202102');

查看上述添加的信息

hive> desc database extended db_hive;

1.4 删除数据库

删除一个空的数据库

drop database db_name;

最好使用 if exists 判断数据库是否存在

drop database if exists db_name;

如果数据库不为空,可以采用cascade命令强制删除

drop database db_name cascade;

2 数据表的创建和增删改

2.1 创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] 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]

external ：关键字可以让用户创建一个外部表
comment：为表和列添加注释
partitioned by：创建分区表
clustered by：创建分桶表
sorted by：排序不常用
row format delimited：设置表中数据的分隔符
[fields terminated by char] // 字段（列）分隔符
[collection items terminated by char] // 数组分隔符
[map keys terminated by char] // map 分隔符
[lines terminated by char] // 行分隔符一般默认不写这一句
stored as ：指定存储文件类型
location：指定表在HDFS上的存储位置。
like：允许用户复制现有的表结构，但是不复制数据。

例子

创建一张表

create table student(id int, name string);

创建一张外部表

create external table student(id int, name string);

创建表并设置表中数据的分隔符(以制表符为例)

create table student(id int, name string) row format delimited fields terminated by '\t';

创建表并设置表中数组数据的分隔符(以制表符为例)

create table student(id int, name string) collection items terminated by "\t" ;

2.1.1 管理表

默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive会（或多或少的）控制着数据的生命周期。

Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(默认在/user/hive/warehouse)所定义的目录下的子目录下。

当我们删除一个管理表的时候，hive也会删除这个表中的数据。 管理表不适合和其他工具共享数据。

普通创建表

create table if not exists student2(id int,name string)row format delimited fields terminated by '\t'    // 字段（列）分隔符stored as textfile  //指定存储文件类型location '/user/hive/warehouse/student2';  //指定表在HDFS上的存储位置

根据查询结果创建表（查询的结果会添加到新的创建的表中）

create table if not exists student3 as select id, name from student2;

根据已经有的表结构创建表

create table if not exists student4 like student2;

2.1.2 外部表

因为表是外部表，所以hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。

即两者的区别在于

删除时，内部表把元数据和具体数据都删除，而外部表只删除元数据。

注：元数据定义为描述数据的数据，可以为数据说明其元素或属性（名称、大小、数据类型、等），或其结构（长度、字段、数据列），或其相关数据（位于何处、如何联系、拥有者）

因此，元数据指的是存在数据库里面的表结构，数据是存储在HDFS下的文件里。

管理表和外部表的使用场景

每天将收集的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用得到中间表，结果表使用内部表存贮，数据通过select insert进入内部表。

create external table if not exists default.student(id int,name string)row format delimited fields terminated by '\t';

2.1.3 管理表和外部表的互相转换

内部表修改为外部表

alter table student set tblproperties('EXTERNAL'='TRUE')

外部表修改为内部表

alter table student set tblproperties('EXTERNAL'='FALSE')

注意：(‘EXTERNAL’=‘TRUE’)和(‘EXTERNAL’=‘FALSE’)为固定写法，区分大小写！大写是为唯一写法。

2.2 分区表

分区表实际是对应一个HDFS文件系统上的独立的文件，该文件夹下是该分区所有的数据文件。

Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多

hive的分区和MR的分区是不一样的，hive的分桶才是和MR的分区一个概念。hive的分区分的目录，分桶才是分的文件。

创建分区表

创建一个表,并设置以"month"字段分区

create table student(id int, name string) partitioned by(month string);

创建二级分区表

create table student(id int, name string)partitioned by(month string, day string)

添加分区

往分区表里添加一个分区

alter table student add partition(month='202102');

往分区表里添加多个分区(以空格隔开)

alter table student add partition(month='202102') partition(month='202102');

往分区表中添加数据

加上关键字partition(…)指定分区即可;如果没有该分区，则自动新建

load data local inpath'/opt/file.txt' into student partition(month='202102');insert into student partition(month='202102') values(1,'abc');

查询分区表数据

select * from student where month='202102';select * from student where month='202102' and day='01';

删除分区

删除一个分区表里的分区

alter table student drop partition(month='202102');

删除多个分区表里的分区(以逗号隔开)

alter table student drop partition(month='202102'),partition(month='202102');

查看所有分区

show partitions student;

修复分区

如果数据是通过HDFS直接上传到分区目录，如果分区没定义，则会查询不到刚上传的数据

修复命令

msck repair table student;

也可以直接让此目录成为分区目录(这里以month='20200316’为例)

alter table student add partition(month='20200316');

2.3 修改表

重命名表

alter table table_name RENAME TO new_table_name

添加列(添加的列在分区字段之前，括号中使用逗号分隔添加多列)

alter table student add columns(gender string);

更新列信息(旧字段名,新字段名,新字段类型)

alter table student change column name name2 string;

替换表中所有字段(将所有字段合并替换为一个字段)

alter table student replace columns(id string,name string,age int);

注：add 是代表新增一字段，字段位置在所有列后面(partition列前)，replace 则是表示替换表中所有字段。

2.4 删除表

drop table student;

来源：https://www.icode9.com/content-4-859151.html

【Hive学习笔记】数据定义语言(DDL)