Azure Data Factory を使用した Azure SQL Database との間でのデータのコピーCopy data to or from Azure SQL Database by using Azure Data Factory

この記事では、Azure SQL Database をコピー先またはコピー元としてデータをコピーする方法について説明します。This article outlines how to copy data to and from Azure SQL Database. Azure Data Factory については、入門記事でをご覧ください。To learn about Azure Data Factory, read the introductory article.

サポートされる機能Supported capabilities

この Azure SQL Database コネクタは、次のアクティビティでサポートされます。This Azure SQL Database connector is supported for the following activities:

具体的には、この Azure SQL Database コネクタは以下の機能をサポートします。Specifically, this Azure SQL Database connector supports these functions:

  • SQL 認証を使って、およびサービス プリンシパルまたは Azure リソースのマネージド ID で Azure Active Directory (Azure AD) アプリケーション トークン認証を使って、データをコピーする。Copying data by using SQL authentication and Azure Active Directory (Azure AD) Application token authentication with a service principal or managed identities for Azure resources.
  • ソースとして、SQL クエリまたはストアド プロシージャを使用してデータを取得する。As a source, retrieving data by using a SQL query or a stored procedure.
  • シンクとして、宛先テーブルにデータを追記する、またはコピー中にカスタム ロジックを使用してストアド プロシージャを起動する。As a sink, appending data to a destination table or invoking a stored procedure with custom logic during the copy.

注意

Azure SQL Database の Always Encrypted は現在、このコネクタではサポートされていません。Azure SQL Database Always Encrypted isn't supported by this connector now. 回避するには、セルフホステッド統合ランタイム経由で汎用 ODBC コネクタと SQL Server ODBC ドライバーを使用できます。To work around, you can use a generic ODBC connector and a SQL Server ODBC driver via a self-hosted integration runtime. ODBC ドライバーのダウンロードおよび接続文字列の構成については、このガイダンスに従ってください。Follow this guidance with ODBC driver download and connection string configurations.

重要

Azure Data Factory 統合ランタイムを使ってデータをコピーする場合は、Azure サービスがサーバーにアクセスできるように Azure SQL Server ファイアウォールを構成します。If you copy data by using the Azure Data Factory integration runtime, configure an Azure SQL Server firewall so that Azure services can access the server. セルフホステッド統合ランタイムを使用してデータをコピーする場合は、適切な IP 範囲を許可するように Azure SQL Server ファイアウォールを構成します。If you copy data by using a self-hosted integration runtime, configure the Azure SQL Server firewall to allow the appropriate IP range. この範囲には、Azure SQL Database への接続に使用されるコンピューターの IP アドレスが含まれています。This range includes the machine's IP that's used to connect to Azure SQL Database.

作業開始Get started

次のいずれかのツールまたは SDK を使用して、パイプラインでコピー アクティビティを使用できます。You can use one of the following tools or SDKs to use the copy activity with a pipeline. 詳細な手順については、以下のリンクを選択してください。Select a link for step-by-step instructions:

以下のセクションでは、Azure SQL Database コネクタに固有の Azure Data Factory エンティティの定義に使用されるプロパティについて詳しく説明します。The following sections provide details about properties that are used to define Azure Data Factory entities specific to an Azure SQL Database connector.

リンクされたサービスのプロパティLinked service properties

Azure SQL Database のリンクされたサービスでは、次のプロパティがサポートされます。These properties are supported for an Azure SQL Database linked service:

プロパティProperty 説明Description 必須Required
typetype type プロパティを AzureSqlDatabase に設定する必要があります。The type property must be set to AzureSqlDatabase. はいYes
connectionStringconnectionString connectionString プロパティの Azure SQL データベース インスタンスに接続するために必要な情報を指定します。Specify information needed to connect to the Azure SQL Database instance for the connectionString property.
このフィールドは、Azure Data Factory で安全に格納するために SecureString としてマークします。Mark this field as SecureString to store it securely in Azure Data Factory. Azure Key Vault にパスワードまたはサービス プリンシパル キーを設定することもできます。You also can put a password or service principal key in Azure Key Vault. それが SQL 認証である場合は、接続文字列から password 構成を取得します。If it's SQL authentication, pull the password configuration out of the connection string. 詳細については、この表の後にある JSON の例および「Azure Key Vault への資格情報の格納」を参照してください。For more information, see the JSON example following the table and Store credentials in Azure Key Vault.
はいYes
servicePrincipalIdservicePrincipalId アプリケーションのクライアント ID を取得します。Specify the application's client ID. サービス プリンシパルで Azure AD 認証を使う場合は、はい。Yes, when you use Azure AD authentication with a service principal
servicePrincipalKeyservicePrincipalKey アプリケーションのキーを取得します。Specify the application's key. このフィールドを SecureString としてマークして Azure Data Factory に安全に保管するか、Azure Key Vault に格納されているシークレットを参照します。Mark this field as SecureString to store it securely in Azure Data Factory or reference a secret stored in Azure Key Vault. サービス プリンシパルで Azure AD 認証を使う場合は、はい。Yes, when you use Azure AD authentication with a service principal
tenanttenant ドメイン名やテナント ID など、アプリケーションが存在するテナントの情報を指定します。Specify the tenant information, like the domain name or tenant ID, under which your application resides. これは、Azure portal の右上隅をマウスでポイントすることで取得できます。Retrieve it by hovering the mouse in the upper-right corner of the Azure portal. サービス プリンシパルで Azure AD 認証を使う場合は、はい。Yes, when you use Azure AD authentication with a service principal
connectViaconnectVia この統合ランタイムは、データ ストアに接続するために使用されます。This integration runtime is used to connect to the data store. データ ストアがプライベート ネットワークにある場合、Azure 統合ランタイムまたはセルフホステッド統合ランタイムを使用できます。You can use the Azure integration runtime or a self-hosted integration runtime if your data store is located in a private network. 指定されていない場合は、既定の Azure 統合ランタイムが使用されます。If not specified, the default Azure integration runtime is used. いいえNo

さまざまな認証の種類の前提条件と JSON サンプルについては、以下のセクションをご覧ください。For different authentication types, refer to the following sections on prerequisites and JSON samples, respectively:

ヒント

エラー コード "UserErrorFailedToConnectToSqlServer" および "The session limit for the database is XXX and has been reached" (データベースのセッション制限 XXX に達しました) のようなメッセージのエラーが発生する場合は、Pooling=false を接続文字列に追加して、もう一度試してください。If you hit an error with the error code "UserErrorFailedToConnectToSqlServer" and a message like "The session limit for the database is XXX and has been reached," add Pooling=false to your connection string and try again.

SQL 認証SQL authentication

SQL 認証を使用するリンクされたサービスの例Linked service example that uses SQL authentication

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": {
                "type": "SecureString",
                "value": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;User ID=<username>@<servername>;Password=<password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Azure Key Vault 内のパスワードPassword in Azure Key Vault

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": {
                "type": "SecureString",
                "value": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;User ID=<username>@<servername>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30"
            },
            "password": { 
                "type": "AzureKeyVaultSecret", 
                "store": { 
                    "referenceName": "<Azure Key Vault linked service name>", 
                    "type": "LinkedServiceReference" 
                }, 
                "secretName": "<secretName>" 
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

サービス プリンシパルの認証Service principal authentication

サービス プリンシパル ベースの Azure AD アプリケーション トークン認証を使うには、以下の手順のようにします。To use a service principal-based Azure AD application token authentication, follow these steps:

  1. Azure portal から Azure Active Directory アプリケーションを作成しますCreate an Azure Active Directory application from the Azure portal. アプリケーション名と、リンクされたサービスを定義する次の値を記録しておきます。Make note of the application name and the following values that define the linked service:

    • アプリケーション IDApplication ID
    • アプリケーション キーApplication key
    • テナント IDTenant ID
  2. まだ行っていない場合は、Azure portal で Azure SQL Server の Azure Active Directory 管理者をプロビジョニングしますProvision an Azure Active Directory administrator for your Azure SQL Server on the Azure portal if you haven't already done so. Azure AD 管理者は、Azure AD ユーザーまたは Azure AD グループでなければなりませんが、サービス プリンシパルにはなれません。The Azure AD administrator must be an Azure AD user or Azure AD group, but it can't be a service principal. このステップは、次のステップで Azure AD ID を使ってサービス プリンシパルの包含データベース ユーザーを作成できるようにするために行われます。This step is done so that, in the next step, you can use an Azure AD identity to create a contained database user for the service principal.

  3. サービス プリンシパルの包含データベース ユーザーを作成しますCreate contained database users for the service principal. SQL Server Management Studio のようなツールと、少なくとも ALTER ANY USER アクセス許可を持つ Azure AD ID を使用して、データをコピーするデータベースに接続します。Connect to the database from or to which you want to copy data by using tools like SQL Server Management Studio, with an Azure AD identity that has at least ALTER ANY USER permission. 次の T-SQL を実行します。Run the following T-SQL:

    CREATE USER [your application name] FROM EXTERNAL PROVIDER;
    
  4. SQL ユーザーや他のユーザーに対する通常の方法で、サービス プリンシパルに必要なアクセス許可を付与します。Grant the service principal needed permissions as you normally do for SQL users or others. 次のコードを実行します。Run the following code. 詳細については、こちらのドキュメントを参照してください。For more options, see this document.

    EXEC sp_addrolemember [role name], [your application name];
    
  5. Azure Data Factory で、Azure SQL Database のリンクされたサービスを構成します。Configure an Azure SQL Database linked service in Azure Data Factory.

サービス プリンシパル認証を使うリンクされたサービスの例Linked service example that uses service principal authentication

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": {
                "type": "SecureString",
                "value": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;Connection Timeout=30"
            },
            "servicePrincipalId": "<service principal id>",
            "servicePrincipalKey": {
                "type": "SecureString",
                "value": "<service principal key>"
            },
            "tenant": "<tenant info, e.g. microsoft.onmicrosoft.com>"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Azure リソースのマネージド ID 認証Managed identities for Azure resources authentication

