HiveQL
HiveQL是Hive的查询语言,和其他SQL方言一样,源于ANSI SQL标准修订版,却又有自己独特的属性,语法上HiveQL和MySQL很接近,但也是有差异的,接下来我们就聊聊HiveQL使用的艺术性,该文章主要讲HiveQL数据库级别的数据定义语言(Data Definition Language,简称DDL)
部分,表级别的的DDL操作可以参考博客Hive从入门到放弃——HiveQL表级别DDL设计的艺术性(五)。
HiveQL的库
Hive中的库概念上仅仅是一个目录或者是命名空间,作用就是给表加一个逻辑组,来减少表名的冲突,Hive安装好后自带一个default库,如果没有显示的指定或者切换到某一个库,那么将会使用这个默认的default库。
注意:Hive的数据库,表文件都是存在大数据集群的文件系统内的,如常用的hdfs上,AWS S3等,切记这一点,虽然hive支持load local模式,既直接加载本地数据到hive,但是最好还是把文件先上传到hdfs内再操作比较合理;
HiveQL的数据库
Hive会为每一个数据库创建一个目录,默认目录是存在你安装Hive时候配置在hive-site.xml
文件下的hive.metastore.warehouse.dir
属性所对应的目录下,该属性的默认是hdfs下的\user\hive\warehouse
,数据库内的表会以这个目录下的子目录形式存储(default库除外,这个库本身不具有自己的目录),如当我们创建数据dw
时,如果建库的时候又没有指定库的位置,Hive将会对应的创建一个目录/user/hive/warehouse/dw.db
,默认的数据库文件目录会加后缀.db
,但是如果你重新自己定义了数据库在hdfs上的存储位置,而hdfs文件路径自己没有写.db
后缀的话,系统是不会给你自动加上的,规范的话重新定义数据库在hdfs上的存储位置时文件名可以自己加一下.db
,具体有以下几种情况;
- 表不指定数据库,则新建的表在默认库内,默认库不具有自己的目录,所以表存配置在
hive-site.xml
文件下的hive.metastore.warehouse.dir
属性所对应的目录,默认是\user\hive\warehouse
; - 数据库新建不指定hdfs存储位置,新建库存储在配置文件
hive-site.xml
下的hive.metastore.warehouse.dir
属性所对应的目录,默认是\user\hive\warehouse
,且会带.db
后缀,切换到该库下建表,表不指定位置的话,默认以这个目录下的子目录形式存储; - 数据库新建指定自定义的hdfs存储位置,则数据库会在建在自己定义的hdfs目录下,而不是默认的在
hive-site.xml
文件下的hive.metastore.warehouse.dir
属性所对应的目录,切换到该库下建表,表不指定位置的话,默认以这个目录下的子目录形式存储; - 数据库新建指定自定义的hdfs存储位置,则数据库会在建在自己定义的hdfs目录下,而不是默认的在
hive-site.xml
文件下的hive.metastore.warehouse.dir
属性所对应的目录,切换到该库下建表,表指定自定义位置且和数据库的定义的hdfs位置完全没关系的一个目录,则表数据会存储在表自定义的那个目录下,和数据库目录没有任何关系,但是在Hive使用列举表的指令show tables
时,结果内还是显示表在该库内,可以理解为,表和库hive元数据逻辑结构上是隶属关系,但是实际物理存储位置毫无关系,这种方法极其混乱,不方便文件级别隶属管理,一般肯定不会这么写,除非你想写出让同事无法维护的代码。
HiveQL建库语句和MySQL还是有点点像,具体如下;
-- 1. 精简偷懒版
create database dw;
-- 2. rerun容错版
create database if not exists dw;
--3.重定义数据库位置并带属性并支持rerun容错版
create database if not exists dw comment '数据仓库' location '/hive/warehouse' with dbproperties('creater'='rowyet','date'='20200520');
执行效果:
hive> create database if not exists dw comment '数据仓库' location '/hive/warehouse' with dbproperties('creater'='rowyet','date'='20200520');
OK
Time taken: 0.227 seconds
官方总语法,符号[]
可选,|
二选一,可见,HiveQL里面可以用schema
关键字代替database
,然这只是花里胡哨的功能,没什么大作用,还是建议大家养成通用的database
关键字。
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment] -- 数据库描述
[LOCATION hdfs_path] --自定义库外部表hdfs默认的存储位置
[MANAGEDLOCATION hdfs_path] --指定该库内部表的默认路径,不常用
[WITH DBPROPERTIES (property_name=property_value, ...)] -- 设置属性
;
HiveQL列举存在的数据库
HiveQL列举已存在的数据库语法和MySQL一样,如下;
-- 1.常用,注意databases有s,是英语单词database复数格式,少了s会报错;
show databases;
-- 2.库太多支持like模糊匹配,如查询d开头的数据库
show databases like 'd.*'
执行效果:
hive> show databases;
OK
default
dw
liuxw_test
Time taken: 0.136 seconds, Fetched: 3 row(s)
HiveQL查看某一数据库属性
Hive查看数据库属性用关键字describe
,也支持缩写desc
,具体如下;
-- 1.英语棒的娃
describe database dw;
-- 2.偷懒的娃
desc database dw;
-- 3.查看扩展信息,这里可以说明extended关键字可有可无
desc database extended dw;
执行效果:
hive> desc database extended dw;
OK
dw 数据仓库 hdfs://dw-test-cluster-007/hive/warehouse hadoop USER {date=20191008, creater=jianggongqing}
Time taken: 0.025 seconds, Fetched: 1 row(s)
HiveQL切换数据库
HiveQL切换数据保持了通用的SQL关键字,use
,具体如下;
use dw;
执行效果:
hive> use dw;
OK
Time taken: 0.024 seconds
HiveQL查看自己当前所在数据库
开了hive窗口,做到一半,被叫去吃午饭了,回来有午休了会儿,唉,刚刚的语句窗口是在哪个DB来着?都会有这个疑问对吧?当然,你也可以重复使用use
命令来切换到某一个你想要的库,查看自己当前所在数据库语句如下;
select current_database();
执行效果:
hive> select current_database();
OK
dw
Time taken: 0.805 seconds, Fetched: 1 row(s)
hive v0.8.0
可以通过set
属性来完善这一缺陷,可以用以下语句来提示hive目前所在的库;
set hive.cli.print.current.db=true;
注意观察标签hive>
变成了hive (dw)>
,带出来了数据库,执行效果:
hive> set hive.cli.print.current.db=true;
hive (dw)>
HiveQL删除数据库
HiveQL删除库也没什么特殊的,和MySQL语法类似,具体如下;
-- 1.删除数据库
drop database rowyet;
-- 2.rerun删除容错版
drop database if exists rowyet;
效果展示;
hive (rowyet)> drop database rowyet;
OK
Time taken: 0.545 seconds
注意,删除库的时候要确保库里面没表了,不然会报错,如下;
hive (rowyet)> drop database rowyet;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database rowyet is not empty. One or more tables exist.)
HiveQL修改数据库
修改数据库指令一般很少用,但是必要的时候也可以用来辅助修改下数据库的属性什么的,好过删除重建嘛,指令如下;
alter database database_name set dbproperties (property_name=property_value, ...); -- (note: schema added in hive 0.14.0)
alter database database_name set owner [user|role] user_or_role; -- (note: hive 0.13.0 and later; schema added in hive 0.14.0)
alter database database_name set location hdfs_path; -- (note: hive 2.2.1, 2.4.0 and later)
alter database database_name set managedlocation hdfs_path; -- (note: hive 4.0.0 and later)
效果展示;
hive (rowtext)> alter database rowyet set dbproperties('edited-by'='lauliu');
OK
Time taken: 0.044 seconds