Hive HQL知识DDL汇总之数据库操作

Hive HQL知识DDL汇总之数据库操作

1. 概述

1. Hive可以将结构化数据、文件转换为一张表来进行数据查询和分析。
2. Hive基于SQL规范实现了一套类SQL的查询规范，称之为HQL
3. SQL本身是一种编程语言规范，数据类型定义、函数定义、DDL（data definition language）、DML（data manipulation language）。
4. HQL可以看成是输入的信息，经过hive驱动转换后（sql解析、sql优化、编译–将sql转换为mapreduce程序、执行器），最终是变成了mapreduce程序运行。
5. 既然是SQL规范，则对应HQL语句大致可以分为DDL数据定义和DML数据操作语言。本文主要讲解DDL数据定义语言，也就是针对数据库创建、修改，数据表创建、修改等的操作。
6. Hive本身不能存储数据，所以元数据也就是数据库表等结构信息存储在其他数据库中，默认是derby数据库，一般企业中配置为存储在mysql数据库中。表中数据则存储在hdfs文件系统中。

1. 本文环境是配置将元数据存储在mysql中，具体环境配置可以参考我另外一篇博文
   
2. 这里可以看到存储在mysql中的hive表的元数据信息
   
3. 可以可以看到hive元数据的版本信息
   
4. 查看数据库列表信息
   

2. 数据库和数据表在HDFS中位置

本文中，配置hive的数据库和数据库表的元数据存储在mysql数据库中。如上述截图所示
那对应的数据信息存储在哪里呢？没错，就是hdfs。

• hive的网页端：http://linux100:10002/
  
• hdfs的网页端：http://linux100:9870/dfshealth.html#tab-overview
  
  
• hive的数据存储路径

# 这个路径下就是数据库和数据表数据存储路径
/user/hive/warehouse/

3. 数据库操作

hive中默认数据库名：default

3.1 创建数据库

create database db_hive;

# 可以优化sql语句
create database if not exists db_hive;

# 可以指定数据库在hdfs上存储数据的位置（注意元数据并不存储在hdfs上）.但一般不会这么指定，而是在配置文件中统一配置存储路径
create database if not exists db_hive location '/db_hive.db';

可以看出就算是创建数据库的操作，也是有一个完整的流程：

编译指令(Compiling command)
指令分析（Semantic Analysis）
完成指令编译（ Completed compiling command）
执行指令（Executing command）
启动任务（Starting task）
完成指令执行（ Completed executing command）
执行结果显示（OK）

3.2 查询数据库

1. 显示数据库

• 普通查询，显示所有数据库

show databases;

• 过滤查询, 这里使用like进行模糊查询，*是通配符号

show databases like 'db_do*';

show databases like '*it';

2. 查看数据库详细信息， desc指令

• 显示数据库信息

desc database db_doit;

注意,comment可以针对字段也可以针对数据库，数据库表加描述

create database db_comment_test comment 'this is about db comment test';

• 显示数据库详细信息

desc database extended db_doit;

3. 切换数据库

use db_doit;

# 通过以下指令查询当前数据库
 select current_database();

3.3 修改数据库

修改数据库和表的指令和mysql一样，都是alter指令。
使用alter database对dbproperties 设置键值对属性值。
注意，数据库其他元数据信息不可更改，这一点和mysql等关系型数据库不一样，因为hive并不是一个真正的数据库。所以hive的数据库名、数据库所在目录位置都不能修改

# 这里把创建时间修改到2030年
alter database db_doit set dbproperties('createtime'='20300905');

# 使用以下指令查看详细信息
desc database extended db_doit;

3.4 删除数据库

1. 删除空数据库drop指令
   删除前
   

drop database db_drop_test;

删除后

2. 删除前判断数据库是否存在

drop database if exists db_drop_test;

3. 数据库不为空，强制删除 cascade参数

drop database db_drop_test2;

普通删除前

普通删除，删除失败

强制删除

# cascade 串联和级联的意思，可以理解为递归删除文件一样
drop database db_drop_test2 cascade;

删除后，删除成功