A propos des tables externes avec partitionnement de fichier source
Sur Autonomous Database, vous pouvez créer des tables externes partitionnées à partir de données partitionnées de style Hive ou de données partitionnées de dossier simples stockées dans votre banque d'objets cloud.
En utilisant le partitionnement de fichier source, au lieu de fournir une spécification de partition complète, la procédure dérive les informations de partitionnement du chemin d'accès à certains modèles de fichier. Par exemple, supposons ce qui suit :
-
Style Hive : par exemple :
sales/country=USA/year=2020/month=01/file1.csv
-
Style de partitionnement de dossier simple : par exemple :
sales/USA/2020/01/file1.parquet
L'utilisation de l'un de ces formats de partitionnement communs simplifie considérablement la création et la gestion des tables externes partitionnées. En outre, même si les colonnes de partition peuvent ne pas apparaître dans le fichier de données, elles peuvent toujours être interrogées à l'aide de SQL. Le partitionnement des données améliore également les performances des requêtes en réduisant considérablement la quantité de données analysées. Dans cet exemple, lorsque vous interrogez les données 'USA', la requête peut ignorer l'analyse des fichiers pour d'autres pays.
Format Hive - Données partitionnées dans la banque d'objets cloud
Hive offre un format de métadonnées standard pour les moteurs de traitement du Big Data. Les données partitionnées dans la banque d'objets cloud générées au format Hive sont représentées au format folder/subfolder
. Par exemple, sur la banque d'objets cloud, un fichier de données au format Hive est stocké comme suit :
table/partition1=partition1_value/partition2=partition2_value/data_file.csv
Les fichiers enregistrés au format partitionné Hive fournissent des informations de partition dans le nom du chemin du fichier de données. Le nom du chemin du fichier de données inclut des informations sur le contenu de l'objet, y compris les noms de colonne de partition et les valeurs de colonne de partition (le fichier de données n'inclut pas les colonnes de partition et leurs valeurs associées).
Par exemple, prenons une table SALES
partitionnée externe créée à partir de données au format Hive sur la banque d'objets cloud :
.../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
Les informations de partition au format Hive indiquent que les fichiers de données de la banque d'objets cloud sont partitionnés par country
, year
et month
, et que les valeurs de ces colonnes de partition sont également indiquées dans le nom de chemin au format Hive de chaque fichier de données (le nom de chemin inclut les valeurs des colonnes partitionnées : country
, year
et month
).
Les noms de colonne du chemin seront utilisés par l'API pour simplifier la définition de la table.
Format de dossier simple - Données partitionnées dans la banque d'objets cloud
Les données partitionnées dans la banque d'objets cloud générées au format dossier sont représentées au format folder/subfolder
, comme les données partitionnées au format Hive, mais les informations du chemin indiquent les valeurs de colonne et n'incluent pas les noms de colonne. En outre, avec les données partitionnées au format dossier, l'ordre de partition spécifié dans le nom d'objet est significatif et doit correspondre à l'ordre dans les colonnes de la table.
Par exemple, sur la banque d'objets cloud, un fichier de données au format dossier est stocké comme suit :
table/partition1_value/partition2_value/*.parquet
Le chemin inclut les valeurs de colonne de partition, dans l'ordre des colonnes de partition, et les fichiers de données. Autonomous Database vous permet de créer une table partitionnée externe à partir de données de format de dossier et vous pouvez effectuer une requête à l'aide des partitions indiquées.
Les fichiers enregistrés au format partitionné par dossier fournissent les valeurs de colonne de partition de données dans le nom de fichier. Contrairement à Hive, les chemins d'accès n'incluent pas le nom de colonne. Par conséquent, les noms de colonne doivent être fournis. L'ordre des colonnes de partition est important et l'ordre du nom de fichier des noms de partition de colonne doit correspondre à l'ordre du paramètre partition_columns
.
A propos de l'interrogation des données partitionnées dans la banque d'objets cloud
Lorsque vous interrogez des données partitionnées externes au format Hive, le moteur de requête comprend et utilise les informations de partitionnement du nom du chemin d'accès au fichier. Par exemple, considérons une table SALES
partitionnée externe dans laquelle le fichier source sales/country=USA/year=2020/month=02/file3.csv
sur la banque d'objets inclut les données de vente suivantes :
tents, 291
canoes, 22
backpacks, 378
Les valeurs country
dans le nom de chemin et les valeurs de période pour month
et year
ne sont pas indiquées en tant que colonnes dans le fichier de données. Les valeurs de colonne de partition sont spécifiées uniquement dans le nom du chemin avec les valeurs affichées : USA, 2020 et 02. Une fois que vous avez créé une table partitionnée externe avec ce fichier de données, vous pouvez utiliser les colonnes de partition et leurs valeurs lorsque vous exécutez une requête sur la table partitionnée externe.
Par exemple :
SELECT year, month, product, units
FROM SALES WHERE year='2020' AND month='02' AND country='USA'
L'avantage de créer une table partitionnée externe avec des données générées en tant que données partitionnées au format Hive est que le moteur de requête est optimisé pour partitionner le nettoyage des données afin de sélectionner la partition correcte et que la requête sélectionne uniquement les données d'une partition et n'a besoin que de rechercher un seul fichier de données. Par conséquent, l'interrogation ne nécessite qu'un balayage du fichier file3.csv
(/sales/country=USA/year=2020/month=02/file3.csv
). Pour de grandes quantités de données, un tel élagage de partition peut améliorer considérablement les performances.
A l'aide de tables externes Oracle Database standard, la colonne de partition doit être disponible en tant que colonne dans le fichier de données pour être utilisée pour les requêtes ou les définitions de partition. Sans la gestion spéciale disponible avec les tables partitionnées externes sur Autonomous Database, cela peut poser problème si vous voulez utiliser les données stockées au format Hive sur la banque d'objets cloud, car vous devez régénérer les fichiers de données pour inclure la partition en tant que colonne dans le fichier de données.
A propos de la création de tables externes partitionnées
Lorsque vous utilisez des données non structurées stockées au format Hive sur la banque d'objets cloud et que vous créez une table partitionnée externe, les colonnes et leurs types ne peuvent pas être dérivés du fichier source. Par conséquent, les colonnes et leurs types de données doivent être indiqués avec le paramètre column_list
. Pour créer les tables externes partitionnées, utilisez la procédure DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE
pour indiquer les colonnes de partition et leurs types comme suit :
-
La racine de la liste de fichiers est indiquée dans le nom du chemin avec le paramètre
file_uri_list
. Par exemple,http://.../sales/*
-
Les noms de colonne et les types de données sont indiqués avec le paramètre
column_list
. -
L'option
partition_columns
du paramètreformat
indique les colonnes de partition. -
La DLL générée inclut les colonnes indiquées dans le nom du chemin.
Dans cet exemple, lorsque la table externe est créée, les colonnes country
, year
et month
sont ajoutées dans le paramètre column_list
. La table externe est créée avec les colonnes country
, year
et month
, qui ne figurent pas dans les fichiers de données, et des partitions de liste sont créées pour permettre l'élagage des partitions.
Lorsque vous utilisez des données structurées, telles que des fichiers Parquet, Avro ou ORC stockés dans un format de dossier sur la banque d'objets cloud, les colonnes et leurs types de données sont connus. Vous n'avez pas besoin d'indiquer la liste des colonnes comme requis avec les données non structurées. Pour créer les tables externes partitionnées, utilisez la procédure DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE
pour indiquer les colonnes de partition et leurs types comme suit :
- La racine de la liste de fichiers est indiquée dans le nom du chemin avec le paramètre
file_uri_list
. Par exemple,http://.../sales/*
- Le paramètre
column_list
n'est pas requis pour les fichiers structurés. Si vous n'indiquez pas de liste de colonnes, vous devez définir les colonnes de partition et leurs types de données lorsque vous créez la table partitionnée externe. Utilisez l'optionpartition_columns
dans le paramètreformat
pour indiquer les colonnes de partition et leurs types de données. - La DLL générée inclut les colonnes indiquées dans le nom du chemin.
Pour obtenir des exemples complets, reportez-vous à Interrogation des données partitionnées externes avec l'organisation du fichier source de format Hive et à Interrogation des données partitionnées externes avec l'organisation du fichier source de format de dossier.
- Partitionnement externe : fichiers source CSV avec dossiers de type Hive
Indique comment créer des tables partitionnées externes avec des fichiers source CSV stockés dans la banque d'objets cloud dans des dossiers de type Hive. - Partitionnement externe : fichiers source CSV avec dossiers simples
Indique comment créer des tables partitionnées externes avec des fichiers source CSV stockés dans la banque d'objets cloud dans un format de dossier simple. - Partitionnement externe : fichiers source Parquet avec des dossiers de type Hive
Indique comment créer des tables partitionnées externes avec des fichiers source Parquet stockés dans la banque d'objets cloud dans des dossiers de type Hive. - Partitionnement externe : Parquet avec dossiers simples
Indique comment créer des tables partitionnées externes avec des fichiers source Parquet stockés dans la banque d'objets cloud dans un format de dossier simple.
Partitionnement externe : fichiers source CSV avec dossiers de type Hive
Montre comment créer des tables partitionnées externes avec des fichiers source CSV stockés dans la banque d'objets cloud dans des dossiers de type Hive.
Liste des fichiers source :
.../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"]}');
Le paramètre
partition_columns
du paramètre format
doit correspondre aux noms de colonne trouvés dans le chemin (par exemple, la colonne country
correspond à "country=…
")
Rubrique parent : A propos des tables externes avec partitionnement de fichier source
Partitionnement externe : Fichiers source CSV avec dossiers simples
Montre comment créer des tables partitionnées externes avec des fichiers source CSV stockés dans la banque d'objets cloud dans un format de dossier simple.
Liste des fichiers source :
.../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"]}');
L'appel d'API est le même que dans l'exemple précédent, mais l'ordre de
partition_columns
dans le paramètre format
est significatif car le nom de colonne ne se trouve pas dans le chemin du fichier.
Rubrique parent : A propos des tables externes avec partitionnement de fichier source
Partitionnement externe : Parquet de fichiers source avec des dossiers de type Hive
Montre comment créer des tables partitionnées externes avec des fichiers source Parquet stockés dans la banque d'objets cloud dans des dossiers de type Hive.
Liste des fichiers source :
.../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)')
)
)
);
Le paramètre
column_list
n'est pas indiqué. Comme indiqué, pour chaque colonne de partition, indiquez le nom et le type de données dans le paramètre format
partition_columns
.
Rubrique parent : A propos des tables externes avec partitionnement de fichier source
Partitionnement externe : Parquet avec dossiers simples
Montre comment créer des tables partitionnées externes avec des fichiers source Parquet stockés dans la banque d'objets cloud dans un format de dossier simple.
Liste des fichiers source :
.../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)')
)
)
);
Le paramètre
column_list
n'est pas indiqué. Vous devez inclure à la fois le nom et le type de données pour les colonnes de partition. En outre, l'ordre de partition_columns
dans la clause de format est important car le nom de colonne ne se trouve pas dans le chemin du fichier.
Rubrique parent : A propos des tables externes avec partitionnement de fichier source