Azure Data Factory 또는 Azure Synapse Analytics를 사용하여 Azure SQL Database에서 데이터 복사 및 변환

적용 대상: Azure Data Factory Azure Synapse Analytics

이 문서에서는 Azure Data Factory 또는 Azure Synapse 파이프라인의 복사 작업을 사용하여 Azure SQL Database 간에 데이터를 복사하고 Data Flow를 사용하여 Azure SQL Database에서 데이터를 변환하는 방법을 설명합니다. 자세한 내용은 Azure Data Factory 또는 Azure Synapse Analytics의 소개 문서를 참조하세요.

지원되는 기능

이 Azure SQL Database 커넥터는 다음 기능에 대해 지원됩니다.

① Azure 통합 런타임 ② 자체 호스팅 통합 런타임

복사 작업의 경우, 이 Azure SQL Database 커넥터는 다음 함수를 지원합니다.

• Azure 리소스에 대한 서비스 주체 또는 관리 ID와 함께 SQL 인증 및 Microsoft Entra 애플리케이션 토큰 인증을 사용하여 데이터를 복사합니다.
• SQL 쿼리 또는 저장 프로시저를 사용하여 데이터 검색(원본). Azure SQL Database 원본에서 병렬 복사를 선택할 수도 있습니다. 자세한 내용은 SQL Database에서 병렬 복사 섹션을 참조하세요.
• 싱크로서 원본 스키마에 대상 테이블이 존재하지 않는 경우 자동으로 만듭니다. 복사 시 사용자 지정 논리를 사용하여 테이블에 데이터를 추가하거나 저장된 프로시저를 호출합니다.

서버가 일시 중지될 때 Azure SQL Database 서버리스 계층을 사용하는 경우 자동 다시 시작이 준비될 때까지 기다리는 대신 작업 실행이 실패합니다. 활동 다시 시도를 추가하거나 추가 활동을 연결하여 서버가 실제 실행 시 라이브 상태를 유지할 수 있습니다.

시작하기

파이프라인에 복사 작업을 수행하려면 다음 도구 또는 SDK 중 하나를 사용하면 됩니다.

• 데이터 복사 도구
• Azure Portal
• .NET SDK
• Python SDK
• Azure PowerShell
• REST API
• Azure Resource Manager 템플릿

UI를 사용하여 Azure SQL Database 연결된 서비스 만들기

다음 단계를 사용하여 Azure Portal UI에서 Azure SQL Database 연결된 서비스를 만듭니다.

1. Azure Data Factory 또는 Synapse 작업 영역에서 관리 탭으로 이동하여 연결된 서비스를 선택하고 새로 만들기를 클릭합니다.

   • Azure Data Factory
   • Azure Synapse

   

2. SQL을 검색하고 Azure SQL Database 커넥터를 선택합니다.

   

3. 서비스 세부 정보를 구성하고, 연결을 테스트하고, 새로운 연결된 서비스를 만듭니다.

   

커넥터 구성 세부 정보

다음 섹션에서는 Azure SQL Database 커넥터와 관련된 Azure Data Factory 또는 Synapse 파이프라인 엔터티를 정의하는 데 사용되는 속성에 대해 자세히 설명합니다.

연결된 서비스 속성

Azure SQL Database 커넥터 권장 버전은 TLS 1.3을 지원합니다. 레거시 버전에서 Azure SQL Database 커넥터 버전을 업그레이드하려면 이 섹션을 참조하세요. 속성 세부 정보는 해당 섹션을 참조하세요.

• 권장 버전
• 레거시 버전

권장 버전

권장 버전을 적용하면 Azure SQL Database 연결된 서비스에 대해 다음 제네릭 속성이 지원됩니다.

추가 연결 속성은 아래 표를 참조하세요.

SQL 인증

SQL 인증을 사용하려면 앞 섹션에서 설명한 일반 속성 외에 다음 속성을 지정합니다.

예제: SQL 인증 사용

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SQL",
            "userName": "<user name>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

