DBMS_CLOUD Avro、ORCおよびParquetのサポート

この項では、Autonomous AI Databaseで提供されるDBMS_CLOUD Avro、ORCおよびParquetサポートについて説明します。

Avro、ORCまたはParquet用のDBMS_CLOUDパッケージ・フォーマット・オプション

DBMS_CLOUDのフォーマット引数は、ソース・ファイルのフォーマットを指定します。

format引数を指定するには、次の2つの方法があります。

format => '{"format_option" : "format_value" }'

AND

format => json_object('format_option' value 'format_value')

次に例を示します:

format => json_object('type' VALUE 'CSV')

複数のフォーマット・オプションを指定するには、値を","で区切ります。

たとえば:

format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true', 'dateformat' value 'YYYY-MM-DD-HH24-MI-SS', 'blankasnull' value 'true')
書式オプション 説明 構文
regexuri

regexuriの値がTRUEに設定されている場合、クラウドのソース・ファイルURIのファイル名にワイルドカードと正規表現を使用できます。

文字"*"および"?"は、regexuriパラメータがFALSEに設定されている場合、ワイルドカード文字とみなされます。regexuriパラメータがTRUEに設定されている場合、文字"*"および"?"は、指定された正規表現パターンの一部です。

正規表現パターンは、URI内のファイル名またはサブフォルダ・パスでのみサポートされ、パターン一致はREGEXP_LIKE関数によって実行されるものと同じです。ディレクトリ名では正規表現パターンはサポートされていません。

外部表の場合、このオプションは、オブジェクト・ストレージ内のファイルに作成される表でのみサポートされます。

たとえば:

format => JSON_OBJECT('regexuri' value TRUE)

REGEXP_LIKE条件の詳細は、『Oracle Database 19c SQL言語リファレンス』のREGEXP_LIKE条件または『Oracle Database 26ai SQL言語リファレンス』を参照してください。

regexuri: True

デフォルト値:False
type ファイル・タイプを指定します。

type: avro | orc | parquet

schema

スキーマをfirstまたはallに設定した場合、外部表の列とデータ型は、Avro、ORCまたはParquetファイルのメタデータから自動的に導出されます。

列名は、Avro、ORCまたはParquetに含まれるものと一致します。データ型は、Avro、ORCまたはParquetデータ型からOracleデータ型に変換されます。すべての列が表に追加されます。

値firstは、列とそのデータ型を自動生成するためにfile_uri_list内の最初のAvro、ORCまたはParquetファイルのメタデータを使用するよう指定します。すべてのファイルのスキーマが同じ場合は、firstを使用します。

値allは、列とそのデータ型を自動生成するためにfile_uri_list内のすべてのAvro、ORCまたはParquetファイルのメタデータを使用するよう指定します。各ファイルに異なるスキーマが含まれる可能性がある場合は、all (低速)を使用します。

デフォルト: column_listが指定されている場合、schemaの値は、指定されていても無視されます。column_listが指定されていない場合、schemaのデフォルト値はfirstです。

ノート: Avro、ORCまたはParquet形式のファイルの場合は、schemaフォーマット・オプションは使用できなくなり、DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLEプロシージャを使用してパーティション化された外部表についてのcolumn_listパラメータを指定する必要があります。

schema : first | all

DBMS_CLOUDパッケージAvroからOracleデータ型へのマッピング

Avroデータ・タイプからOracleデータ型へのマッピングについて説明します。

ノート:複合タイプ(マップ、配列、構造物など)は、Oracle Database 19c以降でサポートされます。Avro複合タイプの使用方法の詳細は、「DBMS_CLOUDパッケージAvro、ORCおよびParquet複合型」を参照してください。

Avroタイプ Oracleタイプ
INT NUMBER(10)
LONG NUMBER(19)
ブール NUMBER(1)
UTF8バイト配列 RAW(2000)
FLT BINARY_FLOAT
DBL BINARY_DOUBLE
デシマル(p) NUMBER(p)
デシマル(p、s) NUMBER(p,s)
DATE DATE
STRING VARCHAR2
TIME_MILLIS VARCHAR2(20バイト)
TIME_MICROS VARCHAR2(20バイト)
TIMESTAMP_MILLIS タイムスタンプ(3)
TIMESTAMP_MICROS タイムスタンプ(6)
ENUM VARCHAR2(n): "n"は、AVRO ENUMの可能な値の実際の最大長です
DURATION RAW(2000)
FIXED RAW(2000)
NULL VARCHAR2(1)バイト

Avro複合タイプの使用方法の詳細は、「DBMS_CLOUDパッケージAvro、ORCおよびParquet複合型」を参照してください。

DBMS_CLOUDパッケージORCからOracleデータ型へのマッピング

ORCデータ型とOracleデータ型のマッピングについて説明します。

ORC複合タイプの使用方法の詳細は、「DBMS_CLOUDパッケージAvro、ORCおよびParquet複合型」を参照してください。

