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配列はstructsのarrayであり、各項目には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"}]