예제: Azure Key Vault의 암호:

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SQL",
            "userName": "<user name>",
            "password": {
                "type": "AzureKeyVaultSecret",
                "store": {
                    "referenceName": "<Azure Key Vault linked service name>",
                    "type": "LinkedServiceReference"
                },
                "secretName": "<secretName>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

예제: Always Encrypted 사용

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SQL",
            "userName": "<user name>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            },
            "alwaysEncryptedSettings": {
                "alwaysEncryptedAkvAuthType": "ServicePrincipal",
                "servicePrincipalId": "<service principal id>",
                "servicePrincipalKey": {
                    "type": "SecureString",
                    "value": "<service principal key>"
                }
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

서비스 주체 인증

서비스 주체 인증을 사용하려면 앞 섹션에서 설명한 일반 속성 외에 다음 속성을 지정합니다.

또한 아래 단계를 수행해야 합니다.

1. Azure Portal에서 Microsoft Entra 애플리케이션을 만듭니다. 애플리케이션 이름 및 연결된 서비스를 정의하는 다음 값을 적어 둡니다.

   • 애플리케이션 ID
   • 애플리케이션 키
   • 테넌트 ID

2. 아직 수행하지 않은 경우 Azure Portal에서 서버에 대해 Microsoft Entra 관리자를 프로비전합니다. Microsoft Entra 관리자는 Microsoft Entra 사용자 또는 Microsoft Entra 그룹이어야 하지만 서비스 주체는 될 수 없습니다. 이 단계는 다음 단계에서 Microsoft Entra ID를 사용하여 서비스 주체에 대해 포함된 데이터베이스 사용자를 만들 수 있도록 수행됩니다.

3. 서비스 주체에 대한 포함된 데이터베이스 사용자를 만듭니다. SQL Server Management Studio와 같은 도구를 사용하여 데이터를 복사하려는 데이터베이스에 연결합니다(최소한 ALTER ANY USER 권한이 있는 Microsoft Entra ID 사용). 다음 T-SQL을 실행합니다.

   CREATE USER [your application name] FROM EXTERNAL PROVIDER;
   

4. 일반적으로 SQL 사용자나 기타 사용자에 대해 수행하듯이 서비스 주체에 필요한 권한을 부여합니다. 다음 코드를 실행합니다. 자세한 옵션은 이 문서를 참조하세요.

   ALTER ROLE [role name] ADD MEMBER [your application name];
   

5. Azure Data Factory 또는 Synapse 작업 영역에서 Azure SQL Database 연결된 서비스를 구성합니다.

서비스 주체 인증을 사용하는 연결된 서비스 예제

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "hostNameInCertificate": "<host name>",
            "authenticationType": "ServicePrincipal",
            "servicePrincipalId": "<service principal id>",
            "servicePrincipalCredential": {
                "type": "SecureString",
                "value": "<application key>"
            },
            "tenant": "<tenant info, e.g. microsoft.onmicrosoft.com>"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

시스템이 할당한 관리 ID 인증

Azure의 다른 리소스에 인증할 때 서비스를 나타내는 Azure 리소스에 대한 시스템이 할당한 관리 ID와 데이터 팩터리 또는 Synapse 작업 영역을 연결할 수 있습니다. Azure SQL Database 인증에 관리 ID를 사용할 수 있습니다. 지정된 Factory 또는 Synapse 작업 영역은 이 ID를 사용하여 데이터베이스에 액세스하고 해당 데이터베이스 간에 데이터를 복사할 수 있습니다.

시스템이 할당한 관리 ID 인증을 사용하려면 이전 섹션에서 설명한 일반 속성을 지정하고 다음 단계를 수행합니다.

1. 아직 수행하지 않은 경우 Azure Portal에서 서버에 대해 Microsoft Entra 관리자를 프로비전합니다. Microsoft Entra 관리자는 Microsoft Entra 사용자 또는 Microsoft Entra 그룹일 수 있습니다. 관리 ID를 가진 그룹에 관리자 역할을 부여하는 경우 3단계 및 4단계를 건너뛰세요. 관리자는 데이터베이스에 대한 전체 액세스 권한을 가집니다.

2. 관리 ID에 대한 포함된 데이터베이스 사용자를 만듭니다. SQL Server Management Studio와 같은 도구를 사용하여 데이터를 복사하려는 데이터베이스에 연결합니다(최소한 ALTER ANY USER 권한이 있는 Microsoft Entra ID 사용). 다음 T-SQL을 실행합니다.

   CREATE USER [your_resource_name] FROM EXTERNAL PROVIDER;
   

3. SQL 사용자 및 다른 사용자에 대해 일반적으로 수행하는 것처럼 관리 ID에 필요한 권한을 부여합니다. 다음 코드를 실행합니다. 자세한 옵션은 이 문서를 참조하세요.

   ALTER ROLE [role name] ADD MEMBER [your_resource_name];
   

4. Azure SQL Database 연결된 서비스를 구성합니다.

예제

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "SystemAssignedManagedIdentity"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

사용자가 할당한 관리 ID 인증

Azure의 다른 리소스에 인증할 때 서비스를 나타내는 사용자가 할당한 관리 ID와 데이터 팩터리 또는 Synapse 작업 영역을 연결할 수 있습니다. Azure SQL Database 인증에 관리 ID를 사용할 수 있습니다. 지정된 Factory 또는 Synapse 작업 영역은 이 ID를 사용하여 데이터베이스에 액세스하고 해당 데이터베이스 간에 데이터를 복사할 수 있습니다.

사용자가 할당한 관리 ID를 사용하려면 앞 섹션에서 설명한 일반 속성 외에 다음 속성을 지정합니다.

또한 아래 단계를 수행해야 합니다.

1. 아직 수행하지 않은 경우 Azure Portal에서 서버에 대해 Microsoft Entra 관리자를 프로비전합니다. Microsoft Entra 관리자는 Microsoft Entra 사용자 또는 Microsoft Entra 그룹일 수 있습니다. 사용자가 할당한 관리 ID를 사용하는 그룹에 관리자 역할을 부여하는 경우 3단계를 건너뜁니다. 관리자는 데이터베이스에 대한 전체 액세스 권한을 가집니다.

2. 사용자가 할당한 관리 ID에 대한 포함된 데이터베이스 사용자를 만듭니다. SQL Server Management Studio와 같은 도구를 사용하여 데이터를 복사하려는 데이터베이스에 연결합니다(최소한 ALTER ANY USER 권한이 있는 Microsoft Entra ID 사용). 다음 T-SQL을 실행합니다.

   CREATE USER [your_resource_name] FROM EXTERNAL PROVIDER;
   

3. 하나 이상의 사용자가 할당한 관리 ID를 만들고 SQL 사용자 및 다른 사용자에 대해 일반적으로 수행하는 것처럼 사용자가 할당한 관리 ID에 필요한 권한을 부여합니다. 다음 코드를 실행합니다. 자세한 옵션은 이 문서를 참조하세요.

   ALTER ROLE [role name] ADD MEMBER [your_resource_name];
   

4. 하나 이상의 사용자가 할당한 관리 ID를 데이터 팩터리에 할당하고 각 사용자가 할당한 관리 ID에 대한 자격 증명을 만듭니다.

5. Azure SQL Database 연결된 서비스를 구성합니다.

예제

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "server": "<name or network address of the SQL server instance>",
            "database": "<database name>",
            "encrypt": "<encrypt>",
            "trustServerCertificate": false,
            "authenticationType": "UserAssignedManagedIdentity",
            "credential": {
                "referenceName": "credential1",
                "type": "CredentialReference"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

레거시 버전

레거시 버전을 적용하면 Azure SQL Database 연결된 서비스에 대해 다음 제네릭 속성이 지원됩니다.

다양한 인증 유형의 경우 특정 속성 및 필수 구성 요소에 대한 다음 섹션을 각각 참조하세요.

• 레거시 버전에 대한 SQL 인증
• 레거시 버전에 대한 서비스 주체 인증
• 레거시 버전에 대한 시스템 할당 관리 ID 인증
• 레거시 버전에 대한 사용자 할당 관리 ID 인증

레거시 버전에 대한 SQL 인증

SQL 인증을 사용하려면 이전 섹션에 설명된 제네릭 속성을 지정합니다.

레거시 버전에 대한 서비스 주체 인증

서비스 주체 인증을 사용하려면 앞 섹션에서 설명한 일반 속성 외에 다음 속성을 지정합니다.

또한 해당 권한을 부여하려면 서비스 주체 인증의 단계를 따라야 합니다.

레거시 버전에 대한 시스템 할당 관리 ID 인증

시스템 할당 관리 ID 인증을 사용하려면 시스템 할당 관리 ID 인증의 권장 버전과 동일한 단계를 따릅니다.

레거시 버전에 대한 사용자 할당 관리 ID 인증

사용자 할당 관리 ID 인증을 사용하려면 사용자 할당 관리 ID 인증의 권장 버전과 동일한 단계를 따릅니다.

데이터 세트 속성

데이터 세트 정의에 사용할 수 있는 섹션 및 속성의 전체 목록은 데이터 세트를 참조하세요.

Azure SQL Database 데이터 세트에 다음 속성이 지원됩니다.

데이터 세트 속성 예제

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

복사 작업 속성

작업 정의에 사용할 수 있는 섹션 및 속성의 전체 목록은 파이프라인을 참조하세요. 이 섹션에서는 Azure SQL Database 원본 및 싱크에서 지원하는 속성 목록을 제공합니다.

Azure SQL Database가 원본인 경우

Azure SQL Database에서 데이터를 복사하기 위해 복사 작업 source 섹션에서 지원되는 속성은 다음과 같습니다.

주의 사항:

• sqlReaderQuery가 AzureSqlSource에 지정되면 복사 작업은 데이터를 가져오는 Azure SQL Database 원본에 대해 이 쿼리를 실행합니다. 저장 프로시저가 매개 변수를 사용하는 경우에는 sqlReaderStoredProcedureName 및 storedProcedureParameters를 지정하여 저장 프로시저를 지정할 수도 있습니다.
• 원본에서 저장 프로시저를 사용하여 데이터를 검색할 때 저장 프로시저가 다른 매개 변수 값이 전달되면 다른 스키마를 반환하도록 설계된 경우, UI에서 스키마를 가져오거나 자동 테이블을 만들어 SQL 데이터베이스로 데이터를 복사하면 예기치 않은 결과가 발생할 수 있습니다.

SQL 쿼리 예제

"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>"
            }
        }
    }
]

