Hive DDL操作

数据库DDL操作

1.创建数据库

hive > create database db_hive;
# 或者
hive > create database if not exists db_hive;

数据库在HDFS上的默认存储路径是/usr/hive/warehouse/数据库名.db

2. 显示所有数据库

hive > show databases;

3. 查询数据库

hive > show databases like 'db_hive*';

4. 查询数据库详情

hive > desc database db_hive;
#或者
hive > desc database extended db_hive;

5. 切换当前数据库

hive > use db_hive;

6. 删除数据库

#删除空数据库
hive > drop database db_hive;
#如果删除数据库不存在，最好采用 if exists 判断数据库是否存在
hive > drop database if exists db_hive;
#如果数据库中有表存在，这里需要使用cascade强制删除数据库
hive > drop database if exists db_hive cascade;

表DDL操作

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]  row format delimited fields terminated by “分隔符”
[STORED AS file_format] 
[LOCATION hdfs_path]

• 字段解释说明
  • create table 创建一个指定名字的表
  • external 创建一个外部表，在建表的同时指定一个指向实际数据的路径（location),指定表的数据保存在哪里
  • comment 为表和列添加注释
  • partitioned by 创建分区表
  • clustered by 创建分桶表
  • sorted by 按照字段排序（一般不常用）
  • row format 指定每一行中字段的分隔符
    • row format delimited fields terminated by '\t'
  • stored as 指定存储文件类型
    • 常用的存储文件类型：sequencefile、textfile、orcfile
    • 如果文件数据是纯文本，可以使用stored as textfile。如果数据需要压缩，使用stored as sequencefile
  • location 指定表在HDFS上的存储位置

2. 创建内部表

• 使用标准建表语句创建

use myhive;
create table stu(id int,name string);
insert into stu(id,name) values(1,"zhangsan");
#查询
select * from stu;

• 子查询语句建表

create table if not exists myhive.stu1 as select id,name from stu;
#查询
select * from stu1;

• 根据已经存在的表结构创建表

create table if not exists myhive2.stu2 like stu;
#查询
select * from stu2;
* hql标准语句创建表
```shell
use myhive;
create table if not exists stu3(
id int,
name string
)row format delimited fields terminated by '\t'
stored as textfile
location '/user/stu3'

3. 创建外部表
   外部表特点：指定其他的hdfs路径的数据加载到表当中，所以hive表会认为自己不完全独占这份数据，所以删除hive表的时候，数据任然存在hdfs当中，不会删除
   HQL建表语句中要加上external关键字，location字段可以指定，也可以不指定

create external table myhive.teacher(t_id string,t_name string)
row format delimited fields terminated by '\t';

4. 内部表和外部表的互相转换

#内部表转换为外部表
alter table stu set tblproperties('EXTERNAL'='TRUE');
#外部表转换为内部表
alter table teacher set tblproperties('EXTERNAL'='TRUE');

5. 内部表和外部表的区别

   • 创建语句的区别
     外部表在创建的时候需要加上external关键字
   • 删除表之后的区别
     内部表删除后，表的元数据和真实数据都被删除了
     外部表删除后，仅仅只是把该表的元数据删除了，真实数据还在，后期还是可以恢复出来

6. 内部表和外部表的使用时机

   • 内部表由于删除表时会同步删除HDFS的数据文件，所以确定如果一个表仅删除是你独占使用，其他人不使用的时候就可以创建内部表，如果一个表的文件数据，其他人也要使用，那么就创建外部表
   • 一般外部表都是用在数据仓库的ODS层
   • 内部表都是用在数据仓库的DW层