How do I exclude certain columns within a transformation?

Question

When using Upsolver SQLake, if my source table has 100's of columns, and I want to include most of them in a transformation, but exclude a few, can I do that without having to explicitly map every column in the transformation SQL?

For example, if my source table has 5 columns, (col1, col2, col3, col4, col5), and in my transformation I do not want to include col3. I could use the following SQL:

SELECT col1, col2, col4, col5 FROM sourcetable

However, if my source table has 1000 columns, I'd rather not have to type out 999 columns if I don't have to.

I was looking for an option to generate SQL, or some option to exclude certain columns from a transformation.

Answer 1

SQLake supports an EXCEPT parameter in the transformation job definition. The transformation SQL will be evaluated, however columns in the EXCEPT reference will be excluded in the target table.

CREATE JOB insert_all_columns_except_col3
    START_FROM = NOW
    ADD_MISSING_COLUMNS = TRUE
    RUN_INTERVAL = 1 MINUTE
AS INSERT INTO target_table MAP_COLUMNS_BY_NAME EXCEPT col3
SELECT *
FROM source_table
WHERE $commit_time BETWEEN RUN_START_TIME() and RUN_END_TIME();

In this case, all columns from "source_table" will be written into "target_table" except for col3.