データ ファクトリは、特定のデータ ファクトリを表す Azure リソースのマネージド ID に関連付けることができます。A data factory can be associated with a managed identity for Azure resources that represents the specific data factory. このマネージド ID を Azure SQL Database の認証に使用できます。You can use this managed identity for Azure SQL Database authentication. 指定されたファクトリは、この ID を使用してデータベースにアクセスし、データを双方向にコピーできます。The designated factory can access and copy data from or to your database by using this identity.

マネージド ID 認証を使用するには、次の手順に従います。To use managed identity authentication, follow these steps.

  1. まだ行っていない場合は、Azure portal で Azure SQL Server の Azure Active Directory 管理者をプロビジョニングしますProvision an Azure Active Directory administrator for your Azure SQL Server on the Azure portal if you haven't already done so. Azure AD 管理者には、Azure AD ユーザーまたは Azure AD グループを使用できます。The Azure AD administrator can be an Azure AD user or an Azure AD group. マネージド ID を含むグループに管理者ロールを付与する場合は、手順 3. と手順 4. をスキップします。If you grant the group with managed identity an admin role, skip steps 3 and 4. 管理者はデータベースに対してフル アクセス権を持っています。The administrator has full access to the database.

  2. Azure Data Factory のマネージド ID 用に包含データベース ユーザーを作成します。Create contained database users for the Azure Data Factory managed identity. SQL Server Management Studio のようなツールと、少なくとも ALTER ANY USER アクセス許可を持つ Azure AD ID を使用して、データをコピーするデータベースに接続します。Connect to the database from or to which you want to copy data by using tools like SQL Server Management Studio, with an Azure AD identity that has at least ALTER ANY USER permission. 次の T-SQL を実行します。Run the following T-SQL:

    CREATE USER [your Data Factory name] FROM EXTERNAL PROVIDER;
    
  3. SQL ユーザーや他のユーザーに対する通常の方法と同様に、Data Factory のマネージド ID に必要なアクセス許可を付与します。Grant the Data Factory managed identity needed permissions as you normally do for SQL users and others. 次のコードを実行します。Run the following code. 詳細については、こちらのドキュメントを参照してください。For more options, see this document.

    EXEC sp_addrolemember [role name], [your Data Factory name];
    
  4. Azure Data Factory で、Azure SQL Database のリンクされたサービスを構成します。Configure an Azure SQL Database linked service in Azure Data Factory.

Example

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": {
                "type": "SecureString",
                "value": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;Connection Timeout=30"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

データセットのプロパティDataset properties

データセットの定義に使用できるセクションとプロパティの詳細な一覧については、データセットに関するページを参照してください。For a full list of sections and properties available to define datasets, see Datasets. このセクションでは、Azure SQL Database データセットでサポートされるプロパティの一覧を示します。This section provides a list of properties supported by the Azure SQL Database dataset.

Azure SQL Database をコピー元またはコピー先にしたデータ コピーについては、次のプロパティがサポートされています。To copy data from or to Azure SQL Database, the following properties are supported:

プロパティProperty 説明Description 必須Required
typetype データセットの type プロパティは、AzureSqlTable に設定する必要があります。The type property of the dataset must be set to AzureSqlTable. はいYes
tableNametableName リンクされたサービスが参照する Azure SQL データベース インスタンスのテーブルまたはビューの名前。The name of the table or view in the Azure SQL Database instance that the linked service refers to. ソースの場合はいいえ、シンクの場合ははいNo for source, Yes for sink

データセットのプロパティの例Dataset properties example

