Guide avancé concernant l'analyse des variants à l'aide de BigQuery

Cette page décrit les méthodes avancées d'analyse de variantes à l'aide de BigQuery.

Les données de ce tutoriel sont issues du projet Platinum Genomes d'Illumina. Les données ont été chargées dans une table BigQuery utilisant le schéma des variantes BigQuery. Le nom de la table est platinum_genomes_deepvariant_variants_20180823.

Si les données relatives aux variantes se trouvent dans une table BigQuery utilisant le schéma des variantes BigQuery, il est très simple d'appliquer les requêtes de ce tutoriel à vos données. Pour en savoir plus sur le chargement de données relatives aux variantes dans BigQuery, consultez la documentation sur l'utilisation du pipeline de transformation.

Objectifs

À la fin de ce tutoriel, vous aurez appris :

  • à obtenir un aperçu des données ;
  • comment les segments de non-variantes sont représentés ;
  • comment les appels de variantes sont représentés ;
  • comment les filtres de qualité des appels de variantes sont représentés ;
  • à agréger les colonnes hiérarchiques ;
  • à condenser les requêtes ;
  • à compter des lignes distinctes ;
  • à regrouper les lignes ;
  • à écrire des fonctions définies par l'utilisateur.

Ce tutoriel explique également comment trouver :

  • le nombre de lignes dans la table ;
  • le nombre d'appels de variantes ;
  • les variantes appelées pour chaque exemple ;
  • le nombre d'échantillons ;
  • les variantes par chromosome ;
  • les variantes de haute qualité par échantillon.

Coûts

Ce tutoriel utilise des composants facturables de Google Cloud, dont :

  • BigQuery

Utilisez le Simulateur de coût pour générer une estimation des coûts en fonction de votre utilisation prévue. Les nouveaux utilisateurs de Cloud Platform peuvent bénéficier d'un essai gratuit.

Avant de commencer

  1. Connectez-vous à votre compte Google Cloud. Si vous débutez sur Google Cloud, créez un compte pour évaluer les performances de nos produits en conditions réelles. Les nouveaux clients bénéficient également de 300 $ de crédits gratuits pour exécuter, tester et déployer des charges de travail.
  2. Dans Google Cloud Console, sur la page de sélection du projet, sélectionnez ou créez un projet Google Cloud.

    Accéder au sélecteur de projet

  3. Assurez-vous que la facturation est activée pour votre projet Cloud. Découvrez comment vérifier que la facturation est activée pour votre projet.

  4. Vous devez avoir pris connaissance du schéma des variantes BigQuery.

Afficher les données et le schéma de la table

Accéder à la table et consulter le schéma

La table Platinum Genome d'Illumina platinum_genomes_deepvariant_variants_20180823 est accessible au public.

Variantes et non-variantes dans la table

Les données de Platinum Genome d'Illumina utilisent le format gVCF, ce qui signifie que certaines lignes de la table incluent des non-variantes. Ces non-variantes sont également nommées "appels de référence".

Dans la table, les segments de non-variantes sont généralement représentés de la manière suivante :

  • Avec une valeur alternate_bases de longueur nulle
  • Avec la chaîne de texte <NON_REF> en tant que valeur alternate_bases.alt
  • Avec la chaîne de texte <*> en tant que valeur alternate_bases.alt

La manière dont les segments de non-variantes sont représentés dépend généralement du détecteur de variantes qui génère les données sources. Les variantes de la table platinum_genomes_deepvariant_variants_20180823 ont été appelées à l'aide de DeepVariant, qui utilise le format <*>.

Des segments de non-variantes sont représentés dans certaines lignes des tables suivantes. Les segments représentent un bloc de référence de 10 bases sur le chromosome 1. Le bloc de référence commence à la position 1000. La base de référence à la position 1000 correspond à A. Les bases de référence aux autres positions du bloc ne sont pas représentées.

Dans la table suivante, la colonne REPEATED RECORD alternate_bases ne contient aucune valeur, ce qui signifie qu'il s'agit d'un ARRAY de longueur nulle.

