Redscope

A schema introspection tool for aws Redshift

Redscope is used to read the raw SQL DDL from your redshift database and save the results to .sql files in an easy-to-use project tree.

Why use Redscope?

Keeping track of the database schema is annoying. If DDL files are kept under version control, and a database migration tool is used as part of your CI, there is a chance that the table definition can be out of date as it changes over time. This tool allows the current state of the database schema to be read from redshift, and put under version control so changes can be tracked over time.

getting started

pip install redscope

Create a redscope project

redscope init

populate the redshift/redscope/.redscope file

This file is used to tell redscope how to connect to redshift. In order to easily support using existing .env files in your projects, it is necessary to tell redscope the name of your environment file, as well as the name of the environment variable which contains a standard psycopg2 connection string.

[env]
file = name_of_env_file.env

[redshift]
connection = ENV_VARIABLE_WITH_PSYCOPG2_CONNECTION_STRING

Introspect the database schema

rescope inspect

Redscope API

STILL UNDER DEVELOPMENT

For whatever reason, sometimes it is nice to be able to reference SQL ddl directly from your python code. This can be accomplished using the redscope api.

from pathlib import Path
from dotenv import load_dotenv
from redscope.api import RedshiftSchema

load_dotenv('my-env-file.env')

redshift_schema = RedshiftSchema('MY_DB_CONNECTION_NAME')

# sales_report_table now is a Table DDL object
sales_report_table = redshift_schema.schema('sales').table('report').fetch()

# will print full table ddl including constraints, encoding, and defaults
print(sales_report_table.ddl())

# will print simple ddl with just columns and data types
print(sales_report_table.simple_ddl())


# Accessing a function definition
func_foo = redshift_schema.schema('my_funcs').function('foo').fetch()

# func foo is a Function DDL object
print(func_foo.ddl())



# getting all objects in a schema
reporting_views = redshift_schema.schema('reporting').views().fetch()

for name, ddl in reporting_views.items():
    print(f"the key is the schema qualified view name. {name}")
    print(f"the value is the SQL ddl string.{ddl}")


# Files can also be saved
root_path = Path.cwd()

tables = redshift_schema.schema('sales').tables().fetch()

"""
root_path
    schema
        sales
            tables
                schema.table.sql -- one file per table
"""
for table in tables:
    table.save_file(root_path)

Release files for redscope 0.5.5

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

Source distribution (sdist)

Source distribution for redscope 0.5.5
File	Size	Uploaded
redscope-0.5.5.tar.gz	10.4 kB	Feb 9, 2021	Details

Built distribution (wheel)

Table of built distributions (wheels) for redscope 0.5.5
File	Interpreter	ABI	Platform	Reset
redscope-0.5.5-py3-none-any.whl 18.7 kB Feb 9, 2021	Python 3	none	any	Details

Total release size: 29.2 kB

Release files / redscope-0.5.5.tar.gz

Download URL	redscope-0.5.5.tar.gz
Size	10.4 kB
Tags	Source
SHA-256 checksum How to use checksums	`df69ebc2b63d5a5a802ec2c7c12ae07f6bdb293b5c6e743233eaf5bdbb90c1a5`
BLAKE2b-256 checksum How to use checksums	`e1125c59db97369f378d01e7cd312cc959cb45d3f7abb1ae5aaa05ea1753e18c`
Upload date	Feb 9, 2021
Uploaded using Trusted Publishing? What is trusted publishing?	No
Uploaded via	`twine/3.3.0 pkginfo/1.6.1 requests/2.25.1 setuptools/53.0.0 requests-toolbelt/0.9.1 tqdm/4.55.1 CPython/3.6.12`

Release files / redscope-0.5.5-py3-none-any.whl

Download URL	redscope-0.5.5-py3-none-any.whl
Size	18.7 kB
Tags	Python 3
SHA-256 checksum How to use checksums	`7034fd85ca330a2b6b41a4556b8979906e4902bd6478a7a216b2c7b1077cf198`
BLAKE2b-256 checksum How to use checksums	`3eb5b7597f3c9615470cccc031eb508ead8550bde5649154b213622bb6e98687`
Upload date	Feb 9, 2021
Uploaded using Trusted Publishing? What is trusted publishing?	No
Uploaded via	`twine/3.3.0 pkginfo/1.6.1 requests/2.25.1 setuptools/53.0.0 requests-toolbelt/0.9.1 tqdm/4.55.1 CPython/3.6.12`