CODEWITHSUNDEEP
google-bigquerylegacy-sql
PK109
PK109

Reputation: 75

BigQuery create Table differences between standard and legacy sql

I have a few questions around the create table syntax in standard and legacy sql

  1. The new BigQueryUI doesn't show standard sql types and shows only legacy types. I understand they are mapped one to one with the legacy types but the examples in creating partitioned tables shows options which are not available in the UI
  2. If I create a table using the JSON field schema can I still use the standard sql?
  3. The BigQueryUI shows only partitioning the table by Ingestion time, but I want to create a table with date column and I don't see an option for it. If I have to create the DDL manually, I did not see the examples on how to use JSON field schema to construct a create table statement.

Upvotes: 0

Views: 1477

Answers (1)

Tamir Klein
Tamir Klein

Reputation: 3632

The new BigQueryUI doesn't show standard sql types

BigQuery standardSQL and LegacySQL are 2 options to write SQL syntax (See this link for more detail) and have nothing to do with the column Types in BigQuery, Details on table types can be found in this link, I also find this Link helpful

If I create a table using the JSON field schema can I still use the standard sql?

To create a table using JSON you need to run bq command line, If you need help how to write this syntax let us know

but I want to create a table with date column and I don't see an option for it

You can use this standardSQL syntax to do this:

#standardSQL
CREATE OR REPLACE TABLE `project.dataset.tableId`
PARTITION BY myDate
CLUSTER BY cluster_col AS
SELECT * from sourceTable

Note: myDate column is a column in the source table

Upvotes: 1

Related Questions