Dynamically update Workflow based on Input Schema

YULteryx

Hi everyone!

We're currently looking at hundreds of files and trying to "figure out" what column is most probably the Primary Key.

I have created a simple workflow which will read/write .CSV files from/to HDFS. Step by step, I am:

1. Summarizing all the columns from the input to count unique values ("Count distinct")
2. Transposing to obtain 2 columns (column_name + count_Distinct)
3. Appending the total number of rows for each and calculating the % of unique values
4. Sorting by % descending, selecting relevant columns and outputting the result.

This works well for a single file, but it would be amazing if we could automate the process for our +- 300 files tables (with different schema/size):

1. For the input, I'm thinking of a batch macro for all files and Auto configure by name to adapt for the schemas
2. For the output, I'm thinking about taking the name of the .CSV from a field (1 output per table name)
3. My issue is dynamically updating the "summarize" tool, which is crucial for the workflow. The tool would need to "Select All columns" and "Count Discount" for all, no matter how many columns are within the table. I thought about modifying the inner XML with an action tool, but I'm unsure if specifying "select all + count discount" is do-able.

Thanks in advance!

Find_Primary_Key.png

LukeM

Hi @YULteryx ,

Instead of using the Summarize tool to Count Distinct, could you:

1. Transpose all your data to just Name and Value fields
2. Use a Unique tool to get just distinct Name and Value pairs
3. Add a new field which is just a 1 for every row
4. Use the Summarize tool to 'Group By' Name and 'Sum' the new 1 field, creating a count.

This should replicate the Count Distinct but in a more dynamic way for your Batch Macro.

Hope this helps.

Luke