DBMS_CLOUDパッケージAvro、ORCおよびParquet複合型

Avro、ORCおよびParquet複合データ型とOracleデータ型のマッピングについて説明します。

Autonomous Databaseは、次の複合型を含む複合データ型をサポートしています:

  • struct

  • リスト

  • マップ

  • 結合

  • 配列

Avro、ORCまたはParquetのソース・ファイル・タイプを指定すると、ソース・ファイルに複合列が含まれる場合、Autonomous Databaseの問合せは複合列に対してJSONを返します。これにより、問合せ結果の処理が簡素化されます。つまり、Oracleの強力なJSON解析機能をファイル・タイプおよびデータ・タイプのすべてに一貫して使用できます。次の表に、Autonomous Databaseの複合型の形式を示します:

ノート

複合フィールドはVARCHAR2列にマップされ、VARCHAR2のサイズ制限が適用されます。
タイプ Parquet オーク Avro Oracle
List: 値のシーケンス 表示 表示 Array VARCHAR2 (JSON形式)
マップ: 単一キーを持つオブジェクトのリスト マップ マップ マップ VARCHAR2 (JSON形式)
Union: 異なるタイプの値 使用できません。 結合 結合 VARCHAR2 (JSON形式)
Object: 0つ以上のキーと値のペア Struct Struct 記録 VARCHAR2 (JSON形式)

ORC、ParquetまたはAvroソース・ファイルに複合タイプが含まれる場合、これらの共通複合タイプについてJSON出力を問い合せることができます。たとえば、次の例は、複合型を含むORCファイルmovie-info.orcを示しています(ParquetおよびAvroソース・ファイルにも同じ複合型の処理が適用されます)。

次のスキーマがあるmovie-info.orcファイルについて検討してください:

id    int
original_title string
overview       string
poster_path    string
release_date   string
vote_count     int
runtime        int
popularity     double
genres         array<struct<id:int,name:string>

各ムービーは、genresの配列を使用した複数のgenresによって分類されています。genres配列はstructsarrayであり、各項目にはid (int)およびname (string)が含まれます。genres配列は複合タイプとみなされます。次のように、DBMS_CLOUD.CREATE_EXTERNAL_TABLEを使用して、このORCファイルに対する表を作成できます:

BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
        table_name =>'movie_info',
        credential_name =>'OBJ_STORE_CRED',
        file_uri_list =>'https://objectstorage.us-phoenix-1.oraclecloud.com/n/mytenancy/b/movies/o/movie-info.orc',
        format => '{"type":"orc", "schema": "first"}');
END;
/

外部表を作成すると、ORCファイル内のスキーマに基づいて列が自動的に生成されます(AvroまたはParquetを使用している場合も同様です)。この例では、DBMS_CLOUD.CREATE_EXTERNAL_TABLEによって、次のようにデータベースに表が作成されます:

CREATE TABLE "ADMIN"."MOVIE_INFO" 
    ( "ID"
      NUMBER(10,0), 
      "ORIGINAL_TITLE"  VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP", 
      "OVERVIEW"        VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP", 
      "POSTER_PATH"     VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP", 
      "RELEASE_DATE"    VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP", 
      "VOTE_COUNT"      NUMBER(10,0), 
      "RUNTIME"         NUMBER(10,0), 
      "POPULARITY"      BINARY_DOUBLE, 
      "GENRES"          VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP"
    )  DEFAULT COLLATION "USING_NLS_COMP"
    ORGANIZATION EXTERNAL 
     ( TYPE      ORACLE_BIGDATA
       DEFAULT DIRECTORY "DATA_PUMP_DIR"
       ACCESS PARAMETERS
       ( com.oracle.bigdata.credential.name=OBJ_STORE_CRED
         com.oracle.bigdata.fileformat=ORC
   )
       LOCATION
        (
      'https://objectstorage.us-phoenix-1.oraclecloud.com/n/mytenancy/b/movies/o/movie-info.orc'
           )
     )
   REJECT LIMIT UNLIMITED 
   PARALLEL;
 )

これで、ムービー・データを問い合せることができます:

SELECT original_title, release_date, genres 
     FROM movie_info 
     WHERE release_date > '2000'
     ORDER BY original_title;

次の出力が生成されます。


