CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquery
Bish
Bish

Reputation: 339

BigQuery: Copy Views between Datasets in Bulk

Is there a way to copy Views in bulk from one dataset to another in Google BigQuery?

Currently you cannot copy views in BigQuery from one dataset to another in bulk. According to the documentation:

Currently, you can copy a view only by using the Google Cloud console.

The bq command-line tool does not support copying a view. Instead, you must recreate the view in the target dataset.

Even when using the Dataset Copy service from BigQuery, you can only copy tables, not views.

Currently, I haven't found a standard way but I have found a hack.

Upvotes: 0

Views: 1333

Answers (1)

Bish
Bish

Reputation: 339

This is the hack I am using:

Let's say you were trying to copy a whole heap of views from the google_ads dataset to the google_adwords dataset. You can edit the ddl of the view in situ and run an EXECUTE IMMEDIATE statement, like so:

FOR tx IN (
    SELECT 
        table_schema,
        table_name,
        table_type,
        REPLACE(ddl, '.google_ads.', '.google_adwords.') AS new_ddl
    FROM
        google_adwords.INFORMATION_SCHEMA.TABLES
    WHERE
        table_type = 'VIEW'
        AND table_name NOT LIKE 'test\\_%'
    ORDER BY 1, 2
)
DO EXECUTE IMMEDIATE FORMAT(
    tx.new_ddl
);
END FOR;

The DDL values look like this example:

CREATE VIEW 
    `my-project-name.google_adwords.ads_AccountBasicStats_0123456789`
AS 
    SELECT 
        *, 
        DATE ('2023-05-07') AS _LATEST_DATE, 
        DATE (_PARTITIONTIME) AS _DATA_DATE, 
    FROM 
        `123456789876.google_adwords.p_ads_AccountBasicStats_0123456789`;

So, essentially using the SQL above we are replacing the schema name and then running the DDL statement using EXECUTE IMMEDIATE.

Once, that's done, we can delete the source Views using another EXECUTE IMMEDIATE:

FOR tn IN (
    SELECT
        table_schema,
        table_name,
        CASE
            table_type
            WHEN 'BASE TABLE' THEN 'TABLE'
            ELSE table_type
        END AS table_type
    FROM
        google_ads.INFORMATION_SCHEMA.TABLES
    WHERE 
        table_name NOT LIKE 'test\\_%'
) 
DO EXECUTE IMMEDIATE FORMAT(
    "DROP %s %s.%s",
    tn.table_type,
    tn.table_schema,
    tn.table_name
);
END FOR;

Of course, be careful with the delete bit !!

Upvotes: 1

Related Questions