Kopiowanie i przekształcanie danych w usłudze Azure SQL Database przy użyciu usługi Azure Data Factory lub Azure Synapse Analytics

Artykuł
01/05/2024

DOTYCZY: Azure Data Factory Azure Synapse Analytics

Napiwek

Wypróbuj usługę Data Factory w usłudze Microsoft Fabric — rozwiązanie analityczne typu all-in-one dla przedsiębiorstw. Usługa Microsoft Fabric obejmuje wszystko, od przenoszenia danych do nauki o danych, analizy w czasie rzeczywistym, analizy biznesowej i raportowania. Dowiedz się, jak bezpłatnie rozpocząć nową wersję próbną !

W tym artykule opisano sposób używania działania kopiowania w potokach usługi Azure Data Factory lub Azure Synapse do kopiowania danych z i do usługi Azure SQL Database oraz używania Przepływ danych do przekształcania danych w usłudze Azure SQL Database. Aby dowiedzieć się więcej, przeczytaj artykuł wprowadzający dotyczący usługi Azure Data Factory lub Azure Synapse Analytics.

Obsługiwane możliwości

Ten łącznik usługi Azure SQL Database jest obsługiwany w następujących funkcjach:

Obsługiwane możliwości	IR	Zarządzany prywatny punkt końcowy
działanie Kopiuj (źródło/ujście)	(1) (2)	✓
Przepływ danych mapowania (źródło/ujście)	(1)	✓
Działanie Lookup	(1) (2)	✓
Działanie GetMetadata	(1) (2)	✓
Działanie skryptu	(1) (2)	✓
Działanie procedury składowanej	(1) (2)	✓

(1) Środowisko Azure Integration Runtime (2) Self-hosted Integration Runtime

W przypadku działanie Kopiuj ten łącznik usługi Azure SQL Database obsługuje następujące funkcje:

Kopiowanie danych przy użyciu uwierzytelniania SQL i uwierzytelniania tokenu aplikacji Entra firmy Microsoft przy użyciu jednostki usługi lub tożsamości zarządzanych dla zasobów platformy Azure.
Jako źródło pobieranie danych przy użyciu zapytania SQL lub procedury składowanej. Możesz również wybrać równoległe kopiowanie ze źródła usługi Azure SQL Database. Aby uzyskać szczegółowe informacje, zobacz sekcję Równoległa kopia z bazy danych SQL.
Jako ujście automatycznie tworzy tabelę docelową, jeśli nie istnieje na podstawie schematu źródłowego; dołączanie danych do tabeli lub wywoływanie procedury składowanej za pomocą logiki niestandardowej podczas kopiowania.

Jeśli używasz warstwy bezserwerowej usługi Azure SQL Database, pamiętaj, że po wstrzymaniu serwera uruchomienie działania kończy się niepowodzeniem zamiast czekać na gotowość automatycznego wznawiania. Możesz dodać ponawianie próby działania lub łańcuch dodatkowych działań, aby upewnić się, że serwer jest na żywo podczas rzeczywistego wykonywania.

Ważne

Jeśli skopiujesz dane przy użyciu środowiska Azure Integration Runtime, skonfiguruj regułę zapory na poziomie serwera, aby usługi platformy Azure mogły uzyskiwać dostęp do serwera. Jeśli skopiujesz dane przy użyciu własnego środowiska Integration Runtime, skonfiguruj zaporę tak, aby zezwalała na odpowiedni zakres adresów IP. Ten zakres obejmuje adres IP maszyny używany do nawiązywania połączenia z usługą Azure SQL Database.

Rozpocznij

Aby wykonać działanie Kopiuj za pomocą potoku, możesz użyć jednego z następujących narzędzi lub zestawów SDK:

Tworzenie połączonej usługi Azure SQL Database przy użyciu interfejsu użytkownika

Wykonaj poniższe kroki, aby utworzyć połączoną usługę Azure SQL Database w interfejsie użytkownika witryny Azure Portal.

Przejdź do karty Zarządzanie w obszarze roboczym usługi Azure Data Factory lub Synapse i wybierz pozycję Połączone usługi, a następnie kliknij pozycję Nowy:
- Azure Data Factory
- Azure Synapse
Wyszukaj ciąg SQL i wybierz łącznik usługi Azure SQL Database.
Skonfiguruj szczegóły usługi, przetestuj połączenie i utwórz nową połączoną usługę.

szczegóły konfiguracji Połączenie or

Poniższe sekcje zawierają szczegółowe informacje o właściwościach używanych do definiowania jednostek potoku usługi Azure Data Factory lub Synapse specyficznych dla łącznika usługi Azure SQL Database.

Właściwości połączonej usługi

Te właściwości ogólne są obsługiwane w przypadku połączonej usługi Azure SQL Database:

Właściwości	Opis	Wymagania
type	Właściwość type musi być ustawiona na Wartość AzureSqlDatabase.	Tak
Parametry połączenia	Określ informacje potrzebne do nawiązania połączenia z wystąpieniem usługi Azure SQL Database dla właściwości connectionString . Możesz również umieścić hasło lub klucz jednostki usługi w usłudze Azure Key Vault. Jeśli jest to uwierzytelnianie SQL, pobierz konfigurację `password` z parametry połączenia. Aby uzyskać więcej informacji, zobacz przykład JSON poniżej tabeli i Przechowywanie poświadczeń w usłudze Azure Key Vault.	Tak
azureCloudType	W przypadku uwierzytelniania jednostki usługi określ typ środowiska chmury platformy Azure, do którego zarejestrowano aplikację Firmy Microsoft Entra. Dozwolone wartości to AzurePublic, AzureChina, AzureUsGovernment i AzureGermany. Domyślnie jest używane środowisko chmury potoku usługi Synapse lub fabryki danych.	Nie.
alwaysEncrypted Ustawienia	Określ zawszeszyfrowanesettings informacje potrzebne do włączenia funkcji Always Encrypted w celu ochrony poufnych danych przechowywanych na serwerze SQL przy użyciu tożsamości zarządzanej lub jednostki usługi. Aby uzyskać więcej informacji, zobacz przykład JSON w poniższej tabeli i sekcję Using Always Encrypted (Używanie funkcji Always Encrypted ). Jeśli nie zostanie określone, domyślne ustawienie zawsze szyfrowane jest wyłączone.	Nie.
connectVia	To środowisko Integration Runtime służy do nawiązywania połączenia z magazynem danych. Możesz użyć środowiska Azure Integration Runtime lub własnego środowiska Integration Runtime, jeśli magazyn danych znajduje się w sieci prywatnej. Jeśli nie zostanie określony, zostanie użyte domyślne środowisko Azure Integration Runtime.	Nie.

W przypadku różnych typów uwierzytelniania zapoznaj się z następującymi sekcjami dotyczącymi określonych właściwości, wymagań wstępnych i przykładów JSON, odpowiednio:

Uwierzytelnianie SQL
Uwierzytelnianie jednostki usługi
Uwierzytelnianie tożsamości zarządzanej przypisanej przez system
Uwierzytelnianie tożsamości zarządzanej przypisanej przez użytkownika

Napiwek

