Documentación de Oracle Cloud Infrastructure

Consulta de Tablas Externas con Partición Implícita

En Autonomous AI Database puede crear tablas externas particionadas implícitas a partir de datos particionados de estilo Hive o de datos particionados de carpetas simples almacenados en el almacén de objetos en la nube.

Tema principal: Consulta de Datos Externos con Autonomous AI Database

Acerca de las Tablas Externas con Partición Implícita

En Autonomous AI Database, utilice el procedimiento DBMS_CLOUD.CREATE_EXTERNAL_TABLE para crear tablas externas particionadas implícitas a partir de datos particionados de estilo Hive o de datos particionados de carpetas simples almacenados en el almacén de objetos en la nube.

Al transferir las opciones adecuadas a este procedimiento, las particiones se derivan de los datos de origen. Las tablas externas particionadas soportan la detección en tiempo de ejecución de columnas de partición y sus valores. La detección en tiempo de ejecución de cambios en la estructura del almacén de objetos subyacente, como la adición o eliminación de objetos, simplifica el proceso de mantenimiento al eliminar la necesidad de procedimientos de sincronización adicionales necesarios para DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE. Esto garantiza que los datos estén actualizados en el tiempo de ejecución de la consulta.

Con la partición implícita, la base de datos de IA autónoma determina automáticamente las columnas sobre las que se "particiona" una tabla en función de la estructura de archivos jerárquica del origen de almacenamiento de objetos. No es necesario declarar explícitamente un esquema de partición. La partición implícita proporciona ventajas de rendimiento similares a las de la tabla de particiones sin la necesidad de definir explícitamente una tabla externa particionada mediante el procedimiento DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE.

Para optimizar el tiempo de respuesta de las consultas de juegos de datos muy grandes mediante la partición implícita, puede activar la optimización definiendo strict_column_order en true. El parámetro strict_column_order es una subopción de la opción de formato implicit_partition_config. Esta optimización solo se aplica cuando partition_type está colmado. Consulte Query External Implicit Particted Data with Hive Format Source File Organization para obtener un ejemplo y DBMS_CLOUD Package Format Options para obtener más información sobre los parámetros de formato de partición implícita.

Las tablas externas particionadas implícitas soportan los siguientes estilos de nomenclatura para objetos particionados en el almacén de objetos:

Para obtener más información sobre los formatos de nomenclatura, consulte Acerca de las Tablas Externas con Partición de Archivos de Origen.

Mediante el procedimiento DBMS_CLOUD.CREATE_EXTERNAL_TABLE, las tablas externas particionadas implícitas se pueden crear de las siguientes formas:
  • Optimice la partición implícita. Utilice la opción implicit_partition_config para activar la partición implícita y la optimización del tiempo de consulta transcurrido.

    En este ejemplo, la partición implícita se activa definiendo partition_type en hive. El único valor válido para partition_type es hive. La optimización se activa definiendo setting strict_column_order en true. La opción partition_columns especifica qué columnas se particionan.

    Por ejemplo:

    BEGIN
dbms_cloud.create_external_table (
   table_name        => 'partitions1',
   file_uri_list     => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet',
   column_list       => 'c varchar2(100), y number, total_sales number',
   format            => '{"type":"parquet",
                          "implicit_partition_config":{
                                    "partition_type":"hive",
                                    "strict_column_order":true,
                                    "partition_columns":["org","tenant","dbtype","year","month","day"]
                          }
                          }');
END;
/
  • Definir el tipo de partición en conjunto con una lista determinada de columnas de partición

    En este caso, implicit_partition_type se define en hive y implicit_partition_columns proporciona una lista de columnas de partición.

    Por ejemplo:

    BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'MY_CREDENTIAL',
   file_uri_list     => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet',
   column_list       => 'product varchar2(100), units number, country varchar2(100),year (number), month varchar2(2)',
   format            => '{"type":"parquet",
                          "implicit_partition_type":"hive",
                          "implicit_partition_columns":["country","year","month"]}');
