Search code examples
google-bigquerypartitioninglegacy-sql

bigquery - partition on custom fields -- only for standardSQL?

I just saw that I can create a custom partitioned table in BigQuery. Meaning partitioned by any timestamp column.

However, the newly created table can only be accessed and queried from StandardSQL!

Is there a way to query the new tables from Legacy?

enter image description here

Solution

  • Just at the begining of the documentation says in limitations you don't have Legacy SQL support.

    Partitioned tables are subject to the following limitations:

    • The partitioning column must be either a scalar DATE or TIMESTAMP column. While the mode of the column may be REQUIRED or NULLABLE, it cannot be REPEATED (array-based). Additionally, the partitioning column must be a top-level field. You cannot use a leaf field from a RECORD (STRUCT) as the partitioning column.
    • You cannot use legacy SQL to query partitioned tables or to write query results to partitioned tables.
    • You cannot use wildcard tables when querying partitioned tables.
    • You cannot use DML statements to modify partitioned tables.