Jeśli wystąpi błąd z kodem błędu "UserErrorFailedTo Połączenie ToSqlServer", a komunikat taki jak "Limit sesji dla bazy danych to XXX i został osiągnięty", dodaj Pooling=false go do parametry połączenia i spróbuj ponownie. Pooling=falseJest również zalecana w przypadku konfiguracji połączonej usługi SHIR(Self Hosted Integration Runtime). Buforowanie i inne parametry połączenia można dodać jako nowe nazwy parametrów i wartości w sekcji Dodatkowe właściwości połączenia połączonego formularza tworzenia usługi.

Uwierzytelnianie SQL

Aby użyć typu uwierzytelniania SQL, określ właściwości ogólne opisane w poprzedniej sekcji.

Przykład: używanie uwierzytelniania SQL

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

Przykład: hasło w usłudze Azure Key Vault

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": "Data Source=tcp:<servername>.database.windows.net,1433;Initial Catalog=<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"
        }
    }
}

Przykład: Używanie funkcji Always Encrypted

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": "Data Source=tcp:<servername>.database.windows.net,1433;Initial Catalog=<databasename>;User ID=<username>@<servername>;Password=<password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30"
        },
        "alwaysEncryptedSettings": {
            "alwaysEncryptedAkvAuthType": "ServicePrincipal",
            "servicePrincipalId": "<service principal id>",
            "servicePrincipalKey": {
                "type": "SecureString",
                "value": "<service principal key>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Uwierzytelnianie nazwy głównej usługi

Aby użyć uwierzytelniania jednostki usługi, oprócz właściwości ogólnych opisanych w poprzedniej sekcji, określ następujące właściwości:

Właściwości	Opis	Wymagania
servicePrincipalId	Określ identyfikator klienta aplikacji.	Tak
servicePrincipalKey	Określ klucz aplikacji. Oznacz to pole jako SecureString , aby bezpiecznie przechowywać je lub odwoływać się do wpisu tajnego przechowywanego w usłudze Azure Key Vault.	Tak
tenant	Określ informacje o dzierżawie, takie jak nazwa domeny lub identyfikator dzierżawy, w którym znajduje się aplikacja. Pobierz go, umieszczając wskaźnik myszy w prawym górnym rogu witryny Azure Portal.	Tak

Należy również wykonać poniższe kroki:

Utwórz aplikację Firmy Microsoft Entra w witrynie Azure Portal. Zanotuj nazwę aplikacji i następujące wartości, które definiują połączoną usługę:
- Application ID
- Klucz aplikacji
- Identyfikator dzierżawy
Aprowizuj administratora firmy Microsoft Entra dla serwera w witrynie Azure Portal, jeśli jeszcze tego nie zrobiono. Administrator firmy Microsoft Entra musi być użytkownikiem entra firmy Microsoft lub grupą Microsoft Entra, ale nie może być jednostką usługi. Ten krok jest wykonywany w celu utworzenia użytkownika zawartej bazy danych dla jednostki usługi przy użyciu tożsamości entra firmy Microsoft.
Utwórz użytkowników zawartej bazy danych dla jednostki usługi. Połączenie do bazy danych z programu lub do której chcesz skopiować dane przy użyciu narzędzi, takich jak SQL Server Management Studio, z tożsamością firmy Microsoft Entra, która ma co najmniej uprawnienie ALTER ANY USER. Uruchom następujący kod T-SQL:
```
CREATE USER [your application name] FROM EXTERNAL PROVIDER;
```
Przyznaj jednostce usługi wymagane uprawnienia, tak jak zwykle w przypadku użytkowników SQL lub innych użytkowników. Uruchom następujący kod. Aby uzyskać więcej opcji, zobacz ten dokument.
```
ALTER ROLE [role name] ADD MEMBER [your application name];
```
Konfigurowanie połączonej usługi Azure SQL Database w obszarze roboczym usługi Azure Data Factory lub Synapse.

Przykład połączonej usługi korzystającej z uwierzytelniania jednostki usługi

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": "Data Source=tcp:<servername>.database.windows.net,1433;Initial Catalog=<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"
        }
    }
}

Uwierzytelnianie tożsamości zarządzanej przypisanej przez system

Obszar roboczy fabryki danych lub usługi Synapse może być skojarzony z tożsamością zarządzaną przypisaną przez system dla zasobów platformy Azure reprezentujących usługę podczas uwierzytelniania w innych zasobach na platformie Azure. Tej tożsamości zarządzanej można użyć do uwierzytelniania usługi Azure SQL Database. Wyznaczona fabryka lub obszar roboczy usługi Synapse mogą uzyskiwać dostęp do danych lub do bazy danych oraz kopiować je przy użyciu tej tożsamości.

Aby użyć uwierzytelniania tożsamości zarządzanej przypisanej przez system, określ właściwości ogólne opisane w poprzedniej sekcji i wykonaj następujące kroki.

Aprowizuj administratora firmy Microsoft Entra dla serwera w witrynie Azure Portal, jeśli jeszcze tego nie zrobiono. Administrator firmy Microsoft Entra może być użytkownikiem entra firmy Microsoft lub grupą Entra firmy Microsoft. Jeśli przyznasz grupie rolę administratora tożsamości zarządzanej, pomiń kroki 3 i 4. Administrator ma pełny dostęp do bazy danych.
Utwórz użytkowników zawartej bazy danych dla tożsamości zarządzanej. Połączenie do bazy danych z programu lub do której chcesz skopiować dane przy użyciu narzędzi, takich jak SQL Server Management Studio, z tożsamością firmy Microsoft Entra, która ma co najmniej uprawnienie ALTER ANY USER. Uruchom następujący kod T-SQL:
```
CREATE USER [your_resource_name] FROM EXTERNAL PROVIDER;
```
Udziel tożsamości zarządzanej wymaganych uprawnień, tak jak zwykle dla użytkowników SQL i innych użytkowników. Uruchom następujący kod. Aby uzyskać więcej opcji, zobacz ten dokument.
```
ALTER ROLE [role name] ADD MEMBER [your_resource_name];
```
Konfigurowanie połączonej usługi Azure SQL Database.

Przykład

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

Uwierzytelnianie tożsamości zarządzanej przypisanej przez użytkownika

Obszar roboczy fabryki danych lub usługi Synapse może być skojarzony z tożsamościami zarządzanymi przypisanymi przez użytkownika, które reprezentują usługę podczas uwierzytelniania w innych zasobach na platformie Azure. Tej tożsamości zarządzanej można użyć do uwierzytelniania usługi Azure SQL Database. Wyznaczona fabryka lub obszar roboczy usługi Synapse mogą uzyskiwać dostęp do danych lub do bazy danych oraz kopiować je przy użyciu tej tożsamości.

Aby użyć uwierzytelniania tożsamości zarządzanej przypisanej przez użytkownika, oprócz właściwości ogólnych opisanych w poprzedniej sekcji, określ następujące właściwości:

Właściwości	Opis	Wymagania
poświadczenia	Określ tożsamość zarządzaną przypisaną przez użytkownika jako obiekt poświadczeń.	Tak

Należy również wykonać poniższe kroki:

Aprowizuj administratora firmy Microsoft Entra dla serwera w witrynie Azure Portal, jeśli jeszcze tego nie zrobiono. Administrator firmy Microsoft Entra może być użytkownikiem entra firmy Microsoft lub grupą Entra firmy Microsoft. Jeśli przyznasz grupie tożsamość zarządzaną przypisaną przez użytkownika rolę administratora, pomiń kroki 3. Administrator ma pełny dostęp do bazy danych.
Utwórz użytkowników zawartej bazy danych dla tożsamości zarządzanej przypisanej przez użytkownika. Połączenie do bazy danych z programu lub do której chcesz skopiować dane przy użyciu narzędzi, takich jak SQL Server Management Studio, z tożsamością firmy Microsoft Entra, która ma co najmniej uprawnienie ALTER ANY USER. Uruchom następujący kod T-SQL:
```
CREATE USER [your_resource_name] FROM EXTERNAL PROVIDER;
```
Utwórz jedną lub wiele tożsamości zarządzanych przypisanych przez użytkownika i przyznaj tożsamości zarządzanej przypisanej przez użytkownika wymagane uprawnienia, tak jak zwykle dla użytkowników SQL i innych. Uruchom następujący kod. Aby uzyskać więcej opcji, zobacz ten dokument.
```
ALTER ROLE [role name] ADD MEMBER [your_resource_name];
```
Przypisz jedną lub wiele tożsamości zarządzanych przypisanych przez użytkownika do fabryki danych i utwórz poświadczenia dla każdej tożsamości zarządzanej przypisanej przez użytkownika.
Konfigurowanie połączonej usługi Azure SQL Database.

Przykład:

{
    "name": "AzureSqlDbLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": "Data Source=tcp:<servername>.database.windows.net,1433;Initial Catalog=<databasename>;Connection Timeout=30",
            "credential": {
                "referenceName": "credential1",
                "type": "CredentialReference"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Właściwości zestawu danych

Aby uzyskać pełną listę sekcji i właściwości dostępnych do definiowania zestawów danych, zobacz Zestawy danych.

Następujące właściwości są obsługiwane w przypadku zestawu danych usługi Azure SQL Database:

Właściwości	Opis	Wymagania
type	Właściwość type zestawu danych musi być ustawiona na AzureSqlTable.	Tak
schema	Nazwa schematu.	Nie dla źródła, Tak dla ujścia
table	Nazwa tabeli/widoku.	Nie dla źródła, Tak dla ujścia
tableName	Nazwa tabeli/widoku ze schematem. Ta właściwość jest obsługiwana w celu zapewnienia zgodności z poprzednimi wersjami. W przypadku nowego obciążenia użyj polecenia `schema` i `table`.	Nie dla źródła, Tak dla ujścia

Przykład właściwości zestawu danych

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

Właściwości działania kopiowania

Aby uzyskać pełną listę sekcji i właściwości dostępnych do definiowania działań, zobacz Pipelines (Potoki). Ta sekcja zawiera listę właściwości obsługiwanych przez źródło i ujście usługi Azure SQL Database.

Usługa Azure SQL Database jako źródło

Napiwek

Aby wydajnie ładować dane z usługi Azure SQL Database przy użyciu partycjonowania danych, dowiedz się więcej na temat kopiowania równoległego z bazy danych SQL.

Aby skopiować dane z usługi Azure SQL Database, w sekcji źródła działania kopiowania są obsługiwane następujące właściwości:

Właściwości	Opis	Wymagania
type	Właściwość type źródła działania kopiowania musi być ustawiona na Wartość AzureSqlSource. Typ "SqlSource" jest nadal obsługiwany w celu zapewnienia zgodności z poprzednimi wersjami.	Tak
sqlReaderQuery	Ta właściwość używa niestandardowego zapytania SQL do odczytywania danych. Może to być na przykład `select * from MyTable`.	Nie.
sqlReaderStoredProcedureName	Nazwa procedury składowanej, która odczytuje dane z tabeli źródłowej. Ostatnia instrukcja SQL musi być instrukcją SELECT w procedurze składowanej.	Nie.
storedProcedureParameters	Parametry procedury składowanej. Dozwolone wartości to pary nazw lub wartości. Nazwy i wielkość liter parametrów muszą być zgodne z nazwami i wielkością parametrów procedury składowanej.	Nie.
Isolationlevel	Określa zachowanie blokowania transakcji dla źródła SQL. Dozwolone wartości to: ReadCommitted, ReadUncommitted, RepeatableRead, Serializable, Snapshot. Jeśli nie zostanie określony, zostanie użyty domyślny poziom izolacji bazy danych. Aby uzyskać więcej informacji, zapoznaj się z tym dokumentem .	Nie.
partitionOptions	Określa opcje partycjonowania danych używane do ładowania danych z usługi Azure SQL Database. Dozwolone wartości to: Brak (wartość domyślna), PhysicalPartitionsOfTable i DynamicRange. Jeśli opcja partycji jest włączona (czyli nie `None`), stopień równoległości równoczesnego ładowania danych z usługi Azure SQL Database jest kontrolowany przez `parallelCopies` ustawienie działania kopiowania.	Nie.
partycja Ustawienia	Określ grupę ustawień partycjonowania danych. Zastosuj, gdy opcja partycji nie `None`jest .	Nie.
W obszarze `partitionSettings`:
partitionColumnName	Określ nazwę kolumny źródłowej w liczbą całkowitą lub typu daty/daty/godziny (`int`, `smallint`, `smalldatetimedatedatetimebigintdatetime2`lub `datetimeoffset`), która będzie używana przez partycjonowanie zakresu na potrzeby kopiowania równoległego. Jeśli nie zostanie określony, indeks lub klucz podstawowy tabeli jest automatycznie wykrywany i używany jako kolumna partycji. Zastosuj, gdy opcja partycji to `DynamicRange`. Jeśli używasz zapytania do pobierania danych źródłowych, należy podłączyć `?AdfDynamicRangePartitionCondition` się do klauzuli WHERE. Aby zapoznać się z przykładem, zobacz sekcję Kopiowanie równoległe z bazy danych SQL.	Nie.
partitionUpperBound	Maksymalna wartość kolumny partycji dla podziału zakresu partycji. Ta wartość służy do decydowania o kroku partycji, a nie do filtrowania wierszy w tabeli. Wszystkie wiersze w tabeli lub wyniku zapytania zostaną podzielone na partycje i skopiowane. Jeśli nie zostanie określony, działanie kopiowania automatycznie wykrywa wartość. Zastosuj, gdy opcja partycji to `DynamicRange`. Aby zapoznać się z przykładem, zobacz sekcję Kopiowanie równoległe z bazy danych SQL.	Nie.
partitionLowerBound	Minimalna wartość kolumny partycji dla podziału zakresu partycji. Ta wartość służy do decydowania o kroku partycji, a nie do filtrowania wierszy w tabeli. Wszystkie wiersze w tabeli lub wyniku zapytania zostaną podzielone na partycje i skopiowane. Jeśli nie zostanie określony, działanie kopiowania automatycznie wykrywa wartość. Zastosuj, gdy opcja partycji to `DynamicRange`. Aby zapoznać się z przykładem, zobacz sekcję Kopiowanie równoległe z bazy danych SQL.	Nie.

Pamiętaj o następujących kwestiach:

Jeśli dla usługi AzureSqlSource określono wartość sqlReaderQuery, działanie kopiowania uruchamia to zapytanie względem źródła usługi Azure SQL Database w celu pobrania danych. Można również określić procedurę składowaną, określając parametry sqlReaderStoredProcedureName i storedProcedureParameters .
W przypadku używania procedury składowanej w źródle do pobierania danych należy pamiętać, że procedura składowana jest zaprojektowana jako zwracanie innego schematu po przekazaniu innej wartości parametru, może wystąpić błąd lub nieoczekiwany wynik podczas importowania schematu z interfejsu użytkownika lub kopiowania danych do bazy danych SQL z automatycznym tworzeniem tabeli.

Przykład zapytania 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>"
            }
        }
    }
]

