Load GFF v3 data to BigQuery

convert-to-geography

SELECT 
seq_id,source,type,seq_start,seq_end,
ST_MAKELINE(ST_GEOGPOINT(LOG(seq_start)/LOG(10),-0.01),ST_GEOGPOINT(LOG(seq_end)/LOG(10),0.01)) AS range_log10,
score,strand,phase,attributes
FROM `project.dataset.tablename`
LIMIT 1000

gff-schema.json

[
  {
    "mode": "NULLABLE",
    "name": "seq_id",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "source",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "type",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "seq_start",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "seq_end",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "score",
    "type": "STRING"
  },
  {
    "mode": "NULLABLE",
    "name": "strand",
    "type": "INTEGER"
  },
  {
    "mode": "NULLABLE",
    "name": "phase",
    "type": "INTEGER"
  },
  {
    "mode": "REPEATED",
    "name": "attributes",
    "type": "RECORD",
    "fields": [
      {
        "mode": "NULLABLE",
        "name": "tag",
        "type": "STRING"
      },
      {
        "mode": "REPEATED",
        "name": "value",
        "type": "STRING"
      }
    ]
  }
]

gff_to_bigquery.pl

#!/usr/bin/perl

# gsutil cat gs://some/where.gff | grep -vE '^#' | perl ./gff_to_bigquery.pl | gsutil cp - gs://some/where.gff.json

use strict;
use JSON;

while ( my $line = <> ) {
  chomp $line;
  my ($seqid_,$source_,$type_,$start_,$end_,$score_,$strand_,$phase_,$attributes) = split /\t/, $line;
  my $seqid = $seqid_ eq '.' ? "" : $seqid_;
  my $source = $source_ eq '.' ? "" : $source_;
  my $type = $type_ eq '.' ? "" : $type_;
  my $start = $start_ eq '.' ? "" : int($start_);
  my $end = $end_ eq '.' ? "" : int($end_);
  my $score = $score_ eq '.' ? "" : $score_;
  my $strand = $strand_ eq '+' ? 1 : $strand_ eq '-' ? -1 : "";
  my $phase = $phase_ eq '.' ? "" : int($phase_);
  my @attr = split ';', $attributes;
  my @aout = ();
  foreach my $a (@attr) {
    my ( $k, $v ) = $a =~ m/^(.+?)=(.+?)$/;
    my @v = split ',', $v;
    my %attr = ();
    #$attr{ $k } = \@v;
    $attr{ tag } = $k;
    $attr{ value } = \@v;
    push @aout, \%attr;
  }
  print JSON::encode_json({
    seq_id => $seqid,
    source => $source,
    type => $type,
    seq_start => $start,
    seq_end => $end,
    score => $score,
    strand => $strand,
    phase => $phase,
    attributes => \@aout,
  }), "\n";
}