在Azure数据工厂中处理嵌套JSON对象。

huangapple go评论73阅读模式
英文:

Working with nested JSON objects in Azure Data Factory

问题

我目前正在尝试调用一个API以获取JSON格式的数据,并将其导入Azure Synapse Data Warehouse。以下是传入JSON的示例片段。我认为我遇到的问题涉及JSON的嵌套。当我运行数据移动活动时,SQL表只显示组和id列,而其他所有内容都为空。属性对象中的所有内容都为空。

我不确定映射应该是什么样的,它将$['data']作为集合引用,并将属性中的每列列为['attribute']['columnName']。我找到的每个来源都说这是正确的,但出于某种原因它找不到这些值。

{
    "data": [
        {
            "group": "A",
            "id": 1,
            "attribute": {
                "userfirstname": "Greg",
                "userlastname": "Boydle",
                "useremailaddress": "GregBoydle@gmail.org",
                
            }
        },
        {
            "group": "B",
            "id": 2,
            "attribute": {
                "userfirstname": "John",
                "userlastname": "Broglee",
                "useremailaddress": "JohnBroglee@myspace.gov",
                
            }
        }
    ]
}

非常感谢您的所有帮助,如果有需要解答的澄清问题,请告诉我。

英文:

I'm currently trying to call an API to grab data in JSON format and bring it into an Azure Synapse Data Warehouse. Below is a sample piece of the incoming JSON. The issue I'm running into I believe to be involving nesting the JSON. When I run the move data activity, the table in SQL only shows the group and id columns, while everything else is left null. Everything in the attribute object is left null.

I'm unsure what the mapping should look like, it has $['data'] as the collection reference, and every column listed in attribute as ['attribute']['columnName']. Every source I've found says this is correct, but it's not finding those values for some reason.

{
    "data": [
        {
            "group": "A",
            "id": 1,
            "attribute": {
                "userfirstname": "Greg",
                "userlastname": "Boydle",
                "useremailaddress": "GregBoydle@gmail.org",
                
            }
        },
        {
            "group": "B",
            "id": 2,
            "attribute": {
                "userfirstname": "John",
                "userlastname": "Broglee",
                "useremailaddress": "JohnBroglee@myspace.gov",
                
            }
        }
    ]
}

Any and all help is greatly appreciated, let me know if there are any clarifying questions that need answered.

答案1

得分: 1

以下是 Azure 数据工厂和 Synapse 中的映射:

ADF 映射:
在Azure数据工厂中处理嵌套JSON对象。

Synapse 映射:
在Azure数据工厂中处理嵌套JSON对象。

  • 使用集合引用作为 $[data] 并导入模式。
  • 不要启用高级编辑器。
    Azure SQL 表输出:
    在Azure数据工厂中处理嵌套JSON对象。

Azure Synapse 专用池输出:
在Azure数据工厂中处理嵌套JSON对象。

英文:

Below is the mapping in the Azure data factory and synapse:

ADF Mapping:
在Azure数据工厂中处理嵌套JSON对象。
Synapse Mapping:
在Azure数据工厂中处理嵌套JSON对象。

  • Use the Collection reference as $[data] and import the schema.
  • Do not enable the advanced editor.
    Azure sql table Output:
    在Azure数据工厂中处理嵌套JSON对象。
    Azure synapse dedicated pool output:
    在Azure数据工厂中处理嵌套JSON对象。

答案2

得分: 0

我纠正一下我的帖子,看起来我在 ADF 中有一些错误的设置,我从批量插入切换到复制命令,现在它正常工作了。谢谢 dileeprajnarayanthumula 之前的回答。

英文:

just giving a clarifying answer to my own post, it looks like I had some wrong settings in ADF, I switched from bulk insert to copy command and it's now working as expected. Thank you dileeprajnarayanthumula for answering earlier.

huangapple
  • 本文由 发表于 2023年7月18日 05:06:54
  • 转载请务必保留本文链接:https://go.coder-hub.com/76708083.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定