Cette page a été traduite par l'API Cloud Translation.
Switch to English

Aplatir la table des variantes BigQuery

Cette page montre comment aplatir une table BigQuery utilisant le schéma des variantes BigQuery.

L'aplatissement d'une table BigQuery entraîne la suppression des lignes répétées. Cette technique peut s'avérer utile :

  • lorsque vous exploitez des outils tiers, comme ceux utilisés pour la visualisation des données, qui nécessitent une table avec une structure aplatie ;
  • si vous interrogez plusieurs champs répétés indépendamment, ou si vous calculez le produit croisé de tels champs et que vous rencontrez le message d'erreur suivant : Cannot query the cross product of repeated fields ....

Aplatir une table

Supposons que vous disposez d'une table dans BigQuery contenant la ligne ci-dessous. La ligne contient deux bases alternatives, C et T (dans la colonne alternate_bases.alt), ainsi que deux appels, NA12890 et NA12878 (dans la colonne call.name).

Ligne reference_name start_position end_position reference_bases alternate_bases.alt alternate_bases.AC quality filter call.name call.genotype
1 chrY 9909316 9909317 A C 1 577.59 LowMQ NA12890 1
T 1 2
NA12878 1
2

Exécutez la requête suivante pour aplatir la table sur l'enregistrement d'appel répété et renvoyer une ligne pour chaque appel. Notez l'utilisation de l'opérateur de virgule (,), qui représente une notation abrégée utilisée pour JOIN.

#standardSQL
SELECT
  reference_name, start_position, end_position, reference_bases,
  call.name AS call_name
FROM
  `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE` AS t,
  t.call AS call

La requête ci-dessus utilise un chemin d'accès complet dans t.call, mais vous pouvez utiliser l'opérateur UNNEST à la place de t.call, comme illustré ci-dessous :

#standardSQL
SELECT
  reference_name, start_position, end_position, reference_bases,
  call.name AS call_name
FROM
  `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE`,
  UNNEST(call) AS call

Dans les deux cas, les requêtes renvoient une ligne pour chaque appel :

Ligne reference_name start_position end_position reference_bases call_name
1 chrY 9909316 9909317 A NA12890
2 chrY 9909316 9909317 A NA12878

Aplatir des champs supplémentaires

Vous pouvez inclure des informations supplémentaires pour chaque appel en ajoutant des champs à la clause SELECT. Par exemple, la requête suivante ajoute les génotypes d'appel (sous forme de tableau d'entiers) :

#standardSQL
SELECT
  reference_name, start_position, end_position, reference_bases,
  call.name AS call_name, call.genotype
FROM
  `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE` AS t,
  t.call AS call

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

Ligne reference_name start_position end_position reference_bases call_name genotype
1 chrY 9909316 9909317 A NA12890 1
2
2 chrY 9909316 9909317 A NA12878 1
2

Pour aplatir davantage la table BigQuery et renvoyer un génotype par ligne, ajoutez un autre élément JOIN avec le champ call.genotype :

#standardSQL
SELECT
  reference_name, start_position, end_position, reference_bases,
  call.name AS call_name, genotype
FROM
  `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE` AS t,
  t.call AS call,
  call.genotype AS genotype

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

Ligne reference_name start_position end_position reference_bases call_name genotype
1 chrY 9909316 9909317 A NA12890 1
2 chrY 9909316 9909317 A NA12890 2
3 chrY 9909316 9909317 A NA12878 1
4 chrY 9909316 9909317 A NA12878 2

Dans le résultat, chaque champ call_name est dupliqué, car chaque appel contient deux valeurs genotype.

La requête suivante montre ce qui se produit lorsque vous ajoutez un enregistrement répété de façon indépendante (alternate_bases) à la clause SELECT :

#standardSQL
SELECT
  reference_name, start_position, end_position, reference_bases,
  call.name AS call_name, genotype, alternate_bases
