hadoop 数据仓库 大数据 Hive的基本SQL操作（DDL篇）

目录

​编辑

一、数据库的基本操作

1.1 展示所有数据库

1.2 切换数据库

1.3 创建数据库

1.4 删除数据库

1.5 显示数据库信息

1.5.1 显示数据库信息

1.5.2 显示数据库详情

二、数据库表的基本操作

2.1 创建表的操作

2.1.1 创建普通hive表（不包含行定义格式）

2.1.2 创建自定义行格式的hive表

2.1.3 创建默认分隔符的hive表（^A、^B、^C）

2.1.4 创建hive的外部表(需要添加external和location的关键字)

2.1.5 创建单分区表

2.1.6 创建多分区表

2.1.7 给分区表添加分区列的值

2.1.8 删除分区列的值

2.1.9 修复分区

2.1.9.1 在hdfs创建目录并上传文件

2.1.9.2 创建外部表

2.1.9.3 查询结果（没有数据）

2.1.9.4 修复分区

2.1.9.5 再查询结果（有数据）

2.2 查看表的操作 

2.2.1 查看表结构

2.2.2 查看表分区

2.2.3 查看表详细结构

2.2.4 查看表的类型

2.2.5 查看建表信息

2.3 修改表的操作

2.3.1 修改内部表为外部表

2.3.2 修改表名

2.3.3 更新列

2.3.4 新增或替换列

2.3.5 修改列名、列类型、注释

2.4 删除表的操作

2.4.1 Drop删除

2.4.2 Truncate 删除

2.4.3 drop 和truncate的区别

一、数据库的基本操作

1.1 展示所有数据库

show databases;

1.2 切换数据库

use database_name;

提示：database_name 指的我们真实存在的数据库名称。

例子：use test;

1.3 创建数据库

语法：

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name

      [COMMENT database_comment]

      [LOCATION hdfs_path]

      [WITH DBPROPERTIES (property_name=property_value, ...)];

例子：

create database test;

注意：当进入hive的命令行开始编写SQL语句的时候，如果没有任何相关的数据库操作，那么默认情况下，所有的表存在于default数据库，在hdfs上的展示形式是将此数据库的表保存在hive的默认路径下，如果创建了数据库，那么会在hive的默认路径下生成一个database_name.db的文件夹，此数据库的所有表会保存在database_name.db的目录下。

1.4 删除数据库

语法：

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

例子：

drop database test;

1.5 显示数据库信息

1.5.1 显示数据库信息

语法：desc database database_name;

例子：desc databse test;

1.5.2 显示数据库详情

语法：desc  database EXTENDED database_name;

例子：desc databse EXTENDED test;

二、数据库表的基本操作

2.1 创建表的操作

2.1.1 创建普通hive表（不包含行定义格式）

create table psn

(

id int,

name string,

likes array,

address map

)

2.1.2 创建自定义行格式的hive表

create table psn2

(

id int,

name string,

likes array,

address map

)

row format delimited

fields terminated by ','

collection items terminated by '-'

map keys terminated by ':';

2.1.3 创建默认分隔符的hive表（^A、^B、^C）

create table psn3

(

id int,

name string,

likes array,

address map

)

row format delimited

fields terminated by '\001'

collection items terminated by '\002'

map keys terminated by '\003';

2.1.4 创建hive的外部表(需要添加external和location的关键字)

create external table psn4

(

id int,

name string,

likes array,

address map

)

row format delimited

fields terminated by ','

collection items terminated by '-'

map keys terminated by ':'

location '/data';

在之前创建的表都属于hive的内部表（psn,psn2,psn3）,而psn4属于hive的外部表， 内部表跟外部表的区别： 1、hive内部表创建的时候数据存储在hive的默认存储目录中，外部表在创建的时候需要制定额外的目录 2、hive内部表删除的时候，会将元数据和数据都删除，而外部表只会删除元数据，不会删除数据 应用场景: 内部表：需要先创建表，然后向表中添加数据，适合做中间表的存储 外部表：可以先创建表，再添加数据，也可以先有数据，再创建表，本质上是将hdfs的某一个目录的数据跟                hive的表关联映射起来，因此适合原始数据的存储，不会因为误操作将数据给删除掉

2.1.5 创建单分区表

create table psn5(id int,name string,likes array,address map)

partitioned by(gender string)

row format delimited

fields terminated by ','

collection items terminated by '-'

map keys terminated by ':';

2.1.6 创建多分区表

create table psn6

(

id int,

name string,

likes array,

address map

)

partitioned by(gender string,age int)

row format delimited

fields terminated by ','

collection items terminated by '-'

map keys terminated by ':';

注意： 1、当创建完分区表之后，在保存数据的时候，会在hdfs目录中看到分区列会成为一个目录，以多级目录的形式存在 2、当创建多分区表之后，插入数据的时候不可以只添加一个分区列，需要将所有的分区列都添加值 3、多分区表在添加分区列的值得时候，与顺序无关，与分区表的分区列的名称相关，按照名称就行匹配

2.1.7 给分区表添加分区列的值

alter table table_name add partition(col_name=col_value)

2.1.8 删除分区列的值

alter table table_name drop partition(col_name=col_value)

注意: 1、添加分区列的值的时候，如果定义的是多分区表，那么必须给所有的分区列都赋值 2、删除分区列的值的时候，无论是单分区表还是多分区表，都可以将指定的分区进行删除

