Interroger des données partitionnées en externe

BigQuery permet d'interroger des 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. Vous pouvez créer et modifier des tables à l'aide de Cloud Console, de l'outil de ligne de commande bq et de l'API BigQuery.

Pour obtenir des instructions sur l'interrogation de tables partitionnées gérées, consultez la page Présentation des tables partitionnées.

Limites

  • La compatibilité avec le partitionnement Hive est basée sur un préfixe d'URI source commun à tous les URI, qui se termine immédiatement avant l'encodage de partition, comme suit : 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.
  • 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 suivants suivent la configuration par défaut (les paires clé/valeur sont configurées en tant que répertoires avec un signe = en tant que séparateur), et les clés de partitionnement sont toujours dans le même ordre :

gs://myBucket/myTable/dt=2019-10-31/lang=en/foo
gs://myBucket/myTable/dt=2018-10-31/lang=fr/bar

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

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 partition échoue. Par exemple, considérons le chemin d'accès suivant, qui n'encode pas les noms des clés de partitionnement :

gs://myBucket/myTable/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://myBucket/myTable/dt=2019-10-31/lang=en/foo
gs://myBucket/myTable/lang=fr/dt=2018-10-31/bar

Pour ces fichiers, la détection du schéma de partition échouerait.

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 depuis Cloud Storage. BigQuery accepte trois modes de détection de schéma de partition Hive :

  • AUTO : les noms et types de clés sont détectés automatiquement. Les types suivants peuvent être détectés : CHAÎNE, ENTIER, DATE et HORODATAGE.
  • STRINGS : les noms de clés sont automatiquement convertis en type CHAÎNE.
  • CUSTOM : le schéma de clé de partitionnement est encodé comme spécifié dans le préfixe d'URI source.

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

Pour utiliser un schéma CUSTOM, vous devez le spécifier dans le champ du préfixe d'URI source. L'utilisation d'un schéma CUSTOM vous permet de spécifier le type de chaque clé de partitionnement. Les valeurs doivent être valablement analysées en tant que type spécifié, sinon la requête échoue.

Par exemple, si vous définissez l'option source_uri_prefix sur gs://myBucket/myTable/{dt:DATE}/{val:STRING}, BigQuery traite val comme une CHAÎNE, dt comme une DATE, et utilise gs://myBucket/myTable comme préfixe d'URI source pour les fichiers correspondants.

É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. Cela permet à BigQuery d'éviter la lecture de fichiers inutiles, ce qui 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.

Créer une table externe pour les données partitionnées avec Hive

Vous pouvez créer une table externe pour les données partitionnées avec Hive de différentes manières :

  • Utiliser Cloud Console
  • En utilisant l'outil de ligne de commande bq
  • Utiliser les bibliothèques clientes

Console

  1. Ouvrez la page "BigQuery" dans Cloud Console.

    Accéder à la page "BigQuery"

  2. Dans le panneau Explorateur, développez votre projet et sélectionnez un ensemble de données.

  3. Développez l'option Actions, puis cliquez sur Ouvrir.

  4. Dans le panneau de détails, cliquez sur Créer une table.

  5. Dans la section Source de la page Create table (Créer une table), procédez comme suit :

    1. Dans la liste déroulante Créer une table à partir de, sélectionnez Cloud Storage.
    2. Dans le champ Select file from Cloud Storage bucket (Sélectionner un fichier depuis le bucket Cloud Storage), saisissez le chemin d'accès au dossier Cloud Storage en utilisant le format générique. Par exemple, my_bucket/my_files*.
    3. Dans la liste déroulante Format de fichier, sélectionnez le type de fichier.
    4. Cochez la case Partitionnement des données source.
    5. Dans le champ Sélectionnez le préfixe d'URI source, saisissez le préfixe d'URI Cloud Storage. Exemple : gs://my_bucket/my_files.
    6. Sélectionnez un Mode d'inférence de la partition. Si vous sélectionnez Fournir ma propre définition, saisissez les informations de schéma pour les clés de partitionnement.
    7. Facultatif : Pour exiger que toutes les requêtes sur cette table doivent utiliser un filtre de prédicat, cochez l'option Demander un filtre de partitionnement. Pour en savoir plus, consultez la section Exiger des filtres de prédicat sur les clés de partitionnement dans les requêtes.
  6. Dans la section Destination de la page Create table (Créer une table), procédez comme suit :

    1. Sélectionnez le nom du projet et le nom de l'ensemble de données.
    2. Dans la liste déroulante Type de table, sélectionnez Table externe.
    3. Dans le champ Nom de la table, saisissez un nom pour la table externe.
  7. Dans la section Schéma de la page Créer une table, saisissez les informations de schéma. BigQuery est compatible avec la détection automatique de schéma pour certains formats. Pour en savoir plus, consultez la section Utiliser la détection automatique de schéma.

  8. Cliquez sur Create table.

