search

dbt-labs / dbt-external-tables

dbt macros to stage external sources
https://hub.getdbt.com/dbt-labs/dbt_external_tables/latest/
Apache License 2.0
288 stars 116 forks source link

Ability to add column alias #213

Closed moseleyi closed 3 months ago

moseleyi commented 1 year ago

Describe the feature

When defining a column for an external table, I would like to add metadata columns like metadata$filename, which are not correct column names.

Describe alternatives you've considered

I don't think there are any?

Additional context

I'm using Snowflake and stage tables have useful metadata that I would like to be in the external tables. At the moment I'm getting the error:

  'METADATA$FILE_LAST_MODIFIED' cannot be used as a column name for external tables.

I think it would be great to be able to add alias to column definition:

- name: metadata$file_content_key
  alias: file_content_key
  description: "Unique key for each file"
  data_type: varchar
natej-eb commented 10 months ago

Another use case I've run into is when the column name in the source file happens to be value, which Snowflake doesn't allow because of their external table value column. I'd like to alias this column name specifically.

{
 "key": {...},
 "value": {...},
 "meta": {...}
}
cakkinep commented 4 months ago

@natej-eb take a look at the enhancements i am proposing on this PR and let me know if this solves your needs.

cakkinep commented 4 months ago

Describe the feature

When defining a column for an external table, I would like to add metadata columns like metadata$filename, which are not correct column names.

Describe alternatives you've considered

I don't think there are any?

Additional context

I'm using Snowflake and stage tables have useful metadata that I would like to be in the external tables. At the moment I'm getting the error:

  'METADATA$FILE_LAST_MODIFIED' cannot be used as a column name for external tables.

I think it would be great to be able to add alias to column definition:

- name: metadata$file_content_key
  alias: file_content_key
  description: "Unique key for each file"
  data_type: varchar

Can you explain more about why you want to have the filename as a named column in the table when you can use the metadata$filename in a table? I am trying to understand the usecase better.