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 Genom Platinum Illumina. Data dimuat ke dalam tabel BigQuery yang menggunakan skema varian BigQuery. Nama tabelnya adalah platinum_genomes_deepvariant_variants_20180823.

Jika data varian Anda berada dalam tabel BigQuery yang menggunakan skema varian BigQuery, Anda dapat dengan mudah menerapkan kueri dalam tutorial ini ke data Anda. Untuk 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 representasi segmen non-varian.
• Cari tahu cara representasi panggilan varian.
• Cari tahu cara representasi filter kualitas panggilan varian.
• Menggabungkan kolom hierarkis.
• Persingkat kueri.
• Menghitung baris yang berbeda.
• Mengelompokkan baris.
• Menulis fungsi yang ditentukan pengguna.

Tutorial ini juga menunjukkan cara menemukan informasi berikut:

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

Biaya

Dalam dokumen ini, Anda akan 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. 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.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the 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.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

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

6. Anda harus memahami skema varian BigQuery.

Melihat skema dan data tabel

Mengakses tabel dan melihat skema

Tabel platinum_genomes_deepvariant_variants_20180823 Genom Platinum Illumina tersedia secara publik.

Varian dan non-varian dalam tabel

Data Illumina Platinum Genomes menggunakan format gVCF, yang berarti bahwa 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 representasi segmen non-varian 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 yang berisi nilai yang mewakili segmen non-varian. Segmen menampilkan informasi berikut:

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

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

reference_name	start_position	end_position	reference_bases	alternate_bases.alt
1	1000	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	1000	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.

Melihat 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 Pratinjau 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 Kueri Baru:

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

4. Klik Run query. Kueri akan menampilkan hasil berikut:

   Baris	number_of_rows
   1	105923159

Menghitung panggilan varian dalam tabel

Setiap baris dalam tabel memiliki posisi genomik yang merupakan varian atau segmen non-varian.

Setiap baris juga berisi kolom call, yang merupakan ARRAY panggilan varian. Setiap kolom call menyertakan name dan nilai lainnya, seperti genotipe, kolom kualitas, kedalaman baca, dan 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 ditunjukkan di bawah. 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 akan menampilkan hasil berikut:

Baris	number_of_calls
1	182104652

JOINing setiap baris

Hitung jumlah total panggilan varian di semua sampel dengan menggunakan JOIN di setiap baris dengan kolom call. Kueri menggunakan operator koma (,), yang merupakan notasi singkat yang digunakan untuk JOIN. Join 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 akan menampilkan hasil berikut:

Baris	number_of_calls
1	182104652

Menghitung name di 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 akan 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 terlebih dahulu untuk memfilter segmen non-varian:

#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 akan menampilkan hasil berikut:

Baris	number_of_real_variants
1	38549388

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

Untuk setiap baris dalam tabel, subkueri dikeluarkan melalui kolom alternate_bases 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 akan 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 teratas dalam tabel, Anda dapat mulai membuat kueri untuk baris turunan. Baris ini menyertakan data seperti setiap sampel yang telah melakukan panggilan 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 tempat setiap kumpulan panggilan muncul, 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 menampilkan enam baris. Setiap call_name sesuai dengan individu 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 hanya menghitung baris varian:

#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 akan 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 yang benar menurut genotipe.

Memfilter varian sebenarnya menurut genotipe

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

Untuk memfilter varian menurut 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 akan 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

Di Menghitung varian yang dipanggil oleh setiap sampel, setiap kueri menampilkan enam baris dengan nilai untuk call_name. Untuk membuat kueri dan mendapatkan nilai untuk 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 akan menampilkan hasil berikut:

Baris	number_of_callsets
1	6

Menghitung varian per kromosom

Untuk menghitung jumlah varian per kromosom, jalankan kueri berikut. Kueri ini 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 grup.

#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 pada 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 akan menampilkan hasil berikut:

Baris	call_filter	number_of_calls
1	RefCall	11681534
2	BERHASIL	26867854

Nilai PASS menunjukkan bahwa panggilan varian memiliki kualitas tinggi.

FILTERing untuk panggilan varian berkualitas tinggi

Saat menganalisis varian, Anda mungkin ingin memfilter varian dengan kualitas yang lebih rendah. Jika kolom FILTER berisi nilai PASS, kemungkinan kolom tersebut tidak berisi nilai lain. Anda dapat memverifikasinya dengan menjalankan kueri berikut. Kueri juga menghapus panggilan 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 hasil nol.

Menghitung semua panggilan berkualitas tinggi untuk setiap sampel

Kueri berikut menunjukkan cara menghitung semua panggilan (varian dan non-varian) untuk setiap kumpulan panggilan, dan menghapus panggilan apa pun 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 akan 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 benar berkualitas tinggi untuk setiap sampel

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

Meringkas kueri

Seiring kueri menjadi lebih kompleks, penting untuk membuatnya ringkas agar 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 grup.

Menulis kueri ini bisa jadi 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 ingin menghasilkan hasil per varian, bukan hasil per call atau per genotype.

Fungsi UNNEST memungkinkan Anda membuat kueri di kolom ARRAY seolah-olah kolom tersebut adalah tabel. Fungsi ini menampilkan satu baris untuk setiap elemen ARRAY. Tindakan ini juga tidak mengubah konteks kueri. Gunakan fungsi UNNEST dalam 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 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 berfungsi, dan ringkas, tetapi tidak memungkinkan Anda mengurutkan output dalam urutan numerik kromosom (reference_name) menaik karena nilai dalam reference_name adalah jenis string, dan setiap nilai berisi awalan "chr."

Untuk mengurutkan output secara numerik, hapus awalan "chr" terlebih dahulu 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 menggunakan fungsi REGEXP_REPLACE untuk mengganti string awalan "chr" dengan string kosong. Kueri kemudian mengubah fungsi GROUP BY dan ORDER BY untuk menggunakan alias chromosome yang dihitung. Output masih diurutkan menurut string:

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

Sebagai gantinya, 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" bersifat numerik. Gunakan fungsi CASE untuk menambahkan "0" ke kromosom 1 hingga 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 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 menetapkannya 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 yang ditampilkan di Menghitung varian per kromosom.

Menulis fungsi yang ditentukan pengguna (UDF)

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 di Meringkas kueri menunjukkan cara membuat kueri yang kompleks, tetapi kueri tersebut terlalu kompleks.

Kueri berikut menunjukkan cara membuat kueri lebih ringkas dengan memindahkan logika CASE ke dalam 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 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 menampilkan hasil yang benar, dan logikanya lebih ringkas.

Meningkatkan performa kueri dan mengurangi biaya

Harga BigQuery didasarkan pada jumlah byte yang diproses untuk kueri. Performa kueri akan meningkat jika jumlah data yang diproses dikurangi. BigQuery memberikan data tentang jumlah detik yang telah berlalu sejak kueri dimulai dan jumlah byte yang diproses 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 kueri mana yang paling cocok bagi Anda, periksa durasi kueri yang berbeda dan lihat jumlah byte data yang diprosesnya.

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 beberapa arsitektur, tutorial atau panduan memulai, dengan menggunakan kembali project dapat membantu Anda agar tidak melampaui batas kuota project.

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.

Langkah selanjutnya

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