Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
3,097 questions
Column functions not supported in configurations - MDF Expression Error
56220211
96
Reputation points
I am building a pipeline with a Mapping Data Flow activity in synapse analytics. I am trying to create a derived column with an expression similar to below. However, it fails with the error "Column functions not supported in configurations".
array(toString(byNames(columnNames())))
I need to dynamically get the list of column names and get the values on the fly. Looks like it is not working when I use byNames() function along with columnNames() function. Is there any workaround for this?
PS: I cannot use columns() function because I need to reorder or filter some of the columns.
{count} votes
-
MarkKromer-MSFT 5,156 Reputation points Microsoft Employee2021-09-15T22:46:05.303+00:00 Just columnNames() alone will give you the column names
-
Philip Lee 1 Reputation point
2021-09-16T07:31:36.333+00:00 I'm having the same issue.
Trying to generate a hash of all incoming columns using- columns_hash = md5(byNames(columnNames())) which throws the error above.
If I pass a parameter in to generate the hash as in - columns_hash = md5(byNames(split($columns,','))) it works fine.
Why, when both the function split() and columnNames() return an array of string, which is then passed to the byNames() function, does the second expression work but the first fail?
-
Mark Kromer MSFT 1,141 Reputation points
2021-09-16T08:21:46.433+00:00 Wouldn't you use just want to hash the column values instead? md5(columns())
-
Philip Lee 1 Reputation point
2021-09-16T09:31:51.527+00:00 Yep - that worked :) Stupidity error on my part doh!!!
Thanks
-
56220211 96 Reputation points
2021-09-16T16:15:25.083+00:00 Thanks for your response. However, just the columnNames() function does not work in my case because I actually want the values of the columns. I want to get all the columns dynamically first and then reorder the columns before calculating the hash value. This is to make sure the hash value is correct even if the column orders are different in the source file.
-
Mark Kromer MSFT 1,141 Reputation points
2021-09-16T16:25:17.31+00:00 Reorder your columns with a Select transformation prior to the Derived Column. Then you can use just md5(columns()) to get the row hash of the column values using the column order you set.
-
56220211 96 Reputation points
2021-09-16T16:31:55.967+00:00 Is there a way to reorder the columns with Select transformation if I do not know my columns at the design time.
Sign in to comment