FROM
  `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE` AS t,
  t.call AS call,
  call.genotype AS genotype

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

Ligne reference_name start_position end_position reference_bases call_name genotype alternate_bases.alt alternate_bases.AC
1 chrY 9909316 9909317 A NA12890 1 C 1
T 1
2 chrY 9909316 9909317 A NA12890 2 C 1
T 1
3 chrY 9909316 9909317 A NA12878 1 C 1
T 1
4 chrY 9909316 9909317 A NA12878 2 C 1
T 1

Même si la colonne genotype a été aplatie, le résultat contient les deux colonnes alternate_bases. Pour renvoyer la base alternative spécifique correspondant à l'index de la colonne genotype, utilisez la fonction ORDINAL, comme illustré dans la requête suivante :

#standardSQL
SELECT
  reference_name, start_position, end_position, reference_bases,
  call.name AS call_name, genotype,
  IF(genotype > 0, alternate_bases[ORDINAL(genotype)], NULL) AS alternate_bases
FROM
  `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE` AS t,
  t.call AS call,
  call.genotype AS genotype

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

Ligne reference_name start_position end_position reference_bases call_name genotype alternate_bases.alt alternate_bases.AC
1 chrY 9909316 9909317 A NA12890 1 C 1
2 chrY 9909316 9909317 A NA12890 2 T 1
3 chrY 9909316 9909317 A NA12878 1 C 1
4 chrY 9909316 9909317 A NA12878 2 T 1

La sémantique de la colonne genotype dans la clause SELECT a été modifiée, car chaque ligne ne contient qu'un seul allèle alternatif.

La requête suivante montre comment reformater la colonne genotype à l'aide de IF(genotype > 0, 1, genotype) AS alt_genotype, ce qui permet de définir la valeur du génotype comme suit :

  • 1 s'il correspond à la base alternative.
  • 0 s'il correspond à la base de référence.
  • -1 s'il n'est pas appelé. Veuillez noter que l'outil Variant Transforms utilise -1 pour désigner les génotypes non appelés (tels que ., dans le fichier VCF).

La requête suivante montre comment n'inclure que la colonne alternate_bases.alt en aplatissant l'enregistrement alternate_bases et en utilisant l'index comme critère de filtrage :

#standardSQL
SELECT
  reference_name, start_position, end_position, reference_bases,
  call.name AS call_name,
  IF(genotype > 0, 1, genotype) AS alt_genotype,
  IF(genotype > 0, alts.alt, NULL) AS alt
FROM
  `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE` AS t,
  t.call AS call,
  call.genotype AS genotype
LEFT JOIN
  t.alternate_bases AS alts WITH OFFSET AS a_index
WHERE
  genotype IN (a_index + 1, 0, -1)

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

Ligne reference_name start_position end_position reference_bases call_name alt_genotype alt
1 chrY 9909316 9909317 A NA12890 1 C
2 chrY 9909316 9909317 A NA12890 1 T
3 chrY 9909316 9909317 A NA12878 1 C
4 chrY 9909316 9909317 A NA12878 1 T

La requête se sert d'une commande LEFT JOIN pour inclure toutes les lignes n'ayant pas de base alternative. Pour n'inclure que les enregistrements ayant au moins une base alternative, vous pouvez effectuer l'une des opérations suivantes :

  • Utiliser une commande INNER JOIN
  • Inclure t.alternate_bases AS alts WITH OFFSET as a_index dans la clause FROM

De plus, la requête renvoie des lignes supplémentaires pour la valeur de génotype 0 s'il existe un site à plusieurs allèles (ce qui sous-entend plusieurs bases alternatives). Ce comportement est dû au fait que la commande LEFT JOIN effectue une opération CROSS JOIN entre chaque génotype et les bases alternatives. Elle filtre ensuite par a_index. En conséquence, alors que les génotypes ne comportant pas la valeur 0 sont filtrés par a_index + 1, les génotypes comprenant la valeur 0 seront renvoyés pour toutes les combinaisons avec une base alternate_base et un génotype comprenant la valeur 0.

Exemple de requête

La requête suivante illustre parfaitement l'aplatissement d'une table de variantes BigQuery afin qu'elle ne contienne aucun enregistrement répété. La valeur du génotype est définie comme suit :

  • 1 s'il correspond à la base alternative.
  • 0 s'il correspond à la base de référence.
  • -1 s'il n'est pas défini.

La requête est effectuée sur la table suivante :

Ligne reference_name start_position end_position reference_bases alternate_bases.alt alternate_bases.AC quality filter call.name call.genotype
1 chr9 139825375 139825376 T 120.24 LowGQX NA12878 0
PASS NA12877 -1
NA12890 -1
NA12889 -1
NA12891 0
0
2 chr4 4070556 4070558 A null LowGQX NA12878 0
LowMQ 0
MaxDepth
3 chrX 61702219 61702220 T C 1 5.72 LowGQX NA12877 0
LowMQ 1
LowQD
TruthSensitivityTranche99.90to100.00
4 chrY 9909316 9909317 A C 1 577.59 LowMQ NA12890 1
T 1 2
NA1878 1
2 
#standardSQL
SELECT
  reference_name, start_position, end_position, reference_bases,
  IF(genotype > 0, alts.alt, NULL) AS alt,
  t.quality,
  ARRAY_TO_STRING(t.filter, ' ') AS filter,
  call.name AS call_name,
  IF(genotype > 0, 1, genotype) AS alt_genotype,
  call.phaseset
FROM
  `PROJECT_ID.BIGQUERY_DATASET.BIGQUERY_TABLE` AS t,
  t.call AS call,
  call.genotype AS genotype
LEFT JOIN
  t.alternate_bases AS alts WITH OFFSET AS a_index
WHERE
  genotype IN (a_index + 1, 0, -1)

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

Ligne reference_name start_position end_position reference_bases alt quality filter call_name alt_genotype phaseset
1 chr9 139825375 139825376 T null 120.24 LowGQX PASS NA12878 0 null
2 chr9 139825375 139825376 T null 120.24 LowGQX PASS NA12877 -1 null
3 chr9 139825375 139825376 T null 120.24 LowGQX PASS NA12890 -1 null
4 chr9 139825375 139825376 T null 120.24 LowGQX PASS NA12889 -1 null
5 chr9 139825375 139825376 T null 120.24 LowGQX PASS NA12891 0 null
6 chr9 139825375 139825376 T null 120.24 LowGQX PASS NA12891 0 null
7 chr4 4070556 4070558 A null null LowGQX LowMQ MaxDepth NA12878 0 null
8 chr4 4070556 4070558 A null null LowGQX LowMQ MaxDepth NA12878 0 null
9 chrX 61702219 61702220 T null 5.72 LowGQX LowMQ LowQD TruthSensitivityTranche99.90to100.00 NA12877 0 null
10 chrX 61702219 61702220 T C 5.72 LowGQX LowMQ LowQD TruthSensitivityTranche99.90to100.00 NA12877 1 null
11 chrY 9909316 9909317 A C 577.59 LowMQ NA12890 1 null
12 chrY 9909316 9909317 A T 577.59 LowMQ NA12890 1 null
13 chrY 9909316 9909317 A C 577.59 LowMQ NA12878 1 null
14 chrY 9909316 9909317 A T 577.59 LowMQ NA12878 1 null

Pour d'autres champs répétés, vous pouvez effectuer l'une des opérations suivantes :

  • Les concaténer en un seul champ (à l'aide d'une fonction telle que ARRAY_TO_STRING)
  • Les ajouter à la clause FROM ou LEFT JOIN pour les aplatir

Pour représenter le résultat de la requête dans une nouvelle table, consultez la section Créer une table à partir d'un résultat de requête.