END;
/
  • Definir el tipo de partición en activo sin proporcionar una lista de columnas de partición

    En este caso, implicit_partition_type se define en hive y no se proporciona implicit_partition_columns. Las columnas de partición se detectan automáticamente buscando '=' en la ruta especificada por file_uri_list. column_name está a la izquierda de '=' y el valor está a la derecha. Si no se encuentra un column_name en la ruta de acceso, se devuelve un error.

    Por ejemplo:

    BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'MY_CREDENTIAL',
   file_uri_list     => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet',
   column_list       => 'product varchar2(100), units number, country varchar2(100),year (number), month varchar2(2)',
   format            => '{"type":"parquet", "implicit_partition_type":"hive"');
END;
/
  • Proporcionar una lista de columnas de partición sin especificar el tipo

    En este caso, implicit_partition_type no está definido y implicit_partition_columns proporciona una lista de columnas.

    Por ejemplo:

    BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'MY_CREDENTIAL',
   file_uri_list     => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet',
   column_list       => 'product varchar2(100), units number, country varchar2(100),year (number), month varchar2(2)',
   format            => '{"type":"parquet",
                          "implicit_partition_columns":["country","year","month"]}');
END;
/

Consulte Query External Implicit Particted Data with Hive Format Source File Organization y CREATE_EXTERNAL_TABLE Procedure para obtener más información.

Consulta de datos particionados implícitos externos con organización Hive Format Source File

Utilice el procedimiento DBMS_CLOUD.CREATE_EXTERNAL_TABLE para crear una tabla externa particionada implícita a partir de datos del almacén de objetos generados a partir de datos de Hive.

Los archivos de origen de ejemplo del siguiente ejemplo utilizan este formato de nomenclatura:

OBJBUCKET/<table>/<partcol1>=<value1>/<partcol2>=<value2>/file.parquet

Tenga en cuenta los siguientes archivos de origen de ejemplo:

OBJBUCKET/sales/country=USA/year=2024/month=01/sales-2024-01.parquet

OBJBUCKET/sales/country=USA/year=2024/month=02/sales-2024-02.parquet

OBJBUCKET/sales/country=USA/year=2024/month=02/sales-2024-03.parquet

Para crear una tabla externa particionada implícita con datos almacenados en este formato de Hive de ejemplo, haga lo siguiente:

  1. Almacene las credenciales del almacén de objetos mediante el procedimiento DBMS_CLOUD.CREATE_CREDENTIAL.

    Por ejemplo:

    BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL (
    credential_name => 'MY_CREDENTIAL',
    username => 'adb_user@example.com',
    password => 'password' );
END;
/

    No es necesario crear una credencial para acceder al almacén de objetos de Oracle Cloud Infrastructure si activa las credenciales de entidad de recurso. Consulte Uso de la entidad de recurso para acceder a los Recursos de Oracle Cloud Infrastructure para obtener más información.

    Esta operación almacena las credenciales en la base de datos en un formato cifrado. Puede utilizar cualquier nombre para el nombre de credencial. Tenga en cuenta que este paso solo es necesario una vez, a menos que cambien las credenciales del almacén de objetos. Una vez almacenadas las credenciales, puede utilizar el mismo nombre de credencial para crear tablas externas.

    Consulte Procedimiento CREATE_CREDENTIAL para obtener información sobre los parámetros username y password para diferentes servicios de almacenamiento de objetos.

  2. Cree una tabla externa particionada implícita sobre sus archivos de origen mediante el procedimiento DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE.
    1. Caso 1: Activar la partición implícita.
      En este ejemplo, dado que no se proporciona la opción de formato implicit_partition_columns, las columnas de partición se detectan automáticamente y la partición implícita se activa definiendo la opción de formato implicit_partition_type en hive.

      Sin embargo, si se proporciona una lista de columnas, esas columnas se utilizan como columnas de partición y la base de datos de IA autónoma no intenta detectar las columnas.

      Para detectar las columnas de partición, la base de datos de IA autónoma comienza a buscar desde el principio de la ruta, especificada por file_uri_list, '='. Cuando se encuentra, la parte izquierda de '=' hasta el último '/' se toma como columna (por ejemplo, "país") y la parte derecha de '=', hasta el primer '/' como valor (por ejemplo, "Estados Unidos"). La búsqueda continúa para '=' hasta después de '/' que sigue el primer valor de partición, detectando el segundo '=' en la ruta, y así sucesivamente. 

      BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'MY_CREDENTIAL',
   file_uri_list     => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet',
   column_list       => 'product varchar2(100), units number, country varchar2(100),year (number), month varchar2(2)',
   format            => '{"type":"parquet", "implicit_partition_type":"hive"');
