Copy activity with REST source - mapping JSON to SQL

Question

Copy activity with REST source - mapping JSON to SQL

Paul Hernandez 691 Microsoft Employee

Hi everyone,

I'm trying to parse a response from a REST call using the copy activity.

I can check how the response looks like using the preview data:

I used the "import schema" option in the copy activity. The mappings look like:

The pipeline finished successfully but no record is inserted in the target table.

I also verified the json path in the "Advanced editor" and check them with an online jsonpath evaluator:

Everything looks good to me. The only strange thing is, in the data preview an extra array level is shown on the top level, but I guess this is only a data preview thing.

Any help will be highly appreciated.

Best regards,

Paul

Paul Hernandez 691 Reputation points Microsoft Employee

2022-02-09T07:58:30.55+00:00

As additional information, I made it work using the same columns names in the output as in the input.

That's a bit suspicious, in fact I compared the two translators and they look the same:

The one who maps the incoming json attributes to the table with the same names works and the other one doesn't.

I also noticed that the dropdowns to select the target columns are not recognizing the schema of the target table in normal mode but they do in the "Advanced editor".

Any idea or should I report a bug?
Ryan Abbey 1,186 Reputation points

2022-02-09T20:54:07.527+00:00

So you're saying the one that maps "displayName" to "displayName" is working? (I'm assuming your defined the table with column "displayName" rather than this is successfully loading to column "Group_Name"?)

It does look a touch suspicious... I have no problem renaming source columns when landing to storage, wouldn't have thought it would be any different loading to a table.

When you run in debug and no rows are loaded, if you look at the glasses alongside the copy activity in the Output window, does it identify the records as being rejected? You can try set up a log for these rejections and the log should give a reason why it rejects them
Paul Hernandez 691 Reputation points Microsoft Employee

2022-02-10T07:13:05.32+00:00

Hi @Ryan Abbey ,

thanks for your input. I will enable logging and see what happen. In the mean time I also discovered the copy is only working if the sink is using "Bulk Insert". Copy command does not apply since I am adding additional columns and polybase does not complain but it is not working.
Paul Hernandez 691 Reputation points Microsoft Employee

2022-02-10T15:49:00.31+00:00

Hi @Ryan Abbey ,

Appart from the column names problem I have a second issue.

I want to add additional columns to the source like this:

The output now looks like:

I want to flatten out this json but I found two issues.

If I configure the mappings like this:

The added column "DSTS" has only NULL values

If then I do not select any collection reference, then only one line is inserted with all columns at NULL except the "DSTS"

According to this article https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping

Do you see something wrong?

Thanks in advance!
Ryan Abbey 1,186 Reputation points

2022-02-10T22:08:13.373+00:00

Afraid I don't have much to contribute there... try look at the code (or post here) and see if there is something that gives away why it is excluding that column
MartinJaffer-MSFT 26,161 Reputation points

2022-02-14T23:33:54.107+00:00
As a quick test, (coming from https://learn.microsoft.com/en-us/answers/questions/730871/index.html ), I made test data

{ "value": [ { "id": "02", "displayName": "deus" }, { "id": "04", "displayName": "quat" }, { "id": "08", "displayName": "oct" } ] }

and additional column @utcnow() . I used source json blob, and sink delimited text blob. Things seemed to work...

"02","deus","2022-02-14T23:21:58.1216604Z" "04","quat","2022-02-14T23:21:58.1216604Z" "08","oct","2022-02-14T23:21:58.1216604Z"

I did notice some differences... I was shortcutting by using a json in blob storage instead of going to a REST API, and I was using a csv blob instead of setting up a SQL table. Either could be a cause for why mine worked. I also noticed in my mapping , it wrote Additional on the new column. Your picture did not show that.

More tests needed on my end. Next would be either modifying the source to have another column to cross-apply, or shift to use SQL sink.

1 answer

Your answer

Paul Hernandez 691 Reputation points Microsoft Employee

2022-02-09T07:58:30.55+00:00

As additional information, I made it work using the same columns names in the output as in the input.

That's a bit suspicious, in fact I compared the two translators and they look the same:

The one who maps the incoming json attributes to the table with the same names works and the other one doesn't.

I also noticed that the dropdowns to select the target columns are not recognizing the schema of the target table in normal mode but they do in the "Advanced editor".

Any idea or should I report a bug?
Ryan Abbey 1,186 Reputation points

2022-02-09T20:54:07.527+00:00

So you're saying the one that maps "displayName" to "displayName" is working? (I'm assuming your defined the table with column "displayName" rather than this is successfully loading to column "Group_Name"?)

It does look a touch suspicious... I have no problem renaming source columns when landing to storage, wouldn't have thought it would be any different loading to a table.

When you run in debug and no rows are loaded, if you look at the glasses alongside the copy activity in the Output window, does it identify the records as being rejected? You can try set up a log for these rejections and the log should give a reason why it rejects them
Paul Hernandez 691 Reputation points Microsoft Employee

2022-02-10T07:13:05.32+00:00

Hi @Ryan Abbey ,

thanks for your input. I will enable logging and see what happen. In the mean time I also discovered the copy is only working if the sink is using "Bulk Insert". Copy command does not apply since I am adding additional columns and polybase does not complain but it is not working.
Paul Hernandez 691 Reputation points Microsoft Employee

