ADF converts NUMBER datatype to Decimal, String (if precision > 28) while copying data type from ORACLE to ADLS as Parquet

Swapnil Sarkar 0

While extracting data from Oracle on premise to Azure Data Lake as Parquet files via SHIR, we notice the below points:

NUMBER Datatype in Oracle gets converted to Decimal, String (if precision > 28) in parquet file

Kindly share some light on the above conversion.

And please highlight the point that which resource is responsible for this data type conversion is it ADF internal resource or ADF asks the oracle server to perform the conversion.

Additionally we noticed that due to this datatype conversion the copy duration is significantly increasing and is drastically impacting our data loads.

AnnuKumari-MSFT 33,151 Reputation points Microsoft Employee

2023-08-19T07:30:36.56+00:00

Hi Swapnil Sarkar ,

Just following up to see if the below answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'

1 answer

AnnuKumari-MSFT 33,151 Reputation points Microsoft Employee

2023-07-31T07:19:39.4033333+00:00

Hi Swapnil Sarkar ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

As per my understanding, you have noticed that the NUMBER datatype in Oracle gets converted to Decimal, String (if precision > 28) in the Parquet file. You want to know if that is expected and in case it is , then which resource is responsible to do that.

This is an expected behavior. When you copy data from and to Oracle, interim data type mappings are used within the service . Oracle data types like FLOAT, INTEGER and NUMBER are converted into Decimal, String (if precision > 28)

For more details, kindly check the official documentation : Data type mapping for Oracle

Probably, the conversion is taken care in the oracle connector present in ADF. Since it is increasing the copy duration and impacting the data loads, you can try utilizing the guidance provided in the following documentation to improve the performance while using copy activity : Copy activity performance and scalability guide

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyuou
Please sign in to rate this answer.
Swapnil Sarkar 0 Reputation points

2023-07-31T07:32:23.12+00:00

Hi Annu,

Thanks for your prompt response.

To address your points:

Copy activity performance and scalability guide: We have already implemented most of the optimization techniques mentioned in the document, such as using a folder instead of files, applying dynamic range partition, using a staged copy and even parallel copies.

But we still see a bottleneck around those data conversion, to highlight we created a replica of the same table with the Number data types changed to string and as a result we see drastic improvement in the performance. But this manual conversion is not what we want to go ahead with.

We would further like to understand this Oracle Connector present in ADF and how it works, if you could share some documentation of the same would be really helpful. Additionally, what is the compatibility of the ADF oracle connector with different version of Oracle like 12, 18, 19.

Thanks & Regards,

Swapnil

AnnuKumari-MSFT 33,151 Reputation points Microsoft Employee

2023-08-02T06:34:46.19+00:00

Hi Swapnil Sarkar ,

I understand that you want to understand how the things work in the background, but unfortunately , that details is not available from the internal team.

I would recommend you to go for support ticket.

For a deeper investigation and immediate assistance on this issue , if you have a support plan you may file a support ticket. In case you don't have a support plan , I will enable a one-time free support request for your subscription. Please do let us know.

Hope it helps. Kindly accept the answer by clicking on Accept answer button or comment below for additional queries . Here is how you can Create an Azure support request. Thankyou

AnnuKumari-MSFT 33,151 Reputation points Microsoft Employee

2023-08-03T11:15:52.42+00:00

Hi Swapnil Sarkar ,

Just checking in to see if the above answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'
Sign in to comment

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

Share via

ADF converts NUMBER datatype to Decimal, String (if precision > 28) while copying data type from ORACLE to ADLS as Parquet

1 answer

Your answer