Managing UDFs in dbt

Ivan N — Fri, 04 Aug 2023 11:53:15 +0000

TLDR;
I used dbt's macro and on-run-end hook to create UDFs in BigQuery.

I rencetly had a uses case where we were migrating a customer warehouse to dbt. UDFs were widely used and we wanted to preserve them to make the migration easier.

However dbt does not have any special way of managing UDFs. This mean that you need to write the DDL (data definition language) toCREATE andDROP the UDFs yourself 😱

Here is a solution that I borrowed and adjusted from dbt's forum:

The code

dbt/macros/udfs/clean_dates.sql:

{% macro create_clean_dates() %}
    CREATE OR REPLACE FUNCTION 
    {{ target.schema }}.clean_dates(d STRING) 
    RETURNS DATE AS (
    SAFE.PARSE_DATE(
    "%Y/%m/%d",
    REGEXP_REPLACE(d, r"\-", "/")
    )
    )
{% endmacro %}

one file per UDF makes it easier for a developer to quickly find the code when they need to make changes

dbt/macros/create_udfs.sql:

{% macro create_udfs() %}

create schema if not exists {{target.schema}};
{{create_clean_dates()}};
...
{% endmacro %}

I use PyCharm's Edit-> Sort Lines to keep UDFs sorted alphabetically
I add the semicolon ; here but you can do that in the macro as well, doesn't matter, as long as you are consistent.

dbt/dbt_project.yml:

...
on-run-start:
  - '{{ create_udfs() }}'

These two lines make sure that the UDFs are created before any dbt models are run/built. The only downside is that it adds some time to your dbt build.

Naming conventions

We have implemented the following naming conventions to make support easier:

For the example in our UDF clean_dates we do the following:

File has the same name as the UDF: clean_dates.sql
The macro is called create_clean_dates as it makes it easier to distinguish the UDF from the macro
All UDFs are created in the same dataset target.schema, which will default to dbt. This makes it easier to

References:
https://discourse.getdbt.com/t/using-dbt-to-manage-user-defined-functions/18

Batch rename BigQuery tables with SQL

Ivan N — Thu, 03 Aug 2023 10:13:58 +0000

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.

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

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

The complete code:

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:

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

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

  regexp_extract(table_id, r"ABC_\d{8}") as new_name

The regular expression only takes the literal string ABC_ and then 8 digits

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 🤷

DEV Community: Ivan N