저장 프로시저 예제

"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>"
            }
        }
    }
]

저장 프로시저 정의

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 Database가 싱크인 경우

Azure SQL Database로 데이터를 복사하기 위해 복사 작업 sink 섹션에서 지원되는 속성은 다음과 같습니다.

예제 1: 데이터 추가

"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",
                "tableOption": "autoCreate",
                "writeBatchSize": 100000
            }
        }
    }
]

예제 2: 복사 중 저장 프로시저 호출

자세한 내용은 SQL 싱크에서 저장 프로시저 호출을 참조하세요.

"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" }
                }
            }
        }
    }
]

예제 3: 데이터 Upsert

"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",
                "tableOption": "autoCreate",
                "writeBehavior": "upsert",
                "upsertSettings": {
                    "useTempDB": true,
                    "keys": [
                        "<column name>"
                    ]
                },
            }
        }
    }
]

SQL 데이터베이스에서 병렬 복사

복사 작업의 Azure SQL Database 커넥터는 데이터를 병렬로 복사하는 기본 제공 데이터 분할을 제공합니다. 복사 작업의 원본 탭에서 데이터 분할 옵션을 찾을 수 있습니다.

분할된 복사본을 사용하도록 설정하면 복사 작업이 Azure SQL Database 원본에 대해 병렬 쿼리를 실행하여 파티션별로 데이터를 로드합니다. 병렬 수준은 복사 작업의 parallelCopies 설정에 의해 제어됩니다. 예를 들어 parallelCopies를 4로 설정하는 경우, 서비스는 지정된 파티션 옵션과 설정에 따라 4개의 쿼리를 동시에 생성하고 실행하며, 각 쿼리는 Azure SQL Database에서 데이터의 일부를 검색합니다.