reference_name start_position end_position reference_bases alternate_bases.alt
1 1000 1010 A

Dans la table suivante, la colonne REPEATED RECORD alternate_bases ne contient aucune valeur, ce qui signifie qu'il s'agit d'un <*> de longueur nulle.

reference_name start_position end_position reference_bases alternate_bases.alt
1 1000 1010 A <*>

Les requêtes utilisées dans ce guide utilisent les trois représentations illustrées ci-dessus.

Consultez la section sur le format VCF pour en savoir plus sur la représentation des positions de non-variantes dans le génome.

Afficher les données d'une table

Pour afficher les données de la table platinum_genomes_deepvariant_variants_20180823, procédez comme suit :

  1. Accédez à la page Détails de l'interface utilisateur de BigQuery.

    Les informations sur la table s'affichent. Vous remarquez qu'elle contient 19,6 Go de données et compte plus de 105 000 000 lignes.

  2. Cliquez sur Aperçu pour afficher certaines des lignes de la table.

Interroger la table

Après avoir visualisé le schéma de la table et certaines de ses lignes, vous pouvez désormais commencer à émettre des requêtes et à analyser des données. Avant de poursuivre, assurez-vous d'avoir pris connaissance de la syntaxe des requêtes en SQL standard utilisée par BigQuery.

Compter le nombre total de lignes dans la table

Pour afficher le nombre de lignes dans la table, procédez comme suit :

  1. Accédez à l'interface utilisateur de BigQuery.

    Accéder à l'interface utilisateur de BigQuery

  2. Cliquez sur Saisir une requête.

  3. Copiez et collez la requête suivante dans la zone de texte Nouvelle requête :

     #standardSQL
     SELECT
       COUNT(1) AS number_of_rows
     FROM
       `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823`

  4. Cliquez sur Exécuter la requête. L'exécution de la requête renvoie les éléments suivants :

    Ligne number_of_rows
    1 105923159

Compter les appels de variantes dans la table

Chaque ligne de la table possède une position génomique, qui est un segment de variantes ou de non-variantes.

Chaque ligne contient également une colonne call, qui est un ARRAY d'appels de variantes. Chaque colonne call inclut le champ name et d'autres valeurs, telles que le génotype, la qualité des colonnes, la profondeur de lecture et d'autres valeurs généralement incluses dans un fichier VCF.

Pour compter les appels de variantes, interrogez le nombre d'éléments compris dans les colonnes ARRAY. Vous pouvez procéder de plusieurs façons, comme indiqué ci-dessous. Chaque requête renvoie la valeur 182 104 652, ce qui signifie qu'il y a en moyenne 1,7 appel de variantes par ligne dans l'ensemble de données.

Faire la somme des longueurs des tableaux call

Pour obtenir le nombre total d'appels de variantes sur tous les échantillons, vous pouvez faire la somme des longueurs des tableaux call :

