将数据转移到 Azure SQL 数据库以便在 Azure 机器学习中处理Move data to an Azure SQL Database for Azure Machine Learning

本文概述了用于将平面文件(CSV 或 TSV 格式)中的数据或 SQL Server 中存储的数据移到 Azure SQL 数据库的选项。This article outlines the options for moving data either from flat files (CSV or TSV formats) or from data stored in SQL Server to an Azure SQL Database. 将数据移到云中的这些任务是“团队数据科学流程”的一部分。These tasks for moving data to the cloud are part of the Team Data Science Process.

如果需要通过某个主题来大致了解将数据移到 SQL Server 以便进行机器学习的选项,请参阅将数据移到 Azure 虚拟机上的 SQL ServerFor a topic that outlines the options for moving data to SQL Server for Machine Learning, see Move data to SQL Server on an Azure virtual machine.

下表汇总了用于将数据移到 Azure SQL 数据库的选项。The following table summarizes the options for moving data to an Azure SQL Database.

SOURCE 目标:Azure SQL 数据库DESTINATION: Azure SQL Database
平面文件(CSV 或 TSV 格式)Flat file (CSV or TSV formatted) 批量插入 SQL 查询Bulk Insert SQL Query
本地 SQL ServerOn-premises SQL Server 1. 导出到平面文件1.Export to Flat File
2.SQL 数据库迁移向导2. SQL Database Migration Wizard
3.数据库备份和还原3. Database back up and restore
4.Azure 数据工厂4. Azure Data Factory

先决条件Prerequisites

此处所述的过程要求具有:The procedures outlined here require that you have:

Data:使用 NYC 出租车数据集演示迁移过程。Data: The migration processes are demonstrated using the NYC Taxi dataset. NYC 出租车数据集包含行程数据和费用的相关信息,且可用于 Azure Blob 存储:NYC 出租车数据The NYC Taxi dataset contains information on trip data and fairs and is available on Azure blob storage: NYC Taxi Data. NYC 出租车行程数据集说明中介绍了这些文件的示例和说明。A sample and description of these files are provided in NYC Taxi Trips Dataset Description.

可将此处描述的流程调整为一组自己的数据,或者通过使用 NYC 出租车数据集来按照所述的步骤进行操作。You can either adapt the procedures described here to a set of your own data or follow the steps as described by using the NYC Taxi dataset. 若要将 NYC 出租车数据集上传到 SQL Server 数据库中,请按照将数据批量导入 SQL Server 数据库中概述的过程进行操作。To upload the NYC Taxi dataset into your SQL Server database, follow the procedure outlined in Bulk Import Data into SQL Server Database.

将数据从平面文件源移动到 Azure SQL 数据库Moving data from a flat file source to an Azure SQL database

可以使用批量插入 SQL 查询将平面文件(CSV 或 TSV 格式)中的数据移动到 Azure SQL 数据库。Data in flat files (CSV or TSV formatted) can be moved to an Azure SQL database using a Bulk Insert SQL Query.

批量插入 SQL 查询Bulk Insert SQL Query

批量插入 SQL 查询使用过程的步骤类似于将数据从平面文件源移到 Azure VM 上的 SQL Server 的说明。The steps for the procedure using the Bulk Insert SQL Query are similar to the directions for moving data from a flat file source to SQL Server on an Azure VM. 有关详细信息,请参阅批量插入 SQL 查询For details, see Bulk Insert SQL Query.

将数据从 SQL Server 移到 Azure SQL 数据库Moving Data from SQL Server to an Azure SQL Database

如果源数据存储在 SQL Server 中,则有多种方式可将数据移到 Azure SQL 数据库:If the source data is stored in SQL Server, there are various possibilities for moving the data to an Azure SQL Database:

  1. 导出到平面文件Export to Flat File
  2. SQL 数据库迁移向导SQL Database Migration Wizard
  3. 数据库备份和还原Database back up and restore
  4. Azure 数据工厂Azure Data Factory

前三种方式的步骤非常类似于将数据移到 Azure 虚拟机上的 SQL Server 中涵盖相同过程的部分中所述的步骤。The steps for the first three are similar to those sections in Move data to SQL Server on an Azure virtual machine that cover these same procedures. 以下说明提供了该主题中相应部分的链接。Links to the appropriate sections in that topic are provided in the following instructions.

导出到平面文件Export to Flat File

这个导出到平面文件的操作的步骤类似于导出到平面文件中所述的这些说明。The steps for this exporting to a flat file are similar to those directions covered in Export to Flat File.

SQL 数据库迁移向导SQL Database Migration Wizard

使用 SQL 数据库迁移向导的步骤类似于 SQL 数据库迁移向导中所述的这些说明。The steps for using the SQL Database Migration Wizard are similar to those directions covered in SQL Database Migration Wizard.

数据库备份和还原Database back up and restore

使用数据库备份和还原的步骤类似于数据库备份和还原中列出的这些说明。The steps for using database backup and restore are similar to those directions listed in Database backup and restore.

Azure 数据工厂Azure Data Factory

若要了解如何使用 Azure 数据工厂 (ADF) 将数据移到 Azure SQL 数据库,请参阅此主题:使用 Azure 数据工厂将数据从 SQL Server 移到 SQL AzureLearn how to move data to an Azure SQL Database with Azure Data Factory (ADF) in this topic, Move data from a SQL Server to SQL Azure with Azure Data Factory. 本主题演示如何使用 ADF 通过 Azure Blob 存储将数据从 SQL Server 数据库移到 Azure SQL 数据库。This topic shows how to use ADF to move data from a SQL Server database to an Azure SQL Database via Azure Blob Storage.

如果需要使用本地和云混合源持续迁移数据,请考虑使用 ADF。Consider using ADF when data needs to be continually migrated with hybrid on-premises and cloud sources. 如果在迁移过程中数据需要转换或需要新的业务逻辑,ADF 也可以提供帮助。ADF also helps when the data needs transformations, or needs new business logic during migration. ADF 允许使用简单的 JSON 脚本计划和监视作业,JSON 脚本可定期管理数据移动。ADF allows for the scheduling and monitoring of jobs using simple JSON scripts that manage the movement of data on a periodic basis. ADF 还具有其他功能,例如支持复杂操作。ADF also has other capabilities such as support for complex operations.