특히 Azure SQL Database에서 대량의 데이터를 로드하는 경우 특별히 데이터 분할로 병렬 복사를 사용하도록 설정하는 것이 좋습니다. 다양한 시나리오에 대해 권장되는 구성은 다음과 같습니다. 파일 기반 데이터 저장소에 데이터를 복사할 때 여러 파일로 폴더에 쓰는 것이 좋습니다(폴더 이름만 지정). 이 경우 단일 파일에 쓰는 것보다 성능이 좋습니다.

파티션 옵션을 사용하여 데이터를 로드하는 모범 사례:

1. 데이터 기울이기를 방지하려면 고유한 열(예: 기본 키 또는 고유 키)을 분할 열로 선택합니다.
2. 테이블에 기본 제공 파티션이 있는 경우 "테이블의 실제 파티션" 파티션 옵션을 사용하여 성능을 향상시킵니다.
3. Azure Integration Runtime을 사용하여 데이터를 복사하는 경우 더 많은 컴퓨팅 리소스를 활용할 수 있도록 더 큰 “DIU(데이터 통합 ​​단위)”(>4)를 설정할 수 있습니다. 여기서 적용 가능한 시나리오를 확인합니다.
4. "복사 병렬 처리 수준"은 파티션 수를 제어합니다. 이 수를 너무 크게 설정하면 성능이 저하되는 경우가 있습니다. 이 수를 (DIU 또는 자체 호스팅 IR 노드 수) * (2~4)로 설정하는 것이 좋습니다.

예제: 실제 파티션이 있는 대형 테이블에서 전체 로드

"source": {
    "type": "AzureSqlSource",
    "partitionOption": "PhysicalPartitionsOfTable"
}

예: 동적 범위 파티션이 있는 쿼리

"source": {
    "type": "AzureSqlSource",
    "query": "SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>",
    "partitionOption": "DynamicRange",
    "partitionSettings": {
        "partitionColumnName": "<partition_column_name>",
        "partitionUpperBound": "<upper_value_of_partition_column (optional) to decide the partition stride, not as data filter>",
        "partitionLowerBound": "<lower_value_of_partition_column (optional) to decide the partition stride, not as data filter>"
    }
}

실제 파티션을 확인하기 위한 샘플 쿼리

SELECT DISTINCT s.name AS SchemaName, t.name AS TableName, pf.name AS PartitionFunctionName, c.name AS ColumnName, iif(pf.name is null, 'no', 'yes') AS HasPartition
FROM sys.tables AS t
LEFT JOIN sys.objects AS o ON t.object_id = o.object_id
LEFT JOIN sys.schemas AS s ON o.schema_id = s.schema_id
LEFT JOIN sys.indexes AS i ON t.object_id = i.object_id 
LEFT JOIN sys.index_columns AS ic ON ic.partition_ordinal > 0 AND ic.index_id = i.index_id AND ic.object_id = t.object_id 
LEFT JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id 
LEFT JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id 
LEFT JOIN sys.partition_functions pf ON pf.function_id = ps.function_id 
WHERE s.name='[your schema]' AND t.name = '[your table name]'

실제 파티션이 테이블에 있는 경우 다음과 같이 "HasPartition"이 "yes"로 표시됩니다.

Azure SQL Database에 데이터를 로드하는 모범 사례

Azure SQL Database로 데이터를 복사하는 경우 다른 쓰기 동작이 필요할 수 있습니다.

• 추가: 내 원본 데이터에 새 레코드만 있습니다.
• Upsert: 내 원본 데이터에 삽입 및 업데이트가 모두 있습니다.
• 덮어쓰기: 매번 전체 차원 테이블을 다시 로드합니다.
• 사용자 지정 논리를 사용하여 작성: 최종적으로 대상 테이블에 삽입하기 전에 추가 처리가 필요합니다.

서비스에서 구성하는 방법 및 모범 사례는 각 섹션을 참조하세요.

