Cloud Life Sciences est obsolète et ne sera plus disponible sur Google Cloud après le 8 juillet 2025. Les cas d'utilisation de Cloud Life Sciences sont désormais compatibles avec Batch. Pour savoir comment migrer votre charge de travail, consultez Migrer vers un lot.

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

Ce tutoriel explique comment effectuer les opérations suivantes :

Obtenir un aperçu des données génomiques
Identifier comment les segments de non-variantes sont représentés
Identifier comment les appels de variantes sont représentés
Identifier comment les filtres de qualité des appels de variantes sont représentés
Agréger les colonnes hiérarchiques
Condenser les requêtes
Compter le nombre de lignes distinctes
Regrouper des lignes
Écrire des fonctions définies par l'utilisateur

Ce tutoriel explique également comment trouver les informations suivantes :

Nombre de lignes dans la table
Nombre d'appels de variantes
les variantes appelées pour chaque exemple ;
Nombre d'échantillons
les variantes par chromosome ;
Variantes de haute qualité par échantillon

Coûts

Dans ce document, vous utilisez les composants facturables suivants de Google Cloud :

BigQuery

Obtenez une estimation des coûts en fonction de votre utilisation prévue à l'aide du simulateur de coût. Les nouveaux utilisateurs de Google Cloud peuvent bénéficier d'un essai gratuit.

Avant de commencer

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.

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

Vérifiez que la facturation est activée pour votre projet Google Cloud.

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

Vérifiez que la facturation est activée pour votre projet Google Cloud.

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 affichent les informations suivantes :

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 représentations des tables précédentes.

Consultez la section sur la spécification du 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 :

Affichez la table dans la page BigQuery de la console Google Cloud.

Accéder à la page "BigQuery"

Les informations sur la table s'affichent. La table contient 19,6 Go de données et compte plus de 105 000 000 lignes.
Cliquez sur Aperçu pour afficher certaines des lignes de la table.

Interroger la table

Après avoir affiché le schéma de la table et certaines de ses lignes, commencez à émettre des requêtes et à analyser des données. Avant de poursuivre, assurez-vous d'avoir pris connaissance de la syntaxe de requête en langage 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 :

Accédez à la page "BigQuery" de la console Google Cloud.

Accéder à la page "BigQuery"
Cliquez sur Saisir une requête.

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`

Cliquez sur Exécuter la requête. La requête renvoie le résultat suivant :

Row number_of_rows

1 105923159

Row	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é par la suite. 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`

Comptez le nombre total d'appels de variantes sur tous les échantillons en ajoutant la longueur de chaque tableau call :

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

La requête renvoie le résultat suivant :

Row	number_of_calls
1	182104652

Appliquer une clause `JOIN` à chaque ligne

Comptez le nombre total d'appels de variantes sur tous les échantillons en utilisant une clause JOIN sur chaque ligne de la colonne call. La requête utilise l'opérateur de virgule (,), qui est une notation abrégée utilisée pour la clause JOIN. 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

La requête renvoie le résultat suivant :

Row	number_of_calls
1	182104652

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

La requête renvoie le résultat suivant :

Row	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>", "<*>"))

La requête renvoie le résultat suivant :

Row	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. Le résultat indique que la 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, il ne s'agit donc pas 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>", "<*>"))

La requête renvoie le résultat suivant :

Row	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

La requête renvoie le résultat suivant :

Row	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, 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
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

La requête renvoie le résultat suivant :

Row	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 obtenir la valeur correspondant au nombre de lignes, exécutez 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

La requête renvoie le résultat suivant :

Ligne	number_of_callsets
1	6

Compter des variantes par chromosome

Pour compter le nombre de variantes par chromosome, exécutez la requête ci-dessous. La requête effectue les opérations suivantes :

Elle compte toutes les lignes dans lesquelles il existe au moins un appel de variante avec au moins un génotype supérieur à 0.
Elle 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 :

