Interroger des données partitionnées en externe

BigQuery est compatible avec le chargement de données partitionnées en externe aux formats Avro, Parquet, ORC, JSON et CSV stockées sur Cloud Storage à l'aide de la configuration de partitionnement Hive par défaut. L'assistance de partitionnement Hive est activée en définissant les options appropriées dans le fichier de définition de table. Il est actuellement possible de créer et de modifier une table à l'aide de Cloud Console, de l'outil de ligne de commande bq et de l'API REST.

Pour obtenir des instructions sur l'interrogation des tables partitionnées gérées, consultez la section Utiliser des tables partitionnées.

Limites

  • L'assistance de partitionnement Hive est conçue sous réserve d'un préfixe URI source commun pour tous les URI qui se termine immédiatement avant l'encodage de partitionnement (sous forme de gs://<bucket>/<path_to_table>/.).
  • La structure du répertoire d'une table partitionnée Hive est supposée contenir des clés de partitionnement identiques s'affichant dans le même ordre, avec maximum dix clés de partitionnement par table.
  • Les données doivent respecter une configuration de partitionnement Hive par défaut.
  • Les clés de partitionnement Hive et les colonnes des fichiers sous-jacents ne peuvent pas se chevaucher.
  • La création et la modification de tables sont actuellement limitées à Cloud Console, à l'outil de ligne de commande bq et à l'API REST.
  • Toutes les limites d'interrogation des sources de données externes stockées dans Cloud Storage s'appliquent.
  • L'assistance n'est fournie que pour le langage SQL standard.

Configurations de données compatibles

Les données doivent suivre une configuration de partitionnement Hive par défaut. Par exemple, les fichiers ci-dessous respectent la configuration par défaut, avec les paires clé/valeur disposées sous forme de répertoires avec un signe = en tant que séparateur, et les clés de partitionnement sont toujours dans le même ordre.

gs://bucket/table/dt=2019-10-31/lang=en/foo
gs://bucket/table/dt=2018-10-31/lang=fr/bar

Le préfixe d'URI source commun utilisé dans cet exemple est gs://bucket/table.

Configurations de données non compatibles

Si les noms des clés de partitionnement ne sont pas encodés dans le chemin d'accès au répertoire, la détection du schéma de partitionnement va échouer. Par exemple, considérons le chemin d'accès suivant, qui n'encode pas les noms des clés de partitionnement :

gs://bucket/table/2019-10-31/en/foo

Les fichiers dans lesquels le schéma n'est pas dans un ordre cohérent ne vont pas être détectés. Par exemple, considérons les deux fichiers suivants qui contiennent des encodages de clés de partitionnement inversés :

gs://bucket/table/dt=2019-10-31/lang=en/foo
gs://bucket/table/lang=fr/dt=2018-10-31/bar

Modes de détection des schémas de partition

Les clés de partitionnement Hive s'affichent comme des colonnes normales lorsque les données sont interrogées à partir de Cloud Storage. Notre offre est compatible avec les trois modes de détection de schéma de partition Hive suivants :

  • AUTO : les noms et types clés sont détectés de manière automatique. Les types suivants peuvent être détectés : CHAÎNE, ENTIER, DATE et HORODATAGE.
  • CHAÎNES : les noms de clés sont automatiquement obtenus avec le type CHAÎNE.
  • PERSONNALISÉ : le schéma de clé de partitionnement est encodé dans le préfixe URI source.

Fournir un schéma de clé de partitionnement personnalisé

Le mode de détection PERSONNALISÉ nécessite l'encodage du schéma dans le champ de préfixe URI source. L'utilisation du mode PERSONNALISÉ autorise les types spécifiés par l'utilisateur pour chaque clé de partitionnement. Les valeurs doivent être valablement analysées en tant que type spécifié sinon le chargement échoue.

Par exemple, définir source_uri_prefix sur gs://my_bucket/my_table/{dt:DATE}/{val:STRING} traite val comme une CHAÎNE et dt comme une DATE, et extrait gs://my_bucket/my_table comme préfixe URI source pour les fichiers correspondants.

Consultez les instructions détaillées concernant Cloud Console, l'outil de ligne de commande bq et l'API REST.

Éliminer des partitions

BigQuery élimine les partitions lorsque cela est possible en utilisant des prédicats de requête sur les clés de partitionnement, ce qui permet d'éviter la lecture de fichiers inutiles. Cela permet d'améliorer les performances.

Exiger des filtres de prédicat sur les clés de partitionnement dans les requêtes

