Hi Team, I have build a dynamic pipeline to load the data from Oracle tables to datalake as Parquet files. Now, when I am trying to copy the data from source to sink through CopyActivity, then I am not able to convert the decimal datatype of the columns dynamically. In the json, I have changed my source and sink type as Int32 for one column, but when I preview the sink data, then I see decimal values only for that column. Similar query was raised few years back , so wanted to know if we have any solution now. https://learn.microsoft.com/en-us/answers/questions/576333/azure-data-factory-oracle-to-parquet-datatype-mism Currently, I am passing my columns as parameter in dataflow derived column transformation to convert it into integer type. But, I don't think its a good solution if I have to do some other column datatype conversion. So, is there a better way where I can convert all the columns with new datatype in one go .

@Vaibhav Thank you for your response and clarifying your requirements. I understand that you have already implemented the solution I suggested using the Data Flow transformation in Azure Data Factory, but you are looking for a more dynamic solution where you can convert all the columns with new datatype in one go, similar to the Copy Activity dynamic mapping tab. However, as per my knowledge, there is no direct way to convert all the columns with a new datatype in one go using the Copy Activity dynamic mapping tab. The dynamic mapping tab only allows you to map columns between the source and sink datasets dynamically. If you want to convert all the columns with a new datatype in one go, you can use the Data Flow transformation in Azure Data Factory. You can use the Derived Column transformation to convert the decimal datatype columns to Int32, string to date, or any other datatype conversion. Hope this helps. Do let us know if you any further queries. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Azure Data Factory Oracle to Parquet datatype mismatch

Vaibhav 105

Hi Team,

I have build a dynamic pipeline to load the data from Oracle tables to datalake as Parquet files.
Now, when I am trying to copy the data from source to sink through CopyActivity, then I am not able to convert the decimal datatype of the columns dynamically.

User's image

In the json, I have changed my source and sink type as Int32 for one column, but when I preview the sink data, then I see decimal values only for that column.

Similar query was raised few years back , so wanted to know if we have any solution now.
https://learn.microsoft.com/en-us/answers/questions/576333/azure-data-factory-oracle-to-parquet-datatype-mism
Currently, I am passing my columns as parameter in dataflow derived column transformation to convert it into integer type. But, I don't think its a good solution if I have to do some other column datatype conversion.
So, is there a better way where I can convert all the columns with new datatype in one go .

Smaran Thoomu 17,995 Reputation points Microsoft Vendor

2024-05-10T07:55:15.53+00:00
Hi @Vaibhav

Thank you for reaching out to the community forum with your query.

I understand that you are facing an issue while converting decimal datatype columns to Int32 while copying data from Oracle tables to Datalake as Parquet files using Azure Data Factory. You have already tried changing the source and sink type as Int32 for one column in the JSON, but when you preview the sink data, you see decimal values only for that column. You have also referred to a similar query raised a few years back and are looking for a better solution to convert all the columns with new datatype in one go.

As far as I know, to convert all the columns with a new datatype in one go, you can use the Data Flow transformation in Azure Data Factory. You can use the Derived Column transformation to convert the decimal datatype columns to Int32. Here are the steps to achieve this:

Create a new Data Flow in your Azure Data Factory pipeline.

Add a Source transformation to read data from the Oracle table.

Add a Derived Column transformation to convert the decimal datatype columns to Int32. You can use the following expression in the Derived Column transformation to convert the decimal datatype column to Int32: toInteger(columnName) Replace columnName with the name of the decimal datatype column.

Add a Sink transformation to write data to the Datalake as Parquet files.

You can also parameterize the Derived Column transformation to convert all the decimal datatype columns to Int32 dynamically. Here are the steps to achieve this:

Create a new parameter in your Azure Data Factory pipeline to store the column names that need to be converted to Int32.

Pass the parameter value to the Derived Column transformation.

Use the following expression in the Derived Column transformation to convert the decimal datatype columns to Int32 dynamically: toInteger(iif(contains(parameterColumnName, columnName), columnName, null)) Replace parameterColumnName with the name of the parameter that stores the column names that need to be converted to Int32, and columnName with the name of the column.

I hope this helps. Let me know if you have any further questions or concerns.
Vaibhav 105 Reputation points

2024-05-10T10:34:32.96+00:00

@Smaran Thoomu

Thanks for the response.

If you see my query ,I have already implemented the solution that you shared on dataflow. ((ORGNBR,TAXID) is passed through parameters only) .
I am using parameter for the columns that I want to convert to Integer. Problem with this solution is , I can only do integer conversion in one go. If I need to convert other columns, such as decimal to string or string to date, I would have to create additional Derived columns which defeats the purpose of having a dynamic pipeline. Also, I will be required to create additional columns in my control table for each conversion where I need to mention the columns.

So, I was looking for some solution where I just need to mention all the datatype change at one time just like the copyactivity dynamic mapping tab, where I can pass the complete json through parameters. .
PRADEEPCHEEKATLA 90,261 Reputation points

2024-05-20T08:28:38.4033333+00:00

@Vaibhav - Just checking in to see if the below answer helped. If this answers your query, do click "Accept the answer” for the same, which might be beneficial to other community members reading this thread. And, if you have any further query do let us know.

1 answer

Smaran Thoomu 17,995 Reputation points Microsoft Vendor

2024-05-10T11:27:40.9033333+00:00

@Vaibhav Thank you for your response and clarifying your requirements.

I understand that you have already implemented the solution I suggested using the Data Flow transformation in Azure Data Factory, but you are looking for a more dynamic solution where you can convert all the columns with new datatype in one go, similar to the Copy Activity dynamic mapping tab.
However, as per my knowledge, there is no direct way to convert all the columns with a new datatype in one go using the Copy Activity dynamic mapping tab. The dynamic mapping tab only allows you to map columns between the source and sink datasets dynamically.

If you want to convert all the columns with a new datatype in one go, you can use the Data Flow transformation in Azure Data Factory. You can use the Derived Column transformation to convert the decimal datatype columns to Int32, string to date, or any other datatype conversion.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Azure Data Factory Oracle to Parquet datatype mismatch

1 answer

Your answer