数据仓库Hive编程——HiveQL的数据定义（四）：分区表和管理表

von Neumann

已于 2022-10-07 20:58:46 修改

阅读量1.4w

点赞数 4

分类专栏：大数据与数据科学文章标签： Hive 数据仓库 Hadoop HQL SQL

于 2019-08-08 20:06:44 首次发布

本文链接：https://blog.csdn.net/hy592070616/article/details/97649465

版权

大数据与数据科学专栏收录该内容

17 篇文章 8 订阅

订阅专栏

分类目录：商业智能《数据仓库Hive编程》总目录

数据分区的一般概念存在已久。其可以有多种形式，但是通常使用分区来水平分散压力，将数据从物理上转移到和使用最频繁的用户更近的地方，以及实现其他目的。

Hive中有分区表的概念。我们可以看到分区表具有重要的性能优势，而且分区表还可以将数据以一种符合逻辑的方式进行组织，比如分层存储。

我们首先会讨论下分区管理表。重新来看《数据仓库Hive编程》中前面几篇文章日到的的那张emplyees表并假设我们在一个非常大的跨国公司工作。我们的HR人员经常会执行一些带WHERE语句的查询，这样可以将结果限制在某个特定的国家或者某个特定的第一级细分。为简单起见我们将只使用到state。在address字段中已经重复包含了州信息。这和state分区是不同的。我们可以在字段address中删除state元素。查询中并不会造成模糊不清的问题，因为我们需要通过使用address.state才能调用到address中这个元素的值。那么，让我们先按照country再按照state来对数据进行分区：

CREATE TABLE employees (
　name　　　　　　　STRING,
　salary　　　　　　FLOAT,
　subordinates 　　ARRAY<STRING>,
　deductions 　　　 MAP<STRING, FLOAT>,
　address 　　　　　STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
)
PARTITIONED BY (country STRING, state STRING);

分区表改变了Hive对数据存储的组织方式。如果我们是在mydb数据库中创建的这个表，那么对于这个表只会有一个employees目录与之对应：

hdfs://master_server/user/hive/warehouse/mydb.db/employees

但是，Hive现在将会创建好可以反映分区结构的子目录。例如：

...
.../employees/country=CA/state=AB
.../employees/country=CA/state=BC
...
.../employees/country=US/state=AL
.../employees/country=US/state=AK
...

是的，那些是实际的目录名称。州目录下将会包含有零个文件或者多个文件，这些文件中存放着那些州的雇员信息。

分区字段（这个例子中就是country和state）一旦创建好，表现得就和普通的字段一样。事实上，除非需要优化查询性能，否则使用这些表的用户不需要关心这些“字段”是否是分区字段。
例如，下面这个查询语句将会查找出在美国伊利诺斯州的所有雇员：

SELECT * FROM employees
WHERE country = 'US' AND state = 'IL';

需要注意的是，因为country和state的值已经包含在文件目录名称中了，所以也就没有必要将这些值存放到它们目录下的文件中了。事实上，数据只能从这些文件中获得，因此用户需要在表的模式中说明这点，而且这个数据浪费空间。

对数据进行分区，也许最重要的原因就是为了更快地查询。在前面那个将结果范围限制在伊利诺斯州的雇员的查询中，仅仅需要扫描一个目录下的内容即可。即使我们有成千上万个国家和州目录，除了一个目录其他的都可以忽略不计。对于非常大的数据集，分区可以显著地提高查询性能，除非对分区进行常见的范围筛选。

当我们在WHERE子句中增加谓词来按照分区值进行过滤时，这些谓词被称为分区过滤器。

即使你做一个跨越整个美国的查询，Hive也只会读取65个文件目录，其中包含有50个州，9大地区，以及哥伦比亚特区和6个军事属地。

当然，如果用户需要做一个查询，查询对象是全球各地的所有员工，那么这也是可以做到的。Hive会不得不读取每个文件目录，但这种宽范围的磁盘扫描还是比较少见的。

但是，如果表中的数据以及分区个数都非常大的话，执行这样一个包含有所有分区的查询可能会触发一个巨大的MapReduce任务。一个高度建议的安全措施就是将Hive设置为strict模式，这样如果对分区表进行查询而WHERE子句没有加分区过滤的话，将会禁止提交这个任务。用户也可以按照下面的语句将属性值设置为nostrict：

