Bulk Transferring and Snapshotting Data in BigQuery

Created by Ben Deverman, Modified on Wed, 22 Jan at 12:57 PM by Ben Deverman

PAD offers several methods for creating snapshots and moving data in bulk between datasets. Depending on whether you're working with static tables, views, or materialized views, you have a few options available. These range from simple point-and-click features in the UI to running lightweight code. Below are a couple of methods to accomplish your needs.

Static Tables: If you're looking to copy multiple static tables, BigQuery's Data Transfer Service is a fast, straightforward point-and-click solution. Here's how it works:

While logged into BigQuery, select the dataset or table you want to transfer.
Click the “Copy" button.
Choose the Destination Project, Dataset, and Table.
Hit the "Copy" button at the bottom to initiate the transfer.

Once the job has been executed, you can further modify it to run once, on-demand, or on a schedule. Please note that this method is only available for static tables, not views or materialized views.

For more information on Data Transfer Service, refer to Google's documentation here.

Static Tables, Views, & Materialized Views: Datasets often include a mix of static tables, views, and materialized views. Unfortunately, BigQuery’s UI doesn’t reliably copy views as static tables. However, you can use procedural SQL to “loop” through each table in a dataset—whether it’s a static table, view, or materialized view—and create static snapshots of them in bulk!

To do this, simply copy the code below, update your source and destination variables, and run it.

For more on procedural SQL, refer to Google’s documentation here.

DECLARE source_project STRING DEFAULT 'source-project-id';
DECLARE source_dataset STRING DEFAULT 'source-dataset';
DECLARE target_project STRING DEFAULT 'target-project-id';
DECLARE target_dataset STRING DEFAULT 'target-dataset';

DECLARE view_name STRING;
DECLARE view_query STRING;
DECLARE create_table_sql STRING;

-- Step 1: Loop over all views, materialized views, and static (base) tables in the source dataset
FOR table_row IN (
  SELECT table_name, table_type
  FROM `project.dataset.INFORMATION_SCHEMA.TABLES`
  WHERE table_type IN ('VIEW', 'MATERIALIZED VIEW', 'BASE TABLE')
) DO

  -- Extract the table_name from the STRUCT
  SET view_name = table_row.table_name;

  -- Step 2: Get the SQL query for the current view, materialized view, or static table
  IF table_row.table_type = 'VIEW' THEN
    -- For regular views, we retrieve the view_definition
    SET view_query = (
      SELECT view_definition
      FROM `project.dataset.INFORMATION_SCHEMA.VIEWS`
      WHERE table_name = view_name
    );
  -- For materialized views, we select the content directly
  ELSEIF table_row.table_type = 'MATERIALIZED VIEW' THEN
    SET view_query = FORMAT("SELECT * FROM `%s.%s.%s`", source_project, source_dataset, view_name);
  -- For static (base) tables, we can directly reference the table
  ELSEIF table_row.table_type = 'BASE TABLE' THEN
    SET view_query = FORMAT("SELECT * FROM `%s.%s.%s`", source_project, source_dataset, view_name);
  END IF;

  -- Step 3: Generate the SQL to create a static table in the target dataset
  SET create_table_sql = FORMAT("""
    CREATE OR REPLACE TABLE `%s.%s.%s` AS %s
  """, target_project, target_dataset, view_name, view_query);

  -- Step 4: Execute the SQL to create and populate the table
  EXECUTE IMMEDIATE create_table_sql;

END FOR;

Have questions? Contact us at [email protected]!