Is there a way to filter out all columns that start with say a particular alphabet? Is there a way to use an expression in the filter component? OR is there a way to use a Grid Variable for Field Selection? Any help please?

Matillion ETL
1

Is there a way to filter out all columns that start with say a particular alphabet? Is there a way to use an expression in the filter component? OR is there a way to use a Grid Variable for Field Selection? Any help please?

2

More details :

I'm connecting to a SQL server db via Matillion and trying to load a table into Snowflake. The table has about 70 to 80 columns. about 50 of these are that start with alphabets 'V' and 'C' . I want to be able to filter out and not load anything that starts with a C. What is the ideal way to do this? ( example of column names - V12345, C12345678) and so on

3

I haven't done it before, but glancing at what is available, I'd probably try this: "Table Metadata to Grid" to get column names, followed by a "Grid Iterator" to iterate over those column names. Within each iteration: for each column name, if it starts with a 'C', then do something with it; else ignore it.

4

Thanks for your response Kevin. I'll check this out and let you know.

5

I do something like below , to create the insert statement for any table dynamically :

SELECT 

listagg(A.COLUMN_NAME, ', ') AS C, 

  COUNT(A.COLUMN_NAME)     AS C_CNT,

  listagg(B.COLUMN_NAME, ', ') AS V ,

  COUNT(B.COLUMN_NAME)     AS V_CNT

FROM DATALAKE.INFORMATION_SCHEMA.COLUMNS A, DATALAKE.INFORMATION_SCHEMA.COLUMNS B  

WHERE A.COLUMN_NAME = B.COLUMN_NAME AND A.COLUMN_NAME NOT IN ('LAKE_LOAD_ID','DATA_DT') AND 

A.TABLE_NAME = TRIM('${L_LAKE_TABLE_NAME}') AND B.TABLE_NAME = TRIM('${L_LAND_TABLE_NAME}' )

ORDER BY A.ORDINAL_POSITION

 

I dont know the metadata table for SQL Server. But, this concept can be extended for filtering out the columns too as ,

A.COLUMN_NAME NOT LIKE ('V%') AND A.COLUMN_NAME NOT LIKE ('C%')