Support external tables about sqlalchemy-redshift HOT 10 OPEN

Redshift seems to have a view that includes both internal and external tables: SVV_COLUMNS

perhaps this can be used instead?

from sqlalchemy-redshift.

wow

from sqlalchemy-redshift.

@c-nichols urgh that's probably going to need some changes to https://github.com/sqlalchemy-redshift/bigcrunch

Eg add the appropriate IAM roles to the redshift cluster.

from sqlalchemy-redshift.

Adding some notes based on digging around...

Both views and tables are normally defined in pg_table_def (a catalog table) and this is what is currently used in _get_all_column_info.

External table information isn't in pg catalog tables. Views that include external tables aren't, either, because they are required to use WITH NO SCHEMA BINDING. In fact, describing these views in the shell (with /d) doesn't even show anything useful.

Metadata about tables can be found in svv_external_tables:

# \d svv_external_tables       
  View "pg_catalog.svv_external_tables"  
      Column       |  Type   | Modifiers 
-------------------+---------+-----------
 schemaname        | text    |           
 tablename         | text    |           
 location          | text    |           
 input_format      | text    |           
 output_format     | text    |           
 serialization_lib | text    |           
 serde_parameters  | text    |           
 compressed        | integer |           
 parameters        | text    |           

Metadata for views that include tables can be found in pg_get_late_binding_view_cols(). One example for querying this from the docs:

select * from pg_get_late_binding_view_cols() cols(view_schema name, view_name name, col_name name, col_type varchar, col_num int);

I'm still digging into this but what I need, ideally, is to be able to use Introspector.get_columns to return column meta data from an external table.

from sqlalchemy-redshift.

Any update on this issue/feature?

from sqlalchemy-redshift.

The maintainers of this project aren't actively working on any new features. A PR for this would be welcome and I'd be happy to review, but neither I nor @graingert have time to devote to adding this support ourselves.

from sqlalchemy-redshift.

Support for late binding views was added in #159, hooray!

However, support for external tables looks a bit more difficult. At first I thought we could UNION in information from svv_external_columns much like @e01n0 did for late binding views from pg_get_late_binding_view_cols, but it looks like the internal representation of the data is slightly different.

For example, in pg_get_late_binding_view_cols an integer is represented as integer but in svv_external_columns it's shown as an int. Likewise, external character columns are indicated as varchar(36) instead of character varying(36). I don't know how well that will work with type inference.

from sqlalchemy-redshift.

For example, in pg_get_late_binding_view_cols an integer is represented as integer but in svv_external_columns it's shown as an int. Likewise, external character columns are indicated as varchar(36) instead of character varying(36). I don't know how well that will work with type inference.

As far as I'm aware, int and integer are just aliases for one another and same for varchar and character varying, so this may not be a problem? I'd encourage you to give UNIONing a try and see what happens.

from sqlalchemy-redshift.

Any updates on this feature? Thanks

from sqlalchemy-redshift.

partially supported in #263

from sqlalchemy-redshift.