azure - 如何使用 Azure 逻辑应用将 Json 文件从 azure blob 存储解析到 Azure Sql

Question

[][1]我每周都会在 blob 存储中放入多个 json 文件，我想使用 azure 逻辑应用来解析 json 文件并将数据复制到 Azure Sql 中？请帮忙

Answer 1

为了实现您的要求，您可以遵循以下流程:-

Blob 触发器(当添加或修改 Blob 时(仅限属性)(V2)) >> 使用路径获取 Blob 内容 (V2) >> 解析 JSON < strong>>> SQL 相关操作(例如我正在使用插入行(V2))。

下面是我上传到容器的示例 JSON。

{
  "employees": {
    "emp_name": "abc",
    "hire_date": "2022-10-23",
    "salary": 10000
  }
}

我正在使用触发器路径来获取 blob 的内容。插入行时，我正在使用解析 JSON 值。下面是我的逻辑应用程序。

结果:

更新答案

根据您的要求，您可以使用 `` 手动触发流，或设置重复触发器以使流有时触发，然后使用从存储帐户列出该特定容器中的所有文件。流程如下所示

详细流程

结果:

我的逻辑应用的代码 View

{
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "For_each": {
                "actions": {
                    "Get_blob_content_using_path_(V2)": {
                        "inputs": {
                            "host": {
                                "connection": {
                                    "name": "@parameters('$connections')['azureblob']['connectionId']"
                                }
                            },
                            "method": "get",
                            "path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('AccountNameFromSettings'))}/GetFileContentByPath",
                            "queries": {
                                "inferContentType": true,
                                "path": "@items('For_each')?['Path']",
                                "queryParametersSingleEncoded": true
                            }
                        },
                        "runAfter": {},
                        "type": "ApiConnection"
                    },
                    "Insert_row_(V2)": {
                        "inputs": {
                            "body": {
                                "emp_id": "@body('Parse_JSON')?['employees']?['employee_id']",
                                "emp_name": "@body('Parse_JSON')?['employees']?['emp_name']",
                                "hire_date": "@body('Parse_JSON')?['employees']?['hire_date']",
                                "salary": "@body('Parse_JSON')?['employees']?['salary']"
                            },
                            "host": {
                                "connection": {
                                    "name": "@parameters('$connections')['sql']['connectionId']"
                                }
                            },
                            "method": "post",
                            "path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('default'))},@{encodeURIComponent(encodeURIComponent('default'))}/tables/@{encodeURIComponent(encodeURIComponent('[dbo].[employees]'))}/items"
                        },
                        "runAfter": {
                            "Parse_JSON": [
                                "Succeeded"
                            ]
                        },
                        "type": "ApiConnection"
                    },
                    "Parse_JSON": {
                        "inputs": {
                            "content": "@json(body('Get_blob_content_using_path_(V2)'))",
                            "schema": {
                                "properties": {
                                    "employees": {
                                        "properties": {
                                            "emp_name": {
                                                "type": "string"
                                            },
                                            "employee_id": {
                                                "type": "integer"
                                            },
                                            "hire_date": {
                                                "type": "string"
                                            },
                                            "salary": {
                                                "type": "integer"
                                            }
                                        },
                                        "type": "object"
                                    }
                                },
                                "type": "object"
                            }
                        },
                        "runAfter": {
                            "Get_blob_content_using_path_(V2)": [
                                "Succeeded"
                            ]
                        },
                        "type": "ParseJson"
                    }
                },
                "foreach": "@body('Lists_blobs_(V2)')?['value']",
                "runAfter": {
                    "Lists_blobs_(V2)": [
                        "Succeeded"
                    ]
                },
                "type": "Foreach"
            },
            "Lists_blobs_(V2)": {
                "inputs": {
                    "host": {
                        "connection": {
                            "name": "@parameters('$connections')['azureblob']['connectionId']"
                        }
                    },
                    "method": "get",
                    "path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('AccountNameFromSettings'))}/foldersV2/@{encodeURIComponent(encodeURIComponent('JTJmY29udGFpbmVyMQ=='))}",
                    "queries": {
                        "nextPageMarker": "",
                        "useFlatListing": false
                    }
                },
                "metadata": {
                    "JTJmY29udGFpbmVyMQ==": "/container1"
                },
                "runAfter": {},
                "type": "ApiConnection"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {
            "$connections": {
                "defaultValue": {},
                "type": "Object"
            }
        },
        "triggers": {
            "manual": {
                "inputs": {
                    "schema": {}
                },
                "kind": "Http",
                "type": "Request"
            }
        }
    },
    "parameters": {
        "$connections": {
            "value": {
                "azureblob": {
                    "connectionId": "/subscriptions/<SUBSCRIPTION ID>/resourceGroups/<RESOURCE GROUP NAME>/providers/Microsoft.Web/connections/azureblob",
                    "connectionName": "azureblob",
                    "id": "/subscriptions/<SUBSCRIPTION ID>/providers/Microsoft.Web/locations/centralus/managedApis/azureblob"
                },
                "sql": {
                    "connectionId": "/subscriptions/<SUBSCRIPTION ID>/resourceGroups/<RESOURCE GROUP NAME>/providers/Microsoft.Web/connections/sql",
                    "connectionName": "sql",
                    "id": "/subscriptions/<SUBSCRIPTION ID>/providers/Microsoft.Web/locations/centralus/managedApis/sql"
                }
            }
        }
    }
}