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

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

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

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

目標

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

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

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

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

費用

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

  • BigQuery

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

始める前に

  1. 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.
  2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

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

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

    Go to project selector

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

  6. 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 を使用して呼び出されました。

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

  • 染色体 110 ベースの参照ブロック
  • 参照ブロックは位置 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 テーブルのデータを表示するには、次の手順を行います。

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

    BigQuery ページに移動

    テーブルに関する情報が表示されます。このテーブルには、105,000,000 行を超える 19.6 GB のデータが含まれています。

  2. [Preview] をクリックして、テーブルの行をいくつか見てみます。

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

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

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

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

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

    BigQuery ページに移動

  2. [クエリを新規作成] をクリックします。

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

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

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

    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 という値がある場合は、その列にはその他の値が含まれていないはずです。これを検証するには、以下のクエリを実行します。このクエリでは、FILTERPASS 値を含まないすべてのコールを除外します。

#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 つ以上存在するすべての行を数える
  • バリアント行を染色体別にグループ化し、各グループの行数を数える

最初のタスクを完了するには、行レベルでクエリの実行コンテキストを維持しながら、ARRAYgenotype)を ARRAYcall)内で調べる必要があるため、このクエリの記述は複雑になる可能性があります。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 クエリプランの説明をご覧ください。

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

クリーンアップ

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

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

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

  1. In the Google Cloud console, go to the Manage resources page.

    Go to Manage resources

  2. In the project list, select the project that you want to delete, and then click Delete.
  3. In the dialog, type the project ID, and then click Shut down to delete the project.

次のステップ