{
    "name": "AzureSQLDbDataset",
    "properties":
    {
        "type": "AzureSqlTable",
        "linkedServiceName": {
            "referenceName": "<Azure SQL Database linked service name>",
            "type": "LinkedServiceReference"
        },
        "schema": [ < physical schema, optional, retrievable during authoring > ],
        "typeProperties": {
            "tableName": "MyTable"
        }
    }
}

コピー アクティビティのプロパティCopy activity properties

アクティビティの定義に利用できるセクションとプロパティの完全な一覧については、パイプラインに関するページを参照してください。For a full list of sections and properties available for defining activities, see Pipelines. このセクションでは、Azure SQL Database のソースとシンクでサポートされるプロパティの一覧を示します。This section provides a list of properties supported by the Azure SQL Database source and sink.

ソースとしての Azure SQL DatabaseAzure SQL Database as the source

Azure SQL Database からデータをコピーするために、コピー アクティビティの source セクションでは次のプロパティがサポートされています。To copy data from Azure SQL Database, the following properties are supported in the copy activity source section:

プロパティProperty 説明Description 必須Required
typetype コピー アクティビティの source の type プロパティは AzureSqlSource に設定する必要があります。The type property of the copy activity source must be set to AzureSqlSource. "SqlSource" タイプは、現在も下位互換性のためにサポートされています。"SqlSource" type is still supported for backward compatibility. はいYes
sqlReaderQuerysqlReaderQuery このプロパティは、カスタム SQL クエリを使用してデータを読み取ります。This property uses the custom SQL query to read data. 例: select * from MyTableAn example is select * from MyTable. いいえNo
sqlReaderStoredProcedureNamesqlReaderStoredProcedureName ソース テーブルからデータを読み取るストアド プロシージャの名前。The name of the stored procedure that reads data from the source table. 最後の SQL ステートメントはストアド プロシージャの SELECT ステートメントにする必要があります。The last SQL statement must be a SELECT statement in the stored procedure. いいえNo
storedProcedureParametersstoredProcedureParameters ストアド プロシージャのパラメーター。Parameters for the stored procedure.
使用可能な値は、名前または値のペアです。Allowed values are name or value pairs. パラメーターの名前とその大文字と小文字は、ストアド プロシージャのパラメーターの名前とその大文字小文字と一致する必要があります。The names and casing of parameters must match the names and casing of the stored procedure parameters.
いいえNo

注意する点:Points to note:

  • AzureSqlSourcesqlReaderQuery が指定されている場合、コピー アクティビティでは、データを取得するために Azure SQL Database ソースに対してこのクエリを実行します。If sqlReaderQuery is specified for AzureSqlSource, the copy activity runs this query against the Azure SQL Database source to get the data. sqlReaderStoredProcedureNamestoredProcedureParameters を指定して、ストアド プロシージャを指定することもできます (ストアド プロシージャでパラメーターを使用する場合)。You also can specify a stored procedure by specifying sqlReaderStoredProcedureName and storedProcedureParameters if the stored procedure takes parameters.
  • sqlReaderQuery または sqlReaderStoredProcedureName を指定しない場合は、データセット JSON の "structure" セクションで定義されている列を使用して、クエリが作成されます。If you don't specify either sqlReaderQuery or sqlReaderStoredProcedureName, the columns defined in the "structure" section of the dataset JSON are used to construct a query. クエリ select column1, column2 from mytable は Azure SQL Database に対して実行されます。The query select column1, column2 from mytable runs against Azure SQL Database. データセット定義に "structure" がない場合は、すべての列がテーブルから選択されます。If the dataset definition doesn't have "structure," all columns are selected from the table.

SQL クエリの例SQL query example

