Acerca de las tablas externas con partición de archivos de origen
En Autonomous Database, puede crear tablas externas particionadas a partir de datos particionados de estilo Hive o a partir de datos particionados de carpetas simples almacenados en el almacén de objetos en la nube.
Mediante la partición de archivos de origen, en lugar de proporcionar una especificación de partición completa, el procedimiento deriva la información de partición de la ruta de archivo para determinados patrones de archivo. Por ejemplo, tenga en cuenta las siguientes especificaciones de archivos de datos:
-
Estilo de Hive: por ejemplo:
sales/country=USA/year=2020/month=01/file1.csv
-
Estilo de partición de carpeta simple: por ejemplo:
sales/USA/2020/01/file1.parquet
El uso de uno de estos formatos de partición comunes simplifica en gran medida la creación y gestión de tablas externas particionadas. Además, aunque las columnas de partición no aparezcan en el archivo de datos, se pueden seguir consultando mediante SQL. La partición de datos también mejora el rendimiento de las consultas al reducir drásticamente la cantidad de datos explorados. En este ejemplo, al consultar datos "USA", la consulta puede omitir la exploración de los archivos de otros países.
Datos particionados con formato de Hive en el almacén de objetos en la nube
Hive ofrece un formato de metadatos estándar para motores de procesamiento de big data. Los datos particionados en Cloud Object Store que se generan en formato Hive se representan en formato folder/subfolder
. Por ejemplo, en el almacén de objetos en la nube, un archivo de datos con formato de Hive se almacena de la siguiente forma:
table/partition1=partition1_value/partition2=partition2_value/data_file.csv
Los archivos guardados en formato particionado de Hive proporcionan información de partición en el nombre de ruta del archivo de datos. El nombre de ruta del archivo de datos incluye información sobre el contenido del objeto, incluidos los nombres de columna de partición y los valores de columna de partición (el archivo de datos no incluye las columnas de partición y sus valores asociados).
Por ejemplo, considere una tabla SALES
particionada externa creada a partir de datos de formato de Hive en el almacén de objetos en la nube:
.../sales/country=USA/year=2020/month=01/file1.csv
.../sales/country=USA/year=2020/month=01/file2.csv
.../sales/country=USA/year=2020/month=02/file3.csv
.../sales/country=USA/year=2020/month=03/file1.csv
.../sales/country=FRA/year=2020/month=03/file1.csv
La información de partición de formato de Hive muestra que los archivos de datos de Cloud Object Store están particionados por country
, year
y month
, y los valores de estas columnas de partición también se especifican en el nombre de ruta de formato de Hive para cada archivo de datos (el nombre de ruta incluye valores para las columnas particionadas: country
, year
y month
).
La API utilizará los nombres de columna de la ruta para simplificar la definición de la tabla.
Datos particionados con formato de carpeta simple en el almacén de objetos en la nube
Los datos particionados en el almacén de objetos en la nube que se generan en formato de carpeta se representan en un formato folder/subfolder
, similar a los datos particionados en formato Hive, pero en la información de la ruta se muestran los valores de columna y no se incluyen los nombres de columna. Además, con los datos particionados en formato de carpeta, el orden de partición especificado en el nombre del objeto es significativo y debe coincidir con el orden en las columnas de la tabla.
Por ejemplo, en el almacén de objetos en la nube se almacena un archivo de datos con formato de carpeta de la siguiente forma:
table/partition1_value/partition2_value/*.parquet
La ruta incluye tanto valores de columna de partición, en orden de columna de partición, como archivos de datos. Autonomous Database permite crear una tabla particionada externa a partir de datos de formato de carpeta y puede realizar una consulta mediante las particiones especificadas.
Los archivos guardados en formato particionado de carpeta proporcionan los valores de columna de partición de datos en el nombre de archivo. A diferencia de Hive, las rutas no incluyen el nombre de columna, por lo que se deben proporcionar estos. El orden de las columnas de partición es importante y el orden en el nombre de archivo para los nombres de partición de columna debe coincidir con el orden en el parámetro partition_columns
.
Acerca de la consulta de datos particionados en el almacén de objetos en la nube
Al consultar datos particionados externos en formato Hive, el motor de consultas entiende y utiliza la información de partición del nombre de ruta de archivo. Por ejemplo, piense en una tabla SALES
partición externa donde el archivo de origen, sales/country=USA/year=2020/month=02/file3.csv
en el almacén de objetos incluya los siguientes datos de ventas:
tents, 291
canoes, 22
backpacks, 378
Los valores country
en el nombre de ruta y los valores de período para month
y year
no se especifican como columnas en el archivo de datos. Los valores de la columna de partición se especifican solo en el nombre de ruta con los valores que se muestran: USA, 2020 y 02. Después de crear una tabla con particiones externas con este archivo de datos, puede utilizar las columnas de partición y sus valores al ejecutar una consulta en la tabla con particiones externas.
Por ejemplo:
SELECT year, month, product, units
FROM SALES WHERE year='2020' AND month='02' AND country='USA'
La ventaja de crear una tabla particionada externa con datos generados como datos particionados con formato Hive es que el motor de consulta está optimizado para eliminar particiones de los datos para seleccionar la partición correcta y la consulta solo selecciona datos de una partición y solo necesita buscar un único archivo de datos. Por lo tanto, la consulta solo necesitaría explorar el archivo file3.csv
(/sales/country=USA/year=2020/month=02/file3.csv
). Para grandes cantidades de datos, dicha depuración de particiones puede proporcionar mejoras significativas en el rendimiento.
Mediante las tablas externas estándar de Oracle Database, la columna de partición debe estar disponible como columna en el archivo de datos para utilizarla para consultas o definiciones de partición. Sin la manipulación especial disponible con tablas particionadas externas en Autonomous Database, esto supondría un problema si deseara utilizar datos almacenados en formato Hive en el almacén de objetos en la nube, ya que tendría que volver a generar los archivos de datos para incluir la partición como una columna en el archivo de datos.
Acerca de la creación de tablas externas particiones
Al utilizar datos no estructurados almacenados en formato Hive en el almacén de objetos en la nube y crear una tabla particionada externa, las columnas y sus tipos no se pueden derivar del archivo de origen. Por lo tanto, se deben especificar las columnas y sus tipos de datos con el parámetro column_list
. Para crear las tablas externas particionadas, utilice el procedimiento DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE
para especificar las columnas de partición y sus tipos:
-
La raíz de la lista de archivos se especifica en el nombre de ruta con el parámetro
file_uri_list
. Por ejemplo,http://.../sales/*
-
Los nombres de columna y los tipos de datos se especifican con el parámetro
column_list
. -
La opción
partition_columns
del parámetroformat
especifica las columnas de partición. -
La DLL generada incluye las columnas especificadas en el nombre de ruta.
Para este ejemplo, cuando se crea la tabla externa, las columnas country
, year
y month
se agregan en el parámetro column_list
. La tabla externa se crea con las columnas country
, year
y month
, que no están en los archivos de datos, y las particiones de lista se crean activando la depuración de particiones.
Al utilizar datos estructurados, como los archivos Parquet, Avro u ORC almacenados en formato de carpeta en el almacén de objetos en la nube, se conocen las columnas y sus tipos de datos. No es necesario especificar la lista de columnas como ocurre con los datos no estructurados. Para crear las tablas externas particionadas, utilice el procedimiento DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE
para especificar las columnas de partición y sus tipos:
- La raíz de la lista de archivos se especifica en el nombre de ruta con el parámetro
file_uri_list
. Por ejemplo,http://.../sales/*
- El parámetro
column_list
no es necesario para los archivos estructurados. Si no especifica la lista de columnas, debe definir las columnas de partición y sus tipos de datos al crear la tabla particionada externa. Utilice la opciónpartition_columns
en el parámetroformat
para especificar las columnas de partición y sus tipos de datos. - La DLL generada incluye las columnas especificadas en el nombre de ruta.
Consulte Query External Partited Data with Hive Format Source File Organization y Query External Partited Data with Folder Format Source File Organization para obtener ejemplos completos.
- Partición externa: archivos de origen CSV con carpetas de estilo Hive
Muestra cómo crear tablas particionadas externas con archivos de origen CSV almacenados en el almacén de objetos en la nube en carpetas de estilo Hive. - Partición externa: archivos de origen CSV con carpetas simples
Muestra cómo crear tablas particionadas externas con archivos de origen CSV almacenados en el almacén de objetos en la nube en formato de carpeta simple. - Partición externa: archivos de origen de Parquet con carpetas de estilo Hive
Muestra cómo crear tablas particionadas externas con archivos de origen Parquet almacenados en el almacén de objetos en la nube en carpetas de estilo Hive. - Partición externa: Parquet con carpetas simples
muestra cómo crear tablas particionadas externas con archivos de origen Parquet almacenados en el almacén de objetos en la nube en formato de carpeta simple.
Partición externa: archivos de origen CSV con carpetas de estilo Hive
Muestra cómo crear tablas particionadas externas con archivos de origen CSV almacenados en el almacén de objetos en la nube en carpetas de estilo Hive.
Lista de archivos de origen:
.../sales/country=USA/year=2020/month=01/file1.csv
.../sales/country=USA/year=2020/month=01/file2.csv
.../sales/country=USA/year=2020/month=02/file3.csv
.../sales/country=USA/year=2020/month=03/file1.csv
.../sales/country=FRA/year=2020/month=03/file1.csv
API:
DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE
(
table_name => 'mysales',
credential_name => 'mycredential',
file_uri_list => 'https://objectstorage.us-phoenix-1.oraclecloud.com/.../sales/*.csv',
column_list => 'product varchar2(100), units number, country varchar2(100), year number, month varchar2(2)',
field_list => 'product, units', --[Because country, year and month are not in the file, they are not listed in the field list]
format => '{"type":"csv","partition_columns":["country", "year", "month"]}');
El valor
partition_columns
en el parámetro format
debe coincidir con los nombres de columna encontrados en la ruta (por ejemplo, la columna country
coincide con "country=…
")
Partición externa: archivos de origen CSV con carpetas simples
Muestra cómo crear tablas particionadas externas con archivos de origen CSV almacenados en el almacén de objetos en la nube en formato de carpeta simple.
Lista de archivos de origen:
.../sales/USA/2020/01/file1.csv
.../sales/USA/2020/01/file2.csv
.../sales/USA/2020/02/file3.csv
.../sales/USA/2020/03/file1.csv
.../sales/FRA/2020/03/file1.csv
API:
DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE
(
table_name => 'mysales',
credential_name => 'mycredential',
file_uri_list => 'https://objectstorage.us-phoenix-1.oraclecloud.com/.../sales/*.csv',
column_list => 'product varchar2(100), units number, country varchar2(100), year number, month varchar2(2)',
field_list => 'product, units', --[Because country, year and month are not in the file, they are not listed in the field list]
format => '{"type":"csv","partition_columns":["country", "year", "month"]}');
La llamada de API es la misma que en el ejemplo anterior, pero el orden de
partition_columns
en el parámetro format
es significativo porque el nombre de columna no está en la ruta de archivo.
Partición externa: archivos de origen de Parquet con carpetas de estilo Hive
Muestra cómo crear tablas particionadas externas con archivos de origen Parquet almacenados en el almacén de objetos en la nube en carpetas de estilo Hive.
Lista de archivos de origen:
.../sales/USA/2020/01/file1.parquet
.../sales/USA/2020/01/file2.parquet
.../sales/USA/2020/02/file3.parquet
.../sales/USA/2020/03/file1.parquet
.../sales/FRA/2020/03/file1.parquet
API:
DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE
(
table_name => 'mysales',
credential_name => 'mycredential',
file_uri_list => 'https://objectstorage.us-phoenix-1.oraclecloud.com/.../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)')
)
)
);
El parámetro
column_list
no se ha especificado. Como se muestra, para cada columna de partición, especifique tanto el nombre y el tipo de datos en el parámetro format
partition_columns
.
Partición externa: Parquet con carpetas simples
Muestra cómo crear tablas particionadas externas con archivos de origen Parquet almacenados en el almacén de objetos en la nube en formato de carpeta simple.
Lista de archivos de origen:
.../sales/USA/2020/01/file1.parquet
.../sales/USA/2020/01/file2.parquet
.../sales/USA/2020/02/file3.parquet
.../sales/USA/2020/03/file1.parquet
.../sales/FRA/2020/03/file1.parquet
API:
DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE (
table_name => 'mysales',
credential_name => 'mycredential',
file_uri_list => 'https://objectstorage.us-phoenix-1.oraclecloud.com/.../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)')
)
)
);
El parámetro
column_list
no se ha especificado. Debe incluir el nombre y el tipo de datos para las columnas de partición. Además, el orden de partition_columns
en la cláusula de formato es importante porque el nombre de columna no está en la ruta de acceso del archivo.