데이터 추가

데이터 추가는 Azure SQL Database 싱크 커넥터의 기본 동작입니다. 서비스는 테이블에 효율적으로 쓰기 위해 일괄 삽입을 수행합니다. 복사 작업에 따라 원본 및 싱크를 구성할 수 있습니다.

데이터 Upsert

복사 작업은 이제 기본적으로 데이터베이스 임시 테이블에 데이터를 로드한 다음, 키가 있는 경우 싱크 테이블의 데이터를 업데이트하고 그렇지 않으면 새 데이터를 삽입할 수 있습니다. 복사 작업의 upsert 설정에 대한 자세한 내용은 Azure SQL Database가 싱크인 경우를 참조하세요.

전체 테이블 덮어쓰기

복사 작업 싱크에서 preCopyScript 속성을 구성할 수 있습니다. 이 경우 서비스는 실행되는 각 복사 작업에 대해 스크립트를 먼저 실행합니다. 그런 다음, 복사를 실행하여 데이터를 삽입합니다. 예를 들어 전체 테이블을 최신 데이터로 덮어쓰려면 원본에서 새 데이터를 대량으로 로드하기 전에 먼저 모든 레코드를 삭제하는 스크립트를 지정합니다.

사용자 지정 논리를 사용하여 데이터 작성

사용자 지정 논리를 사용하여 데이터를 작성하는 단계는 데이터 Upsert 섹션에서 설명하는 단계와 비슷합니다. 원본 데이터를 대상 테이블에 최종 삽입하기 전에 추가 처리를 적용해야 하는 경우, 준비 테이블에 로드한 다음 저장 프로시저 작업을 호출하거나 복사 작업 싱크에서 저장 프로시저를 호출하여 데이터를 적용하거나 매핑 데이터 흐름을 사용할 수 있습니다.

SQL 싱크에서 저장 프로시저 호출

Azure SQL Database로 데이터를 복사하는 경우, 원본 테이블의 각 일괄 처리에서 추가 매개 변수를 사용하여 사용자 지정 저장 프로시저를 구성하고 호출할 수도 있습니다. 저장 프로시저 기능은 테이블 반환 매개 변수를 활용합니다.

기본 제공 복사 메커니즘이 용도에 적합하지 않은 경우, 저장 프로시저를 사용할 수 있습니다. 예를 들어 최종적으로 원본 데이터를 대상 테이블에 삽입하기 전에 추가 처리를 적용하려는 경우입니다. 추가 처리 예시로는 열을 병합하고, 추가 값을 조회하고, 둘 이상의 테이블에 삽입하려는 경우가 있습니다.

다음 샘플에서는 저장 프로시저를 사용하여 Azure SQL Database의 테이블에 upsert(업데이트/삽입)를 수행하는 방법을 보여 줍니다. 입력 데이터와 싱크 Marketing 테이블에 각각 ProfileID, State 및 Category라는 세 개의 열이 있다고 가정합니다. ProfileID 열을 기준으로 upsert를 수행하고, “ProductA”라는 특정 범주에만 적용합니다.

1. 데이터베이스에서 테이블 형식을 sqlWriterTableType과 동일한 이름으로 정의합니다. 테이블 형식의 스키마는 입력 데이터에서 반환된 스키마와 같아야 합니다.

   CREATE TYPE [dbo].[MarketingType] AS TABLE(
       [ProfileID] [varchar](256) NOT NULL,
       [State] [varchar](256) NOT NULL,
       [Category] [varchar](256) NOT NULL
   )
   

2. 데이터베이스에서 저장 프로시저를 sqlWriterStoredProcedureName과 동일한 이름으로 정의합니다. 지정된 원본의 입력 데이터를 처리하고 출력 테이블에 병합합니다. 저장 프로시저에 있는 테이블 형식의 매개 변수 이름은 데이터 세트에 정의된 tableName과 동일합니다.

   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 또는 Synapse 파이프라인에서 다음과 같이 복사 작업의 SQL 싱크 섹션을 정의합니다.

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

저장 프로시저를 사용하여 Azure SQL Database에 데이터를 쓸 때 싱크는 원본 데이터를 미니 일괄 처리로 분할한 다음 삽입을 수행하므로 저장 프로시저의 추가 쿼리를 여러번 실행할 수 있습니다. Azure SQL Database에 데이터를 쓰기 전에 실행할 복사 작업에 대한 쿼리가 있는 경우 저장 프로시저에 추가하지 않고 사전 복사 스크립트 상자에 추가하는 것이 좋습니다.

매핑 데이터 흐름 속성

매핑 데이터 흐름에서 데이터를 변환하는 경우 Azure SQL Database에서 테이블에 대한 읽기 및 쓰기를 수행할 수 있습니다. 자세한 내용은 매핑 데이터 흐름에서 원본 변환 및 싱크 변환을 참조하세요.

원본 변환

Azure SQL Database에 특정한 설정은 원본 변환의 원본 옵션 탭에서 사용할 수 있습니다.

입력: 원본을 테이블에 표시할지 선택하거나(Select * from <table-name>과 동일) 사용자 지정 SQL 쿼리를 입력합니다.

