Documentación de Oracle Cloud Infrastructure

Consulta de datos particionados externos con organización de archivo de origen de formato Hive

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

Tenga en cuenta los siguientes archivos de origen de ejemplo en el almacén de objetos:

custsales/month=2019-01/custsales-2019-01.csv

custsales/month=2019-02/custsales-2019-02.csv

custsales/month=2019-03/custsales-2019-03.csv

Con esta nomenclatura, los valores de month se capturan en el nombre del objeto.

Para crear una tabla externa particionada con datos almacenados en este formato de Hive de ejemplo, realice lo siguiente:

  1. Store 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;
/

    La creación de una credencial para acceder al almacén de objetos de Oracle Cloud Infrastructure no es necesaria si activa las credenciales de la entidad de recurso. Consulte Uso de la principal de recurso para acceder a los recursos de Oracle Cloud Infrastructure para obtener más información.

    Con esta operación se almacenan 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. Creación de una tabla particionada externa sobre los archivos de origen mediante el procedimiento DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE.

    El procedimiento DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE soporta archivos externos en los servicios de almacenamiento de objetos en la nube soportados. La credencial es una propiedad de nivel de 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 => 'sales_sample',
        CREDENTIAL_NAME => 'DEF_CRED_NAME',
        FILE_URI_LIST => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_landing/o/sales_sample/*.parquet',
        FORMAT => '{"type":"parquet", "schema": "first","partition_columns":[{"name":"month","type":"varchar2(100)"}]}');
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. Hay dos opciones para esta lista:

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

      • Especifique un URI de archivo único con comodines, donde los comodines solo pueden estar después de la última barra "/". 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 de datos para la tabla externa. La lista incluye las columnas dentro del archivo de datos y las derivadas del nombre del objeto (de los nombres de la ruta del archivo).

      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. El parámetro partition_columns format especifica los nombres de las columnas de partición.

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

      Para obtener más información, consulte DBMS_CLOUD Package Format Options.

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

    La llamada DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE generaría la siguiente definición de tabla: 

    CREATE TABLE "ADMIN"."SALES_SAMPLE"
   ( "DAY_ID" TIMESTAMP (6),
      "GENRE_ID" NUMBER(19,0),
      "MOVIE_ID" NUMBER(19,0),
      "CUST_ID" NUMBER(19,0),
      "APP" VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
      "DEVICE" VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
      "OS" VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
      "PAYMENT_METHOD" VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
      "LIST_PRICE" BINARY_DOUBLE,
      "DISCOUNT_TYPE" VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
      "DISCOUNT_PERCENT" BINARY_DOUBLE,
      "ACTUAL_PRICE" BINARY_DOUBLE,
      "MONTH" VARCHAR2(100 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.fileformat=parquet
com.oracle.bigdata.filename.columns=["month"]
com.oracle.bigdata.file_uri_list="https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_landing/o/sales_sample/*.parquet"
com.oracle.bigdata.credential.schema="ADMIN"
com.oracle.bigdata.credential.name=CRED_OCI
com.oracle.bigdata.trimspaces=notrim
  )
    )
   REJECT LIMIT 0
  PARTITION BY LIST ("MONTH")
 (PARTITION "P1" VALUES (('2019-01'))
      LOCATION
       ( 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_landing/o/sales_sample/month=2019-01/*.parquet'
       ),
 PARTITION "P2" VALUES (('2019-02'))
      LOCATION
       ( 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_landing/o/sales_sample/month=2019-02/*.parquet'
       ))
  PARALLEL ;

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

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

  3. A continuación puede ejecutar consultas en la tabla particionada externa que ha creado en el paso anterior.

    Autonomous Database utiliza 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 movie_id, month FROM sales WHERE month='2019-02'

    Las tablas particionadas externas que crea con DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE incluyen dos columnas invisibles: file$path y file$name. Estas columnas ayudan a identificar el archivo del que procede un registro. Consulte Columnas de metadatos de tablas externas para obtener más información.

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 los 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 las opciones de formato según corresponda. Consulte Validación de datos externos y Validación de datos particionados externos para obtener más información.