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.
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.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Google Cloud project.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Google Cloud project.
- 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 valeuralternate_bases.alt
- Avec la chaîne de texte
<*>
en tant que valeuralternate_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 chromosome1
- 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 spécification du format VCF. pour en savoir plus sur la représentation des 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 le tableau sur la page BigQuery de la console Google Cloud.
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.
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 Run query. La requête renvoie le résultat suivant :
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é 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 :
Ligne | 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 :
Ligne | 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 :
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>", "<*>"))
La requête renvoie le résultat suivant :
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. 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 :
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
La requête renvoie le résultat suivant :
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, 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 :
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 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 :
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
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 :
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
, 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 :
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
La requête renvoie le résultat suivant :
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 :
- 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 :
Ligne | 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 :
- In the Google Cloud console, go to the Manage resources page.
- In the project list, select the project that you want to delete, and then click Delete.
- In the dialog, type the project ID, and then click Shut down to delete the project.
Étape suivante
- Consultez les autres tutoriels sur Cloud Life Sciences.
- Analysez les variantes dans BigQuery à l'aide des langages R, RMarkdown ou JavaScript.