hive数据仓库HQL语句之DDL语法

一、 数据库管理语言

1、 创建数据库

create database if not exists database_name [comment "注释内容"] [localtion "hdfs路径"] [with  dbproperties  (property=值,  属性=值)]

2、 查看数据库

1、 查看hive中的所有数据库

show  databases;

2、 查看指定数据库的创建信息

desc  database   database_name;

3、 查看指定数据库创建的详细信息

desc  database   extended     database_name

3、 修改数据库

修改dbproperties属性，数据库名和数据库的地址信息无法更改

alter  database    database_name   set    dbproperties(xxxx=xxx)

4、 删除数据库

drop  database    database_name [cascade];

二、 数据表管理语言

1、创建表

内部表/外部表

1、 概念

• 内部表删除时HDFS上的表数据也删除了，外部表删除时HDFS上的表数据还在

2、 判断

• 通过命令 desc formatted tab_name 来判断是内部表还是外部表

3、创建(普通表就是内部表，加上external则为外部表)

create external table  table_name.......

4、 转换

• 更改表的属性external，属性值为true为外部表，属性值为false为内部表

alter table  table_name set tblproperties("external"="true/false")

分区表

1、概念

• 分区表可以是外部表也可以是内部表
• 在表现形式上为：在HDFS上以文件夹的形式存储不同的分区数据
• 分区表一般用在同一批数据，表示不同时间维度或者其他角度的数据

2、 创建

create [external] table table_name(字段名 字段类型...) partitioned by(列名 列类型) row format delimited xxxx

3、 增加数据

insert into table_name partition(列名="xxxx") value("xxx","xxx")
或
load data [local] inpath "文件路径" into table table_name partiton(分区字段="分区值")

4、 查询分区表的分区个数

show partitions table_name

5、 注意事项

• 分区表可以是多级分区
• 如果分区路径已经存在，把符合格式的数据放到分区目录下，能查询回来上传的数据文件结果
• 如果分区路径不存在，手动创建分区目录，并且把文件上传到目录下，hive是无法识别我们自己手动创建的分区目录，如果要识别的话，需要修复该数据表信息

msck repair table dept_partition2

分桶表

1、 概念

• 将数据按照不同的文件进行存放
• 分桶表表现形式为在HDFS上数据表目录下有多个文件，一个文件就是一个桶

2、创建语法

create table xxxx(......) clustered by(表字段) into num buckets
或
指定分桶的排序信息(分桶类似于MR程序中的分区机制，分区类似于hashpartitioner分区机制)
create table xxxx(.....)  clustered by(表字段) sorted by(表字段  desc/asc) into num buckets

3、 增加数据

1、 insert into table_name values()
2、 通过load命令装载数据(常用，但是默认不分桶)，如果要启用分桶
	set sethive.strict.checks.bucketing=false；
	set hive.mapred.mode="nonstrict";

4、 抽样查询

select * from table_name tablesample(bucket x out of y)
从表中抽取num/y个桶的数据，其中num表示表的总桶数，x表示从那个桶开始抽取	

数据块抽样
select * from table_name tablesample(小数  percent)

2、 修改表

修改表名

alter table table_name rename to newtable_name

修改表的列信息

• 增加列

alter table table_name add columns(字段名 字段类型)```

• 修改列

alter table table_name change old_column new_column type

• 替换列

alter table table_name replace columns(列名  列的类型)
首先按照顺序依次替换表中已有的列名和类型，如果替换列的数量大于表中列的数量，多余的列会增加

修改表成为内部表或者外部表

alter table table_name tblproperties("external" = "true/false")

修改表的分区信息

• 增加分区

alter table table_name add partition(分区字段 = 值) partition(分区字段 = 值)

• 删除分区

alter table table_name drop partition(分区字段 = 值), partition(分区字段 = 值)

3、 查询表

• 查询表的字段信息和分区字段信息

desc table_name

• 查看表的详细信息

desc formatted table_name

• 展示创建的所有表

show create table table_name

4、 删除表

• 删除表可以删除内部表的结构和数据，外部表只能删除结构，不能删除数据

drop table table_name