DBMS_CLOUD Soporte de Avro, ORC y Parquet
En esta sección se trata el soporte de Avro, ORC y Parquet de DBMS_CLOUD
que se proporciona con Autonomous Database.
Opciones de formato de paquete DBMS_CLOUD para Avro, ORC o Parquet
El argumento de formato de DBMS_CLOUD
especifica el formato de los archivos de origen.
Las dos formas de especificar el argumento de formato son:
format => '{"format_option" : “format_value” }'
Y:
format => json_object('format_option' value 'format_value'))
Ejemplos:
format => json_object('type' VALUE 'CSV')
Para especificar varias opciones de formato, separe los valores con una ",
".
Por ejemplo:
format => json_object('ignoremissingcolumns' value 'true', 'removequotes' value 'true', 'dateformat' value 'YYYY-MM-DD-HH24-MI-SS', 'blankasnull' value 'true')
Opción de formato | Descripción | Sintaxis |
---|---|---|
|
Cuando el valor de Los caracteres "*" y "?" se consideran caracteres comodín cuando el parámetro Los patrones de expresión regular solo están soportados para el nombre de archivo o la ruta de la subcarpeta en los URI y la coincidencia de patrones es idéntica a la realizada por la función Para las tablas externas, esta opción solo está soportada con las tablas que se crean en un archivo de Object Storage. Por ejemplo:
Consulte Condición REGEXP_LIKE en Referencia de lenguaje SQL 19c de Oracle Database o Referencia de lenguaje SQL de Oracle Database 23ai para obtener más información sobre la condición |
Valor por defecto: |
type |
Especifica el tipo de archivo. |
|
|
Cuando el esquema se define en Los nombres de columna coincidirán con los que se encuentran en Avro, ORC o Parquet. Los tipos de datos se convierten de los tipos de datos de Avro, ORC o Parquet a los tipos de datos de Oracle. Se agregan todas las columnas a la tabla. El valor El valor Por defecto: si se especifica Nota: Para los archivos de formato Avro, ORC o Parquet, la opción de formato |
|
Asignación de tipo de datos de Avro a Oracle en el paquete DBMS_CLOUD
Describe la asignación de tipos de datos de Avro a tipos de datos de Oracle.
Note:
Los tipos complejos, como asignaciones, matrices y estructuras, están soportados a partir de Oracle Database 19c. Consulte DBMS_CLOUD Tipos complejos de Avro, ORC y Parquet del paquete.Tipo de Avro | Type Oracle |
---|---|
INT | NUMBER(10) |
LONG | NÚMERO(19) |
Boletín | NÚMERO(1) |
UTF8 BYTE_ARRAY | RAW(2000) |
FLT | BINARY_FLOAT |
DBL | BINARY_DOUBLE |
DECIMAL(p) | NÚMERO(p) |
DECIMAL(p,s) | NUMBER(p,s) |
DATE | DATE |
STRING | VARCHAR2 |
TIME_MILLIS | VARCHAR2(20 BYTES) |
TIME_MICROS | VARCHAR2(20 BYTES) |
TIMESTAMP_MILLIS | TIMESTAMP(3) |
TIMESTAMP_MICROS | TIMESTAMP(6) |
ENUM | VARCHAR2(n) Donde: "n" es la longitud máxima real de los valores posibles de AVRO ENUM |
DURATION | RAW(2000) |
FIXED | RAW(2000) |
NULL | VARCHAR2(1) BYTE |
Asignación de tipo de datos de ORC a Oracle en el paquete DBMS_CLOUD
Describe la asignación de tipos de datos de ORC a tipos de datos de Oracle.
Tipo de ORC | Type Oracle | Más información |
---|---|---|
matriz | Formato JSON VARCHAR2(n) | Tipos complejos de Avro, ORC y Parquet del paquete DBMS_CLOUD |
bigint (64 bits) | NÚMERO(19) | |
binarios | BLOB | |
booleano (1 bit) | NÚMERO(1) | |
carácter | CHAR(n) | |
Fecha | DATE | |
doble | BINARY_DOUBLE | |
float | BINARY_FLOAT | |
int (32 bits) | NUMBER(10) | |
list | Formato JSON VARCHAR2(n) | Tipos complejos de Avro, ORC y Parquet del paquete DBMS_CLOUD |
mapa | Formato JSON VARCHAR2(n) | Tipos complejos de Avro, ORC y Parquet del paquete DBMS_CLOUD |
smallint (16 bits) | NÚMERO(5) | |
Cadena | VARCHAR2(4000) | |
struct | Formato JSON VARCHAR2(n) | Tipos complejos de Avro, ORC y Parquet del paquete DBMS_CLOUD |
registro de hora | TIMESTAMP | |
diminuto (8 bits) | NÚMERO(3) | |
union | Formato JSON VARCHAR2(n) | Tipos complejos de Avro, ORC y Parquet del paquete DBMS_CLOUD |
varchar | VARCHAR2(n) |
Asignación de tipo de datos de Parquet a Oracle en el paquete DBMS_CLOUD
Describe la asignación de tipos de datos de Parquet a tipos de dato de Oracle.
Note:
Los tipos complejos, como asignaciones, matrices y estructuras, están soportados a partir de Oracle Database 19c. Consulte DBMS_CLOUD Tipos complejos Avro, ORC y Parquet del paquete.Tipo de Parquet | Type Oracle |
---|---|
UINT_64 | NÚMERO(20) |
INT_64 | NÚMERO(19) |
UINT_32 | NUMBER(10) |
INT_32 | NUMBER(10) |
UINT_16 | NÚMERO(5) |
INT_16 | NÚMERO(5) |
UINT_8 | NÚMERO(3) |
INT_8 | NÚMERO(3) |
Boletín | NÚMERO(1) |
UTF8 BYTE_ARRAY | VARCHAR2(4000 BYTES) |
FLT | BINARY_FLOAT |
DBL | BINARY_DOUBLE |
DECIMAL(p) | NÚMERO(p) |
DECIMAL(p,s) | NUMBER(p,s) |
DATE | DATE |
STRING | VARCHAR2(4000) |
TIME_MILLIS | VARCHAR2(20 BYTES) |
TIME_MILLIS_UTC | VARCHAR2(20 BYTES) |
TIME_MICROS | VARCHAR2(20 BYTES) |
TIME_MICROS_UTC | VARCHAR2(20 BYTES) |
TIMESTAMP_MILLIS | TIMESTAMP(3) |
TIMESTAMP_MILLIS_UTC | TIMESTAMP(3) |
TIMESTAMP_MICROS | TIMESTAMP(6) |
TIMESTAMP_MICROS_UTC | TIMESTAMP(6) |
TIMESTAMP_NANOS | TIMESTAMP(9) |
DBMS_CLOUD Paquete de asignación de tipo de datos de Oracle a Parquet
Describe la asignación de tipos de datos de Oracle a tipos de datos de Parquet.
Type Oracle | Tipo de Parquet |
---|---|
BINARY_DOUBLE | DBL |
BINARY_FLOAT | FLT |
DATE | DATE |
NUMBER(p,s) | DECIMAL(p,s) |
NÚMERO(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 |
Parámetros de sesión NLS
Los parámetros de sesión de NLS NLS_DATE_FORMAT
, NLS_TIMESTAMP_FORMAT
, NLS_TIMESTAMP_TZ_FORMAT
y NLS_NUMERIC_CHARACTERS
definen cómo se deben mostrar la fecha, el registro de hora, el registro de hora con formato de zona horaria y el separador de radio para el registro de hora con marcador decimal cuando se consulta una tabla con esos tipos de columna.
Además, al exportar datos mediante DBMS_CLOUD.EXPORT_DATA
y especificar la salida de Parquet, Autonomous Database lee los valores de estos parámetros de la tabla NLS_SESSION_PARAMETERS
. Autonomous Database utiliza estos valores para convertir los tipos de dato DATE
o TIMESTAMP
de Oracle en tipos de Parquet.
Los parámetros NLS_SESSION_PARAMETERS
soportan una máscara de formato RR
(especificación de año de dos caracteres).
La máscara de formato RR
para el año no está soportada para estos parámetros al exportar datos a Parquet con DBMS_CLOUD.EXPORT_DATA
. Se genera un error de aplicación si intenta exportar a parquet y NLS_SESSION_PARAMETERS
está definido para utilizar la máscara de formato RR
(el valor por defecto para el formato RR
depende del valor del parámetro NLS_TERRITORY
).
Cuando uno de los parámetros NLS_DATE_FORMAT
, NLS_TIMESTAMP_FORMAT
o NLS_TIMESTAMP_TZ_FORMAT
utiliza la máscara de formato RR
, debe cambiar el valor de formato por el valor soportado para exportar datos a Parquet con DBMS_CLOUD.EXPORT_DATA
. Por ejemplo:
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');
Si se define NLS_DATE_FORMAT
, se aplica a las columnas con el tipo de dato DATE
. Si se define NLS_TIMESTAMP_FORMAT
, se aplica a las columnas con el tipo de dato TIMESTAMP
. Si se define NLS_TIMESTAMP_TZ_FORMAT
, se aplica a las columnas con el tipo de dato TIMESTAMP WITH TIME ZONE
.
Consulte Parámetros de fecha y hora en Oracle Database 19c Database Globalization Support Guide o Oracle Database 23ai Database Globalization Support Guide y NLS Data Dictionary Views en Oracle Database 19c Database Globalization Support Guide o Oracle Database 23ai Database Globalization Support Guide para obtener más información.
Tipos complejos de Avro, ORC y Parquet del paquete DBMS_CLOUD
Describe la asignación de tipos de datos complejos de Avro, ORC y Parquet a tipos de datos de Oracle.
Autonomous Database soporta tipos de datos complejos, incluidos los siguientes tipos complejos:
-
struct
-
list
-
mapa
-
union
-
matriz
Al especificar un tipo de archivo de origen Avro, ORC o Parquet y si el archivo de origen incluye columnas complejas, las consultas de Autonomous Database devuelven JSON para las columnas complejas. Esto simplifica el procesamiento de los resultados de las consultas; puede utilizar las potentes funciones de análisis de JSON de Oracle de forma consistente en los tipos de archivo y los tipos de datos. En la siguiente tabla se muestra el formato de los tipos complejos en Autonomous Database:
Note:
Los campos complejos se asignan a columnasVARCHAR2
y se aplican los límites de tamaño VARCHAR2
.
Tipo | Parquet | ORC | Avro | Oracle |
---|---|---|---|---|
List: secuencia de valores | List | List | Matriz | VARCHAR2 (formato JSON)
|
Map: lista de objetos con clave única | Asignación | Asignación | Asignación | VARCHAR2 (formato JSON)
|
Union: valores de diferente tipo | No Disponible | Unión | Unión | VARCHAR2 (formato JSON)
|
Object: cero o más pares clave-valor | Estruc | Estruc | Registro | VARCHAR2 (formato JSON)
|
Si los archivos de origen de ORC, Parquet o Avro contienen tipos complejos, puede consultar la salida de JSON para estos tipos complejos comunes. Por ejemplo, a continuación se muestra un archivo ORC, movie-info.orc
, con un tipo complejo (la misma gestión de tipos complejos se aplica a los archivos de origen de Parquet y Avro).
Tenga en cuenta el archivo movie-info.orc
con el siguiente esquema:
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>
Observe que cada película se clasifica en varios genres
utilizando una matriz de genres
. La matriz genres
es un valor array
de structs
y cada elemento tiene un valor id
(int
) y un valor name
(string
). La matriz genres
se considera un tipo complejo. Puede crear una tabla sobre este archivo ORC utilizando DBMS_CLOUD.CREATE_EXTERNAL_TABLE
de la siguiente manera:
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;
/
Al crear la tabla externa, la base de datos genera automáticamente las columnas según el esquema del archivo ORC (si utiliza Avro o Parquet, se aplica lo mismo). En este ejemplo, DBMS_CLOUD.CREATE_EXTERNAL_TABLE
crea una tabla en la base de datos de la siguiente manera:
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;
)
Ahora puede consultar los datos de la película:
SELECT original_title, release_date, genres
FROM movie_info
WHERE release_date > '2000'
ORDER BY original_title;
Esto genera la siguiente salida:
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"}]
Observe que el tipo complejo genres
se devuelve como una matriz de JSON.
Para que los datos de JSON sean más útiles, puede transformar la columna mediante las funciones JSON de Oracle. Por ejemplo, puede utilizar la notación JSON ".", así como las funciones de transformación más potentes, como JSON_TABLE
.
Consulte Simple Dot-Notation Access to JSON Data en Oracle Database 19c JSON Developer's Guide o Oracle Database 23ai JSON Developer's Guide para obtener información sobre la notación ".".
Consulte SQL/JSON Function JSON_TABLE en la Oracle Database 19c JSON Developer's Guide o la Oracle Database 23ai JSON Developer's Guide para obtener información sobre 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;
Con JSON_TABLE
se crea una fila para cada valor de la matriz, unión externa, y la estructura se analiza para extraer el nombre del género. Esto genera la siguiente salida:
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"}]
Asignación de nombre de columna de Avro, ORC y Parquet a Oracle en el paquete DBMS_CLOUD
Describe reglas para el método de conversión de los nombres de columna de Avro, ORC y Parquet en nombres de columna de Oracle.
Están soportados los siguientes elementos para los nombres de columna Avro, ORC y Parquet, pero puede que sea necesario utilizar comillas dobles para las referencias de Oracle SQL en tablas externas. Por lo tanto, para facilitar el uso y evitar tener que utilizar comillas dobles al hacer referencia a nombres de columna, si es posible, no utilice lo siguiente en los nombres de columna de Avro, ORC y Parquet:
-
Espacios en blancos embebidos
-
Números iniciales
-
Guiones bajos iniciales
-
Palabras reservadas de Oracle SQL
En la siguiente tabla se muestran varios tipos de nombres de columna de Avro, ORC y Parquet y reglas para utilizar los nombres de columna en los nombres de columna de Oracle en tablas externas.
Nombre de Avro, ORC o Parquet | Nombre de CREATE TABLE | CATÁLOGO de Oracle | SQL válido | Notas: |
---|---|---|---|---|
parte, parte o parte | parte, parte, pieza | PARTE |
|
Oracle pone implícitamente en mayúsculas los nombres de columna que no estén entre comillas |
No orden | "No orden" | No orden | select "Ord No" |
Las comillas dobles son necesarias cuando hay espacios en blanco embebidos, lo que también conserva las mayúsculas/minúsculas de caracteres |
__clave_índice__ | "__clave_índice__" | __clave_índice__ | select "__index_key__" |
Las comillas dobles son necesarias cuando hay un carácter de subrayado inicial, lo que también conserva las mayúsculas/minúsculas de caracteres |
6Way | "6Way" | 6Way | select "6Way" |
Las comillas dobles son necesarias cuando hay un dígito numérico inicial, lo que también conserva las mayúsculas/minúsculas de caracteres |
Create, Create o CREATE, etc. (cualquier variación de mayúsculas/minúsculas), partition, Partition, PARTITION, etc. (para una palabra reservada de Oracle) | "CREAR" "PARTICIÓN" | CREAR PARTICIÓN |
|
Se necesitan comillas dobles para las palabras reservadas de Oracle SQL. Se fuerza su conversión en mayúsculas, pero siempre se deben incluir entre comillas dobles cuando se utilizan en cualquier parte de SQL |
ID de fila, ID de fila, ROWid, etc. (para ID de fila, consulte las notas) | rowid |
|
Para ROWID, cualquier variación combinada o en minúscula de ROWID conserva las mayúsculas o minúsculas y debe estar siempre entre comillas dobles y utilizar las variaciones originales de estas. Debido al conflicto inherente con Oracle ROWID para la tabla, si especifica ROWID en mayúscula, se almacena automáticamente como "ROWID" en minúscula y siempre se debe incluir entre comillas dobles cuando se hace referencia a él. |
Notas:
-
En general, se puede hacer referencia a un nombre de columna de una tabla externa sin comillas dobles.
-
A menos que haya un espacio en blanco embebido, un guion bajo inicial ("_") o un dígito numérico inicial (de "0" a "9") en el nombre de columna, se conservan las mayúsculas/minúsculas originales del nombre de columna y siempre se debe hacer referencia a ellas con comillas dobles y utilizando las mayúsculas, minúsculas o mayúsculas combinadas del nombre de columna Avro, ORC o Parquet.
-
Después de utilizar
DBMS_CLOUD.CREATE_EXTERNAL_TABLE
para crear una tabla externa con el formato especificado comoavro
,orc
oparquet
, utilice el comandoDESCRIBE
en SQL*Plus para ver los nombres de columna de la tabla. -
Cuando se utilizan palabras reservadas de Oracle SQL en los nombres de columna Avro, ORC o Parquet, siempre deben incluir comillas dobles cuando se hace referencia a ellas en cualquier parte de SQL. Consulte Oracle SQL Reserved Words en Oracle Database 23ai SQL Language Reference o Oracle Database 23ai SQL Language Reference para obtener más información.