读取Hive表数据

Apache Hive是一个分布式数据仓库基础架构。 Hive有助于管理支持多种数据格式的大型数据集，包括逗号分隔值(.csv)、TextFile、RCFile、 ORC、和Parquet。

PXF Hive连接器读取Hive表中存储的数据。本节描述如何使用PXF Hive连接器。

先决条件

在使用PXF处理Hive表数据之前，请确保您已满足PXF Hadoop 先决条件。

如果您打算将PXF过滤器下推与Hive整数类型一起使用，请确保配置参数hive.metastore.integral.jdo.pushdown存在，并且在两个配置文件中的hive-site.xml中均设置为true。Hadoop集群和 $PXF_CONF/servers/default/hive-site.xml。 有关更多信息，请参考更新Hadoop配置。

Hive数据格式

PXF Hive连接器支持多种数据格式，并定义了以下用于访问这些格式的配置文件：

文件格式 描述 配置文件
TextFile 平面文件，其数据以逗号，制表符或空格分隔的值格式或JSON表示法。 Hive, HiveText
SequenceFile 平面文件，由二进制键/值对组成。 Hive
RCFile 列式记录文件，由二进制键/值对组成; 高行压缩率。 Hive, HiveRC
ORC 优化了的行列数据文件，具有stripe、footer、和postscript部分; 减少数据大小. Hive, HiveORC, HiveVectorizedORC
Parquet 压缩的列式数据。 Hive

注意: Hive 配置文件支持所有文件存储格式。 它将对底层文件类型使用最佳的 Hive* 配置文件。

数据类型映射

PXF Hive连接器支持基础和复杂数据类型。

基础数据类型

要在Greenplum数据库中展示Hive数据，请将使用基础数据类型的数据值映射到相同类型的Greenplum数据列。

下表总结了Hive基本数据类型的外部映射规则。

Hive 数据类型 Greenplum 数据类型
boolean bool
int int4
smallint int2
tinyint int2
bigint int8
float float4
double float8
string text
binary bytea
timestamp timestamp

注意: HiveVectorizedORC 配置文件不支持 timestamp 数据类型。

复杂数据类型

Hive支持的数据类型，包括数组(array), 结构(struct), 映射(map), 以及混合类型。 PXF 将以上复杂数据类型映射为 text。您可以创建Greenplum数据库函数或应用程序代码来提取这些复杂数据类型的子部分。

本章稍后提供通过 Hive 和 HiveORC 配置文件使用复杂数据类型的示例。

注意: HiveVectorizedORC 配置文件不支持复杂类型。

样本数据集

本主题中介绍的示例在公共数据集上运行。 这个简单的数据集为零售业务建模，并包含具有以下名称和数据类型的字段：

字段名 数据类型
location text
month text
number_of_orders integer
total_sales double

准备样本数据集以备用：

  1. 首先，创建一个文本文件：
$ vi /tmp/pxf_hive_datafile.txt
  1. 将以下数据添加到 pxf_hive_datafile.txt; 注意使用逗号 , 分隔四个字段的值：
Prague,Jan,101,4875.33
Rome,Mar,87,1557.39
Bangalore,May,317,8936.99
Beijing,Jul,411,11600.67
San Francisco,Sept,156,6846.34
Paris,Nov,159,7134.56
San Francisco,Jan,113,5397.89
Prague,Dec,333,9894.77
Bangalore,Jul,271,8320.55
Beijing,Dec,100,4248.41

记住 pxf_hive_datafile.txt 的路径; 您将在后续的练习中使用它。

Hive命令行

Hive命令行是类似 psql 的子系统。启动Hive命令行：

$ HADOOP_USER_NAME=hdfs hive

默认的Hive数据库名为 default。

示例：创建Hive表

创建一个Hive表以展现示例数据集。

  1. 在 default 数据库中创建一个名为 sales_info 的Hive表：
hive> CREATE TABLE sales_info (location string, month string,
        number_of_orders int, total_sales double)
        ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
        STORED AS textfile;

注意：

