Update instructions to clarify standard SQL versus legacy SQL

[tomasramanauskas]

Hello,

I'm new to BigQuery and trying to follow the examples, but all 3 examples fail.

I do have access to the tables and can run simple count(*) queries on them.

Here is one example that I'm getting an error:

SELECT
REGEXP_EXTRACT(reference_name, r'chr(.+)') AS chr,
start AS start,
reference_bases,
alt,
call.call_set_name
FROM
genomics-public-data.platinum_genomes.variants v,
v.call call,
v.alternate_bases alt WITH OFFSET alt_offset
WHERE
-- Require that at least one genotype matches this alternate.
EXISTS (SELECT gt FROM UNNEST(call.genotype) gt WHERE gt = alt_offset+1)

Query Failed
Error: Encountered " "OFFSET" "OFFSET "" at line 10, column 32. Was expecting:

Any help is appreciated.

[tomasramanauskas]

I'm talking about the queries from platinumGenomes:

https://github.com/googlegenomics/bigquery-examples/tree/master/platinumGenomes

[deflaux]

@tomasramanauskas sorry for the trouble! We should clarify that some of the queries in this repository are legacy SQL whereas others are standard SQL.

Here are the instructions to enable standard SQL which will allow those particular queries you mention to work: https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql

See also https://github.com/googlegenomics/getting-started-bigquery for some starter queries.

[tomasramanauskas]

@deflaux thanks! Will have a look at it.

[tomasramanauskas]

@deflaux "Legacy SQL" vs "Standard SQL" works just fine.

However I was able to query silver-wall-555.TuteTable.hg19 AS annots on Friday and today I get:
Error: Not found: Project silver-wall-555

Has the data been removed and you can't access it too?

[deflaux]

You are correct - that table is currently unavailable. Here are the details about it: http://googlegenomics.readthedocs.io/en/latest/use_cases/discover_public_data/tute_genomics_public_data.html We have reached out to the owners of the table. Hopefully it will be restored soon.

[tomasramanauskas]

@deflaux thanks, yes would be good to have this table back. I hope it's just a coincidence and not related to "PierianDx Acquires Tute Genomics" http://pieriandx.com/news/pieriandx-acquires-tute-genomics/

[deflaux]

The Tute table has been restored.

[tomasramanauskas]

@deflaux Great! I can confirm it works for me. Thanks