Lorsque vous créez une table partitionnée en externe, vous pouvez exiger l'utilisation de filtres de prédicat sur les clés de partitionnement en activant l'option requirePartitionFilter sous HivePartitioningOptions.

Lorsque cette option est activée, toute tentative d'interrogation de la table partitionnée en externe sans spécifier de clause WHERE génère l'erreur suivante : Cannot query over table <table_name> without a filter over column(s) <partition key names> that can be used for partition elimination.

Pour en savoir plus sur l'ajout de l'option requirePartitionFilter lorsque vous créez une table partitionnée, suivez les instructions de l'outil de ligne de commande bq et de l'API REST.

Définir HivePartitioningOptions à l'aide de Cloud Console

Pour définir les options de partitionnement Hive dans Cloud Console, procédez comme suit :

  1. Ouvrez la page BigQuery dans Cloud Console.

    Accéder à BigQuery

  2. Dans le volet Ressources, sélectionnez votre ensemble de données.

  3. Cliquez sur le bouton Créer une table afin de créer une table.

  4. Sélectionnez Cloud Storage dans la liste déroulante Créer une table à partir de.

Options de partitionnement Hive

  1. Dans le champ Select file from GCS bucket (Sélectionner un fichier depuis le bucket GCS), saisissez le chemin d'accès au fichier de votre ensemble de données, puis sélectionnez son type dans la liste déroulante.
  2. Cochez la case Source data partitioning (Partitionnement des données source), puis saisissez le préfixe d'URI source dans le champ prévu à cet effet.
  3. Sous Partition inference mode (Mode d'inférence de la partition), sélectionnez "Automatically infer types" (Déduire automatiquement les types) pour un mode AUTO ou "All columns are strings" (Toutes les colonnes sont des chaînes) pour des données CHAÎNES.
  4. Sélectionnez "Table externe" dans la liste déroulante Type de table.

Configurer les options HivePartitioningOptions à l'aide de l'outil de ligne de commande bq

Les objets HivePartitioningOptions sont définis dans l'objet ExternalDataConfiguration lors de la création du fichier de définition de table.

Demander la détection automatique du type de clé de partitionnement

bq mkdef --source_format=ORC --hive_partitioning_mode=AUTO \
--hive_partitioning_source_uri_prefix=gcs_uri_shared_prefix \
gcs_uris >/tmp/table_def_file

Demander la détection de clé de partitionnement string-typed

bq mkdef --source_format=PARQUET --hive_partitioning_mode=STRINGS \
--hive_partitioning_source_uri_prefix=gcs_uri_shared_prefix \
gcs_uris >/tmp/table_def_file

Fournir un schéma de clé de partitionnement personnalisé encodé via le champ source_uri_prefix

bq mkdef --source_format=NEWLINE_DELIMITED_JSON --hive_partitioning_mode=CUSTOM \
--hive_partitioning_source_uri_prefix=gcs_uri_shared_prefix/custom_schema_encoding \
gcs_uris file_schema >/tmp/table_def_file

Le schéma de clé de partitionnement est encodé immédiatement après le préfixe URI source. Utilisez le schéma suivant pour spécifier --hive_partitioning_source_uri_prefix :

--hive_partitioning_source_uri_prefix=gcs_uri_shared_prefix/{key1:TYPE1}/{key2:TYPE2}/{key3:TYPE3}

Exiger un filtre de partitionnement

Pour appliquer un filtre de prédicat au moment de la requête, ajoutez --require_hive_partition_filter=True aux commandes bq mkdef ci-dessus. Cet exemple utilise la détection de type de clé de partitionnement AUTO :

bq mkdef --source_format=ORC --hive_partitioning_mode=AUTO \
--hive_partitioning_source_uri_prefix=gcs_uri_shared_prefix \
--require_hive_partition_filter=True \
gcs_uris >/tmp/table_def_file

Configurer les options HivePartitioningOptions à l'aide de l'API REST

L'assistance pour le partitionnement Hive est activée en définissant HivePartitioningOptions sur ExternalDataConfiguration lors de la création du fichier de définition de table.

Notez que lorsque hivePartitioningOptions.mode est défini sur PERSONNALISÉ, il est nécessaire d'encoder le schéma de clé de partitionnement dans l'élément sourceUriPrefix en définissant hivePartitioningOptions.sourceUriPrefix sur gs://bucket/path_to_table/{key1:TYPE1}/{key2:TYPE2}/{key3:TYPE3}.

Pour appliquer un filtre de prédicat au moment de la requête, définissez hivePartitioningOptions.requirePartitionFilter sur true.