Copia masiva de varias tablas mediante Azure Data Factory con PowerShell
SE APLICA A:
Azure Data Factory Azure Synapse Analytics
Sugerencia
Pruebe Data Factory en Microsoft Fabric, una solución de análisis todo en uno para empresas. Microsoft Fabric abarca todo, desde el movimiento de datos hasta la ciencia de datos, el análisis en tiempo real, la inteligencia empresarial y los informes. Obtenga más información sobre cómo iniciar una nueva evaluación gratuita.
En este tutorial se muestra cómo puede copiar varias tablas de Azure SQL Database a Azure Synapse Analytics. Además, puede aplicar el mismo patrón en otros escenarios de copia. Por ejemplo, para copiar tablas de SQL Server u Oracle a Azure SQL Database, Data Warehouse o el blob de Azure, o bien para copiar diferentes rutas de acceso de blob a tablas de Azure SQL Database.
A grandes rasgos, este tutorial incluye los pasos siguientes:
- Creación de una factoría de datos.
- Creación de los servicios vinculados de Azure SQL Database, Azure Synapse Analytics y Azure Storage.
- Creación de los conjuntos de datos de Azure SQL Database y Azure Synapse Analytics.
- Cree una canalización para buscar las tablas que se deben copiar y otra canalización para realizar la operación de copia real.
- Inicio de la ejecución de una canalización.
- Supervisión de las ejecuciones de canalización y actividad.
En este tutorial se usa Azure PowerShell. Para obtener información sobre el uso de otras herramientas o SDK para crear una factoría de datos, consulte los inicios rápidos.
Flujo de trabajo de un extremo a otro
En este escenario, tenemos varias tablas en Azure SQL Database que queremos copiar en Azure Synapse Analytics. Esta es la secuencia lógica de pasos del flujo de trabajo que se realiza en las canalizaciones:
- La primera canalización busca la lista de tablas que debe copiarse en los almacenes de datos del receptor. También puede mantener una tabla de metadatos que muestre todas las tablas que se deben copiar en el almacén de datos receptor. A continuación, la canalización desencadena otra canalización, que itera en todas las tablas de la base de datos y realiza la operación de copia de datos.
- La segunda canalización realiza la copia real. Toma la lista de tablas como un parámetro. Para cada tabla de la lista, copie la tabla específica de Azure SQL Database a la tabla correspondiente de Azure Synapse Analytics con la copia almacenada provisionalmente mediante Blob Storage y PolyBase a fin de obtener el mejor rendimiento. En este ejemplo, la primera canalización pasa la lista de tablas como un valor para el parámetro.
Si no tiene una suscripción a Azure, cree una cuenta gratuita antes de empezar.
Requisitos previos
Nota:
Se recomienda usar el módulo Azure Az de PowerShell para interactuar con Azure. Consulte Instalación de Azure PowerShell para empezar. Para más información sobre cómo migrar al módulo Az de PowerShell, consulte Migración de Azure PowerShell de AzureRM a Az.
- Azure PowerShell. Siga las instrucciones de Instalación y configuración de Azure PowerShell.
- Cuenta de Azure Storage. La cuenta de Azure Storage se usa como almacenamiento de blobs de almacenamiento provisional en la operación de copia masiva.
- Azure SQL Database. Esta base de datos contiene los datos de origen.
- Azure Synapse Analytics. Este almacén de datos contiene los datos que se copian de SQL Database.
Preparación de SQL Database y Azure Synapse Analytics
Preparación de la base de datos de Azure SQL de origen:
Cree una base de datos con los datos de ejemplo de Adventure Works LT en SQL Database siguiendo el artículo Creación de una base de datos en Azure SQL Database. En este tutorial se copian todas las tablas de esta base de datos de ejemplo a Azure Synapse Analytics.
Preparación de la instancia receptora de Azure Synapse Analytics:
Si no tiene un área de trabajo de Azure Synapse Analytics, consulte el artículo Introducción a Azure Synapse Analytics para conocer los pasos para crear una.
Cree los esquemas de tabla correspondientes en Azure Synapse Analytics. Debe usar Azure Data Factory para migrar o copiar datos en un paso posterior.
Servicios de Azure para acceder a SQL Server
Para SQL Database y Azure Synapse Analytics, permita que los servicios de Azure accedan a SQL Server. Asegúrese de que la opción Permitir el acceso a servicios de Azure esté activada para el servidor. Esta configuración permite al servicio Data Factory leer los datos de Azure SQL Database y escribir los datos en Azure Synapse Analytics. Para comprobar y activar esta configuración, realice los siguientes pasos:
- Haga clic en Todos los servicios a la izquierda y en Servidores SQL.
- Seleccione el servidor y haga clic en Firewall en CONFIGURACIÓN.
- En la hoja Configuración de firewall, haga clic en Activar para Permitir el acceso a los servicios de Azure.
Crear una factoría de datos
Inicie PowerShell. Mantenga Azure PowerShell abierto hasta el final de este tutorial. Si lo cierra y vuelve a abrirlo, deberá ejecutar los comandos de nuevo.
Ejecute el siguiente comando y escriba el nombre de usuario y la contraseña que utiliza para iniciar sesión en Azure Portal:
Connect-AzAccountEjecute el siguiente comando para ver todas las suscripciones de esta cuenta:
Get-AzSubscriptionEjecute el comando siguiente para seleccionar la suscripción con la que desea trabajar. Reemplace SubscriptionId con el identificador de la suscripción de Azure:
Select-AzSubscription -SubscriptionId "<SubscriptionId>"Ejecute el cmdlet Set-AzDataFactoryV2 para crear una factoría de datos. Reemplace los marcadores de posición con sus propios valores antes de ejecutar el comando.
$resourceGroupName = "<your resource group to create the factory>" $dataFactoryName = "<specify the name of data factory to create. It must be globally unique.>" Set-AzDataFactoryV2 -ResourceGroupName $resourceGroupName -Location "East US" -Name $dataFactoryNameTenga en cuenta los siguientes puntos:
El nombre de la instancia de Azure Data Factory debe ser único de forma global. Si recibe el siguiente error, cambie el nombre y vuelva a intentarlo.
The specified Data Factory name 'ADFv2QuickStartDataFactory' is already in use. Data Factory names must be globally unique.Para crear instancias de Data Factory, debe ser administrador o colaborador de la suscripción de Azure.
Para una lista de las regiones de Azure en las que Data Factory está disponible actualmente, seleccione las regiones que le interesen en la página siguiente y expanda Análisis para poder encontrar Data Factory: Productos disponibles por región. Los almacenes de datos (Azure Storage, Azure SQL Database, etc.) y los procesos (HDInsight, etc.) que usa la factoría de datos pueden encontrarse en otras regiones.
Crear servicios vinculados
En este tutorial, creará tres servicios vinculados para el origen, el receptor, y el blob de almacenamiento provisional respectivamente, lo que incluye conexiones con sus almacenes de datos:
Creación del servicio vinculado de Azure SQL Database de origen
Cree un archivo JSON con el nombre AzureSqlDatabaseLinkedService.json en la carpeta C:\ADFv2TutorialBulkCopy con el siguiente contenido: Si todavía no existe, cree la carpeta ADFv2TutorialBulkCopy.
Importante
Reemplace <servername>, <databasename>, <username>@<servername> and <password> por los nombres de su base de datos de Azure SQL antes de guardar el archivo.
{ "name": "AzureSqlDatabaseLinkedService", "properties": { "type": "AzureSqlDatabase", "typeProperties": { "connectionString": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;User ID=<username>@<servername>;Password=<password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30" } } }En Azure PowerShell, cambie a la carpeta ADFv2TutorialBulkCopy.
Ejecute el cmdlet Set-AzDataFactoryV2LinkedService para crear el servicio vinculado: AzureSqlDatabaseLinkedService.
Set-AzDataFactoryV2LinkedService -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -Name "AzureSqlDatabaseLinkedService" -File ".\AzureSqlDatabaseLinkedService.json"Este es la salida de ejemplo:
LinkedServiceName : AzureSqlDatabaseLinkedService ResourceGroupName : <resourceGroupName> DataFactoryName : <dataFactoryName> Properties : Microsoft.Azure.Management.DataFactory.Models.AzureSqlDatabaseLinkedService
Creación del servicio vinculado receptor de Azure Synapse Analytics
Cree un archivo JSON con el nombre AzureSqlDWLinkedService.json en la carpeta C:\ADFv2TutorialBulkCopy con el siguiente contenido:
Importante
Reemplace <servername>, <databasename>, <username>@<servername> and <password> por los nombres de su base de datos de Azure SQL antes de guardar el archivo.
{ "name": "AzureSqlDWLinkedService", "properties": { "type": "AzureSqlDW", "typeProperties": { "connectionString": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;User ID=<username>@<servername>;Password=<password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30" } } }Para crear el servicio vinculado: AzureSqlDWLinkedService, ejecute el cmdlet Set-AzDataFactoryV2LinkedService.
Set-AzDataFactoryV2LinkedService -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -Name "AzureSqlDWLinkedService" -File ".\AzureSqlDWLinkedService.json"Este es la salida de ejemplo:
LinkedServiceName : AzureSqlDWLinkedService ResourceGroupName : <resourceGroupName> DataFactoryName : <dataFactoryName> Properties : Microsoft.Azure.Management.DataFactory.Models.AzureSqlDWLinkedService
Creación del servicio vinculado de Azure Storage de almacenamiento provisional
En este tutorial, debe usar Azure Blob Storage como un área de almacenamiento provisional para habilitar PolyBase a fin de mejorar el rendimiento de copia.
Cree un archivo JSON con el nombre AzureStorageLinkedService.json en la carpeta C:\ADFv2TutorialBulkCopy con el siguiente contenido:
Importante
Reemplace <accountName> y <accountKey> por el nombre y la clave de su cuenta de Azure Storage antes de guardar el archivo.
{ "name": "AzureStorageLinkedService", "properties": { "type": "AzureStorage", "typeProperties": { "connectionString": "DefaultEndpointsProtocol=https;AccountName=<accountName>;AccountKey=<accountKey>" } } }Para crear el servicio vinculado: AzureStorageLinkedService, ejecute el cmdlet Set-AzDataFactoryV2LinkedService.
Set-AzDataFactoryV2LinkedService -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -Name "AzureStorageLinkedService" -File ".\AzureStorageLinkedService.json"Este es la salida de ejemplo:
LinkedServiceName : AzureStorageLinkedService ResourceGroupName : <resourceGroupName> DataFactoryName : <dataFactoryName> Properties : Microsoft.Azure.Management.DataFactory.Models.AzureStorageLinkedService
Creación de conjuntos de datos
En este tutorial, creará los conjuntos de datos de origen y recepción que especifican la ubicación donde se almacenan los datos:
Creación de un conjunto de datos de la instancia de SQL Database de origen
Cree un archivo JSON con el nombre AzureSqlDatabaseDataset.json en la carpeta C:\ADFv2TutorialBulkCopy con el siguiente contenido. El valor de "tableName" es ficticio dado que más tarde debe usar la consulta SQL en la actividad de copia para recuperar los datos.
{ "name": "AzureSqlDatabaseDataset", "properties": { "type": "AzureSqlTable", "linkedServiceName": { "referenceName": "AzureSqlDatabaseLinkedService", "type": "LinkedServiceReference" }, "typeProperties": { "tableName": "dummy" } } }Para crear el conjunto de datos: AzureSqlDatabaseDataset, ejecute el cmdlet Set-AzDataFactoryV2Dataset.
Set-AzDataFactoryV2Dataset -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -Name "AzureSqlDatabaseDataset" -File ".\AzureSqlDatabaseDataset.json"Este es la salida de ejemplo:
DatasetName : AzureSqlDatabaseDataset ResourceGroupName : <resourceGroupname> DataFactoryName : <dataFactoryName> Structure : Properties : Microsoft.Azure.Management.DataFactory.Models.AzureSqlTableDataset
Creación de un conjunto de datos para la instancia receptora de Azure Synapse Analytics
Cree un archivo JSON con el nombre AzureSqlDWDataset.json en la carpeta C:\ADFv2TutorialBulkCopy con el siguiente contenido: El valor de "tableName" se establece como un parámetro, más adelante la actividad de copia que hace referencia a este conjunto de datos pasa el valor real en el conjunto de datos.
{ "name": "AzureSqlDWDataset", "properties": { "type": "AzureSqlDWTable", "linkedServiceName": { "referenceName": "AzureSqlDWLinkedService", "type": "LinkedServiceReference" }, "typeProperties": { "tableName": { "value": "@{dataset().DWTableName}", "type": "Expression" } }, "parameters":{ "DWTableName":{ "type":"String" } } } }Para crear el conjunto de datos: AzureSqlDWDataset, ejecute el cmdlet Set-AzDataFactoryV2Dataset.
Set-AzDataFactoryV2Dataset -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -Name "AzureSqlDWDataset" -File ".\AzureSqlDWDataset.json"Este es la salida de ejemplo:
DatasetName : AzureSqlDWDataset ResourceGroupName : <resourceGroupname> DataFactoryName : <dataFactoryName> Structure : Properties : Microsoft.Azure.Management.DataFactory.Models.AzureSqlDwTableDataset
Creación de canalizaciones
En este tutorial, creará dos canalizaciones:
Creación de la canalización "IterateAndCopySQLTables"
Esta canalización toma la lista de tablas como un parámetro. Para cada tabla de la lista, copia los datos de la tabla de Azure SQL Database a Azure Synapse Analytics mediante la copia almacenada provisionalmente y PolyBase.
Cree un archivo JSON con el nombre IterateAndCopySQLTables.json en la carpeta C:\ADFv2TutorialBulkCopy con el siguiente contenido:
{ "name": "IterateAndCopySQLTables", "properties": { "activities": [ { "name": "IterateSQLTables", "type": "ForEach", "typeProperties": { "isSequential": "false", "items": { "value": "@pipeline().parameters.tableList", "type": "Expression" }, "activities": [ { "name": "CopyData", "description": "Copy data from Azure SQL Database to Azure Synapse Analytics", "type": "Copy", "inputs": [ { "referenceName": "AzureSqlDatabaseDataset", "type": "DatasetReference" } ], "outputs": [ { "referenceName": "AzureSqlDWDataset", "type": "DatasetReference", "parameters": { "DWTableName": "[@{item().TABLE_SCHEMA}].[@{item().TABLE_NAME}]" } } ], "typeProperties": { "source": { "type": "SqlSource", "sqlReaderQuery": "SELECT * FROM [@{item().TABLE_SCHEMA}].[@{item().TABLE_NAME}]" }, "sink": { "type": "SqlDWSink", "preCopyScript": "TRUNCATE TABLE [@{item().TABLE_SCHEMA}].[@{item().TABLE_NAME}]", "allowPolyBase": true }, "enableStaging": true, "stagingSettings": { "linkedServiceName": { "referenceName": "AzureStorageLinkedService", "type": "LinkedServiceReference" } } } } ] } } ], "parameters": { "tableList": { "type": "Object" } } } }Para crear la canalización: IterateAndCopySQLTables, ejecute el cmdlet Set-AzDataFactoryV2Pipeline.
Set-AzDataFactoryV2Pipeline -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -Name "IterateAndCopySQLTables" -File ".\IterateAndCopySQLTables.json"Este es la salida de ejemplo:
PipelineName : IterateAndCopySQLTables ResourceGroupName : <resourceGroupName> DataFactoryName : <dataFactoryName> Activities : {IterateSQLTables} Parameters : {[tableList, Microsoft.Azure.Management.DataFactory.Models.ParameterSpecification]}
Creación de la canalización "GetTableListAndTriggerCopyData"
Esta canalización lleva a cabo dos pasos:
- Busca la tabla del sistema de Azure SQL Database para obtener la lista de tablas que se copiará.
- Desencadena la canalización "IterateAndCopySQLTables" para realizar la copia de datos real.
Cree un archivo JSON con el nombre GetTableListAndTriggerCopyData.json en la carpeta C:\ADFv2TutorialBulkCopy con el siguiente contenido:
{ "name":"GetTableListAndTriggerCopyData", "properties":{ "activities":[ { "name": "LookupTableList", "description": "Retrieve the table list from Azure SQL dataabse", "type": "Lookup", "typeProperties": { "source": { "type": "SqlSource", "sqlReaderQuery": "SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES WHERE TABLE_TYPE = 'BASE TABLE' and TABLE_SCHEMA = 'SalesLT' and TABLE_NAME <> 'ProductModel'" }, "dataset": { "referenceName": "AzureSqlDatabaseDataset", "type": "DatasetReference" }, "firstRowOnly": false } }, { "name": "TriggerCopy", "type": "ExecutePipeline", "typeProperties": { "parameters": { "tableList": { "value": "@activity('LookupTableList').output.value", "type": "Expression" } }, "pipeline": { "referenceName": "IterateAndCopySQLTables", "type": "PipelineReference" }, "waitOnCompletion": true }, "dependsOn": [ { "activity": "LookupTableList", "dependencyConditions": [ "Succeeded" ] } ] } ] } }Para crear la canalización: GetTableListAndTriggerCopyData, ejecute el cmdlet Set-AzDataFactoryV2Pipeline.
Set-AzDataFactoryV2Pipeline -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -Name "GetTableListAndTriggerCopyData" -File ".\GetTableListAndTriggerCopyData.json"Este es la salida de ejemplo:
PipelineName : GetTableListAndTriggerCopyData ResourceGroupName : <resourceGroupName> DataFactoryName : <dataFactoryName> Activities : {LookupTableList, TriggerCopy} Parameters :
Inicio y supervisión de la ejecución de la canalización
Inicie una ejecución para la canalización principal "GetTableListAndTriggerCopyData" y capture el identificador de ejecución de la canalización a fin de poder realizar una supervisión en el futuro. De forma subyacente, desencadena la ejecución de la canalización "IterateAndCopySQLTables" tal como se especifica en la actividad ExecutePipeline.
$runId = Invoke-AzDataFactoryV2Pipeline -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -PipelineName 'GetTableListAndTriggerCopyData'Ejecute el siguiente script para comprobar continuamente el estado de ejecución de la canalización GetTableListAndTriggerCopyData e imprima la ejecución de la canalización final y el resultado de la ejecución de actividad.
while ($True) { $run = Get-AzDataFactoryV2PipelineRun -ResourceGroupName $resourceGroupName -DataFactoryName $DataFactoryName -PipelineRunId $runId if ($run) { if ($run.Status -ne 'InProgress') { Write-Host "Pipeline run finished. The status is: " $run.Status -ForegroundColor "Yellow" Write-Host "Pipeline run details:" -ForegroundColor "Yellow" $run break } Write-Host "Pipeline is running...status: InProgress" -ForegroundColor "Yellow" } Start-Sleep -Seconds 15 } $result = Get-AzDataFactoryV2ActivityRun -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -PipelineRunId $runId -RunStartedAfter (Get-Date).AddMinutes(-30) -RunStartedBefore (Get-Date).AddMinutes(30) Write-Host "Activity run details:" -ForegroundColor "Yellow" $resultEste es el resultado de la ejecución de ejemplo:
Pipeline run details: ResourceGroupName : <resourceGroupName> DataFactoryName : <dataFactoryName> RunId : 0000000000-00000-0000-0000-000000000000 PipelineName : GetTableListAndTriggerCopyData LastUpdated : 9/18/2017 4:08:15 PM Parameters : {} RunStart : 9/18/2017 4:06:44 PM RunEnd : 9/18/2017 4:08:15 PM DurationInMs : 90637 Status : Succeeded Message : Activity run details: ResourceGroupName : <resourceGroupName> DataFactoryName : <dataFactoryName> ActivityName : LookupTableList PipelineRunId : 0000000000-00000-0000-0000-000000000000 PipelineName : GetTableListAndTriggerCopyData Input : {source, dataset, firstRowOnly} Output : {count, value, effectiveIntegrationRuntime} LinkedServiceName : ActivityRunStart : 9/18/2017 4:06:46 PM ActivityRunEnd : 9/18/2017 4:07:09 PM DurationInMs : 22995 Status : Succeeded Error : {errorCode, message, failureType, target} ResourceGroupName : <resourceGroupName> DataFactoryName : <dataFactoryName> ActivityName : TriggerCopy PipelineRunId : 0000000000-00000-0000-0000-000000000000 PipelineName : GetTableListAndTriggerCopyData Input : {pipeline, parameters, waitOnCompletion} Output : {pipelineRunId} LinkedServiceName : ActivityRunStart : 9/18/2017 4:07:11 PM ActivityRunEnd : 9/18/2017 4:08:14 PM DurationInMs : 62581 Status : Succeeded Error : {errorCode, message, failureType, target}Puede obtener el identificador de ejecución de la canalización "IterateAndCopySQLTables" y comprobar el resultado de la ejecución de actividad detallado como se muestra a continuación.
Write-Host "Pipeline 'IterateAndCopySQLTables' run result:" -ForegroundColor "Yellow" ($result | Where-Object {$_.ActivityName -eq "TriggerCopy"}).Output.ToString()Este es el resultado de la ejecución de ejemplo:
{ "pipelineRunId": "7514d165-14bf-41fb-b5fb-789bea6c9e58" }$result2 = Get-AzDataFactoryV2ActivityRun -DataFactoryName $dataFactoryName -ResourceGroupName $resourceGroupName -PipelineRunId <copy above run ID> -RunStartedAfter (Get-Date).AddMinutes(-30) -RunStartedBefore (Get-Date).AddMinutes(30) $result2Conéctese a la instancia receptora de Azure Synapse Analytics y confirme que los datos se han copiado correctamente desde Azure SQL Database.
Contenido relacionado
En este tutorial, realizó los pasos siguientes:
- Creación de una factoría de datos.
- Creación de los servicios vinculados de Azure SQL Database, Azure Synapse Analytics y Azure Storage.
- Creación de los conjuntos de datos de Azure SQL Database y Azure Synapse Analytics.
- Cree una canalización para buscar las tablas que se deben copiar y otra canalización para realizar la operación de copia real.
- Inicio de la ejecución de una canalización.
- Supervisión de las ejecuciones de canalización y actividad.
Vaya al tutorial siguiente para obtener información sobre cómo copiar datos de forma incremental de un origen a un destino: