访问hbase

pxf读取hbase源

外部表中定义的名字需要和hbase中列的名字相同，或者通过hbase中的映射表pxflookup来做对应

以下是使用PXF连接HBase数据源的示例

1. 创建server

create extension pxf_fdw ;
create server hbase_test foreign data wrapper hbase_pxf_fdw options (host    'test-3');
create user mapping for public server hbase_test;

1. 使用外部表方式访问

1) 列名字和hbase中的相同

  ```sql
  seaboxsql=# create foreign table t_hbase("product:name" varchar,       "shipping_info:zipcode" int) server hbase_test options(resource 'order_info');
  CREATE FOREIGN TABLE
  seaboxsql=# select * from t_hbase;
    product:name  | shipping_info:zipcode 
  ----------------+-----------------------
   tennis racquet |                 12345
   soccer ball    |                 56789
   snorkel set    |                 34567
  (3 rows)
  ```

2) 使用映射表访问hbase

  ```bash
  seaboxsql=# create foreign table t_hbase_map(pname varchar, zip int) server       hbase_test options(resource 'order_info');
  CREATE FOREIGN TABLE
  seaboxsql=# select * from t_hbase_map;
       pname      |  zip  
  ----------------+-------
   tennis racquet | 12345
   soccer ball    | 56789
   snorkel set    | 34567
  (3 rows)
  ```

1. 使用COPY命令访问

seaboxsql=# create table t_hbase("product:name" varchar, "shipping_info:zipcode"     int);
CREATE TABLE
seaboxsql=# copy t_hbase from 'fdw://hbase_test/order_info';
COPY 3
seaboxsql=# select * from t_hbase;
  product:name  | shipping_info:zipcode 
----------------+-----------------------
 soccer ball    |                 56789
 snorkel set    |                 34567
 tennis racquet |                 12345
(3 rows)

seaboxsql=# create table t_hbase_map(pname varchar, zip int);
CREATE TABLE
seaboxsql=# copy t_hbase_map from 'fdw://hbase_test/order_info';
COPY 3
seaboxsql=# select * from t_hbase_map;
     pname      |  zip  
----------------+-------
 tennis racquet | 12345
 soccer ball    | 56789
 snorkel set    | 34567
(3 rows)

1. HBase中的数据准备

create 'order_info', 'product', 'shipping_info'

put 'order_info', '1', 'product:name', 'tennis racquet'
put 'order_info', '1', 'product:location', 'out of stock'
put 'order_info', '1', 'shipping_info:state', 'CA'
put 'order_info', '1', 'shipping_info:zipcode', '12345'
put 'order_info', '2', 'product:name', 'soccer ball'
put 'order_info', '2', 'product:location', 'on floor'
put 'order_info', '2', 'shipping_info:state', 'CO'
put 'order_info', '2', 'shipping_info:zipcode', '56789'
put 'order_info', '3', 'product:name', 'snorkel set'
put 'order_info', '3', 'product:location', 'warehouse'
put 'order_info', '3', 'shipping_info:state', 'OH'
put 'order_info', '3', 'shipping_info:zipcode', '34567'

--- 下面这个是映射表，名称必须是pxflookup
create 'pxflookup', 'mapping'
put 'pxflookup', 'order_info', 'mapping:pname', 'product:name'
put 'pxflookup', 'order_info', 'mapping:zip', 'shipping_info:zipcode'

配置文件注意事项 1. $PXF_CONF/servers/default或者对应server name下需要包含hbase-site.xml