Panduan lanjutan untuk menganalisis varian menggunakan BigQuery

Halaman ini menjelaskan metode lanjutan tentang cara menggunakan BigQuery untuk menganalisis varian.

Data dalam tutorial ini berasal dari project Illumina Platinum Genomes. Data dimuat ke tabel BigQuery yang menggunakan skema varian BigQuery. Nama tabelnya adalah platinum_genomes_deepvariant_variants_20180823.

Jika data varian berada di tabel BigQuery yang menggunakan skema varian BigQuery, mudah untuk menerapkan kueri dalam tutorial ini ke data Anda. Untuk mengetahui informasi tentang cara memuat data varian ke BigQuery, lihat dokumentasi tentang penggunaan pipeline transformasi.

Tujuan

Tutorial ini menunjukkan cara melakukan hal berikut:

• Dapatkan ringkasan data genomik.
• Cari tahu cara segmen non-varian direpresentasikan.
• Cari tahu cara panggilan varian direpresentasikan.
• Cari tahu cara filter kualitas panggilan varian ditampilkan.
• Kolom hierarki agregat.
• Kueri yang ringkas.
• Menghitung baris yang berbeda-beda.
• Kelompokkan baris.
• Menulis fungsi yang ditentukan pengguna.

Tutorial ini juga menunjukkan cara menemukan informasi berikut:

• Jumlah baris dalam tabel
• Jumlah panggilan varian
• Varian dipanggil untuk setiap sampel
• Jumlah sampel
• Varian per kromosom
• Varian berkualitas tinggi per sampel

Biaya

Dalam dokumen ini, Anda menggunakan komponen Google Cloud yang dapat ditagih berikut:

• BigQuery

Untuk membuat perkiraan biaya berdasarkan proyeksi penggunaan Anda, gunakan kalkulator harga. Pengguna baru Google Cloud mungkin memenuhi syarat untuk mendapatkan uji coba gratis.

Sebelum memulai

1. Login ke akun Google Cloud Anda. Jika Anda baru menggunakan Google Cloud, buat akun untuk mengevaluasi performa produk kami dalam skenario dunia nyata. Pelanggan baru juga mendapatkan kredit gratis senilai $300 untuk menjalankan, menguji, dan men-deploy workload.

2. Di konsol Google Cloud, pada halaman pemilih project, pilih atau buat project Google Cloud.

   Catatan: Jika tidak berencana untuk menyimpan resource yang Anda buat dalam prosedur ini, buatlah project, bukan memilih project yang ada. Setelah menyelesaikan langkah ini, Anda dapat menghapus project, yang menghapus semua resource yang terkait dengan project ini.

   Buka pemilih project

3. Pastikan penagihan telah diaktifkan untuk project Google Cloud Anda.

4. Di konsol Google Cloud, pada halaman pemilih project, pilih atau buat project Google Cloud.

   Catatan: Jika tidak berencana untuk menyimpan resource yang Anda buat dalam prosedur ini, buatlah project, bukan memilih project yang ada. Setelah menyelesaikan langkah ini, Anda dapat menghapus project, yang menghapus semua resource yang terkait dengan project ini.

   Buka pemilih project

5. Pastikan penagihan telah diaktifkan untuk project Google Cloud Anda.

6. Anda seharusnya sudah memahami skema varian BigQuery.

Melihat data dan skema tabel

Mengakses tabel dan melihat skema

Tabel platinum_genomes_deepvariant_variants_20180823 Illumina Platinum Genomes tersedia secara publik.

Varian dan non-varian dalam tabel

Data Illumina Platinum Genomes menggunakan format gVCF, yang berarti ada baris dalam tabel yang menyertakan non-varian. Non-varian ini juga dikenal sebagai "panggilan referensi".

Dalam tabel, segmen non-varian umumnya direpresentasikan dengan cara berikut:

• Dengan nilai alternate_bases panjang nol
• Dengan string teks <NON_REF> sebagai nilai alternate_bases.alt
• Dengan string teks <*> sebagai nilai alternate_bases.alt

Cara segmen non-varian direpresentasikan biasanya bergantung pada pemanggil varian yang menghasilkan data sumber. Varian dalam tabel platinum_genomes_deepvariant_variants_20180823 telah dipanggil menggunakan DeepVariant, yang menggunakan notasi <*>.

