SQLBucket is a lightweight framework to help write, orchestrate and validate SQL data pipelines. It gives the possibility to set variables and introduces some control flow using the fantastic Jinja2 library. It also implements a very simplistic unit and integration test framework where you can validate the results of your ETL in the form of SQL checks. With SQLBucket, you can apply TDD principles when writing data pipelines.
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 sqlbucketSQLBucket works only for Python 3.6 and 3.7, and probably 3.8 although not tested yet.
A Simple Example
To start working, you need to instantiate your SQLBucket core object with the project_folder parameter. That folder will contain all your SQL ETL. The python file where you create your SQLBucket object is also a good place to instantiate your command line interface, as shown below.
# my_sqlbucket.py
from sqlbucket import SQLBucket
bucket = SQLBucket(projects_folder='projects')
if __name__ == '__main__':
bucket.cli()The following command will create your first project in your projects folder.
python my_sqlbucket.py create-project -n my_first_projectFor more info on CLI, please refer to its documentations.
Your projects should now look like the structure below:
projects/
|-- my_first_project/
|-- config.yaml
|-- queries/
|-- query_one.sql
|-- query_two.sql
|-- integrity/
|-- integrity_one.sqlSQLBucket project structure
An SQLBucket project is made of 3 core components: the configuration, the ETL queries and the integrity check queries.
Configuration
The config.yaml is the core of your project. This is where you can define variables at project level, and configure the order your sql queries must be executed. For a better explanations on how to configure variables you can refer to the usage documentation, and also the variables documentation which also describes environment and connections variables.
ETL queries
The queries folder simply contain your SQL queries. You can organize them in the folder structure of your choice. As long as they are in the queries folder, SQLBucket will find them and execute them when configured to do so. See the documentation on how to write SQL with SQLBucket.
Integrity queries
The integrity folder simply contain SQL queries to help you validate your ETL. You can organize them in the folder structure of your choice. The only convention is to return the result of your integrity (True/False) in a field named passed. The main idea is that integrity is done by SQL itself. Check documentation on integrity for a more detailed explanation on testing the integrity of your ETL. We also have a set of common macros that can be helpful to start with.
See below a full example that will actually first run your ETL, and then run your integrity checks for a given database configuration.
from sqlbucket import SQLBucket
connections = {
'db_demo': 'postgresql://user:password@host:5439/database'
}
bucket = SQLBucket(connections=connections)
project = bucket.load_project(
project_name='my_first_project',
connection_name='db_demo',
variables={'foo': 1}
)
# to run ETL
project.run()
# to run integrity
project.run_integrity()We recommend setting your connection urls as environment variables for security purposes.
Template project
To get you up to speed, you can create a fork of the SQLBucket template project and start building SQL data pipelines within minutes.
Contributing
For guidance on how to make a contribution to SQLBucket, see the contributing guidelines.
Links
License: MIT
Releases: https://pypi.org/project/sqlbucket/
Issue tracker: https://github.com/socialpoint-labs/sqlbucket/issues
3 maintainers
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Filter files by name, interpreter, ABI, and platform.
If you're not sure about the file name format, learn more about wheel file names.
Copy a direct link to the current filters
File details
Details for the file sqlbucket-0.4.1.tar.gz.
File metadata
- Download URL: sqlbucket-0.4.1.tar.gz
- Upload date:
- Size: 14.8 kB
- Tags: Source
- Uploaded using Trusted Publishing? No
- Uploaded via:
twine/3.2.0 pkginfo/1.5.0.1 requests/2.24.0 setuptools/47.3.1 requests-toolbelt/0.9.1 tqdm/4.46.1 CPython/3.8.5
File hashes
| Algorithm | Hash digest | |
|---|---|---|
| SHA256 |
47a75825ca4f41b5e0cf600b3c8b64efd2b36af09595d90d2d37aba95e224cb0
|
|
| MD5 |
0b369023ab96659d0e48fb73f328b533
|
|
| BLAKE2b-256 |
5296a3c66a1d0c6c22ccba3067a8c32c2368693590a298a18231c4f72f3ed469
|
File details
Details for the file sqlbucket-0.4.1-py3-none-any.whl.
File metadata
- Download URL: sqlbucket-0.4.1-py3-none-any.whl
- Upload date:
- Size: 15.5 kB
- Tags: Python 3
- Uploaded using Trusted Publishing? No
- Uploaded via:
twine/3.2.0 pkginfo/1.5.0.1 requests/2.24.0 setuptools/47.3.1 requests-toolbelt/0.9.1 tqdm/4.46.1 CPython/3.8.5
File hashes
| Algorithm | Hash digest | |
|---|---|---|
| SHA256 |
7e0d0589afc46043a32426f8d4b4f78b856672152b34fd38960c7041fcba5088
|
|
| MD5 |
3bda0b5a1e7b1098fcf0a9d76db56fd4
|
|
| BLAKE2b-256 |
61c67fd542b1bb1ee081b2a4a32e7c98c16ed92b1969bc4e800759ee1b8e15c1
|
