0

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.
PK109
  • 53
  • 8

1 Answers1

1

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

Tamir Klein
  • 3,514
  • 1
  • 20
  • 38
  • Thanks Tamir. What if the table I want to create is a brand new one into which I want to stream data? Then it appears I have to create a dummy table with no records and use the query you provided to create the table I want. – PK109 Mar 11 '19 at 08:08
  • Correct this is the way. If you find my answer helpful for you please consider accepting it and voting up – Tamir Klein Mar 11 '19 at 08:22
  • @PK109, note: important on SO - you can mark accepted answer by using the tick on the left of the posted answer, below the voting. See [This](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work#5235) for why it is important! – Tamir Klein Mar 14 '19 at 12:07