jakob / jakob/Postico

Add support for Redshift Spectrum External Tables

Open
#382 7 comments 1 reaction 0 assignees View on GitHub
Dominant language
Python
Stars
485
Forks
8
PR merge metrics
No merged PRs in 30d

Description

Redshift recently launched [Spectrum](http://docs.aws.amazon.com/redshift/latest/dg/c-using-spectrum.html) which allows querying data stored in S3 (similar to Athena, but different implementation.

When you create your external schema, it properly shows up in the sidebar, however it does not populate any table info, making it tricky to query the table properly. It would be nice if Postico could list the tables and their schemas.

To list tables:
`select * from svv_external_tables`

This returns the following columns:
```
schemaname
tablename
location
input_format
output_format
serialization_lib
serde_parameters
compressed
parameters
```

To list table columns (and tables incidentally):
`select * from svv_external_columns`

This returns the following columns:
```
schemaname
tablename
columnname
external_type
columnnum
part_key
```

Probably simplest to just use `svv_external_columns` to get the data. It would be nice if the schema showed with a different color on the sidebar to indicate that it was external as well.

Contributor guide

No contributing guide indexed for this repository

Research direction

Start by tracing how external schemas are loaded into the sidebar, then review the metadata queries using svv_external_tables and svv_external_columns. Done means external schemas display their tables and columns, with external schemas visually distinguished in the sidebar.

Written by the indexing model from the issue text.

Assessment

Tech stack
aws, sql
Domain
database
Issue type
Feature
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.