#standardSQL
SELECT
  SUM(ARRAY_LENGTH(call)) AS number_of_calls
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823`

L'exécution de la requête renvoie la valeur correcte (182,104,652) :

Ligne number_of_calls
1 182104652

Appliquer une clause JOIN à chaque ligne

Une autre méthode pour obtenir le nombre total d'appels de variantes sur tous les échantillons consiste à utiliser une clause JOIN sur chaque ligne avec la colonne call. Notez l'utilisation de l'opérateur de virgule (,), qui est une notation abrégée utilisée pour JOIN. Notez également que la jointure au niveau de la colonne call crée une opération UNNEST implicite sur la colonne call :

#standardSQL
SELECT
  COUNT(call) AS number_of_calls
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call

Compter name dans une colonne call

Une troisième méthode pour obtenir le nombre total d'appels de variantes sur tous les échantillons consiste à compter les valeurs name dans la colonne call. Chaque colonne call doit avoir une valeur name unique. Ainsi, vous pouvez exécuter la requête suivante :

#standardSQL
SELECT
  COUNT(call.name) AS number_of_calls
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call call

L'exécution de la requête renvoie la valeur correcte (182,104,652) :

Ligne number_of_calls
1 182104652

Compter des segments de variantes et de non-variantes

Pour compter les segments de variantes et non-variantes dans la table, commencez par exécuter une requête permettant de filtrer les segments de non-variantes :

#standardSQL
SELECT
  COUNT(1) AS number_of_real_variants
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call call
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.alternate_bases) AS alt
          WHERE
            alt.alt NOT IN ("<NON_REF>", "<*>"))

L'exécution de la requête renvoie les éléments suivants :

Ligne number_of_real_variants
1 38549388

Comme indiqué dans la partie Compter les appels de variantes, le nombre total d'appels de variantes dans la table est de 182 104 652. Ce résultat indique que la grande majorité des lignes de la table sont des segments de non-variantes.

Comme indiqué dans la section Variantes et non-variantes dans la table, il existe au moins trois méthodes permettant de classer une ligne de variantes en tant que segment de non-variantes. Dans la requête ci-dessus, la clause WHERE inclut les lignes dans lesquelles la colonne alternate_bases possède une valeur correspondant à une variante véritable (l'inverse d'une valeur de repère spéciale telle que <*> ou <NON_REF>).

Pour chaque ligne de la table, une sous-requête est émise sur la colonne alternate_bases de cette ligne, qui renvoie la valeur 1 pour chaque valeur alternate_bases n'étant pas <NON_REF> ou <*>. Le nombre de lignes renvoyées par la sous-requête correspond au nombre de segments de variantes.

La requête suivante indique comment obtenir le nombre de segments de non-variantes :

#standardSQL
SELECT
  COUNT(1) AS number_of_non_variants
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call call
WHERE
  NOT EXISTS (SELECT 1
                FROM UNNEST(v.alternate_bases) AS alt
              WHERE
                alt.alt NOT IN ("<NON_REF>", "<*>"))

L'exécution de la requête renvoie les éléments suivants :

Ligne number_of_non_variants
1 143555264

Additionner le nombre de variantes réelles (38 549 388) au nombre de segments de non-variantes (143 555 264) donne le nombre total d'appels de variantes.

Compter les variantes appelées par chaque échantillon

Après avoir examiné les lignes de niveau supérieur de la table, vous pouvez commencer à interroger des lignes enfants. Ces lignes incluent des données telles que les échantillons individuels ayant reçu des appels effectués auprès des variantes.

Chaque variante de la table possède zéro ou plusieurs valeurs pour call.name. Une valeur call.name particulière peut s'afficher sur plusieurs lignes.

Pour compter le nombre de lignes dans lesquelles chaque ensemble d'appels s'affiche, exécutez la requête suivante :

#standardSQL
SELECT
  call.name AS call_name,
  COUNT(call.name) AS call_count_for_call_set
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
GROUP BY
  call_name
ORDER BY
  call_name

L'exécution de la requête renvoie six lignes. Chaque call_name correspond à un individu séquencé :

Ligne call_name call_count_for_call_set
1 NA12877 31592135
2 NA12878 28012646
3 NA12889 31028550
4 NA12890 30636087
5 NA12891 33487348
6 NA12892 27347886

Les humains ne disposent généralement pas des 30 millions de variantes indiquées dans les valeurs call_count_for_call_set. Pour filtrer les segments de non-variantes et ne compter que les lignes de variantes, procédez comme suit :

#standardSQL
SELECT
  call.name AS call_name,
  COUNT(call.name) AS call_count_for_call_set
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.alternate_bases) AS alt
          WHERE
            alt.alt NOT IN ("<NON_REF>", "<*>"))
GROUP BY
  call_name
ORDER BY
  call_name

L'exécution de la requête renvoie les éléments suivants :

Ligne call_name call_count_for_call_set
1 NA12877 6284275
2 NA12878 6397315
3 NA12889 6407532
4 NA12890 6448600
5 NA12891 6516669
6 NA12892 6494997

Le nombre de variantes est maintenant plus proche de 6 millions, un chiffre plus typique chez l'homme. Passez à la section suivante pour filtrer les variantes véritables par génotype.

Filtrer les variantes véritables par génotype

Les variantes de la table incluent des variantes sans appels, qui sont représentées par une valeur genotype de -1. Ces variantes ne sont pas considérées comme de véritables variantes pour les individus. Vous devez les filtrer. Les véritables variantes ne peuvent inclure que des appels avec des génotypes supérieurs à zéro. Si un appel n'inclut que des génotypes sans appel (-1) ou de référence (0), il ne s'agit pas de véritables variantes.

Pour filtrer les variantes par génotype, procédez comme suit :

#standardSQL
SELECT
  call.name AS call_name,
  COUNT(call.name) AS call_count_for_call_set
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
  EXISTS (SELECT 1 FROM UNNEST(call.genotype) AS gt WHERE gt > 0)
  AND NOT EXISTS (SELECT 1 FROM UNNEST(call.genotype) AS gt WHERE gt < 0)
GROUP BY
  call_name
ORDER BY
  call_name

L'exécution de la requête renvoie les éléments suivants :

Ligne call_name call_count_for_call_set
1 NA12877 4486610
2 NA12878 4502017
3 NA12889 4422706
4 NA12890 4528725
5 NA12891 4424094
6 NA12892 4495753

Compter les échantillons dans la table

En comptant les variantes appelées par chaque échantillon, chaque requête renvoie six lignes avec des valeurs pour call_name. Pour interroger et obtenir la valeur correspondant à ce nombre de lignes, vous pouvez exécuter la requête suivante :

#standardSQL
SELECT
  COUNT(DISTINCT call.name) AS number_of_callsets
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v,  v.call

L'exécution de la requête renvoie les éléments suivants :

Ligne number_of_callsets
1 6

Compter des variantes par chromosome

Pour compter le nombre de variantes par chromosome, vous pouvez exécuter la requête suivante, qui :

  • compte toutes les lignes dans lesquelles il existe au moins un appel de variante avec au moins un génotype supérieur à 0 ;
  • regroupe les lignes de variantes par chromosome et compte chaque groupe.
#standardSQL
SELECT
  reference_name,
  COUNT(reference_name) AS number_of_variant_rows
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
          WHERE gt > 0)
GROUP BY
  reference_name
ORDER BY
  CASE
    WHEN SAFE_CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) < 10
      THEN CONCAT('0', REGEXP_REPLACE(reference_name, '^chr', ''))
      ELSE REGEXP_REPLACE(reference_name, '^chr', '')
  END

L'exécution de la requête renvoie le nom du chromosome (reference_name) et le nombre de lignes de variantes pour chaque chromosome :

Ligne reference_name number_of_variant_rows
1 chr1 615000
2 chr2 646401
3 chr3 542315
4 chr4 578600
5 chr5 496202

Compter des variantes de haute qualité par échantillon

Interroger des appels avec plusieurs valeurs FILTER

Le format VCF décrit la colonne FILTER, qui permet d'attribuer des appels de variantes de qualités différentes.

La requête suivante indique comment afficher les valeurs FILTER par appel de variante pour l'ensemble de données :

#standardSQL
SELECT
  call_filter,
  COUNT(call_filter) AS number_of_calls
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v,
  v.call,
  UNNEST(call.FILTER) AS call_filter
GROUP BY
  call_filter
ORDER BY
  number_of_calls

L'exécution de la requête renvoie les éléments suivants :

Ligne call_filter number_of_calls
1 RefCall 11681534
2 PASS 26867854

La valeur PASS signifie qu'un appel de variante est de haute qualité.

FILTER les appels de variantes de haute qualité

Lors de l'analyse des variantes, vous pouvez filtrer les variantes de qualité inférieure. Si la colonne FILTER contient la valeur PASS, la colonne ne devrait comprendre aucune autre valeur. Vous pouvez le vérifier en exécutant la requête ci-dessous. La requête omet également les appels qui ne contiennent pas de valeur PASS sous FILTER.

#standardSQL
SELECT
  reference_name,
  start_position,
  end_position,
  reference_bases,
  call.name AS call_name,
  (SELECT STRING_AGG(call_filter) FROM UNNEST(call.FILTER) AS call_filter) AS filters,
  ARRAY_LENGTH(call.FILTER) AS filter_count
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
  EXISTS (SELECT 1 FROM UNNEST(call.FILTER) AS call_filter WHERE call_filter = 'PASS')
  AND ARRAY_LENGTH(call.FILTER) > 1
ORDER BY
  filter_count DESC, reference_name, start_position, end_position, reference_bases, call_name
LIMIT
  10

Comme prévu, l'exécution de la requête ne renvoie aucun résultat.

Compter tous les appels de haute qualité pour chaque échantillon

La requête suivante montre comment compter tous les appels (variantes et non-variantes) pour chaque ensemble d'appels et omet tous les appels avec un filtre non-PASS :

#standardSQL
SELECT
  call.name AS call_name,
  COUNT(1) AS number_of_calls
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
  NOT EXISTS (SELECT 1 FROM UNNEST(call.FILTER) AS call_filter WHERE call_filter != 'PASS')
GROUP BY
  call_name
ORDER BY
  call_name

L'exécution de la requête renvoie les éléments suivants :

Ligne call_name number_of_calls
1 NA12877 29795946
2 NA12878 26118774
3 NA12889 29044992
4 NA12890 28717437
5 NA12891 31395995
6 NA12892 25349974

Compter tous les appels de véritables variantes de haute qualité pour chaque échantillon

La requête suivante montre comment compter tous les appels (variantes et non-variantes) pour chaque échantillon. Elle omet tous les appels avec un filtre autre que PASS et n'inclut que les appels avec au moins une variante véritable, ce qui signifie que genotype > 0 :

#standardSQL
SELECT
  call.name AS call_name,
  COUNT(1) AS number_of_calls
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, v.call
WHERE
  NOT EXISTS (SELECT 1 FROM UNNEST(call.FILTER) AS call_filter WHERE call_filter != 'PASS')
  AND EXISTS (SELECT 1 FROM UNNEST(call.genotype) as gt WHERE gt > 0)
GROUP BY
  call_name
ORDER BY
  call_name

L'exécution de la requête renvoie les éléments suivants :

Ligne call_name number_of_calls
1 NA12877 4486610
2 NA12878 4502017
3 NA12889 4422706
4 NA12890 4528725
5 NA12891 4424094
6 NA12892 4495753

Bonnes pratiques

Condenser les requêtes

À mesure que vos requêtes deviennent plus complexes, elles doivent rester concises afin que leur logique soit correcte et simple à suivre.

L'exemple suivant montre comment démarrer à partir d'une requête permettant de compter le nombre de variants par chromosome, puis la condenser, étape par étape, à l'aide de la syntaxe SQL et les fonctions définies par l'utilisateur.

Comme expliqué dans la section Compter des variantes par chromosome, la requête présente les conditions suivantes :

  • compte toutes les lignes dans lesquelles il existe au moins un appel de variante avec au moins un génotype supérieur à 0 ;
  • regroupe les lignes de variantes par chromosome et compte chaque groupe.

Écrire cette requête peut s'avérer compliqué car, pour terminer la première tâche, vous devez examiner un ARRAY (genotype) dans un ARRAY (call) tout en conservant le contexte d'exécution de la requête au niveau de la ligne. Il en va ainsi car vous souhaitez produire un résultat par variante au lieu d'un résultat par call ou par genotype.

La fonction UNNEST vous permet d'interroger une colonne ARRAY en tant que table. La fonction renvoie une ligne pour chaque élément d'un ARRAY et ne modifie pas le contexte de la requête. Ainsi, vous pouvez commencer par utiliser une fonction UNNEST d'une sous-requête EXISTS dans une clause WHERE :

#standardSQL
SELECT
  reference_name,
  COUNT(reference_name) AS number_of_variant_rows
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.call) AS call
          WHERE EXISTS (SELECT 1
                          FROM UNNEST(call.genotype) AS gt
                        WHERE gt > 0))
GROUP BY
  reference_name
ORDER BY
  reference_name

La requête renvoie les mêmes résultats que dans l'exemple de la section Compter des variantes par chromosome :

Ligne reference_name number_of_variant_rows
1 chr1 615000
2 chr10 396773
3 chr11 391260
4 chr12 382841
5 chr13 298044

Il est possible de rendre la requête plus concise en modifiant la clause EXISTS en JOIN de la colonne call avec la colonne call.genotype. Souvenez-vous que l'opérateur de virgule est une notation abrégée utilisée pour JOIN :

#standardSQL
SELECT
  reference_name,
  COUNT(reference_name) AS number_of_variant_rows
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
          WHERE gt > 0)
GROUP BY
  reference_name
ORDER BY
  reference_name

La requête est concise et opérationnelle, mais elle ne vous permet pas de trier les résultats par ordre numérique croissant de chromosomes (reference_name). Cela est dû au fait que les valeurs de reference_name sont des types de chaîne et que chaque valeur contient le préfixe "chr".

Pour trier les résultats de manière numérique, commencez par supprimer le préfixe "chr" de la colonne reference_name et attribuez-lui l'alias chromosome :

#standardSQL
SELECT
  REGEXP_REPLACE(reference_name, '^chr', '') AS chromosome,
  COUNT(reference_name) AS number_of_variant_rows
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
          WHERE gt > 0)
GROUP BY
  chromosome
ORDER BY
  chromosome

La requête utilise la fonction REGEXP_REPLACE pour remplacer la chaîne de préfixe "chr" par une chaîne vide. Elle modifie ensuite les fonctions GROUP BY et ORDER BY pour utiliser l'alias chromosome calculé. Toutefois, les résultats sont toujours triés par chaîne :

Ligne chromosome number_of_variant_rows
1 1 615000
2 10 396773
3 11 391260
4 12 382841
5 13 298044

Pour trier les résultats de manière numérique, convertissez la colonne chromosome pour la faire passer d'une chaîne à un entier :

#standardSQL
SELECT
  CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) AS chromosome,
  COUNT(reference_name) AS number_of_variant_rows
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
          WHERE gt > 0)
GROUP BY
  chromosome
ORDER BY
  chromosome

Dans ce cas, la requête renvoie une erreur, car tous les noms de chromosomes, tels que "X", "Y" et "M" sont des valeurs numériques. Utilisez la fonction CASE pour ajouter un "0" aux chromosomes 1 à 9, puis supprimez le préfixe "chr" :

#standardSQL
SELECT
  CASE
    WHEN SAFE_CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) < 10
      THEN CONCAT('0', REGEXP_REPLACE(reference_name, '^chr', ''))
      ELSE REGEXP_REPLACE(reference_name, '^chr', '')
  END AS chromosome,
  COUNT(reference_name) AS number_of_variant_rows
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
          WHERE gt > 0)
GROUP BY
  chromosome
ORDER BY
  chromosome

La requête renvoie les résultats appropriés :

Ligne chromosome number_of_variant_rows
1 01 615000
2 02 646401
3 03 542315
4 04 578600
5 05 496202

Vous remarquerez l'utilisation de la fonction SAFE_CAST, qui renvoie NULL pour les chromosomes X, Y, et M au lieu de renvoyer une erreur.

Pour effectuer une dernière amélioration des résultats, affichez à nouveau la colonne reference_name au lieu de la configurer sur l'alias chromosome. Pour ce faire, déplacez la clause CASE vers la fonction ORDER BY :

#standardSQL
SELECT
  reference_name,
  COUNT(reference_name) AS number_of_variant_rows
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
          WHERE gt > 0)
GROUP BY
  reference_name
ORDER BY
  CASE
    WHEN SAFE_CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) < 10
      THEN CONCAT('0', REGEXP_REPLACE(reference_name, '^chr', ''))
      ELSE REGEXP_REPLACE(reference_name, '^chr', '')
  END

Cette dernière requête est identique à celle présentée dans la section Compter des variantes par chromosome.

Écrire des fonctions définies par l'utilisateur

BigQuery est compatible avec les fonctions définies par l'utilisateur, qui vous permettent de créer une fonction à l'aide d'une autre expression SQL ou d'un autre langage de programmation, tel que JavaScript.

L'exemple illustré dans la section Condenser les requêtes montre comment créer une requête complexe, qui finit par devenir trop détaillée.

La requête suivante montre comment la rendre plus concise en déplaçant la logique CASE dans une fonction :

#standardSQL
CREATE TEMPORARY FUNCTION SortableChromosome(reference_name STRING)
  RETURNS STRING AS (
  -- Remove the leading "chr" (if any) in the reference_name
  -- If the chromosome is 1 - 9, prepend a "0" since
  -- "2" sorts after "10", but "02" sorts before "10".
  CASE
    WHEN SAFE_CAST(REGEXP_REPLACE(reference_name, '^chr', '') AS INT64) < 10
      THEN CONCAT('0', REGEXP_REPLACE(reference_name, '^chr', ''))
      ELSE REGEXP_REPLACE(reference_name, '^chr', '')
  END
);

SELECT
  reference_name,
  COUNT(reference_name) AS number_of_variant_rows
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
          WHERE gt > 0)
GROUP BY
  reference_name
ORDER BY SortableChromosome(reference_name)

La requête suivante montre également comment rendre la requête plus concise, mais en utilisant une fonction définie en JavaScript :

#standardSQL
CREATE TEMPORARY FUNCTION SortableChromosome(reference_name STRING)
  RETURNS STRING LANGUAGE js AS """
  // Remove the leading "chr" (if any) in the reference_name
  var chr = reference_name.replace(/^chr/, '');

  // If the chromosome is 1 - 9, prepend a "0" since
  // "2" sorts after "10", but "02" sorts before "10".
  if (chr.length == 1 && '123456789'.indexOf(chr) >= 0) {
    return '0' + chr;
  }

  return chr;
""";

SELECT
  reference_name,
  COUNT(reference_name) AS number_of_variant_rows
FROM
  `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v
