PolarDB에서 OSS에 있는 csv파일 로드하기 (FOREIGN TABLE)

 

Introduction

 이번 포스트에서는 PolarDB (for MySQL or PostreSQL)을 이용해 OSS (Object Storage Service)에 업로드한 CSV파일을 간단하게 읽는 방법에 대해서 설명한다. 이 방법을 통해 AWS의 Athena나 Redshift Spectrum처럼 PolarDB에서도 OSS에 있는 CSV형식의 파일에 다이렉트로 쿼리할 수 있다.

 

Prerequisites

버전 확인

Alibaba cloud 공식 문서에 따르면 PolarDB for MySQL의 경우 아래 버전을 만족해야 한다. :

• A cluster of PolarDB for MySQL 8.0.1 whose revision version is 8.0.1.1.25.4 or later.
• A cluster of PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.1 or later.

 

만약 PolarDB for PostgreSQL을 사용하고 있다면 아래 버전을 만족해야 한다. PolarDB 문서에는 나와있지 않지만 Postgres docs에는 나와있다. :

• A cluster of PolarDB for PostgreSQL whose version is 12 or later.

OSS에 액세스하기 위한 KEY 생성

OSS에 PolarDB가 액세스할 수 있도록 키를 발급해야 한다.

1. RAM (Resource Access Management) 콘솔에 들어간다.
2. 좌측 패널에서 Identities > Users를 찾아 들어간다.
3. RAM사용자의 이름을 클릭하거나 OSS access 권한을 가진 새로운 RAM사용자를 만든다.
4. User AccessKeys 섹션에서 Create AccessKey를 클릭한다.
5. Create AccessKey 창에서 AccessKey ID 와 AccessKey secret를 확인한다.
6. Download CSV File 를 눌러 키페어를 CSV로 다운로드 하거나 Copy 를 눌러서 복사할 수 있다.

SQL 실행 절차 in MySQL

OSS의 정보를 가지고 server를 만든다.

CREATE SERVER <server_name> 
FOREIGN DATA WRAPPER oss OPTIONS (
    DATABASE '<my_database_name>', --optional
    EXTRA_SERVER_INFO '{
        "oss_endpoint": "<my_oss_endpoint>",
        "oss_bucket": "<my_oss_bucket>",
        "oss_access_key_id": "<my_oss_access_key_id>",
        "oss_access_key_secret": "<my_oss_access_key_secret>",
        "oss_prefix":"<my_oss_prefix>",
        "oss_sts_token":"<my_oss_sts_token>"
    }' -- prefix and sts token are optional
);

foreign table을 만든다.

CREATE TABLE <table_name> 
(...) 
engine=csv 
connection="<connection_string>"

 

<connection_string> consists of the OSS server name and optional directory or file path.

<connection_string> 는 위에서 만든 서버의 이름과 부가적인 파일 혹은 디렉토리의 경로를 포함한다.

 

e.g. "server_name/folder_name/file_name" (".csv" 확장자는 포함하지 않음)

 

SQL 실행 절차 PostgreSQL

OSS를 위한 foreign data wrapper를 만든다.

CREATE EXTENSION oss_fdw;

OSS 서버 정보를 가지고 server를 만든다.

CREATE SERVER <server_name>
FOREIGN DATA WRAPPER "oss_fdw" OPTIONS
(
    HOST '<oss_endpoint>',
    BUCKET '<bucket_name>',
    ID '<key_id>',
    KEY '<key_secret>'
);

foreign table을 만든다.

CREATE FOREIGN TABLE <table_name> 
(...) 
SERVER <server_name> 
OPTIONS (
    FILEPATH '<file_path>', 
    DELIMITER ',' ,
    FORMAT 'csv', 
    ENCODING 'utf8', 
    PARSE_ERRORS '100'
);

 

<file_path> 은 경로를 포함한 OSS에 있는 파일 이름이다.

 

e.g. 'folder/filename.csv' 혹은 디렉토리를 입력하고 *를 통해 해당 위치의 모든 파일을 읽을 수도 있다.