Open jeremyyeo opened 5 months ago
Looks like svv_redshift_columns
(https://docs.aws.amazon.com/redshift/latest/dg/cross-database-overview.html) might be what we want to query here
We are running into the same issue. We are trying to create the dbt output in an other database than the source data is located.
adapter.get_columns_in_relation
is not returning any columns for tables located in a different database.
I tracked it down to this line where information_schema."columns"
is used. This table only returns information about the current database and therefore returns an empty list of columns for tables located in another database.
Maybe something like this could replace information_schema."coloumns"
:
SELECT ordinal_position,
table_name,
column_name,
data_type,
character_maximum_length,
numeric_precision,
numeric_scale
FROM SVV_ALL_COLUMNS WHERE database_name = '<database>' AND schema_name = '<schema>' AND table_name = '<table>'
We are running into the same issue
@VolkerSchiewe are you also using RA3 nodes?
We are running into the same issue
@VolkerSchiewe are you also using RA3 nodes?
yes
@VolkerSchiewe @jeremyyeo I just opened #738, do you want to it's version of redshift__get_columns_in_relation()
? I'm also going to ask the Redshift team for advice
This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.
@dataders any update on that topic? Have you talked to the redshift team?
hey @VolkerSchiewe yeah we've been in discussion with them. Have you looked over #742? The consensus between their team and ours right now is that rather than solve one-off issues like this one, we instead holistically address cross-database functionality.
Are you on a team currently that would benefit from this feature? If so I can log this internally so we can track interest over time.
Hey @dataders , my team and I are also facing this same exact issue. We were planning to data shares to get our production tables into a lower environment, but it comes in as a separate database.
Whenever I look at dbt logs, I see that it queries information_schema."columns"
which doesn't return those data-shared (separate database) tables.
However, when I query svv_redshift_columns
, we see those production tables (datashared) in this table.
This would help us tremendously and is a huge blocker for us to get our lower environment setup.
@bteh the solution for Redshift that I came up with in the meantime is to create a macro like this:
{% macro get_redshift_columns(database, schema, table) %}
{% set columns = [] %}
{% if execute %}
{% set columns = run_query("SHOW COLUMNS FROM TABLE "~database~"."~schema~"."~table) %}
{% endif %}
{% do return(columns) %}
{% endmacro %}
and then call it in your model.
It seems that this issue also affects codegen's generate_source command (https://github.com/dbt-labs/dbt-codegen/blob/0.12.1/macros/generate_source.sql)
Is this a new bug in dbt-redshift?
Current Behavior
Not too sure if this calls for it's own issue but pretty much an extension of #179 -
adapter.get_columns_in_relation
does not support introspecting a table that is in a different database.Expected Behavior
Just like #179 - we need to make
adapter.get_columns_in_relation
work across databases.Steps To Reproduce
dev
andsources_only
) add 2 tables.-- using a connection to database dev create table dev.public.source_bar as select 1 id;
get_columns_in_relation
:^ We didn't manage to retrieve column
id
for the source that is in the other database (sources_only
).The reason for that is straightforward - if we look at the
get_columns_in_relation
implementation and try and run that query straight up in Redshift:Relevant log output
No response
Environment
Additional Context
This results in https://github.com/dbt-labs/dbt-codegen/issues/167 - which further materializes itself in dbt Cloud IDE as users try and use the "generate model" function and find that it doesn't work as expected: