HiveQL是Hive查询语言。和普遍使用的所有SQL方言一样,它不完全遵守任一种ANSISQL标准的修订版。HiveQL可能和MySQL的方言最接近,但是两者还是存在显著性差异的。Hive不支持行级插入操作、更新操作和删除操作。Hive也不支持事务。当然了,大部分的HiveQL还是很常见的。本篇首先介绍Hive中的数据库概念以及操作细节。
1 Hive中的数据库
1.1 创建数据库
hive中数据库的概念本质上仅仅是表的一个目录或者命名空间。然而,对于具有很多组和用户的大集群来说,这是非常有用的,因为这样可以避免表命名冲突。通常会使用数据库来将生产表组织成逻辑组。
如果用户没有显式指定数据库,那么将会使用默认的数据库default
通过下面语句创建一个数据库:
hive> CREATE DATABASE IF NOT EXISTS financials;
hive会为每个数据库创建一个目录。数据库中的表将会以这个数据库目录的子目录形式存储。但有一个例外就是default数据库中的表,因为这个数据库本身没有自己的目录。数据库所在的目录位于属性hive.metastore.warehouse.dir所指定的顶层目录之后,假设配置项的值是/hive/warehouse,那么当我们创建数据库financials时,hive将会对应地创建一个目录/hive/warehouse/financials.db。这里请注意,数据库的文件目录名是以.db结尾的。
上图除了红框处的其他目录都是默认数据库default里的表目录
我们可以通过如下的命令来修改这个默认的位置:
hive> CREATE DATABASE IF NOT EXISTS financials
> LOCATION '/user';
还可以为这个数据库增加一个描述信息,这样通过DESCRIBE DATABASE 命令就可以查看到该信息:
hive> CREATE DATABASE financials
> COMMENT 'Holds all financial tables';
从上图结果中可以看到DESCRIBE DATABASE语句也会显示出这个数据库所在的文件目录位置路径
1.2 设置当前的工作数据库
USE命令用于将某个数据库设置为用户当前的工作数据库,和在文件系统中切换工作目录是一个概念:
hive> USE financials;
然后,使用像SHOW TABLES这样的命令就会显示当前这个数据库下所有的表。不幸的是,并没有一个命令可以让用户查看当前所在的是哪个数据库。幸运的是,在hive中是可以重复使用USE命令的,这是因为在hive中并没有嵌套数据库的概念。
可以通过设置一个属性值在提示符里显示当前所在的数据库:
hive> set hive.cli.print.current.db=true;
1.2 删除数据库
最后,用户可以删除数据库,执行后其在/hive/warehouse/下对应的目录financials.db也同时被删除:
hive> DROP DATABASE IF EXISTS financials;
默认情况下,hive是不允许用户删除一个包含有表的数据库的。用户要么先删除数据库中的表,然后再删除数据库;要么在删除命令的最后加上关键字CASCADE,这样可以使hive自行先删除数据库中的表:
hive> DROP DATABASE IF EXISTS financials CASCADE;
2 创建表
2.1 CREATE TABLE
CREATE TABLE语句遵从SQL语法惯例,但是hive的这个语句中具有显著的功能扩展,使其可以具有更广泛的灵活性。例如,可以定义表的数据文件存储在什么位置、使用什么样的存储格式等等。
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name -- (Note: TEMPORARY available in Hive 0.14.0 and later)
[(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
[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]
[SKEWED BY (col_name, col_name, ...) -- (Note: Available in Hive 0.10.0 and later)]
ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
[STORED AS DIRECTORIES]
[
[ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] -- (Note: Available in Hive 0.6.0 and later)
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)] -- (Note: Available in Hive 0.6.0 and later)
[AS select_statement]; -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)
其中,ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’通过指定的分隔符(这里是’\t’)将行分解成列。如果某行的字段个数比预期的要少,那么缺少的字段将返回null;如果某行的字段个数比预期的要多,那么多出的字段将会被省略掉。
根据上述经建表命令格式,创建一个employees表:
CREATE TABLE IF NOT EXISTS mydb.employees (
name STRING COMMENT 'Employee name',
salary FLOAT COMMENT 'Employee salary',
subordinates ARRAY<STRING> COMMENT 'Names of subordinates',
deductions MAP<STRING, FLOAT> COMMENT 'Keys are deductions names, values are percentages',
address STRUCT<street:STRING, city:STRING, state:STRING, zip:INT> COMMENT 'Home address' )
COMMENT 'Description of the table'
LOCATION '/hive/warehouse/mydb.db/employees'
TBLPROPERTIES ('creator'='by', 'created_at'='2019-05-03 17:26');
首先可以看到,如果我们当前所处的数据库并非是目标数据库,那么是可以在表名前增加一个数据库名来进行指定的,也就是mydb。
我们可以在字段类型后为每个字段增加一个注释。和数据库一样,我们也可以为表本身添加一个注释,还可以自定义一个或多个表属性。大多数情况下,TBLPROPERTIES的主要作用是按键-值对的格式为表增加额外的文档说明。
最后,从例子可看出可以根据情况为表中的数据指定一个存储路径。例子中是使用默认的路径/hive/warehouse/mydb.db/employees。其中/hive/warehouse是默认的“数据仓库”路径地址,mydb.db是数据库目录,employees是表目录。
默认情况下,hive总是将创建的表的目录放置在这个表所属的数据库目录下。不过,default数据库是个例外,其在/hive/warehouse下并没有对应一个数据库目录。因此default数据库中的表目录会直接位于/hive/warehouse目录下(除非用户明确指定其他路径)
2.2 复制表模式
还可以拷贝一张以及存在的表的模式(而无需拷贝数据):
CREATE TABLE IF NOT EXISTS mydb.employees2 LIKE mydb.employees;
2.3 列举所有表
SHOW TABLES命令可以列举出所有的表,如果不增加其他参数,那么只会显示当前工作数据库下的表。但即使不在我们想要的数据库下,还是可以列举指定数据库下的表的:
SHOW TABLE IN mydb;
2.4 查看表的详细信息
接下来可以使用DESCRIBE FORMATTED mydb.employees命令来查看这个表的详细表结构信息(如果当前所处的工作数据库就是mydb的话,可以不加mydb.这个前缀)。
hive> DESCRIBE FORMATTED mydb.employees;
OK
# col_name data_type comment
name string Employee name
salary float Employee salary
subordinates array<string> Names of subordinates
deductions map<string,float> Keys are deductions names, values are percentages
address struct<street:string,city:string,state:string,zip:int> Home address
# Detailed Table Information
Database: mydb
Owner: root
CreateTime: Fri May 03 19:25:26 CST 2019
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://192.168.230.10:9000/hive/warehouse/mydb.db/employees
Table Type: MANAGED_TABLE
Table Parameters:
comment Description of the table
created_at 2019-05-03 17:26
creator by
transient_lastDdlTime 1556882726
# Storage Information
SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
serialization.format 1
Time taken: 0.269 seconds, Fetched: 33 row(s)
注意以Location:开头的那行描述信息。这个是hive在hdfs中的存储表中数据的完整的URL目录路径。
2.5 管理表
我们目前所创建的表都是所谓的管理表MANAGED_TABLE,有时也被称为内部表。因为这种表,hive会(或多或少地)控制着数据的生命周期。正如我们所看见的,hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如/hive/warehouse)所定义的目录的子目录下。当我们删除一个管理表时,hive也会删除这个表中的数据。
但是,管理表不方便和其他工作共享数据。例如,假设我们有一份由Pig或者其他工具创建并且主要由这一工具使用的数据,同时我们还想使用hive在这份数据上执行一些查询,可是并没有给予hive对数据的所有权,我们可以创建一个外部表指向这份数据,而并不需要对其具有所有权。
2.6 外部表
假设数据文件已经位于hdfs下的/data/stocks目录下,下面的语句将创建一个外部表,其可以读取所有位于/data/stocks目录下的以逗号分隔的数据:
CREATE EXTERNAL TABLE IF NOT EXISTS stocks (
exchange STRING,
symbol STRING,
ymd STRING,
price_open FLOAT,
price_high FLOAT,
price_low FLOAT,
price_close FLOAT,
volume INT,
price_adj_close FLOAT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/data/stocks';
关键字EXTERNAL告诉hive这个表是外部的,而后面的LOCATION字句则告诉hive数据位于哪个路径下。因为表是外部的,所以hive并非认为其完全拥有这份数据。因此,删除该表并不会删除这份数据,但是描述表的元数据信息会被删除掉。
我们可以在DESCRIBE FORMATTED tablename语句的输出中查看到表是否是管理表或外部表。对于管理表,我们可看到Table Type: MANAGED_TABLE,对于外部表,则是Table Type: EXTERNAL_TABLE
我们还可以对一张存在的表进行表结构复制(而不会复制数据):
CREATE EXTERNAL TABLE IF NOT EXISTS mydb.employee3
LIKE mydb.employees
LOCATION '/path/to/data';
这里,如果上述语句中省略掉EXTERNAL关键字而且源表是外部表的话,那么生成的新表也将是外部表。如果省略掉EXTERNAL而且源表是管理表的话,那么生成的新表也将是管理表。但是,如果语句中含有EXTERNAL关键字而源表是管理表的话,那么生成的新表将是外部表。
3 删除表
hive支持和SQL中DROP TABLE命令类似的操作:
DROP TABLE IF EXISTS taable_name
对于管理表,表的元数据信息和表内的数据都会被删除。而对于外部表,表的元数据信息会被删除,但是表中的数据不会被删除。
事实上,如果用户开启了Hadoop回收站功能(这个功能默认是关闭的),那么数据将会被转移到用户在分布式文件系统中的用户根目录下的.Trash目录下,也就是HDFS中的/user/$USER/.Trash目录。如果想开启这个功能,只需要在core-site.xml中将配置属性fs.trash.interval的值设置为一个合理的正整数即可。这个值是“回收站检查点”的时间间隔,单位是分钟。因此如果设置值为1440,那么就表示24小时。如果用户不小心删除了一张存储着重要数据的管理表的话,那么可以先重建表,然后重建所需要的分区,再从.Trash目录中将误删的文件移动到正确的文件目录下来重新存储数据。
参考:《Hive编程指南》