Przykład procedury składowanej

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

Definicja procedury składowanej

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

Usługa Azure SQL Database jako ujście

Napiwek

Dowiedz się więcej o obsługiwanych zachowaniach zapisu, konfiguracjach i najlepszych rozwiązaniach z artykułu Najlepsze rozwiązanie dotyczące ładowania danych do usługi Azure SQL Database.

Aby skopiować dane do usługi Azure SQL Database, w sekcji ujścia działania kopiowania są obsługiwane następujące właściwości:

Właściwości	Opis	Wymagania
type	Właściwość type ujścia działania kopiowania musi być ustawiona na AzureSqlSink. Typ "SqlSink" jest nadal obsługiwany w celu zapewnienia zgodności z poprzednimi wersjami.	Tak
preCopyScript	Określ zapytanie SQL dla działania kopiowania, które ma zostać uruchomione przed zapisaniem danych w usłudze Azure SQL Database. Jest wywoływany tylko raz na przebieg kopiowania. Użyj tej właściwości, aby wyczyścić wstępnie załadowane dane.	Nie.
tableOption	Określa, czy automatycznie utworzyć tabelę ujścia, jeśli nie istnieje na podstawie schematu źródłowego. Automatyczne tworzenie tabeli nie jest obsługiwane, gdy ujście określa procedurę składowaną. Dozwolone wartości to: `none` (wartość domyślna), `autoCreate`.	Nie.
sqlWriterStoredProcedureName	Nazwa procedury składowanej, która definiuje sposób stosowania danych źródłowych do tabeli docelowej. Ta procedura składowana jest wywoływana na partię. W przypadku operacji uruchamianych tylko raz i nie ma nic wspólnego z danymi źródłowymi, na przykład usuń lub obcinaj, użyj `preCopyScript` właściwości . Zobacz przykład z wywołania procedury składowanej z ujścia SQL.	Nie.
storedProcedureTableTypeParameterName	Nazwa parametru typu tabeli określona w procedurze składowanej.	Nie.
sqlWriterTableType	Nazwa typu tabeli, która ma być używana w procedurze składowanej. Działanie kopiowania sprawia, że dane są przenoszone w tabeli tymczasowej z tym typem tabeli. Kod procedury składowanej może następnie scalić dane kopiowane z istniejącymi danymi.	Nie.
storedProcedureParameters	Parametry procedury składowanej. Dozwolone wartości to pary nazw i wartości. Nazwy i wielkość liter parametrów muszą być zgodne z nazwami i wielkością parametrów procedury składowanej.	Nie.
writeBatchSize	Liczba wierszy do wstawienia do tabeli SQL na partię. Dozwolona wartość to liczba całkowita (liczba wierszy). Domyślnie usługa dynamicznie określa odpowiedni rozmiar partii na podstawie rozmiaru wiersza.	Nie.
writeBatchTimeout	Czas oczekiwania na ukończenie operacji wstawiania, operacji upsert i procedury składowanej przed przekroczeniem limitu czasu. Dozwolone wartości są dla przedziału czasu. Przykładem jest "00:30:00" przez 30 minut. Jeśli żadna wartość nie jest określona, limit czasu jest domyślnie ustawiona na "00:30:00".	Nie.
disableMetricsCollection	Usługa zbiera metryki, takie jak jednostki DTU usługi Azure SQL Database na potrzeby optymalizacji wydajności kopiowania i rekomendacji, co wprowadza dodatkowy dostęp do głównej bazy danych. Jeśli interesuje Cię to zachowanie, określ `true` , aby go wyłączyć.	Nie (wartość domyślna to `false`)
maxConcurrent Połączenie ions	Górny limit połączeń współbieżnych ustanowionych z magazynem danych podczas uruchamiania działania. Określ wartość tylko wtedy, gdy chcesz ograniczyć połączenia współbieżne.	Nr
WriteBehavior	Określ zachowanie zapisu dla działania kopiowania w celu załadowania danych do usługi Azure SQL Database. Dozwolona wartość to Insert i Upsert. Domyślnie usługa używa wstawiania do ładowania danych.	Nie.
upsert Ustawienia	Określ grupę ustawień zachowania zapisu. Zastosuj, gdy opcja WriteBehavior ma wartość `Upsert`.	Nie.
W obszarze `upsertSettings`:
useTempDB	Określ, czy chcesz użyć globalnej tabeli tymczasowej, czy tabeli fizycznej jako tabeli tymczasowej dla operacji upsert. Domyślnie usługa używa globalnej tabeli tymczasowej jako tabeli tymczasowej. wartość to `true`.	Nie.
interimSchemaName	Określ schemat tymczasowy do tworzenia tabeli tymczasowej, jeśli jest używana tabela fizyczna. Uwaga: użytkownik musi mieć uprawnienia do tworzenia i usuwania tabeli. Domyślnie tabela tymczasowa będzie współdzielić ten sam schemat co tabela ujścia. Zastosuj, gdy opcja useTempDB to `False`.	Nie.
keys	Określ nazwy kolumn dla unikatowej identyfikacji wierszy. Można użyć pojedynczego klucza lub serii kluczy. Jeśli nie zostanie określony, używany jest klucz podstawowy.	Nie.

Przykład 1. Dołączanie danych

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

Przykład 2: Wywoływanie procedury składowanej podczas kopiowania

Dowiedz się więcej na stronie Wywoływanie procedury składowanej z ujścia 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" }
                }
            }
        }
    }
]

Przykład 3. Dane 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>"
                    ]
                },
            }
        }
    }
]

Równoległa kopia z bazy danych SQL

Łącznik usługi Azure SQL Database w działaniu kopiowania zapewnia wbudowane partycjonowanie danych w celu równoległego kopiowania danych. Opcje partycjonowania danych można znaleźć na karcie Źródło działania kopiowania.

Zrzut ekranu przedstawiający opcje partycji

Po włączeniu kopii partycjonowanej działanie kopiowania uruchamia zapytania równoległe względem źródła usługi Azure SQL Database w celu załadowania danych według partycji. Stopień równoległy jest kontrolowany przez parallelCopies ustawienie działania kopiowania. Jeśli na przykład ustawiono parallelCopies wartość cztery, usługa jednocześnie generuje i uruchamia cztery zapytania na podstawie określonej opcji partycji i ustawień, a każde zapytanie pobiera część danych z usługi Azure SQL Database.

Zaleca się włączenie kopiowania równoległego przy użyciu partycjonowania danych, szczególnie w przypadku ładowania dużej ilości danych z usługi Azure SQL Database. Poniżej przedstawiono sugerowane konfiguracje dla różnych scenariuszy. Podczas kopiowania danych do magazynu danych opartego na plikach zaleca się zapisywanie w folderze jako wielu plików (tylko określ nazwę folderu), w tym przypadku wydajność jest lepsza niż zapisywanie w jednym pliku.

