CODEWITHSUNDEEP
google-bigquery
Altimus Prime
Altimus Prime

Reputation: 2327

Create table SQL syntax in Google Bigquery

I've been reading the bigquery documentation since late last night and understand very little of it. It talks about loading data via different methods, but doesn't say how to create the table that I'm going to load data into. When I use the web UI it expects me to type out the schema. My table has over 400 columns. I will not type out hundreds of column names, types and lengths.

I've been uploading hundreds of GB of data in csv format to a google bucket. The csv files do not have column names. I have the schema in sql format which I prefer to use.

If I try creating a table through a query I get an error already on line 2 that says, 

"Error: Encountered "" at line 2, column 1."

CREATE TABLE [example-mdi:myData_1.ST] (
`ADDRESS_ID` varchar(9),
`INDIVIDUAL_ID` varchar(2),
`FIRST_NAME` varchar(25),
`LAST_NAME` varchar(2),...

How can I do this or what is the right way?

Upvotes: 13

Views: 110561

Answers (5)

Vishwanath Biswal
Vishwanath Biswal

Reputation: 11

Syntax:

CREATE OR REPLACE TABLE `projectid.datasetid.tablename`
(FIELD1 TYPE, FIELD2 TYPE, FIELD3 TYPE);

Note: Enclosed projectid.datasetid.tablename inside backtick (``) not single or double quote.

Example:

CREATE TABLE `mars.dev_project.sales_data` (
  timestamp DATETIME,
  Serial_No STRING,
  Entry_No INT64,
  Item_No STRING,
  Location_Code STRING,
  Quantity FLOAT64,
  Order_No STRING,
  Posting_Date DATE,
  Order_Source STRING,
  Order_Date DATE,
  Flag STRING DEFAULT 'YES',
  Created_At DATE
);

Also I found interesting this video, where he has explained 6 different ways to create table in BigQuery:

Upvotes: 0

Chadwick Robbert
Chadwick Robbert

Reputation: 1036

As of April 2021, complex tables can be fully created directly using standard SQL (see the specific syntax guide).

CREATE TABLE IF NOT EXISTS `project.dataset.table_name`
(
    someName STRING
    , dateTime TIMESTAMP NOT NULL -- REQUIRED or non-null column
    , index INT64 -- INT64 for INTEGER column
    , longitude FLOAT64 -- FLOAT64 for FLOAT column
    , arr ARRAY< -- declaring Array. This ARRAY is of datatype STRUCT
        STRUCT< -- declaring STRUCT
            a FLOAT64 -- the individual STRUCT members do not need the STRUCT column name again
            , b STRING
        >
    >
);

Upvotes: 3

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

When you create table in Web UI - you can enter schema field by field (Edit as Fields mode - default mode) or you can enter schema as a text (Edit as Text mode)
So, if you already have your schema in sql format you can just use it (you will might need to slightly adjust it to conform with BigQuery)

See more about creating tables with different clients (in We bUI section above option is not presented, so that's why you missed it I think)

P.S. As of today, BigQuery doe not support DDL - so CREATE TABLE is not available

Update

As of today - Jan 17, 2018 - BigQuery data definition language support is now in Beta

Upvotes: 2

Elliott Brossard
Elliott Brossard

Reputation: 33745

You can use a CREATE TABLE statement to create the table using standard SQL. In your case the statement would look something like this:

CREATE TABLE `example-mdi.myData_1.ST` (
  `ADDRESS_ID` STRING,
  `INDIVIDUAL_ID` STRING,
  `FIRST_NAME` STRING,
  `LAST_NAME` STRING,
  ...
);

Upvotes: 22

Altimus Prime
Altimus Prime

Reputation: 2327

Mikhail is right and gets credit for the answer. If you're as slow as me you're going to want more details, because after he pointed me the right way it still took a while to figure out what he's talking about and how to get it done.

When you're at the create table user interface click the "edit as text" link.

enter image description here

In the text box input that pops up you'll enter something like:

ADDRESS_ID:string,
INDIVIDUAL_ID:string,
First_name:string,
Last_name:string...

Hyphens are not permitted.

Upvotes: 3

Related Questions