Row	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`

La spécification du format VCF décrit la colonne FILTER que vous pouvez utiliser pour étiqueter les appels de variantes de différentes qualités.

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

La requête renvoie le résultat suivant :

Row	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, il est probable que la colonne ne contienne 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

La requête renvoie le résultat suivant :

Row	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

La requête renvoie le résultat suivant :

Row	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 :

Elle compte toutes les lignes dans lesquelles il existe au moins un appel de variante avec au moins un génotype supérieur à 0.
Elle 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. Vous conservez le contexte d'exécution de la requête au niveau de la ligne, car vous souhaitez produire un résultat par variante plutôt qu'un résultat par call ou par genotype.

La fonction UNNEST vous permet d'interroger une colonne ARRAY comme s'il s'agissait d'une table. La fonction renvoie une ligne pour chaque élément d'un ARRAY et ne modifie pas le contexte de la requête. Utilisez une fonction UNNEST dans 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
…	…	…

Vous pouvez rendre la requête plus concise en modifiant la clause EXISTS en clause JOIN de la colonne call avec la colonne call.genotype. 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. La requête modifie ensuite les fonctions GROUP BY et ORDER BY pour utiliser l'alias chromosome calculé. Le résultat est toujours trié 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

La requête renvoie une erreur, car tous les noms de chromosomes ne sont pas des valeurs numériques (par exemple "X", "Y" et "M"). 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 :

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

La requête utilise 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. Vous pouvez les employer pour créer une fonction à l'aide d'une autre expression SQL ou d'un autre langage de programmation, par exemple JavaScript.

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

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 lorsque la quantité de données traitées est réduite. 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.

Effectuer un nettoyage

Une fois le tutoriel terminé, vous pouvez procéder au nettoyage des ressources que vous avez créées afin qu'elles ne soient plus comptabilisées dans votre quota et qu'elles ne vous soient plus facturées. Dans les sections suivantes, nous allons voir comment supprimer ou désactiver ces ressources.

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

Pour supprimer le projet :

Attention : La suppression d'un projet aura les effets suivants :

Tout le contenu du projet est supprimé. Si vous avez utilisé un projet existant pour les tâches de ce document, lorsque vous le supprimez, vous supprimez également tout autre travail effectué dans le projet.
Les ID de projets personnalisés sont perdus. Lorsque vous avez créé ce projet, vous avez peut-être créé un ID de projet personnalisé que vous souhaitez utiliser à l'avenir. Pour conserver les URL qui utilisent l'ID de projet, telle qu'une URL appspot.com, supprimez les ressources sélectionnées dans le projet au lieu de supprimer l'ensemble du projet.

Si vous envisagez d'explorer plusieurs architectures, tutoriels et guides de démarrage rapide, réutiliser des projets peut vous aider à ne pas dépasser les limites de quotas des projets.

Dans la console Google Cloud, accédez à la page Gérer les ressources.
Accéder à la page Gérer les ressources
Dans la liste des projets, sélectionnez le projet que vous souhaitez supprimer, puis cliquez sur Supprimer.
Dans la boîte de dialogue, saisissez l'ID du projet, puis cliquez sur Arrêter pour supprimer le projet.

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

Objectifs

Coûts

Avant de commencer

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

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

Variantes et non-variantes dans la table

Afficher les données d'une table

Interroger la table

Compter le nombre total de lignes dans la table

Compter les appels de variantes dans la table

Faire la somme des longueurs des tableaux call

Appliquer une clause JOIN à chaque ligne

Compter name dans une colonne call

Compter des segments de variantes et de non-variantes

Compter les variantes appelées par chaque échantillon

Filtrer les variantes véritables par génotype

Compter les échantillons dans la table

Compter des variantes par chromosome

Compter des variantes de haute qualité par échantillon

Interroger des appels avec plusieurs valeurs FILTER

FILTER les appels de variantes de haute qualité

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

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

Bonnes pratiques

Condenser les requêtes

Écrire des fonctions définies par l'utilisateur

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

Effectuer un nettoyage

Étapes suivantes

Faire la somme des longueurs des tableaux `call`

Appliquer une clause `JOIN` à chaque ligne

Compter `name` dans une colonne `call`

Interroger des appels avec plusieurs valeurs `FILTER`

`FILTER` les appels de variantes de haute qualité