Scenariusz	Sugerowane ustawienia
Pełne ładowanie z dużej tabeli z partycjami fizycznymi.	Opcja partycji: fizyczne partycje tabeli. Podczas wykonywania usługa automatycznie wykrywa partycje fizyczne i kopiuje dane według partycji. Aby sprawdzić, czy tabela ma partycję fizyczną, czy nie, możesz odwołać się do tego zapytania.
Pełne ładowanie z dużej tabeli, bez partycji fizycznych, podczas gdy z liczbą całkowitą lub kolumną datetime na potrzeby partycjonowania danych.	Opcje partycji: partycja zakresu dynamicznego. Kolumna partycji (opcjonalnie): określ kolumnę używaną do partycjonowania danych. Jeśli nie zostanie określony, zostanie użyta kolumna indeksu lub klucza podstawowego. Górna granica partycji i dolna granica partycji (opcjonalnie): określ, czy chcesz określić krok partycji. Nie dotyczy to filtrowania wierszy w tabeli. Wszystkie wiersze w tabeli zostaną podzielone na partycje i skopiowane. Jeśli nie zostanie określony, działanie kopiowania automatycznie wykrywa wartości. Jeśli na przykład kolumna partycji "ID" zawiera wartości z zakresu od 1 do 100, a dolna granica zostanie ustawiona na wartość 20, a górna granica to 80, z kopią równoległą jako 4, usługa pobiera dane według 4 partycji — identyfikatory w zakresie <=20, [21, 50], [51, 80] i >=81.
Załaduj dużą ilość danych przy użyciu zapytania niestandardowego, bez partycji fizycznych, natomiast z liczbą całkowitą lub kolumną date/datetime na potrzeby partycjonowania danych.	Opcje partycji: partycja zakresu dynamicznego. Zapytanie: `SELECT * FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>`. Kolumna partycji: określ kolumnę używaną do partycjonowania danych. Górna granica partycji i dolna granica partycji (opcjonalnie): określ, czy chcesz określić krok partycji. Nie jest to przeznaczone do filtrowania wierszy w tabeli, wszystkie wiersze w wyniku zapytania zostaną partycjonowane i skopiowane. Jeśli nie zostanie określony, działanie kopiowania automatycznie wykrywa wartość. Podczas wykonywania usługa zastępuje `?AdfRangePartitionColumnName` rzeczywistą nazwę kolumny i zakresy wartości dla każdej partycji i wysyła je do usługi Azure SQL Database. Jeśli na przykład kolumna partycji "ID" zawiera wartości z zakresu od 1 do 100, a dolna granica zostanie ustawiona jako 20 i górna granica jako 80, z kopią równoległą jako 4, usługa pobiera dane według 4 partycji — identyfikatory w zakresie <=20, [21, 50], [51, 80] i >=81. Poniżej przedstawiono więcej przykładowych zapytań dla różnych scenariuszy: 1. Wykonaj zapytanie względem całej tabeli: `SELECT * FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition` 2. Kwerenda z tabeli z zaznaczeniem kolumny i dodatkowymi filtrami klauzuli where: `SELECT <column_list> FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>` 3. Kwerenda z podzapytaniami: `SELECT <column_list> FROM (<your_sub_query>) AS T WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>` 4. Kwerenda z partycją w podzapytaniu: `SELECT <column_list> FROM (SELECT <your_sub_query_column_list> FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition) AS T`

Najlepsze rozwiązania dotyczące ładowania danych z opcją partycji:

Wybierz charakterystyczną kolumnę jako kolumnę partycji (np. klucz podstawowy lub unikatowy klucz), aby uniknąć niesymetryczności danych.
Jeśli tabela ma wbudowaną partycję, użyj opcji partycji "Partycje fizyczne tabeli", aby uzyskać lepszą wydajność.
Jeśli używasz środowiska Azure Integration Runtime do kopiowania danych, możesz ustawić większe wartości "Integracja danych Units (DIU)" (>4) w celu korzystania z większej liczby zasobów obliczeniowych. Sprawdź odpowiednie scenariusze.
"Stopień równoległości kopiowania" steruje numerami partycji, ustawiając tę liczbę zbyt dużą, czasami boli wydajność, zaleca się ustawienie tej liczby jako (DIU lub liczba węzłów własnego środowiska IR) * (od 2 do 4).

Przykład: pełne ładowanie z dużej tabeli z partycjami fizycznymi

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

Przykład: zapytanie z partycją zakresu dynamicznego

"source": {
    "type": "AzureSqlSource",
    "query": "SELECT * FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition 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>"
    }
}

Przykładowe zapytanie do sprawdzania partycji fizycznej

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]'

Jeśli tabela ma partycję fizyczną, zostanie wyświetlona wartość "HasPartition" jako "tak", jak pokazano poniżej.

Wynik zapytania SQL

Najlepsze rozwiązanie dotyczące ładowania danych do usługi Azure SQL Database

Podczas kopiowania danych do usługi Azure SQL Database może być wymagane inne zachowanie zapisu:

Dołącz: Moje dane źródłowe mają tylko nowe rekordy.
Upsert: Moje dane źródłowe mają zarówno wstawki, jak i aktualizacje.
Zastąp: Chcę ponownie załadować całą tabelę wymiarów za każdym razem.
Pisanie za pomocą logiki niestandardowej: potrzebuję dodatkowego przetwarzania przed ostatecznym wstawieniem do tabeli docelowej.

Zapoznaj się z odpowiednimi sekcjami dotyczącymi sposobu konfigurowania w usłudze i najlepszych rozwiązań.

Dołączanie danych

Dołączanie danych to domyślne zachowanie tego łącznika ujścia usługi Azure SQL Database. usługa wykonuje operację wstawiania zbiorczego w celu wydajnego zapisu w tabeli. Źródło i ujście można skonfigurować odpowiednio w działaniu kopiowania.

Wykonywanie operacji upsert dla danych

działanie Kopiuj teraz obsługuje natywne ładowanie danych do tabeli tymczasowej bazy danych, a następnie aktualizowanie danych w tabeli ujścia, jeśli klucz istnieje i w przeciwnym razie wstaw nowe dane. Aby dowiedzieć się więcej na temat ustawień upsert w działaniach kopiowania, zobacz Azure SQL Database as the sink (Usługa Azure SQL Database jako ujście).

Zastąp całą tabelę

Właściwość preCopyScript można skonfigurować w ujściu działania kopiowania. W takim przypadku dla każdego uruchomionego działania kopiowania usługa uruchamia skrypt jako pierwszy. Następnie uruchamia kopię, aby wstawić dane. Aby na przykład zastąpić całą tabelę najnowszymi danymi, określ skrypt, aby najpierw usunąć wszystkie rekordy przed zbiorczym załadowaniem nowych danych ze źródła.

Zapisywanie danych przy użyciu logiki niestandardowej

Kroki zapisywania danych za pomocą logiki niestandardowej są podobne do kroków opisanych w sekcji Dane upsert. Jeśli musisz zastosować dodatkowe przetwarzanie przed ostatecznym wstawieniem danych źródłowych do tabeli docelowej, możesz załadować do tabeli przejściowej, a następnie wywołać działanie procedury składowanej lub wywołać procedurę składowaną w ujściu działania kopiowania, aby zastosować dane, lub użyć Przepływ danych mapowania.

Wywoływanie procedury składowanej z ujścia SQL

Podczas kopiowania danych do usługi Azure SQL Database można również skonfigurować i wywołać procedurę składowaną określoną przez użytkownika z dodatkowymi parametrami w każdej partii tabeli źródłowej. Funkcja procedury składowanej korzysta z parametrów wartości tabeli.

Procedurę składowaną można użyć, gdy wbudowane mechanizmy kopiowania nie obsługują tego celu. Przykładem jest zastosowanie dodatkowego przetwarzania przed ostatecznym wstawieniem danych źródłowych do tabeli docelowej. Niektóre dodatkowe przykłady przetwarzania to, gdy chcesz scalić kolumny, wyszukać dodatkowe wartości i wstawić je do więcej niż jednej tabeli.

W poniższym przykładzie pokazano, jak za pomocą procedury składowanej wykonać operację upsert w tabeli w usłudze Azure SQL Database. Załóżmy, że dane wejściowe i tabela Marketing ujścia mają trzy kolumny: ProfileID, State i Category. Wykonaj upsert na podstawie kolumny ProfileID i zastosuj ją tylko do określonej kategorii o nazwie "ProductA".

W bazie danych zdefiniuj typ tabeli o takiej samej nazwie jak sqlWriterTableType. Schemat typu tabeli jest taki sam jak schemat zwracany przez dane wejściowe.

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

W bazie danych zdefiniuj procedurę składowaną o takiej samej nazwie jak sqlWriterStoredProcedureName. Obsługuje dane wejściowe z określonego źródła i scala je z tabelą wyjściową. Nazwa parametru typu tabeli w procedurze składowanej jest taka sama jak nazwa_tabeli zdefiniowana w zestawie danych.

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

W potoku usługi Azure Data Factory lub Synapse zdefiniuj sekcję ujścia SQL w działaniu kopiowania w następujący sposób:

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

Podczas zapisywania danych w usłudze Azure SQL Database przy użyciu procedury składowanej ujście dzieli dane źródłowe na mini partie, a następnie wykonuje wstawianie, więc dodatkowe zapytanie w procedurze składowanej można wykonać wiele razy. Jeśli masz zapytanie dotyczące działania kopiowania do uruchomienia przed zapisaniem danych w usłudze Azure SQL Database, nie zaleca się dodawania go do procedury składowanej, dodaj je w polu Skrypt przed kopiowaniem.

Właściwości przepływu mapowania danych

Podczas przekształcania danych w przepływie mapowania danych można odczytywać i zapisywać w tabelach z usługi Azure SQL Database. Aby uzyskać więcej informacji, zobacz przekształcanie źródła i przekształcanie ujścia w przepływach danych mapowania.

Przekształcanie źródła

Ustawienia specyficzne dla usługi Azure SQL Database są dostępne w usłudze Karta Opcje źródła przekształcenia źródła.

Dane wejściowe: wybierz, czy wskazujesz źródło w tabeli (odpowiednik Select * from <table-name>) lub wprowadź niestandardowe zapytanie SQL.

Zapytanie: w przypadku wybrania pozycji Zapytanie w polu wejściowym wprowadź zapytanie SQL dla źródła. To ustawienie zastępuje dowolną tabelę wybraną w zestawie danych. Klauzule Order By nie są obsługiwane w tym miejscu, ale można ustawić pełną instrukcję SELECT FROM. Można również użyć funkcji tabeli zdefiniowanych przez użytkownika. select * from udfGetData() to funkcja zdefiniowana przez użytkownika w języku SQL, która zwraca tabelę. To zapytanie utworzy tabelę źródłową, której można użyć w przepływie danych. Korzystanie z zapytań to również doskonały sposób na zmniejszenie liczby wierszy na potrzeby testowania lub wyszukiwania.

Napiwek

Wspólne wyrażenie tabeli (CTE) w języku SQL nie jest obsługiwane w trybie zapytania przepływu danych mapowania, ponieważ wymaganie wstępne korzystania z tego trybu polega na tym, że zapytania mogą być używane w klauzuli FROM zapytania SQL, ale nie można tego zrobić. Aby użyć CTK, należy utworzyć procedurę składowaną przy użyciu następującego zapytania:

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

Następnie użyj trybu Procedury składowanej w transformacji źródłowej przepływu danych mapowania i ustaw @query przykładowy .with CTE as (select 'test' as a) select * from CTE Następnie można użyć ctEs zgodnie z oczekiwaniami.

Procedura składowana: wybierz tę opcję, jeśli chcesz wygenerować projekcję i dane źródłowe z procedury składowanej wykonywanej z źródłowej bazy danych. Możesz wpisać schemat, nazwę procedury i parametry lub kliknąć pozycję Odśwież, aby poprosić usługę o odnalezienie schematów i nazw procedur. Następnie możesz kliknąć pozycję Importuj, aby zaimportować wszystkie parametry procedury przy użyciu formularza @paraName.

Procedura składowana

Przykład sql: Select * from MyTable where customerId > 1000 and customerId < 2000
Przykład sparametryzowanego kodu SQL: "select * from {$tablename} where orderyear > {$year}"

Rozmiar partii: wprowadź rozmiar partii, aby podzielić duże dane na odczyty.

Poziom izolacji: wartość domyślna dla źródeł SQL w przepływie mapowania danych jest odczytywana jako niezatwierdzona. W tym miejscu można zmienić poziom izolacji na jedną z następujących wartości:

Odczyt zatwierdzony
Odczyt niezatwierdzony
Powtarzalny odczyt
Serializacji
Brak (ignoruj poziom izolacji)

Poziom izolacji

Włącz wyodrębnianie przyrostowe: użyj tej opcji, aby poinformować usługę ADF o przetwarzaniu tylko wierszy, które uległy zmianie od czasu ostatniego wykonania potoku. Aby włączyć wyodrębnianie przyrostowe z dryfem schematu, wybierz tabele oparte na kolumnach przyrostowych/limitów wodnych, a nie na tabelach, które są włączone dla natywnego przechwytywania danych zmian.

Kolumna przyrostowa: w przypadku korzystania z funkcji wyodrębniania przyrostowego należy wybrać kolumnę daty/godziny lub liczbową, która ma być używana jako znak wodny w tabeli źródłowej.

Włącz natywne przechwytywanie danych zmian (wersja zapoznawcza): użyj tej opcji, aby poinformować usługę ADF o przetwarzaniu tylko danych różnicowych przechwyconych przez technologię przechwytywania zmian SQL od czasu ostatniego wykonania potoku. Dzięki tej opcji dane różnicowe, w tym wstawianie wierszy, aktualizowanie i usuwanie, zostaną załadowane automatycznie bez konieczności wprowadzania kolumn przyrostowych. Przed użyciem tej opcji w usłudze ADF należy włączyć przechwytywanie zmian danych w usłudze Azure SQL DB. Aby uzyskać więcej informacji na temat tej opcji w usłudze ADF, zobacz przechwytywanie danych zmian natywnych.

Rozpocznij odczytywanie od początku: ustawienie tej opcji przy użyciu wyodrębniania przyrostowego spowoduje, że usługa ADF odczytuje wszystkie wiersze podczas pierwszego wykonywania potoku z włączonym wyodrębnieniem przyrostowym.

Przekształcenie ujścia

Ustawienia specyficzne dla usługi Azure SQL Database są dostępne na karcie Ustawienia przekształcenia ujścia.

Metoda aktualizacji: określa, jakie operacje są dozwolone w miejscu docelowym bazy danych. Ustawieniem domyślnym jest zezwalanie tylko na wstawianie. Aby zaktualizować, upsert lub usunąć wiersze, do tagowania wierszy dla tych akcji jest wymagane przekształcenie alter-row. W przypadku aktualizacji, operacji upsert i usuwania należy ustawić kolumnę klucza lub kolumny w celu określenia, który wiersz ma zostać zmieniony.

Kolumny kluczy

Nazwa kolumny wybranej tutaj jako klucz będzie używana przez usługę w ramach kolejnej aktualizacji, upsert, delete. W związku z tym należy wybrać kolumnę, która istnieje w mapowaniu ujścia. Jeśli nie chcesz zapisywać wartości w tej kolumnie klucza, kliknij pozycję "Pomiń zapisywanie kolumn kluczy".

Kolumnę klucza używaną tutaj do aktualizowania docelowej tabeli usługi Azure SQL Database można sparametryzować. Jeśli masz wiele kolumn dla klucza złożonego, kliknij pozycję "Wyrażenie niestandardowe" i będzie można dodać zawartość dynamiczną przy użyciu języka wyrażeń przepływu danych, który może zawierać tablicę ciągów z nazwami kolumn dla klucza złożonego.

Akcja tabeli: określa, czy należy ponownie utworzyć lub usunąć wszystkie wiersze z tabeli docelowej przed zapisem.

Brak: żadna akcja nie zostanie wykonana w tabeli.
Utwórz ponownie: tabela zostanie porzucona i utworzona ponownie. Wymagane w przypadku dynamicznego tworzenia nowej tabeli.
Obcinanie: wszystkie wiersze z tabeli docelowej zostaną usunięte.

Rozmiar partii: określa liczbę wierszy zapisywanych w każdym zasobniku. Większe rozmiary partii zwiększają kompresję i optymalizację pamięci, ale ryzykuj z wyjątków pamięci podczas buforowania danych.

Użyj bazy danych TempDB: domyślnie usługa będzie używać globalnej tabeli tymczasowej do przechowywania danych w ramach procesu ładowania. Możesz też usunąć zaznaczenie opcji "Użyj bazy danych TempDB", a zamiast tego poprosić usługę o przechowywanie tymczasowej tabeli przechowywania w bazie danych użytkownika, która znajduje się w bazie danych używanej dla tego ujścia.

Korzystanie z bazy danych tymczasowych

Skrypty wstępne i post sql: wprowadź wielowierszowe skrypty SQL, które będą wykonywane przed (przetwarzanie wstępne) i po (przetwarzaniu po przetworzeniu) dane są zapisywane w bazie danych ujścia

Zrzut ekranu przedstawiający ustawienia ujścia ze skryptami przetwarzania sql i wstępnymi.

Napiwek

Zaleca się podzielenie pojedynczych skryptów wsadowych z wieloma poleceniami na wiele partii.
W ramach partii można uruchamiać tylko instrukcje języka DDL (Data Definition Language) i Języka manipulowania danymi (DML), które zwracają prostą liczbę aktualizacji. Dowiedz się więcej na temat wykonywania operacji wsadowych

Obsługa wierszy błędów

Podczas zapisywania w usłudze Azure SQL DB niektóre wiersze danych mogą zakończyć się niepowodzeniem z powodu ograniczeń ustawionych przez miejsce docelowe. Niektóre typowe błędy to:

Ciąg lub dane binarne zostaną obcięte w tabeli
Nie można wstawić wartości NULL do kolumny
Instrukcja INSERT powoduje konflikt z ograniczeniem CHECK

Domyślnie uruchomienie przepływu danych zakończy się niepowodzeniem podczas pierwszego błędu, który zostanie wyświetlony. Możesz wybrać opcję Kontynuuj przy błędzie , który umożliwia ukończenie przepływu danych, nawet jeśli poszczególne wiersze zawierają błędy. Usługa udostępnia różne opcje obsługi tych wierszy błędów.

Zatwierdzenie transakcji: wybierz, czy dane są zapisywane w jednej transakcji, czy w partiach. Pojedyncza transakcja zapewni gorzej wydajność, ale żadne zapisane dane nie będą widoczne dla innych do momentu zakończenia transakcji.

Dane wyjściowe odrzucone: jeśli to ustawienie jest włączone, możesz wyświetlić wiersze błędów w pliku csv w usłudze Azure Blob Storage lub na wybranym koncie usługi Azure Data Lake Storage Gen2. Spowoduje to zapisanie wierszy błędów z trzema dodatkowymi kolumnami: operacją SQL, taką jak INSERT lub UPDATE, kodem błędu przepływu danych i komunikatem o błędzie w wierszu.

Zgłoś powodzenie w przypadku błędu: w przypadku włączenia przepływu danych przepływ danych zostanie oznaczony jako powodzenie, nawet jeśli zostaną znalezione wiersze błędów.

Obsługa wierszy błędów

Mapowanie typów danych dla usługi Azure SQL Database

Gdy dane są kopiowane z usługi Azure SQL Database lub do usługi Azure SQL Database, następujące mapowania są używane z typów danych usługi Azure SQL Database do tymczasowych typów danych usługi Azure Data Factory. Te same mapowania są używane przez funkcję potoku usługi Synapse, która bezpośrednio implementuje usługę Azure Data Factory. Aby dowiedzieć się, jak działanie kopiowania mapuje schemat źródłowy i typ danych na ujście, zobacz Mapowania schematu i typu danych.

Typ danych usługi Azure SQL Database	Typ danych tymczasowych usługi Data Factory
bigint	Int64
dane binarne	Bajt[]
bitowe	Wartość logiczna
char	Ciąg, Znak[]
data	DateTime
Datetime	DateTime
datetime2	DateTime
Datetimeoffset	DateTimeOffset
Dziesiętne	Dziesiętne
ATRYBUT FILESTREAM (varbinary(max))	Bajt[]
Liczba zmiennoprzecinkowa	Liczba rzeczywista
obraz	Bajt[]
int	Int32
pieniędzy	Dziesiętne
nchar	Ciąg, Znak[]
Ntext	Ciąg, Znak[]
numeryczne	Dziesiętne
nvarchar	Ciąg, Znak[]
rzeczywiste	Pojedynczy
Rowversion	Bajt[]
smalldatetime	DateTime
smallint	Int16
smallmoney	Dziesiętne
Sql_variant	Objekt
text	Ciąg, Znak[]
time	przedział_czasu
timestamp	Bajt[]
tinyint	Byte
uniqueidentifier	Identyfikator GUID
varbinary	Bajt[]
varchar	Ciąg, Znak[]
xml	String

Uwaga

W przypadku typów danych mapujących na typ tymczasowy dziesiętny obecnie działanie Kopiuj obsługuje precyzję do 28. Jeśli masz dane o precyzji większej niż 28, rozważ przekonwertowanie na ciąg w zapytaniu SQL.

Właściwości działania wyszukiwania

Aby dowiedzieć się więcej o właściwościach, sprawdź działanie Wyszukiwania.

Właściwości działania GetMetadata

Aby dowiedzieć się więcej o właściwościach, sprawdź działanie GetMetadata

Korzystanie z funkcji Always Encrypted