original_title              release_date   genres
(500) Days of Summer        2009           [{"id":3,"name":"Drama"},{"id":6,"name":"Comedy"},{"id":17,"name":"Horror"},{"id":19,"name":"Western"},{"id":18,"name":"War"},{"id":15,"name":"Romance"}]
10,000 BC                   2008           [{"id":6,"name":"Comedy"}]
11:14                       2003           [{"id":9,"name":"Thriller"},{"id":14,"name":"Family"}]
127 Hours                   2010           [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"}]
13 Going on 30              2004           [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"},{"id":18,"name":"War"},{"id":15,"name":"Romance"}]
1408                        2007           [{"id":45,"name":"Sci-Fi"},{"id":6,"name":"Comedy"},{"id":17,"name":"Horror"},{"id":6,"name":"Comedy"},{"id":18,"name":"War"}]

複合型genresがJSON配列として返されることに注意してください。

JSONデータをより有効にするために、OracleのJSONファンクションを使用して列を変換できます。たとえば、JSONの「.」表記法に加え、JSON_TABLEなどのより強力な変換ファンクションを使用できます。

「.」表記法の詳細は、JSONデータに対する単純なドット表記法アクセスを参照してください。

JSON_TABLEの詳細は、SQL/JSONファンクションJSON_TABLEを参照してください。

次の例は、配列の各値を取得し、その値を結果セットの行に変換する表に対する問合せを示しています:
SELECT original_title, release_date, m.genre_name, genres
    FROM movie_info mi,
       JSON_TABLE(mi.genres, '$.name[*]'
        COLUMNS (genre_name VARCHAR2(25) PATH
      '$')                 
                 ) AS m
 WHERE rownum < 10;

JSON_TABLEは、配列の値ごとに行を作成し、外部結合を検討し、structを解析してgenreの名前を抽出します。次の出力が生成されます。


original_title                   release_date         genre_name        genres
(500) Days of Summer             2009                 Drama             [{"id":3,"name":"Drama"},{"id":6,"name":"Comedy"},
                                                                        {"id":17,"name":"Horror"},{"id":19,"name":"Western"},
                                                                        {"id":18,"name":"War"},{"id":15,"name":"Romance"}]
(500) Days of Summer             2009                 Comedy            [{"id":3,"name":"Drama"},{"id":6,"name":"Comedy"},
                                                                        {"id":17,"name":"Horror"},{"id":19,"name":"Western"},
                                                                        {"id":18,"name":"War"},{"id":15,"name":"Romance"}]
(500) Days of Summer             2009                 Horror            [{"id":3,"name":"Drama"},{"id":6,"name":"Comedy"},
                                                                        {"id":17,"name":"Horror"},{"id":19,"name":"Western"},
                                                                        {"id":18,"name":"War"},{"id":15,"name":"Romance"}]
(500) Days of Summer             2009                 Western           [{"id":3,"name":"Drama"},{"id":6,"name":"Comedy"},
                                                                        {"id":17,"name":"Horror"},{"id":19,"name":"Western"},
                                                                        {"id":18,"name":"War"},{"id":15,"name":"Romance"}]
(500) Days of Summer             2009                 War               [{"id":3,"name":"Drama"},{"id":6,"name":"Comedy"},
                                                                        {"id":17,"name":"Horror"},{"id":19,"name":"Western"},
                                                                        {"id":18,"name":"War"},{"id":15,"name":"Romance"}]
(500) Days of Summer             2009                 Romance           [{"id":3,"name":"Drama"},{"id":6,"name":"Comedy"},
                                                                        {"id":17,"name":"Horror"},{"id":19,"name":"Western"},
                                                                        {"id":18,"name":"War"},{"id":15,"name":"Romance"}]
10,000 BC                        2008                 Comedy            [{"id":6,"name":"Comedy"}]
11:14                            2003                 Family            [{"id":9,"name":"Thriller"},{"id":14,"name":"Family"}]
11:14                            2003                 Thriller          [{"id":9,"name":"Thriller"},{"id":14,"name":"Family"}]
127 Hours                        2010                 Comedy            [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"}]
127 Hours                        2010                 Drama             [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"}]
13 Going on 30                   2004                 Romance           [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"},
                                                                        {"id":18,"name":"War"},{"id":15,"name":"Romance"}]
13 Going on 30                   2004                 Comedy            [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"},
                                                                        {"id":18,"name":"War"},{"id":15,"name":"Romance"}]
13 Going on 30                   2004                 War               [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"},
                                                                        {"id":18,"name":"War"},{"id":15,"name":"Romance"}]
13 Going on 30                   2004                 Drama             [{"id":6,"name":"Comedy"},{"id":3,"name":"Drama"},
                                                                        {"id":18,"name":"War"},{"id":15,"name":"Romance"}]