I’d like the action be something like 1) search for all decimal types of the datasource (output: dynamic list of columns) 2) for each column add a cast decimal to double transform. Is that possible?
Yup this is a first-class feature in Pipeline Builder, see below screenshots for walkthrough:
-
In transform library, click on the
All columns...button near the top-right corner
-
Click on
Select typesnear top-right corner and choose + confirm the data type you care about
-
Search for the transform you want (i.e., Cast); notice that there’s now greyed-out text next to it that reads “Apply to each column” because you’ve selected multiple columns to apply the transformation to
You’ll end up with a configuration that looks like the below:
It’s essentially a for-loop (I assume optimized when transpiled to JavaSpark when you deploy your pipeline) that will apply your desired transformation to your selected columns as well (notice the light green tag labelled
column).
2 Likes
Thanks Josh, this seems to work pretty well! Do you know if it will automatically capture and cast if a new column is added to source? Or would I have to come in and edit the transform to include the new column?
My hunch is that it doesn’t do automatic capture so if your input table has a schema update and gets new DecimalType() columns, those aren’t automatically cast by your existing transform. I think this is deliberate from the product team as they don’t want to make assumptions about the logic you want to implement for every pipeline!
Hey Josh-- Turns out there is a way to get a dynamic list, using the Apply to Multiple Columns transform: pick the “column has type” from the dropdown and selecting the data type you’d like. This matches all the columns for that data type, then you can select the cast action to the new data type further in the transform configuration. Thanks for you help, I wouldn’t have discovered the Apply to Multiple Columns option without your assistance!
1 Like