Azure管道将blob文件加载到文件名是动态的Azure SQL数据库

问题描述：

大家好，

您能帮我以下吗?

我在同一个Blob中有两个文件

I have two files in same Blob

2018-10-23_Depot

2018-10-23_DockType

两个文件数据都应放在两个不同的表中(表名-Depot，DockType)

Both file data should go in two different tables (Table name - Depot , DockType)

日期每次都在改变.但是文件的剩余名称始终相同，不会更改

Date is changing every time. but remaining name of the file is always same , It do not change

如何通过ADF管道加载这些文件，以及如何将文件名作为软件仓库"传递和"DockType"在管道中

How to load these files through ADF pipeline and how to pass file name as "Depot" and "DockType" in pipeline

非常感谢快速帮助

谢谢

此致

马赫什语

Mahesh Dadhich

答

您好，

请按照以下步骤操作:

1.使用名称为"filename"的参数创建一个 zure blob数据集. 并通过参数到blob fileName属性.

1. Create an azure blob dataset with a parameter named "filename" in it and pass parameter to the blob fileName property.

2.类似地，c 创建一个带有 ="font-size:0.75em">名为"tablename"的参数；在 it 然后将参数传递给tableName属性.

2. Similarly, create an azure sql dataset with a parameter named "tablename" in it then pass parameter to tableName property.

3.使用创建管道对于每个活动，在每个活动中都包含一个复制具有以上 blob数据集作为源并使用azure sql .

3. Create a pipeline with a for each activity, in for each activity contains a Copy activity with the above blob dataset as source and the azure sql dataset as the sink.

4.在管道中，创建一个名为"sourceAndSinkItems"的参数.数组的类型，默认值为

[{"source":"2018-10-23_Depot","sink":"Depot"},{"source":"2018-10-23_DockType","sink":"DockType"}]"

将其传递给属性"items"每个活动的.

5.在复制活动中，通过

@item().source

到文件名" azure blob数据集和

to the "filename" of the azure blob dataset and

@item().sink

到表名" sql数据集.

to the "tablename" of the sql dataset.

6.触发管道运行.

以下是示例管道/源数据集/接收器数据集json定义:

The below is the sample pipeline/source dataset/sink dataset json definition:

管道:

{
    "name": "pipeline38",
    "properties": {
        "activities": [
            {
                "name": "ForEach1",
                "type": "ForEach",
                "typeProperties": {
                    "items": {
                        "value": "@pipeline().parameters.sourceAndSinkItems",
                        "type": "Expression"
                    },
                    "activities": [
                        {
                            "name": "Copy Data1",
                            "type": "Copy",
                            "typeProperties": {
                                "source": {
                                    "type": "BlobSource",
                                    "recursive": true
                                },
                                "sink": {
                                    "type": "SqlSink",
                                    "writeBatchSize": 10000
                                }
                            },
                            "inputs": [
                                {
                                    "referenceName": "AzureBlob1",
                                    "type": "DatasetReference",
                                    "parameters": {
                                        "filename": {
                                            "value": "@item().source",
                                            "type": "Expression"
                                        }
                                    }
                                }
                            ],
                            "outputs": [
                                {
                                    "referenceName": "AzureSqlTable2",
                                    "type": "DatasetReference",
                                    "parameters": {
                                        "tablename": {
                                            "value": "@item().sink",
                                            "type": "Expression"
                                        }
                                    }
                                }
                            ]
                        }
                    ]
                }
            }
        ],
        "parameters": {
            "sourceAndSinkItems": {
                "type": "Array",
                "defaultValue": [
                    {
                        "source": "2018-10-23_Depot",
                        "sink": "Depot"
                    },
                    {
                        "source": "2018-10-23_DockType",
                        "sink": "DockType"
                    }
                ]
            }
        }
    }
}

blob数据集:

{
    "name": "AzureBlob1",
    "properties": {
        "linkedServiceName": {
            "referenceName": "azurestorage1",
            "type": "LinkedServiceReference"
        },
        "parameters": {
            "filename": {
                "type": "String"
            }
        },
        "type": "AzureBlob",
        "typeProperties": {
            "format": {
                "type": "TextFormat",
                "columnDelimiter": ",",
                "nullValue": "\\N",
                "treatEmptyAsNull": true,
                "skipLineCount": 0,
                "firstRowAsHeader": false
            },
            "fileName": {
                "value": "@dataset().filename",
                "type": "Expression"
            },
            "folderPath": "aaa"
        }
    },
    "type": "Microsoft.DataFactory/factories/datasets"
}

azure< g class ="gr_ gr_2959 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id ="2959" id ="2959"> sql</g>数据集

azure <g class="gr_ gr_2959 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="2959" id="2959">sql</g> dataset

{
    "name": "AzureSqlTable2",
    "properties": {
        "linkedServiceName": {
            "referenceName": "azuresql1",
            "type": "LinkedServiceReference"
        },
        "parameters": {
            "tablename": {
                "type": "String"
            }
        },
        "type": "AzureSqlTable",
        "typeProperties": {
            "tableName": {
                "value": "@dataset().tablename",
                "type": "Expression"
            }
        }
    },
    "type": "Microsoft.DataFactory/factories/datasets"
}

希望会有所帮助.

Azure管道将blob文件加载到文件名是动态的Azure SQL数据库

相关推荐