Skip to content

06.PXF.md

Latest commit

 

History

History
143 lines (114 loc) · 4.07 KB

06.PXF.md

File metadata and controls

143 lines (114 loc) · 4.07 KB

참고

https://gpdbkr.blogspot.com/search/label/GPDB6_PXF_S3연동
https://gpdbkr.blogspot.com/search/label/GPDB6-PXF-DB연동

1. Java 설치 ( 모든 노드 )

sudo java -version
sudo yum install java-17-openjdk
# yum install java-21-openjdk
export JAVA_HOME=/usr/lib/jvm/java-17-openjdk-17.0.15.0.6-2.el9.aarch64
# export JAVA_HOME=/usr/lib/jvm/java-21-openjdk-21.0.7.0.6-1.el9.aarch64
# rm /etc/alternatives/java
# ln -s /usr/lib/jvm/java-17-openjdk-17.0.15.0.6-2.el9.aarch64/bin/java /etc/alternatives/java

2. PXF 설치 ( 모든 노드 )

2-1. pxf 설치 & 권한 설정

sudo dnf install edb-whpg7-pxf-6.10.1-1.el9.x86_64.rpm 
sudo chown -R gpadmin:gpadmin /usr/local/edb-whpg7-pxf

2-2. 폴더 심볼릭 링크 설정
cd /usr/local/greenplum-db
ln -s /usr/local/edb-whpg7-pxf pxf

2-3. PXF Base 폴더 생성.

mkdir ~/pxf-base

2-4. PXF .bashrc 설정. vi ~/.bachrc

source /usr/local/greenplum-db/greenplum_path.sh
export JAVA_HOME=/usr/lib/jvm/java-17-openjdk-17.0.16.0.8-2.el9.x86_64

export GPHOME=/usr/local/greenplum-db
export PXF_HOME=/usr/local/greenplum-db/pxf
export PXF_BASE=${HOME}/pxf-base
export PATH=$GPHOME/bin:$PXF_HOME/bin:$PATH

export COORDINATOR_DATA_DIRECTORY=/data/coordinator/gpseg-1 
export PGPORT=5432

3. PXF 명령어

$PXF_HOME/bin/pxf --help

$PXF_HOME/bin/pxf cluster register
$PXF_HOME/bin/pxf [cluster] start
$PXF_HOME/bin/pxf [cluster] status
$PXF_HOME/bin/pxf [cluster] stop
$PXF_HOME/bin/pxf cluster sync   ; 설정 화일 모든 노드로 동기화.

4. PXF cluster 초기화

$PXF_HOME/bin/pxf cluster register

5. S3 연동

참고 : https://gpdbkr.blogspot.com/search/label/GPDB6_PXF_S3연동

5-1. csv 연동

5-2. Parquet 연동

6. DB 연동

6-1. EPAS 연동

6-2. Oracle 연동 참고 : https://gpdbkr.blogspot.com/search/label/GPDB6-PXF-DB연동

참고

CREATE EXTENSION pxf;

drop table rtitrans_etl4;

CREATE TABLE rtitrans (
    imsi character varying(82),
    subscriber_mccmnc character varying(10),
    msisdn character varying(82),
    imei character varying(50),
    called_digits character varying(50),
    start_datetime integer,
    end_datetime integer,
    first_cell_lac integer,
    first_cell_idsac integer,
    current_cell_lac integer,
    current_cell_idsac integer,
    dr_type integer,
    status character varying(50),
    ingest_time bigint,
    processed_time bigint,
    export_time bigint,
    extra_col text,
    gploaded_time timestamp without time zone
)
WITH (appendonly=true) DISTRIBUTED BY (imsi);

=== pxf minoi parquet =====

CREATE EXTERNAL TABLE minio_parquet_cusomer (
    -- MinIO Parquet 파일의 실제 스키마에 맞게 컬럼 정의
    -- 데이터 타입은 Parquet의 논리적 타입과 PostgreSQL 타입 매핑 고려
    c_customer_sk             INTEGER   ,
    c_customer_id             CHAR(16)  ,
    c_current_cdemo_sk        INTEGER                       ,
    c_current_hdemo_sk        INTEGER                       ,
    c_current_addr_sk         INTEGER                       ,
    c_first_shipto_date_sk    INTEGER                       ,
    c_first_sales_date_sk     INTEGER                       ,
    c_salutation              CHAR(10)                      ,
    c_first_name              CHAR(20)                      ,
    c_last_name               CHAR(30)                      ,
    c_preferred_cust_flag     CHAR(1)                       ,
    c_birth_day               INTEGER                       ,
    c_birth_month             INTEGER                       ,
    c_birth_year              INTEGER                       ,
    c_birth_country           VARCHAR(20)                   ,
    c_login                   CHAR(13)                      ,
    c_email_address           CHAR(50)                      ,
    c_last_review_date        CHAR(10)                      
)
--LOCATION ('pxf://192.168.56.51/pgaa/tpch_sf_1/customer/?PROFILE=s3&SERVER=s3')
LOCATION ('pxf://192.168.56.51/pgaa/tpch_sf_1/customer/?PROFILE=s3:parquet')
--ON ANY SEGMENT
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');