* `STORED AS textfile` 子句指示Hive以 Textfile (默认) 格式创建表。 Hive Textfile格式支持逗号、制表符和空格分隔的值， 以及用JSON表示的数据。
* `DELIMITED FIELDS TERMINATED BY` 子句定义数据记录(行)中的字段分隔符。 `sales_info` 表字段分隔符是逗号(`,`)。
  1. 将 pxf_hive_datafile.txt 示例数据文件加载到您刚刚创建的 sales_info 表中：
hive> LOAD DATA LOCAL INPATH '/tmp/pxf_hive_datafile.txt'
        INTO TABLE sales_info;

在本章稍后的例子中，您将通过PXF直接访问 sales_info Hive 表。 另外您还将在其他Hive文件格式类型的表中插入 sales_info 数据，并使用PXF直接访问这些数据。

  1. 在sales_info上执行查询以验证是否成功加载了数据：
hive> SELECT * FROM sales_info;

确定Hive表的HDFS位置

如果需要指定Hive表的HDFS文件位置，请使用其HDFS文件路径引用它。 您可以通过 DESCRIBE 命令确定Hive表在HDFS中的位置。例如：

hive> DESCRIBE EXTENDED sales_info;
Detailed Table Information
...
location:hdfs://<namenode>:<port>/apps/hive/warehouse/sales_info
...

查询外部Hive数据

您可以创建一个Greenplum数据外部表以访问Hive表中的数据。 如前所述，PXF Hive 连接器定义了特定的配置文件以支持不同的文件格式。 这些配置文件分别为 Hive、 HiveText、HiveRC、 HiveORC 和 HiveVectorizedORC。

HiveText 和 HiveRC 配置文件分别针对文本和RCFile格式进行了优化。 HiveORC 和 HiveVectorizedORC 配置文件针对ORC文件格式进行了优化。 Hive 配置文件为所有文件存储类型进行了优化。 当Hive表底层由不同文件格式的多个分区组成时，您可以使用 Hive 配置文件。

使用以下语法创建引用Hive表的Greenplum数据库外部表：

CREATE EXTERNAL TABLE <table_name>
    ( <column_name> <data_type> [, ...] | LIKE <other_table> )
