03_matrixdb通过pxf读写hdfs文件

本文主要是介绍03_matrixdb通过pxf读写hdfs文件，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

matrixdb视频教程 - 03matrixdb通过pxf读写hdfs文件

作者

shidb

日期

2021-05-11

环境介绍

sdw5 namenode hive
sdw3 matrixdb集群

matrixdb通过pxf读取hdfs文件

在hdfs上创建文件夹

[hdfs@sdw5 ~]$ hdfs dfs -mkdir /matrixdb/pxf

上传文件

echo 'Prague,Jan,101,4875.33
> Rome,Mar,87,1557.39
> Bangalore,May,317,8936.99
> Beijing,Jul,411,11600.67' > pxf_hdfs_simple.txt[hdfs@sdw5 ~]$ ls
pxf_hdfs_simple.txt[hdfs@sdw5 ~]$ hdfs dfs -put pxf_hdfs_simple.txt /matrixdb/pxf/
hdfs dfs -cat /matrixdb/pxf/pxf_hdfs_simple.txt
Prague,Jan,101,4875.33
Rome,Mar,87,1557.39
Bangalore,May,317,8936.99
Beijing,Jul,411,11600.67

cdh配置proxyuser

hdfs->配置->core-site.xml
core-site.xml的集群范围高级配置添加
hadoop.proxyuser.shidb.host 
*
hadoop.proxyuser.shidb.group 
*

配置pxf

将hdfs下的core-site.xml、hdfs-site.xml拷贝到

[root@sdw5 ~]# cp /etc/hadoop/conf.cloudera.hdfs/core-site.xml .
[root@sdw5 ~]# cp /etc/hadoop/conf.cloudera.hdfs/hdfs-site.xml .拷贝到所有matrixdb pxf节点的如下路径
cp core-site.xml /usr/local/pxf-matrixdb3/conf/servers/cdh_hdfs/
cp hdfs-site.xml /usr/local/pxf-matrixdb3/conf/servers/cdh_hdfs/

如果之前已经配置过，需要重置

停止pxf

[shidb@sdw3 ~]$ pxf cluster stop
Stopping PXF on 1 segment host…
PXF stopped successfully on 1 out of 1 host

重置pxf配置

[shidb@sdw3 ~]$ pxf cluster reset
Ensure your PXF cluster is stopped before continuing. This is a destructive action. Press y to continue:
y
Resetting PXF on master host and 0 segment hosts...
PXF has been reset on 1 out of 1 host

初始化pxf

[shidb@sdw3 ~]$ pxf cluster init
Initializing PXF on master host and 0 segment hosts...
PXF initialized successfully on 1 out of 1 host

pxf同步

[shidb@sdw3 ~]$ pxf cluster sync
Syncing PXF configuration files from master host to 1 segment host...
PXF configs synced successfully on 1 out of 1 host

启动PXF

[shidb@sdw3 ~]$ pxf cluster start
Starting PXF on 1 segment host...
PXF started successfully on 1 out of 1 host

查看pxf状态

[shidb@sdw3 ~]$ pxf cluster status
Checking status of PXF servers on 1 segment host...
PXF is running on 1 out of 1 host

master上单独也需要启动

[shidb@sdw3 ~]$ pxf start
Using CATALINA_BASE:   /usr/local/pxf-matrixdb3/pxf-service
Using CATALINA_HOME:   /usr/local/pxf-matrixdb3/pxf-service
Using CATALINA_TMPDIR: /usr/local/pxf-matrixdb3/pxf-service/temp
Using JRE_HOME:        /usr/local/jdk1805/jre
Using CLASSPATH:       /usr/local/pxf-matrixdb3/pxf-service/bin/bootstrap.jar:/usr/local/pxf-matrixdb3/pxf-service/bin/tomcat-juli.jar
Using CATALINA_PID:    /usr/local/pxf-matrixdb3/run/catalina.pid
Existing PID file found during start.
Tomcat appears to still be running with PID 763115. Start aborted.
If the following process is not a Tomcat process, remove the PID file and try again:
UID         PID   PPID  C STIME TTY          TIME CMD
shidb    763115      1 99 12:27 pts/178  00:00:28 /usr/local/jdk1805/jre/bin/java -Djava.util.logging.config.file=/usr/local/pxf-matrixdb3/pxf-service/conf/logging.properties -Djava.util.logg
[shidb@sdw3 ~]$

matrixdb操作

创建插件

CREATE EXTENSION pxf_fdw ;

创建FDW Server

DROP SERVER hdfs_svr FOREIGN DATA WRAPPER hdfs_pxf_fdw;
CREATE SERVER hdfs_svr FOREIGN DATA WRAPPER hdfs_pxf_fdw OPTIONS ( config 'single_hdfs' );

创建用户名admin的FDW User mapping

CREATE USER MAPPING FOR shidb SERVER hdfs_svr;
drop USER MAPPING FOR shidb SERVER hdfs_svr;CREATE USER MAPPING FOR hdfs SERVER hdfs_svr;
drop USER MAPPING FOR hdfs SERVER hdfs_svr;

创建FDW Foreign Table，指向对应的HDFS文件

DROP FOREIGN TABLE pxf_hdfs_table;
CREATE FOREIGN TABLE pxf_hdfs_table (
location text,
month text,
num_orders int,
total_sales float8) SERVER hdfs_svr OPTIONS ( resource '/matrixdb/pxf/pxf_hdfs_simple.txt', format 'text',delimiter ',');

9.查询Foreign Table获取数据

SELECT * FROM pxf_hdfs_table ;
postgres=# select * from pxf_hdfs_table ;location  | month | num_orders | total_sales
-----------+-------+------------+-------------Prague    | Jan   |        101 |     4875.33Rome      | Mar   |         87 |     1557.39Bangalore | May   |        317 |     8936.99Beijing   | Jul   |        411 |    11600.67
(4 rows)

matrixdb读写hdfs

drop FOREIGN TABLE pxf_hdfs_dir;
CREATE FOREIGN TABLE pxf_hdfs_dir (
location text,
month text,
num_orders int,
total_sales float8) SERVER hdfs_svr OPTIONS ( resource '/matrixdb/pxf/', format 'text',delimiter ',');postgres=# select * from pxf_hdfs_dir;location  | month | num_orders | total_sales 
-----------+-------+------------+-------------Prague    | Jan   |        101 |     4875.33Rome      | Mar   |         87 |     1557.39Bangalore | May   |        317 |     8936.99Beijing   | Jul   |        411 |    11600.67

如需入群沟通交流，请扫码添加好友
在这里插入图片描述