HQL操作之-DDL命令

HQL操作之-DDL命令

参考:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

Hive表结构.png

查询数据库信息

-- 查看数据库信息
desc database mydb2;
desc database extended mydb2; 
describe database extended mydb2;

删除数据库

-- 删除一个空数据库
drop database databasename;
-- 如果数据库不为空，使用 cascade 强制删除 
drop database databasename cascade;

内部表和外部表

• 默认创建内部表，如果创建外部表需要使用关键字external；create external table
• 删除内部表，会把数据和表结构都删除
• 删除外部表，只删除表结构
• 生产一般都是外部表

创建内部表：

-- 默认导入数据的时候，是使用^A，^B，^C分割。下面自定义为 ; , :。
create table t1(
  id int,
  name string,
  hobby array<string>,
  addr map<string,string>
)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";

-- 查看表信息
desc t1;

desc formatted t1;

--加载数据
load data local inpath '/mnt/hadoop/data/t1.dat' into table t1;
-- 查询数据：
select * from t1;

-- 查询数据位置：
dfs -ls /user/hive/warehouse/mydb.db/t1
dfs -cat /user/hive/warehouse/mydb.db/t1/t1.dat;

-- 删除表：
drop table t1;

-- 再次查看：数据也没了
dfs -ls /user/hive/warehouse/mydb.db;

创建外部表：

-- 默认导入数据的时候，是使用^A，^B，^C分割。下面自定义为 ; , :。
create external table t1(
  id int,
  name string,
  hobby array<string>,
  addr map<string,string>
)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";

-- 查看表信息
desc t1;

desc formatted t1;

--加载数据
load data local inpath '/mnt/hadoop/data/t1.dat' into table t1;
-- 查询数据：
select * from t1;

-- 查询数据位置：
dfs -ls /user/hive/warehouse/mydb.db/t1
dfs -cat /user/hive/warehouse/mydb.db/t1/t1.dat;

-- 删除表：
drop table t1;

-- 再次查看：数据还在
dfs -ls /user/hive/warehouse/mydb.db;

内部表外部表转换

-- 默认导入数据的时候，是使用^A，^B，^C分割。下面自定义为 ; , :。
create table t1(
  id int,
  name string,
  hobby array<string>,
  addr map<string,string>
)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";

-- 查看表信息
desc t1;

desc formatted t1;

--加载数据
load data local inpath '/mnt/hadoop/data/t1.dat' into table t1;
-- 查询数据：
select * from t1;



-- 内部表转外部表
alter table t1 set tblproperties('EXTERNAL'='TRUE'); 
-- 查询表信息，是否转换成功
desc formatted t1;
-- 外部表转内部表。EXTERNAL 大写，false 不区分大小
alter table t1 set tblproperties('EXTERNAL'='FALSE'); 
-- 查询表信息，是否转换成功
desc formatted t1;

分区表

作用：避免全表扫描，提高查询效率

分区表创建和数据加载：

-- 默认导入数据的时候，是使用^A，^B，^C分割。下面自定义为 ; , :。
create table if not exists t1(
  id int,
  name string,
  hobby array<string>,
  addr map<string,string>
)
partitioned by (dt string)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";
-- 加载数据。
load data local inpath "/mnt/hadoop/data/t1.dat" into table t1 partition(dt="2020-06-01");
load data local inpath "/mnt/hadoop/data/t1.dat" into table t1 partition(dt="2020-06-02");

-- 查看有几个分区
show partitions t1;
-- 增加分区
alter table t1 add partition(dt="2020-06-03");

-- 增加多个分区，不加载数据
alter table t1;
add partition(dt='2020-06-05') partition(dt='2020-06-06');
-- 增加多个分区。准备数据
hdfs dfs -cp /user/hive/warehouse/mydb.db/t1/dt=2020-06-01 /user/hive/warehouse/mydb.db/t1/dt=2020-06-07
hdfs dfs -cp /user/hive/warehouse/mydb.db/t1/dt=2020-06-01 /user/hive/warehouse/mydb.db/t1/dt=2020-06-08
-- 增加多个分区。加载数据
alter table t1 add
partition(dt='2020-06-07') location '/user/hive/warehouse/mydb.db/t1/dt=2020-06-07' partition(dt='2020-06-08') location '/user/hive/warehouse/mydb.db/t1/dt=2020-06-08';
-- 查询数据
select * from t1;

-- 修改分区的hdfs路径：
alter table t1 partition(dt='2020-06-01') set location '/user/hive/warehouse/t1/dt=2020-06-03';

-- 可以删除一个或多个分区，用逗号隔开
alter table t1 drop partition(dt='2020-06-03'), partition(dt='2020-06-04');
-- 查看某个分区的数据：
select * from t1 where dt='2020-06-01';

分区的字段，不是表中已经存在的数据，可以将分区字段看成伪列

分桶表

当单个分区或者表的数据量过大，分区不能更细的粒度划分数据，就需要分桶技术划分。

原理：

• MR：key.hashCode % reduceTask (默认分区)
• Hive中。分桶字段.hashCode % 分桶个数

-- 创建分桶表
create table course(
    id int,
    name string,
    score int
)
clustered by (id) into 3 buckets
row format delimited fields terminated by ",";

create table course_common(
id int,
name string,
score int )
row format delimited fields terminated by ",";
-- 普通表加载数据
load data local inpath '/mnt/hadoop/data/course.dat' into table course_common;
-- 通过 insert ... select ... 给桶表加载数据
insert into table course select * from course_common;
-- 观察分桶数据。数据按照:(分区字段.hashCode) % (分桶数) 进行分区

• 分桶规则：分桶字段.hashCode % 分桶数
• 分桶表加载数据时，使用 insert……select…… 方式进行
• 网上有资料说要使用分区表需要设置 hive.enforce.bucketing=true，那是Hive 1.x 以前的版本;Hive 2.x 中，删除了该参数，始终可以分桶;

修改表和删除表

--修改表名
alter table t1 rename to t2;
-- 修改列名
alter table t2 change column id cid int;
-- 修改字段类型 修改字段数据类型时，要满足数据类型转换的要求。如int可以转为string，但是 string不能转为int
alter table t2 change column cid cid string;
-- 增加字段
alter table t2 add column (common string);
-- 删除字段
alter table t2 replace columns (cid string,cname string,score int);
-- 删除表
drop table t2;

HQL DDL小结：

• 操作对象：库、表
• 表的分类：
  • 内部表：表数据和元数据都删除
  • 外部表：只删除元数据
  • 分区表：按照指定字段分区，将数据放到不同的目录，提高SQL查询性能
  • 分桶表：按照分桶字段，将表中字段分开。分桶字段.hashCode % 分桶数据
• 命令：create、alter、drop