Hi is there a way to programmatically detect if a query string is in the legacy or SQL-2011 syntax? I know the former uses [project:dataset.table] for table references while the later uses `project.dataset.table` but this doesn't seem very bullet proof.
0
votes
2 Answers
4
votes
There's no way to tell just from the query text in all cases, which is why BigQuery has the "Use Legacy SQL" checkbox in the UI and the use_legacy_sql option for the query API. For example, consider this query:
SELECT *
FROM (SELECT 1 AS x), (SELECT 2), (SELECT 3);
The results are very different despite the query being valid in both dialects.
Standard SQL queries can still contain [], too, such as for array literals.
1
votes
Assuming query is syntax-wise correct and expected to actually work - you can do Dry Run using both options (Legacy and Standard) and see which fails and which not. Based on result you can potentially derive the answer