LOCATION ('pxf://<hive-db-name>.<hive-table-name>
    ?PROFILE=Hive|HiveText|HiveRC|HiveORC|HiveVectorizedORC[&SERVER=<server_name>]'])
FORMAT 'CUSTOM|TEXT' (FORMATTER='pxfwritable_import' | delimiter='<delim>')

CREATE EXTERNAL TABLE 命令中Hive连接器使用的特定关键字和值见下表中描述。

关键字 值
\ Hive数据库的名称。 如果省略，默认为名为 default 的Hive数据库
\ Hive表的名称
PROFILE PROFILE 关键字的值必须指定为 Hive, HiveText, HiveRC, HiveORC, 或 HiveVectorizedORC 之一
SERVER=\ PXF用于访问数据的命名服务器配置。可选的; 如果未指定，PXF将使用default服务器。
FORMAT (Hive, HiveORC, 和 HiveVectorizedORC 配置文件) FORMAT 子句必须指定为 'CUSTOM'。 CUSTOM 格式需要内置的 pxfwritable_import formatter
FORMAT (HiveText 和 HiveRC 配置文件) FORMAT 子句必须指定为 TEXT。 在delimiter ='<delim>'格式选项中指定单个ascii字符字段定界符。

访问TextFile格式的Hive表

您可以使用Hive 和 HiveText 配置文件来访问以TextFile格式存储的Hive表数据。

示例: 使用Hive配置文件

使用 Hive 配置文件创建一个可读的Greenplum数据库外部表，该表引用此前您创建的Hive文本格式表 sales_info。

  1. 创建外部表:
postgres=# CREATE EXTERNAL TABLE salesinfo_hiveprofile(location text, month text, num_orders int, total_sales float8)
            LOCATION ('pxf://default.sales_info?PROFILE=Hive')
          FORMAT 'custom' (FORMATTER='pxfwritable_import');
  1. 查询表:
postgres=# SELECT * FROM salesinfo_hiveprofile;

   location    | month | num_orders | total_sales
---------------+-------+------------+-------------
 Prague        | Jan   |        101 |     4875.33
 Rome          | Mar   |         87 |     1557.39
 Bangalore     | May   |        317 |     8936.99
 ...

示例: 使用HiveText配置文件

使用 HiveText 配置文件创建一个可读的Greenplum数据库外部表，该表引用此前您创建的Hive文本格式表 sales_info。

  1. 创建外部表:
postgres=# CREATE EXTERNAL TABLE salesinfo_hivetextprofile(location text, month text, num_orders int, total_sales float8)
             LOCATION ('pxf://default.sales_info?PROFILE=HiveText')
           FORMAT 'TEXT' (delimiter=E',');

注意，FORMAT子句delimiter值指定为单个ASCII逗号字符','。E转义字符。

  1. 查询外部表:
postgres=# SELECT * FROM salesinfo_hivetextprofile WHERE location='Beijing';

 location | month | num_orders | total_sales
----------+-------+------------+-------------
 Beijing  | Jul   |        411 |    11600.67
 Beijing  | Dec   |        100 |     4248.41
(2 rows)

访问RCFile格式的Hive表

RCFile Hive表格式用于行列格式的数据。PXF HiveRC 配置文件提供对RCFile数据的访问。

示例: 使用HiveRC配置文件

使用 HiveRC 配置文件在Hive中查询RCFile格式的数据。

  1. 启动 hive 命令行并创建一个以RCFile格式存储的Hive表:
$ HADOOP_USER_NAME=hdfs hive

hive> CREATE TABLE sales_info_rcfile (location string, month string,
        number_of_orders int, total_sales double)
      ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
      STORED AS rcfile;
  1. 将 sales_info 表的数据写入到 sales_info_rcfile:
hive> INSERT INTO TABLE sales_info_rcfile SELECT * FROM sales_info;

样本数据集的副本现在以RCFile格式存储在Hive sales_info_rcfile 表中。

  1. 查询 sales_info_rcfile Hive 表以验证数据是否正确加载:
hive> SELECT * FROM sales_info_rcfile;
  1. 使用 PXF HiveRC 配置文件创建Greenplum数据库可读外部表，该表引用此前步骤您创建的Hive sales_info_rcfile 表。例如:
postgres=# CREATE EXTERNAL TABLE salesinfo_hivercprofile(location text, month text, num_orders int, total_sales float8)
             LOCATION ('pxf://default.sales_info_rcfile?PROFILE=HiveRC')
           FORMAT 'TEXT' (delimiter=E',');
  1. 查询外部表:
postgres=# SELECT location, total_sales FROM salesinfo_hivercprofile;

   location    | total_sales
---------------+-------------
 Prague        |     4875.33
 Rome          |     1557.39
 Bangalore     |     8936.99
 Beijing       |    11600.67
 ...

访问ORC格式的Hive表

优化行列(ORC) 文件格式是一种列文件格式，提供了一种高效的方式来存储和访问HDFS数据。 ORC 格式在压缩和性能方面都优于文本和RCFile格式。 PXF 支持ORC 1.2.1版本。

ORC 具有类型感知能力，且专门针对Hadoop工作负载而设计。 ORC文件存储文件中数据的类型和编码信息。一组行数据(也称为stripe)中的所有列一起以ORC格式文件存储于磁盘上。ORC 格式类型的列性质允许进行读取投影，从而有助于避免在查询期间访问不必要的列。

ORC 还支持在file, stripe, row 级别使用内置索引进行谓词下推，从而将过滤操作移至数据加载阶段。

有关ORC文件格式的详细信息，请参考Apache orc 和 Apache Hive LanguageManual ORC 网站。

支持ORC文件格式的配置文件

选择支持ORC的配置文件时，请考虑以下事情:

  • HiveORC 配置文件:
+ 一次读取一行
+ 支持列投影
+ 支持复杂类型。 您可以访问由数据(array), 映射(map), 结构(struct), 和联合数据类型组成的Hive表。 PXF 将这些复杂类型序列化为 `text`。
  • HiveVectorizedORC 配置文件:
+ 一次最多读取1024行
+ 不支持列投影
+ 不支持复杂类型或 timestamp 数据类型

示例: 使用HiveORC配置文件

在接下来的例子中，您将创建以ORC格式存储的Hive表，并使用 HiveORC 配置文件查询此Hive表。

  1. 用ORC文件格式创建Hive表:
$ HADOOP_USER_NAME=hdfs hive

hive> CREATE TABLE sales_info_ORC (location string, month string,
        number_of_orders int, total_sales double)
      STORED AS ORC;
  1. 将 sales_info 表的数据写入到 sales_info_ORC:
hive> INSERT INTO TABLE sales_info_ORC SELECT * FROM sales_info;

样本数据集的副本现在以ORC格式存储在 sales_info_ORC 中。

  1. 在 sales_info_ORC 上执行Hive查询以验证数据是否正确加载:
hive> SELECT * FROM sales_info_ORC;
  1. 启动 psql 子系统并打开计时:
$ psql -d postgres

postgres=> \timing
Timing is on.
  1. 使用PXF HiveORC 配置文件创建Greenplum数据库外部表，该表引用此前您在步骤 1 创建的名为 sales_info_ORC 的Hive表。 FORMAT 子句必须指定为 'CUSTOM'。 HiveORC CUSTOM 格式仅支持内置的 'pxfwritable_import' formatter。
postgres=> CREATE EXTERNAL TABLE salesinfo_hiveORCprofile(location text, month text, num_orders int, total_sales float8)
             LOCATION ('pxf://default.sales_info_ORC?PROFILE=HiveORC')
             FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
  1. 查询外部表:
postgres=> SELECT * FROM salesinfo_hiveORCprofile;

   location    | month | number_of_orders | total_sales 
---------------+-------+------------------+-------------
 Prague        | Jan   |              101 |     4875.33
 Rome          | Mar   |               87 |     1557.39
 Bangalore     | May   |              317 |     8936.99
 ...

Time: 425.416 ms

示例: 使用HiveVectorizedORC配置文件

在以下示例中，您将使用 HiveVectorizedORC 配置文件查询您在此前示例中创建的 sales_info_ORC Hive表。

  1. 启动 psql 子系统:
$ psql -d postgres
  1. 使用PXF HiveVectorizedORC 配置文件创建Greenplum数据库外部表， 该表引用您在此前示例步骤 1 中创建的名为 sales_info_ORC 的Hive表。 FORMAT 子句必须指定为 'CUSTOM'。 HiveVectorizedORC CUSTOM 格式仅支持内置的 'pxfwritable_import' formatter。
postgres=> CREATE EXTERNAL TABLE salesinfo_hiveVectORC(location text, month text, num_orders int, total_sales float8)
             LOCATION ('pxf://default.sales_info_ORC?PROFILE=HiveVectorizedORC')
             FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
  1. 查询外部表:
postgres=> SELECT * FROM salesinfo_hiveVectORC;

   location    | month | number_of_orders | total_sales 
---------------+-------+------------------+-------------
 Prague        | Jan   |              101 |     4875.33
 Rome          | Mar   |               87 |     1557.39
 Bangalore     | May   |              317 |     8936.99
 ...

Time: 425.416 ms

访问Parquet格式的Hive表

PXF Hive 配置文件支持使用Parquet存储格式的非分区和分区Hive表。 使用等效的Greenplum数据库数据类型映射Hive表列。 例如, 如果在 default 模式中使用以下命令创建一个Hive表:

hive> CREATE TABLE hive_parquet_table (location string, month string,
            number_of_orders int, total_sales double)
        STORED AS parquet;

定义Greenplum数据库外部表:

postgres=# CREATE EXTERNAL TABLE pxf_parquet_table (location text, month text, number_of_orders int, total_sales double precision)
    LOCATION ('pxf://default.hive_parquet_table?profile=Hive')
    FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

查询外部表:

postgres=# SELECT month, number_of_orders FROM pxf_parquet_table;

处理复杂数据类型

示例: 使用Hive配置文件处理复杂数据类型

本示例中使用 Hive 配置文件以及数组和映射复杂数据类型，特别是整数数组和字符串键/值对。

此示例中的数据模式包含具有以下名称和数据类型的字段:

字段名 数据类型
index int
name string
intarray 整数数组
propmap 字符串键/值对映射

当您在Hive表中指定数组字段，必须为集合中的每个项目标识终止符。 相似的，您还必须为映射键指定终止符。

  1. 创建一个文本文件，从中加载数据:
$ vi /tmp/pxf_hive_complex.txt
  1. 将以下文本添加到 pxf_hive_complex.txt。 此数据使用 逗号 , 分隔字段值，百分号 % 分隔集合项， 并使用 : 终止映射键值:
3,Prague,1%2%3,zone:euro%status:up
89,Rome,4%5%6,zone:euro
400,Bangalore,7%8%9,zone:apac%status:pending
183,Beijing,0%1%2,zone:apac
94,Sacramento,3%4%5,zone:noam%status:down
101,Paris,6%7%8,zone:euro%status:up
56,Frankfurt,9%0%1,zone:euro
202,Jakarta,2%3%4,zone:apac%status:up
313,Sydney,5%6%7,zone:apac%status:pending
76,Atlanta,8%9%0,zone:noam%status:down
  1. 创建一个Hive表来展示此数据:
$ HADOOP_USER_NAME=hdfs hive

hive> CREATE TABLE table_complextypes( index int, name string, intarray ARRAY<int>, propmap MAP<string, string>)
         ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
         COLLECTION ITEMS TERMINATED BY '%'
         MAP KEYS TERMINATED BY ':'
         STORED AS TEXTFILE;

注意:

* `FIELDS TERMINATED BY` 将逗号标识为字段分隔符
* `COLLECTION ITEMS TERMINATED BY` 子句将百分号作为集合项(数组项, 映射键/值对)的终止符
* `MAP KEYS TERMINATED BY` 将冒号标识为映射键的终止符
  1. 将 pxf_hive_complex.txt 示例数据文件加载到您刚刚创建的 table_complextypes 表中:
hive> LOAD DATA LOCAL INPATH '/tmp/pxf_hive_complex.txt' INTO TABLE table_complextypes;
  1. 在Hive表 table_complextypes 上执行查询以验证数据是否已成功加载:
hive> SELECT * FROM table_complextypes;

3   Prague  [1,2,3] { "zone":"euro","status":"up"}
89  Rome    [4,5,6] { "zone":"euro"}
400 Bangalore   [7,8,9] { "zone":"apac","status":"pending"}
...
  1. 使用 Hive 配置文件创建可读Greenplum数据库外部表，该表引用名为 table_complextypes 的Hive表:
postgres=# CREATE EXTERNAL TABLE complextypes_hiveprofile(index int, name text, intarray text, propmap text)
             LOCATION ('pxf://table_complextypes?PROFILE=Hive')
           FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

注意整数数组和映射复杂类型已映射为Greenplum数据库文本数据类型

  1. 查询外部表:
postgres=# SELECT * FROM complextypes_hiveprofile;

 index |    name    | intarray |              propmap
-------+------------+----------+------------------------------------
     3 | Prague     | [1,2,3]  | { "zone":"euro","status":"up"}
    89 | Rome       | [4,5,6]  | { "zone":"euro"}
   400 | Bangalore  | [7,8,9]  | { "zone":"apac","status":"pending"}
   183 | Beijing    | [0,1,2]  | { "zone":"apac"}
    94 | Sacramento | [3,4,5]  | { "zone":"noam","status":"down"}
   101 | Paris      | [6,7,8]  | { "zone":"euro","status":"up"}
    56 | Frankfurt  | [9,0,1]  | { "zone":"euro"}
   202 | Jakarta    | [2,3,4]  | { "zone":"apac","status":"up"}
   313 | Sydney     | [5,6,7]  | { "zone":"apac","status":"pending"}
    76 | Atlanta    | [8,9,0]  | { "zone":"noam","status":"down"}
(10 rows)

intarray 和 propmap 都被序列化为文本字符串

示例: 使用HiveORC配置文件处理复杂数据类型

在以下示例中，您将创建和填充以ORC格式存储的Hive表。 您将使用 HiveORC 配置文件查询此Hive表中的复杂数据类型。

  1. 用ORC存储格式创建Hive表:
$ HADOOP_USER_NAME=hdfs hive

hive> CREATE TABLE table_complextypes_ORC( index int, name string, intarray ARRAY<int>, propmap MAP<string, string>)
        ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
        COLLECTION ITEMS TERMINATED BY '%'
        MAP KEYS TERMINATED BY ':'
      STORED AS ORC;
  1. 将您在此前示例中创建的 table_complextypes 表的数据写入到 table_complextypes_ORC 表中:
hive> INSERT INTO TABLE table_complextypes_ORC SELECT * FROM table_complextypes;

A copy of the sample data set is now stored in ORC format in table_complextypes_ORC.

  1. 在 table_complextypes_ORC 上执行查询以验证数据是否成功加载:
hive> SELECT * FROM table_complextypes_ORC;

OK
3       Prague       [1,2,3]    { "zone":"euro","status":"up"}
89      Rome         [4,5,6]    { "zone":"euro"}
400     Bangalore    [7,8,9]    { "zone":"apac","status":"pending"}
...
  1. 启动 psql 子系统:
$ psql -d postgres
  1. 使用 PXF HiveORC 配置文件创建可读Greenplum数据库外部表，该表引用您在步骤 1 中创建的名为 table_complextypes_ORC 的Hive表。 FORMAT 子句必须指定为 'CUSTOM'。 HiveORC CUSTOM 格式仅支持内置的 'pxfwritable_import' formatter。
postgres=> CREATE EXTERNAL TABLE complextypes_hiveorc(index int, name text, intarray text, propmap text)
           LOCATION ('pxf://default.table_complextypes_ORC?PROFILE=HiveORC')
             FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

注意整数数组和映射复杂类型已映射为Greenplum数据库文本数据类型

  1. 查询外部表:
postgres=> SELECT * FROM complextypes_hiveorc;

 index |    name    | intarray |              propmap               
-------+------------+----------+------------------------------------
     3 | Prague     | [1,2,3]  | { "zone":"euro","status":"up"}
    89 | Rome       | [4,5,6]  | { "zone":"euro"}
   400 | Bangalore  | [7,8,9]  | { "zone":"apac","status":"pending"}
 ...

intarray 和 propmap 都被序列化为文本字符串

分区过滤下推

PXF Hive连接器支持Hive分区修剪和Hive分区目录结构。这样可以在包含Hive表的选定HDFS文件上排除分区。要使用分区筛选功能来减少网络流量和I/O，请使用WHERE子句在PXF外部表上运行查询，该子句引用已分区的Hive表中的特定分区列。

下面介绍了对Hive字符串和整数类型的PXF Hive连接器分区过滤支持：

  • 字符串类型支持关系运算符=，<<，\<=，>，>=和<>。
  • 整数类型支持关系运算符=和<>(要对Hive整数类型使用分区过滤，必须按照前提条件中所述更新Hive配置)。
  • 与上述关系运算符一起使用时，支持逻辑运算符AND和OR。
  • 不支持LIKE字符串运算符。

要利用PXF分区过滤下推功能，Hive和PXF分区字段名称必须相同。否则，PXF将忽略分区过滤，并且过滤将在Greenplum数据库端执行，从而影响性能。

PXF Hive连接器仅在分区列上过滤，而不在其他表属性上过滤。此外，仅以上确定的那些数据类型和运算符支持过滤器下推。 默认情况下，PXF过滤器下推处于启用状态。您可以按照如下所述配置PXF过滤器下推关于过滤器下推。

示例: 使用Hive配置文件访问同构分区的数据

在此示例中，您将使用 Hive 配置文件来查询名为 sales_part 的Hive表，该表使用 delivery_state 和 delivery_city 字段分区。 然后，您创建一个Greenplum数据库外部表以查询 sales_part。 该过程包括一些演示过滤器下推的特定示例。

  1. 创建一个名为 sales_part 的Hive表，包含两个分区字段， delivery_state 和 delivery_city ：
hive> CREATE TABLE sales_part (name string, type string, supplier_key int, price double)
        PARTITIONED BY (delivery_state string, delivery_city string)
        ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
  1. 加载数据至Hive表并添加一些分区：
hive> INSERT INTO TABLE sales_part
        PARTITION(delivery_state = 'CALIFORNIA', delivery_city = 'Fresno')
        VALUES ('block', 'widget', 33, 15.17);
hive> INSERT INTO TABLE sales_part
        PARTITION(delivery_state = 'CALIFORNIA', delivery_city = 'Sacramento')
        VALUES ('cube', 'widget', 11, 1.17);
hive> INSERT INTO TABLE sales_part
        PARTITION(delivery_state = 'NEVADA', delivery_city = 'Reno')
        VALUES ('dowel', 'widget', 51, 31.82);
hive> INSERT INTO TABLE sales_part
        PARTITION(delivery_state = 'NEVADA', delivery_city = 'Las Vegas')
        VALUES ('px49', 'pipe', 52, 99.82);
  1. 查询 sales_part 表：
hive> SELECT * FROM sales_part;

Hive分区表上的 SELECT * 语句显示记录末尾的分区字段。

  1. 检查 sales_part 表Hive/HDFS的目录结构：
$ sudo -u hdfs hdfs dfs -ls -R /apps/hive/warehouse/sales_part
/apps/hive/warehouse/sales_part/delivery_state=CALIFORNIA/delivery_city=Fresno/
/apps/hive/warehouse/sales_part/delivery_state=CALIFORNIA/delivery_city=Sacramento/
/apps/hive/warehouse/sales_part/delivery_state=NEVADA/delivery_city=Reno/
/apps/hive/warehouse/sales_part/delivery_state=NEVADA/delivery_city=Las Vegas/
  1. 创建一个PXF外部表来读取Hive分区表 sales_part。 要利用分区过滤器的下推功能， 请在 CREATE EXTERNAL TABLE 属性列表的末尾定义与Hive分区字段相同的字段。
$ psql -d postgres

postgres=# CREATE EXTERNAL TABLE pxf_sales_part(
             item_name TEXT, item_type TEXT,
             supplier_key INTEGER, item_price DOUBLE PRECISION,
             delivery_state TEXT, delivery_city TEXT)
           LOCATION ('pxf://sales_part?Profile=Hive')
           FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
  1. 查询外部表:
