Exclude columns + custom sql

[dekinf]

Hi everyone,
I’m working on a Sling replication and I’d like to exclude a couple of source columns. I succeeded in doing this using the select parameter (for example [-col_name]) which is great. However, at the same time I need to use the sql parameter (a custom SQL) for the stream. Is it possible to combine select with sql in a replication so I can both run custom SQL and exclude certain columns? If not, is there any recommended best practice for excluding columns when using sql in Sling?
Thanks in advance for any tips!

[flarco]

hey @dekinf could you try adding {fields}?

streams:
  my_schema.my_table:
    select: [col1, col2]
    sql: |
      select {fields} from  {stream_schema}.{stream_table}

[flarco]

hey @dekinf thanks for the details.
This should be fixed in the next release, where it won't wrap as a subquery when providing {fields} and custom SQL.
You can use the dev build right now to try it if you'd like.

[dekinf]

Hi @flarco perfect thanks!

[dekinf]

Hi @flarco , thanks again for the update — it’s great. I just tested everything in the new release.

I did run into one more thing:

When I use a config like this:

select: [-my_excluded_col]
sql: |
  select my_custom_sql_cols,
         {fields}
  from my_table
  where last_update > {incremental_value}

If I don’t also exclude my_custom_sql_cols, Sling generates those columns twice — once from my custom SQL and once from {fields}.

So at the moment I need to do something like:

select: [-my_custom_sql_cols, -my_excluded_col]
sql: |
  select my_custom_sql_cols,
         {fields}
  from my_table
  where last_update > {incremental_value}

Not a big issue, but I wanted to check if this is the expected behavior or if there’s a cleaner way to avoid duplicates.

Thanks again for your work and for all the helpful answers!

[flarco]

@dekinf just seeing this, yea it gets all the column list of the table, and doesn't "parse" the custom SQL to see what's in there.
So, you'll have to just continue what you're doing to exclude again as a work-around.

[dekinf]

@flarco Perfect thanks!