本頁面說明使用 BigQuery 分析變異基因段的進階方法。
本教學課程中的資料來自 Illumina Platinum Genomes 專案。且已載入到使用 BigQuery 變體結構定義的 BigQuery 表格中。資料表名稱為 platinum_genomes_deepvariant_variants_20180823。
如果您的變體資料位於使用 BigQuery 變體結構定義的 BigQuery 表格中，便可將本教學課程中的查詢輕鬆套用至您的資料。如要瞭解如何將變體資料載入到 BigQuery 中，請參閱關於使用轉換管道的說明文件。
目標
本教學課程說明如何執行下列操作：
- 大致瞭解基因體學資料。
- 瞭解如何表示非變體片段。
- 瞭解如何表示變體識別。
- 瞭解如何表示變體識別品質篩選器。
- 匯總階層式資料欄。
- 壓縮查詢。
- 計算不同的資料列數。
- 將列分組。
- 撰寫使用者定義函式。
本教學課程也說明如何找出下列資訊：
- 資料表中的資料列數
- 變體識別數
- 針對每個樣本識別的變體數
- 樣本數
- 每個染色體的變體數
- 每個樣本的高品質變體數
費用
在本文件中，您會使用 Google Cloud的下列計費元件：
- BigQuery
如要根據預測用量估算費用，請使用 Pricing Calculator。
事前準備
- 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 變體結構定義。
- 使用長度為零的
alternate_bases值 - 使用文字字串
<NON_REF>做為alternate_bases.alt值 - 使用文字字串
<*>做為alternate_bases.alt值 - 染色體
1中10個鹼基的參考區塊 - 參考區塊的起始位置為
1000 - 位於
1000位置的參考鹼基為A - 該區塊其他位置的參考鹼基並未顯示出來
在 Google Cloud 控制台的 BigQuery 頁面中查看資料表。
資料表相關資訊會顯示出來。該資料表包含 19.6 GB 的資料，和 105,000,000 個以上的資料列。
按一下 [Preview] (預覽)，查看資料表中的部分資料列。
前往 Google Cloud 控制台的「BigQuery」頁面。
按一下 [Compose query] (撰寫查詢)。
複製以下查詢，然後貼到「New Query」(新查詢) 文字區域中：
#standardSQL SELECT COUNT(1) AS number_of_rows FROM `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823`
按一下 [Run query] (執行查詢)。查詢會傳回下列結果：
列 number_of_rows 1 105923159 - 計算至少包含一個變體識別 (且至少有一個大於 0 的基因型) 的所有資料列數量。
- 按照染色體為變體資料列分組並計算每個群組數。
- 計算至少包含一個變體識別 (且至少有一個大於 0 的基因型) 的所有資料列數量。
- 按照染色體為變體資料列分組並計算每個群組數。
- 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.
查看資料表結構定義與資料
存取資料表並查看結構定義
Illumina Platinum Genomes platinum_genomes_deepvariant_variants_20180823資料表可公開存取。
資料表中的變體與非變體
Illumina Platinum Genomes 資料使用 gVCF 格式，這表示資料表中有包含非變體的資料列。這些非變體也稱為「參考識別」。
在資料表中，非變體片段通常以下列方式表示：
非變體片段的表示方式通常取決於產生來源資料的變體識別端。platinum_genomes_deepvariant_variants_20180823 資料表中的變異基因段已使用 DeepVariant 識別，且該 DeepVariant 使用 <*> 標記法。
下表顯示包含表示非變體片段之值的部分資料列。區隔會顯示下列資訊：
在下表中，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 資料表中的資料，請完成下列步驟：
查詢資料表
查看資料表結構定義及其部分資料列之後，請開始發出查詢及分析資料。在繼續操作之前，請確保您熟悉 BigQuery 使用的標準 SQL 查詢語法。
計算資料表中的資料列總數
如要查看資料表中的資料列數，請執行下列操作：
計算資料表中的變體識別數
資料表中的每個資料列都有一個為變體或非變體片段的基因體位置。
每個資料列也都包含一個 call 資料欄，這個資料欄是變體識別的 ARRAY。每個 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
使用 JOIN 針對含有 call 資料欄的每個資料列，計算所有樣本的變體識別總數。查詢使用逗號 (,) 運算子，這是用於 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 數
計算所有樣本變體識別總數的第三種方式，是計算 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，因此結果顯示資料表中的大多數資料列都是非變體片段。
如資料表中的變體與非變體相關章節所示，您可以使用至少三種方式將變體資料列分類為非變體片段。在上述查詢中，WHERE 子句所含資料列的 alternate_bases 資料欄擁有真實變體的值，這表示其並非 <*> 或 <NON_REF> 等特殊標記值。
針對資料表中的每個資料列，系統會透過該資料列的 alternate_bases 資料欄發出子查詢，進而針對並非 <NON_REF> 或 <*> 的每個 alternate_bases 值傳回值 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，資料表中的每個變體都有零或多個值。特定 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
執行這項查詢會傳回六個資料列。每個 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 |
變體數現在更接近 6 百萬，對於人類而言，這是更常見的值。請繼續下一節，按照基因型篩選真實變體。
按照基因型篩選真實變體
資料表中的變體包含無檢測結果 (No-call)，由 genotype 值 -1 表示。系統不會將這些變體視為個人的真實變體，因此您必須將這些變體篩選出來。真實變體只能包含基因型大於零的識別。如果某項識別僅包含無檢測結果 (-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 的值。如要查詢並取得資料列數量的值，請執行下列查詢：
#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 |
計算每個染色體的變體數
如要計算每個染色體的變體數，請執行下列查詢。這項查詢會執行下列動作：
#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)：
#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 語法與使用者定義的函式逐步壓縮查詢。
如計算每個染色體的變體數相關章節所述，查詢具有下列需求條件：
撰寫這項查詢可能會很複雜，因為如要完成第一項工作，您必須查看 ARRAY (call) 內的 ARRAY (genotype)，同時將查詢的執行環境保持在資料列層級。您會在資料列層級保留查詢的執行環境，因為您想要產生每個變體的結果，而不是每個 call 或每個 genotype 的結果。
UNNEST 函式可讓您對 ARRAY 資料欄執行查詢，就像該資料欄是資料表一樣。這個函式會針對 ARRAY 的每個元素傳回一個資料列。同時不會變更查詢環境。在 WHERE 子句的 EXISTS 子查詢中使用 UNNEST 函式：
#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 子句變更為對具有 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」。
如要按照數字順序對輸出進行排序，請先從 reference_name 資料欄中移除「chr」前置字串，並為其提供別名 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
這項查詢會傳回正確輸出：
| 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，而不是傳回錯誤。
做為對輸出的最後一項改善，請再次顯示 reference_name 資料欄，而不是將其設定為 chromosome 別名。如要執行此操作，請將 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 提供開始查詢之後經過的秒數，以及查詢已處理的位元組數等相關資料。如要瞭解如何最佳化查詢，請參閱 BigQuery 查詢計畫說明。
這個頁面中的部分範例 (例如計算資料表中的變體識別數) 說明了撰寫查詢的多種方式。如要決定最適合您的查詢方法，請檢查不同查詢的持續時間，並瞭解查詢處理的資料位元組數。
清除所用資源
完成教學課程後，您可以清除所建立的資源，這樣資源就不會繼續使用配額，也不會產生費用。下列各節將說明如何刪除或關閉這些資源。
如要避免付費，最簡單的方法就是刪除您為了本教學課程所建立的專案。
如要刪除專案：