ORC型 Oracleタイプ その他の情報入手先
配列 VARCHAR2(n) JSON形式 DBMS_CLOUDパッケージのAvro、ORCおよびParquet複合型
bigint (64ビット) NUMBER(19)  
binary BLOB  
boolean (1ビット) NUMBER(1)  
文字 CHAR(n)  
日付 DATE  
倍精度浮動小数点 BINARY_DOUBLE  
浮動小数 BINARY_FLOAT  
int (32ビット) NUMBER(10)  
list VARCHAR2(n) JSON形式 DBMS_CLOUDパッケージAvro、ORCおよびParquet複合型
マップ VARCHAR2(n) JSON形式 DBMS_CLOUDパッケージAvro、ORCおよびParquet複合型
smallint (16ビット) NUMBER(5)  
文字列 VARCHAR2(4000)  
struct VARCHAR2(n) JSON形式 DBMS_CLOUDパッケージAvro、ORCおよびParquet複合型
タイムスタンプ TIMESTAMP  
tinyint (8ビット) NUMBER(3)  
UNION VARCHAR2(n) JSON形式 DBMS_CLOUDパッケージAvro、ORCおよびParquet複合型
varchar VARCHAR2(n)  

DBMS_CLOUDパッケージのParquetからOracleデータ型へのマッピング

Oracleデータ型へのParquetデータ型のマッピングについて説明します。

ノート:複合タイプ(マップ、配列、構造など)は、Oracle Database 19c以降でサポートされます。Parquet複合型の使用の詳細は、「DBMS_CLOUDパッケージのAvro、ORCおよびParquet複合型」を参照してください。

Parquetタイプ Oracleタイプ
UINT_64 NUMBER(20)
INT_64 NUMBER(19)
UINT_32 NUMBER(10)
INT_32 NUMBER(10)
UINT_16 NUMBER(5)
INT_16 NUMBER(5)
UINT_8 NUMBER(3)
INT_8 NUMBER(3)
ブール NUMBER(1)
UTF8バイト配列 VARCHAR2(4000バイト)
FLT BINARY_FLOAT
DBL BINARY_DOUBLE
デシマル(p) NUMBER(p)
デシマル(p、s) NUMBER(p,s)
DATE DATE
STRING VARCHAR2(4000)
TIME_MILLIS VARCHAR2(20バイト)
時間(ミリ秒) VARCHAR2(20バイト)
TIME_MICROS VARCHAR2(20バイト)
時間マイクロ秒 VARCHAR2(20バイト)
TIMESTAMP_MILLIS タイムスタンプ(3)
タイムスタンプ_MILLIS_UTC タイムスタンプ(3)
TIMESTAMP_MICROS タイムスタンプ(6)
タイムスタンプ_マイクロS_UTC タイムスタンプ(6)
タイムスタンプ_ナノ タイムスタンプ(9)

Parquet複合型の使用の詳細は、「DBMS_CLOUDパッケージのAvro、ORCおよびParquet複合型」を参照してください。

DBMS_CLOUDパッケージのOracleデータ型からParquetへのマッピング

Parquetデータ・タイプへのOracleデータ・タイプのマッピングについて説明します。

Oracleタイプ Parquetタイプ
BINARY_DOUBLE DBL
BINARY_FLOAT FLT
DATE DATE
NUMBER(p,s) デシマル(p、s)
NUMBER(p) デシマル(p)
タイムスタンプ(3) TIMESTAMP_MILLIS
タイムスタンプ(3) タイムスタンプ_MILLIS_UTC
タイムスタンプ(6) TIMESTAMP_MICROS
タイムスタンプ(6) タイムスタンプ_マイクロS_UTC
タイムスタンプ(9) タイムスタンプ_ナノ
VARCHAR2(4000) STRING

NLSセッション・パラメータ

NLSセッション・パラメータNLS_DATE_FORMAT、NLS_TIMESTAMP_FORMAT、NLS_TIMESTAMP_TZ_FORMATおよびNLS_NUMERIC_CHARACTERSは、日付、タイムスタンプ、タイムゾーン付きタイムスタンプ書式、および小数点マーカー付きタイムスタンプの基数セパレータを、それらの列タイプの表が問合せされるときにどのように表示するかを定義します。

また、DBMS_CLOUD.EXPORT_DATAを使用してデータをエクスポートし、Parquet出力を指定した場合、Autonomous AI Databaseはこれらのパラメータの値をNLS_SESSION_PARAMETERS表から読み取ります。Autonomous AI Databaseでは、これらの値を使用して、Oracleデータ型DATEまたはTIMESTAMPをParquet型に変換します。

NLS_SESSION_PARAMETERSパラメータは、RR書式マスク(2文字の年の指定)をサポートします。

DBMS_CLOUD.EXPORT_DATAを使用してParquetにデータをエクスポートする場合、これらのパラメータでは、年のRR書式マスクはサポートされていません。parquetにエクスポートしようとして、NLS_SESSION_PARAMETERSがRR書式マスクを使用するように設定されている場合、アプリケーション・エラーが発生します(RR書式のデフォルト値はNLS_TERRITORYパラメータの値によって異なります)。

