CODEWITHSUNDEEP
google-bigquery
user_1357
user_1357

Reputation: 7940

BigQuery: Run queries to create table and append to the table if table exist

Is it possible to run query to create a table if it does not exist, and append to the table if the table already exists? I like to write a single query to create or append. Note: I am using Admin console for now, will be using API eventually.

I have following query: 

CREATE TABLE IF NOT EXISTS `project_1.dataset_1.tabe_1`
 OPTIONS(
   description="Some desc"
 ) AS
 SELECT *
 FROM source_table

I get following error:

A table named project_1.dataset_1.tabe_1 already exists.

Upvotes: 1

Views: 3277

Answers (2)

ahmetpergamum
ahmetpergamum

Reputation: 113

Above query creates a table named 'table_1' if it does not exist under 'project_1.dataset_1', and append to the table if the table already exists.

IF
  (
  SELECT
    COUNT(1)
  FROM
    `project_1.dataset_1.__TABLES_SUMMARY__`
  WHERE
    table_id='table_1') = 0 
THEN
    CREATE OR REPLACE TABLE
      `project_1.dataset_1.table_1` AS
    SELECT
      'apple' AS item,
      'fruit' AS category
    UNION ALL
    SELECT
      'leek',
      'vegetable';
  
ELSE
    INSERT
      `project_1.dataset_1.table_1` ( item,
        category )
    SELECT
      'lettuce' AS item,
      'vegetable' AS category
    UNION ALL
    SELECT
      'orange',
      'fruit';
END IF;

Upvotes: 4

shollyman
shollyman

Reputation: 4384

This seems like it may be a good opportunity to leverage scripting within a single query to accomplish your needs.

See this page for adding control flow to a query to handle an error (e.g. if the table create fails due to existing). For the exception case, you could then INSERT ... SELECT statement as needed.

You can do this via the API as well if you prefer. Simply issue a tables.get equivalent appropriate to the particular library/language you choose and see if the table exists, and then insert the appropriate query based on that outcome of that check.

Upvotes: 0

Related Questions