Tabel berikut menunjukkan beberapa baris berisi nilai yang mewakili segmen non-varian. Segmen tersebut menampilkan informasi berikut:

• Blok referensi 10 berdasarkan kromosom 1
• Blok referensi dimulai pada posisi 1000
• Basis referensi di posisi 1000 adalah A
• Basis referensi pada posisi lain blok tidak ditampilkan

Dalam tabel berikut, kolom alternate_bases REPEATED RECORD tidak berisi nilai, yang berarti kolom tersebut adalah ARRAY dengan panjang 0.

reference_name	start_position	end_position	reference_bases	alternate_bases.alt
1	1.000	1010	A

Dalam tabel berikut, kolom alternate_bases REPEATED RECORD memiliki panjang 1, dan berisi string teks literal <*>.

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

Kueri yang digunakan dalam panduan ini menggunakan representasi dalam tabel sebelumnya.

Lihat spesifikasi VCF untuk mengetahui informasi selengkapnya tentang cara merepresentasikan posisi non-varian dalam genom.

Menampilkan data tabel

Untuk melihat data dalam tabel platinum_genomes_deepvariant_variants_20180823, selesaikan langkah-langkah berikut:

1. Lihat tabel di halaman BigQuery di Konsol Google Cloud.

   Buka halaman BigQuery

   Informasi tentang tabel akan muncul. Tabel ini berisi 19,6 GB data dan memiliki lebih dari 105.000.000 baris.

2. Klik Preview untuk melihat beberapa baris dalam tabel.

Membuat kueri tabel

Setelah melihat skema tabel dan beberapa barisnya, mulailah mengeluarkan kueri dan menganalisis data. Sebelum melanjutkan, pastikan Anda sudah memahami Sintaksis Kueri SQL Standar yang digunakan BigQuery.

Menghitung total baris dalam tabel

Untuk melihat jumlah baris dalam tabel:

1. Buka halaman BigQuery di konsol Google Cloud.

   Buka halaman BigQuery

2. Klik Compose query.

3. Salin dan tempel kueri berikut ke area teks New Query:

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

4. Klik Jalankan kueri. Kueri tersebut menampilkan hasil berikut:

   Baris	number_of_rows
   1	105923159

Menghitung panggilan varian dalam tabel

Setiap baris dalam tabel memiliki posisi genom yang bisa berupa segmen varian atau non-varian.

Setiap baris juga berisi kolom call, yang merupakan ARRAY dari panggilan varian. Setiap kolom call menyertakan name dan nilai lainnya, seperti genotipe, kolom kualitas, kedalaman baca, dan nilai lainnya yang biasanya ditemukan dalam file VCF.

Untuk menghitung jumlah panggilan varian, buat kueri jumlah elemen di dalam kolom ARRAY. Anda dapat melakukannya dengan beberapa cara yang akan ditampilkan berikutnya. Setiap kueri menampilkan nilai 182.104.652, yang berarti ada rata-rata 1,7 panggilan varian per baris dalam set data.

Menjumlahkan panjang array call

Hitung jumlah total panggilan varian di semua sampel dengan menambahkan panjang setiap array call:

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

Kueri tersebut menampilkan hasil berikut:

Baris	number_of_calls
1	182104652

JOIN pada setiap baris

Hitung jumlah total panggilan varian di semua contoh menggunakan JOIN di setiap baris dengan kolom call. Kueri menggunakan operator koma (,), yang merupakan notasi singkat yang digunakan untuk JOIN. Gabungan ke kolom call membuat operasi UNNEST implisit pada kolom call.

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

Kueri tersebut menampilkan hasil berikut:

Baris	number_of_calls
1	182104652

Menghitung name dalam kolom call

Cara ketiga untuk menghitung jumlah total panggilan varian di semua sampel adalah dengan menghitung nilai name di kolom call. Setiap kolom call harus memiliki satu nilai name, sehingga Anda dapat menjalankan kueri berikut:

#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

Kueri tersebut menampilkan hasil berikut:

Baris	number_of_calls
1	182104652

Menghitung segmen varian dan non-varian

Untuk menghitung jumlah segmen varian dan non-varian dalam tabel, jalankan kueri untuk memfilter segmen non-varian terlebih dahulu:

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

Kueri tersebut menampilkan hasil berikut:

Baris	number_of_real_variants
1	38549388

