Greenplum의 데이터 연동 방식은 dblink,gpcopy,PXF, gpkafka 등 여러 방식으로 데이터 이관 또는 통합이 가능합니다.
이중 Pivotal Extension Framework (PXF)의 기본 개념은 데이터는 원천 시스템에 저장, 연산은 Greenplum에서 수행합니다.
즉, 이기종 database를 ETL과 같이 데이터 이관 없이 Greenplum에서 조회 및 연산 수행할 수 있습니다. 이는 Greenplum의 one SQL으로 멀티 소스의 데이터를 통합 분석이 가능합니다.
Greenplum 는 External Table으로 쉬운 Interface 제공(연동 편의성)하고, 고속의 병렬 Interface 기능으로 최상의 성능 제공하고 있습니다.
PXF 지원하는 이기종 시스템
- 하둡 에코 시스템 (Hadoop, HDFS, Hive, and HBase)
- RDBMS (Oracle, DB2, MSSQL, MySQL, Postgresql 등)
- 클라우드 스토리지(AWS S3, Minio, Google Cloud Storage, Azure Blob Storage and Azure Data Lake)
PXF 지원하는 포맷
- Avro, AvroSequenceFile
- JSON
- ORC
- Parquet
- RCFile
- SequenceFile
- Text (plain, delimited, embedded line feeds)
아래의 예제는 PXF를 이용하여 Oracle의 데이터를 Greenplum에서 조회하는 기능입니다.
### Greenplum PXF 설정 요약
1. Java 설치
- PXF는 Java로 구동되기 때문에 사전에 Java 1.8 또는 Java 11를 모든 노드에 설치합니다.
2. PXF 설정
- Greenplum 엔진에 기본적으로 탑재되어 있어, 설정만 하면 됩니다.
- JDBC 연동하는 경우에는 각 DBMS의 jdbc 드라이버가 필요합니다.
3. 관련 링크
-관련 링크: https://gpdb.docs.pivotal.io/6-5/pxf/instcfg_pxf.html
### Java 설치 및 PXF 설정, Oracle 연동
1. PXF 를 이용하기 위한 Java 설치 (모든 노드)
- PXF는 Java 기반으로 구성되어 Java가 필수적입니다.
- 모든 노드에 jdk 설치 필요 합니다.
- 개별 설치시
# yum -y install java-1.8.0-openjdk-1.8.0* (개별 설치시)
- 전체 노드 일괄 설치시 (데이터 노드 전체 설치시)
# gpssh -e -v -f gphostfile sudo yum -y install java-1.8.0-openjdk-1.8.0*
2. Java 환경 설정
- 각 노드별로 설정 필요합니다.
- 개별 설정 시
$ echo 'export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.x86_64/jre' >> /home/gpadmin/.bashrc
- 전체 노드 일괄 설정시
$ gpssh -e -v -f /home/gpadmin/gpconfigs/hostfile "echo 'export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.242.b08-0.el7_7.x86_64/jre' >> /home/gpadmin/.bashrc"
- 설정 확인
$ gpssh -e -v -f /home/gpadmin/gpconfigs/hostfile "cat /home/gpadmin/.bashrc | grep JAVA_HOME"
[INFO] login mdw
[INFO] login sdw2
[INFO] login sdw1
[ mdw] cat /home/gpadmin/.bashrc | grep JAVA_HOME
[ mdw] export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.242.b08-0.el7_7.x86_64/jre
[sdw2] cat /home/gpadmin/.bashrc | grep JAVA_HOME
[sdw2] export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.242.b08-0.el7_7.x86_64/jre
[sdw1] cat /home/gpadmin/.bashrc | grep JAVA_HOME
[sdw1] export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.242.b08-0.el7_7.x86_64/jre
[INFO] completed successfully
3. Initializing PXF
1) 마스터 노드에서 PXF 클러스터 초기구성
$ PXF_CONF=/usr/local/greenplum-pxf $GPHOME/pxf/bin/pxf cluster init
Initializing PXF on master and 2 other hosts...
PXF initialized successfully on 3 out of 3 hosts
2) 커멘드
$ $GPHOME/pxf/bin/pxf cluster start
$ $GPHOME/pxf/bin/pxf cluster status
$ $GPHOME/pxf/bin/pxf cluster stop
$ $GPHOME/pxf/bin/pxf cluster reset
3) pxf service 확인
[gpadmin@mdw gpconfigs]$ $GPHOME/pxf/bin/pxf cluster status
Checking status of PXF servers on 2 hosts...
PXF. is running on 2 out of 2 hosts
[gpadmin@mdw gpconfigs]$
4) pxf binary 경로
$ cd /usr/local/greenplum-db/pxf
$ ls
apache-tomcat bin conf lib pxf-service run templates version
4. oracle jdbc 복사 및 설정
1) oracle jdbc 파일 복사
$ cp ojdbc6.jar $PXF_CONF/lib
$ ls -la $PXF_CONF/lib
2) oracle 서버 환경 설정
oracle의 서비스가 여려개 될 수 있기 때문에 각 서버/서비스별로 별도의 별칭으로 폴더 생성
아래의 예시는 oracle_s1 으로 함.
$ mkdir $PXF_CONF/servers/oracle_s1
$ cd $PXF_CONF/server/oracle_s1
$ vi jdbc-site.xml
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<property>
<name>jdbc.driver</name>
<value>oracle.jdbc.OracleDriver</value>
</property>
<property>
<name>jdbc.url</name>
<value>jdbc:oracle:thin:@172.16.127.161:1521/orcl</value>
</property>
<property>
<name>jdbc.user</name>
<value>scott</value>
</property>
<property>
<name>jdbc.password</name>
<value>tiger</value>
</property>
</configuration>
3) 설정 파일 모든 노드에 Sync(복제)
- 마스터 노드만 설정하고 난뒤 모든 노드에 일괄 동기화 (파일 및 설정 파일 동기화)
$ $GPHOME/pxf/bin/pxf cluster sync
$ gpssh -h sdw1 -h sdw2 "ls -la /usr/local/greenplum-pxf/servers/oracle_s1"
[sdw1] 합계 4
[sdw1] drwxrwxr-x 2 gpadmin gpadmin 27 3월 31 17:56 .
[sdw1] drwxrwxr-x 5 gpadmin gpadmin 51 3월 31 17:47 ..
[sdw1] -rw-rw-r-- 1 gpadmin gpadmin 432 3월 31 17:56 jdbc-site.xml
[sdw2] 합계 4
[sdw2] drwxrwxr-x 2 gpadmin gpadmin 27 3월 31 17:56 .
[sdw2] drwxrwxr-x 5 gpadmin gpadmin 51 3월 31 17:47 ..
[sdw2] -rw-rw-r-- 1 gpadmin gpadmin 432 3월 31 17:56 jdbc-site.xml
$ gpssh -h sdw1 -h sdw2 "ls -la /usr/local/greenplum-pxf/lib"
[sdw1] 합계 1944
[sdw1] drwxrwxr-x 2 gpadmin gpadmin 24 3월 31 17:50 .
[sdw1] drwxrwxr-x 8 gpadmin gpadmin 88 2월 7 11:39 ..
[sdw1] -rwxr-xr-x 1 gpadmin gpadmin 1988051 3월 31 17:50 ojdbc6.jar
[sdw2] 합계 1944
[sdw2] drwxrwxr-x 2 gpadmin gpadmin 24 3월 31 17:50 .
[sdw2] drwxrwxr-x 8 gpadmin gpadmin 88 2월 7 11:39 ..
[sdw2] -rwxr-xr-x 1 gpadmin gpadmin 1988051 3월 31 17:50 ojdbc6.jar
[gpadmin@mdw oracle_s1]$
4) pxf 설정 반영
$ $GPHOME/pxf/bin/pxf cluster stop
$ $GPHOME/pxf/bin/pxf cluster start
5. Greenplum 에서 oracle 접속하기 위한 유저 설정
1) 계정 생성 및 권한 설정
- 일반 유저로 생성합니다.
$ psql
gpadmin=# CREATE EXTENSION pxf;
gpadmin=# CREATE ROLE uorascott LOGIN ENCRYPTED PASSWORD 'oracle';
gpadmin=# GRANT SELECT ON PROTOCOL pxf TO uorascott;
gpadmin=# GRANT INSERT ON PROTOCOL pxf TO uorascott;
gpadmin=# GRANT ALL ON PROTOCOL pxf TO uorascott;
2) EXTERNAL TABLE 생성
- 테이블 생성시 LOCATION에는 오라클의 유저계정과 테이블명 사용 (scott.tiger)
- SERVER명은 여러개의 Oracle 서버가 구성될 수 있기 때문에, 서버명을 기입해야 함.
$ psql -U uorascott -d gpadmin
DROP EXTERNAL TABLE IF EXISTS EXT_R_EMP;
CREATE EXTERNAL TABLE EXT_R_EMP
(
EMPNO NUMERIC(4),
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR NUMERIC(4),
HIREDATE DATE,
SAL NUMERIC(7,2),
COMM NUMERIC(7,2),
DEPTNO NUMERIC(2)
)
LOCATION ('pxf://scott.emp?PROFILE=Jdbc&SERVER=oracle_s1')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
3) Greenplum 에서 External table 조회
- 조회 시점에 Oracle의 데이터를 가져옵니다.(JDBC를 통해서...)
gpadmin=> SELECT * FROM EXT_R_EMP;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+------------+---------+---------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | | 30
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20
7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30
7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10
(14 rows)
Time: 165.690 ms
6. 특수 기능
1) Partition Options
- 대량의 데이터 추출이 필요할 경우 PARTITION_BY, RANGE, INTERVAL을 이용하여 멀티 프로세스로 데이터 추출 지원
- 단 소량의 데이터일 경우에는 의미 없음. (아래는 예시입니다.)
$ psql -U uorascott -d gpadmin
DROP EXTERNAL TABLE IF EXISTS EXT_R_EMP_PT;
CREATE EXTERNAL TABLE EXT_R_EMP_PT
(
EMPNO NUMERIC(4),
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR NUMERIC(4),
HIREDATE DATE,
SAL NUMERIC(7,2),
COMM NUMERIC(7,2),
DEPTNO NUMERIC(2)
)
LOCATION ('pxf://scott.emp?PROFILE=Jdbc&SERVER=oracle_s1&PARTITION_BY=SAL:int&RANGE=100:10000&INTERVAL=1000')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
- Partition Options 조회
gpadmin=> SELECT * FROM EXT_R_EMP_PT;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+------------+---------+---------+--------
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30
7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | | 30
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30
7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10
(14 rows)
Time: 365.874 ms <<<<<<<<<<<<<< 프로세싱하는 동안 시간이 지연, 용량이 클 때 파티션 기능 유리.
gpadmin=>
2) Named Query
- 사전에 쿼리를 저장하고 난 뒤에 쿼리를 이용하여 쿼리 결과를 External Table화 하는 기능
- 자세한 내용은 메뉴얼를 참조하시면 됩니다.
https://gpdb.docs.pivotal.io/6-5/pxf/jdbc_pxf.html
7. JDBC 설정 옵션
1) 메뉴얼 참조 : https://gpdb.docs.pivotal.io/6-5/pxf/jdbc_pxf.html
8. PXF 주요 기능
1) PXF Filter Pushdown
- External table 조회시 Where 조건이 들어갈 경우에는 원천 소스시스템에서 필터링되고 난뒤의 결과값을
Greenplum으로 가져옵니다.
=> 즉, 파티션 스캔/인덱스 스캔 후 필요한 데이터만 네트워크를 타고 이동.
- 이를 위해서는 Greenplum 파라미터 중에 gp_external_enable_filter_pushdown 가 on 일때 적용됩니다. (default 값은 on )
2) Column Projection
- External Table에 컬럼을 많이 정의하더라도, 조회되는 컬럼에 대해서만 데이터를 추출
- 소스가 되는 Oracle에서는 작업할 내역은 없습니다.
- 기본적으로 확인하기 위해서 수행했던 스크립트는 아래와 같습니다.
1) DB Startup and 리스너 start
$ sqlplus "/as sysdba"
SQL>startup
ORACLE instance started.
...
SQL>exit
$ lsnrctl start
$ tnsping orcl
$ sqlplus scott/tiger
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ------------ ---------- --------- -------------
7369 SMITH CLERK 7902 17-DEC-80 800 20
SQL> DESC EMP;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
2) Oracle로 접속시 에러 발생될 경우 => iptables를 내려주세요..
[root@oracle ~]# service iptables stop
iptables: Setting chains to policy ACCEPT: filter [ OK ]
iptables: Flushing firewall rules: [ OK ]
iptables: Unloading modules: [ OK ]
Pivotal 메뉴얼에 더 많은 정보가 있으니 참고하세요.
링크: https://gpdb.docs.pivotal.io/6-5/pxf/jdbc_pxf.html
