HAWQ技术解析(九) —— 外部数据
来源:互联网 发布:淘宝如何关联阿里宝贝 编辑:程序博客网 时间:2024/04/30 06:07
一、安装配置PXF
如果使用Ambari安装管理HAWQ集群,那么不需要执行任何手工命令行安装步骤,从Ambari web接口就可以安装所有需要的PXF插件。详细安装步骤参考用HAWQ轻松取代传统数据仓库(二) —— 安装部署。如果使用命令行安装PXF,参见Installing PXF from the Command Line。PXF相关的缺省安装目录和文件如表1所示。
目录
描述
/usr/lib/pxf
PXF库目录。
/etc/pxf/conf
PXF配置目录。该目录下包含pxf-public.classpath、pxf-private.classpath及其它配置文件。
/var/pxf/pxf-service
PXF服务实例所在目录。
/var/log/pxf
该目录包含pxf-service.log和所有Tomcat相关的日志文件。(PXF需要在主机上运行Tomcat,用Ambari安装PXF时会自动安装Tomcat),这些文件的属主是pxf:pxf,对其他用户是只读的。
/var/run/pxf/catalina.pid
PXF Tomcat容器的PID文件,存储进程号。
表1
与安装一样,PXF也可以使用Ambari的图形界面进行交互式配置,完成后重启PXF服务以使配置生效。手工配置步骤参考Configuring PXF。注意,手工配置需要修改所有集群主机上的相关配置文件,然后重启所有节点上的PXF服务。二、PXF profile
PXF profile是一组通用元数据属性的集合,用于简化外部数据读写。PXF自带多个内建的profile,每个profile将一组元数据属性归于一类,使得对以下数据存储系统的访问更加容易:
- HDFS文件数据(读写)
- Hive(只读)
- HBase(只读)
- JSON(只读)
Profile
描述
相关Java类
HdfsTextSimple
读写HDFS上的平面文本文件,每条记录由固定分隔符的一行构成。
org.apache.hawq.pxf.plugins.hdfs.HdfsDataFragmenter
org.apache.hawq.pxf.plugins.hdfs.LineBreakAccessor
org.apache.hawq.pxf.plugins.hdfs.StringPassResolverHdfsTextMulti
从HDFS上的平面文件中读取具有固定分隔符的记录,每条记录由一行或多行(记录中包含换行符)构成。此profile是不可拆分的(非并行),比HdfsTextSimple读取慢。
org.apache.hawq.pxf.plugins.hdfs.HdfsDataFragmenter
org.apache.hawq.pxf.plugins.hdfs.QuotedLineBreakAccessor
org.apache.hawq.pxf.plugins.hdfs.StringPassResolverHive
读Hive表,支持text、RC、ORC、Sequence或Parquet存储格式。
org.apache.hawq.pxf.plugins.hive.HiveDataFragmenter
org.apache.hawq.pxf.plugins.hive.HiveAccessor
org.apache.hawq.pxf.plugins.hive.HiveResolver
org.apache.hawq.pxf.plugins.hive.HiveMetadataFetcher
org.apache.hawq.pxf.service.io.GPDBWritableHiveRC
优化读取RCFile存储格式的Hive表,必须指定DELIMITER参数。
org.apache.hawq.pxf.plugins.hive.HiveInputFormatFragmenter
org.apache.hawq.pxf.plugins.hive.HiveRCFileAccessor
org.apache.hawq.pxf.plugins.hive.HiveColumnarSerdeResolver
org.apache.hawq.pxf.plugins.hive.HiveMetadataFetcher
org.apache.hawq.pxf.service.io.TextHiveORC
优化读取ORCFile存储格式的Hive表。
org.apache.hawq.pxf.plugins.hive.HiveInputFormatFragmenter
org.apache.hawq.pxf.plugins.hive.HiveORCAccessor
org.apache.hawq.pxf.plugins.hive.HiveORCSerdeResolver
org.apache.hawq.pxf.plugins.hive.HiveMetadataFetcher
org.apache.hawq.pxf.service.io.GPDBWritableHiveText
优化读取TextFile存储格式的Hive表,必须指定DELIMITER参数。
org.apache.hawq.pxf.plugins.hive.HiveInputFormatFragmenter
org.apache.hawq.pxf.plugins.hive.HiveLineBreakAccessor
org.apache.hawq.pxf.plugins.hive.HiveStringPassResolver
org.apache.hawq.pxf.plugins.hive.HiveMetadataFetcher
org.apache.hawq.pxf.service.io.TextHBase
读取HBase数据存储引擎。
org.apache.hawq.pxf.plugins.hbase.HBaseDataFragmenter
org.apache.hawq.pxf.plugins.hbase.HBaseAccessor
org.apache.hawq.pxf.plugins.hbase.HBaseResolverAvro
读取Avro文件。
org.apache.hawq.pxf.plugins.hdfs.HdfsDataFragmenter
org.apache.hawq.pxf.plugins.hdfs.AvroFileAccessor
org.apache.hawq.pxf.plugins.hdfs.AvroResolverJSON
读取HDFS上的JSON文件。
org.apache.hawq.pxf.plugins.hdfs.HdfsDataFragmenter
org.apache.hawq.pxf.plugins.json.JsonAccessor
org.apache.hawq.pxf.plugins.json.JsonResolver 表2二、访问HDFS文件
HDFS是Hadoop应用的主要分布式存储机制。PXF的HDFS插件用于读取存储在HDFS文件中的数据,支持具有固定分隔符的文本和Avro两种文件格式。在使用PXF访问HDFS文件前,确认已经在集群所有节点上安装了PXF HDFS插件(Ambari会自动安装),并授予了HAWQ用户(典型的是gpadmin)对HDFS文件相应的读写权限。
1. PXF支持的HDFS文件格式
PXF HDFS插件支持对以下两种文件格式的读取:
- comma-separated value(.csv)或其它固定分隔符的平面文本文件。
- 由JSON定义的、基于Schema的Avro文件格式。
- HdfsTextSimple - 单行文本文件
- HdfsTextMulti - 内嵌换行符的多行文本文件
- Avro - Avro文件
2. 查询外部HDFS数据
HAWQ通过外部表的形式访问HDFS文件。下面是创建一个HDFS外部表的语法。
CREATE EXTERNAL TABLE <table_name> ( <column_name> <data_type> [, ...] | LIKE <other_table> )LOCATION ('pxf://<host>[:<port>]/<path-to-hdfs-file> ?PROFILE=HdfsTextSimple|HdfsTextMulti|Avro[&<custom-option>=<value>[...]]')FORMAT '[TEXT|CSV|CUSTOM]' (<formatting-properties>);CREATE EXTERNAL TABLE语句中使用的各个关键字和相应值的描述如表3所示。
关键字
值
<host>[:<port>]
HDFS NameNode主机名、端口。
<path-to-hdfs-file>
HDFS文件路径。
PROFILE
PROFILE关键字指定为HdfsTextSimple、HdfsTextMulti或Avro之一。
<custom-option>
与特定PROFILE对应的定制选项。
FORMAT 'TEXT'
当<path-to-hdfs-file>指向一个单行固定分隔符的平面文件时,使用该关键字。
FORMAT 'CSV'
当<path-to-hdfs-file>指向一个单行或多行的逗号分隔值(CSV)平面文件时,使用该关键字。
FORMAT 'CUSTOM'
Avro文件使用该关键字。Avro 'CUSTOM'格式只支持内建的(formatter='pxfwritable_import')格式属性。
<formatting-properties>
与特定PROFILE对应的格式属性。
表3下面是几个HAWQ访问HDFS文件的例子。
(1)使用HdfsTextSimple Profile。
HdfsTextSimple Profile用于读取一行表示一条记录的平面文本文件或CSV文件,支持的<formatting-properties>是delimiter,用来指定文件中每条记录的字段分隔符。
为PXF创建一个HDFS目录。
su - hdfshdfs dfs -mkdir -p /data/pxf_exampleshdfs dfs -chown -R gpadmin:gpadmin /data/pxf_examples建立一个名为pxf_hdfs_simple.txt的平面文本文件,生成四条记录,使用逗号作为字段分隔符。
echo 'Prague,Jan,101,4875.33Rome,Mar,87,1557.39Bangalore,May,317,8936.99Beijing,Jul,411,11600.67' > /tmp/pxf_hdfs_simple.txt将文件传到HDFS上。
hdfs dfs -put /tmp/pxf_hdfs_simple.txt /data/pxf_examples/显示HDFS上的pxf_hdfs_simple.txt文件内容。
hdfs dfs -cat /data/pxf_examples/pxf_hdfs_simple.txt使用HdfsTextSimple profile创建一个可从pxf_hdfs_simple.txt文件查询数据的HAWQ外部表。delimiter=e','中的e表示转义,就是说如果记录正文中含有逗号,需要用\符号进行转义。
su - gpadmin psql -d db1db1=# create external table pxf_hdfs_textsimple(location text, month text, num_orders int, total_sales float8)db1-# location ('pxf://hdp1:51200/data/pxf_examples/pxf_hdfs_simple.txt?profile=hdfstextsimple')db1-# format 'text' (delimiter=e',');CREATE EXTERNAL TABLEdb1=# select * from pxf_hdfs_textsimple; location | month | num_orders | total_sales -----------+-------+------------+------------- Prague | Jan | 101 | 4875.33 Rome | Mar | 87 | 1557.39 Bangalore | May | 317 | 8936.99 Beijing | Jul | 411 | 11600.67(4 rows)用CSV格式创建第二个外部表。当指定格式为‘CSV’时,逗号是缺省分隔符,不再需要使用delimiter说明。
db1=# create external table pxf_hdfs_textsimple_csv(location text, month text, num_orders int, total_sales float8)db1-# location ('pxf://hdp1:51200/data/pxf_examples/pxf_hdfs_simple.txt?profile=hdfstextsimple')db1-# format 'csv';CREATE EXTERNAL TABLEdb1=# select * from pxf_hdfs_textsimple_csv; location | month | num_orders | total_sales -----------+-------+------------+------------- Prague | Jan | 101 | 4875.33 Rome | Mar | 87 | 1557.39 Bangalore | May | 317 | 8936.99 Beijing | Jul | 411 | 11600.67(4 rows)
(2)使用HdfsTextMulti Profile
HdfsTextMulti profile用于读取一条记录中含有换行符的平面文本文件。因为PXF将换行符作为行分隔符,所以当数据中含有换行符时需要用HdfsTextMulti进行特殊处理。HdfsTextMulti Profile支持的<formatting-properties>是delimiter,用来指定文件中每条记录的字段分隔符。
创建一个平面文本文件。
vi /tmp/pxf_hdfs_multi.txt输入以下记录,以冒号作为字段分隔符,第一个字段中含有换行符。
"4627 Star Rd.San Francisco, CA 94107":Sept:2017"113 Moon St.San Diego, CA 92093":Jan:2018"51 Belt Ct.Denver, CO 90123":Dec:2016"93114 Radial Rd.Chicago, IL 60605":Jul:2017"7301 Brookview Ave.Columbus, OH 43213":Dec:2018将文件传到HDFS上。
su - hdfshdfs dfs -put /tmp/pxf_hdfs_multi.txt /data/pxf_examples/使用HdfsTextMulti profile创建一个可从pxf_hdfs_multi.txt文件查询数据的外部表,指定分隔符是冒号。
db1=# create external table pxf_hdfs_textmulti(address text, month text, year int)db1-# location ('pxf://hdp1:51200/data/pxf_examples/pxf_hdfs_multi.txt?profile=hdfstextmulti')db1-# format 'csv' (delimiter=e':');CREATE EXTERNAL TABLEdb1=# select * from pxf_hdfs_textmulti; address | month | year --------------------------+-------+------ 4627 Star Rd. | Sept | 2017 San Francisco, CA 94107 113 Moon St. | Jan | 2018 San Diego, CA 92093 51 Belt Ct. | Dec | 2016 Denver, CO 90123 93114 Radial Rd. | Jul | 2017 Chicago, IL 60605 7301 Brookview Ave. | Dec | 2018 Columbus, OH 43213 (5 rows)
(3)Avro Profile
参见Avro Profile。
(4)访问HDFS HA集群中的文件
为了访问HDFS HA集群中的外部数据,将CREATE EXTERNAL TABLE LOCATION子句由<host>[:<port>]修改为<HA-nameservice>。
gpadmin=# create external table pxf_hdfs_textmulti_ha (address text, month text, year int) location ('pxf://mycluster/data/pxf_examples/pxf_hdfs_multi.txt?profile=hdfstextmulti') format 'csv' (delimiter=e':');gpadmin=# select * from pxf_hdfs_textmulti_ha;查询结果如图1所示。
Hive是Hadoop的分布式数据仓库框架,支持多种文件格式,如CVS、RC、ORC、parquet等。PXF的Hive插件用于读取存储在Hive表中的数据。PXF提供两种方式查询Hive表:
- 通过整合PXF与HCatalog直接查询。
- 通过外部表查询。
- 在HAWQ和HDFS集群的所有节点上(master、segment、NameNode、DataNode)安装了PXF HDFS插件。
- 在HAWQ和HDFS集群的所有节点上安装了PXF Hive插件。
- 如果配置了Hadoop HA,PXF也必须安装在所有运行NameNode服务的HDFS节点上。
- 所有PXF节点上都安装了Hive客户端。
- 集群所有节点上都安装了Hive JAR文件目录和conf目录。
- 已经测试了PXF访问HDFS。
- 在集群中的一台主机上运行Hive Metastore服务。
- 在NameNode上的hive-site.xml文件中设置了hive.metastore.uris属性。
2. PXF支持的Hive文件格式
PXF Hive插件支持的Hive文件格式及其访问这些格式对应的profile如表4所示。
文件格式
描述
Profile
TextFile
逗号、tab或空格分隔的平面文件格式或JSON格式。
Hive、HiveText
SequenceFile
二进制键值对组成的平面文件。
Hive
RCFile
记录由键值对组成的列数据,具有行高压缩率。
Hive、HiveRC
ORCFile
优化的列式存储,减小数据大小。
Hive
Parquet
压缩的列式存储。
Hive
Avro
基于schema的、由JSON所定义的序列化格式。
Hive
表43. 数据类型映射
为了在HAWQ中表示Hive数据,需要将使用Hive私有数据类型的数据值映射为等价的HAWQ类型值。表5是对Hive私有数据类型的映射规则汇总。
Hive数据类型
HAWQ数据类型
boolean
bool
int
int4
smallint
int2
tinyint
int2
bigint
int8
float
float4
double
float8
string
text
binary
bytea
timestamp
timestamp
表5除简单类型外,Hive还支持array、struct、map等复杂数据类型。由于HAWQ原生不支持这些类型,PXF将它们统一映射为text类型。可以创建HAWQ函数或使用应用程序抽取复杂数据类型子元素的数据。
下面是一些HAWQ访问Hive表的例子。
4. 准备示例数据
(1)准备数据文件,添加如下记录,用逗号分隔字段。
vi /tmp/pxf_hive_datafile.txtPrague,Jan,101,4875.33Rome,Mar,87,1557.39Bangalore,May,317,8936.99Beijing,Jul,411,11600.67San Francisco,Sept,156,6846.34Paris,Nov,159,7134.56San Francisco,Jan,113,5397.89Prague,Dec,333,9894.77Bangalore,Jul,271,8320.55Beijing,Dec,100,4248.41(2)创建文本格式的Hive表sales_info
create database test;use test;create table sales_info (location string, month string, number_of_orders int, total_sales double) row format delimited fields terminated by ',' stored as textfile;(3)向sales_info表装载数据
load data local inpath '/tmp/pxf_hive_datafile.txt' into table sales_info;(4)查询sales_info表数据,验证装载数据成功。
select * from sales_info;(5)确认sales_info表在HDFS上的位置,在创建HAWQ外部表时需要用到该信息。
describe extended sales_info;...location:hdfs://mycluster/apps/hive/warehouse/test.db/sales_info...
5. 使用PXF和HCatalog查询Hive
HAWQ可以获取存储在HCatalog中的元数据,通过HCatalog直接访问Hive表,而不用关心Hive表对应的底层文件存储格式。HCatalog建立在Hive metastore之上,包含Hive的DDL语句。使用这种方式的好处是:
- 不须要知道Hive表结构。
- 不须要手工输入Hive表的位置与格式信息。
- 如果表的元数据改变,HCatalog自动提供更新后的元数据。这是使用PXF静态外部表方式无法做到的。
- HAWQ使用PXF从HCatalog查询表的元数据。
- HAWQ用查询到的元数据创建一个内存目录表。如果一个查询中多次引用了同一个表,内存目录表可以减少对外部HCatalog的调用次数。
- PXF使用内存目录表的元数据信息查询Hive表。查询结束后,内存目录表将被删除。
db1=# select * from hcatalog.test.sales_info; location | month | number_of_orders | total_sales ---------------+-------+------------------+------------- 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(10 rows)获取Hive表的字段和数据类型映射。
db1=# \d+ hcatalog.test.sales_info; PXF Hive Table "test.sales_info" Column | Type | Source type ------------------+--------+------------- location | text | string month | text | string number_of_orders | int4 | int total_sales | float8 | double可以使用通配符获取所有Hive库表的信息。
\d+ hcatalog.test.*;\d+ hcatalog.*.*;还可以使用pxf_get_item_fields函数获得Hive表的描述信息,该函数目前仅支持Hive profile。
db1=# select * from pxf_get_item_fields('hive','test.sales_info'); path | itemname | fieldname | fieldtype | sourcefieldtype ------+------------+------------------+-----------+----------------- test | sales_info | location | text | string test | sales_info | month | text | string test | sales_info | number_of_orders | int4 | int test | sales_info | total_sales | float8 | double(4 rows)pxf_get_item_fields函数同样也支持通配符。
select * from pxf_get_item_fields('hive','test.*');select * from pxf_get_item_fields('hive','*.*');
6. 查询Hive外部表
使用外部表方式需要标识适当的profile。PXF Hive插件支持三种Hive相关的profile,Hive、HiveText和HiveRC。HiveText和HiveRC分别针对TEXT和RC文件格式做了特别优化,而Hive profile可用于所有PXF支持的Hive文件存储类型。当底层Hive表由多个分区组成,并且分区使用了不同的文件格式,需要使用Hive profile。
以下语法创建一个HAWQ的Hive外部表:
CREATE EXTERNAL TABLE <table_name> ( <column_name> <data_type> [, ...] | LIKE <other_table> )LOCATION ('pxf://<host>[:<port>]/<hive-db-name>.<hive-table-name> ?PROFILE=Hive|HiveText|HiveRC[&DELIMITER=<delim>'])FORMAT 'CUSTOM|TEXT' (formatter='pxfwritable_import' | delimiter='<delim>')CREATE EXTERNAL TABLE语句中Hive插件使用关键字和相应值的描述如表6所示。
关键字
值
<host>[:]
HDFS NameNode主机名、端口号
<hive-db-name>
Hive数据库名,如果忽略,缺省是defaults。
<hive-table-name>
Hive表名。
PROFILE
必须是Hive、HiveText或HiveRC之一。
DELIMITER
指定字段分隔符,必须是单个ascii字符或相应字符的十六进制表示。
FORMAT (Hive profile)
必须指定为CUSTOM,仅支持内建的pxfwritable_import格式属性。
FORMAT (HiveText and HiveRC profiles)
必须指定为TEXT,并再次指定字段分隔符。
表6(1)Hive Profile
Hive profile适用于任何PXF支持的Hive文件存储格式,它实际上是为底层文件存储类型选择最优的Hive* profile。
db1=# create external table salesinfo_hiveprofile(location text, month text, num_orders int, total_sales float8)db1-# location ('pxf://hdp1:51200/test.sales_info?profile=hive')db1-# format 'custom' (formatter='pxfwritable_import');CREATE EXTERNAL TABLEdb1=# db1=# 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 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(10 rows)注意外部表和Hcatalog查询计划的区别,如图3所示。
(2)HiveText Profile
使用HiveText profile时,必须在LOCATION和FORMAT两个子句中都指定分隔符选项。
db1=# create external table salesinfo_hivetextprofile(location text, month text, num_orders int, total_sales float8)db1-# location ('pxf://hdp1:51200/test.sales_info?profile=hivetext&delimiter=,')db1-# format 'text' (delimiter=e',');CREATE EXTERNAL TABLEdb1=# 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)(3)HiveRC Profile
建立一个rcfile格式的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; insert into table sales_info_rcfile select * from sales_info;查询Hive表。
db1=# create external table salesinfo_hivercprofile(location text, month text, num_orders int, total_sales float8)db1-# location ('pxf://hdp1:51200/test.sales_info_rcfile?profile=hiverc&delimiter=,')db1-# format 'text' (delimiter=e',');CREATE EXTERNAL TABLEdb1=# db1=# select location, total_sales from salesinfo_hivercprofile; location | total_sales ---------------+------------- Prague | 4875.33 Rome | 1557.39 Bangalore | 8936.99 Beijing | 11600.67 San Francisco | 6846.34 Paris | 7134.56 San Francisco | 5397.89 Prague | 9894.77 Bangalore | 8320.55 Beijing | 4248.41(10 rows)(4)访问Parquet格式的Hive表
PXF Hive profile支持分区或非分区的Parquet存储格式。建立一个Parquet格式的Hive表,并插入数据。
create table sales_info_parquet (location string, month string, number_of_orders int, total_sales double) stored as parquet;insert into sales_info_parquet select * from sales_info;查询Hive表。
db1=# create external table salesinfo_parquet (location text, month text, num_orders int, total_sales float8)db1-# location ('pxf://hdp1:51200/test.sales_info_parquet?profile=hive')db1-# format 'custom' (formatter='pxfwritable_import');CREATE EXTERNAL TABLEdb1=# db1=# select * from salesinfo_parquet; location | month | num_orders | total_sales ---------------+-------+------------+------------- 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(10 rows)
7. 复杂数据类型
(1)准备数据文件,添加如下记录,用逗号分隔字段,第三个字段是array类型,第四个字段是map类型。
vi /tmp/pxf_hive_complex.txt3,Prague,1%2%3,zone:euro%status:up89,Rome,4%5%6,zone:euro400,Bangalore,7%8%9,zone:apac%status:pending183,Beijing,0%1%2,zone:apac94,Sacramento,3%4%5,zone:noam%status:down101,Paris,6%7%8,zone:euro%status:up56,Frankfurt,9%0%1,zone:euro202,Jakarta,2%3%4,zone:apac%status:up313,Sydney,5%6%7,zone:apac%status:pending76,Atlanta,8%9%0,zone:noam%status:down(2)建立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;(3)向Hive表装载数据
load data local inpath '/tmp/pxf_hive_complex.txt' into table table_complextypes;(4)查询Hive表,验证数据正确导入
select * from table_complextypes;(5)建立Hive外部表并查询数据
db1=# create external table complextypes_hiveprofile(index int, name text, intarray text, propmap text)db1-# location ('pxf://hdp1:51200/test.table_complextypes?profile=hive')db1-# format 'custom' (formatter='pxfwritable_import');CREATE EXTERNAL TABLEdb1=# 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)可以看到,复杂数据类型都被简单地转化为HAWQ的TEXT类型。
8. 访问Hive分区表
PXF Hive插件支持Hive的分区特性与目录结构,并且提供了所谓的分区过滤下推功能,可以利用Hive的分区消除特性,以降低网络流量和I/O负载。PXF的分区过滤下推与MySQL的索引条件下推(Index Condition Pushdown,ICP)概念上类似,都是将过滤条件下推至更底层的存储上,以提高性能。
为了利用PXF的分区过滤下推功能,查询的where子句中应该只使用分区字段。否则,PXF忽略分区过滤,过滤将在HAWQ端执行,影响查询性能。PXF的Hive插件只对分区键执行过滤下推。
分区过滤下推缺省是启用的:
db1=# show pxf_enable_filter_pushdown; pxf_enable_filter_pushdown ---------------------------- on(1 row)(1)使用Hive Profile访问同构分区数据
创建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 ',';insert into table sales_part partition(delivery_state = 'CALIFORNIA', delivery_city = 'Fresno') values ('block', 'widget', 33, 15.17);insert into table sales_part partition(delivery_state = 'CALIFORNIA', delivery_city = 'Sacramento') values ('cube', 'widget', 11, 1.17);insert into table sales_part partition(delivery_state = 'NEVADA', delivery_city = 'Reno') values ('dowel', 'widget', 51, 31.82);insert into table sales_part partition(delivery_state = 'NEVADA', delivery_city = 'Las Vegas') values ('px49', 'pipe', 52, 99.82);查询sales_part表。
select * from sales_part;检查sales_part表在HDFS上的目录结构。
sudo -u hdfs hdfs dfs -ls -R /apps/hive/warehouse/test.db/sales_part建立PXF外部表并查询数据。
db1=# create external table pxf_sales_part(db1(# item_name text, item_type text, db1(# supplier_key integer, item_price double precision, db1(# delivery_state text, delivery_city text)db1-# location ('pxf://hdp1:51200/test.sales_part?profile=hive')db1-# format 'custom' (formatter='pxfwritable_import');CREATE EXTERNAL TABLEdb1=# select * from pxf_sales_part; item_name | item_type | supplier_key | item_price | delivery_state | delivery_city -----------+-----------+--------------+------------+----------------+--------------- block | widget | 33 | 15.17 | CALIFORNIA | Fresno dowel | widget | 51 | 31.82 | NEVADA | Reno cube | widget | 11 | 1.17 | CALIFORNIA | Sacramento px49 | pipe | 52 | 99.82 | NEVADA | Las Vegas(4 rows)执行一个非过滤下推的查询。
db1=# select * from pxf_sales_part where delivery_city = 'Sacramento' and item_name = 'cube'; item_name | item_type | supplier_key | item_price | delivery_state | delivery_city -----------+-----------+--------------+------------+----------------+--------------- cube | widget | 11 | 1.17 | CALIFORNIA | Sacramento(1 row)该查询会利用Hive过滤delivery_city='Sacramento'的分区,但item_name上的过滤条件不会下推至Hive,因为它不是分区列。当所有Sacramento分区的数据传到HAWQ后,在HAWQ端执行item_name的过滤。
执行一个过滤下推的查询。
db1=# select * from pxf_sales_part where delivery_state = 'CALIFORNIA'; item_name | item_type | supplier_key | item_price | delivery_state | delivery_city -----------+-----------+--------------+------------+----------------+--------------- cube | widget | 11 | 1.17 | CALIFORNIA | Sacramento block | widget | 33 | 15.17 | CALIFORNIA | Fresno(2 rows)
(2)使用Hive Profile访问异构分区数据
一个Hive表中的不同分区可能有不同的存储格式,PXF Hive profile也支持这种情况。
建立Hive表。
$ HADOOP_USER_NAME=hdfs hivecreate 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 ',';记下sales_info和sales_info_rcfile表在HDFS中的位置。
describe extended sales_info;describe extended sales_info_rcfile;在我的环境中两个表的目录分别是:
location:hdfs://mycluster/apps/hive/warehouse/test.db/sales_infolocation:hdfs://mycluster/apps/hive/warehouse/test.db/sales_info_rcfile给hive_multiformpart表增加两个分区,位置分别指向sales_info和sales_info_rcfile
alter table hive_multiformpart add partition (year = '2013') location 'hdfs://mycluster/apps/hive/warehouse/test.db/sales_info';alter table hive_multiformpart add partition (year = '2016') location 'hdfs://mycluster/apps/hive/warehouse/test.db/sales_info_rcfile';显式标识与sales_info_rcfile表对应分区的文件格式。
alter table hive_multiformpart partition (year='2016') set fileformat rcfile;此时查看两个分区的存储格式可以看到,sales_info表对应的分区使用的是缺省的TEXTFILE格式,而sales_info_rcfile表对应的分区是RCFILE格式,分别如图4、图5所示。
show partitions hive_multiformpart;desc formatted hive_multiformpart partition(year=2013); desc formatted hive_multiformpart partition(year=2016);
db1=# select * from hcatalog.test.hive_multiformpart; location | month | number_of_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 ...(20 rows)使用外部表方式查询hive_multiformpart表。
db1=# create external table pxf_multiformpart(location text, month text, num_orders int, total_sales float8, year text)db1-# location ('pxf://hdp1:51200/test.hive_multiformpart?profile=hive')db1-# format 'custom' (formatter='pxfwritable_import');CREATE EXTERNAL TABLEdb1=# 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...(20 rows)db1=# select sum(num_orders) from pxf_multiformpart where month='Dec' and year='2013'; sum ----- 433(1 row)
四、访问JSON数据
PXF的JSON插件用于读取存储在HDFS上的JSON文件,支持N层嵌套。为了使用HAWQ访问JSON数据,必须将JSON文件存储在HDFS上,并从HDFS数据存储创建外部表。在使用PXF访问JSON文件前,确认满足以下前提条件:
- 已经在集群所有节点上安装了HDFS插件(Ambari会自动安装)。
- 已经在集群所有节点上安装了JSON插件(Ambari会自动安装)。
- 已经测试了PXF对HDFS的访问。
JSON是一种基于文本的数据交换格式,其数据通常存储在一个以.json为后缀的文件中。一个.json文件包含一组对象的集合,一个JSON对象是一组无序的名/值对,值可以是字符串、数字、true、false、null,或者一个对象或数组。对象和数组可以嵌套。例如,下面是一个JSON数据文件的内容:
{ "created_at":"MonSep3004:04:53+00002013", "id_str":"384529256681725952", "user": { "id":31424214, "location":"COLUMBUS" }, "coordinates":null}(1)JSON到HAWQ的数据类型映射
为了在HAWQ中表示JSON数据,需要将使用私有数据类型的JSON值映射为等价的HAWQ数据类型值。表7是对JSON数据映射规则的总结。
JSON数据类型
HAWQ数据类型
integer、float、string、boolean
使用对应的HAWQ内建数据类型(integer、real、double precision、char、varchar、text、boolean)
Array
使用[]标识一个特定数组中具有私有数据类型成员的下标。
Object
使用 . 点标识符指定每个级别的具有私有数据类型的嵌套成员。
表7(2)JSON文件读模式
PXF的JSON插件用两个模式之一读取数据。缺省模式是每行一个完整的JSON记录,同时也支持对多行构成的JSON记录的读操作。下面是每种读模式的例子。示例schema包含数据列的名称和数据类型如下:
- “created_at” - text
- “id_str” - text
- “user” - object(“id” - integer,“location” - text)
- “coordinates” - object(“type” - text,“values” - array(integer))
{"created_at":"FriJun0722:45:03+00002013","id_str":"343136551322136576","user":{"id":395504494,"location":"NearCornwall"},"coordinates":{"type":"Point","values": [ 6, 50 ]}},{"created_at":"FriJun0722:45:02+00002013","id_str":"343136547115253761","user":{"id":26643566,"location":"Austin,Texas"}, "coordinates": null},{"created_at":"FriJun0722:45:02+00002013","id_str":"343136547136233472","user":{"id":287819058,"location":""}, "coordinates": null}例2 - 多行JSON记录读模式:
{ "root":[ { "record_obj":{ "created_at":"MonSep3004:04:53+00002013", "id_str":"384529256681725952", "user":{ "id":31424214, "location":"COLUMBUS" }, "coordinates":null }, "record_obj":{ "created_at":"MonSep3004:04:54+00002013", "id_str":"384529260872228864", "user":{ "id":67600981, "location":"KryberWorld" }, "coordinates":{ "type":"Point", "values":[ 8, 52 ] } } } ]}下面从PXF的JSON外部表查询上面的示例数据。
3. 将JSON数据装载到HDFS
PXF的JSON插件读取存储在HDFS中的JSON文件。因此在HAWQ查询JSON数据前,必须先将JSON文件传到HDFS上。将前面的单行和多行JSON记录分别保存到singleline.json和multiline.json文件中,而且确保JSON文件中没有空行,然后将文件传到HDFS。
su - hdfshdfs dfs -mkdir /user/datahdfs dfs -chown -R gpadmin:gpadmin /user/datahdfs dfs -put singleline.json /user/datahdfs dfs -put multiline.json /user/data文件传到HDFS后,就可以通过HAWQ查询JSON数据。
4. 查询外部的JSON数据
使用下面的语法创建一个表示JSON数据的HAWQ外部表。
CREATE EXTERNAL TABLE <table_name> ( <column_name> <data_type> [, ...] | LIKE <other_table> )LOCATION ( 'pxf://<host>[:<port>]/<path-to-data>?PROFILE=Json[&IDENTIFIER=<value>]' ) FORMAT 'CUSTOM' ( FORMATTER='pxfwritable_import' );CREATE EXTERNAL TABLE语句中使用的各个关键字和相应值的描述如表8所示。
关键字
值
<host>[:<port>]
HDFS NameNode主机名、端口。
PROFILE
PROFILE关键字必须指定为Json。
IDENTIFIER
只有当JSON文件是多行记录格式时,LOCATION字符串中才包含IDENTIFIER关键字及其对应的值。<value>应该标识用以确定一个返回的JSON对象的成员名称,例如上面的示例2中,应该指定&IDENTIFIER=created_at。
FORMAT
FORMAT子句必须指定为CUSTOM。
FORMATTER
JSON 'CUSTOM'格式只支持内建的'pxfwritable_import'格式属性。
表8创建一个基于单行记录的JSON外部表。
create external table sample_json_singleline_tbl( created_at text, id_str text, text text, "user.id" integer, "user.location" text, "coordinates.values[0]" integer, "coordinates.values[1]" integer)location('pxf://hdp1:51200/user/data/singleline.json?profile=json')format 'custom' (formatter='pxfwritable_import');select * from sample_json_singleline_tbl;查询结果如图6所示。
多行记录的JSON外部表与单行的类似,只是需要指定identifier,指定标识记录的键。
db1=# create external table sample_json_multiline_tbl(db1(# created_at text,db1(# id_str text,db1(# text text,db1(# "user.id" integer,db1(# "user.location" text,db1(# "coordinates.values[0]" integer,db1(# "coordinates.values[1]" integerdb1(# )db1-# location('pxf://hdp1:51200/user/data/multiline.json?profile=json&identifier=created_at')db1-# format 'custom' (formatter='pxfwritable_import');CREATE EXTERNAL TABLEdb1=# select * from sample_json_multiline_tbl; created_at | id_str | text | user.id | user.location | coordinates.values[0] | coordinates.values[1] ---------------------------+--------------------+------+----------+---------------+-----------------------+----------------------- MonSep3004:04:53+00002013 | 384529256681725952 | | 31424214 | COLUMBUS | | MonSep3004:04:54+00002013 | 384529260872228864 | | 67600981 | KryberWorld | 8 | 52(2 rows)
五、向HDFS中写入数据
PXF只能向HDFS文件中写入数据,而对Hive、HBase和JSON等外部数据都是只读的。在使用PXF向HDFS文件写数据前,确认已经在集群所有节点上安装了PXF HDFS插件(Ambari会自动安装),并授予了HAWQ用户(典型的是gpadmin)对HDFS文件相应的读写权限。
1. 写PXF外部表
PXF HDFS插件支持两种可写的profile:HdfsTextSimple和SequenceWritable。创建HAWQ可写外部表的语法如下:
CREATE WRITABLE EXTERNAL TABLE <table_name> ( <column_name> <data_type> [, ...] | LIKE <other_table> )LOCATION ('pxf://<host>[:<port>]/<path-to-hdfs-file> ?PROFILE=HdfsTextSimple|SequenceWritable[&<custom-option>=<value>[...]]')FORMAT '[TEXT|CSV|CUSTOM]' (<formatting-properties>);CREATE EXTERNAL TABLE语句中使用的各个关键字和相应值的描述如表9所示。
关键字
值
<host>[:<port>]
HDFS NameNode主机名、端口。
<path-to-hdfs-file>
HDFS文件路径。
PROFILE
PROFILE关键字指定为HdfsTextSimple或SequenceWritable。
<custom-option>
与特定PROFILE对应的定制选项。
FORMAT 'TEXT'
当<path-to-hdfs-file>指向一个单行固定分隔符的平面文件时,使用该关键字。
FORMAT 'CSV'
当<path-to-hdfs-file>指向一个单行或多行的逗号分隔值(CSV)平面文件时,使用该关键字。
FORMAT 'CUSTOM'
SequenceWritable profile使用该关键字。SequenceWritable 'CUSTOM'格式仅支持内建的formatter='pxfwritable_export(写)和formatter='pxfwritable_import(读)格式属性。
表93. 定制选项
HdfsTextSimple和SequenceWritable profile支持表10所示的定制选项:
选项
值描述
Profile
COMPRESSION_CODEC
压缩编解码对应的Java类名。如果不提供,不会执行数据压缩。支持的压缩编解码包括:org.apache.hadoop.io.compress.DefaultCodec和org.apache.hadoop.io.compress.BZip2Codec
HdfsTextSimple、SequenceWritable
COMPRESSION_CODEC
org.apache.hadoop.io.compress.GzipCodec
HdfsTextSimple
COMPRESSION_TYPE
使用的压缩类型,支持的值为RECORD(缺省)或BLOCK。
HdfsTextSimple、SequenceWritable
DATA-SCHEMA
写入器的序列化/反序列化类名。类所在的jar文件必须在PXF classpath中。该选项被SequenceWritable profile使用,并且没有缺省值。
SequenceWritable
THREAD-SAFE
该Boolean值决定表查询是否运行在多线程模式,缺省值为TRUE。
HdfsTextSimple、SequenceWritable
表104. 使用HdfsTextSimple Profile写数据
HdfsTextSimple profile用于向单行每记录(不含内嵌换行符)的固定分隔符平面文件写数据。使用HdfsTextSimple Profile的建立可写表时,可以选择记录或块压缩,支持以下压缩编解码方法。
- org.apache.hadoop.io.compress.DefaultCodec
- org.apache.hadoop.io.compress.GzipCodec
- org.apache.hadoop.io.compress.BZip2Codec
(1)创建可写外部表,数据写到HDFS的/data/pxf_examples/pxfwritable_hdfs_textsimple1目录中,字段分隔符为逗号。
create writable external table pxf_hdfs_writabletbl_1(location text, month text, num_orders int, total_sales float8) location ('pxf://hdp1:51200/data/pxf_examples/pxfwritable_hdfs_textsimple1?profile=hdfstextsimple') format 'text' (delimiter=e',');(2)向pxf_hdfs_writabletbl_1表插入数据。
insert into pxf_hdfs_writabletbl_1 values ( 'Frankfurt', 'Mar', 777, 3956.98 );insert into pxf_hdfs_writabletbl_1 values ( 'Cleveland', 'Oct', 3812, 96645.37 );insert into pxf_hdfs_writabletbl_1 select * from pxf_hdfs_textsimple;(3)查看HDFS文件的内容。
[hdfs@hdp1 ~]$ hdfs dfs -cat /data/pxf_examples/pxfwritable_hdfs_textsimple1/*Frankfurt,Mar,777,3956.98Cleveland,Oct,3812,96645.37Prague,Jan,101,4875.33Rome,Mar,87,1557.39Bangalore,May,317,8936.99Beijing,Jul,411,11600.67[hdfs@hdp1 ~]$ hdfs dfs -ls /data/pxf_examples/pxfwritable_hdfs_textsimple1Found 3 items-rw-r--r-- 3 pxf gpadmin 26 2017-03-22 10:45 /data/pxf_examples/pxfwritable_hdfs_textsimple1/236002_0-rw-r--r-- 3 pxf gpadmin 28 2017-03-22 10:45 /data/pxf_examples/pxfwritable_hdfs_textsimple1/236003_0-rw-r--r-- 3 pxf gpadmin 94 2017-03-22 10:46 /data/pxf_examples/pxfwritable_hdfs_textsimple1/236004_15[hdfs@hdp1 ~]$ hdfs dfs -cat /data/pxf_examples/pxfwritable_hdfs_textsimple1/236002_0Frankfurt,Mar,777,3956.98[hdfs@hdp1 ~]$ hdfs dfs -cat /data/pxf_examples/pxfwritable_hdfs_textsimple1/236003_0Cleveland,Oct,3812,96645.37[hdfs@hdp1 ~]$ hdfs dfs -cat /data/pxf_examples/pxfwritable_hdfs_textsimple1/236004_15Prague,Jan,101,4875.33Rome,Mar,87,1557.39Bangalore,May,317,8936.99Beijing,Jul,411,11600.67[hdfs@hdp1 ~]$可以看到,一共写入了6条记录,生成了3个文件。其中两个文件各有1条记录,另外一个文件中有4条记录,记录以逗号作为字段分隔符。
(4)查询可写外部表
HAWQ不支持对可写外部表的查询。为了查询可写外部表的数据,需要建立一个可读外部表,指向HDFS的相应文件。
db1=# select * from pxf_hdfs_writabletbl_1;ERROR: External scan error: It is not possible to read from a WRITABLE external table. Create the table as READABLE instead. (CTranslatorDXLToPlStmt.cpp:1041)db1=# create external table pxf_hdfs_textsimple_r1(location text, month text, num_orders int, total_sales float8)db1-# location ('pxf://hdp1:51200/data/pxf_examples/pxfwritable_hdfs_textsimple1?profile=hdfstextsimple')db1-# format 'csv';CREATE EXTERNAL TABLEdb1=# select * from pxf_hdfs_textsimple_r1; location | month | num_orders | total_sales -----------+-------+------------+------------- Cleveland | Oct | 3812 | 96645.37 Frankfurt | Mar | 777 | 3956.98 Prague | Jan | 101 | 4875.33 Rome | Mar | 87 | 1557.39 Bangalore | May | 317 | 8936.99 Beijing | Jul | 411 | 11600.67(6 rows)(5)建立一个使用Gzip压缩,并用冒号(:)做字段分隔符的可写外部表,注意类名区分大小写。
create writable external table pxf_hdfs_writabletbl_2 (location text, month text, num_orders int, total_sales float8) location ('pxf://hdp1:51200/data/pxf_examples/pxfwritable_hdfs_textsimple2?profile=hdfstextsimple&compression_codec=org.apache.hadoop.io.compress.GzipCodec') format 'text' (delimiter=e':');(6)插入数据
insert into pxf_hdfs_writabletbl_2 values ( 'Frankfurt', 'Mar', 777, 3956.98 );insert into pxf_hdfs_writabletbl_2 values ( 'Cleveland', 'Oct', 3812, 96645.37 );(7)使用-text参数查看压缩的数据
[hdfs@hdp1 ~]$ hdfs dfs -text /data/pxf_examples/pxfwritable_hdfs_textsimple2/*Frankfurt:Mar:777:3956.98Cleveland:Oct:3812:96645.37[hdfs@hdp1 ~]$可以看到刚插入的两条记录,记录以冒号作为字段分隔符。
七、删除外部表
使用drop external table <table_name>语句删除外部表,该语句并不删除外部数据,因为外部数据不是由HAWQ管理的。
- HAWQ技术解析(九) —— 外部数据
- HAWQ技术解析(二) —— 安装部署
- HAWQ技术解析(三) —— 基本架构
- HAWQ技术解析(四) —— 启动停止
- HAWQ技术解析(五) —— 连接管理
- HAWQ技术解析(六) —— 定义对象
- HAWQ技术解析(七) —— 存储分布
- HAWQ技术解析(八) —— 大表分区
- HAWQ技术解析(十) —— 过程语言
- HAWQ技术解析(十一) —— 数据管理
- HAWQ技术解析(十二) —— 查询优化
- HAWQ技术解析(十三) —— 资源管理
- HAWQ技术解析(十四) —— 高可用性
- HAWQ技术解析(十五) —— 备份恢复
- HAWQ技术解析(十六) —— 运维监控
- HAWQ技术解析(十七) —— 最佳实践
- HAWQ技术解析(十八) —— 问题排查
- HAWQ取代传统数仓实践(九)——维度表技术之退化维度
- 《使用Java理解程序逻辑》阶段测试1-笔试题
- Spring Cloud构建微服务架构(三)断路器
- 桥本分数式 回溯法
- C++学习学习笔记第1课
- 散列函数
- HAWQ技术解析(九) —— 外部数据
- C语言 判断字符是否是一个数字的两种方法
- iOS之安全加密
- Xcode报错: linker command failed with exit code 1 (use -v to see invocation)
- 文章标题
- Mybatis简介
- 《使用Java理解程序逻辑》阶段测试2-笔试题
- KD_Tree 【bzoj2648 && bzoj2716】SJY摆棋子 && [voilet 3] 天使玩偶
- LeetCode刷题【Array】 Minimum Path Sum