LEROI SQL runner
Project description
LEROI SQL runner
The LEROI SQL runner has three basic functionalities
- executing SQL code in a specific order
sqlrunner --execute {RUNNER_FILE_1}, {RUNNER_FILE_2} ..
- quickly testing SQL code through temporary creation of views
sqlrunner --test {RUNNER_FILE_1}, {RUNNER_FILE_2} ..
- plotting of a dependency graph
sqlrunner --deps
The supported databases are Redshift, Snowflake and Postgres.
Installation
Additionally for Azure DWH, it's required to install the Microsoft ODBC Driver. For Ubuntu 18.04 this is sufficient:
# In case any of these gest stuck, simply run `sudo su` once, to cache the password, then exit using Ctrl+D
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list > /dev/null
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql17
sudo apt-get install unixodbc-dev
Install as pip package
sudo apt install python3-pip
sudo apt install graphviz
# Install with dependencies, ex. s3 and azuredwh
pip install git+https://github.com/leroi-marketing/sql-runner.git#egg=sql-runner[s3,azuredwh]
Configuration
Two configuration files are needed to use the sqlrunner.
- A config.json file that specifies all the necessary configuration variables.
{
"sql_path": "{PATH}",
"database_type": "{SNOWFLAKE} OR {REDSHIFT} OR {POSTGRES}",
"auth": {
"user": "{USERNAME}",
"password": "{PASSWORD}",
"account": "{SNOWFLAKE_ACCOUNT}",
"database": "{SNOWFLAKE_DATABASE}",
"dbname": "{POSTGRES_DATABASE} OR {REDSHIFT_DATABASE}",
"host": "{POSTGRES_HOSTNAME} OR {REDSHIFT_HOSTNAME}",
"port": "{POSTGRES_PORT} OR {REDSHIFT_PORT}"
},
"deps_schema": "{DEPENDENCY_SCHEMA_NAME}",
"test_schema_prefix" : "{PREFIX_}",
"exclude_dependencies": "('EXCLUDED_SCHEMA_1', 'EXCLUDED_SCHEMA_2' ..)",
"graphviz_path": "{GRAPHVIZ_PATH_FOR_WINDOWS}"
}
- One or more csv files specifying the name of the the tables and views and their respective schemas.
{SCHEMA_1};{SQL_FILENAME_1};e
{SCHEMA_1};{SQL_FILENAME_2};e
{SCHEMA_1};{SQL_FILENAME_3};e
{SCHEMA_2};{SQL_FILENAME_4};e
{SCHEMA_3};{SQL_FILENAME_5};e
..
Per schema one directory is expected. The name of the SQL files should correspond to thename of the respective table or view. The last columns specifies the desired action.
e: execute the query
t: create table
v: create view
m: materialize view
Development
To set up dependencies locally for development:
# Install virtualenv (if your default python is python2, specify also `-p python3`)
virtualenv venv
source venv/bin/activate
pip install -e .[s3,azuredwh] # and other optional dependencies
# Run local (non-build) version:
python debug.py [arg1 arg2 ...]
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distributions
No source distribution files available for this release.See tutorial on generating distribution archives.
Built Distribution
sql_runner-0.2.0-py3.7.egg
(31.9 kB
view hashes)