Skip to main content

View management for Amazon's Redshift

Project description

Room with a View

https://img.shields.io/pypi/v/room_with_a_view.svg https://img.shields.io/travis/b12io/room_with_a_view.svg Documentation Status Updates

View management for Amazon’s Redshift

Features

Room with a view is a python script that automatically parses a collection of SQL files to find view and function definitions and their dependencies. It then makes it easy to sync those views with Redshift, automatically dropping and recreating dependent views as necessary so that there are no errors. The key benefits are:

  • No writing code to send view SQL to Redshift: the script does it for you.

  • Edit any view and sync it without worrying about needing to drop or recreate views that depend on it.

  • Error handling tells you exactly where there are errors in your views.

  • The script runs in an atomic transaction, so you can’t accidentally enter a broken state by syncing some views and not others.

Setup

  • Install the package: pip install room_with_a_view.

  • Create settings.yaml, and edit the file to configure your Redshift connection and the location of your .sql files. Example settings.yaml file:

connections:
  default:
    host: localhost
    port: 5432
    user: awsuser
    password: **CHANGEME**
    dbname: postgres

directories:
  - .
  • You’re ready to go! Try room_with_a_view sync-all to sync all your views, or room_with_a_view --help to learn more about the command.

Usage

usage: room_with_a_view.py [-h]
                           [--view-names [VIEW-OR-FUNCTION-NAME [VIEW-OR-FUNCTION-NAME ...]]]
                           [--file-names [FILE-PATH [FILE-PATH ...]]]
                           [--connection CONNECTION]
                           [--settings SETTINGS] [--verbosity VERBOSITY]
                           {sync,drop-all,sync-all,list,drop}

Manages Redshift SQL views. Possible actions:
    sync: Syncs specific views or functions (identified by the --view-names or --file-names parameters).
    drop-all: Drops all views and functions in all .sql files in a set of directories (identified by the --directories parameter). The directory will be searched recursively.
    sync-all: Syncs all views and functions in all .sql files in a set of directories (identified by the --directories parameter). The directory will be searched recursively.
    list: lists all known views and functions.
    drop: Drops specific views or functions (identified by the --view-names or --file-names parameters).

positional arguments:
  {sync,drop-all,sync-all,list,drop}
                        The action to perform.

optional arguments:
  -h, --help            show this help message and exit
  --view-names [VIEW-OR-FUNCTION-NAME [VIEW-OR-FUNCTION-NAME ...]]
                        Names of views or functions to which to apply the action.
  --file-names [FILE-PATH [FILE-PATH ...]]
                        Paths to .sql files to which to apply the action.
  --connection CONNECTION
                        Name of the Redshift connection to use (or "default",
                        if not specified). The name must match a connection in
                        settings.yaml
  --settings SETTINGS   Location of the settings file (settings.yaml by
                        default)
  --verbosity VERBOSITY
                        Verbosity of script output. 0 will output nothing, 1
                        will output names of views and functions being dropped
                        and created, and 2 will output all executed sql

Examples

  • room_with_a_view.py sync-all: Syncs all views and functions in all SQL files in the default directory specified in settings.yaml. Drops and recreates existing views, and makes sure views are created in dependency order.

  • room_with_a_view.py sync --view-names my_view1 my_func1 --file-names ../sql/my_file.sql: Syncs the specific view my_view1 and function my_func1, as well as all views and functions in the file ../sql/my_file.sql.

  • room_with_a_view.py drop-all --connection other_connection: Drops all views and functions in the default directory, using the connection info specified in settings.yaml under the name other_connection to connect to Redshift.

  • room_with_a_view.py drop --view-names my_view1 --directories other_dir1 other_dir2 --settings /path/to/fancy_settings.yaml: Drops the view my_view1, looking for SQL files that contain the view and its dependents in the directories specified by other_dir1 and other_dir2 in the settings file located in /path/to/fancy_settings.yaml.

Credits

This package was created with Cookiecutter and the audreyr/cookiecutter-pypackage project template.

History

0.1.0 (2018-05-28)

  • First release on PyPI.

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

room_with_a_view-0.1.5.tar.gz (16.6 kB view details)

Uploaded Source

File details

Details for the file room_with_a_view-0.1.5.tar.gz.

File metadata

  • Download URL: room_with_a_view-0.1.5.tar.gz
  • Upload date:
  • Size: 16.6 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: Python-urllib/3.8

File hashes

Hashes for room_with_a_view-0.1.5.tar.gz
Algorithm Hash digest
SHA256 3b593bb633652b9e5da3b24461d5dd2ca2bd62a7b9e0d25486b8736e3826ebbc
MD5 4aa9a7052abbaa573a15b9c7010b52ee
BLAKE2b-256 3aaebec9a05f5dd7f1b34f266b89db0aba81a94bcf7e7bbf46a9f509a9c245bd

See more details on using hashes here.

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page