postgres=# SELECT * FROM pxf_sales_part;
  1. 在 pxf_sales_part 上执行另一个查询(不下推) ，以此返回 delivery_city 等于 Sacramento 和 item_name 等于 cube 的记录：
postgres=# SELECT * FROM pxf_sales_part WHERE delivery_city = 'Sacramento' AND item_name = 'cube';

这个查询过滤了 delivery_city 分区 Sacramento。 由于 item_name 字段不是分区列， 因此该列上的过滤不会被下推。 在 Sacramento 分区传输完成后， 在Greenplum数据库端执行此过滤操作。

  1. 查询(使用过滤下推)所有 delivery_state 等于 CALIFORNIA 的记录:
postgres=# SET gp_external_enable_filter_pushdown=on;
postgres=# SELECT * FROM pxf_sales_part WHERE delivery_state = 'CALIFORNIA';

该查询读取位于 CALIFORNIA delivery_state 分区中的所有数据，无论城市是哪个。

示例: 使用Hive配置文件访问异构分区的数据

您可以使用PXF Hive 配置文件访问任何Hive文件存储类型。 使用 Hive 配置文件，您可以在单个Hive表中访问分区以不同文件格式存储的异构格式数据。

本示例中，您将创建一个分区的Hive外部表。该表由之前示例创建的 sales_info (文本格式) 和 sales_info_rcfile (RC 格式) Hive表相关的HDFS数据文件组成。 您将按照年份对数据进行分区，将 sales_info 的数据分配给2013年， 而 sales_info_rcfile 的数据分配给2016年(这里忽略表包含相同数据的事实)。 然后，您将使用PXF Hive 配置文件查询这个分区的Hive外部表。

  1. 创建一个名为 hive_multiformpart 的Hive外部表，该表以名为 year 的字符串字段进行分区：
