DBMS_CLOUD Avro、ORC 和 Parquet 支援

本節涵蓋 Autonomous Database 隨附的 DBMS_CLOUD Avro、ORC 和 Parquet 支援。

DBMS_CLOUD Avro、ORC 或 Parquet 的套裝程式格式選項

DBMS_CLOUD 中的格式引數指定來源檔案的格式。

指定格式引數的兩種方式為：

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

且:

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) 請參閱 Oracle Database 19c SQL Language Reference 中的 REGEXP_LIKE Criteria 或 Oracle Database 23ai SQL Language Reference ，瞭解 REGEXP_LIKE 條件的詳細資訊。	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 開始，支援複雜類型，例如地圖、陣列和結構。請參閱 DBMS_CLOUD Package Avro，ORC，and Parquet Complex Types ，以取得使用 Avro 複雜類型的相關資訊。

Avro 類型	Oracle 類型
INT	NUMBER(10)
LONG	數字 (19)
繁體中文	數字 (1)
UTF8 BYTE_ARRAY	RAW(2000)
公升	BINARY_FLOAT
資料庫	BINARY_DOUBLE
十進位 (p)	數字 (p)
DECIMAL (p,s)	NUMBER (p,s)
DATE	DATE
STRING	VARCHAR2
TIME_MILLIS	VARCHAR2 (20 位元組)
TIME_MICROS	VARCHAR2 (20 位元組)
TIMESTAMP_MILLIS	TIMESTAMP(3)
TIMESTAMP_MICROS	TIMESTAMP(6)
數字	VARCHAR2 ( n) 其中：" n " 是 AVRO ENUM 可能值的實際長度上限
DURATION	RAW(2000)
已修正	RAW(2000)
NULL	VARCHAR2 (1) 位元組

請參閱 DBMS_CLOUD Package Avro，ORC，and Parquet Complex Types ，以取得使用 Avro 複雜類型的相關資訊。

DBMS_CLOUD 將 ORC 封裝至 Oracle 資料類型對應

描述 ORC 資料類型與 Oracle 資料類型的對應。

如需有關使用 ORC 複雜類型的資訊，請參閱 DBMS_CLOUD Package Avro，ORC，and Parquet Complex Types 。

ORC 類型	Oracle 類型	其他資訊
陣列	VARCHAR2 ( n) JSON 格式	DBMS_CLOUD 套件 Avro、ORC 及 Parquet 複雜類型
bigint (64 位元)	數字 (19)
二進位	BLOB
布林值 (1 位元)	數字 (1)
字元	字元 (n)
日期	DATE
雙精準數	BINARY_DOUBLE
浮點數	BINARY_FLOAT
int (32 位元)	NUMBER(10)
清單	VARCHAR2 ( n) JSON 格式	DBMS_CLOUD 套件 Avro、ORC 及 Parquet 複雜類型
對應	VARCHAR2 ( n) JSON 格式	DBMS_CLOUD 套件 Avro、ORC 及 Parquet 複雜類型
smallint (16 位元)	數字 (5)
字串	VARCHAR2(4000)
struct	VARCHAR2 ( n) JSON 格式	DBMS_CLOUD 套件 Avro、ORC 及 Parquet 複雜類型
時戳	TIMESTAMP
tinyint (8 位元)	數字 (3)
聯集	VARCHAR2 ( n) JSON 格式	DBMS_CLOUD 套件 Avro、ORC 及 Parquet 複雜類型
varchar	VARCHAR2 ( n)

DBMS_CLOUD 套件 Parquet 與 Oracle 資料類型對應

描述 Parquet 資料類型與 Oracle 資料類型的對應。

附註：

從 Oracle Database 19c 開始，支援複雜類型，例如地圖、陣列和結構。請參閱 DBMS_CLOUD Package Avro、ORC 和 Parquet Complex Types ，瞭解使用 Parquet 複雜類型的相關資訊。

宴會類型	Oracle 類型
UINT_64	數字 (20)
INT_64	數字 (19)
UINT_32	NUMBER(10)
INT_32	NUMBER(10)
UINT_16	數字 (5)
INT_16	數字 (5)
UINT_8	數字 (3)
INT_8	數字 (3)
繁體中文	數字 (1)
UTF8 BYTE_ARRAY	VARCHAR2 (4000 位元組)
公升	BINARY_FLOAT
資料庫	BINARY_DOUBLE
十進位 (p)	數字 (p)
DECIMAL (p,s)	NUMBER (p,s)
DATE	DATE
STRING	VARCHAR2(4000)
TIME_MILLIS	VARCHAR2 (20 位元組)
TIME_MILLIS_UTC	VARCHAR2 (20 位元組)
TIME_MICROS	VARCHAR2 (20 位元組)
TIME_MICROS_UTC	VARCHAR2 (20 位元組)
TIMESTAMP_MILLIS	TIMESTAMP(3)
TIMESTAMP_MILLIS_UTC	TIMESTAMP(3)
TIMESTAMP_MICROS	TIMESTAMP(6)
TIMESTAMP_MICROS_UTC	TIMESTAMP(6)
TIMESTAMP_NANOS	TIMESTAMP(9)

請參閱 DBMS_CLOUD Package Avro、ORC 和 Parquet Complex Types ，瞭解使用 Parquet 複雜類型的相關資訊。

DBMS_CLOUD 將 Oracle 資料類型封裝至 Parquet 對應

描述 Oracle 資料類型與 Parquet 資料類型的對應。

