How to deploy SQL Scripts on BigQuery Dataset?

How to deploy SQL Scripts on BigQuery Dataset?

Problem Description:

I have few create table and procedure sql scripts that I want to deploy on my BigQuery dataset. I am looking for a Oracle’s "@" or Snowflake’s "!Source" equivalent in BigQuery.

I am new to BigQuery and have I not tried to create a object using sql file.

Solution – 1

Simple table & routine creation in BigQuery:

-- Create table mytable
CREATE TABLE `mydataset.mytable`
  name STRING

-- Insert values into mytable
INSERT INTO `mydataset.mytable` VALUES (1, 'name1'), (2, 'name2');

-- Select values from mytable
SELECT * FROM `mydataset.mytable`;

-- Creating a stored procedure
CREATE PROCEDURE `mydataset.myproc`()
    SELECT * FROM `mydataset.mytable` LIMIT 10;

-- Executing a stored procedure
CALL `mydataset.myproc`();

-- Dropping a table
DROP TABLE `mydataset.mytable`;

-- Dropping a procedure
DROP PROCEDURE `mydataset.mytable`;

Reference: BigQuery DDL & Procedural languages

If you want execute scripts from a SQL script file – one option is to use bq command-line tool for example:

bq query --use_legacy_sql=false < sqlbackup.sql

Reference: bq command-line tool

Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.