END;
/

      Los parámetros son:

      • table_name: es el nombre de la tabla externa.

      • credential_name: es el nombre de la credencial creada en el paso anterior.

      • file_uri_list: es una lista delimitada por comas de los URI de archivo de origen. Para esta lista existen dos opciones:

        • Especifique una lista delimitada por comas de URI de archivos individuales sin comodines.

        • Especifique un único URI de archivo con comodines, donde los comodines solo pueden ser posteriores a la última barra diagonal "/". Se puede utilizar el carácter "*" como comodín para varios caracteres; el carácter "?" se puede utilizar como comodín para un solo carácter.

        En este ejemplo, namespace-string es el espacio Oracle Cloud Infrastructure Object Storage Namepace, y bucketname es el nombre del cubo. Consulte Descripción de los espacios de nombres de Object Storage para obtener más información.

      • column_list: es una lista delimitada por comas de nombres de columna y tipos del dato para la tabla externa. La lista incluye las columnas del archivo de datos y las derivadas del nombre del objeto (de los nombres de la ruta de acceso del archivo especificada por file_uri_list).

        column_list no es necesario cuando los archivos de datos son archivos estructurados (Parquet, Avro u ORC).

      • format: define las opciones que puede especificar para describir el formato del archivo de origen. La opción implicit_partition_type especifica el tipo de formato de datos como hive.

        Si los datos del archivo de origen están cifrados, descifre los datos especificando la opción de formato encryption. Consulte Descifrar datos al importar desde Object Storage para obtener más información sobre el descifrado de datos.

        Consulte DBMS_CLOUD Package Format Options para obtener más información.

    1. Caso 2: Partición implícita optimizada
      En este ejemplo, la partición implícita se activa especificando la opción de formato implicit_partition_config y la partición implícita optimizada se activa definiendo strict_column_order en true.

      Dado que partition_columns especifica una lista de columnas particionadas, esas columnas se utilizan como columnas de partición y la base de datos de IA autónoma no intenta detectar las columnas. 

      BEGIN
dbms_cloud.create_external_table (
   table_name        => 'mysales',
   credential_name   => 'MY_CREDENTIAL',
   file_uri_list     => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet',
   column_list       => 'product varchar2(100), units number, country varchar2(100),year (number), month varchar2(2)',
   format            => '{"type":"parquet",
                          "implicit_partition_config":{
                                    "partition_type":"hive",
                                    "strict_column_order":true,
                                    "partition_columns":["country","year","month","day"]
                          }
                          }');
END;
/

      Los parámetros de formato son:

      • format: define las opciones que puede especificar para describir el formato del archivo de origen. La opción implicit_partition_config tiene las siguientes subopciones:
        • partition_type

          El único valor aceptado es "hive". El valor predeterminado es la ruta de nombre de archivo que no es de hive. Si no se especifica partition_type, se devuelve un error de cliente.

        • partition_columns

          Lista de columnas particionadas.

        • strict_column_order

          Se define en true para activar la partición implícita optimizada. Establézcalo en true solo para los casos en los que sepa que la ruta del almacén de objetos no cambiará y no faltan prefijos. Defínalo en false u omita esta opción para desactivarlo.

        Si los datos del archivo de origen están cifrados, descifre los datos especificando la opción de formato encryption. Consulte Descifrar datos al importar desde Object Storage para obtener más información sobre el descifrado de datos.

        Consulte DBMS_CLOUD Package Format Options para obtener más información.

    Consulte CREATE_EXTERNAL_TABLE Procedure para obtener información detallada sobre los parámetros. Consulte DBMS_CLOUD URI Formats para obtener más información sobre los servicios de almacenamiento de objetos en la nube soportados.

  3. Ahora puede ejecutar consultas en la tabla particionada externa que ha creado en el paso anterior.

    La base de datos de IA autónoma aprovecha la información de partición de la tabla particionada externa, lo que garantiza que la consulta solo acceda a los archivos de datos relevantes del almacén de objetos.

    Por ejemplo:

    SELECT product, units FROM mysales WHERE year='2024' and month='02'

    Esta sentencia SQL solo consulta los datos de la partición del mes 02 del año 2024.