"activities":[
    {
        "name": "CopyFromAzureSQLDatabase",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<Azure SQL Database input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "AzureSqlSource",
                "sqlReaderQuery": "SELECT * FROM MyTable"
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

ストアド プロシージャの例Stored procedure example

"activities":[
    {
        "name": "CopyFromAzureSQLDatabase",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<Azure SQL Database input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "AzureSqlSource",
                "sqlReaderStoredProcedureName": "CopyTestSrcStoredProcedureWithParameters",
                "storedProcedureParameters": {
                    "stringData": { "value": "str3" },
                    "identifier": { "value": "$$Text.Format('{0:yyyy}', <datetime parameter>)", "type": "Int"}
                }
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

ストアド プロシージャの定義Stored procedure definition

CREATE PROCEDURE CopyTestSrcStoredProcedureWithParameters
(
    @stringData varchar(20),
    @identifier int
)
AS
SET NOCOUNT ON;
BEGIN
     select *
     from dbo.UnitTestSrcTable
     where dbo.UnitTestSrcTable.stringData != stringData
    and dbo.UnitTestSrcTable.identifier != identifier
END
GO

シンクとしての Azure SQL DatabaseAzure SQL Database as the sink

ヒント

サポートされる書き込み動作、構成、およびベスト プラクティスの詳細については、「Azure SQL Database にデータを読み込む際のベスト プラクティス」を参照してください。Learn more about the supported write behaviors, configurations, and best practices from Best practice for loading data into Azure SQL Database.

データを Azure SQL Database にコピーするために、コピー アクティビティの sink セクションでは次のプロパティがサポートされています。To copy data to Azure SQL Database, the following properties are supported in the copy activity sink section:

プロパティProperty 説明Description 必須Required
typetype コピー アクティビティの sink の type プロパティは AzureSqlSink に設定する必要があります。The type property of the copy activity sink must be set to AzureSqlSink. "SqlSink" タイプは、現在も下位互換性のためにサポートされています。"SqlSink" type is still supported for backward compatibility. はいYes
writeBatchSizewriteBatchSize SQL テーブルに挿入する "バッチあたりの" 行数。Number of rows to insert into the SQL table per batch.
使用可能な値は integer (行数) です。The allowed value is integer (number of rows). 既定では、Azure Data Factory は、行のサイズに基づいて適切なバッチ サイズを動的に決定します。By default, Azure Data Factory dynamically determines the appropriate batch size based on the row size.
いいえNo
writeBatchTimeoutwriteBatchTimeout タイムアウトする前に一括挿入操作の完了を待つ時間です。The wait time for the batch insert operation to finish before it times out.
使用可能な値は timespan です。The allowed value is timespan. たとえば "00:30:00" (30 分) を指定できます。An example is “00:30:00” (30 minutes).
いいえNo
preCopyScriptpreCopyScript コピー アクティビティがデータを Azure SQL Database に書き込む前に実行する SQL クエリを指定します。Specify a SQL query for the copy activity to run before writing data into Azure SQL Database. これは、コピー実行ごとに 1 回だけ呼び出されます。It's invoked only once per copy run. 前に読み込まれたデータをクリーンアップするには、このプロパティを使います。Use this property to clean up the preloaded data. いいえNo
sqlWriterStoredProcedureNamesqlWriterStoredProcedureName ターゲット テーブルにソース データを適用する方法を定義しているストアド プロシージャの名前です。The name of the stored procedure that defines how to apply source data into a target table.
このストアド プロシージャはバッチごとに呼び出されますThis stored procedure is invoked per batch. 1 回だけ実行され、ソース データとは関係がない操作 (削除/切り詰めなど) の場合は、preCopyScript プロパティを使用します。For operations that run only once and have nothing to do with source data, for example, delete or truncate, use the preCopyScript property.
いいえNo
storedProcedureTableTypeParameterNamestoredProcedureTableTypeParameterName ストアド プロシージャで指定されたテーブル型のパラメーター名。The parameter name of the table type specified in the stored procedure. いいえNo
sqlWriterTableTypesqlWriterTableType ストアド プロシージャで使用するテーブル型の名前。The table type name to be used in the stored procedure. コピー アクティビティでは、このテーブル型の一時テーブルでデータを移動できます。The copy activity makes the data being moved available in a temp table with this table type. その後、ストアド プロシージャのコードにより、コピーされたデータを既存のデータと結合できます。Stored procedure code can then merge the data that's being copied with existing data. いいえNo
storedProcedureParametersstoredProcedureParameters ストアド プロシージャのパラメーター。Parameters for the stored procedure.
使用可能な値は、名前と値のペアです。Allowed values are name and value pairs. パラメーターの名前とその大文字と小文字は、ストアド プロシージャのパラメーターの名前とその大文字小文字と一致する必要があります。Names and casing of parameters must match the names and casing of the stored procedure parameters.
いいえNo

例 1:データを追加するExample 1: Append data

"activities":[
    {
        "name": "CopyToAzureSQLDatabase",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<Azure SQL Database output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "AzureSqlSink",
                "writeBatchSize": 100000
            }
        }
    }
]

例 2:コピー中にストアド プロシージャを呼び出すExample 2: Invoke a stored procedure during copy

詳しくは、「SQL シンクからのストアド プロシージャの呼び出し」をご覧ください。Learn more details from Invoke a stored procedure from a SQL sink.

"activities":[
    {
        "name": "CopyToAzureSQLDatabase",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<Azure SQL Database output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "AzureSqlSink",
                "sqlWriterStoredProcedureName": "CopyTestStoredProcedureWithParameters",
                "storedProcedureTableTypeParameterName": "MyTable",
                "sqlWriterTableType": "MyTableType",
                "storedProcedureParameters": {
                    "identifier": { "value": "1", "type": "Int" },
                    "stringData": { "value": "str1" }
                }
            }
        }
    }
]