hive> set hive.mapred.mode=strict;

hive> SELECT e.name, e.salary FROM employees e LIMIT 100;
FAILED: Error in semantic analysis: No partition predicate found for
　Alias "e" Table "employees"

hive> set hive.mapred.mode=nonstrict;

hive> SELECT e.name, e.salary FROM employees e LIMIT 100;
John Doe 100000.0
...

可以通过SHOW PARTITIONS命令查看表中存在的所有分区：

hive> SHOW PARTITIONS employees;
...
Country=CA/state=AB
country=CA/state=BC
...
country=US/state=AL
country=US/state=AK
...

如果表中现在存在很多的分区，而用户只想查看是否存储某个特定分区键的分区的话，用户还可以在这个命令上增加一个指定了一个或者多个特定分区字段值的PARTITION子句，进行过滤查询：

hive> SHOW PARTITIONS employees PARTITION(country='US');
country=US/state=AL
country=US/state=AK
...

hive> SHOW PARTITIONS employees PARTITION(country='US', state='AK');
country=US/state=AK

DESCRIBE EXTENDED employees命令也会显示出分区键：

hive> DESCRIBE EXTENDED employees;
name　　　　　　string,
salary　　　　　float,
...
address　　　　 struct<...>,
country 　　　　string,
state 　　　　　string

Detailed Table Information...
partitionKeys:[FieldSchema(name:country, type:string, comment:null),
FieldSchema(name:state, type:string, comment:null)],
...

输出信息中的模式信息部分会将country和state以及其他字段列在一起，因为就查询而言，它们就是字段。Detailed Table Information将country和state作为分区键处理。这两个键当前的注释都是null，我们也可以像给普通的字段增加注释一样给分区字段增加注释。

在管理表中用户可以通过载入数据的方式创建分区。如下例中的语句在从一个本地目录$HOME/california-employees载入数据到表中的时候，将会创建一个US和CA分区。用户需要为每个分区字段指定一个值。请注意我们在HiveQL中是如何引用HOME环境变量的：

LOAD DATA LOCAL INPATH '${env:HOME}/california-employees'
INTO TABLE employees
PARTITION (country = 'US', state = 'CA');

Hive将会创建这个分区对应的目录…/employees/country=US/state=CA，而且$HOME/california-employees这个目录下的文件将会被拷贝到上述分区目录下。

外部分区表

外部表同样可以使用分区。事实上，用户可能会发现，这是管理大型生产数据集最常见的情况。这种结合给用户提供了一个可以和其他工具共享数据的方式，同时也可以优化查询性能。

因为用户可以自己定义目录结构，因此用户对于目录结构的使用具有更多的灵活性。

我们举一个新例子，非常适合这种场景，即日志文件分析。对于日志信息，大多数的组织使用一个标准的格式，其中记录有时间戳、严重程度，也许还包含有服务器名称和进程ID，然后跟着一个可以为任何内容的文本信息。假设我们是在我们的环境中进行数据抽取、数据转换和数据装载过程（ETL），以及日志文件聚合过程的，将每条日志信息转换为按照制表键分割的记录，并将时间戳解析成年、月和日3个字段，剩余的hms部分（也就是时间戳剩余的小时、分钟和秒部分）作为一个字段，因为这样显得清楚多了。一种方式是用户可以使用Hive或者Pig内置的字符串解析函数来完成这个日志信息解析过程。另一种方式是，我们可以使用较小的数值类型来保存时间戳相关的字段以节省空间。这里，我们没有采用后面的解决办法。我们可以按照如下方式来定义对应的Hive表：

