CODEWITHSUNDEEP
google-bigquerypartitioninglegacy-sql
Ilja
Ilja

Reputation: 1053

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

Upvotes: 1

Views: 4045

Answers (2)

Ruben Portz
Ruben Portz

Reputation: 334

You just have to state LegacySql to false, for example:

var projectId = 'xxxxxxx';
    
var request = {
  query: 'select * from project.database.table',
  useLegacySql: false
};
var queryResults = BigQuery.Jobs.query(request, projectId);

Upvotes: 0

Pentium10
Pentium10

Reputation: 207982

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.

Upvotes: 4

Related Questions