$ HADOOP_USER_NAME=hdfs hive

hive> CREATE EXTERNAL TABLE hive_multiformpart( location string, month string, number_of_orders int, total_sales double)
        PARTITIONED BY( year string )
        ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
  1. 描述 sales_info 和 sales_info_rcfile 表，注意每个表HDFS文件的 location 信息：
hive> DESCRIBE EXTENDED sales_info;
hive> DESCRIBE EXTENDED sales_info_rcfile;
  1. 在 hive_multiformpart 表中为 sales_info 以及 sales_info_rcfile 表关联的HDFS文件位置创建分区：
hive> ALTER TABLE hive_multiformpart ADD PARTITION (year = '2013') LOCATION 'hdfs://namenode:8020/apps/hive/warehouse/sales_info';
hive> ALTER TABLE hive_multiformpart ADD PARTITION (year = '2016') LOCATION 'hdfs://namenode:8020/apps/hive/warehouse/sales_info_rcfile';
  1. 明确的标识与 sales_info_rcfile 表关联的分区的文件格式：
hive> ALTER TABLE hive_multiformpart PARTITION (year='2016') SET FILEFORMAT RCFILE;

无需指定与 sales_info 表相关联分区的文件格式，因为 TEXTFILE 格式是默认格式。

  1. 查询 hive_multiformpart 表：