쿼리: 입력 필드에서 쿼리를 선택하는 경우에는 원본에 대한 SQL 쿼리를 입력합니다. 이렇게 설정하면 데이터 세트에서 선택한 모든 테이블이 재정의됩니다. Order By 절은 여기서 지원되지 않지만 전체 SELECT FROM 문을 설정할 수 있습니다. 사용자 정의 테이블 함수를 사용할 수도 있습니다. select * from udfGetData()는 테이블을 반환하는 SQL의 UDF입니다. 이 쿼리는 데이터 흐름에서 사용할 수 있는 원본 테이블을 생성합니다. 쿼리를 사용하는 것은 테스트 또는 조회를 위해 행을 줄이는 좋은 방법이기도 합니다.

CREATE PROC CTESP @query nvarchar(max)
AS
BEGIN
EXECUTE sp_executesql @query;
END

저장 프로시저: 원본 데이터베이스에서 실행되는 저장 프로시저에서 프로젝션 및 원본 데이터를 생성하려면 이 옵션을 선택합니다. 스키마, 프로시저 이름 및 매개 변수를 입력하거나, 새로 고침을 클릭하여 서비스에 스키마 및 프로시저 이름을 검색할 수 있습니다. 그런 다음, 가져오기를 클릭하여 @paraName 형식을 사용하여 모든 프로시저 매개 변수를 가져올 수 있습니다.

• SQL 예제: Select * from MyTable where customerId > 1000 and customerId < 2000
• 매개 변수가 있는 SQL 예제: "select * from {$tablename} where orderyear > {$year}"

일괄 처리 크기: 일괄 처리 크기를 입력하여 대량 데이터를 읽기로 청크합니다.

격리 수준: 매핑 데이터 흐름에서 SQL 원본의 기본값은 커밋되지 않은 읽기입니다. 여기에서 격리 수준을 다음 값 중 하나로 변경할 수 있습니다.

• 커밋된 읽기
• 커밋되지 않은 읽기
• 반복 가능한 읽기
• 직렬화 가능
• 없음(격리 수준 무시)

증분 추출 사용: 이 옵션을 사용하여 파이프라인이 마지막으로 실행된 이후 변경된 행만 처리하도록 ADF에 지시합니다. 스키마 드리프트를 사용하여 증분 추출을 사용하도록 설정하려면 네이티브 변경 데이터 캡처가 사용하도록 설정된 테이블이 아닌 증분/워터마크 열을 기반으로 하는 테이블을 선택합니다.

증분 열: 증분 추출 기능을 사용하는 경우 원본 테이블에서 워터마크로 사용할 날짜/시간 또는 숫자 열을 선택해야 합니다.

네이티브 변경 데이터 캡처 사용(미리 보기): 파이프라인이 마지막으로 실행된 이후 SQL 변경 데이터 캡처 기술로 캡처된 델타 데이터만 처리하도록 ADF에 지시하려면 이 옵션을 사용합니다. 이 옵션을 사용하면 증분 열 없이 행 삽입, 업데이트 및 삭제를 포함한 델타 데이터가 자동으로 로드됩니다. ADF에서 이 옵션을 사용하기 전에 Azure SQL DB에서 변경 데이터 캡처를 사용하도록 설정해야 합니다. ADF의 이 옵션에 대한 자세한 내용은 네이티브 변경 데이터 캡처를 참조하세요.

처음부터 읽기 시작: 증분 추출을 사용하여 이 옵션을 설정하면 증분 추출이 켜진 파이프라인의 첫 번째 실행 시 모든 행을 읽도록 ADF에 지시합니다.

싱크 변환

Azure SQL Database 관련 설정은 싱크 변환의 설정 탭에서 사용할 수 있습니다.

업데이트 메서드: 데이터베이스 대상에서 허용되는 작업을 결정합니다. 기본값은 삽입만 허용하는 것입니다. 행을 업데이트, upsert 또는 삭제하려면 해당 작업을 위해 행에 태그를 지정하는 데 행 변경 변환이 필요합니다. 업데이트, upsert 및 삭제의 경우 변경할 행을 결정하기 위해 키 열을 설정해야 합니다.

여기에서 키로 선택한 열 이름은 서버에서 후속 업데이트, upsert, 삭제의 일부로 사용됩니다. 따라서 싱크 매핑에 있는 열을 선택해야 합니다. 키 열에 값을 쓰지 않으려면 “키 열 작성 건너뛰기”를 클릭합니다.

대상 Azure SQL Database 테이블을 업데이트하는 데 사용되는 키 열을 매개 변수화할 수 있습니다. 복합 키에 대한 열이 여러 개 있는 경우 "사용자 지정 식"을 클릭합니다. 그러면 복합 키에 대한 열 이름이 있는 문자열 배열을 포함할 수 있는 데이터 흐름 식 언어를 사용하여 동적 콘텐츠를 추가할 수 있습니다.

테이블 작업: 쓰기 전에 대상 테이블에서 모든 행을 다시 만들지 또는 제거할지 여부를 결정합니다.