Seperti ditunjukkan dalam Menghitung panggilan varian, jumlah total panggilan varian dalam tabel adalah 182.104.652, sehingga hasilnya menunjukkan bahwa sebagian besar baris dalam tabel adalah segmen non-varian.

Seperti ditunjukkan pada bagian Varian dan non-varian dalam tabel, setidaknya ada tiga cara untuk mengklasifikasikan baris varian sebagai segmen non-varian. Pada kueri di atas, klausa WHERE menyertakan baris tempat kolom alternate_bases memiliki nilai yang merupakan varian benar, yang berarti bahwa kolom tersebut bukan nilai penanda khusus seperti <*> atau <NON_REF>.

Untuk setiap baris dalam tabel, subkueri dikeluarkan melalui kolom alternate_bases dari baris tersebut, yang menampilkan nilai 1 untuk setiap nilai alternate_bases yang bukan <NON_REF> atau <*>. Jumlah baris yang ditampilkan subkueri adalah jumlah segmen varian.

Kueri berikut menunjukkan cara mendapatkan jumlah segmen non-varian:

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

Kueri tersebut menampilkan hasil berikut:

Baris	number_of_non_variants
1	143555264

Menambahkan jumlah varian sebenarnya (38.549.388) ke jumlah segmen non-varian (143.555.264) sama dengan jumlah total panggilan varian.

Menghitung varian yang dipanggil oleh setiap sampel

Setelah memeriksa baris tingkat atas dalam tabel, Anda dapat mulai membuat kueri untuk baris turunan. Baris ini mencakup data seperti setiap sampel yang telah memiliki panggilan yang dilakukan terhadap varian.

Setiap varian dalam tabel memiliki nol atau beberapa nilai untuk call.name. Nilai call.name tertentu dapat muncul di beberapa baris.

Untuk menghitung jumlah baris yang menampilkan setiap kumpulan panggilan, jalankan kueri berikut:

#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

Menjalankan kueri akan mengembalikan enam baris. Setiap call_name sesuai dengan masing-masing manusia yang diurutkan:

Baris	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

Manusia biasanya tidak memiliki 30 juta varian yang ditampilkan dalam nilai untuk call_count_for_call_set. Filter segmen non-varian untuk menghitung baris varian saja:

#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

Kueri tersebut menampilkan hasil berikut:

Baris	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

Jumlah varian kini mendekati 6 juta, yang lebih umum untuk manusia. Lanjutkan ke bagian berikutnya untuk memfilter varian sebenarnya menurut genotipe.

Memfilter varian sebenarnya menurut genotipe

Varian dalam tabel menyertakan tanpa panggilan, yang diwakili oleh nilai genotype -1. Varian ini tidak dianggap sebagai varian benar untuk masing-masing, jadi Anda harus memfilternya. Varian sebenarnya hanya dapat menyertakan panggilan dengan genotipe yang lebih besar dari nol. Jika sebuah panggilan hanya menyertakan genotipe yang tanpa panggilan (-1) atau referensi (0), berarti genotipe tersebut bukan varian sebenarnya.

Untuk memfilter varian berdasarkan genotipe, jalankan kueri berikut:

#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

Kueri tersebut menampilkan hasil berikut:

Baris	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

Menghitung sampel dalam tabel

Dalam Menghitung varian yang dipanggil oleh setiap sampel, setiap kueri menampilkan enam baris dengan nilai untuk call_name. Untuk membuat kueri dan mendapatkan nilai jumlah baris, jalankan kueri berikut:

#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

Kueri tersebut menampilkan hasil berikut:

Baris	number_of_callsets
1	6

Menghitung varian per kromosom

Untuk menghitung jumlah varian per kromosom, jalankan kueri berikut. Kueri akan melakukan hal berikut:

• Menghitung semua baris yang berisi setidaknya satu panggilan varian dengan setidaknya satu genotipe yang lebih besar dari 0.
• Mengelompokkan baris varian berdasarkan kromosom dan menghitung setiap kelompok.

#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

Menjalankan kueri akan menampilkan nama kromosom (reference_name) dan jumlah baris varian untuk setiap kromosom:

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

Menghitung varian berkualitas tinggi per sampel

Membuat kueri panggilan dengan beberapa nilai FILTER

Spesifikasi VCF menjelaskan kolom FILTER yang dapat Anda gunakan untuk memberi label panggilan varian dengan kualitas yang berbeda.

Kueri berikut menunjukkan cara melihat nilai FILTER per panggilan varian untuk set data:

#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

Kueri tersebut menampilkan hasil berikut:

Baris	call_filter	number_of_calls
1	RefCall	11681534
2	BERHASIL	26867854

Nilai PASS menandakan bahwa panggilan varian berkualitas tinggi.

FILTER untuk panggilan varian berkualitas tinggi

Saat menganalisis varian, Anda sebaiknya mengecualikan varian yang berkualitas lebih rendah. Jika kolom FILTER berisi nilai PASS, kemungkinan kolom tersebut tidak berisi nilai lain. Anda dapat memverifikasi hal ini dengan menjalankan kueri berikut. Kueri juga menghilangkan panggilan apa pun yang tidak berisi nilai PASS di bagian FILTER.

#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

Seperti yang diharapkan, menjalankan kueri akan menampilkan nol hasil.

Menghitung semua panggilan berkualitas tinggi untuk setiap sampel

Kueri berikut menunjukkan cara menghitung semua panggilan (varian dan non-varian) untuk setiap kumpulan panggilan, dan menghilangkan panggilan dengan filter non-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

Kueri tersebut menampilkan hasil berikut:

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

Menghitung semua panggilan varian asli berkualitas tinggi untuk setiap sampel

Kueri berikut menunjukkan cara menghitung semua panggilan (varian dan non-varian) untuk setiap sampel. Aturan ini menghilangkan semua panggilan dengan filter non-PASS, dan hanya menyertakan panggilan dengan minimal satu varian benar, artinya 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

Kueri tersebut menampilkan hasil berikut:

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

Praktik terbaik

Kueri pemadatan

Saat kueri Anda menjadi lebih kompleks, penting untuk membuatnya tetap ringkas guna memastikan bahwa logikanya benar dan mudah diikuti.

Contoh berikut menunjukkan cara memulai dari kueri yang menghitung jumlah varian per kromosom dan, langkah demi langkah, meringkasnya menggunakan sintaksis SQL dan fungsi yang ditentukan pengguna.

Seperti yang dijelaskan di bagian menghitung varian per kromosom, kueri memiliki persyaratan berikut:

• Menghitung semua baris yang berisi setidaknya satu panggilan varian dengan setidaknya satu genotipe yang lebih besar dari 0.
• Mengelompokkan baris varian berdasarkan kromosom dan menghitung setiap kelompok.

Penulisan kueri ini bisa menjadi rumit karena, untuk menyelesaikan tugas pertama, Anda harus melihat ARRAY (genotype) dalam ARRAY (call) sambil mempertahankan konteks eksekusi kueri di tingkat baris. Anda mempertahankan konteks eksekusi kueri di tingkat baris karena Anda ingin menghasilkan hasil per varian, bukan hasil per-call atau per-genotype.

Fungsi UNNEST memungkinkan Anda membuat kueri melalui kolom ARRAY seolah-olah kolom tersebut adalah tabel. Fungsi ini menampilkan satu baris untuk setiap elemen ARRAY. Ini juga tidak mengubah konteks kueri. Gunakan fungsi UNNEST di subkueri EXISTS dalam klausa WHERE:

#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

Kueri menampilkan hasil yang sama dengan contoh dalam menghitung varian per kromosom:

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

Anda dapat membuat kueri lebih ringkas dengan mengubah klausa EXISTS menjadi JOIN dari kolom call dengan kolom call.genotype. Operator koma adalah notasi singkat yang digunakan untuk 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

Kueri ini berfungsi dan ringkas, tetapi tidak memungkinkan Anda mengurutkan output dalam urutan numerik kromosom menaik (reference_name) karena nilai dalam reference_name adalah jenis string, dan setiap nilai berisi awalan "chr".

Untuk mengurutkan output secara numerik, pertama-tama hapus awalan "chr" dari kolom reference_name dan beri alias 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

Kueri ini menggunakan fungsi REGEXP_REPLACE untuk mengganti string awalan "chr" dengan string kosong. Kueri tersebut kemudian mengubah fungsi GROUP BY dan ORDER BY untuk menggunakan alias chromosome yang dihitung. Output-nya masih mengurutkan berdasarkan string:

Baris	kromosom	number_of_variant_rows
1	1	615000
2	10	396773
3	11	391260
4	12	382841
5	13	298044
...	...	...

Untuk mengurutkan output secara numerik, transmisikan kolom chromosome dari string ke bilangan bulat:

#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

Kueri menampilkan error karena tidak semua nama kromosom, seperti "X", "Y", dan "M" adalah numerik. Gunakan fungsi CASE untuk menambahkan awalan "0" ke kromosom 1 sampai 9 dan menghapus awalan "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

Kueri menampilkan output yang benar:

Baris	kromosom	number_of_variant_rows
1	01	615000
2	02	646401
3	03	542315
4	04	578600
5	05	496202
...	...	...

Kueri ini menggunakan fungsi SAFE_CAST, yang menampilkan NULL untuk kromosom X, Y, dan M, bukan menampilkan error.

Sebagai peningkatan terakhir pada output, tampilkan kolom reference_name lagi, bukan menyetelnya ke alias chromosome. Untuk melakukannya, pindahkan klausa CASE ke fungsi 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

Kueri akhir ini sama dengan kueri yang ditampilkan dalam Menghitung varian per kromosom.

Menulis fungsi yang ditentukan pengguna

BigQuery mendukung fungsi yang ditentukan pengguna. Anda dapat menggunakan fungsi yang ditentukan pengguna untuk membuat fungsi menggunakan ekspresi SQL lain atau bahasa pemrograman lain, seperti JavaScript.

Contoh dalam Kueri Ringkas menunjukkan cara membuat kueri yang kompleks, tetapi kuerinya terlalu kompleks.

Kueri berikut menunjukkan cara membuat kueri yang lebih ringkas dengan memindahkan logika CASE ke dalam sebuah fungsi:

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

Kueri berikut juga menunjukkan cara membuat kueri yang lebih ringkas, tetapi menggunakan fungsi yang ditentukan dalam 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)

Kedua kueri tersebut menampilkan hasil yang benar, dan logikanya lebih ringkas.

Meningkatkan performa kueri dan mengurangi biaya

Harga BigQuery didasarkan pada jumlah byte yang diproses untuk sebuah kueri. Performa kueri meningkat saat jumlah data yang diproses berkurang. BigQuery memberikan data tentang jumlah detik yang telah berlalu sejak kueri dimulai dan jumlah byte yang diproses oleh kueri. Lihat penjelasan paket kueri BigQuery untuk mengetahui informasi tentang cara mengoptimalkan kueri Anda.

Beberapa contoh di halaman ini, seperti Menghitung panggilan varian dalam tabel, menunjukkan beberapa cara untuk menulis kueri. Untuk menentukan metode pembuatan kueri yang terbaik bagi Anda, periksa durasi berbagai kueri dan lihat berapa byte data yang diproses.

Pembersihan

Setelah menyelesaikan tutorial, Anda dapat membersihkan resource yang dibuat agar resource tersebut berhenti menggunakan kuota dan dikenai biaya. Bagian berikut menjelaskan cara menghapus atau menonaktifkan resource ini.

Cara termudah untuk menghilangkan penagihan adalah dengan menghapus project yang Anda buat untuk tutorial.

Untuk menghapus project:

Perhatian: Menghapus project memiliki efek berikut:

• Semua hal dalam project akan dihapus. Jika menggunakan project yang sudah ada untuk tugas dalam dokumen ini, saat Anda menghapusnya, pekerjaan lain yang telah Anda lakukan dalam project tersebut juga akan terhapus.
• Project ID kustom hilang. Saat membuat project ini, Anda mungkin telah membuat project ID kustom yang ingin digunakan di masa mendatang. Untuk mempertahankan URL yang menggunakan project ID, seperti URL appspot.com, hapus resource yang dipilih di dalam project, bukan menghapus seluruh project.

Jika Anda berencana mempelajari berbagai arsitektur, tutorial, atau panduan memulai, menggunakan kembali project dapat membantu agar Anda tidak melampaui batas kuota project.

1. Di konsol Google Cloud, buka halaman Manage resource.

   Buka Manage resource

2. Pada daftar project, pilih project yang ingin Anda hapus, lalu klik Delete.
3. Pada dialog, ketik project ID, lalu klik Shut down untuk menghapus project.

Langkah selanjutnya

• Ikuti tutorial Cloud Life Sciences lainnya.
• Menganalisis varian di BigQuery menggunakan R, RMarkdown, atau JavaScript.