hive> SELECT * from hive_multiformpart;
...
Bangalore   Jul 271 8320.55 2016
Beijing Dec 100 4248.41 2016
Prague  Jan 101 4875.33 2013
Rome    Mar 87  1557.39 2013
...
hive> SELECT * from hive_multiformpart WHERE year='2013';
hive> SELECT * from hive_multiformpart WHERE year='2016';
  1. 查询 hive_multiformpart 表的分区定义并退出 hive:
hive> SHOW PARTITIONS hive_multiformpart;
year=2013
year=2016
hive> quit;
  1. 启动 psql 子系统:
$ psql -d postgres
  1. 使用PXF Hive 配置文件创建一个可读的Greenplum数据库外部表，该表引用此前步骤您在Hive中创建的 hive_multiformpart 外部表。
postgres=# CREATE EXTERNAL TABLE pxf_multiformpart(location text, month text, num_orders int, total_sales float8, year text)
             LOCATION ('pxf://default.hive_multiformpart?PROFILE=Hive')
           FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
  1. 查询PXF外部表：
postgres=# SELECT * FROM pxf_multiformpart;

   location    | month | num_orders | total_sales | year 
---------------+-------+------------+-------------+--------
 ....
 Prague        | Dec   |        333 |     9894.77 | 2013
 Bangalore     | Jul   |        271 |     8320.55 | 2013
 Beijing       | Dec   |        100 |     4248.41 | 2013
 Prague        | Jan   |        101 |     4875.33 | 2016
 Rome          | Mar   |         87 |     1557.39 | 2016
 Bangalore     | May   |        317 |     8936.99 | 2016
 ....
  1. 执行第二个查询以计算2013年的订单总数：