パラメータNLS_DATE_FORMAT、NLS_TIMESTAMP_FORMATまたはNLS_TIMESTAMP_TZ_FORMATのいずれかがRR書式マスクを使用する場合、DBMS_CLOUD.EXPORT_DATAを使用してデータをParquetにエクスポートするには、書式値をサポートされている値に変更する必要があります。たとえば:

ALTER SESSION SET NLS_DATE_FORMAT = "MM/DD/YYYY";
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF';
ALTER SESSION SET  NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH:MI:SS.FF TZH:TZM';

値を変更した後、NLS_SESSION_PARAMETERSビューを問い合せて変更を検証できます。

SELECT value FROM NLS_SESSION_PARAMETERS
       WHERE parameter IN ('NLS_DATE_FORMAT','NLS_TIMESTAMP_FORMAT','NLS_TIMESTAMP_TZ_FORMAT');

NLS_DATE_FORMATが設定されている場合は、DATEデータ型の列に適用されます。NLS_TIMESTAMP_FORMATが設定されている場合は、TIMESTAMPデータ型の列に適用されます。NLS_TIMESTAMP_TZ_FORMATが設定されている場合は、TIMESTAMP WITH TIME ZONEデータ型の列に適用されます。

詳細は、『Oracle Database 19cデータベース・グローバリゼーション・サポート・ガイド』の日付および時間パラメータ、または『Oracle Database 26aiデータベース・グローバリゼーション・サポート・ガイド』のNLSデータ・ディクショナリ・ビューに関する項、または『Oracle Database 19cデータベース・グローバリゼーション・サポート・ガイド』の「Oracle Database 26aiデータベース・グローバリゼーション・サポート・ガイド」を参照してください。

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

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

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

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

ノート:複合フィールドはVARCHAR2列にマップされ、VARCHAR2のサイズ制限が適用されます。

入力してください Parquet ORC Avro Oracle
List: 値のシーケンス List List 配列 VARCHAR2 (JSON形式)
Map: 単一のキーが付いたオブジェクトのリスト マップ マップ マップ VARCHAR2 (JSON形式)
Union: 異なるタイプの値 連絡不可 UNION 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>

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

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;
 )

これで、movieデータを照会できるようになります:

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などのより強力な変換ファンクションを使用できます。

."表記法の詳細は、Oracle Database 19c JSON開発者ガイドのJSONデータへの単純なドット表記法アクセスまたはOracle Database 26ai JSON開発者ガイドを参照してください。

JSON_TABLEの詳細は、『Oracle Database 19c JSON開発者ガイド』のSQL/JSONファンクションJSON_TABLEまたは『Oracle Database 26ai JSON開発者ガイド』を参照してください。

次の例は、配列の各値を取得し、その値を結果セットの行に変換する表に対する問合せを示しています:

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"}]

DBMS_CLOUDパッケージAvro、ORCおよびParquetからOracle列名へのマッピング

Avro、ORCおよびParquet列名をOracle列名に変換する方法のルールについて説明します。

Avro、ORCおよびParquet列名については次のことがサポートされていますが、外部表におけるOracle SQL参照では二重引用符の使用が必要になる場合があります。したがって、列名の参照時に二重引用符を使用せずに済むように、可能であればAvro、ORCおよびParquet列名で次を使用しないでください:

次の表に、Avro、ORCおよびParquet列名の様々なタイプと、これらの列名を外部表のOracle列名で使用するためのルールを示します。

Avro、ORCまたはParquet名 CREATE TABLE名 Oracleカタログ 有効なSQL ノート
part、PartまたはPART part、Part、PART 分断

select part

select Part

select paRt

select PART

 Oracleでは引用符で囲まない列名を暗黙的に大文字にする。
オーダー番号 「Ord No」 オーダー番号 select "Ord No" 空白が埋め込まれている場合、二重引用符が必要であり、文字大/小文字も保持されます。
__索引キー__ "__index_key__" __索引キー__ select "__index_key__" 二重引用符は先頭にアンダースコアがある場合に必要になり、文字の大/小文字も保持されます。
6Way "6方向" 6Way select "6Way" 先頭に数字がある場合は二重引用符が必要であり、文字の大/小文字も保持されます。
CREATE、CREATE、CREATEなどです。(大/小文字の違いがある) PARTITION、PARTITION、PARTITIONなど(Oracleの予約語の場合は) "CREATE" "PARTITION" パーティションの作成

select "CREATE"

select "PARTITION"

 Oracle SQL予約語を囲む二重引用符が必要です。これらは強制的に大文字になりますが、SQL内の任意の場所で使用する場合は常に二重引用符でエスケープする必要があります。
ROWID、ROWID、ROWIDなど(ROWIDについては、「ノート」を参照) ROWID  

select "rowid"

select "Rowid"

select "ROWid"

select "rowid"

 ROWIDの場合、ROWIDの大文字/小文字混在または小文字のバリエーションは保持されるため、常に二重引用符で囲むことによって元の大/小文字のバリエーションを使用する必要があります。表のOracle ROWIDとの特有の競合のため、大文字のROWIDを指定すると、自動的に小文字のROWIDとして格納され、参照時に常に二重引用符で囲む必要があります。

ノート: