Cloud Life Sciences は非推奨となり、2025 年 7 月 8 日以降は Google Cloud で使用できなくなります。Cloud Life Sciences のユースケースが Batch でサポートされるようになりました。ワークロードの移行方法については、Batch に移行するをご覧ください。

BigQuery を使用したバリアント分析の高度なガイド

このページでは、BigQuery を使用してバリアントを分析する高度な方法について説明します。

このチュートリアルのデータは Illumina Platinum Genomes プロジェクトから取得しています。データは、BigQuery バリアントスキーマを使用する BigQuery テーブルに読み込みました。テーブルの名前は platinum_genomes_deepvariant_variants_20180823 です。

バリアントデータが BigQuery バリアントスキーマを使用する BigQuery テーブルにある場合、このチュートリアルのクエリを自分のデータに適用するのは簡単です。バリアントデータを BigQuery に読み込む方法については、変換パイプラインの使用に関するドキュメントをご覧ください。

目標

このチュートリアルでは、次のことを行う方法について説明します。

データの概要を把握する
非バリアントセグメントがどのように表現されているかを知る
バリアントコールがどのように表現されているかを知る
バリアントコールの品質フィルタがどのように表現されているかを知る
階層列を集約する
クエリを要約する
個別の行をカウントする
行をグループ化する
ユーザー定義関数を作成する

このチュートリアルでは、次の情報を調べる方法も説明します。

テーブル内の行数。
バリアントコールの数
各サンプルに対してコールされたバリアントの数
サンプルの数
染色体あたりのバリアントの数
サンプルあたりの高品質バリアントの数

費用

このドキュメントでは、Google Cloud の次の課金対象のコンポーネントを使用します。

BigQuery

料金計算ツールを使うと、予想使用量に基づいて費用の見積もりを生成できます。新しい Google Cloud ユーザーは無料トライアルをご利用いただける場合があります。

始める前に

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

Go to project selector

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.

Go to project selector

Make sure that billing is enabled for your Google Cloud project.

BigQuery のバリアントスキーマに精通している必要があります。

テーブルのスキーマとデータの表示

テーブルにアクセスしてスキーマを表示する

Illumina Platinum Genomes platinum_genomes_deepvariant_variants_20180823 テーブルは、一般公開されています。

テーブル内のバリアントと非バリアント

Illumina Platinum Genomes データでは gVCF 形式が使用されているため、テーブルには非バリアントを含む行もあります。これらの非バリアントは、「参照コール」とも呼ばれます。

テーブルで、非バリアントセグメントは一般に次のように表されます。

長さ 0 の alternate_bases の値を含む
alternate_bases.alt の値としてテキスト文字列 <NON_REF> を含む
alternate_bases.alt の値としてテキスト文字列 <*> を含む

非バリアントセグメントの表現方法は、一般にソースデータを生成したバリアントコールの実行元によって異なります。platinum_genomes_deepvariant_variants_20180823 テーブルのバリアントは、<*> 表記を使用する DeepVariant を使用して呼び出されました。

次の表に、非バリアントセグメントを表す値を含むいくつかの行を示します。セグメントには次の情報が表示されます。

染色体 1 の 10 ベースの参照ブロック
参照ブロックは位置 1000 から始まります。
位置 1000 の参照ベースは A です。
ブロックの他の位置の参照ベースは示されていません。

次の表では、alternate_bases 型の REPEATED RECORD 列の中には値がありません。つまり、これは長さ 0 の ARRAY です。

reference_name	start_position	end_position	reference_bases	alternate_bases.alt
1	1000	1010	A

次の表では、alternate_bases 型の REPEATED RECORD 列の長さが 1 で、この中にリテラル文字列 <*> が格納されています。

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

このガイドで使用しているクエリでは、上の表の表現を使用しています。

ゲノムの非バリアント位置の表現の詳細については、VCF 仕様をご覧ください。

テーブルのデータを表示する

platinum_genomes_deepvariant_variants_20180823 テーブルのデータを表示するには、次の手順を行います。

Google Cloud Console の [BigQuery] ページでテーブルを表示します。

BigQuery ページに移動

テーブルに関する情報が表示されます。このテーブルには、105,000,000 行を超える 19.6 GB のデータが含まれています。
[Preview] をクリックして、テーブルの行をいくつか見てみます。

テーブルに対してクエリを実行する

テーブルスキーマとその行のいくつかを確認したら、クエリの発行とデータの分析を始めます。次に進む前に、BigQuery で使われている標準 SQL クエリ構文を理解していることを確認してください。

テーブル内の行の総数を数える

テーブルの行数を表示するには:

Google Cloud Console の [BigQuery] ページに移動します。

BigQuery ページに移動
[クエリを新規作成] をクリックします。

次のクエリをコピーして [New Query] テキストボックスに貼り付けます。

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

[クエリを実行] をクリックします。次の結果が返されます。

行 number_of_rows

1 105923159

行	number_of_rows
1	105923159

テーブル内のバリアントコールを数える

テーブルの各行にはゲノム位置があり、これはバリアントセグメントのことも非バリアントセグメントのこともあります。

各行には、バリアントコールの ARRAY である call 列も含まれています。各 call 列には、name とその他の値が含まれます。これらは、遺伝子型、品質列、読み取り深度など、VCF ファイルで一般的に見られる値です。

バリアントコールの数をカウントするには、ARRAY 列内の要素の数をクエリします。これは、以下に示すいくつかの方法で行うことができます。どのクエリも返す値は 182,104,652 です。つまり、このデータセット内の行あたりのバリアントコールの数の平均は 1.7 です。

`call` 配列の長さの合計

各 call 配列の長さを加算して、すべてのサンプルにわたるバリアントコールの総数をカウントします。

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

次の結果が返されます。

行	number_of_calls
1	182104652

各行の `JOIN`

call 列に各行の JOIN を使用して、すべてのサンプルにわたるバリアントコールの総数をカウントします。クエリではカンマ（,）演算子を使用しています。これは JOIN に使用される簡略表記です。call 列に結合すると、call 列で暗黙的な UNNEST オペレーションが行われます。

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

次の結果が返されます。

行	number_of_calls
1	182104652

`call` 列の `name` のカウント

全サンプルのバリアントコールの総数を計算する 3 つ目の方法は、call 列の name 値をカウントすることです。各 call 列は単一の name 値を持つ必要があるため、次のクエリを実行できます。

#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

次の結果が返されます。

行	number_of_calls
1	182104652

バリアントセグメントと非バリアントセグメントの数を数える

テーブル内のバリアントセグメントと非バリアントセグメントの数を数えるには、初めに、次のクエリを実行して非バリアントセグメントを除外します。

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

次の結果が返されます。

行	number_of_real_variants
1	38549388

バリアントコールを数えるで説明したように、このテーブル内のバリアントコールの総数は 182,104,652 であるため、この結果はテーブル内の大部分の行が非バリアントセグメントであることを示しています。

テーブル内のバリアントと非バリアントに関するセクションに示されているように、バリアント行を非バリアントセグメントとして分類する方法は少なくとも 3 つあります。先述のクエリでは、WHERE 句に、alternate_bases 列が真のバリアントである値（つまり、<*> や <NON_REF> などの特別なマーカー値でないことを意味する）を持つ行が含まれます。

テーブルの各行について、その行の alternate_bases 列に対してサブクエリが発行され、alternate_bases の値のうち、<NON_REF でも <*> でもないものが見つかるたびに値 1 が返されます。このサブクエリで返される行の数が、バリアントセグメントの数です。

次のクエリは、非バリアントセグメントの数を数える方法を示しています。

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

次の結果が返されます。

行	number_of_non_variants
1	143555264

真のバリアントの数 38,549,388 を非バリアントセグメントの数 143,555,264 に足すと、バリアントコールの総数に等しくなります。

コールされたバリアントの数をサンプル別に数える

テーブル内の最上位の行を調べたので、次にその子に当たる行のクエリを開始します。これらの行の内容は、たとえば、バリアントに対してコールが行われた個々のサンプルなどのデータです。

このテーブル内の各バリアントには、call.name の値が 0 個以上あります。ある call.name の値が複数の行に存在することもあります。

各コールセットが存在する行の数を数えるには、次のクエリを実行します。

#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

このクエリを実行すると、6 行が返されます。call_name の値はそれぞれ、ゲノム解析対象の個人に対応します。

行	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

通常、人間は call_count_for_call_set の値に示される 3 千万個のバリアントを持つことはありません。非バリアントセグメントを除外して、バリアント行だけを数えます。

#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

次の結果が返されます。

行	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

これで、バリアントの数は 600 万に近くなり、より人間に適したものになりました。次のセクションに進み、遺伝子型で真のバリアントを抽出します。

遺伝子型で真のバリアントだけを抽出する

このテーブル内のバリアントにはコールなしも含まれており、これは genotype の -1 という値で表現されます。これらのバリアントは個人の真のバリアントとみなされないため、それらをフィルタで除外する必要があります。真のバリアントには、0 より大きい遺伝子型を持つコールのみが含まれます。あるコールに含まれている遺伝子型がコールなし（-1）または参照（0）だけである場合は、真のバリアントではありません。

遺伝子型でバリアントを抽出するには、次のクエリを実行します。

#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

次の結果が返されます。

行	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

テーブル内のサンプルの数を数える

コールされたバリアントの数をサンプル別に数えるで、各クエリは call_name の値を持つ行を 6 つ返しました。クエリを実行して行数の値を取得するには、次のクエリを実行します。

#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

次の結果が返されます。

行	number_of_callsets
1	6

染色体あたりのバリアントの数を数える

染色体あたりのバリアントの数を数えるには、次のクエリを実行します。クエリは次を行います。

0 より大きい遺伝子型が 1 つ以上含まれているバリアントコールが 1 つ以上存在するすべての行を数える
バリアント行を染色体別にグループ化し、各グループの行数を数える

#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

このクエリを実行すると、染色体の名前（reference_name）と各染色体のバリアント行の数が返されます。

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

サンプルあたりの高品質のバリアントの数を数える

`FILTER` 値を複数持つコールをクエリする

VCF 仕様で説明されているように、FILTER 列を使用すると、バリアントコールの品質に応じてラベルを付けることができます。

次のクエリは、データセットのバリアントコールごとの FILTER 値を表示する方法を示しています。

#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

次の結果が返されます。

行	call_filter	number_of_calls
1	RefCall	11681534
2	PASS	26867854

PASS という値は、バリアントコールが高品質であることを示します。

高品質のバリアントコールを `FILTER` で抽出する

バリアントの分析時に、低品質のバリアントの除外が必要になる場合があります。FILTER 列に PASS という値がある場合は、その列にはその他の値が含まれていないはずです。これを検証するには、以下のクエリを実行します。このクエリでは、FILTER に PASS 値を含まないすべてのコールを除外します。

#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

このクエリを実行すると、結果は何も返されません。

各サンプルのすべての高品質コールの数を数える

次のクエリは、各コールセットのすべてのコール（バリアントと非バリアント）の数を数える方法を示しています。ただし、コールのフィルタが 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

次の結果が返されます。

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

各サンプルのすべての高品質の真のバリアントコールの数を数える

次のクエリは、各サンプルのすべてのコール（バリアントと非バリアント）の数を数える方法を示しています。コールのうち、フィルタが PASS 以外のものはすべて除外され、真のバリアント（genotype > 0）が 1 つ以上あるものだけが対象となります。

#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

次の結果が返されます。

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

ベストプラクティス

クエリの要約

クエリが複雑になるときは、簡潔さを維持することが重要です。そのロジックが正確で、たどりやすい状態にするためです。

次の例では初めに、染色体あたりのバリアント数を数えるクエリを示します。次に、SQL 構文とユーザー定義関数を使用してこのクエリを要約する方法を詳しく説明します。

染色体あたりのバリアントの数を数えるのセクションで説明したように、このクエリには次の要件があります。

0 より大きい遺伝子型が 1 つ以上含まれているバリアントコールが 1 つ以上存在するすべての行を数える
バリアント行を染色体別にグループ化し、各グループの行数を数える

最初のタスクを完了するには、行レベルでクエリの実行コンテキストを維持しながら、ARRAY（genotype）を ARRAY（call）内で調べる必要があるため、このクエリの記述は複雑になる可能性があります。call または genotype ごとの結果ではなく、バリアントごとの結果を生成するため、クエリの実行コンテキストは行レベルで維持します。

UNNEST 関数を使用すると、ARRAY 型の列に対するクエリを、その列がテーブルであるかのように実行できます。この関数は、ARRAY の要素ごとに 1 行を返します。また、クエリのコンテキストを変更することもありません。UNNEST 関数を WHERE 句の中の EXISTS サブクエリで使用できます。

#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

このクエリで返される結果は、染色体あたりのバリアントの数を数えるの例で返されるものと同じです。

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

EXISTS 句を call.genotype 列で call 列の JOIN に変更することで、クエリを簡潔にできます。カンマ演算子は 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

クエリは機能し、簡潔ですが、出力を染色体（reference_name）の数値昇順に並べ替えることができません。これは、reference_name の値が文字列型であり、各値の中に接頭辞「chr」があるからです。

出力を数値として並べ替えるには、初めに「chr」接頭辞を reference_name 列から除去して 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

このクエリでは、REGEXP_REPLACE 関数を使用して「chr」接頭辞文字列を空の文字列に置き換えます。このクエリでは次に、GROUP BY 関数と ORDER BY 関数が変更されて、計算された chromosome エイリアスが使用されます。出力はまだ次のように文字列として並べられています。

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

代わりに出力を数値として並べ替えるには、chromosome 列を文字列から整数にキャストします。

#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

染色体名の一部（「X」、「Y」、「M」など）が数値ではないため、クエリを実行するとエラーが返されます。代わりに、CASE 関数を使用して染色体 1～9 の前に「0」を付加するとともに接頭辞「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

クエリは次のように正しい出力を返します。

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

このクエリは SAFE_CAST 関数を使用します。この関数は、エラーを返す代わりに、染色体 X、Y、M の NULL を返します。

出力を改善する最後の方法として、chromosome 別名に設定する代わりに reference_name 列を再度表示します。このようにするには、次のように CASE 句を 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

この最後のクエリは、染色体あたりのバリアントの数を数えるで示したクエリと同じです。

ユーザー定義関数を作成する

BigQuery は、ユーザー定義関数をサポートしています。ユーザー定義関数を使用すると、別の SQL 式や JavaScript などの別のプログラミング言語を使用して関数を作成できます。

クエリの要約の例では、複雑なクエリを作成する方法を示していますが、このクエリは複雑すぎます。

次のクエリは、CASE ロジックを関数に移動することによって、クエリを簡潔にする方法を示しています。

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

次のクエリも、クエリを簡潔にする方法を示していますが、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)

どちらのクエリも正しい結果を返しますが、そのロジックはより簡潔になっています。

クエリのパフォーマンスの向上とコストの削減

BigQuery の料金は、クエリのために処理されるバイトの数に基づきます。クエリのパフォーマンスも、処理されるデータ量が少ないほど向上します。BigQuery UI には、クエリの開始からの経過時間（秒数）とそのクエリで処理されたバイト数に関するデータが表示されます。クエリを最適化する方法については、BigQuery クエリプランの説明をご覧ください。

このページの例では、テーブル内のバリアントコールの数を数えるの例のように、同じクエリを作成する複数の方法を説明していることがあります。どのクエリ方法が最適かを判断するには、さまざまなクエリの実行時間と処理されるデータのバイト数を調べてください。

クリーンアップ

チュートリアルが終了したら、作成したリソースをクリーンアップして、割り当ての使用を停止し、課金されないようにできます。次のセクションで、リソースを削除または無効にする方法を説明します。

課金をなくす最も簡単な方法は、チュートリアル用に作成したプロジェクトを削除することです。

プロジェクトを削除するには:

注意: プロジェクトを削除すると、次のような影響があります。

プロジェクト内のすべてのものが削除されます。このドキュメントのタスクで既存のプロジェクトを使用した場合、それを削除すると、そのプロジェクトで行った他の作業もすべて削除されます。
カスタムプロジェクト ID が失われます。このプロジェクトを作成したときに、将来使用するカスタムプロジェクト ID を作成した可能性があります。そのプロジェクト ID を使用した URL（たとえば、appspot.com）を保持するには、プロジェクト全体ではなくプロジェクト内の選択したリソースだけを削除します。

複数のアーキテクチャ、チュートリアル、クイックスタートを実施する予定がある場合は、プロジェクトを再利用すると、プロジェクトの割り当て上限を超えないようにすることができます。

In the Google Cloud console, go to the Manage resources page.
Go to Manage resources
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.

BigQuery を使用したバリアント分析の高度なガイド

目標

費用

始める前に

テーブルのスキーマとデータの表示

テーブルにアクセスしてスキーマを表示する

テーブル内のバリアントと非バリアント

テーブルのデータを表示する

テーブルに対してクエリを実行する

テーブル内の行の総数を数える

テーブル内のバリアント コールを数える

call 配列の長さの合計

各行の JOIN

call 列の name のカウント

バリアント セグメントと非バリアント セグメントの数を数える

コールされたバリアントの数をサンプル別に数える

遺伝子型で真のバリアントだけを抽出する

テーブル内のサンプルの数を数える

染色体あたりのバリアントの数を数える

サンプルあたりの高品質のバリアントの数を数える

FILTER 値を複数持つコールをクエリする

高品質のバリアント コールを FILTER で抽出する

各サンプルのすべての高品質コールの数を数える

各サンプルのすべての高品質の真のバリアント コールの数を数える

ベスト プラクティス

クエリの要約

ユーザー定義関数を作成する

クエリのパフォーマンスの向上とコストの削減

クリーンアップ

次のステップ

テーブル内のバリアントコールを数える

`call` 配列の長さの合計

各行の `JOIN`

`call` 列の `name` のカウント

バリアントセグメントと非バリアントセグメントの数を数える

`FILTER` 値を複数持つコールをクエリする

高品質のバリアントコールを `FILTER` で抽出する

各サンプルのすべての高品質の真のバリアントコールの数を数える

ベストプラクティス