CREATE EXTERNAL TABLE IF NOT EXISTS log_messages (
　hms 　　　　 INT,
　severity 　　STRING,
　server 　　　STRING,
　process_id 　INT,
　message 　　 STRING)
PARTITIONED BY (year INT, month INT, day INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

我们现在假定将日志数据按照天进行划分，划分数据尺寸合适，而且按天这个粒度进行查询速度也足够快。

《数据仓库Hive编程》之前的文章中我们创建过一个非分区外部表，是一个股票交易表，那时要求使用一个LOCATION子句。对于外部分区表则没有这样的要求。有一个ALTER TABLE语句可以单独进行增加分区。这个语句需要为每一个分区键指定一个值，本例中，也就是需要为year、month和day这3个分区键都指定值。下面是一个例子，演示如何增加一个2012年1月2日的分区：

ALTER TABLE log_messages ADD PARTITION(year = 2012, month = 1, day = 2)
LOCATION 'hdfs://master_server/data/log_messages/2012/01/02';

我们使用的目录组织习惯完全由我们自己定义。这里，我们按照分层目录结构组织，因为这是一个合乎逻辑的数据组织方式，但是并非要求一定如此。我们可以遵从Hive的目录命名习惯，但是也并非要求一定如此。

这种灵活性的一个有趣的优点是我们可以使用像Amazon S3这样的廉价的存储设备存储旧的数据，同时保存较新的更加“有趣的”数据到HDFS中。例如，每天我们可以使用如下的处理过程将一个月前的旧数据转移到S3中。

① 将分区下的数据拷贝到S3中。例如，用户可以使用hadoop distcp命令：
hadoop distcp /data/log_message/2011/12/02 s3n：//ourbucket/logs/2011/12/02
② 修改表，将分区路径指向到S3路径： A
LTER TABLE log_messages PARTITION(year = 2011, month = 12, day = 2)
SET LOCATION 's3n://ourbucket/logs/2011/01/02';
③ 使用hadoop fs –rmr 命令删除掉HDFS中的这个分区数据：
hadoop fs -rmr /data/log_messages/2011/01/02

并非一定要是Amazon弹性MapReduce用户才能够这样使用S3。Apache Hadoop分支版本包含了对S3的支持。用户仍旧是可以查询这些数据的，甚至允许查询越过一个月时间范围的“界限”，也就是有些数据是从HDFS中读取的，有些数据是从S3中读取的。

顺便说一下，Hive不关心一个分区对应的分区目录是否存在或者分区目录下是否有文件。如果分区目录不存在或分区目录下没有文件，则对于这个过滤分区的查询将没有返回结果。当用户想在另外一个进程开始往分区中写数据之前创建好分区时，这样做是很方便的。数据一旦存在，对于这份数据的查询就会有返回结果。

这个功能所具有的另一个好处是：可以将新数据写入到一个专用的目录中，并与位于其他目录中的数据存在明显的区别。同时，不管用户是将旧数据转移到一个“存档”位置还是直接删除掉，新数据被篡改的风险都被降低了，因为新数据的数据子集位于不同的目录下。

和非分区外部表一样，Hive并不控制这些数据。即使表被删除，数据也不会被删除。

和分区管理表一样，通过SHOW PARTITIONS命令可以查看一个外部表的分区：

hive> SHOW PARTITIONS log_messages;
...
year=2011/month=12/day=31
year=2012/month=1/day=1
year=2012/month=1/day=2

…
同样地，DESCRIBE EXTENDED log_messages语句会将分区键作为表的模式的一部分，和partitionKeys列表的内容同时进行显示：

hive> DESCRIBE EXTENDED log_messages;
...
message 　　　　string,
year 　　　　　　int,
month 　　　　　int,
day 　　　　　　int

Detailed Table Information...
partitionKeys:[FieldSchema(name:year, type:int, comment:null),
FieldSchema(name:month, type:int, comment:null),
FieldSchema(name:day, type:int, comment:null)],
...

这个输出缺少了一个非常重要的信息，那就是分区数据实际存在的路径。这里有一个路径字段，但是该字段仅仅表示如果表是管理表其会使用到的Hive默认的目录。不过，我们可以通过如下方式查看到分区数据所在的路径：

hive> DESCRIBE EXTENDED log_messages PARTITION (year=2012, month=1, day=2);
...
location:s3n://ourbucket/logs/2011/01/02,
...

我们通常会使用分区外部表，因为它具有非常多的优点，例如逻辑数据管理、高性能的查询等。

ALTER TABLE … ADD PARTITION语句并非只有对外部表才能够使用。对于管理表，当有分区数据不是由我们之前讨论过的LOAD和INSERT语句产生时，用户同样可以使用这个命令指定分区路径。用户需要记住并非所有的表数据都是放在通常的warehouse目录下的，同时当删除管理表时，这些数据不会连带被删除掉。因此，从“理智的”角度来看，是否敢于对管理表使用这个功能是一个问题。

自定义表的存储格式

在《数据类型和文件格式（三）：文本文件数据编码》中，我们谈论过Hive的默认存储格式是文本文件格式，这个也可以通过可选的子句STORED AS TEXTFILE显式指定，同时用户还可以在创建表时指定各种各样的分隔符。这里我们重新展示下之前讨论过的那个employees表：

CREATE TABLE employees (
　name　　　　　　　STRING,
　salary　　　　　　FLOAT,
　subordinates 　　ARRAY<STRING>,
　deductions 　　　 MAP<STRING, FLOAT>,
　address 　　　　　STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
COLLECTION ITEMS TERMINATED BY '\002'
MAP KEYS TERMINATED BY '\003'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

TEXTFILE意味着所有字段都使用字母、数字、字符编码，包括那些国际字符集，尽管我们可以发现Hive默认是使用不可见字符来作为分隔符的。使用TEXTFILE就意味着，每一行被认为是一个单独的记录。

用户可以将TEXTFILE替换为其他Hive所支持的内置文件格式，包括SEQUENCEFILE和RCFILE，这两种文件格式都是使用二进制编码和压缩（可选）来优化磁盘空间使用以及I / O带宽性能的。

对于记录是如何被编码成文件的，以及列是如何被编码为记录的，Hive指出了它们之间的不同。用户可以分别自定义这些行为。记录编码是通过一个inputformat对象来控制的。Hive使用了一个名为org.apache.hadoop.mapred.TextInputFormat的Java类。如果用户不熟悉Java的话，这种点分割的命名语法表明了包的一个分层的树形命名空间，这个结构和Java代码的目录结构是对应的。最后一个名字TextInputFormat是位于最顶层包mapred下的一个类。

用户还可以指定第三方的输入和输出格式以及SerDe，这个功能允许用户自定义Hive本身不支持的其他广泛的文件格式。

这里有一个使用了自定义SerDe、输入格式和输出格式的完整的例子，其可以通过Avro协议访问这些文：

CREATE TABLE kst
PARTITIONED BY (ds string)
ROW FORMAT SERDE 'com.linkedin.haivvreo.AvroSerDe'
WITH SERDEPROPERTIES ('schema.url'='http://schema_provider/kst.avsc')
STORED AS
INPUTFORMAT 'com.linkedin.haivvreo.AvroContainerInputFormat'
OUTPUTFORMAT 'com.linkedin.haivvreo.AvroContainerOutputFormat';

ROW FORMAT SERDE …指定了使用的SerDe。Hive提供了WITH SERDEPROPERTIES功能，允许用户传递配置信息给SerDe。Hive本身并不知晓这些属性的含义，需要SerDe去决定这些属性所代表的含义。需要注意的是，每个属性名称和值都应该是带引号的字符串。

STORED AS INPUTFORMAT … OUTPUTFORMAT …子句分别指定了用于输入格式和输出格式的Java类。如果要指定，用户必须对输入格式和输出格式都进行指定。

需要注意的是，DESCRIBE EXTENDED table命令会在DETAILED TABLE INFORMATION部分列举出输入和输出格式以及SerDe和SerDe所自带的属性信息。

von Neumann

关注

4
点赞
踩
6

收藏

觉得还不错? 一键收藏
打赏
1
评论
数据仓库Hive编程——HiveQL的数据定义（四）：分区表和管理表

数据分区的一般概念存在已久。其可以有多种形式，但是通常使用分区来水平分散压力，将数据从物理上转移到和使用最频繁的用户更近的地方，以及实现其他目的。Hive中有分区表的概念。我们可以看到分区表具有重要的性能优势，而且分区表还可以将数据以一种符合逻辑的方式进行组织，比如分层存储。我们首先会讨论下分区管理表。重新来看《数据仓库Hive编程》中前面几篇文章日到的的那张emplyees表并假设我们在一个...
复制链接

扫一扫