postgres=# SELECT sum(num_orders) FROM pxf_multiformpart WHERE month='Dec' AND year='2013';
 sum 
-----
 433

使用PXF访问Hive默认分区

本主题描述了当Hive使用默认分区时，Hive和PXF在查询结果上的区别。 当Hive启用动态分区后，分区表可能会将数据存储在默认分区中。 当分区列的值与列的定义类型不匹配是时(例如，当任何分区列存在NULL值时)，Hive会创建一个默认分区。 在Hive中，任何在分区列上的过滤查询都会 排除 默认分区中存储的数据。

与Hive类似， PXF将表的分区列表示为附加在表末尾的列。但是，PXF会将默认分区中的任何列值都转换为NULL值。这意味着在同一个Hive查询中，在分区列上包含 IS NULL 过滤器的Greenplum数据库查询可以返回不同结果。

考虑使用以下语句创建的Hive分区表:

hive> CREATE TABLE sales (order_id bigint, order_amount float) PARTITIONED BY (xdate date);

该表加载了包含以下内容的五行数据：

1.0    1900-01-01
2.2    1994-04-14
3.3    2011-03-31
4.5    NULL
5.0    2013-12-06

插入第4行会创建一个Hive默认分区，因为分区列 xdate 包含NULL值。

在Hive中，对分区列进行筛选的任何查询都将忽略默认分区中的数据。例如，以下查询不返回任何行：

hive> SELECT * FROM sales WHERE xdate IS null;

但是， 如果将此Hive表映射到Greenplum数据库中的PXF外部表，则所有默认分区列的值都将被转换为实际的NULL值。在Greenplum数据库中，对PXF外部表执行相同的查询将返回第4行作为结果，因为过滤器匹配NULL值。

在Hive分区表上执行 IS NULL 查询时，请牢记此行为。

当前内容版权归GreenPlum和翻译组所有，若有侵权请联系删除下架 修订时间： 2024-09-15 21:48:08

results matching ""

    No results matching ""