sqlbucket

SQLBucket was built to help write, orchestrate and validate SQL ETL. It gives the possibility to set variables and introduces some control flow like if/else and for loops, to make your queries dynamic when writing them. It also implements a very simplistic integration testing framework to validate the results of your ETL pipelines in the form of SQL checks.

Lightweight, it can work as a stand alone service, or be part of your workflow manager environment (Airflow, Luigi, ..).

Installing

Install and update using pip:

pip install -U sqlbucket

SQLBucket works only for Python 3.6 and 3.7, and probably 3.8 although not tested yet.

A Simple Example

To start working with SQLBucket, you need to have a ‘projects’ folder that will contain all your SQL ETL. SQLBucket, essentially works with the following folder structure as a root folder, where you can have as many projects as you want.

projects/
    |-- project1/
    |-- project2/
    |-- project3/
        ...

Inside a project, it must contain a folder called queries that will contains your SQL files to be ran, and a config.yaml that will let you set the order in which those queries must be processed. SQLBucket leverages on the amazing Jinja2 templating library to give you the possibility to set variables in your SQL as well as giving you pure execution flows like for loops. To see in more depth what can be done, see the documentation on how to write SQL in SQLBucket.

In practice, this is how a project folder would look.

projects/
    |-- my_super_project/
        |-- config.yaml
        |-- queries/
            |-- my_super_insert_query.sql
            |-- some_other_query.sql
        |-- integrity/
            |-- test1.sql
            |-- test2.sql

The integrity folder gives you the possibility to write some checks in SQL, that will ran at the end of your ETL to validate your data. Check documentation on integrity for a more detailed explanation on testing the integrity of your ETL.

This is how you would launch a project:

from sqlbucket import SQLBucket

connections = {
'db_test': 'postgresql://user:password@host:5439/database'
}

bucket = SQLBucket(connections=connections)
project = bucket.load_project(
    project_name='fat_etl',
    connection_name='db_test',
    variables={'foo': 1}
)

project.run()
project.run_integrity()

This would trigger logs as below.

You can also launch a project using the sqlbucket command line interface.

Contributing

For guidance on how to make a contribution to SQLBucket, see the contributing guidelines.

Metadata

Release files for sqlbucket 0.3.2

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for sqlbucket 0.3.2
File	Size	Uploaded
sqlbucket-0.3.2.tar.gz	12.2 kB	Feb 26, 2020	Details

Built distribution (wheel)

Table of built distributions (wheels) for sqlbucket 0.3.2
File	Interpreter	ABI	Platform	Reset
sqlbucket-0.3.2-py3-none-any.whl 13.8 kB Feb 26, 2020	Python 3	none	any	Details

Total release size: 26.0 kB

Release files / sqlbucket-0.3.2.tar.gz

Download URL	sqlbucket-0.3.2.tar.gz
Size	12.2 kB
Tags	Source
SHA-256 checksum How to use checksums	`8be70b13b751fd78ee75d33d4ed5dd1610b5f2153727d6cf61179e66e49c78ca`
BLAKE2b-256 checksum How to use checksums	`7388dfe3daa5184657bfe163c50b7fa3f41a9172d3a083d2b18067c312eb0ca6`
Upload date	Feb 26, 2020
Uploaded using Trusted Publishing? What is trusted publishing?	No
Uploaded via	`twine/3.1.1 pkginfo/1.5.0.1 requests/2.22.0 setuptools/42.0.2 requests-toolbelt/0.9.1 tqdm/4.40.0 CPython/3.7.4`

Release files / sqlbucket-0.3.2-py3-none-any.whl

Download URL	sqlbucket-0.3.2-py3-none-any.whl
Size	13.8 kB
Tags	Python 3
SHA-256 checksum How to use checksums	`671fe04bae4e82f03035e991a4dfa2924417ef848df949ece3b19ad04c34da05`
BLAKE2b-256 checksum How to use checksums	`6f93875873b7cbf2038cf6dead27a5077f05afe88934dbe0d3198967d2614782`
Upload date	Feb 26, 2020
Uploaded using Trusted Publishing? What is trusted publishing?	No
Uploaded via	`twine/3.1.1 pkginfo/1.5.0.1 requests/2.22.0 setuptools/42.0.2 requests-toolbelt/0.9.1 tqdm/4.40.0 CPython/3.7.4`