Skip to content

06-1.PXF-Impala연동.md

Latest commit

 

History

History
172 lines (138 loc) · 3.34 KB

06-1.PXF-Impala연동.md

File metadata and controls

172 lines (138 loc) · 3.34 KB

PXF Impala JDBC 연동 가이드

WarehousePG에서 PXF를 통해 Impala 테이블을 외부 테이블로 연동하는 설정 가이드입니다.
씨플랫폼에서 테스트 및 작성.

(0) PXF_BASE 디렉토리

/home/gpadmin/pxf-base

(1) Impala JDBC 드라이버 설치 (전체 노드)

[gpadmin@whpg-m lib]$ pwd
/home/gpadmin/pxf-base/lib

[gpadmin@whpg-m lib]$ ls
ImpalaJDBC41.jar

(2) jdbc-site.xml 설정 (전체 노드)

[gpadmin@whpg-m impala]$ pwd
/home/gpadmin/pxf-base/servers/impala

[gpadmin@whpg-m impala]$ cat jdbc-site.xml
<configuration>
    <property>
        <name>jdbc.driver</name>
        <value>com.cloudera.impala.jdbc.Driver</value>
    </property>
    <property>
        <name>jdbc.url</name>
        <value>jdbc:impala://10.1.10.134:21050/impala_db</value>
    </property>
    <property>
        <name>jdbc.user</name>
        <value>cloudera</value>
    </property>
    <property>
        <name>jdbc.password</name>
        <value>cloudera</value>
    </property>
</configuration>

(3) Table DDL

3-1) Impala 테이블

CREATE TABLE kudu_table (
    id   INT,
    name STRING,
    age  INT
);

INSERT INTO kudu_table VALUES (1, '홍길동', 300);
INSERT INTO kudu_table VALUES (2, 'King',   1);
INSERT INTO kudu_table VALUES (3, '金文受', 88);
INSERT INTO kudu_table VALUES (1, '홍길동', 300);
INSERT INTO kudu_table VALUES (2, 'King',   1);
INSERT INTO kudu_table VALUES (3, '金文受', 88);
INSERT INTO kudu_table VALUES (1, '홍길동', 300);
INSERT INTO kudu_table VALUES (2, 'King',   1);
INSERT INTO kudu_table VALUES (3, '金文受', 88);

CREATE EXTERNAL TABLE sample_parquet_table
STORED AS PARQUET
LOCATION '/user/cloudera/parquet/'
AS SELECT * FROM kudu_table;

3-2) WarehousePG External Table

3-2-1) 실제 생성 DDL

CREATE EXTERNAL TABLE pxf_parquet_table_jdbc (
    id   TEXT,
    name TEXT,
    age  INT
)
LOCATION ('pxf://impala_db.sample_parquet_table?profile=jdbc&server=impala')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');

3-2-2) DBeaver 생성 DDL

CREATE FOREIGN TABLE public.pxf_parquet_table_jdbc (
    id   text NULL,
    "name" text NULL,
    age  int4 NULL
)
SERVER gp_exttable_server
OPTIONS (
    formatter     'pxfwritable_import',
    format        'custom',
    location_uris 'pxf://impala_db.sample_parquet_table?profile=jdbc&server=impala',
    execute_on    'ALL_SEGMENTS',
    log_errors    'disable',
    encoding      'UTF8',
    is_writable   'false'
);

(4) WarehousePG External 테이블 조회 결과

[gpadmin@whpg-m impala]$ whpg -c "select * from pxf_parquet_table_jdbc;"
 id | name | age
----+------+-----
 3  | 金   |  88
 1  | 홍   | 300
 2  | King |   1
 1  | 홍   | 300
 3  | 金   |  88
 2  | King |   1
 3  | 金   |  88
 1  | 홍   | 300
 2  | King |   1
(9 rows)

(5) External 테이블 소스로 CTAS 테이블 생성

CREATE TABLE sample_parquet_table_whpg
AS (SELECT * FROM public.pxf_parquet_table_jdbc);

생성 후 조회 결과

[gpadmin@whpg-m impala]$ whpg -c "select * from pxf_parquet_table_jdbc"
 id | name | age
----+------+-----
 1  | 홍   | 300
 3  | 金   |  88
 2  | King |   1
 3  | 金   |  88
 1  | 홍   | 300
 2  | King |   1
 3  | 金   |  88
 1  | 홍   | 300
 2  | King |   1
(9 rows)