ADW/ATP(Autonomous DB)でObject StorageにアップロードされたCSVファイル(gzip圧縮済)を外部表(EXTERNAL TABLE)として直接参照する。(OCI, Oracle Cloud Infrastructure)

表題の通り、ADW/ATP(Autonomous DB)でObject Storageにアップロードされた
CSVファイル(gzip圧縮済)を外部表(EXTERNAL TABLE)として参照してみるやで彡(ﾟ)(ﾟ)

下記マニュアルを参照しています。

Using Oracle Autonomous Data Warehouse
Query External Data
https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/query-external.html#GUID-72DC8BB6-5245-4262-A100-C35A2E553F70

1. サンプルのCSVファイル(gzip圧縮済み)

下記のCSVファイル(gzip圧縮済み)をサンプルとします。

$ ls -la
total 16
drwxrwxr-x.  2 opc opc 4096 Mar 10 16:16 .
drwxrwxr-x. 23 opc opc 4096 Mar 10 15:15 ..
-rw-rw-r--.  1 opc opc   47 Mar 10 16:00 sample1.csv.gz
-rw-rw-r--.  1 opc opc   44 Mar 10 16:16 sample2.csv.gz
$ zcat sample1.csv.gz
1,a
2,b
3,c

$ zcat sample2.csv.gz
4,d
5,e
6,f

2. CSVファイル(gzip圧縮済)をObject Storageにアップロード

OCI CLI でCSVファイル(gzip圧縮済)をObject Storageにアップロードします。
OCI CLI のセットアップ方法はこちらの記事のリンク先を参照してね彡(ﾟ)(ﾟ)

oci os object bulk-upload -ns namespace -bn bucket-name --src-dir ./

Uploaded sample1.csv.gz  [####################################]  100%
{
  "skipped-objects": [],
  "upload-failures": {},
  "uploaded-objects": {
    "sample1.csv.gz": {
      "etag": "f4484cbe-48a3-4533-98f8-3e7d927bbd92",
      "last-modified": "Sun, 10 Mar 2019 16:27:33 GMT",
      "opc-content-md5": "Y9kGzFq93i/9X/f+jkmo0Q=="
    },
    "sample2.csv.gz": {
      "etag": "4c92564c-bffb-4f1d-a637-a09a3cff85b8",
      "last-modified": "Sun, 10 Mar 2019 16:27:33 GMT",
      "opc-content-md5": "K0TW745KejkBs9vRuciT2A=="
    }
  }
}

3. ADW/ATPにCREDENTIALを作成

DBMS_CLOUD.CREATE_CREDENTIALプロシージャでADW/ATPにCREDENTIALを
作成します。作り方はこちらの記事やリンク先のマニュアルを参照してね彡(ﾟ)(ﾟ)

4. 外部表の作成(DBMS_CLOUD.CREATE_EXTERNAL_TABLEプロシージャ)

DBMS_CLOUD.CREATE_EXTERNAL_TABLEプロシージャで外部表を作成します。

BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
     table_name      => 'EXT_SAMPLE_CSV_GZ',
     credential_name => 'OCI_CLI_AYS_ATP',
     file_uri_list   => 'https://swiftobjectstorage.region.oraclecloud.com/v1/namespace/bucket-name/sample*.csv.gz',
     format          => '{ "type" : "CSV" , "compression" : "gzip" }' ,
     column_list     => 'ITEM_ID   NUMBER, ' ||
                        'ITEM_NAME VARCHAR2(30)'
   );
END;
/

それぞれのパラメータの意味は下記の通りです。

• table_name：外部表名を指定しています。
• credential_name：上記3. で作成したCREDENTAILを指定しています。
• file_url_list：Object Storage上のファイルURLをワイルドカード(*)で指定しています。URL形式の詳細は "こちら"
• format：幾つか指定方法がありますが、本ケースではJSON形式でtype属性(CSV)とcompression属性(gzip)を指定しています。詳細は "こちら"
• column_list：カラム定義を外部表(EXTERNAL TABLE)形式で指定します。

下記マニュアルも参照やで彡(ﾟ)(ﾟ)

A Packages DBMS_CLOUD and DBMS_CLOUD_ADMIN
CREATE_EXTERNAL_TABLE Procedure
https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/dbmscloud-reference.html#GUID-2AFBEFA4-992E-4F53-96DB-F560084C7DA9

5. 作成した外部表の参照

上記4. で作成した外部表を参照してみます。

SELECT * FROM EXT_SAMPLE_CSV_GZ;

   ITEM_ID ITEM_NAME
---------- ------------------------------
         1 a
         2 b
         3 c
         4 d
         5 e
         6 f

6 rows selected.

Object Storage にアップロードしたCSVファイル(gzip圧縮済)のファイルを参照できたやで！彡(^)(^)

6. まとめ

ADW/ATPはObject Storage にアップロードしたファイルを直接参照できる！

という事は、Object StorageをData Lakeとしてデータを貯めこみながら参照しつつ、
使用頻度が高いデータはAutonomous DB(ADW/ATP)上にロード＆加工して
Data Warehouse, Data Mart的に使えるということ彡(ﾟ)(ﾟ)

皆さん、どんどん活用してね彡(^)(^)