• None: 테이블에 대한 작업이 수행되지 않습니다.
• Recreate: 테이블이 삭제되고 다시 생성됩니다. 동적으로 새 테이블을 만드는 경우 필요합니다.
• Truncate: 대상 테이블의 모든 행이 제거됩니다.

일괄 처리 크기: 각 버킷에 작성되는 행 수를 제어합니다. 일괄 처리 크기가 클수록 압축 및 메모리 최적화가 향상되지만 데이터를 캐시할 때 메모리 부족 예외가 발생할 위험이 있습니다.

TempDB 사용: 기본적으로 서비스는 전역 임시 테이블을 사용하여 로드 프로세스의 일부로 데이터를 저장합니다. 또는 "TempDB 사용" 옵션을 선택 취소하고 대신 이 싱크에 사용되는 데이터베이스에 있는 사용자 데이터베이스에 임시 보관 테이블을 저장할 것을 서비스에 요청할 수 있습니다.

사전 및 사후 SQL 스크립트: 데이터를 싱크 데이터베이스에 기록하기 전(사전 처리)과 후(사후 처리)에 실행할 여러 줄 SQL 스크립트를 입력합니다.

오류 행 처리

Azure SQL DB에 쓸 때 대상에서 설정된 제약 조건으로 인해 특정 데이터 행이 실패할 수 있습니다. 일반적인 오류는 다음과 같습니다.

• 테이블에서 문자열이나 이진 데이터는 잘립니다.
• 열에 NULL 값을 삽입할 수 없습니다.
• INSERT 문이 CHECK 제약 조건과 충돌합니다.

기본적으로 첫 번째 오류가 발생할 때 데이터 흐름 실행이 실패합니다. 개별 행에 오류가 있는 경우에도 데이터 흐름이 완료될 수 있도록 오류 발생 시 계속을 선택할 수 있습니다. 서비스는 이러한 오류 행을 처리하는 다양한 옵션을 제공합니다.

트랜잭션 커밋: 데이터를 단일 트랜잭션으로 쓸지 또는 일괄로 쓸지를 선택합니다. 단일 트랜잭션은 더 나쁜 성능을 제공하지만 트랜잭션이 완료될 때까지 기록된 데이터가 다른 사용자에게 표시되지 않습니다.

거부된 데이터 출력: 사용하도록 설정된 경우 선택한 Azure Blob Storage 또는 선택한 Azure Data Lake Storage Gen2 계정의 csv 파일에 오류 행을 출력할 수 있습니다. 이렇게 하면 세 개의 추가 열, 즉 INSERT 또는 UPDATE와 같은 SQL 작업, 데이터 흐름 오류 코드 및 오류 메시지를 포함하는 오류 행이 기록됩니다.

오류 발생 시 성공 보고: 사용하도록 설정된 경우 오류 행이 있는 경우에도 데이터 흐름이 성공으로 표시됩니다.

Azure SQL Database에 대한 데이터 형식 매핑

Azure SQL Database 간에 데이터를 복사하는 경우, Azure SQL Database 데이터 형식에서 Azure Data Factory 중간 데이터 형식으로 다음 매핑이 사용됩니다. 동일한 매핑은 Azure Data Factory를 직접 구현하는 Synapse 파이프라인 기능에서 사용됩니다. 복사 활동에서 원본 스키마와 데이터 형식을 싱크에 매핑하는 방법에 대한 자세한 내용은 스키마 및 데이터 형식 매핑을 참조하세요.

조회 작업 속성

속성에 대한 자세한 내용을 보려면 조회 작업을 확인하세요.

GetMetadata 작업 속성

속성에 대한 자세한 내용을 보려면 GetMetadata 작업을 확인하세요.

Always Encrypted 사용

Always Encrypted를 사용하여 Azure SQL Database 간에 데이터를 복사하는 경우 다음 단계를 수행합니다.

1. Azure Key Vault에 CMK(열 마스터 키)를 저장합니다. Azure Key Vault를 사용하여 Always Encrypted를 구성하는 방법을 자세히 알아보세요.

2. CMK(열 마스터 키)가 저장된 키 자격 증명 모음에 액세스할 수 있도록 합니다. 필요한 권한은 이 문서를 참조하세요.

3. 연결된 서비스를 만들어 SQL 데이터베이스에 연결하고 관리 ID 또는 서비스 주체를 사용하여 ‘Always Encrypted’ 기능을 사용하도록 설정합니다.

네이티브 변경 데이터 캡처

Azure Data Factory는 SQL Server, Azure SQL DB 및 Azure SQL MI에 대한 네이티브 변경 데이터 캡처 기능을 지원할 수 있습니다. SQL 저장소의 행 삽입, 업데이트 및 삭제를 포함하여 변경된 데이터는 ADF 매핑 데이터 흐름에 의해 자동으로 검색 및 추출될 수 있습니다. 매핑 데이터 흐름에 코드 환경이 없으므로 사용자는 데이터베이스를 대상 저장소로 추가하여 SQL 저장소에서 데이터 복제 시나리오를 쉽게 달성할 수 있습니다. 또한 사용자는 SQL 저장소에서 증분 ETL 시나리오를 달성하기 위해 중간에 데이터 변환 논리를 작성할 수도 있습니다.

