How To Deploy SQL Scripts On BigQuery Dataset?

Contents hide

1 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`
(
  id INTEGER,
  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`()
BEGIN
    SELECT * FROM `mydataset.mytable` LIMIT 10;
END;

-- 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