Pages

Oct 5, 2018

[HDGEM] SQL limitations of Google Cloud BigQuery

While BigQuery claims to have added support for ANSI SQL, it still has several cases where query syntax deviates from ANSI SQL for unknown reasons. 

Below is a sample of such limitations:


  • Does not support the ANSI SQL syntax of substring. Need to change to substr.
  • Does not support Top N. Only supports LIMIT N at the end of the SQL
  • Does not support NUMERIC. Only supports INT or FLOAT. There is a loss of precision on many queries (data quality). TPC-DS queries set a specific number of positions for intermediate calculations with CAST. It is not supported by BigQuery.
  • DATE datatype is new and none of BigQuery's functions support it yet in ANSI. Even if you try to convert to TIMESTAMP first, DATEDIFF and DATE_ADD does not work. BigQuery in "Legacy SQL" mode supports this but then joins don't really work.
  • While Group by <expression> allows positioning references, copying an expression (substr for example) from the projection down to the group by is not allowed. You have to use positioning.
  • Does not support INTERSECT or EXCEPT.
  • Does not support UNION syntax. Requires you to use UNION DISTINCT or ALL.
  • Column Alias needs to be a valid identifier. Should only be letters or numbers. No quotes can be used. So "SUM OF SALES" is not a valid alias.
  • You always have to prefix the dataset name on all SQL. No concept of search path or default schema/database.


--
Posted By Blogger to HDGEM at 3/23/2017 08:51:00 AM

QuickBooks Self-Employed

Bigger tax refunds. Better organization. Manage your deductions with QuickBooks Self-Employed .