DBMS_CLOUD Avro、ORC 和 Parquet 支持

本节介绍 Autonomous Database 随附的 DBMS_CLOUD Avro、ORC 和 Parquet 支持。

DBMS_CLOUD Avro、ORC 或 Parquet 的程序包格式选项

DBMS_CLOUD 中的 format 参数指定源文件的格式。

指定格式参数的两种方式是：

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) 有关 REGEXP_LIKE 条件的详细信息，请参阅 Oracle Database 19c SQL Language Reference 或 Oracle Database 23ai SQL Language Reference 中的 REGEXP_LIKE Condition 。	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 Package Avro，ORC，and Parquet Complex Types 。

Avro 类型	Oracle 类型
INT	NUMBER(10)
LONG	数字 (19)
箱子	第（1 个）
UTF8 BYTE_ARRAY	RAW(2000)
FLT	BINARY_FLOAT
数据库 L	BINARY_DOUBLE
DECIMAL(p)	NUMBER(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)
ENUM	VARCHAR2( n )，其中：" n " 是 AVRO ENUM 可能值的实际最大长度
DURATION	RAW(2000)
已修复	RAW(2000)
NULL	VARCHAR2(1) 字节

有关使用 Avro 复杂类型的信息，请参见DBMS_CLOUD Package Avro，ORC，and Parquet Complex Types 。

DBMS_CLOUD 将 ORC 程序包与 Oracle 数据类型映射

介绍 ORC 数据类型到 Oracle 数据类型的映射。

有关使用 ORC 复杂类型的信息，请参阅 DBMS_CLOUD Package Avro、ORC 和 Parquet Complex Types 。

ORC 类型	Oracle 类型	更多信息
数组	VARCHAR2( n ) JSON 格式	DBMS_CLOUD 打包 Avro、ORC 和 Parquet 复杂类型
bigint（64 位）	数字 (19)
二进制	BLOB
布尔值（1 位）	第（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 位）	数字 (5)
string	VARCHAR2(4000)
struct	VARCHAR2( n ) JSON 格式	DBMS_CLOUD 打包 Avro、ORC 和 Parquet 复杂类型
时间戳	TIMESTAMP
微型（8 位）	数字 (3)
并集	VARCHAR2( n ) JSON 格式	DBMS_CLOUD 打包 Avro、ORC 和 Parquet 复杂类型
varchar	VARCHAR2( n )

DBMS_CLOUD 程序包参数到 Oracle 数据类型映射

介绍 Parquet 数据类型与 Oracle 数据类型的映射。

注意：

从 Oracle Database 19c 开始，支持复杂类型，例如映射、数组和结构。有关使用 Parquet 复杂类型的信息，请参见DBMS_CLOUD Package Avro，ORC，and Parquet Complex Types 。

参数类型	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 字节）
FLT	BINARY_FLOAT
数据库 L	BINARY_DOUBLE
DECIMAL(p)	NUMBER(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)

有关使用 Parquet 复杂类型的信息，请参见DBMS_CLOUD Package Avro，ORC，and Parquet Complex Types 。

DBMS_CLOUD 打包 Oracle 数据类型到参数映射

介绍 Oracle 数据类型与 Parquet 数据类型的映射。

Oracle 类型	参数类型
BINARY_DOUBLE	数据库 L
BINARY_FLOAT	FLT
DATE	DATE
NUMBER(p,s)	DECIMAL(p,s)
NUMBER(p)	DECIMAL(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 的列。

有关详细信息，请参阅 Oracle Database 19c Database Globalization Support Guide 中的 Date and Time Parameters 或 Oracle Database 23ai Database Globalization Support Guide 以及 Oracle Database 19c Database Globalization Support Guide 中的 NLS Data Dictionary Views 或 Oracle Database 23ai Database Globalization Support Guide 。

DBMS_CLOUD 程序包 Avro、ORC 和 Parquet 复杂类型

介绍将 Avro、ORC 和 Parquet 复杂数据类型映射到 Oracle 数据类型。

Autonomous Database 支持复杂的数据类型，包括以下复杂类型：

• struct

• list

• 地图

• 并集

• 数组

如果指定 Avro、ORC 或 Parquet 源文件类型并且源文件包括复杂列，则 Autonomous Database 查询将为复杂列返回 JSON。这样可以简化查询结果的处理；您可以在文件类型和数据类型中一致地使用 Oracle 强大的 JSON 解析功能。下表显示了 Autonomous Database 中复杂类型的格式：

注意：

复杂字段映射到 VARCHAR2 列，并应用 VARCHAR2 大小限制。

类型	Parquet	非持续性收费	Avro	Oracle
列表：值序列	列出	列出	Array	VARCHAR2（JSON 格式）
映射：具有单键的对象列表	地图	地图	地图	VARCHAR2（JSON 格式）
Union：不同类型的值	不可用	并集	并集	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 或 Oracle Database 23ai JSON Developer's Guide 中的 Simple Dot-Notation Access to JSON Data 。

有关 JSON_TABLE 的信息，请参见 Oracle Database 19c JSON Developer's Guide 或 Oracle Database 23ai JSON Developer's Guide 中的 SQL/JSON Function 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__"	__ 索引 _ 关键字 __	select "__index_key__"	存在前导下划线时，需要使用双引号，这也会保留字符大小写
6Way	"6Way"	6Way	select "6Way"	当存在前导数字位时，需要使用双引号，这也保留了字符大小写
创建、创建或创建等。（任何情况变化）分区、分区、分区等（对于 Oracle 保留字）	“创建”“分区”	创建分区	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 命令查看表的列名。

• 在 Avro、ORC 或 Parquet 列名中使用 Oracle SQL 保留字时，在 SQL 的任意位置引用这些字时，必须始终使用双引号。有关详细信息，请参阅 Oracle Database 23ai SQL Language Reference 或 Oracle Database 23ai SQL Language Reference 中的 Oracle SQL Reserved Words 。

---

标题和版权信息

Copyright ©2023,2024,

Oracle 和/或其关联公司。