bq

  • Pour activer la détection automatique de clé de partitionnement, définissez l'option --hive_partitioning_mode sur AUTO. Exemple :
bq mkdef --source_format=ORC --hive_partitioning_mode=AUTO \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
GCS_URIS > TABLE_DEF_FILE
  • Pour définir la détection de clé de partitionnement de type chaîne, définissez l'option --hive_partitioning_mode sur STRINGS. Exemple :
bq mkdef --source_format=PARQUET --hive_partitioning_mode=STRINGS \
--hive_partitioning_source_uri_prefix=GCS_URI_SHARED_PREFIX \
GCS_URIS > TABLE_DEF_FILE
  • Pour spécifier un schéma de clé de partitionnement personnalisé à l'aide de l'option --source_uri_prefix, définissez l'option --hive_partitioning_mode sur CUSTOM. Exemple :
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 > TABLE_DEF_FILE

Pour l'option --hive_partitioning_source_uri_prefix, spécifiez la partie <var>CUSOM_SCHEMA_ENCODING</var> de l'argument qui suit immédiatement la partie <var>GCS_URI_SHARED_PREFIX</var>, comme suit :

GCS_URI_SHARED_PREFIX/{KEY1:TYPE1}/{KEY2:TYPE2}/...

Exemple :

--hive_partitioning_source_uri_prefix=gs://myBucket/myTable/{dt:DATE}/{val:STRING}

  • Pour exiger l'utilisation d'un filtre de prédicat au moment de la requête, ajoutez --require_hive_partition_filter=True à la commande bq mkdef. Exemple :
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 > TABLE_DEF_FILE

API

Pour définir le partitionnement Hive à l'aide de l'API BigQuery, ajoutez un objet HiverPartitioningOptions dans l'objetExternalDataConfiguration lorsque vous créez le fichier de définition de table.

Si vous définissez le champ hivePartitioningOptions.mode sur CUSTOM, vous devez encoder le schéma de clé de partitionnement dans le champ hivePartitioningOptions.sourceUriPrefix comme suit : gs://BUCKET/PATH_TO_TABLE/{KEY1:TYPE1}/{KEY2:TYPE2}/...

Pour forcer l'application d'un filtre de prédicat au moment de la requête, définissez le champ hivePartitioningOptions.requirePartitionFilter sur true.

Java

Avant d'essayer l'exemple ci-dessous, suivez la procédure de configuration pour Java décrite dans le guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery en langage Java.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.ExternalTableDefinition;
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.HivePartitioningOptions;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

// Sample to create external table using hive partitioning
public class SetHivePartitioningOptions {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/hive-partitioning-samples/customlayout/*";
    String sourceUriPrefix =
        "gs://cloud-samples-data/bigquery/hive-partitioning-samples/customlayout/{pkey:STRING}/";
    setHivePartitioningOptions(datasetName, tableName, sourceUriPrefix, sourceUri);
  }

  public static void setHivePartitioningOptions(
      String datasetName, String tableName, String sourceUriPrefix, String sourceUri) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Configuring partitioning options
      HivePartitioningOptions hivePartitioningOptions =
          HivePartitioningOptions.newBuilder()
              .setMode("CUSTOM")
              .setRequirePartitionFilter(true)
              .setSourceUriPrefix(sourceUriPrefix)
              .build();

      TableId tableId = TableId.of(datasetName, tableName);
      ExternalTableDefinition customTable =
          ExternalTableDefinition.newBuilder(sourceUri, FormatOptions.parquet())
              .setAutodetect(true)
              .setHivePartitioningOptions(hivePartitioningOptions)
              .build();
      bigquery.create(TableInfo.of(tableId, customTable));
      System.out.println("External table created using hivepartitioningoptions");
    } catch (BigQueryException e) {
      System.out.println("External table was not created" + e.toString());
    }
  }
}