2.1.9 修复分区

在使用hive外部表的时候，可以先将数据上传到hdfs的某一个目录中，然后再创建外部表建立映射关系，如果在上传数据的时候，参考分区表的形式也创建了多级目录，那么此时创建完表之后，是查询不到数据的，原因是分区的元数据没有保存在mysql中，因此需要修复分区，将元数据同步更新到mysql中，此时才可以查询到元数据.

2.1.9.1 在hdfs创建目录并上传文件

hdfs dfs -mkdir /ning

hdfs dfs -mkdir /ning/age=10

hdfs dfs -mkdir /ning/age=20

hdfs dfs -put /root/data/data /ning/age=10

hdfs dfs -put /root/data/data /ning/age=20

2.1.9.2 创建外部表

create external table psn11

(

id int,

name string,

likes array,

address map

)

partitioned by(age int)

row format delimited

fields terminated by ','

collection items terminated by '-'

map keys terminated by ':'

location '/ning';

2.1.9.3 查询结果（没有数据）

select * from psn11;

2.1.9.4 修复分区

msck repair table psn11;

2.1.9.5 再查询结果（有数据）

select * from psn11;

2.2 查看表的操作 

2.2.1 查看表结构

desc formatted psn19;

2.2.2 查看表分区

show partitions psn19;

2.2.3 查看表详细结构

desc formatted psn19;

desc extended psn19;

2.2.4 查看表的类型

desc formatted psn19;

2.2.5 查看建表信息

show create table psn11;

2.3 修改表的操作

2.3.1 修改内部表为外部表

语法：alter table table_name set  TBLPROPERTIES('EXTERNAL'='true');

例子：

alter table psn3 set TBLPROPERTIES('EXTERNAL'='true');

查询下表结构，看下表的类型：

desc formatted psn3;

表的类型变成了EXTERNAL_TABLE，变成了外部表（EXTERNAL_TABLE 是外部表，MANAGED_TABLE是内部表）。

2.3.2 修改表名

语法：alter table table_name RENAME to new_table;

例子：

alter table psn3 rename to new_psn3;

通过查看数据库中的表，我们发现表psn3成功修改为表new_psn3。

2.3.3 更新列

语法：alter table table_name CHANGE COLUMN col_old_name col_new_name column_type;

例子：

# 我们把new_psn3这张表中的name字段修改成new_name字段，类型是string类型。

alter table new_psn3 change column name new_name string;

通过查看表结构，我们发现原本的name字段，已经成功修改成了new_name字段，且类型是string 类型。

2.3.4 新增或替换列

语法：alter table table_name ADD|REPLACE COLUMNS (col_name data_type.........);

新增列例子：

#我们往new_psn3这张表中新增一个列，叫age，类型int 类型。

alter table new_psn3 add columns(age int);

替换列例子：

#我们将new_psn3这张表中的字段进行替换。

alter table new_psn3 replace columns(id int,name string,likes array,address map);

通过查看表，我们知道，表中的字段，被我们成功替换了。

2.3.5 修改列名、列类型、注释

语法：alter table table_name change old_column_name new_column_name new_column_type comment '注释';

例子：

#我们将new_psn3 中的name 修改成new_name,类型还是string ,添加注释“名称”

alter table new_psn3 change name new_name string comment '名称';

2.4 删除表的操作

2.4.1 Drop删除

语法：`DROP TABLE [IF EXISTS] table_name;`

例子：

drop table if existt psn2;

提示：使用drop命令删除普通表，hdfs的数据和hive中的元数据会被删除，但是删除外部表时，只会删除元数据，不会删除hdfs中的数据。

2.4.2 Truncate 删除

语法：

`TRUNCATE [TABLE] table_name [PARTITION partition_spec];`

`partition_spec:` (partition_column = partition_col_value, partition_column = partition_col_value, ...)

例子：

在删除之前，我看下我的psn20表存储的数据：

可以看多，psn20有两个分区，分别是age=10,age=20.

#删除表中指定分区数据

truncate table psn20 partition(age=10);

删除age=10这个分区后，我们发现psn20这个表只剩下age=20的分区数据。

# 删除所有分区数据

truncate table psn20;

提示：

1、从表或分区中删除所有行。可以指定partition_spec一次截断多个分区，省略partition_spec将截断表中的所有分区（即分区表可以通过指定partition_spec删除表中指定的分区数据，如果不指定，则删除整个表中的分区数据）。

2、使用Truncate删除外部表会报错，因为外部表不被Hive所管理，被文件存储系统hdfs管理，所以需要先去hdfs中删除数据文件，再使用Truncate删除表元数据。

2.4.3 drop 和truncate的区别

1）drop 操作会删除元数据（即表不存在）和文件系统的数据（只针对普通表）

2）truncate操作只是会删除表中的数据，不会删除表的元数据（即表还存在，只针对普通表）

3）关于外部表，drop操作只会删除元数据，不会删除文件系统的数据，而truncate操作不能直接操作外部表，因为外部表的不属于Hive管理，直接操作会报错。

更多详细内容，可参考Hive官网DDL语句部分说明文档：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

好了，今天Hive基本SQL操作（DDL篇）的相关内容就分享到这里，如果帮助到大家，欢迎大家点赞+关注+收藏，有疑问也欢迎大家评论留言！

参考文章

 您阅读本篇文章共花了：