大数据—Hive（四）_ DDL数据定义（对数据库、表的增删改）

1、创建数据库

CREATE DATABASE `db_hive1`
[COMMENT database_comment]     --库的注释 当前库拿来干嘛
LOCATION
  'hdfs://hadoop102:8020/user/hive/warehouse/db_hive1.db'
[WITH DBPROPERTIES (property_name=property_value, ...)];  --库的属性 用处不大

2、查询数据库

1）显示数据库

show databases;

2）根据库名过滤

show databases like 'db_hive*';

3）查看数据库详情

desc database db_hive1;

3、修改数据库

在Hive中，修改数据库属性 只能修改在创建数据库时，设定的那些比较鸡肋的DBPROPERTIES 属性。如果是想修改元数据，只能去mysql中修改

alter database db_hive1 set dbproperties('createtime'='20170830');

生产环境几乎不用

4、删除数据库

1）删除空数据库

drop database db_hive2;

2）如果删除的数据库不存在，最好采用 if exists判断数据库是否存在，否则会报错

drop database if exists db_hive2;

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

drop database if exists db_hive2 cascade;

5、创建表

5.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]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]
[LIKE table_name]

EXTERNAL 关键字可以让用户创建一个外部表，在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据
COMMENT 为表和列添加注释
PARTITIONED BY 创建分区表
CLUSTERED BY 创建分桶表
SORTED BY 不常用，对桶中的一个或多个列另外排序
ROW FORMAT 定义行的格式(详讲)
STORED AS 定义文件的格式
LOCATION 指定表的位置信息
TBLPROPERTIES 指定额外的熟悉
AS select查询语句
LIKE 表名

ROW FORMAT 定义行的格式

示例
按照逗号分隔建表

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

insert into test6 values
(1,"zhangsan"),
(2,"lisi"),
(3,"wangwu")

hdfs上看到的数据文件

1,zhangsan
2,lisi
3,wangwu

5.2 管理表

当我们删除一个管理表时，Hive也会删除这个表中数据。

5.3 外部表

删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。

示例
创建部门表

create external table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t'
location '/company/dept';

删除部门表

drop table dept;

源数据没有被删除

5.4 管理表与外部表的互相转换

1）修改内部表student为外部表

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

2）修改外部表student为内部表

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

注意：(‘EXTERNAL’=‘FALSE’) 区别大小写

6、修改表

6.1 重命名表

ALTER TABLE table_name RENAME TO new_table_name

6.2 增加、修改和删除表分区

后续详讲

6.3 增加/修改/替换列信息

1）更改列名、列类型

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

示例
更改dept表，列名deptesc 改成 desc，列类型为 string

alter table dept change column deptdesc desc string;

字段位置在所有列后面，partition列前面

2）增加和替换列

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 

示例
增加1列 (deptdesc string)

alter table dept add columns(deptdesc string);

示例
将dept表中所有列，替换成(deptno string, dname string, loc string)

alter table dept replace columns(deptno string, dname string, loc string);

REPLACE使用的时候，字段的类型要跟之前同名字段类型对应，数量可以减少或者增加，其实就是包含了增加列，删除列的功能

7、删除表

drop table dept;

8、清除表

Truncate只能删除管理表，不能删除外部表中数据

truncate table student;