Oracle 類型	宴會類型
BINARY_DOUBLE	資料庫
BINARY_FLOAT	公升
DATE	DATE
NUMBER (p,s)	DECIMAL (p,s)
數字 (p)	十進位 (p)
TIMESTAMP(3)	TIMESTAMP_MILLIS
TIMESTAMP(3)	TIMESTAMP_MILLIS_UTC
TIMESTAMP(6)	TIMESTAMP_MICROS
TIMESTAMP(6)	TIMESTAMP_MICROS_UTC
TIMESTAMP(9)	TIMESTAMP_NANOS
VARCHAR2(4000)	STRING

NLS 階段作業參數

查詢含有這些資料欄類型的表格時，NLS 階段作業參數 NLS_DATE_FORMAT、NLS_TIMESTAMP_FORMAT、NLS_TIMESTAMP_TZ_FORMAT 和 NLS_NUMERIC_CHARACTERS 會定義日期、時戳、時區格式的時戳，以及含小數標記之時戳的基數分隔符號。

此外，當您使用 DBMS_CLOUD.EXPORT_DATA 匯出資料並指定 Parquet 輸出時，Autonomous Database 會從 NLS_SESSION_PARAMETERS 表格讀取這些參數的值。Autonomous Database 使用這些值將 Oracle 資料類型 DATE 或 TIMESTAMP 轉換為 Parquet 類型。

NLS_SESSION_PARAMETERS 參數支援 RR 格式遮罩 (兩個字元年份設定)。

使用 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 資料類型的資料欄。

See Date and Time Parameters in Oracle Database 19c Database Globalization Support Guide or Oracle Database 23ai Database Globalization Support Guide and NLS Data Dictionary Views in Oracle Database 19c Database Globalization Support Guide or Oracle Database 23ai Database Globalization Support Guide for more information.

DBMS_CLOUD 套件 Avro、ORC 及 Parquet 複雜類型

描述 Avro、ORC 和 Parquet 複雜資料類型與 Oracle 資料類型的對應。

Autonomous Database 支援複雜資料類型，包括下列複雜類型：

• struct

• 清單

• 對應

• 聯集

• 陣列

當您指定 Avro、ORC 或 Parquet 的來源檔案類型，且來源檔案包含複雜資料欄時，Autonomous Database 查詢會傳回複雜資料欄的 JSON。這可簡化查詢結果的處理；您可以跨檔案類型和資料類型一致地使用 Oracle 的強大 JSON 剖析功能。下表顯示 Autonomous Database 中複雜類型的格式：

附註：

複雜欄位會對應至 VARCHAR2 資料欄，並套用 VARCHAR2 大小限制。

Type	Parquet	ORC	Avro	Oracle
清單：值順序	列出	列出	陣列	VARCHAR2 (JSON 格式)
對應：具有單一索引鍵的物件清單	對應	對應	對應	VARCHAR2 (JSON 格式)
聯集：不同類型的值	無法使用	聯集	聯集	VARCHAR2 (JSON 格式)
物件：零或多個索引鍵 - 值組	結構	結構	記錄	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。

請參閱 Oracle Database 19c JSON Developer's Guide 中的 Simple Dot-Notation Access to JSON Data 或 Oracle Database 23ai JSON Developer's Guide ，瞭解 "." 表示法的相關資訊。

請參閱 Oracle Database 19c JSON Developer's Guide 中的 SQL/JSON Function JSON_TABLE 或 Oracle Database 23ai JSON Developer's Guide ，瞭解 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 會為陣列的每個值建立一個資料列、認為外部結合，並剖析結構以擷取基因的名稱。這會產生下列輸出：

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 資料欄名稱中使用下列項目：

• 內嵌空白

• 前置數字

• 前置底線

• Oracle SQL 保留字

下表顯示各種類型的 Avro、ORC 和 Parquet 資料欄名稱，以及在外部表格的 Oracle 資料欄名稱中使用資料欄名稱的規則。

Avro、ORC 或 Parquet 名稱	CREATE TABLE 名稱	Oracle 目錄	有效的 SQL	備註
零件、零件或零件	零件、零件、零件	部分	select part select Part select paRt select PART	Oracle 以隱含方式將未加引號的資料欄名稱大寫
訂單編號	「訂單編號」	訂單編號	select "Ord No"	內嵌空白時需要雙引號，同時保留字元大小寫
__index_key__	中文 _ English	__index_key__	select "__index_key__"	有前置底線時需要雙引號，同時保留字元大小寫
6Way	"6Way"	6Way	select "6Way"	有前置數字時，必須使用雙引號，同時會保留字元大小寫
CREATE、CREATE 或 CREATE 等等。(任何情況變化) 分割區、分割區、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"，而且參照時必須一律以雙引號括住。

注意事項：

• 一般而言，外部表格中的資料欄名稱可以參照而不使用雙引號。

• 除非內嵌空白、前導底線 ("_") 或前導數字 ("0" 到 "9") 在資料欄名稱中，否則會保留資料欄名稱的原始大小寫，而且必須一律使用雙引號來參照，並使用 Avro、ORC 或 Parquet 資料欄名稱的原始大小寫 (大寫、小寫或混合大小寫)。

• 使用 DBMS_CLOUD.CREATE_EXTERNAL_TABLE 以指定為 avro、orc 或 parquet 的格式建立外部表格之後，請使用 SQL*Plus 中的 DESCRIBE 命令來檢視表格的資料欄名稱。

• 當 Oracle SQL 保留字用於 Avro、ORC 或 Parquet 資料欄名稱時，在 SQL 中的任何位置參照時，它們必須一律以雙引號括住。請參閱 Oracle Database 23ai SQL Language Reference 中的 Oracle SQL Reserved Words 或 Oracle Database 23ai SQL Language Reference ，瞭解詳細資訊。

---

標題與著作權資訊

Copyright ©2023,2024,

Oracle 和 (或) 其關係企業。