BigQuery を使用すると、バリアントとゲノム領域の間隔（または重複）によって記述されるデータを含むバリアントに対して JOIN クエリを実行できます。このページでは、複合 JOIN クエリを使用して遺伝子名のリストを取得するとともに次のことを行う方法を説明します。
- 遺伝子にオーバーラップする希少 SNP を見つける
- ゲノムサンプル全体について、遺伝子のいずれかの側の 100,000 塩基対を見つける
このガイドでは、3 つのクエリの例を紹介します。各クエリは、BigQuery がさまざまなサイズのゲノムデータに対してスケーリングする方法を示しています。
このデータは、90 億近くの行を持つ Tute Genomics Annotation テーブルと Illumina Platinum Genomes データセットからのものです。これらのデータセットの情報については、次に示すリンク先をご覧ください。
- Google Cloud ブログの Google Genomics と Tute で遺伝子変異を研究する。
- Illumina Platinum Genomes の一般公開データセットに関するセクション
インライン テーブルに対してクエリを実行する
この例では、クエリの中で定義された区間テーブル intervals を使用し、Illumina Platinum Genomes のバリアントが格納されているテーブルを用いて JOIN クエリを実行する方法を説明します。
Google Cloud Console の [BigQuery] ページに移動します。
[クエリを新規作成] をクリックします。
[New Query] フィールドで、次のクエリを実行します。
#standardSQL WITH -- -- Retrieve the variants in this cohort, flattening by alternate bases and -- counting affected alleles. variants AS ( SELECT REPLACE(reference_name, 'chr', '') as reference_name, start_position, end_position, reference_bases, alternate_bases.alt AS alt, (SELECT COUNTIF(gt = alt_offset+1) FROM v.call call, call.genotype gt) AS num_variant_alleles, (SELECT COUNTIF(gt >= 0) FROM v.call call, call.genotype gt) AS total_num_alleles FROM `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, UNNEST(v.alternate_bases) alternate_bases WITH OFFSET alt_offset ), -- -- Define an inline table that uses five rows -- selected from silver-wall-555.TuteTable.hg19. intervals AS ( SELECT * FROM UNNEST ([ STRUCT<Gene STRING, Chr STRING, gene_start INT64, gene_end INT64, region_start INT64, region_end INT64> ('PRCC', '1', 156736274, 156771607, 156636274, 156871607), ('NTRK1', '1', 156785541, 156852640, 156685541, 156952640), ('PAX8', '2', 113972574, 114037496, 113872574, 114137496), ('FHIT', '3', 59734036, 61238131, 59634036, 61338131), ('PPARG', '3', 12328349, 12476853, 12228349, 12576853) ])), -- -- JOIN the variants with the genomic intervals overlapping -- the genes of interest. -- -- The JOIN criteria is complicated because the task is to see if -- an SNP overlaps an interval. With standard SQL you can use complex -- JOIN predicates, including arbitrary expressions. gene_variants AS ( SELECT reference_name, start_position, reference_bases, alt, num_variant_alleles, total_num_alleles FROM variants INNER JOIN intervals ON variants.reference_name = intervals.Chr AND intervals.region_start <= variants.start_position AND intervals.region_end >= variants.end_position ) -- -- And finally JOIN the variants in the regions of interest -- with annotations for rare variants. SELECT DISTINCT Chr, annots.Start AS Start, Ref, annots.Alt, Func, Gene, PopFreqMax, ExonicFunc, num_variant_alleles, total_num_alleles FROM `silver-wall-555.TuteTable.hg19` AS annots INNER JOIN gene_variants AS vars ON vars.reference_name = annots.Chr AND vars.start_position = annots.Start AND vars.reference_bases = annots.Ref AND vars.alt = annots.Alt WHERE -- Retrieve annotations for rare variants only. PopFreqMax <= 0.01 ORDER BY Chr, Start;[クエリを実行] をクリックします。このクエリの実行には約 10 秒かかり、約 334 GB 分のデータが処理されます。このクエリの結果は、コホート内の希少バリアントのうち、注目している領域とオーバーラップするものを示します。
クエリの結果を表示するには、次のセクションを展開します。
クエリ結果
Chr 開始 Ref Alt Func 遺伝子 PopFreqMax ExonicFunc num_variant_alleles total_num_alleles 1 156699757 T C イントロン RRNAD1 0.002 2 4 1 156705390 C T イントロン RRNAD1 8.0E-4 0 2 1 156714207 T C イントロン HDGF 0.003 0 6 1 156714440 A C イントロン HDGF 0.0068 0 12 1 156723870 C T インタージェニック HDGF、PRCC 0.006 1 2 1 156724456 C T インタージェニック HDGF、PRCC 0.002 2 4 1 156733988 C T インタージェニック HDGF、PRCC 0.001 1 2 1 156742258 T G イントロン PRCC 0.001 2 4 1 156744826 T G イントロン PRCC 0.002 0 8 1 156779764 G A イントロン SH2D2A 0.001 2 4 1 156783454 A C イントロン SH2D2A 0.0014 1 2 1 156786144 C T イントロン NTRK1、SH2D2A 0.0031 2 4 1 156790510 A T イントロン NTRK1 0.002 1 2 1 156815332 A C イントロン INSRR、NTRK1 0.003 0 2 1 156830778 G A エクソン NTRK1 0.0067 ミスセンス 2 4 1 156842064 C T イントロン NTRK1 0.0014 1 2 1 156843438 C A エクソン NTRK1 0.0032 ミスセンス 1 2 1 156845773 C T イントロン NTRK1 0.001 2 4 1 156873318 T C イントロン PEAR1 0.01 4 8 1 156922740 G A イントロン ARHGEF11 0.007 1 2 1 156930100 C T イントロン ARHGEF11 0.001 2 4 2 113901230 G A インタージェニック IL1RN、PSD4 0.0082 1 2 2 113953418 C A イントロン PSD4 0.001 2 4 2 113967621 G C インタージェニック PSD4、PAX8 0.002 0 6 2 113967624 T C インタージェニック PSD4、PAX8 0.002 0 2 2 113980967 G A イントロン PAX8 0.002 2 4 2 113994010 A C ncRNA_exonic PAX8-AS1 0.001 0 4 2 113997745 C A ncRNA_exonic PAX8-AS1 0.001 2 4 2 114061327 T C インタージェニック PAX8、CBWD2 0.001 2 4 2 114084018 A C インタージェニック PAX8、CBWD2 0.0045 0 4 2 114099037 G A インタージェニック PAX8、CBWD2 0.0051 1 2 2 114105670 A T インタージェニック PAX8、CBWD2 0.001 1 2 2 114111325 G T インタージェニック PAX8、CBWD2 0.001 1 2 3 12265797 C T インタージェニック SYN2、PPARG 0.0089 2 4 3 12277958 A G インタージェニック SYN2、PPARG 0.002 1 2 3 12296019 G A インタージェニック SYN2、PPARG 0.002 2 4 3 12316549 G C インタージェニック SYN2、PPARG 0.002 1 2 3 12335681 T G イントロン PPARG 0.0092 2 4 3 12348795 T C イントロン PPARG 0.0014 1 2 3 12353106 T C イントロン PPARG 0.001 2 4 3 12403825 G A イントロン PPARG 0.0051 2 4 3 12404394 G A イントロン PPARG 0.001 1 2 3 12410289 G A イントロン PPARG 0.008 2 4 3 12431381 C T イントロン PPARG 0.0061 2 4 3 12447267 G A イントロン PPARG 0.0089 2 4 3 12449379 C T イントロン PPARG 0.0092 2 4 3 12450848 C A イントロン PPARG 0.0092 2 4 3 12462847 T C イントロン PPARG 0.002 1 2 3 12492797 G A インタージェニック PPARG、TSEN2 0.01 1 2 3 12503201 G A インタージェニック PPARG、TSEN2 0.0099 2 4 3 12530460 A G イントロン TSEN2 0.0092 2 4 3 12531167 A G イントロン TSEN2 0.0099 2 4 3 12557737 A G イントロン TSEN2 0.001 2 4 3 59636143 A G インタージェニック C3orf67、FHIT 0.003 3 6 3 59645934 A C インタージェニック C3orf67、FHIT 0.004 1 2 3 59646893 G A インタージェニック C3orf67、FHIT 0.002 1 2 3 59697024 A G インタージェニック C3orf67、FHIT 0.0072 1 2 3 59701013 G A インタージェニック C3orf67、FHIT 0.004 2 4 3 59733945 A G インタージェニック C3orf67、FHIT 0.001 2 4 3 59747482 C T イントロン FHIT 0.001 2 4 3 59750635 A G イントロン FHIT 0.003 1 2 3 59757776 C T イントロン FHIT 0.001 2 4 3 59770612 G A イントロン FHIT 0.001 2 4 3 59804444 G C イントロン FHIT 0.001 2 4 3 59819769 T C イントロン FHIT 0.001 2 4 3 59884396 C T イントロン FHIT 0.001 2 4 3 59960728 A C イントロン FHIT 0.01 1 2 3 59970345 G A イントロン FHIT 0.002 1 2 3 59972417 T A イントロン FHIT 0.0072 0 2 3 60104328 C A イントロン FHIT 0.01 2 4 3 60139062 G A イントロン FHIT 0.01 0 2 3 60158066 C T イントロン FHIT 0.001 1 2 3 60169285 C T イントロン FHIT 0.005 1 2 3 60216185 T C イントロン FHIT 0.002 1 2 3 60226380 G A イントロン FHIT 0.007 2 4 3 60234539 C A イントロン FHIT 0.002 1 2 3 60247464 A C イントロン FHIT 0.004 2 4 3 60269926 A G イントロン FHIT 0.007 2 4 3 60271228 G T イントロン FHIT 0.007 2 4 3 60286972 T C イントロン FHIT 0.001 2 4 3 60301412 C G イントロン FHIT 0.001 1 2 3 60312251 C T イントロン FHIT 0.0099 1 2 3 60317682 A G イントロン FHIT 0.008 1 2 3 60328557 C G イントロン FHIT 0.0043 2 4 3 60342562 C T イントロン FHIT 0.006 1 2 3 60400033 G A イントロン FHIT 0.004 2 4 3 60435819 C T イントロン FHIT 0.006 2 4 3 60435820 G T イントロン FHIT 0.004 1 2 3 60441288 T C イントロン FHIT 0.006 2 4 3 60444465 C A イントロン FHIT 0.01 1 2 3 60444575 C T イントロン FHIT 0.001 1 2 3 60450581 T C イントロン FHIT 0.01 1 2 3 60456571 G A イントロン FHIT 0.001 2 4 3 60473568 C G イントロン FHIT 0.001 1 2 3 60487557 T C イントロン FHIT 0.001 1 2 3 60559705 A G イントロン FHIT 0.002 2 4 3 60570764 T C イントロン FHIT 0.008 2 4 3 60582100 C T イントロン FHIT 0.001 1 2 3 60587192 G A イントロン FHIT 0.004 1 2 3 60599869 G A イントロン FHIT 0.0086 2 4 3 60603091 C T イントロン FHIT 0.001 2 4 3 60603250 A T イントロン FHIT 0.0099 1 2 3 60609831 T G イントロン FHIT 0.001 2 4 3 60619756 G T イントロン FHIT 0.0015 2 4 3 60680758 C T イントロン FHIT 0.0089 2 4 3 60702243 G C イントロン FHIT 0.001 2 4 3 60702532 A G イントロン FHIT 0.001 1 2 3 60714328 A T イントロン FHIT 0.004 1 2 3 60725297 G A イントロン FHIT 0.001 1 2 3 60726640 G A イントロン FHIT 0.01 2 4 3 60795144 A G イントロン FHIT 0.001 2 4 3 60807171 A G イントロン FHIT 0.001 1 2 3 60813868 T C イントロン FHIT 0.001 1 2 3 60826546 C G イントロン FHIT 0.0023 1 2 3 60837392 C T イントロン FHIT 0.001 1 2 3 60846310 A G イントロン FHIT 0.01 0 2 3 60850985 C T イントロン FHIT 0.004 1 2 3 60852559 T C イントロン FHIT 0.008 1 2 3 60871759 T C イントロン FHIT 0.004 1 2 3 60884396 C T イントロン FHIT 0.002 2 4 3 60897092 C A イントロン FHIT 0.001 2 4 3 60940759 C T イントロン FHIT 0.0089 1 2 3 60982595 A G イントロン FHIT 0.003 2 4 3 60999283 G A イントロン FHIT 0.001 1 2 3 61042977 A G イントロン FHIT 0.001 2 4 3 61043349 T C イントロン FHIT 0.001 2 4 3 61044789 A C イントロン FHIT 0.001 2 4 3 61141621 G A イントロン FHIT 0.003 1 2 3 61148655 G C イントロン FHIT 0.001 2 4 3 61170747 C T イントロン FHIT 0.003 1 2 3 61189473 C G イントロン FHIT 0.0099 1 2 3 61190425 C T イントロン FHIT 0.0023 2 4 3 61193853 C T イントロン FHIT 0.0099 0 2 3 61194793 C T イントロン FHIT 0.007 0 2 3 61194840 A G イントロン FHIT 0.0099 0 2 3 61194886 T A イントロン FHIT 0.0099 0 2 3 61201777 C T イントロン FHIT 0.001 2 4 3 61202292 T C イントロン FHIT 0.007 1 2 3 61232806 G C イントロン FHIT 0.0099 1 2 3 61232910 C T イントロン FHIT 0.0099 1 2 3 61235824 A T イントロン FHIT 0.001 2 4 3 61283810 A C インタージェニック FHIT、PTPRG 0.0089 1 2 3 61293731 T A インタージェニック FHIT、PTPRG 0.0089 2 4 3 61296730 C T インタージェニック FHIT、PTPRG 0.001 1 2 3 61326341 C T インタージェニック FHIT、PTPRG 0.004 2 4 3 61326620 T C インタージェニック FHIT、PTPRG 0.01 1 2 3 61327649 G C インタージェニック FHIT、PTPRG 0.001 2 4 3 61330545 G C インタージェニック FHIT、PTPRG 0.001 2 4 3 61335803 G A インタージェニック FHIT、PTPRG 0.001 2 4 同様のクエリを 1,000 人ゲノムフェーズ 3 からのデータに対して実行すると、約 90 秒かかり、約 3.38 TB 分のデータが処理されます。
実体化テーブルを使用する
大規模なビッグデータを扱うときは、区間テーブルを実体化してその新しいテーブルに対して JOIN クエリを実行できます。このセクションの残りの部分に進む前に、次の手順に沿ってデータセットを作成します。
Google Cloud コンソールで [BigQuery] ページを開きます。
[エクスプローラ] パネルで、データセットを作成するプロジェクトを選択します。
アクション オプションを開いて、[データセットを作成] をクリックします。
[データセットを作成] ページで次の操作を行います。
- [データセット ID] に「
genomics」を入力します。 - その他のデフォルト設定はそのままにします。
- [データセットを作成] をクリックします。
- [データセット ID] に「
特定の遺伝子が格納された実体化テーブルに対してクエリを実行する
次に示す例では、新しい区間テーブルを実体化する方法を説明します。この中には、silver-wall-555:TuteTable.hg19 テーブルからの特定の遺伝子のリストが格納されます。
区間テーブルを作成する方法は次のとおりです。
Google Cloud コンソールで [BigQuery] ページを開きます。
[クエリを新規作成] をクリックします。
[New Query] フィールドで、次のクエリを実行します。このクエリは、silver-wall-555:TuteTable.hg19 テーブルの一部分を実体化して、新しい
genomics.myIntervalTable区間テーブルを作成します。#standardSQL CREATE TABLE `genomics.myIntervalTable` AS ( SELECT Gene, Chr, MIN(Start) AS gene_start, MAX(`End`) AS gene_end, MIN(Start)-100000 AS region_start, MAX(`End`)+100000 AS region_end FROM `silver-wall-555.TuteTable.hg19` WHERE Gene IN ('APC', 'ATM', 'BMPR1A', 'BRCA1', 'BRCA2', 'CDK4', 'CDKN2A', 'CREBBP', 'EGFR', 'EP300', 'ETV6', 'FHIT', 'FLT3', 'HRAS', 'KIT', 'MET', 'MLH1', 'NTRK1', 'PAX8', 'PDGFRA', 'PPARG', 'PRCC', 'PRKAR1A', 'PTEN', 'RET', 'STK11', 'TFE3', 'TGFB1', 'TGFBR2', 'TP53', 'WWOX') GROUP BY Chr, Gene );[クエリを実行] をクリックします。次の結果が返されます。
This statement created a new table named PROJECT_ID:genomics.myIntervalTable.
[New Query] フィールドで、次のクエリを実行します。
#standardSQL WITH -- -- Retrieve the variants in this cohort, flattening by alternate bases and -- counting affected alleles. variants AS ( SELECT REPLACE(reference_name, 'chr', '') as reference_name, start_position, end_position, reference_bases, alternate_bases.alt AS alt, (SELECT COUNTIF(gt = alt_offset+1) FROM v.call call, call.genotype gt) AS num_variant_alleles, (SELECT COUNTIF(gt >= 0) FROM v.call call, call.genotype gt) AS total_num_alleles FROM `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, UNNEST(v.alternate_bases) alternate_bases WITH OFFSET alt_offset ), -- -- JOIN the variants with the genomic intervals overlapping -- the genes of interest. -- -- The JOIN criteria is complicated because the task is to see if -- an SNP overlaps an interval. With standard SQL you can use complex -- JOIN predicates, including arbitrary expressions. gene_variants AS ( SELECT reference_name, start_position, reference_bases, alt, num_variant_alleles, total_num_alleles FROM variants INNER JOIN `genomics.myIntervalTable` AS intervals ON variants.reference_name = intervals.Chr AND intervals.region_start <= variants.start_position AND intervals.region_end >= variants.end_position ) -- -- And finally JOIN the variants in the regions of interest -- with annotations for rare variants. SELECT DISTINCT Chr, annots.Start AS Start, Ref, annots.Alt, Func, Gene, PopFreqMax, ExonicFunc, num_variant_alleles, total_num_alleles FROM `silver-wall-555.TuteTable.hg19` AS annots INNER JOIN gene_variants AS vars ON vars.reference_name = annots.Chr AND vars.start_position = annots.Start AND vars.reference_bases = annots.Ref AND vars.alt = annots.Alt WHERE -- Retrieve annotations for rare variants only. PopFreqMax <= 0.01 ORDER BY Chr, Start;[クエリを実行] をクリックします。このクエリの実行には約 10 秒かかり、約 334 GB 分のデータが処理されます。このクエリの結果は、コホート内の希少バリアントのうち、注目している領域とオーバーラップするものを示します。
クエリの結果を表示するには、次のセクションを展開します。
クエリ結果
Chr 開始 Ref Alt Func 遺伝子 PopFreqMax ExonicFunc num_variant_alleles total_num_alleles 1 156699757 T C イントロン RRNAD1 0.002 2 4 1 156705390 C T イントロン RRNAD1 8.0E-4 0 2 1 156714207 T C イントロン HDGF 0.003 0 6 1 156714440 A C イントロン HDGF 0.0068 0 12 1 156723870 C T インタージェニック HDGF、PRCC 0.006 1 2 1 156724456 C T インタージェニック HDGF、PRCC 0.002 2 4 1 156733988 C T インタージェニック HDGF、PRCC 0.001 1 2 1 156742258 T G イントロン PRCC 0.001 2 4 1 156744826 T G イントロン PRCC 0.002 0 8 1 156779764 G A イントロン SH2D2A 0.001 2 4 1 156783454 A C イントロン SH2D2A 0.0014 1 2 1 156786144 C T イントロン NTRK1、SH2D2A 0.0031 2 4 1 156790510 A T イントロン NTRK1 0.002 1 2 1 156815332 A C イントロン INSRR、NTRK1 0.003 0 2 1 156830778 G A エクソン NTRK1 0.0067 ミスセンス 2 4 1 156842064 C T イントロン NTRK1 0.0014 1 2 1 156843438 C A エクソン NTRK1 0.0032 ミスセンス 1 2 1 156845773 C T イントロン NTRK1 0.001 2 4 1 156873318 T C イントロン PEAR1 0.01 4 8 1 156922740 G A イントロン ARHGEF11 0.007 1 2 1 156930100 C T イントロン ARHGEF11 0.001 2 4 2 113901230 G A インタージェニック IL1RN、PSD4 0.0082 1 2 2 113953418 C A イントロン PSD4 0.001 2 4 2 113967621 G C インタージェニック PSD4、PAX8 0.002 0 6 2 113967624 T C インタージェニック PSD4、PAX8 0.002 0 2 2 113980967 G A イントロン PAX8 0.002 2 4 2 113994010 A C ncRNA_exonic PAX8-AS1 0.001 0 4 2 113997745 C A ncRNA_exonic PAX8-AS1 0.001 2 4 2 114061327 T C インタージェニック PAX8、CBWD2 0.001 2 4 2 114084018 A C インタージェニック PAX8、CBWD2 0.0045 0 4 2 114099037 G A インタージェニック PAX8、CBWD2 0.0051 1 2 2 114105670 A T インタージェニック PAX8、CBWD2 0.001 1 2 2 114111325 G T インタージェニック PAX8、CBWD2 0.001 1 2 3 12265797 C T インタージェニック SYN2、PPARG 0.0089 2 4 3 12277958 A G インタージェニック SYN2、PPARG 0.002 1 2 3 12296019 G A インタージェニック SYN2、PPARG 0.002 2 4 3 12316549 G C インタージェニック SYN2、PPARG 0.002 1 2 3 12335681 T G イントロン PPARG 0.0092 2 4 3 12348795 T C イントロン PPARG 0.0014 1 2 3 12353106 T C イントロン PPARG 0.001 2 4 3 12403825 G A イントロン PPARG 0.0051 2 4 3 12404394 G A イントロン PPARG 0.001 1 2 3 12410289 G A イントロン PPARG 0.008 2 4 3 12431381 C T イントロン PPARG 0.0061 2 4 3 12447267 G A イントロン PPARG 0.0089 2 4 3 12449379 C T イントロン PPARG 0.0092 2 4 3 12450848 C A イントロン PPARG 0.0092 2 4 3 12462847 T C イントロン PPARG 0.002 1 2 3 12492797 G A インタージェニック PPARG、TSEN2 0.01 1 2 3 12503201 G A インタージェニック PPARG、TSEN2 0.0099 2 4 3 12530460 A G イントロン TSEN2 0.0092 2 4 3 12531167 A G イントロン TSEN2 0.0099 2 4 3 12557737 A G イントロン TSEN2 0.001 2 4 3 59636143 A G インタージェニック C3orf67、FHIT 0.003 3 6 3 59645934 A C インタージェニック C3orf67、FHIT 0.004 1 2 3 59646893 G A インタージェニック C3orf67、FHIT 0.002 1 2 3 59697024 A G インタージェニック C3orf67、FHIT 0.0072 1 2 3 59701013 G A インタージェニック C3orf67、FHIT 0.004 2 4 3 59733945 A G インタージェニック C3orf67、FHIT 0.001 2 4 3 59747482 C T イントロン FHIT 0.001 2 4 3 59750635 A G イントロン FHIT 0.003 1 2 3 59757776 C T イントロン FHIT 0.001 2 4 3 59770612 G A イントロン FHIT 0.001 2 4 3 59804444 G C イントロン FHIT 0.001 2 4 3 59819769 T C イントロン FHIT 0.001 2 4 3 59884396 C T イントロン FHIT 0.001 2 4 3 59960728 A C イントロン FHIT 0.01 1 2 3 59970345 G A イントロン FHIT 0.002 1 2 3 59972417 T A イントロン FHIT 0.0072 0 2 3 60104328 C A イントロン FHIT 0.01 2 4 3 60139062 G A イントロン FHIT 0.01 0 2 3 60158066 C T イントロン FHIT 0.001 1 2 3 60169285 C T イントロン FHIT 0.005 1 2 3 60216185 T C イントロン FHIT 0.002 1 2 3 60226380 G A イントロン FHIT 0.007 2 4 3 60234539 C A イントロン FHIT 0.002 1 2 3 60247464 A C イントロン FHIT 0.004 2 4 3 60269926 A G イントロン FHIT 0.007 2 4 3 60271228 G T イントロン FHIT 0.007 2 4 3 60286972 T C イントロン FHIT 0.001 2 4 3 60301412 C G イントロン FHIT 0.001 1 2 3 60312251 C T イントロン FHIT 0.0099 1 2 3 60317682 A G イントロン FHIT 0.008 1 2 3 60328557 C G イントロン FHIT 0.0043 2 4 3 60342562 C T イントロン FHIT 0.006 1 2 3 60400033 G A イントロン FHIT 0.004 2 4 3 60435819 C T イントロン FHIT 0.006 2 4 3 60435820 G T イントロン FHIT 0.004 1 2 3 60441288 T C イントロン FHIT 0.006 2 4 3 60444465 C A イントロン FHIT 0.01 1 2 3 60444575 C T イントロン FHIT 0.001 1 2 3 60450581 T C イントロン FHIT 0.01 1 2 3 60456571 G A イントロン FHIT 0.001 2 4 3 60473568 C G イントロン FHIT 0.001 1 2 3 60487557 T C イントロン FHIT 0.001 1 2 3 60559705 A G イントロン FHIT 0.002 2 4 3 60570764 T C イントロン FHIT 0.008 2 4 3 60582100 C T イントロン FHIT 0.001 1 2 3 60587192 G A イントロン FHIT 0.004 1 2 3 60599869 G A イントロン FHIT 0.0086 2 4 3 60603091 C T イントロン FHIT 0.001 2 4 3 60603250 A T イントロン FHIT 0.0099 1 2 3 60609831 T G イントロン FHIT 0.001 2 4 3 60619756 G T イントロン FHIT 0.0015 2 4 3 60680758 C T イントロン FHIT 0.0089 2 4 3 60702243 G C イントロン FHIT 0.001 2 4 3 60702532 A G イントロン FHIT 0.001 1 2 3 60714328 A T イントロン FHIT 0.004 1 2 3 60725297 G A イントロン FHIT 0.001 1 2 3 60726640 G A イントロン FHIT 0.01 2 4 3 60795144 A G イントロン FHIT 0.001 2 4 3 60807171 A G イントロン FHIT 0.001 1 2 3 60813868 T C イントロン FHIT 0.001 1 2 3 60826546 C G イントロン FHIT 0.0023 1 2 3 60837392 C T イントロン FHIT 0.001 1 2 3 60846310 A G イントロン FHIT 0.01 0 2 3 60850985 C T イントロン FHIT 0.004 1 2 3 60852559 T C イントロン FHIT 0.008 1 2 3 60871759 T C イントロン FHIT 0.004 1 2 3 60884396 C T イントロン FHIT 0.002 2 4 3 60897092 C A イントロン FHIT 0.001 2 4 3 60940759 C T イントロン FHIT 0.0089 1 2 3 60982595 A G イントロン FHIT 0.003 2 4 3 60999283 G A イントロン FHIT 0.001 1 2 3 61042977 A G イントロン FHIT 0.001 2 4 3 61043349 T C イントロン FHIT 0.001 2 4 3 61044789 A C イントロン FHIT 0.001 2 4 3 61141621 G A イントロン FHIT 0.003 1 2 3 61148655 G C イントロン FHIT 0.001 2 4 3 61170747 C T イントロン FHIT 0.003 1 2 3 61189473 C G イントロン FHIT 0.0099 1 2 3 61190425 C T イントロン FHIT 0.0023 2 4 3 61193853 C T イントロン FHIT 0.0099 0 2 3 61194793 C T イントロン FHIT 0.007 0 2 3 61194840 A G イントロン FHIT 0.0099 0 2 3 61194886 T A イントロン FHIT 0.0099 0 2 3 61201777 C T イントロン FHIT 0.001 2 4 3 61202292 T C イントロン FHIT 0.007 1 2 3 61232806 G C イントロン FHIT 0.0099 1 2 3 61232910 C T イントロン FHIT 0.0099 1 2 3 61235824 A T イントロン FHIT 0.001 2 4 3 61283810 A C インタージェニック FHIT、PTPRG 0.0089 1 2 3 61293731 T A インタージェニック FHIT、PTPRG 0.0089 2 4 3 61296730 C T インタージェニック FHIT、PTPRG 0.001 1 2 3 61326341 C T インタージェニック FHIT、PTPRG 0.004 2 4 3 61326620 T C インタージェニック FHIT、PTPRG 0.01 1 2 3 61327649 G C インタージェニック FHIT、PTPRG 0.001 2 4 3 61330545 G C インタージェニック FHIT、PTPRG 0.001 2 4 3 61335803 G A インタージェニック FHIT、PTPRG 0.001 2 4 同様のクエリを 1,000 人ゲノムフェーズ 3 からのデータに対して実行すると、約 90 秒かかり、約 3.38 TB 分のデータが処理されます。
ランダムな遺伝子 250 個が格納された実体化テーブルに対してクエリを実行する
次に示す例では、区間 JOIN を実体化テーブルに対して実行する方法を説明します。このテーブルに格納される 250 個の遺伝子は、silver-wall-555:TuteTable.hg19 テーブルからランダムに選択されたものです。
区間テーブルを作成する方法は次のとおりです。
Google Cloud コンソールで [BigQuery] ページを開きます。
[クエリを新規作成] をクリックします。
[New query] フィールドで、次のクエリを実行します。これは、silver-wall-555:TuteTable.hg19 テーブルの一部を、新しい
genomics.randomGenesIntervalTable区間テーブルに実体化します。#standardSQL CREATE TABLE `genomics.randomGenesIntervalTable` AS ( SELECT Gene, Chr, MIN(Start) AS gene_start, MAX(`End`) AS gene_end, MIN(Start) - 100000 AS region_start, MAX(`End`) + 100000 AS region_end FROM `silver-wall-555.TuteTable.hg19` WHERE Gene IN (SELECT Gene FROM `silver-wall-555.TuteTable.hg19` GROUP BY Gene LIMIT 250) GROUP BY Chr, Gene );
- [クエリを実行] をクリックします。次の結果が返されます。
This statement created a new table named PROJECT_ID:genomics.randomGenesIntervalTable.
[New Query] フィールドで、次のクエリを実行します。
#standardSQL WITH -- -- Retrieve the variants in this cohort, flattening by alternate bases and -- counting affected alleles. variants AS ( SELECT REPLACE(reference_name, 'chr', '') as reference_name, start_position, end_position, reference_bases, alternate_bases.alt AS alt, (SELECT COUNTIF(gt = alt_offset+1) FROM v.call call, call.genotype gt) AS num_variant_alleles, (SELECT COUNTIF(gt >= 0) FROM v.call call, call.genotype gt) AS total_num_alleles FROM `bigquery-public-data.human_genome_variants.platinum_genomes_deepvariant_variants_20180823` v, UNNEST(v.alternate_bases) alternate_bases WITH OFFSET alt_offset ), -- -- JOIN the variants with the genomic intervals overlapping -- the genes of interest. -- -- The JOIN criteria is complicated because the task is to see if -- an SNP overlaps an interval. With standard SQL you can use complex -- JOIN predicates, including arbitrary expressions. gene_variants AS ( SELECT reference_name, start_position, reference_bases, alt, num_variant_alleles, total_num_alleles FROM variants INNER JOIN `genomics.randomGenesIntervalTable` AS intervals ON variants.reference_name = intervals.Chr AND intervals.region_start <= variants.start_position AND intervals.region_end >= variants.end_position ) -- -- And finally JOIN the variants in the regions of interest -- with annotations for rare variants. SELECT DISTINCT Chr, annots.Start AS Start, Ref, annots.Alt, Func, Gene, PopFreqMax, ExonicFunc, num_variant_alleles, total_num_alleles FROM `silver-wall-555.TuteTable.hg19` AS annots INNER JOIN gene_variants AS vars ON vars.reference_name = annots.Chr AND vars.start_position = annots.Start AND vars.reference_bases = annots.Ref AND vars.alt = annots.Alt WHERE -- Retrieve annotations for rare variants only. PopFreqMax <= 0.01 ORDER BY Chr, Start;[クエリを実行] をクリックします。このクエリの実行には約 10 秒かかり、約 334 GB 分のデータが処理されます。このクエリの結果は、コホート内の希少バリアントのうち、注目している領域とオーバーラップするものを示します。
切り捨てられたクエリの結果を表示するには、次のセクションを展開します。
クエリ結果
Chr 開始 Ref Alt Func 遺伝子 PopFreqMax ExonicFunc num_variant_alleles total_num_alleles 1 2925355 C A インタージェニック TTC34、ACTRT2 0.001 2 4 1 2933170 G A インタージェニック TTC34、ACTRT2 0.0083 0 4 1 2944477 G A インタージェニック ACTRT2、LINC00982 0.003 4 6 1 2967591 A T インタージェニック ACTRT2、LINC00982 0.0092 1 2 1 2975255 T C ダウンストリーム LINC00982 0.0082 1 2 1 2977223 C T ncRNA_intronic LINC00982 0.0072 1 2 1 2978803 G C ncRNA_exonic LINC00982 0.002 4 6 1 3006466 G A イントロン PRDM16 0.0098 1 2 1 3011333 G T イントロン PRDM16 0.004 1 2 1 3019659 C T イントロン PRDM16 0.0031 1 2 1 3036896 G A イントロン PRDM16 0.001 1 2 1 3037388 G A イントロン PRDM16 0.002 2 4 1 3041250 T G イントロン PRDM16 0.006 2 4 1 3042502 A T イントロン PRDM16 0.003 4 6 1 3053713 A C イントロン PRDM16 0.002 1 2 1 3063109 C T イントロン PRDM16 0.002 0 2 1 3063593 T C イントロン PRDM16 0.003 1 2 1 3076439 C T イントロン PRDM16 0.001 2 4 1 3078960 G A イントロン PRDM16 0.007 2 4 1 3084268 A C イントロン PRDM16 0.005 0 2 1 3084492 T C イントロン PRDM16 0.0015 0 2 1 3084786 T C イントロン PRDM16 0.0015 0 4 1 3111119 G A イントロン PRDM16 0.003 1 2 1 3111643 C T イントロン PRDM16 0.0041 1 2 1 3114807 G A イントロン PRDM16 0.0041 1 2 1 3165530 C T イントロン PRDM16 0.0089 1 2 1 3169325 G A イントロン PRDM16 0.008 2 4 1 3179623 C T イントロン PRDM16 0.003 2 4 1 3181097 C T イントロン PRDM16 0.001 2 4 1 3194000 G C イントロン PRDM16 0.005 2 4 1 3195769 T C イントロン PRDM16 0.002 1 2 1 3197351 C T イントロン PRDM16 0.0061 1 2 1 3224100 C A イントロン PRDM16 0.003 2 4 1 3228644 G T イントロン PRDM16 0.001 2 4 1 3234045 G A イントロン PRDM16 0.002 1 2 1 3235971 G A イントロン PRDM16 0.0089 1 2 1 3274115 C T イントロン PRDM16 0.001 2 4 1 3291388 G A イントロン PRDM16 0.002 2 4 1 3295658 A C イントロン PRDM16 0.0068 0 6 1 3295937 A C イントロン PRDM16 0.0068 0 2 1 3296205 T C イントロン PRDM16 0.0083 0 2 1 3315690 G A イントロン PRDM16 0.001 2 4 1 3329212 G A エクソン PRDM16 0.0031 ミスセンス 1 2 1 3331787 C T イントロン PRDM16 0.0099 1 2 1 3370316 G C アップストリーム ARHGEF16 0.001 2 4 1 3379560 A G イントロン ARHGEF16 0.0051 0 6 1 3391174 C T イントロン ARHGEF16 0.006 1 2 1 3413873 G A エクソン MEGF6 0.003 ミスセンス 1 2 1 3416272 C T エクソン MEGF6 0.0072 サイレント 2 4 1 3417122 G A イントロン MEGF6 0.0038 2 4 1 3436219 G A イントロン MEGF6 0.0046 2 4 1 12907456 A G エクソン HNRNPCL1、LOC649330 0.006 ミスセンス 0 10 1 12907518 C A エクソン HNRNPCL1、LOC649330 1.0E-4 ミスセンス 0 10 1 12908499 G C イントロン HNRNPCL1 0.0031 0 8 1 12931660 G C インタージェニック PRAMEF2、PRAMEF4 0.004 1 2 1 12937721 G T インタージェニック PRAMEF2、PRAMEF4 0.0038 0 2 1 12940827 G T イントロン PRAMEF4 0.007 2 4 1 12942759 T G イントロン PRAMEF4 0.0076 0 10 1 12942805 T G イントロン PRAMEF4 0.0061 0 12 1 12942812 G A イントロン PRAMEF4 0.0061 0 12 1 12942875 A G イントロン PRAMEF4 0.0068 0 6 1 12942912 G C イントロン PRAMEF4 2.0E-4 0 2 1 12942937 A T エクソン PRAMEF4 0.0029 ミスセンス 0 2 1 12942940 T G エクソン PRAMEF4 0.0038 ミスセンス 0 2 1 12943940 T C イントロン PRAMEF4 0.0015 0 12 1 12944138 A G イントロン PRAMEF4 8.0E-4 0 12 1 12944234 G A イントロン PRAMEF4 0.0015 0 12 1 12944589 T G イントロン PRAMEF4 0.003 0 4 1 12944845 A C イントロン PRAMEF4 0.0014 0 6 1 12946439 T C アップストリーム PRAMEF4 0.0029 0 10 1 12946833 G A アップストリーム PRAMEF4 0.001 0 8 1 12946835 T A アップストリーム PRAMEF4 0.004 0 12 1 12995204 G T インタージェニック PRAMEF8、PRAMEF6 0.003 1 4 1 12997638 T C ダウンストリーム PRAMEF6、PRAMEF9 0.003 2 4 1 13007841 G C アップストリーム PRAMEF6 0.0043 0 8 1 13019228 T A インタージェニック PRAMEF6、LOC391003 0.0015 0 10 1 13038503 G A UTR3 LOC391003 0.0072 1 2 1 13051650 C T インタージェニック LOC391003、PRAMEF5 0.002 2 4 1 15706063 G A イントロン FHAD1 0.0029 1 2 1 15713292 C T イントロン FHAD1 0.001 1 2 1 15766541 G C イントロン CTRC 0.001 1 2 1 15782601 T C アップストリーム CELA2A 0.0038 1 2 1 15828125 G A イントロン CASP9 0.0014 2 4 1 15831037 G A イントロン CASP9 0.0099 1 2 1 15840513 T G イントロン CASP9 0.0043 2 4 1 15868742 G A イントロン DNAJC16 0.001 1 2 1 15876704 G A イントロン DNAJC16 0.001 1 2 1 15900342 C A イントロン AGMAT 0.001 1 2 1 15906257 T C イントロン AGMAT 8.0E-4 1 2 1 15911897 A G アップストリーム AGMAT 0.0043 2 4 1 22764178 C T インタージェニック WNT4、ZBTB40 0.001 2 4 1 22791939 C T イントロン ZBTB40 0.0089 2 4 1 22874394 C G インタージェニック ZBTB40、EPHA8 0.007 1 2 1 22875103 C G インタージェニック ZBTB40、EPHA8 0.007 1 2 1 22906403 C T イントロン EPHA8 0.008 2 4 1 22912956 G A イントロン EPHA8 0.001 1 2 1 22917007 C T イントロン EPHA8 0.001 2 4 1 22927240 G A エクソン EPHA8 0.0013 ミスセンス 2 4 1 22932265 G A インタージェニック EPHA8、MIR6127 0.0089 2 4 1 22944057 C T インタージェニック EPHA8、MIR6127 0.0089 2 4 1 22978799 A G アップストリーム C1QB 0.0099 2 4 1 35170588 C T インタージェニック C1orf94、GJB5 0.01 1 2 1 35172426 C T インタージェニック C1orf94、GJB5 0.008 1 2 1 35172447 G A インタージェニック C1orf94、GJB5 0.001 1 2 1 35175302 C T インタージェニック C1orf94、GJB5 0.008 1 2 1 35177410 A T インタージェニック C1orf94、GJB5 0.001 1 2 1 35178768 C T インタージェニック C1orf94、GJB5 0.0014 2 4 1 35179362 G A インタージェニック C1orf94、GJB5 0.0014 2 4 1 35186166 G A インタージェニック C1orf94、GJB5 0.0099 2 4 1 35186520 A C インタージェニック C1orf94、GJB5 0.002 2 4 1 35196361 G A インタージェニック C1orf94、GJB5 0.0099 2 4 1 35223545 C T エクソン GJB5 0.001 サイレント 1 2 1 35224029 G A UTR3 GJB5 0.003 1 2 1 35227895 T C UTR3 GJB4 5.0E-4 1 2 1 35230455 G T インタージェニック GJB4、GJB3 0.0043 1 2 1 35232954 T C インタージェニック GJB4、GJB3 0.003 1 2 1 35237986 G A インタージェニック GJB4、GJB3 0.0014 1 2 1 35245522 C T インタージェニック GJB4、GJB3 0.001 1 2 1 35256979 C T インタージェニック GJB3、GJA4 0.002 2 4 1 35263872 C T インタージェニック GJA4、SMIM12 5.0E-4 2 4 1 35323895 A C イントロン SMIM12 0.0027 2 4 1 35369676 G A イントロン DLGAP3 0.007 2 4 1 35371634 T A アップストリーム DLGAP3 0.0015 0 4 1 39253519 G A インタージェニック LINC01343、RRAGC 0.005 1 2 1 39288829 G A インタージェニック LINC01343、RRAGC 0.0051 1 2 1 39289832 A C インタージェニック LINC01343、RRAGC 0.002 0 2 1 39312638 G A イントロン RRAGC 0.0038 2 4 1 39361372 G A イントロン RHBDL2 0.005 1 2 1 39363826 T G イントロン RHBDL2 0.0029 1 2 1 39367555 T C イントロン RHBDL2 0.007 2 4 1 39369531 T C イントロン RHBDL2 0.001 2 4 1 39370202 T C イントロン RHBDL2 0.01 1 2 1 39449101 A G インタージェニック RHBDL2、AKIRIN1 0.001 2 4 1 39475057 G A インタージェニック AKIRIN1、NDUFS5 0.01 1 2 1 39485016 C T インタージェニック AKIRIN1、NDUFS5 0.001 2 4 1 39488137 A G インタージェニック AKIRIN1、NDUFS5 0.001 2 4 1 39499212 A C イントロン NDUFS5 0.001 0 2 1 39500605 C G ダウンストリーム NDUFS5 0.002 0 10 1 46813814 T C イントロン NSUN4 0.0014 1 2 1 46817258 A G イントロン NSUN4 0.005 0 2 1 46843158 T C インタージェニック NSUN4、FAAH 0.001 1 2 1 46933509 A G インタージェニック LINC01398、DMBX1 0.002 1 2 1 46935021 G A インタージェニック LINC01398、DMBX1 0.004 2 4 1 46939253 T A インタージェニック LINC01398、DMBX1 0.004 2 4 1 46951788 C A インタージェニック LINC01398、DMBX1 0.002 2 4 1 46980864 G C ダウンストリーム DMBX1 0.003 1 2 1 46989657 T C インタージェニック DMBX1、MKNK1-AS1 0.007 1 2 1 46994678 C T インタージェニック DMBX1、MKNK1-AS1 0.002 1 2 1 46999438 T C インタージェニック DMBX1、MKNK1-AS1 0.002 1 2 1 92761505 A G イントロン GLMN 0.001 2 4 1 92764270 G C イントロン GLMN 0.001 2 4 1 92802210 G A イントロン RPAP2 0.0072 1 2 1 92820663 T A イントロン RPAP2 0.0058 1 2 1 92820664 G T イントロン RPAP2 0.0058 1 2 1 92820953 G A イントロン RPAP2 0.007 2 4 1 92824766 A G イントロン RPAP2 0.0058 1 2 1 92849183 C A イントロン RPAP2 0.01 2 4 1 92850696 C G イントロン RPAP2 0.0023 1 2 1 92861357 T C インタージェニック RPAP2、GFI1 0.01 2 4 1 92877460 C G インタージェニック RPAP2、GFI1 0.002 1 2 1 92880643 A G インタージェニック RPAP2、GFI1 0.001 2 4 1 92911540 G A インタージェニック RPAP2、GFI1 0.004 2 4 1 92911721 A C インタージェニック RPAP2、GFI1 0.0031 0 8 1 92918277 C T インタージェニック RPAP2、GFI1 0.001 2 4 1 92950920 G A イントロン GFI1 0.008 2 4 1 92964788 G A インタージェニック GFI1、EVI5 0.0023 1 2 1 92977480 C T UTR3 EVI5 0.002 1 2 1 92985213 C T イントロン EVI5 0.001 2 4 1 92988342 C T イントロン EVI5 0.008 2 4 1 92992283 G A イントロン EVI5 0.01 2 4 1 92999760 C T イントロン EVI5 0.003 1 2 1 93005149 G C イントロン EVI5 0.003 0 4 1 93018543 A T イントロン EVI5 0.01 2 4 1 93033744 C T イントロン EVI5 0.001 2 4 1 111400296 G A インタージェニック KCNA3、CD53 0.0014 2 4 1 111411924 C T インタージェニック KCNA3、CD53 0.003 1 2 1 111441850 C G UTR3 CD53 0.003 2 4 1 111451527 C T インタージェニック CD53、LRIF1 0.008 2 4 1 111454082 C A インタージェニック CD53、LRIF1 0.001 2 4 1 111466506 A G インタージェニック CD53、LRIF1 0.001 2 4 1 111525974 G A インタージェニック LRIF1、DRAM2 0.002 2 4 1 111574573 G T インタージェニック LRIF1、DRAM2 0.0072 2 4 1 111574594 T A インタージェニック LRIF1、DRAM2 0.005 1 2 1 111574647 G A インタージェニック LRIF1、DRAM2 0.005 1 2 1 111591746 T A インタージェニック LRIF1、DRAM2 0.005 1 2 1 111601459 A G インタージェニック LRIF1、DRAM2 0.005 1 2 1 111604748 G C インタージェニック LRIF1、DRAM2 0.005 1 2 1 112191526 T G イントロン RAP1A 0.001 2 4 1 112206765 A G イントロン RAP1A 0.0043 1 2 1 112226517 G A イントロン RAP1A 0.001 0 2 1 112263324 G T インタージェニック RAP1A、FAM212B 0.003 2 4 1 112264843 G A UTR3 FAM212B 0.001 1 2 1 112285810 C T ncRNA_intronic FAM212B-AS1 0.004 1 2 1 112304285 T C イントロン DDX20 0.0043 1 2 1 112307213 A C イントロン DDX20 0.0043 1 2 1 112309436 G T エクソン DDX20 0.0 ミスセンス 1 2 1 112317384 T C インタージェニック DDX20、KCND3 0.0014 1 2 1 112381367 C T イントロン KCND3 0.002 1 2 1 112396571 G T ncRNA_exonic KCND3-IT1 0.001 1 2 1 113520038 G A インタージェニック SLC16A1-AS1、LOC100996251 0.0023 1 2 同様のクエリを 1,000 人ゲノムフェーズ 3 からのデータに対して実行すると、約 90 秒かかり、約 3.38 TB 分のデータが処理されます。