このページでは、BigQuery を使用してバリアントを分析する高度な方法について説明します。
このチュートリアルのデータは Illumina Platinum Genomes プロジェクトから取得しています。データは、BigQuery バリアント スキーマを使用する BigQuery テーブルに読み込みました。テーブルの名前は platinum_genomes_deepvariant_variants_20180823
です。
バリアント データが BigQuery バリアント スキーマを使用する BigQuery テーブルにある場合、このチュートリアルのクエリを自分のデータに適用するのは簡単です。バリアント データを BigQuery に読み込む方法については、変換パイプラインの使用に関するドキュメントをご覧ください。
目標
このチュートリアルでは、次のことを行う方法について説明します。
- データの概要を把握する
- 非バリアント セグメントがどのように表現されているかを知る
- バリアント コールがどのように表現されているかを知る
- バリアント コールの品質フィルタがどのように表現されているかを知る
- 階層列を集約する
- クエリを要約する
- 個別の行をカウントする
- 行をグループ化する
- ユーザー定義関数を作成する
このチュートリアルでは、次の情報を調べる方法も説明します。
- テーブル内の行数。
- バリアント コールの数
- 各サンプルに対してコールされたバリアントの数
- サンプルの数
- 染色体あたりのバリアントの数
- サンプルあたりの高品質バリアントの数
費用
このドキュメントでは、Google Cloud の次の課金対象のコンポーネントを使用します。
- BigQuery
料金計算ツールを使うと、予想使用量に基づいて費用の見積もりを生成できます。
始める前に
- 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.
-
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.
-
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] ページでテーブルを表示します。
テーブルに関する情報が表示されます。このテーブルには、105,000,000 行を超える 19.6 GB のデータが含まれています。
[Preview] をクリックして、テーブルの行をいくつか見てみます。
テーブルに対してクエリを実行する
テーブル スキーマとその行のいくつかを確認したら、クエリの発行とデータの分析を始めます。次に進む前に、BigQuery で使われている標準 SQL クエリ構文を理解していることを確認してください。
テーブル内の行の総数を数える
テーブルの行数を表示するには:
Google Cloud Console の [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
テーブル内のバリアント コールを数える
テーブルの各行にはゲノム位置があり、これはバリアント セグメントのことも非バリアント セグメントのこともあります。
各行には、バリアント コールの 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 クエリプランの説明をご覧ください。
このページの例では、テーブル内のバリアント コールの数を数えるの例のように、同じクエリを作成する複数の方法を説明していることがあります。どのクエリ方法が最適かを判断するには、さまざまなクエリの実行時間と処理されるデータのバイト数を調べてください。
クリーンアップ
チュートリアルが終了したら、作成したリソースをクリーンアップして、割り当ての使用を停止し、課金されないようにできます。次のセクションで、リソースを削除または無効にする方法を説明します。
課金をなくす最も簡単な方法は、チュートリアル用に作成したプロジェクトを削除することです。
プロジェクトを削除するには:
- In the Google Cloud console, go to the Manage resources page.
- 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.