WHERE
  EXISTS (SELECT 1
            FROM UNNEST(v.call) AS call, UNNEST(call.genotype) AS gt
          WHERE gt > 0)
GROUP BY
  reference_name
ORDER BY SortableChromosome(reference_name)

Les deux requêtes renvoient le résultat approprié, mais avec une logique plus concise.

Améliorer les performances des requêtes et réduire les coûts

Les tarifs de BigQuery sont basés sur le nombre d'octets traités pour une requête. Les performances de la requête s'améliorent également lorsque la quantité de données traitées est réduite. L’interface utilisateur BigQuery fournit des données sur le nombre de secondes écoulées depuis le début d’une requête ainsi que sur le nombre d’octets traités. Consultez l'explication du plan de requête de BigQuery pour en savoir plus sur l'optimisation de vos requêtes.

Certains exemples de cette page, tels qu'illustrés dans la section Compter les appels de variantes dans une table, montrent plusieurs manières d'écrire une requête. Pour déterminer quelle méthode d'interrogation vous convient le mieux, examinez la durée des différentes requêtes et la quantité d'octets de données qu'elles traitent.

Nettoyer

Une fois que vous avez terminé le tutoriel, vous pouvez nettoyer les ressources que vous avez créées sur Google Cloud afin qu'elles ne vous soient plus facturées. Dans les sections suivantes, nous allons voir comment supprimer ou désactiver ces ressources.

Supprimer le projet

Le moyen le plus simple d'empêcher la facturation est de supprimer le projet que vous avez utilisé dans le cadre de ce tutoriel.

Pour supprimer le projet :

  1. Dans Cloud Console, accédez à la page "Projets".

    Accéder à la page Projets

  2. Dans la liste des projets, sélectionnez celui que vous souhaitez supprimer, puis cliquez sur Supprimer le projet. Après avoir coché la case à côté du nom du projet, cliquez sur &quot;Supprimer le projet&quot;.
  3. Dans la boîte de dialogue, saisissez l'ID du projet, puis cliquez sur Arrêter pour supprimer le projet.

Étape suivante