DBMS_CLOUD Tipos complejos de Avro, ORC y Parquet

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
mostrar
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 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:

Nota

Los campos complejos se asignan a columnas VARCHAR2 y se aplican los límites de tamaño VARCHAR2.

Tipo	Parquet	ORC	Avro	de Oracle
List: secuencia de valores	Mostrar	Mostrar	Matriz	`VARCHAR2` (formato JSON)
Map: lista de objetos con clave única	Asignar	Asignar	Asignar	`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	Grabar	`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>

Tenga en cuenta 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 mejorar la utilidad de los datos de JSON, 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 para obtener información sobre la notación de ".".

Consulte Función SQL/JSON JSON_TABLE para obtener información sobre JSON_TABLE.

En el siguiente ejemplo se muestra una consulta en la tabla que toma cada valor de la matriz y convierte el valor en una fila del juego de resultados:

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

Tema principal: DBMS_CLOUD Soporte de Avro, ORC y Parquet

Documentación de Oracle Cloud Infrastructure