BigQuery のバリアント テーブルのフラット化

このページでは、BigQuery バリアント スキーマを使用する BigQuery テーブルをフラット化する方法について説明します。

BigQuery テーブルをフラット化すると、繰り返し行が削除されます。フラット化は次のような場合に役立ちます。

  • フラット化した構造のテーブルを必要とするサードパーティ製ツール(データの可視化に使用されるものなど)を使用する場合。
  • 複数の独立した繰り返しフィールドをクエリしているか、またはそのようなフィールドのクロス積を計算しており、エラー メッセージ「Cannot query the cross product of repeated fields ...」が返される場合。

テーブルのフラット化

BigQuery に次の行を含むテーブルがあるとします。この行には、2 つの非相補的塩基 CTalternate_bases.alt 列)と、2 つのコール NA12890NA12878call.name 列)が含まれています。

Row 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

次のクエリを実行すると、繰り返されるコールレコードに基づいてテーブルがフラット化され、コールごとに 1 行のデータが返されます。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

上記のクエリでは、t.call 内で完全修飾パスを使用していますが、次のように t.call の代わりに UNNEST 演算子を使用することもできます。

#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

どちらの場合も、クエリはコールごとに 1 行を返します。

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

その他のフィールドのフラット化

SELECT 句にフィールドを追加することで、コールごとの追加情報を含めることができます。たとえば、次のクエリは、コールの遺伝子型を(整数の配列として)追加します。

#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

このクエリを実行すると、次の結果が返されます。

Row 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

この BigQuery テーブルをさらにフラット化して、それぞれの行に 1 つの遺伝子型を返すようにするには、call.genotype フィールドを対象とする別の JOIN を追加します。

#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

このクエリを実行すると、次の結果が返されます。

Row 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

出力では、各コールに 2 つの genotype 値が含まれているため、それぞれの call_name が重複しています。

次のクエリは、独立した繰り返しレコード(alternate_bases)を 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

このクエリを実行すると、次の結果が返されます。

Row 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

genotype 列はフラット化されましたが、出力には両方の alternate_bases 列が含まれています。genotype 列のインデックスに対応した特定の非相補的塩基を返すには、次のクエリのように ORDINAL 関数を使用します。

#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

このクエリを実行すると、次の結果が返されます。

Row 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

各行には単一の非相補的対立遺伝子しか含まれていないため、SELECT 句の genotype 列のセマンティクスが変更されたことがわかります。

次のクエリは、IF(genotype > 0, 1, genotype) AS alt_genotype を使用して genotype 列を再フォーマットする方法を示しています。この結果、遺伝子型の値は次のように設定されます。

  • 1。非相補的塩基に一致する場合。
  • 0。参照塩基に一致する場合。
  • -1。コールされていない場合。Variant Transforms ツールは、コールされていない遺伝子型(VCF ファイルの . など)を示すために -1 を使用します。

次のクエリは、alternate_bases レコードをフラット化し、インデックスをフィルタ条件として使用することにより、alternate_bases.alt 列だけを含める方法を示しています。

#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)

このクエリを実行すると、次の結果が返されます。

Row 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

このクエリは、LEFT JOIN を使用して非相補的塩基のない行をすべて含めます。少なくとも 1 つの非相補的塩基があるレコードだけを含めるには、以下のいずれかを実行します。

  • INNER JOIN を使用する。
  • FROM 句に t.alternate_bases AS alts WITH OFFSET as a_index を含める。

さらに、複対立遺伝子のサイト(つまり複数の非相補的塩基)がある場合、このクエリは遺伝子型の値が 0 の追加の行を返します。これは、LEFT JOIN が各遺伝子型と非相補的塩基の間で CROSS JOIN を行うためです。続いて、a_index でフィルタリングします。その結果、0 以外の遺伝子型は a_index + 1 で絞り込まれますが、alternate_base と値が 0 の遺伝子型があるすべての組み合わせについては、値が 0 の遺伝子型が返されます。

クエリの例

次のクエリ例では、繰り返しレコードが含まれないように、BigQuery バリアント テーブルをフラット化する方法を示します。遺伝子型の値は次のように設定されています。

  • 1。非相補的塩基に一致する場合。
  • 0。参照塩基に一致する場合。
  • -1。設定されていない場合。

クエリは次のテーブルに対して実行されます。

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

このクエリを実行すると、次の結果が返されます。

Row 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

他の繰り返しフィールドについては、次のどちらかを実行できます。

  • それらを単一のフィールドとして連結する(ARRAY_TO_STRING などの関数を使用)。
  • それらを FROM 句または LEFT JOIN 句に追加してフラット化する。

クエリの結果を新しいテーブルに実体化するには、クエリ結果からのテーブルの作成をご覧ください。

このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...