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

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

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

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

目標

このチュートリアルを完了すると、以下の方法がわかります。

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

このチュートリアルでは、以下の確認方法も説明します。

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

費用

このチュートリアルでは、Google Cloud の課金対象となる以下のコンポーネントを使用します。

  • BigQuery

料金計算ツールを使うと、予想使用量に基づいて費用の見積もりを出すことができます。Cloud Platform を初めて使用する方は、無料トライアルをご利用いただけます。

始める前に

  1. Google アカウントにログインします。

    Google アカウントをまだお持ちでない場合は、新しいアカウントを登録します。

  2. Google Cloud Console の [プロジェクト セレクタ] ページで、Google Cloud プロジェクトを選択または作成します。

    [プロジェクトの選択] ページに移動

  3. Cloud プロジェクトに対して課金が有効になっていることを確認します。プロジェクトに対して課金が有効になっていることを確認する方法を学習する

  4. 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 <*>

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

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

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

platinum_genomes_deepvariant_variants_20180823 テーブルのデータを表示するには:

  1. BigQuery UI の [Details] ページに移動します

    テーブルに関する情報が表示されます。19.6 GB 分のデータが格納され、行数は 105,000,000 を超えていることがわかります。

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

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

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

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

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

  1. BigQuery UI に移動します。

    BigQuery UI に移動する

  2. [Compose query] をクリックします。

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

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

  4. [Run query] をクリックします。このクエリを実行すると、次の結果が返されます。

    Row number_of_rows
    1 105923159

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

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

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

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

call 配列の長さの合計

全サンプルのバリアント コールの総数を計算する方法の 1 つは、次のように各 call 配列の長さを合計することです。

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

このクエリを実行すると、正しい値(182,104,652)が返されます。

Row number_of_calls
1 182104652

各行の JOIN

全サンプルのバリアント コールの総数を数える 2 つ目の方法は、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

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

このクエリを実行すると、正しい値(182,104,652)が返されます。

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

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

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

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

Row 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 の値はそれぞれ、ゲノム解析対象の個人に対応します。

Row 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

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

Row 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

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

Row 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

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

Row 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)と各染色体のバリアント行の数が返されます。

Row 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

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

Row 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

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

Row 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

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

Row 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

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

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

このクエリをさらに簡潔にするには、EXISTS 句を、JOINcall 列)と call.genotype 列に変更します。すでに説明したように、カンマ演算子は 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 別名を使用します。しかし、出力はまだ次のように文字列として並べられています。

Row 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

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

Row 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 クエリプランの説明をご覧ください。

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

クリーンアップ

チュートリアルが完了したら、Google Cloud で作成したリソースをクリーンアップして、今後料金が発生しないようにします。以下のセクションでは、このようなリソースを削除または無効にする方法を説明します。

プロジェクトの削除

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

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

  1. Cloud Console で、プロジェクト ページに移動します。

    プロジェクト ページに移動

  2. プロジェクト リストで、削除するプロジェクトを選択し、[プロジェクトの削除] をクリックします。 プロジェクト名の横にあるチェックボックスをオンにし、[プロジェクトの削除] をクリックします。
  3. ダイアログでプロジェクト ID を入力し、[シャットダウン] をクリックしてプロジェクトを削除します。

次のステップ