Azure SQL Database にデータを読み込む際のベスト プラクティスBest practice for loading data into Azure SQL Database

Azure SQL Database にデータをコピーする場合は、さまざまな書き込み動作が必要になることがあります。When you copy data into Azure SQL Database, you might require different write behavior:

  • 追加:ソース データには新しいレコードのみが含まれている。Append: My source data has only new records.
  • アップサート: ソース データには挿入と更新の両方が含まれている。Upsert: My source data has both inserts and updates.
  • 上書き:毎回ディメンション テーブル全体を再度読み込みたい。Overwrite: I want to reload an entire dimension table each time.
  • カスタム ロジックでの書き込み:宛先テーブルへの最終挿入の前に追加の処理が必要である。Write with custom logic: I need extra processing before the final insertion into the destination table.

Azure Data Factory で構成する方法およびベスト プラクティスについては、対応するセクションを参照してください。Refer to the respective sections about how to configure in Azure Data Factory and best practices.

データを追加するAppend data

データの追加は、この Azure SQL Database シンク コネクタの既定の動作です。Appending data is the default behavior of this Azure SQL Database sink connector. Azure Data Factory は、テーブルに効率的に書き込むために一括挿入を実行します。Azure Data Factory does a bulk insert to write to your table efficiently. コピー アクティビティで、それに応じてソースとシンクを構成できます。You can configure the source and sink accordingly in the copy activity.

データをアップサートするUpsert data

オプション 1: コピーするデータが大量に存在する場合は、次のアプローチを使用してアップサートを実行します。Option 1: When you have a large amount of data to copy, use the following approach to do an upsert:

  • 最初に、データベース スコープ一時テーブルを使用して、コピー アクティビティですべてのレコードを一括で読み込みます。First, use a database scoped temporary table to bulk load all records by using the copy activity. データベース スコープ一時テーブルに対する操作はログに記録されないため、数百万のレコードを数秒で読み込むことができます。Because operations against database scoped temporary tables aren't logged, you can load millions of records in seconds.
  • Azure Data Factory でストアド プロシージャ アクティビティを実行して MERGE または INSERT/UPDATE ステートメントを適用します。Run a stored procedure activity in Azure Data Factory to apply a MERGE or INSERT/UPDATE statement. 一時テーブルをソースとして使用して、すべての更新または挿入を 1 つのトランザクションとして実行します。Use the temp table as the source to perform all updates or inserts as a single transaction. この方法により、ラウンド トリップやログ操作の数が削減されます。In this way, the number of round trips and log operations is reduced. ストアド プロシージャ アクティビティの最後に、次のアップサート サイクルの準備のために一時テーブルを切り捨てることができます。At the end of the stored procedure activity, the temp table can be truncated to be ready for the next upsert cycle.