Tema principal: Consulta de tablas externas con particiones implícitas

Optimizar la planificación de consultas del almacén de objetos con la opción strict_column_order

Cuando una consulta se dirige a una carpeta de almacén de objetos con un gran número de archivos y subcarpetas, la fase de planificación y lista puede convertirse en el costo principal antes de que se escanee realmente cualquier dato. Esto es común con los diseños de carpetas de estilo Hive donde los valores de partición están embebidos en la ruta.

Nota

  • El valor strict_column_order está desactivado por defecto. Activar solo si el diseño de ruta es coherente.

  • Si cambian las convenciones de carpeta (como agregar, eliminar o reordenar columnas de partición), debe actualizar la opción partition_columns y puede que tenga que desactivar esta opción.

Active la opción strict_column_order de la siguiente manera:

Si los datos utilizan rutas particionadas de estilo Hive y las columnas de partición siempre aparecen en un orden fijo y consistente sin segmentos faltantes, active la optimización mediante la siguiente configuración: 

strict_column_order = true  (with partition_type = "hive")

La base de datos puede omitir directorios innecesarios siguiendo el orden de las particiones que ya están definidas. Esto significa que no tiene que enumerar todos los objetos, lo que puede acelerar significativamente el tiempo de planificación de grandes conjuntos de datos.

Notas para el uso de la opción strict_column_order:

  • Las rutas siguen el orden y la nomenclatura de Hive, por ejemplo:

    .../country=US/year=2025/month=09/...

  • Las columnas de partición mantienen un conjunto y una secuencia fijos, sin prefijos que se reordenen ni se salten.

  • Debe optimizar la lista de tiempo de plan para las carpetas que contienen un gran número de objetos.
  • No puede usar esta opción en juegos de datos donde no todos los prefijos están presentes. Por ejemplo, algunas carpetas incluyen year=, mientras que otras comienzan con month=).

Ejemplos: utilice la opción strict_column_order para las particiones de estilo Hive con DBMS_CLOUD.CREATE_EXTERNAL_TABLE

BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
    table_name        => 'sales_xt',
    credential_name   => 'MY_CREDENTIAL',
    file_uri_list     => 'https://objectstorage.example.com/n/tenant/b/lake/o/sales/',
    -- Data is stored as: .../country=US/year=2025/month=09/*.parquet
    column_list       => 'product       VARCHAR2(100),
                          units         NUMBER,
                          amount        NUMBER,
                          country       VARCHAR2(30),
                          year          NUMBER,
                          month         NUMBER',
    format            => '{
      "type": "parquet",
      "implicit_partition_config": {
        "partition_type": "hive",
        "partition_columns": ["country","year","month"],
        "strict_column_order": true
      }
    }'
  );
END;
/

Compruebe que la lógica de planificación evita la enumeración de rutas de nivel superior no relacionadas (por ejemplo, rutas que pertenecen a otros países o años):

  • Ejecute una consulta selectiva que restrinja las particiones iniciales, por ejemplo:
    SELECT COUNT(*) FROM sales_xt WHERE country = ''US'' AND year = 2025;
    con
    strict_column_order = true
    .
  • Si no se cumplen las suposiciones de diseño, desactive la opción y vuelva a intentarlo.

Consultar datos de estilo no de Hive particionados implícitos externos

Utilice el procedimiento DBMS_CLOUD.CREATE_EXTERNAL_TABLE para crear una tabla externa particionada implícita a partir de datos del almacenamiento de objetos que se hayan generado a partir de datos que no sean de Hive.

Los archivos de origen de ejemplo del siguiente ejemplo utilizan este formato de nomenclatura:

OBJBUCKET/<table>/<value1>/<value2>/file.parquet

Tenga en cuenta los siguientes archivos de origen de ejemplo:

OBJBUCKET/sales/USA/2024/01/sales-2024-01.parquet

OBJBUCKET/sales/USA/2024/01/sales-2024-02.parquet

OBJBUCKET/sales/USA/2024/01/sales-2024-03.parquet

Para crear una tabla externa particionada implícita con datos almacenados en este formato de Hive de ejemplo, haga lo siguiente:

  1. Almacene las credenciales del almacén de objetos mediante el procedimiento DBMS_CLOUD.CREATE_CREDENTIAL.

    Por ejemplo:

    BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL (
    credential_name => 'MY_CREDENTIAL',
    username => 'adb_user@example.com',
    password => 'password' );
END;
/

    No es necesario crear una credencial para acceder al almacén de objetos de Oracle Cloud Infrastructure si activa las credenciales de entidad de recurso. Consulte Uso de la entidad de recurso para acceder a los Recursos de Oracle Cloud Infrastructure para obtener más información.

    Esta operación almacena las credenciales en la base de datos en un formato cifrado. Puede utilizar cualquier nombre para el nombre de credencial. Tenga en cuenta que este paso solo es necesario una vez, a menos que cambien las credenciales del almacén de objetos. Una vez almacenadas las credenciales, puede utilizar el mismo nombre de credencial para crear tablas externas.

    Consulte Procedimiento CREATE_CREDENTIAL para obtener información sobre los parámetros username y password para diferentes servicios de almacenamiento de objetos.

  2. Cree una tabla externa particionada implícita sobre sus archivos de origen mediante el procedimiento DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE.
    En este ejemplo, la partición implícita se activa al proporcionar las columnas de partición con la opción de formato implicit_partition_columns. Puesto que el nombre de carpeta no incluye las columnas de partición, los valores de partición de la ruta de acceso, especificados por file_uri_list, solo se pueden detectar si se proporciona una lista explícita de columnas con la opción de formato implicit_partition_columns. Para detectar los valores de columna respectivos en la ruta, el orden de las columnas debe ser el mismo que el orden de los valores.

    Las columnas de partición se detectan automáticamente. 
    BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
   table_name        => 'mysales',
   credential_name   => 'MY_CREDENTIAL',
   file_uri_list     => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet',
   column_list       => 'product varchar2(100), units number, country varchar2(100),year (number), month varchar2(2)',
   format            => '{"type":"parquet", "implicit_partition_columns":["country","year","month"]}');
END;
/

    Los parámetros son:

    • table_name: es el nombre de la tabla externa.

    • credential_name: es el nombre de la credencial creada en el paso anterior.

    • file_uri_list: es una lista delimitada por comas de los URI de archivo de origen. Para esta lista existen dos opciones:

      • Especifique una lista delimitada por comas de URI de archivos individuales sin comodines.

      • Especifique un único URI de archivo con comodines, donde los comodines solo pueden ser posteriores a la última barra diagonal "/". Se puede utilizar el carácter "*" como comodín para varios caracteres; el carácter "?" se puede utilizar como comodín para un solo carácter.

    • column_list: es una lista delimitada por comas de nombres de columna y tipos del dato para la tabla externa. La lista incluye las columnas del archivo de datos y las derivadas del nombre del objeto (de los nombres de la ruta de acceso del archivo especificada por file_uri_list).

      column_list no es necesario cuando los archivos de datos son archivos estructurados (Parquet, Avro u ORC).

    • format: define las opciones que puede especificar para describir el formato del archivo de origen. La opción implicit_partition_type no está definida. Dado que se proporciona implicit_partition_columns, el tipo se detecta automáticamente como no activo.

      Si los datos del archivo de origen están cifrados, descifre los datos especificando la opción de formato encryption. Consulte Descifrar datos al importar desde Object Storage para obtener más información sobre el descifrado de datos.

      Consulte DBMS_CLOUD Package Format Options para obtener más información.

    En este ejemplo, namespace-string es el espacio Oracle Cloud Infrastructure Object Storage Namepace, y bucketname es el nombre del cubo. Consulte Descripción de los espacios de nombres de Object Storage para obtener más información.

    Consulte CREATE_EXTERNAL_TABLE Procedure para obtener información detallada sobre los parámetros.

    Consulte DBMS_CLOUD URI Formats para obtener más información sobre los servicios de almacenamiento de objetos en la nube soportados.

  3. Ahora puede ejecutar consultas en la tabla particionada externa que ha creado en el paso anterior.

    La base de datos de IA autónoma aprovecha la información de partición de la tabla particionada externa, lo que garantiza que la consulta solo acceda a los archivos de datos relevantes del almacén de objetos.

    Por ejemplo:

    SELECT product, units FROM mysales WHERE year='2024'

    Esta sentencia SQL solo consulta los datos de la partición para el año 2024.