Some details

[marcua]

Problems

• Because we hard-code view names, we can't have multiple environments (e.g., dev/production)
• Because there's no notion of view dependencies, when we change a view's schema, we have to manually drop views and recreate the dependency chain.
• Our view materializer similarly runs into these issues.

Solution: Room with a View, an open source package for managing (Redshift) views

• Any view/function/anything you can write in SQL can be preceded with a SQL comment of the form --export thing_name.
• If you want to use an exported thing in another file, you can say --from path/to/some/file.sql import exported_name

Example of how the package can get used

• room_with_a_view.py --environment staging --connection conection1 sync viewname -> creates the necessary views and functions that viewname depends on (including any other view that would have been dropped due to the cascading drop of the view to support schema changes)
• Alternative sync examples: room_with_a_view.py --environment staging --connection conection1 sync_all or room_with_a_view.py --environment staging --connection conection1 sync_file filename.sql
• room_with_a_view.py --environment staging --connection conection1 [drop viewname|drop_all|drop_file filename.sql]
• room_with_a_view.py --environment staging --connection conection1 materialize viewname would create viewname
• room_with_a_view.py --environment staging --connection conection1 list would list all known exported symbols and the comment around them

Other things

• We might have to say export function or export view so we know how to drop it (DROP FUNCTION vs DROP VIEW)

Example settings.yaml file

connections:
  connection1:
    hostname: hostname
    port: 5432
environments:
  production:
    postfix: ""
  staging:
    postfix: _staging

[thisisdhaas]

I took at stab at this in #2. It uses regexes to automatically parse CREATE VIEW and CREATE FUNCTION statements and automatically builds the dependency graph by looking for other view and function names in the body of the statements.

It doesn't support namespacing or materialization, both of which are nontrivial.