例として、Azure Data Factory で、コピー アクティビティストアド プロシージャ アクティビティを連結させたパイプラインを作成できます。As an example, in Azure Data Factory, you can create a pipeline with a Copy activity chained with a Stored Procedure activity. 前者は、ソース ストアから Azure SQL Database 一時テーブル (たとえば、データセット内のテーブル名 ##UpsertTempTable) にデータをコピーします。The former copies data from your source store into an Azure SQL Database temporary table, for example, ##UpsertTempTable, as the table name in the dataset. 次に、後者がストアド プロシージャを呼び出して、一時テーブルのソース データをターゲット テーブルにマージし、一時テーブルをクリーンアップします。Then the latter invokes a stored procedure to merge source data from the temp table into the target table and clean up the temp table.

Upsert

データベースで、前のストアド プロシージャ アクティビティから指し示されている、次の例に示すような MERGE ロジックを含むストアド プロシージャを定義します。In your database, define a stored procedure with MERGE logic, like the following example, which is pointed to from the previous stored procedure activity. ターゲットは Marketing テーブルであり、そこには 3 つの列 (ProfileIDStateCategory) があるものとします。Assume that the target is the Marketing table with three columns: ProfileID, State, and Category. ProfileID 列に基づいて、アップサートを実行します。Do the upsert based on the ProfileID column.

CREATE PROCEDURE [dbo].[spMergeData]
AS
BEGIN
    MERGE TargetTable AS target
    USING ##UpsertTempTable AS source
    ON (target.[ProfileID] = source.[ProfileID])
    WHEN MATCHED THEN
        UPDATE SET State = source.State
    WHEN NOT matched THEN
        INSERT ([ProfileID], [State], [Category])
      VALUES (source.ProfileID, source.State, source.Category);
    
    TRUNCATE TABLE ##UpsertTempTable
END

オプション 2: コピー アクティビティ内でのストアド プロシージャの呼び出しも選択できます。Option 2: You also can choose to invoke a stored procedure within the copy activity. このアプローチでは、コピー アクティビティでの既定のアプローチとして一括挿入を使用する (これは、大規模なアップサートには適していません) 代わりに、ソース テーブル内の各行を実行します。This approach runs each row in the source table instead of using bulk insert as the default approach in the copy activity, which isn't appropriate for large-scale upsert.

テーブル全体を上書きするOverwrite the entire table

コピー アクティビティ シンクで preCopyScript プロパティを構成できます。You can configure the preCopyScript property in the copy activity sink. この場合は、実行されるコピー アクティビティごとに、Azure Data Factory は最初にスクリプトを実行します。In this case, for each copy activity that runs, Azure Data Factory runs the script first. 次に、コピーが実行されてデータが挿入されます。Then it runs the copy to insert the data. たとえば、テーブル全体を最新のデータで上書きするには、ソースから新しいデータを一括で読み込む前に、すべてのレコードを最初に削除するスクリプトを指定します。For example, to overwrite the entire table with the latest data, specify a script to first delete all the records before you bulk load the new data from the source.

カスタム ロジックでデータを書き込むWrite data with custom logic

カスタム ロジックでデータを書き込む手順は、「データをアップサートする」セクションで説明されている手順に似ています。The steps to write data with custom logic are similar to those described in the Upsert data section. 宛先テーブルへのソース データの最終挿入の前に (大規模な) 追加の処理を適用する必要がある場合は、次の 2 つのうちの 1 つを実行できます。When you need to apply extra processing before the final insertion of source data into the destination table, for large scale, you can do one of two things:

  • データベース スコープ一時テーブルに読み込んでから、ストアド プロシージャを呼び出す。Load to a database scoped temporary table and then invoke a stored procedure.
  • コピー中にストアド プロシージャを呼び出す。Invoke a stored procedure during copy.

SQL シンクからのストアド プロシージャの呼び出しInvoke a stored procedure from a SQL sink

データの Azure SQL Database へのコピー時に、ユーザーが指定したストアド プロシージャを構成し、追加のパラメーターと共に呼び出すこともできます。When you copy data into Azure SQL Database, you also can configure and invoke a user-specified stored procedure with additional parameters. ストアド プロシージャ機能は テーブル値パラメーターを利用しています。The stored procedure feature takes advantage of table-valued parameters.

ヒント

ストアド プロシージャを呼び出すと、一括操作を使用する代わりに行ごとにデータが処理されます (大規模なコピーにはお勧めできません)。Invoking a stored procedure processes the data row by row instead of by using a bulk operation, which we don't recommend for large-scale copy. 詳細については、「Azure SQL Database にデータを読み込む際のベスト プラクティス」を参照してください。Learn more from Best practice for loading data into Azure SQL Database.

組み込みのコピー メカニズムでは目的を達成できない場合は、ストアド プロシージャを使用できます。You can use a stored procedure when built-in copy mechanisms don't serve the purpose. 1 つの例は、宛先テーブルへのソース データの最終挿入の前に追加の処理を適用する場合です。An example is when you want to apply extra processing before the final insertion of source data into the destination table. その他の処理の例をいくつか挙げると、列のマージ、追加の値の検索、複数のテーブルへの挿入があります。Some extra processing examples are when you want to merge columns, look up additional values, and insert into more than one table.

次の例では、Azure SQL Database 内のテーブルに upsert を行うストアド プロシージャを使用する方法を示します。The following sample shows how to use a stored procedure to do an upsert into a table in Azure SQL Database. 入力データとシンクの Marketing テーブルには、それぞれ 3 つの列(ProfileIDStateCategory) があるものとします。Assume that the input data and the sink Marketing table each have three columns: ProfileID, State, and Category. ProfileID 列に基づいてアップサートを行い、"ProductA" という特定のカテゴリに対してのみ適用します。Do the upsert based on the ProfileID column, and only apply it for a specific category called "ProductA".

  1. データベースで、sqlWriterTableType と同じ名前のテーブル型を定義します。In your database, define the table type with the same name as sqlWriterTableType. テーブル型のスキーマは、入力データから返されるスキーマと同じです。The schema of the table type is the same as the schema returned by your input data.

    CREATE TYPE [dbo].[MarketingType] AS TABLE(
        [ProfileID] [varchar](256) NOT NULL,
        [State] [varchar](256) NOT NULL,
        [Category] [varchar](256) NOT NULL
    )
    
  2. データベース内で、SqlWriterStoredProcedureName と同じ名前のストアド プロシージャを定義します。In your database, define the stored procedure with the same name as SqlWriterStoredProcedureName. これによって指定したソースの入力データが処理され、出力テーブルにマージされます。It handles input data from your specified source and merges into the output table. ストアド プロシージャ内のテーブル型のパラメーター名は、データセットで定義されている tableName と同じです。The parameter name of the table type in the stored procedure is the same as tableName defined in the dataset.

    CREATE PROCEDURE spOverwriteMarketing @Marketing [dbo].[MarketingType] READONLY, @category varchar(256)
    AS
    BEGIN
    MERGE [dbo].[Marketing] AS target
    USING @Marketing AS source
    ON (target.ProfileID = source.ProfileID and target.Category = @category)
    WHEN MATCHED THEN
        UPDATE SET State = source.State
    WHEN NOT MATCHED THEN
        INSERT (ProfileID, State, Category)
        VALUES (source.ProfileID, source.State, source.Category);
    END
    
  3. Azure Data Factory で、コピー アクティビティの SQL シンク セクションを次のように定義します。In Azure Data Factory, define the SQL sink section in the copy activity as follows:

    "sink": {
        "type": "AzureSqlSink",
        "SqlWriterStoredProcedureName": "spOverwriteMarketing",
        "storedProcedureTableTypeParameterName": "Marketing",
        "SqlWriterTableType": "MarketingType",
        "storedProcedureParameters": {
            "category": {
                "value": "ProductA"
            }
        }
    }
    

Mapping Data Flow のプロパティMapping data flow properties

Mapping Data Flow のソース変換シンク変換に関する記事で詳細を確認してください。Learn details from source transformation and sink transformation in mapping data flow.

Azure SQL Database のデータ型のマッピングData type mapping for Azure SQL Database

Azure SQL Database をコピー元またはコピー先としてデータがコピーされると、次の Azure SQL Database のデータ型から Azure Data Factory の中間データ型へのマッピングが使用されます。When data is copied from or to Azure SQL Database, the following mappings are used from Azure SQL Database data types to Azure Data Factory interim data types. コピー アクティビティでソースのスキーマとデータ型がシンクにマッピングされるしくみについては、スキーマとデータ型のマッピングに関する記事を参照してください。To learn how the copy activity maps the source schema and data type to the sink, see Schema and data type mappings.

Azure SQL Database のデータ型Azure SQL Database data type Azure Data Factory の中間データ型Azure Data Factory interim data type
bigintbigint Int64Int64
binarybinary Byte[]Byte[]
bitbit BooleanBoolean
charchar String, Char[]String, Char[]
datedate DatetimeDateTime
DatetimeDatetime DatetimeDateTime
datetime2datetime2 DatetimeDateTime
DatetimeoffsetDatetimeoffset DateTimeOffsetDateTimeOffset
DecimalDecimal DecimalDecimal
FILESTREAM attribute (varbinary(max))FILESTREAM attribute (varbinary(max)) Byte[]Byte[]
FloatFloat DoubleDouble
imageimage Byte[]Byte[]
intint Int32Int32
moneymoney DecimalDecimal
ncharnchar String, Char[]String, Char[]
ntextntext String, Char[]String, Char[]
numericnumeric DecimalDecimal
nvarcharnvarchar String, Char[]String, Char[]
realreal SingleSingle
rowversionrowversion Byte[]Byte[]
smalldatetimesmalldatetime DatetimeDateTime
smallintsmallint Int16Int16
smallmoneysmallmoney DecimalDecimal
sql_variantsql_variant ObjectObject
texttext String, Char[]String, Char[]
timetime TimeSpanTimeSpan
timestamptimestamp Byte[]Byte[]
tinyinttinyint ByteByte
uniqueidentifieruniqueidentifier GuidGuid
varbinaryvarbinary Byte[]Byte[]
varcharvarchar String, Char[]String, Char[]
xmlxml xmlXml

注意

10 進の中間型にマップされるデータ型の場合、現在 Azure Data Factory では最大 28 の有効桁数をサポートしています。For data types that map to the Decimal interim type, currently Azure Data Factory supports precision up to 28. 28 よりも大きな有効桁数のデータがある場合は、SQL クエリで文字列に変換することを検討してください。If you have data with precision larger than 28, consider converting to a string in SQL query.

次の手順Next steps

Azure Data Factory のコピー アクティビティによってソースおよびシンクとしてサポートされるデータ ストアの一覧については、サポートされるデータ ストアと形式の記事を参照してください。For a list of data stores supported as sources and sinks by the copy activity in Azure Data Factory, see Supported data stores and formats.