BigQuery ETL

This repository contains Mozilla Data Team's:

• Derived ETL jobs that do not require a custom container
• User-defined functions (UDFs)
• Airflow DAGs for scheduled bigquery-etl queries
• Tools for query & UDF deployment, management and scheduling

For more information, see https://mozilla.github.io/bigquery-etl/

Quick Start

Prerequisites

• Pyenv (optional) Recommended if you want to install different versions of Python, see instructions here. After the installation of pyenv, make sure that your terminal app is configured to run the shell as a login shell.
• Homebrew (not required, but useful for Mac) - Follow the instructions here to install Homebrew on your Mac.
• Python 3.11 - (see this guide for instructions if you're on a Mac and haven't installed anything other than the default system Python).

GCP CLI tools

• For Mozilla Employees (not in Data Engineering) - Set up GCP command line tools, as described on docs.telemetry.mozilla.org. Note that some functionality (e.g. writing UDFs or backfilling queries) may not be allowed. Run gcloud auth login --update-adc to authenticate against GCP.
• For Data Engineering - In addition to setting up the command line tools, you will want to log in to shared-prod if making changes to production systems. Run gcloud auth login --update-adc --project=moz-fx-data-shared-prod (if you have not run it previously).

Installing bqetl

1. Clone the repository

git clone [email protected]:mozilla/bigquery-etl.git
cd bigquery-etl

Work in a branch of this repository, not a fork. All of Mozilla has write access to mozilla/bigquery-etl, so clone it directly (as above), push your branch here, and open the pull request from there. CI does not run on pull requests from forks. See CONTRIBUTING.md.

2. Install the bqetl command line tool

./bqetl bootstrap

3. Install standard pre-commit hooks

venv/bin/pre-commit install

Finally, if you are using Visual Studio Code, you may also wish to use our recommended defaults:

cp .vscode/settings.json.default .vscode/settings.json
cp .vscode/launch.json.default .vscode/launch.json

And you should now be set up to start working in the repo. The easiest way to do this for many tasks is to use bqetl. You may also want to read up on common workflows.

Upgrading Dependencies in venv from requirements.txt

Sometimes a new dependency is added to bigquery-etl, or there is an important update made that should be installed locally for bqetl commands to continue working. bootstrap will only setup from scratch, so use update instead to update dependencies in-place.

0. Prerequisites

• already followed the steps above for Installing bqetl
• requirements.txt has the up-to-date dependencies list to install

1. Update dependencies

./bqetl update

Releasing a new version of bqetl

To push a new version of bqetl to PyPI, update the version in pyproject.toml. The version numbers follow the CalVer scheme, with the Micro version numbers starting at 1. For example, for the first package version getting published in March 2024, the version would be 2024.3.1.