마지막 실행에서 변경된 데이터를 자동으로 가져올 수 있도록 ADF에서 검사점을 기록할 수 있으려면 파이프라인과 작업 이름을 변경하지 않은 상태로 유지해야 합니다. 파이프라인 이름이나 작업 이름을 변경하면 검사점이 다시 설정되어 다음 실행에서 처음부터 시작하거나 지금부터 변경 내용을 가져올 수 있습니다. 파이프라인 이름 또는 작업 이름을 변경하지만 검사점을 계속 유지하여 마지막 실행에서 변경된 데이터를 자동으로 가져오려는 경우 데이터 흐름 작업에서 사용자 고유의 검사점 키를 사용하여 시나리오를 달성하세요.

파이프라인을 디버그할 때 이 기능은 동일하게 작동합니다. 디버그 실행 중에 브라우저를 새로 고치면 검사점이 다시 설정됩니다. 디버그 실행의 파이프라인 결과에 만족하면 파이프라인을 게시하고 트리거할 수 있습니다. 게시된 파이프라인을 처음 트리거하는 순간 처음부터 자동으로 다시 시작하거나 지금부터 변경 내용을 가져옵니다.

모니터링 섹션에서는 항상 파이프라인을 다시 실행할 수 있습니다. 이렇게 하면 변경된 데이터가 선택한 파이프라인 실행의 이전 검사점에서 항상 캡처됩니다.

예제 1:

매핑 데이터 흐름에서 데이터베이스에 참조된 싱크 변환을 사용하여 SQL CDC 지원 데이터 세트에 참조된 원본 변환을 직접 연결하면 SQL 원본에서 발생한 변경 내용이 대상 데이터베이스에 자동으로 적용되므로 데이터베이스 간에 데이터 복제 시나리오를 쉽게 가져올 수 있습니다. 싱크 변환에서 update 메서드를 사용하여 대상 데이터베이스에서 삽입 허용, 업데이트 허용 또는 삭제 허용 여부를 선택할 수 있습니다. 매핑 데이터 흐름의 예제 스크립트는 다음과 같습니다.

source(output(
		id as integer,
		name as string
	),
	allowSchemaDrift: true,
	validateSchema: false,
	enableNativeCdc: true,
	netChanges: true,
	skipInitialLoad: false,
	isolationLevel: 'READ_UNCOMMITTED',
	format: 'table') ~> source1
source1 sink(allowSchemaDrift: true,
	validateSchema: false,
	deletable:true,
	insertable:true,
	updateable:true,
	upsertable:true,
	keys:['id'],
	format: 'table',
	skipDuplicateMapInputs: true,
	skipDuplicateMapOutputs: true,
	errorHandlingOption: 'stopOnFirstError') ~> sink1

예 2:

SQL CDC를 통한 데이터베이스 간 데이터 복제 대신 ETL 시나리오를 사용하도록 설정하려는 경우 isInsert(1), isUpdate(1) 및 isDelete(1)를 비롯한 식을 매핑 데이터 흐름에서 사용하여 다양한 작업 유형으로 행을 구분할 수 있습니다. 다음은 값이 있는 하나의 열을 파생시킬 때 데이터 흐름을 매핑하는 예제 스크립트 중 하나입니다. 1은 삽입된 행을 나타내고, 2는 업데이트된 행을 나타내고 3은 델타 데이터를 처리하기 위해 다운스트림 변환에 대해 삭제된 행을 나타냅니다.

source(output(
		id as integer,
		name as string
	),
	allowSchemaDrift: true,
	validateSchema: false,
	enableNativeCdc: true,
	netChanges: true,
	skipInitialLoad: false,
	isolationLevel: 'READ_UNCOMMITTED',
	format: 'table') ~> source1
source1 derive(operationType = iif(isInsert(1), 1, iif(isUpdate(1), 2, 3))) ~> derivedColumn1
derivedColumn1 sink(allowSchemaDrift: true,
	validateSchema: false,
	skipDuplicateMapInputs: true,
	skipDuplicateMapOutputs: true) ~> sink1

알려진 제한 사항:

• SQL CDC의 순 변경 내용만 cdc.fn_cdc_get_net_changes_를 통해 ADF에서 로드됩니다.

Azure SQL Database 버전 업그레이드

Azure SQL Database 버전을 업그레이드하려면 연결된 서비스 편집 페이지의 버전에서 권장을 선택하고 권장 버전의 연결 서비스 속성을 참조하여 연결된 서비스를 구성합니다.

권장 버전과 레거시 버전의 차이점

아래 표에서는 권장 버전과 레거시 버전을 사용하는 Azure SQL Database 간의 차이점을 보여 줍니다.

관련 콘텐츠

복사 작업에서 원본 및 싱크로 지원되는 데이터 저장소 목록은 지원되는 데이터 저장소 및 형식을 참조하세요.