BigQuery での区間 JOIN の実行

BigQuery を使用すると、バリアントと、ゲノム領域区間によって記述されるデータ(オーバーラップとも言う)との JOIN クエリを実行できます。このページでは、複合 JOIN クエリを使用して遺伝子名のリストを取得するとともに次のことを行う方法を説明します。

  • 遺伝子にオーバーラップする希少 SNP を見つける
  • ゲノムサンプル全体について、遺伝子のいずれかの側の 100,000 塩基対を見つける

次の 3 つのクエリがあり、どれも BigQuery がさまざまなサイズのゲノムデータに対してスケーリングできることを示しています。

このデータは、90 億近くの行を持つ Tute Genomics Annotation テーブルと Illumina Platinum Genomes データセットからのものです。これらのデータセットの情報については、次に示すリンク先をご覧ください。

インライン テーブルを使用して区間 JOIN を実行する

この例では、クエリの中で定義された区間テーブル intervals を使用し、Illumina Platinum Genomes からのバリアントが格納されているテーブルとの JOIN クエリを実行する方法を説明します。

  1. BigQuery UI に移動します。

    BigQuery UI に移動する

  2. [Compose Query] をクリックします。

  3. [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;
    
  4. [RUN QUERY] をクリックします。このクエリの実行には約 10 秒かかり、約 334 GB 分のデータが処理されます。このクエリの結果は、コホート内の希少バリアントのうち、注目している領域とオーバーラップするものを示します。

    同様のクエリを 1,000 人ゲノムフェーズ 3 からのデータに対して実行すると、約 90 秒かかり、約 3.38 TB 分のデータが処理されます。

実体化テーブルを使用する

大規模なビッグデータを扱うときは、区間テーブルを実体化してその新しいテーブルに対して JOIN クエリを実行できます。

特定の遺伝子との区間 JOIN を実行する

次に示す例では、新しい区間テーブルを実体化する方法を説明します。この中には、silver-wall-555:TuteTable.hg19 テーブルからの特定の遺伝子のリストが格納されます。

  1. BigQuery UI に移動します。

    BigQuery UI に移動する

  2. 次の手順でデータセットを作成します。

    1. ナビゲーションで、Google Cloud Platform プロジェクトの横にある矢印アイコンをクリックし、[Create new dataset] をクリックします。

    2. [Dataset ID] に「genomics」と入力します。

    3. その他のデフォルト設定はそのままにして、[OK] をクリックします。

  3. 次の手順で区間テーブルを作成します。

    1. BigQuery で、[Compose Query] をクリックします。

    2. [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 );
      
      1. [RUN QUERY] をクリックします。
  4. [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;
    
  5. [RUN QUERY] をクリックします。このクエリの実行には約 10 秒かかり、約 334 GB 分のデータが処理されます。このクエリの結果は、コホート内の希少バリアントのうち、注目している領域とオーバーラップするものを示します。

    同様のクエリを 1,000 人ゲノムフェーズ 3 からのデータに対して実行すると、約 90 秒かかり、約 3.38 TB 分のデータが処理されます。

ランダムな遺伝子との区間 JOIN を実行する

次に示す例では、区間 JOIN を実体化テーブルに対して実行する方法を説明します。このテーブルに格納される 250 個の遺伝子は、silver-wall-555:TuteTable.hg19 テーブルからランダムに選択されたものです。

  1. BigQuery UI に移動します。

    BigQuery UI に移動する

  2. 次の手順でデータセットを作成します。

    1. ナビゲーションで、Google Cloud Platform プロジェクトの横にある矢印アイコンをクリックし、[Create new dataset] をクリックします。

    2. [Dataset ID] に「genomics」と入力します。

    3. その他のデフォルト設定はそのままにして、[OK] をクリックします。

  3. 次の手順で区間テーブルを作成します。

    1. BigQuery で、[Compose Query] をクリックします。

    2. [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 );
      
      1. [RUN QUERY] をクリックします。
  4. [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;
    
  5. [RUN QUERY] をクリックします。このクエリの実行には約 10 秒かかり、約 334 GB 分のデータが処理されます。このクエリの結果は、コホート内の希少バリアントのうち、注目している領域とオーバーラップするものを示します。

    同様のクエリを 1,000 人ゲノムフェーズ 3 からのデータに対して実行すると、約 90 秒かかり、約 3.38 TB 分のデータが処理されます。

このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...