question

MBW-1525 avatar image
0 Votes"
MBW-1525 asked MainKhalifa-3598 answered

Inferring: Data type from Mapping cannot be determined if the first value of a field is null.

Hello,

so i was wondering if there is a possibility to infer data types for the mapping with a recursive method, instead of just looking at the first row. I will try to explain my usecase as detailed as possible:
I downloaded a Database as a json (Zendesk) and uploaded the json into a blob storage and now i want to use the json file as source for a table in azure sql database. The problem is some of the values in the first row/entry have null as value and thus i have to look into the data and search for an row/entry where that field is not null (empty) to determine the datatype and then manually map that data type in ADF. This is a tedious task since I have to push multiple tables to the azure sql as we are currently migrating in our company.

The only related and relevant threads i found are:
https://docs.microsoft.com/en-us/answers/questions/86880/pipeline-failed-when-parsing-json-due-to-null-valu.html

But this is currently not a viable solution. As im using the LinkedService (SQL Server) for all tables and dont even have a table in sql to map the linked service to.


Best regards

MBW

sql-server-generalazure-data-factoryazure-sql-databasesql-server-migration
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HI @MBW-1525, welcome to Microsoft Q&A forum.

If I understand correctly you want to read multiple JSON files and create DDL statements of SQL tables.

Just wanted to check if you specifically want to use ADF. Otherwise this can be achieved using .NET program where we can loop though all the JSON file, load them into .NET datatables and then generate the table DDL schema based on any of not null rows. We just need to provide the json properties datatypes mapping to SQL type once in the code. If you want to do it through the ADF, we can put the correct tag on the question and appropriate engineers can take a look at it.

0 Votes 0 ·

HI @AnuragSharma-MSFT,

i would like to do it through ADF, since im not very familiar with .net coding. If possible Python would be an option.

Best regards

MBW

0 Votes 0 ·

Thanks for your reply, I have added the azure data factory tag to the question and it will be looked upon by appropriate engineers now.

0 Votes 0 ·
ShaikMaheer-MSFT avatar image
0 Votes"
ShaikMaheer-MSFT answered ShaikMaheer-MSFT commented

Hi @MBW-1525 ,

Could you please help me understand your ask better by sharing details on below asks.

  • Did you tried creating dataset in ADF for your data? If yes, what is the format and connector type of that dataset?

  • Do you mean you are trying to create a dataset for SQL table and you don't see proper datatype coming up?

between, You can have your mappings dynamic also in copy activity. Please check below video for better idea if that helps.
https://www.youtube.com/watch?v=b27gmOufge4

You can also import schema in to your datasets using sample files from local.
190614-image.png



image.png (59.3 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MBW-1525 , Just checking if you get chance to share updates. Thank you.

0 Votes 0 ·
MBW-1525 avatar image MBW-1525 ShaikMaheer-MSFT ·

Hi @ShaikMaheer-MSFT, i solved the problem with mapping everything to string and using DBeaver to analyze the rows.

0 Votes 0 ·

Hi @MBW-1525 , Awesome. Thank you for sharing your resolution details with community. Please feel free to mark it as Accepted Answer. Accepted answers help community as well.

0 Votes 0 ·
MainKhalifa-3598 avatar image
0 Votes"
MainKhalifa-3598 answered

Thank you By mapping all fields to strings and analyzing the rows in DBeaver, I was able to solve the problem.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.