Podczas kopiowania danych z/do usługi Azure SQL Database za pomocą funkcji Always Encrypted wykonaj poniższe kroki:

Przechowywanie klucza głównego kolumny (CMK) w usłudze Azure Key Vault. Dowiedz się więcej na temat konfigurowania funkcji Always Encrypted przy użyciu usługi Azure Key Vault
Upewnij się, że masz dostęp do magazynu kluczy, w którym jest przechowywany klucz główny kolumny (CMK ). Zapoznaj się z tym artykułem , aby uzyskać wymagane uprawnienia.
Utwórz połączoną usługę w celu nawiązania połączenia z bazą danych SQL i włącz funkcję "Always Encrypted" przy użyciu tożsamości zarządzanej lub jednostki usługi.

Uwaga

Funkcja Always Encrypted usługi Azure SQL Database obsługuje poniższe scenariusze:

Magazyny danych źródła lub ujścia używają tożsamości zarządzanej lub jednostki usługi jako typu uwierzytelniania dostawcy kluczy.
Magazyny danych źródła i ujścia używają tożsamości zarządzanej jako typu uwierzytelniania dostawcy kluczy.
Magazyny danych źródła i ujścia używają tej samej jednostki usługi co typ uwierzytelniania dostawcy kluczy.

Uwaga

Obecnie funkcja Always Encrypted usługi Azure SQL Database jest obsługiwana tylko w przypadku przekształcania źródła w przepływach danych mapowania.

Przechwytywanie danych zmian natywnych

Usługa Azure Data Factory może obsługiwać natywne funkcje przechwytywania danych zmian dla programów SQL Server, Azure SQL DB i Azure SQL MI. Zmienione dane, w tym wstawianie wierszy, aktualizowanie i usuwanie w magazynach SQL, można automatycznie wykrywać i wyodrębniać przez przepływ danych mapowania usługi ADF. Bez użycia kodu w przepływie danych mapowania użytkownicy mogą łatwo osiągnąć scenariusz replikacji danych z magazynów SQL, dołączając bazę danych jako magazyn docelowy. Co więcej, użytkownicy mogą również tworzyć dowolną logikę przekształcania danych między w celu osiągnięcia przyrostowego scenariusza ETL z magazynów SQL.

Upewnij się, że nazwa potoku i działania pozostaje niezmieniona, aby punkt kontrolny mógł być rejestrowany przez usługę ADF w celu automatycznego pobierania zmienionych danych z ostatniego uruchomienia. Jeśli zmienisz nazwę potoku lub nazwę działania, punkt kontrolny zostanie zresetowany, co prowadzi do rozpoczęcia od początku lub pobrania zmian od teraz w następnym uruchomieniu. Jeśli chcesz zmienić nazwę potoku lub nazwę działania, ale nadal zachowaj punkt kontrolny, aby automatycznie uzyskać zmienione dane z ostatniego uruchomienia, użyj własnego klucza punktu kontrolnego w działaniu przepływu danych, aby to osiągnąć.

Podczas debugowania potoku ta funkcja działa tak samo. Pamiętaj, że punkt kontrolny zostanie zresetowany podczas odświeżania przeglądarki podczas uruchamiania debugowania. Po zadowoleniu z wyniku potoku z przebiegu debugowania możesz przejść do przodu, aby opublikować i wyzwolić potok. W momencie, gdy po raz pierwszy wyzwalasz opublikowany potok, automatycznie uruchamia się ponownie od początku lub pobiera zmiany od teraz.

W sekcji monitorowania zawsze masz możliwość ponownego uruchomienia potoku. W tym przypadku zmienione dane są zawsze przechwytywane z poprzedniego punktu kontrolnego wybranego przebiegu potoku.

Przykład 1:

Jeśli bezpośrednio utworzysz łańcuch przekształcenia źródłowego, do którego odwołujesz się do zestawu danych z włączoną usługą SQL CDC z przekształceniem ujścia, do którego odwołujesz się do bazy danych w przepływie mapowania, zmiany wprowadzone w źródle SQL zostaną automatycznie zastosowane do docelowej bazy danych, dzięki czemu można łatwo uzyskać scenariusz replikacji danych między bazami danych. Możesz użyć metody aktualizacji w transformacji ujścia, aby wybrać, czy chcesz zezwolić na wstawianie, zezwalanie na aktualizowanie lub zezwalanie na usuwanie w docelowej bazie danych. Przykładowy skrypt w przepływie danych mapowania jest następujący.

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

Przykład 2:

Jeśli chcesz włączyć scenariusz ETL zamiast replikacji danych między bazą danych za pośrednictwem usługi SQL CDC, możesz użyć wyrażeń w przepływie danych mapowania, w tym isInsert(1), isUpdate(1) i isDelete(1) w celu odróżnienia wierszy z różnymi typami operacji. Poniżej przedstawiono jeden z przykładowych skryptów mapowania przepływu danych na wyprowadzanie jednej kolumny z wartością: 1, aby wskazać wstawione wiersze, 2, aby wskazać zaktualizowane wiersze i 3, aby wskazać usunięte wiersze dla przekształceń podrzędnych w celu przetwarzania danych różnicowych.

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

Znane ograniczenie:

Tylko zmiany netto z usługi SQL CDC zostaną załadowane przez usługę ADF za pośrednictwem cdc.fn_cdc_get_net_changes_.

Aby uzyskać listę magazynów danych obsługiwanych jako źródła i ujścia działania kopiowania, zobacz Obsługiwane magazyny danych i formaty.

Kopiowanie i przekształcanie danych w usłudze Azure SQL Database przy użyciu usługi Azure Data Factory lub Azure Synapse Analytics

Obsługiwane możliwości

Rozpocznij

Tworzenie połączonej usługi Azure SQL Database przy użyciu interfejsu użytkownika

szczegóły konfiguracji Połączenie or

Właściwości połączonej usługi

Uwierzytelnianie SQL

Uwierzytelnianie nazwy głównej usługi

Przykład połączonej usługi korzystającej z uwierzytelniania jednostki usługi

Uwierzytelnianie tożsamości zarządzanej przypisanej przez system

Uwierzytelnianie tożsamości zarządzanej przypisanej przez użytkownika

Właściwości zestawu danych

Przykład właściwości zestawu danych

Właściwości działania kopiowania

Usługa Azure SQL Database jako źródło

Przykład zapytania SQL

Przykład procedury składowanej

Definicja procedury składowanej

Usługa Azure SQL Database jako ujście

Równoległa kopia z bazy danych SQL

Przykładowe zapytanie do sprawdzania partycji fizycznej

Najlepsze rozwiązanie dotyczące ładowania danych do usługi Azure SQL Database

Dołączanie danych

Wykonywanie operacji upsert dla danych

Zastąp całą tabelę

Zapisywanie danych przy użyciu logiki niestandardowej

Wywoływanie procedury składowanej z ujścia SQL

Właściwości przepływu mapowania danych

Przekształcanie źródła

Przekształcenie ujścia

Obsługa wierszy błędów

Mapowanie typów danych dla usługi Azure SQL Database

Właściwości działania wyszukiwania

Właściwości działania GetMetadata

Korzystanie z funkcji Always Encrypted

Przechwytywanie danych zmian natywnych

Przykład 1:

Przykład 2:

Znane ograniczenie:

Powiązana zawartość

Dodatkowe zasoby