Documentación de Oracle Cloud Infrastructure

Consulta de datos particionados externos con organización de archivos de origen de formato de carpeta

Utilice DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE para crear una tabla particionada externa y generar la información de partición a partir de la ruta del archivo del almacén de objetos en la nube.

Al crear una tabla externa con archivos de datos de formato de carpeta, tiene dos opciones para especificar los tipos de columnas de partición:

  • Puede especificar manualmente las columnas y sus tipos de dato con el parámetro column_list. Consulte Query External Particted Data with Hive Format Source File Organization para obtener un ejemplo con el parámetro column_list.

  • Puede permitir que DBMS_CLOUD derive las columnas del archivo de datos y sus tipos de información en archivos de datos estructurados, como archivos de datos Avro, ORC y Parquet. En este caso, utilice la opción partition_columns con el parámetro format para proporcionar los nombres de columna y sus tipos de dato para las columnas de partición y no necesita proporcionar los parámetros column_list ni field_list.

Tenga en cuenta los siguientes archivos de origen de ejemplo en Object Store:

.../sales/USA/2020/01/sales1.parquet

.../sales/USA/2020/02/sales2.parquet

Para crear una tabla externa particionada con la ruta de acceso del archivo del almacén de objetos en la nube que defina las particiones de los archivos con este formato de carpeta 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 => 'DEF_CRED_NAME',
    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 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 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 particionada externa encima de los archivos de origen mediante el procedimiento DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE.

    El procedimiento DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE admite archivos particionados externos en los servicios de almacenamiento de objetos en la nube soportados. La credencial es una propiedad de nivel en tabla; los archivos externos deben estar todos en el mismo almacén de objetos en la nube.

    Por ejemplo:

    BEGIN DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE(
    table_name => 'MYSALES',
    credential_name => 'DEF_CRED_NAME',
    file_uri_list => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/sales/*.parquet',
    format =>
        json_object('type' value 'parquet', 'schema' value 'first',
                    'partition_columns' value
                          json_array(
                                json_object('name' value 'country', 'type' value 'varchar2(100)'),
                                json_object('name' value 'year', 'type' value 'number'),
                                json_object('name' value 'month', 'type' value 'varchar2(2)')
                          )
         )
    );
END;
/

    Los parámetros DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE para los archivos de datos estructurados, como para un archivo de datos Parquet, no necesitan los parámetros column_list o field_list. Los nombres de columna y los tipos de dato se derivan para las columnas del primer archivo de parquet que el procedimiento explora (y, por lo tanto, todos los archivos deben tener la misma unidad). La lista de columnas generadas incluye las columnas derivadas del nombre de objeto y estas columnas tienen los tipos de dato especificados con el parámetro partition_columns format.

    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 que se encuentran dentro del archivo, así como las derivadas del nombre del objeto.

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

    • field_list: identifica los campos en los archivos de origen y sus tipos de datos. El valor por defecto es NULL, que significa que los campos y sus tipos del dato están determinados por el parámetro column_list.

      field_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. El parámetro partition_columns format especifica los nombres de las columnas de partición. Consulte Opciones de formato de paquete DBMS_CLOUD para obtener más información.

      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.

    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 Procedimiento CREATE_EXTERNAL_PART_TABLE para obtener información detallada sobre los parámetros.

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

    Si hay filas en los archivos de origen que no coincidan con las opciones de formato que ha especificado, la consulta informa de un error. Puede utilizar parámetros DBMS_CLOUD, como rejectlimit, para suprimir estos errores. Como alternativa, también puede validar la tabla particionada externa que ha creado para ver los mensajes de error y las filas rechazadas de modo que pueda cambiar la opción de formato según corresponda. Consulte Validación de datos externos y Validación de datos externos particionados para más información.

  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 en el almacén de objetos. Por ejemplo, la siguiente consulta solo lee archivos de datos de una partición.

    Por ejemplo:

    SELECT year, month, product, units
FROM SALES WHERE year='2020' AND month='02' AND country='USA'

    Las tablas particionadas externas que cree con DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE incluyen dos columnas invisibles file$path y file$name. Estas columnas ayudan a identificar de qué archivo procede un registro. Consulte Columnas de Metadatos de Tabla Externa para obtener más información.