2022-02-10T15:49:00.31+00:00

Hi @Ryan Abbey ,

Appart from the column names problem I have a second issue.

I want to add additional columns to the source like this:

The output now looks like:

I want to flatten out this json but I found two issues.

If I configure the mappings like this:

The added column "DSTS" has only NULL values

If then I do not select any collection reference, then only one line is inserted with all columns at NULL except the "DSTS"

According to this article https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping

Do you see something wrong?

Thanks in advance!
Ryan Abbey 1,186 Reputation points

2022-02-10T22:08:13.373+00:00

Afraid I don't have much to contribute there... try look at the code (or post here) and see if there is something that gives away why it is excluding that column
MartinJaffer-MSFT 26,161 Reputation points

2022-02-14T23:33:54.107+00:00

As a quick test, (coming from https://learn.microsoft.com/en-us/answers/questions/730871/index.html ), I made test data

{ "value": [ { "id": "02", "displayName": "deus" }, { "id": "04", "displayName": "quat" }, { "id": "08", "displayName": "oct" } ] }

and additional column @utcnow() . I used source json blob, and sink delimited text blob. Things seemed to work...

"02","deus","2022-02-14T23:21:58.1216604Z" "04","quat","2022-02-14T23:21:58.1216604Z" "08","oct","2022-02-14T23:21:58.1216604Z"

I did notice some differences... I was shortcutting by using a json in blob storage instead of going to a REST API, and I was using a csv blob instead of setting up a SQL table. Either could be a cause for why mine worked. I also noticed in my mapping , it wrote Additional on the new column. Your picture did not show that.

More tests needed on my end. Next would be either modifying the source to have another column to cross-apply, or shift to use SQL sink.

Answer 1

Sorry for the delay. I have the results of my next test. @Paul Hernandez @Ryan Abbey

Again shortcutting by using JSOn in a blob instead of REST connector, but this time with SQL sink, I was able to successfuly copy all. This indicates the cause is not due to being SQL. That leaves the possibility of the cause being a REST source with pagination mixing with the mapping collection reference. Or a typo in mapping.

Source data:

{  
	"lowdata":"hello",  
	"value": [  
		{  
			"id": "02",  
			"displayName": "deus"  
		},  
		{  
			"id": "04",  
			"displayName": "quat"  
		},  
		{  
			"id": "08",  
			"displayName": "oct"  
		}  
	]  
}

Pipeline definition:

{  
    "name": "pipeline21",  
    "properties": {  
        "activities": [  
            {  
                "name": "Copy data1",  
                "type": "Copy",  
                "dependsOn": [],  
                "policy": {  
                    "timeout": "7.00:00:00",  
                    "retry": 0,  
                    "retryIntervalInSeconds": 30,  
                    "secureOutput": false,  
                    "secureInput": false  
                },  
                "userProperties": [],  
                "typeProperties": {  
                    "source": {  
                        "type": "JsonSource",  
                        "additionalColumns": [  
                            {  
                                "name": "time",  
                                "value": {  
                                    "value": "@utcnow()",  
                                    "type": "Expression"  
                                }  
                            }  
                        ],  
                        "storeSettings": {  
                            "type": "AzureBlobStorageReadSettings",  
                            "recursive": false,  
                            "enablePartitionDiscovery": false  
                        },  
                        "formatSettings": {  
                            "type": "JsonReadSettings"  
                        }  
                    },  
                    "sink": {  
                        "type": "AzureSqlSink",  
                        "writeBehavior": "insert",  
                        "sqlWriterUseTableLock": false  
                    },  
                    "enableStaging": false,  
                    "translator": {  
                        "type": "TabularTranslator",  
                        "mappings": [  
                            {  
                                "source": {  
                                    "path": "['id']"  
                                },  
                                "sink": {  
                                    "name": "col1",  
                                    "type": "String"  
                                }  
                            },  
                            {  
                                "source": {  
                                    "path": "['displayName']"  
                                },  
                                "sink": {  
                                    "name": "col2",  
                                    "type": "String"  
                                }  
                            },  
                            {  
                                "source": {  
                                    "path": "$['lowdata']"  
                                },  
                                "sink": {  
                                    "name": "col3",  
                                    "type": "String"  
                                }  
                            },  
                            {  
                                "source": {  
                                    "path": "$['time']"  
                                },  
                                "sink": {  
                                    "name": "col4",  
                                    "type": "String"  
                                }  
                            }  
                        ],  
                        "collectionReference": "$['value']",  
                        "mapComplexValuesToString": true  
                    }  
                },  
                "inputs": [  
                    {  
                        "referenceName": "disper",  
                        "type": "DatasetReference"  
                    }  
                ],  
                "outputs": [  
                    {  
                        "referenceName": "AzureSqlTable2",  
                        "type": "DatasetReference"  
                    }  
                ]  
            }  
        ],  
        "annotations": []  
    }  
}

Paul Hernandez 691 Reputation points Microsoft Employee

2022-02-23T08:53:03.54+00:00

Hi @MartinJaffer-MSFT ,

thanks for your input.
I recreated the pipeline from scratch and the problem was solved.
MartinJaffer-MSFT 26,161 Reputation points

2022-02-23T17:44:50.393+00:00

Thank you for letting us know!

Share via

Copy activity with REST source - mapping JSON to SQL

1 answer

Your answer