Documentación de Oracle Cloud Infrastructure

Tipos complejos de Avro, ORC y Parquet del paquete DBMS_CLOUD

Describe la asignación de tipos de dato complejos de Avro, ORC y Parquet a tipos de dato de Oracle.

Autonomous Database soporta tipos de datos complejos, incluidos los siguientes tipos complejos:

  • struct

  • lista

  • mapa

  • union

  • matriz

Al especificar un tipo de archivo de origen de Avro, ORC o Parquet y 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 coherente en los tipos de archivo y los tipos de dato. 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 de VARCHAR2.
Tipo Parquet ORC Avro Oracle
Lista: secuencia de valores Lista Lista Matriz VARCHAR2 (formato JSON)
Mapa: lista de objetos con una sola clave Mapa Mapa Mapa VARCHAR2 (formato JSON)
Unión: valores de diferentes tipos No Disponible Sindicato Sindicato VARCHAR2 (formato JSON)
Objeto: cero o más pares clave-valor Estruc Estruc Registro VARCHAR2 (formato JSON)

Si los archivos de origen 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 (se aplica el mismo manejo de tipo complejo para los archivos de origen Parquet y Avro).

Considere 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 por varios genres mediante una matriz de genres. La matriz genres es un array de structs y cada elemento tiene un id (int) y un name (string). La matriz genres se considera un tipo complejo. Puede crear una tabla a través de este archivo ORC mediante DBMS_CLOUD.CREATE_EXTERNAL_TABLE de la siguiente forma: 

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 basadas en el esquema del archivo ORC (si utiliza Avro o Parquet, se aplica lo mismo). Para este ejemplo, DBMS_CLOUD.CREATE_EXTERNAL_TABLE crea una tabla en la base de datos de la siguiente forma: 

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;

De este modo, se produce 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 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 Acceso simple de notación de puntos a datos JSON para obtener información sobre la notación ".".

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

El siguiente ejemplo muestra una consulta en la tabla que toma cada valor de la matriz y convierte el valor en una fila en el 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;

JSON_TABLE crea una fila para cada valor de la matriz, piensa en la unión externa y la estructura se analiza para extraer el nombre del género. De este modo, se produce 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"}]