Hive~库表操作DDL_hive 元数据获取ddl

1.库DDL

创建库 

create database test;
create database if not exists test;
create database if not exists test location '/hive'; # 自定义存储目录

查询库

show databases;

库详情信息

desc database test;
desc database extended test;

切换库

use test;

修改库

alter database test set dbproperties('createtime'='20200503');

删除库

#空数据库
drop database test;
#非空数据库
drop database test cascade;

2.hive的数据类型

基本数据类型

集合数据类型

Hive 有三种复杂数据类型 ARRAY、MAP 和 STRUCT。ARRAY 和 MAP 与 Java 中的 Array 和 Map 类似，而 STRUCT 与 C 语言中的 Struct 类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。

3.HIVE表的DDL重要相关概念

内外表：默认为内部表，EXTERNAL 关键字可以创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表（管理表）时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。使用场景在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过 SELECT+INSERT 进入内部表。

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

创建表语法

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]

4.实例

①.创建表SQL

create external table if not exists test_table(
name string comment '姓名',
friends array<string> comment '朋友',
properties map<string, string> comment'属性',
father struct<name:string, age:int> comment'父亲'
)
partitioned by (month string)
row format delimited fields terminated by ',' #属性的分隔符为’，‘
collection items terminated by '_' #MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ':' #map格式为key：value
lines terminated by '\n'; #数据行之间的分隔符

② 导入数据

#数据
touch test.txt
vim test.txt
zhagnsan,lisi_wangwu,age:18_gender:man,zhangshanfather_39
lisi,zhangsan_wangwu,age:19_gender:woman,lisifather_40
#导入数据：
load data local inpath 'test.txt' into table test_table partition(month='202005');

③.查询数据

select * from test_table;
select * from test_table where month='202005';
select * from test_table where month='202005' union select * from test_table where month='202004';

④.管理表与外部表的互相转换

#修改内部表为外部表
alter table test_table set tblproperties('EXTERNAL'='TRUE');
#修改外部表为内部表
alter table test_table set tblproperties('EXTERNAL'='FALSE');
#注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！向test_table 表中插入数据，注意表设计有分区，插入的时候需要设置分区

⑤ 分区操作

#创建单个分区
alter table test_table add partition(month='202004');
#同时创建多个分区
alter table test_table add partition(month='202003') partition(month='202002');
#删除单个分区
alter table test_table drop partition(month='202004');
#同时删除多个分区
alter table test_table drop partition(month='202003'), partition (month='202002');
#查看分区表有多少分区
show partitions test_table;
#查看分区表结构
desc formatted test_table;
#创建二级分区表
create table test_partitions(name string) partitioned by (month string, day string)
row format delimited fields terminated by '\t';
#二级分区加载数据
touch test_partition.txt
vim test_partition.txt
zhagnsan
lisi
#加载数据
load  data local inpath 'test_partition.txt' into table test_partitions partition(month='202005', day='3');
# 二级分区查询分区数据
select * from test_partitions where month=202005 and day=3;

⑥.重命名表

语法：ALTER TABLE table_name RENAME TO new_table_name
alter table test_table rename to test1;

⑦.增加列

ALTER TABLE table_name ADDCOLUMNS (col_name data_type[COMMENT col_comment], ...) 
#注：ADD 是代表新增一字段，字段位置在所有列后面(partition 列前)
alter table test_table add columns(tabledesc string);
备注：ALTER TABLE  table_name  add columns( desc1 string COMMENT 'tttt') CASCADE；加分区表字段需要加上CASCADE

⑧.替换列

ALTER TABLE table_name replace [CLOUMN] col_old_name col_new_name column_type [CONMMENT col_conmment] [FIRST|AFTER column_name] [CASCADE|RESTRICT];
alter table test_table replace columns(tabledesc string, tabledesc1 string);

⑨.更新列

语法: ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
alter table test_table change column tabledesc tabledesc1 string;
注意：列的数据类型需要兼容

⑩. 删除表

drop table test_table;