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

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.

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 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 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 => '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. 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).

     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 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.

   La llamada DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE daría como resultado 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 el procedimiento CREATE_EXTERNAL_PART_TABLE 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.

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

   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.