Batch rename BigQuery tables with SQL
Tags: bigquery, gcp, sql, regex
Published: 2023-08-03 10:13:58

**TLDR**; 
Recently I had a situation where we had 400+ wildcard tables that were not named correctly. I used a single SQL statement to programatically fetch all the table names and rename them based on a regex. 


## What are wildcard tables and what can you do with them

> Wildcard tables are individual tables that have the same preffix e.g.: `sales_20220101`, `sales_20220102`, etc.
For more details see the [official documentation.](https://cloud.google.com/bigquery/docs/querying-wildcard-tables)

BigQuery has a filter function `_TABLE_SUFFIX` that allows you to do some clever stuff with wildcard tables:

```sql
SELECT
count(*)
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE _TABLE_SUFFIX BETWEEN '29' and '35'
```
This will select all the tables `1929, 1930 ... 1935`.

I personally prefer partitioned tables, but wildcard have their uses. Namely when you load data from a 3rd party and you expect that they might change their schema at some point in the future. 
More on that in another article.

## The problem 

* Tables were annoyingly named `ABC_20220101_0700_BLA` which made using `_TABLE_SUFFIX` impossible.

* Luckily with a simple string subset I can easily get to the correct naming schema `ABC_20220101`.

* However the biggest issue was the volume of tables (over 400), so anything manual was out of the question. 

## The solution
My first thought was to use the BigQuery Python API, but I decided to challenge myself and use just SQL.
There are several features in BQ SQL that I used:

* Data definition language (DDL) - you know this from `CREATE TABLE` or `DROP TABLE`, But did you know there is 
[ALTER TABLE](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#alter_table_rename_to_statement) as well

* Procedural language - Most SQL users will be familiar with `DECLARE` and `SET` keywords. But BigQuery SQL allows you add complex logic like `IF` and `WHILE` statements. In this problem I used [FOR IN](https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in)

* `INFORMATION_SCHEMA` is a special "dataset" that has several views which give you a lot of metadata about your warehouse (datastes, tables, views, etc.).
Read more about it [here](https://cloud.google.com/bigquery/docs/information-schema-intro).

The complete code:
```sql
FOR t IN
  (
      SELECT 
        table_id as old_name,
        regexp_extract(table_id, r"ABC_\d{8}") as new_name
      FROM my_dataset.__TABLES__
  )
DO
EXECUTE IMMEDIATE (
    "ALTER TABLE my_dataset." || t.old_name || " RENAME TO " || t.new_name
);
END FOR;
```

-------
## Explanation:


```sql
FOR t IN
  (
      -- some query that we loop over
  )
DO
  -- run some other query 
END FOR;
```
* The `FOR` loop allows us to itterate over each row of any query



```sql
SELECT 
  table_id as old_name,
FROM my_dataset.__TABLES__
```
* `my_dataset.__TABLES__` wil return a table that has a lot of information about each table in this dataset. 
I only cared about the name of the tables: `table_id`


```sql
  regexp_extract(table_id, r"ABC_\d{8}") as new_name
```
* The regular expression only takes the literal string `ABC_` and then 8 digits 


```sql
EXECUTE IMMEDIATE (
    "ALTER TABLE my_dataset." || t.old_name || " RENAME TO " || t.new_name
);
```
* `ALTER TABLE my_dataset.x RENAME TO y` does exactly what it says on the tin - renames a table. (Don't forget to add the dataset) 
* `EXECUTE IMMEDIATE` allows us to run a string as a query. Think of it as `eval` in Python.
* BigQuery SQL syntax will not let us to `SELECT ... FROM my_dataset.some_var` because it will treat `some_var` as literal rather than replacing it with its value. 
* That is why we need to first build a string of the query. And that's what the `||` chars are for. They just concatenate strings. 
* Here is what the evaluated string looks like:
`"ALTER TABLE my_dataset.ABC_123_BLA  RENAME TO ABC_123_BLA "`

## Final thoughts:

I was getting some weird error:
```
Not found: Dataset my-project:my_dataset was not found in location US at [3:7]
```
It turns out that using the bacticks "`" around my table and dataset names was causing this - so I removed them and it all worked :shrug: