Copiare e trasformare i dati da e verso SQL Server usando Azure Data Factory

SI APPLICA A: Azure Data Factory Azure Synapse Analytics

Questo articolo illustra come usare l'attività di copia in Azure Data Factory per copiare dati da e SQL Server database e usare Flusso di dati per trasformare i dati nel database SQL Server. Per altre informazioni su Azure Data Factory, vedere l'articolo introduttivo.

Funzionalità supportate

Questo SQL Server connettore è supportato per le attività seguenti:

È possibile copiare dati da un database SQL Server in qualsiasi archivio dati sink supportato. In caso contrario, è possibile copiare dati da qualsiasi archivio dati di origine supportato in un SQL Server database. Per un elenco degli archivi dati supportati come origini o sink dall'attività di copia, vedere la tabella relativa agli archivi dati supportati.

In particolare, il connettore SQL Server supporta:

  • SQL Server versione 2005 e successive.
  • La copia dei dati tramite l'autenticazione di SQL o di Windows.
  • Come origine, recuperare i dati usando una query SQL o un stored procedure. Per informazioni dettagliate, è anche possibile scegliere di eseguire la copia parallela SQL Server origine dati. Per informazioni dettagliate, vedere la sezione Copia parallela dal database SQL.
  • Come sink, creare automaticamente la tabella di destinazione se non esiste in base allo schema di origine; Aggiunta di dati a una tabella o chiamata di un stored procedure logica personalizzata durante la copia.

SQL Server Express LocalDB non è supportato.

Nota

SQL Server Always Encrypted non è attualmente supportato da questo connettore. Per risolvere il problema, è possibile usare un connettore ODBC generico e un SQL Server ODBC. Seguire queste indicazioni con il download del driver ODBC e le configurazioni della stringa di connessione.

Prerequisiti

Se l'archivio dati si trova all'interno di una rete locale, una rete virtuale di Azure o un cloud privato virtuale di Amazon, è necessario configurare un runtime di integrazione self-hosted per connettersi.

Se l'archivio dati è un servizio dati cloud gestito, è possibile usare il Azure Integration Runtime. Se l'accesso è limitato agli indirizzi IP approvati nelle regole del firewall, è possibile Azure Integration Runtime indirizzi IP consentiti.

È anche possibile usare la funzionalità runtime di integrazione della rete virtuale gestita in Azure Data Fatcory per accedere alla rete locale senza installare e configurare un runtime di integrazione self-hosted.

Per altre informazioni sui meccanismi di sicurezza di rete e sulle opzioni supportate da Data Factory, vedere strategie di accesso ai dati.

Introduzione

Per eseguire l'attività di copia con una pipeline, è possibile usare uno degli strumenti o SDK seguenti:

Le sezioni seguenti forniscono informazioni dettagliate sulle proprietà usate per definire Data Factory entità specifiche per il connettore SQL Server database.

Proprietà del servizio collegato

Le proprietà seguenti sono supportate per il SQL Server collegato:

Proprietà Descrizione Obbligatoria
type La proprietà type deve essere impostata su SqlServer.
connectionString Specificare le informazioni connectionString necessarie per connettersi al database SQL Server usando l'autenticazione SQL o autenticazione di Windows. Vedere gli esempi seguenti.
È anche possibile inserire una password Azure Key Vault. Se si tratta dell'autenticazione SQL, estrarre password la configurazione dalla stringa di connessione. Per altre informazioni, vedere l'esempio JSON che segue la tabella e Archiviare le credenziali in Azure Key Vault.
userName Specificare un nome utente se si usa autenticazione di Windows. Ad esempio, nomedominio\nomeutente. No
password Specificare una password per l'account utente specificato per il nome utente. Contrassegnare questo campo come SecureString per archiviarlo in modo sicuro in Azure Data Factory. In alternativa, fare riferimento a un segreto archiviato in Azure Key Vault. No
connectVia Questo runtime di integrazione viene usato per connettersi all'archivio dati. Per altre informazioni, vedere la sezione Prerequisiti. Se non specificato, viene usato il runtime di integrazione di Azure predefinito. No

Suggerimento

Se viene visualizzato un errore con il codice di errore "UserErrorFailedToConnectToSqlServer" e un messaggio simile a "Il limite di sessione per il database è XXX ed è stato raggiunto", aggiungere alla stringa di connessione e Pooling=false riprovare.

Esempio 1: Usare l'autenticazione SQL

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "typeProperties": {
            "connectionString": "Data Source=<servername>\\<instance name if using named instance>;Initial Catalog=<databasename>;Integrated Security=False;User ID=<username>;Password=<password>;"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Esempio 2: Usare l'autenticazione SQL con una password in Azure Key Vault

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "typeProperties": {
            "connectionString": "Data Source=<servername>\\<instance name if using named instance>;Initial Catalog=<databasename>;Integrated Security=False;User ID=<username>;",
            "password": { 
                "type": "AzureKeyVaultSecret", 
                "store": { 
                    "referenceName": "<Azure Key Vault linked service name>", 
                    "type": "LinkedServiceReference" 
                }, 
                "secretName": "<secretName>" 
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Esempio 3: Usare autenticazione di Windows

{
    "name": "SqlServerLinkedService",
    "properties": {
        "type": "SqlServer",
        "typeProperties": {
            "connectionString": "Data Source=<servername>\\<instance name if using named instance>;Initial Catalog=<databasename>;Integrated Security=True;",
            "userName": "<domain\\username>",
            "password": {
                "type": "SecureString",
                "value": "<password>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
     }
}

Proprietà del set di dati

Per un elenco completo delle sezioni e delle proprietà disponibili per la definizione di set di dati, vedere l'articolo sui set di dati. In questa sezione viene fornito un elenco delle proprietà supportate dal set SQL Server set di dati.

Per copiare dati da e in un database SQL Server, sono supportate le proprietà seguenti:

Proprietà Descrizione Obbligatoria
type La proprietà type del set di dati deve essere impostata su SqlServerTable.
schema Nome dello schema. No per l'origine, Sì per il sink
tabella Nome della tabella/vista. No per l'origine, Sì per il sink
tableName Nome della tabella/vista con schema. Questa proprietà è supportata per garantire la compatibilità con le versioni precedenti. Per i nuovi carichi di lavoro, usare schema e table. No per l'origine, Sì per il sink

Esempio

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

Proprietà dell'attività di copia

Per un elenco completo delle sezioni e delle proprietà disponibili per definire le attività, vedere l'articolo sulle pipeline. In questa sezione viene fornito un elenco delle proprietà supportate dall'origine SQL Server sink.

SQL Server come origine

Suggerimento

Per caricare i dati da SQL Server in modo efficiente usando il partizionamento dei dati, vedere Copia parallela dal database SQL.

Per copiare dati da un database SQL Server, impostare il tipo di origine nell'attività di copia su SqlSource. Nella sezione source dell'attività di copia sono supportate le proprietà seguenti:

Proprietà Descrizione Obbligatoria
type La proprietà type dell'origine dell'attività di copia deve essere impostata su SqlSource.
sqlReaderQuery Usare la query SQL personalizzata per leggere i dati. Un esempio è select * from MyTable. No
sqlReaderStoredProcedureName Questa proprietà definisce il nome della stored procedure che legge i dati dalla tabella di origine. L'ultima istruzione SQL deve essere un'istruzione SELECT nella stored procedure. No
storedProcedureParameters Questi parametri sono relativi alla stored procedure.
I valori consentiti sono coppie nome-valore. I nomi e le maiuscole e minuscole dei parametri devono corrispondere ai nomi e all'utilizzo di maiuscole e minuscole stored procedure parametri.
No
isolationLevel Specifica il comportamento di blocco della transazione per l'origine SQL. I valori consentiti sono: ReadCommitted, ReadUncommitted, RepeatableRead, Serializable, Snapshot. Se non viene specificato, viene usato il livello di isolamento predefinito del database. Per altre informazioni dettagliate, vedere questo documento. No
partitionOptions Specifica le opzioni di partizionamento dei dati usate per caricare i dati SQL Server.
I valori consentiti sono: Nessuno (impostazione predefinita), PhysicalPartitionsOfTable e DynamicRange.
Quando un'opzione di partizione è abilitata (ovvero non ), il grado di parallelismo per caricare contemporaneamente i dati da SQL Server è controllato dall'impostazione None parallelCopies nell'attività di copia.
No
partitionSettings Specifica il gruppo di impostazioni per il partizionamento dei dati.
Applicare quando l'opzione di partizione non è None .
No
In partitionSettings :
partitionColumnName Specificare il nome della colonna di origine in tipo integer o date/datetime ( , , , , , , o ) che verrà utilizzato dal partizionamento degli intervalli per int smallint la bigint copia date smalldatetime datetime datetime2 datetimeoffset parallela. Se non specificato, l'indice o la chiave primaria della tabella viene rilevata automaticamente e usata come colonna di partizione.
Si applica quando l'opzione di partizione è DynamicRange. Se si usa una query per recuperare i dati di origine, eseguire l'hook ?AdfDynamicRangePartitionCondition nella clausola WHERE. Per un esempio, vedere la sezione Copia parallela dal database SQL.
No
partitionUpperBound Valore massimo della colonna di partizione per la suddivisione dell'intervallo di partizioni. Questo valore viene usato per decidere lo stride della partizione, non per filtrare le righe nella tabella. Tutte le righe del risultato della tabella o della query verranno partizionate e copiate. Se non specificato, l'attività di copia rileva automaticamente il valore.
Si applica quando l'opzione di partizione è DynamicRange. Per un esempio, vedere la sezione Copia parallela dal database SQL.
No
partitionLowerBound Valore minimo della colonna di partizione per la suddivisione dell'intervallo di partizioni. Questo valore viene usato per decidere lo stride della partizione, non per filtrare le righe nella tabella. Tutte le righe del risultato della tabella o della query verranno partizionate e copiate. Se non specificato, l'attività di copia rileva automaticamente il valore.
Si applica quando l'opzione di partizione è DynamicRange. Per un esempio, vedere la sezione Copia parallela dal database SQL.
No

Tenere presente quanto segue:

  • Se sqlReaderQuery viene specificato per SqlSource, l'attività di copia esegue la query sull'origine SQL Server per ottenere i dati. In alternativa, è possibile specificare una stored procedure indicando i parametri sqlReaderStoredProcedureName e storedProcedureParameters, se la stored procedure accetta parametri.
  • Quando si usa stored procedure nell'origine per recuperare i dati, si noti che se il stored procedure è progettato per restituire uno schema diverso quando viene passato un valore di parametro diverso, è possibile che si verifichi un errore o venga visualizzato un risultato imprevisto durante l'importazione dello schema dall'interfaccia utente o quando si copiano dati nel database SQL con la creazione automatica di tabelle.

Esempio: Usare una query SQL

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

Esempio: Usare un stored procedure

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

Definizione della stored procedure

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

SQL Server come sink

Suggerimento

Per altre informazioni sui comportamenti di scrittura supportati, sulle configurazioni e sulle procedure consigliate, vedere Procedure consigliate per il caricamento dei dati in SQL Server.

Per copiare dati da SQL Server, impostare il tipo di sink nell'attività di copia su SqlSink. Nella sezione sink dell'attività di copia sono supportate le proprietà seguenti:

Proprietà Descrizione Obbligatoria
type La proprietà type del sink dell'attività di copia deve essere impostata su SqlSink.
preCopyScript Questa proprietà specifica una query SQL per l'esecuzione dell'attività di copia prima di scrivere dati SQL Server. Viene richiamata solo una volta per ogni esecuzione della copia. È possibile usare questa proprietà per pulire i dati precaricati. No
tableOption Specifica se creare automaticamente la tabella sink se non esiste in base allo schema di origine. La creazione automatica di tabelle non è supportata quando il sink specifica stored procedure. I valori consentiti sono: none (impostazione predefinita), autoCreate. No
sqlWriterStoredProcedureName Il nome della stored procedure che definisce come applicare i dati di origine in una tabella di destinazione.
Questa stored procedure viene richiamata per batch. Per le operazioni eseguite una sola volta e che non hanno nulla a che fare con i dati di origine, ad esempio l'eliminazione o il troncamento, usare la preCopyScript proprietà .
Vedere l'esempio da Richiamare un stored procedure da un sink SQL.
No
storedProcedureTableTypeParameterName Nome del parametro del tipo di tabella specificato nel stored procedure. No
sqlWriterTableType Nome del tipo di tabella da usare nel stored procedure. Nel corso dell'attività di copia, i dati spostati vengono resi disponibili in una tabella temporanea di questo tipo. Il codice della stored procedure può quindi unire i dati di cui è in corso la copia con i dati esistenti. No
storedProcedureParameters Parametri per la stored procedure.
I valori consentiti sono coppie nome-valore. I nomi e le maiuscole e minuscole dei parametri devono corrispondere ai nomi e alle maiuscole e minuscole dei parametri della stored procedure.
No
writeBatchSize Numero di righe da inserire nella tabella SQL per batch.
I valori consentiti sono integer per il numero di righe. Per impostazione predefinita, Azure Data Factory le dimensioni del batch appropriate in base alle dimensioni della riga.
No
writeBatchTimeout Questa proprietà specifica il tempo di attesa per l'operazione di inserimento batch da completare prima del timeout.
I valori consentiti sono per l'intervallo di tempo. Un esempio è "00:30:00" per 30 minuti. Se non viene specificato alcun valore, il timeout viene impostato sul valore predefinito "02:00:00".
No
maxConcurrentConnections Limite massimo di connessioni simultanee stabilite all'archivio dati durante l'esecuzione dell'attività. Specificare un valore solo quando si desidera limitare le connessioni simultanee. No

Esempio 1: Aggiungere dati

"activities":[
    {
        "name": "CopyToSQLServer",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<SQL Server output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "<source type>"
            },
            "sink": {
                "type": "SqlSink",
                "tableOption": "autoCreate",
                "writeBatchSize": 100000
            }
        }
    }
]

Esempio 2: Richiamare un stored procedure durante la copia

Per altre informazioni, vedere Richiamare una stored procedure da un sink SQL.

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

Copia parallela dal database SQL

Il connettore SQL Server nell'attività di copia fornisce il partizionamento dei dati incorporato per copiare i dati in parallelo. Le opzioni di partizionamento dei dati sono disponibili nella scheda Origine dell'attività di copia.

Screenshot delle opzioni di partizione

Quando si abilita la copia partizionata, l'attività di copia esegue query parallele sull'origine SQL Server per caricare i dati in base alle partizioni. Il grado di parallelismo è controllato dall'impostazione parallelCopies sull'attività di copia. Ad esempio, se si imposta su quattro, Data Factory genera ed esegue contemporaneamente quattro query in base all'opzione e alle impostazioni di partizione specificate e ogni query recupera una parte dei dati dal parallelCopies SQL Server.

È consigliabile abilitare la copia parallela con il partizionamento dei dati, soprattutto quando si caricano grandi quantità di dati dal SQL Server. Di seguito sono riportate le configurazioni consigliate per i diversi scenari: Quando si copiano dati nell'archivio dati basato su file, è consigliabile scrivere in una cartella come più file (specificare solo il nome della cartella), nel qual caso le prestazioni sono migliori rispetto alla scrittura in un singolo file.

Scenario Impostazioni consigliate
Carico completo da tabelle di grandi dimensioni, con partizioni fisiche. Opzione di partizione: partizioni fisiche della tabella.

Durante l'Data Factory rileva automaticamente le partizioni fisiche e copia i dati in base alle partizioni.

Per verificare se la tabella ha una partizione fisica o meno, è possibile fare riferimento a questa query.
Carico completo da tabelle di grandi dimensioni, senza partizioni fisiche, mentre con una colonna integer o datetime per il partizionamento dei dati. Opzioni di partizione: Partizione a intervalli dinamici.
Colonna di partizione (facoltativo): specificare la colonna usata per partizionare i dati. Se non viene specificato, viene usato l'indice o la colonna chiave primaria.
Limite superiore della partizione e limite inferiore della partizione (facoltativo): specificare se si vuole determinare lo stride della partizione. Questa operazione non consente di filtrare le righe della tabella, ma di partizionare e copiare tutte le righe della tabella. Se non specificato, l'attività di copia rileva automaticamente i valori.

Ad esempio, se la colonna di partizione "ID" ha valori da 1 a 100 e si imposta il limite inferiore su 20 e il limite superiore su 80, con copia parallela su 4, Data Factory recupera i dati da 4 partizioni - ID nell'intervallo <=20, [21, 50], [51, 80] e >=81, rispettivamente.
Caricare una grande quantità di dati usando una query personalizzata, senza partizioni fisiche, mentre con una colonna integer o date/datetime per il partizionamento dei dati. Opzioni di partizione: Partizione a intervalli dinamici.
Query: SELECT * FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>.
Colonna di partizione: Specificare la colonna usata per partizionare i dati.
Limite superiore della partizione e limite inferiore della partizione (facoltativo): specificare se si vuole determinare lo stride della partizione. Non è per filtrare le righe nella tabella, tutte le righe nel risultato della query verranno partizionate e copiate. Se non specificato, l'attività di copia rileva automaticamente il valore.

Durante l'esecuzione, Data Factory con il nome della colonna e gli intervalli di valori effettivi per ogni partizione e li invia ?AdfRangePartitionColumnName SQL Server.
Ad esempio, se la colonna di partizione "ID" ha valori da 1 a 100 e si imposta il limite inferiore su 20 e il limite superiore su 80, con copia parallela su 4, Data Factory recupera i dati da 4 partizioni- ID nell'intervallo <=20, [21, 50], [51, 80] e >=81, rispettivamente.

Di seguito sono disponibili altre query di esempio per scenari diversi:
1. Eseguire una query sull'intera tabella:
SELECT * FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition
2. Eseguire una query da una tabella con la selezione della colonna e filtri clausola where aggiuntivi:
SELECT <column_list> FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>
3. Eseguire query con sottoquery:
SELECT <column_list> FROM (<your_sub_query>) AS T WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>
4. Eseguire query con la partizione nella sottoquery:
SELECT <column_list> FROM (SELECT <your_sub_query_column_list> FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition) AS T

Procedure consigliate per caricare i dati con l'opzione di partizione:

  1. Scegliere colonna distintiva come colonna di partizione ,ad esempio chiave primaria o chiave univoca, per evitare l'avallata dei dati.
  2. Se la tabella include una partizione predefinita, usare l'opzione di partizione "Partizioni fisiche della tabella" per ottenere prestazioni migliori.
  3. Se si usa Azure Integration Runtime per copiare i dati, è possibile impostare " Unità di integrazione dati(DIU)" (>4) più grandi per usare più risorse di calcolo. Controllare gli scenari applicabili.
  4. " Gradodi parallelismodella copia " controlla i numeri di partizione, l'impostazione di questo numero troppo grande a volte danneggia le prestazioni, consiglia di impostare questo numero su (DIU o numero di nodi di IR self-hosted) * (da 2 a 4).

Esempio: carico completo da tabelle di grandi dimensioni con partizioni fisiche

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

Esempio: query con partizione a intervalli dinamici

"source": {
    "type": "SqlSource",
    "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>"
    }
}

Query di esempio per controllare la partizione fisica

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

Se la tabella ha una partizione fisica, "HasPartition" verrà visualizzato come "Sì" come segue.

Risultato della query SQL

Procedura consigliata per il caricamento dei dati in SQL Server

Quando si copiano dati in SQL Server, potrebbe essere necessario un comportamento di scrittura diverso:

  • Append: i dati di origine hanno solo nuovi record.
  • Upsert:i dati di origine hanno sia inserimenti che aggiornamenti.
  • Sovrascrivi: si vuole ricaricare ogni volta l'intera tabella delle dimensioni.
  • Scrivere con logica personalizzata:è necessaria un'elaborazione aggiuntiva prima dell'inserimento finale nella tabella di destinazione.

Vedere le rispettive sezioni per informazioni su come configurare in Azure Data Factory procedure consigliate.

Accodare dati

L'aggiunta di dati è il comportamento predefinito di questo connettore SQL Server sink. Azure Data Factory esegue un inserimento bulk per scrivere nella tabella in modo efficiente. È possibile configurare l'origine e il sink di conseguenza nell'attività di copia.

Eseguire l'upsert dei dati

Opzione 1: Quando si dispone di una grande quantità di dati da copiare, è possibile caricare in blocco tutti i record in una tabella di staging usando l'attività di copia, quindi eseguire un'attività stored procedure per applicare un'istruzione MERGE o INSERT/UPDATE in un'unica operazione.

attività Copy attualmente non supporta in modo nativo il caricamento dei dati in una tabella temporanea del database. Esiste un modo avanzato per configurarlo con una combinazione di più attività, vedere Ottimizzare gli scenari di upsert bulk del database SQL. Di seguito viene illustrato un esempio dell'uso di una tabella permanente come staging.

Ad esempio, in Azure Data Factory è possibile creare una pipeline con un attività Copy concatenato a un'attività stored procedure. La prima copia i dati dall'archivio di origine SQL Server una tabella di staging, ad esempio UpsertStagingTable, come nome della tabella nel set di dati. Quest'ultimo richiama quindi un stored procedure per unire i dati di origine dalla tabella di staging alla tabella di destinazione e pulire la tabella di staging.

Upsert

Nel database definire un stored procedure con logica MERGE, come nell'esempio seguente, a cui punta l'attività stored procedure precedente. Si supponga che la destinazione sia la tabella Marketing con tre colonne: ProfileID, State e Category. Eseguire l'upsert in base alla colonna ProfileID.

CREATE PROCEDURE [dbo].[spMergeData]
AS
BEGIN
    MERGE TargetTable AS target
    USING UpsertStagingTable 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 UpsertStagingTable
END

Opzione 2: È possibile scegliere di richiamare un'stored procedure all'interno dell'attività di copia. Questo approccio esegue ogni batch (come regolato dalla proprietà ) nella tabella di origine anziché usare l'inserimento bulk come approccio writeBatchSize predefinito nell'attività di copia.

Sovrascrivere l'intera tabella

È possibile configurare la proprietà preCopyScript in un sink dell'attività di copia. In questo caso, per ogni attività di copia eseguita, Azure Data Factory lo script. Quindi esegue la copia per inserire i dati. Ad esempio, per sovrascrivere l'intera tabella con i dati più recenti, specificare uno script per eliminare prima tutti i record prima di caricare i nuovi dati dall'origine.

Scrivere dati con logica personalizzata

I passaggi per scrivere dati con logica personalizzata sono simili a quelli descritti nella sezione Upsert data (Upsert data). Quando è necessario applicare un'elaborazione aggiuntiva prima dell'inserimento finale dei dati di origine nella tabella di destinazione, è possibile caricare in una tabella di staging e quindi richiamare un'attività stored procedure o richiamare un stored procedure nel sink dell'attività di copia per applicare i dati.

Richiamare una stored procedure da un sink SQL

Quando si copiano dati SQL Server database, è anche possibile configurare e richiamare un stored procedure specificato dall'utente con parametri aggiuntivi in ogni batch della tabella di origine. La stored procedure sfrutta i parametri con valori di tabella.

È possibile usare una stored procedure quando non si possono usare i meccanismi di copia predefiniti. Un esempio è quando si vuole applicare un'elaborazione aggiuntiva prima dell'inserimento finale dei dati di origine nella tabella di destinazione. Alcuni esempi di elaborazione aggiuntivi si verificano quando si vogliono unire colonne, cercare valori aggiuntivi e inserire in più tabelle.

Nell'esempio seguente viene illustrato come usare una stored procedure per eseguire un'operazione upsert in una tabella del database SQL Server. Si supponga che i dati di input e la tabella Marketing sink siano ognuno con tre colonne: ProfileID, State e Category. Eseguire l'upsert in base alla colonna ProfileID e applicarlo solo per una categoria specifica denominata "ProductA".

  1. Nel database definire il tipo di tabella con lo stesso nome di sqlWriterTableType. Lo schema del tipo di tabella è identico allo schema restituito dai dati di input.

    CREATE TYPE [dbo].[MarketingType] AS TABLE(
        [ProfileID] [varchar](256) NOT NULL,
        [State] [varchar](256) NOT NULL,
        [Category] [varchar](256) NOT NULL
    )
    
  2. Nel database definire l'stored procedure con lo stesso nome di sqlWriterStoredProcedureName. che gestisce i dati di input dell'origine specificata e li unisce nella tabella di output. Il nome del parametro del tipo di tabella nel stored procedure è uguale a tableName definito nel set di dati.

    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. In Azure Data Factory definire la sezione sink SQL nell'attività di copia come segue:

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

Proprietà del flusso di dati per mapping

Quando si trasformano i dati nel flusso di dati di mapping, è possibile leggere e scrivere nelle tabelle SQL Server Database. Per altre informazioni, vedere la trasformazione origine e la trasformazione sink nei flussi di dati per mapping.

Nota

Per accedere alle SQL Server locali, è necessario usare Azure Data Factory virtuale gestita usando un endpoint privato. Fare riferimento a questa esercitazione per i passaggi dettagliati.

Trasformazione origine

Nella tabella seguente sono elencate le proprietà supportate da SQL Server origine. È possibile modificare queste proprietà nella scheda Opzioni di origine.

Nome Descrizione Obbligatoria Valori consentiti Proprietà dello script del flusso di dati
Tabella Se si seleziona Tabella come input, il flusso di dati recupera tutti i dati dalla tabella specificata nel set di dati. No - -
Query Se si seleziona Query come input, specificare una query SQL per recuperare i dati dall'origine, che sostituisce qualsiasi tabella specificata nel set di dati. L'uso di query è un ottimo modo per ridurre le righe per i test o le ricerche.

La clausola Order By non è supportata, ma è possibile impostare un'istruzione SELECT FROM completa. È possibile usare anche funzioni di tabella definite dall'utente. select * from udfGetData() è una funzione definita dall'utente in SQL che restituisce una tabella che è possibile usare nel flusso di dati.
Esempio di query: Select * from MyTable where customerId > 1000 and customerId < 2000
No string query
Dimensioni dei batch Specificare una dimensione batch per la suddivisione in blocchi di dati di grandi dimensioni in operazioni di lettura. No Intero batchSize
Livello di isolamento Scegliere uno dei livelli di isolamento seguenti:
- Read Committed
- Lettura non di cui è stato eseguito ilcommitted (impostazione predefinita)
- Lettura ripetibile
- Serializzabile
- Nessuno (ignora il livello di isolamento)
No READ_COMMITTED
READ_UNCOMMITTED
REPEATABLE_READ
SERIALIZABLE
Nessuno
isolationLevel

SQL Server di script di origine

Quando si usa SQL Server come tipo di origine, lo script del flusso di dati associato è:

source(allowSchemaDrift: true,
    validateSchema: false,
    isolationLevel: 'READ_UNCOMMITTED',
    query: 'select * from MYTABLE',
    format: 'query') ~> SQLSource

Trasformazione sink

Nella tabella seguente sono elencate le proprietà supportate SQL Server sink. È possibile modificare queste proprietà nella scheda Opzioni sink.

Nome Descrizione Obbligatoria Valori consentiti Proprietà dello script del flusso di dati
Update (metodo) Specificare le operazioni consentite nella destinazione del database. Per impostazione predefinita, sono consentiti solo gli inserimenti.
Per aggiornare, eseguire l'upsert o eliminare righe, è necessaria una trasformazione Alter row per contrassegnare le righe per tali azioni.
true o false deletable
Inseribile
Aggiornabile
upsertable
Colonne chiave Per gli aggiornamenti, gli upsert e le eliminazioni, le colonne chiave devono essere impostate per determinare la riga da modificare.
Il nome della colonna scelto come chiave verrà usato come parte dell'aggiornamento successivo, upsert, delete. Pertanto, è necessario selezionare una colonna presente nel mapping sink.
No Array chiavi
Ignorare la scrittura di colonne chiave Se non si vuole scrivere il valore nella colonna chiave, selezionare "Ignora la scrittura delle colonne chiave". No true o false skipKeyWrites
azione Tabella Determina se ricreare o rimuovere tutte le righe dalla tabella di destinazione prima della scrittura.
- Nessuno: non verrà eseguita alcuna azione per la tabella.
- Ricrea: la tabella verrà eliminata e ricreata. Questa opzione è obbligatoria se si crea una nuova tabella in modo dinamico.
- Troncamento: tutte le righe della tabella di destinazione verranno rimosse.
No true o false Ricreare
truncate
Dimensioni dei batch Specificare il numero di righe scritte in ogni batch. Dimensioni batch più grandi migliorano l'ottimizzazione della compressione e della memoria, ma rischiano di causare eccezioni di memoria insufficiente durante la memorizzazione nella cache dei dati. No Intero batchSize
Pre e post-script SQL Specificare gli script SQL su più righe che verranno eseguiti prima (pre-elaborazione) e dopo la scrittura dei dati (post-elaborazione) nel database sink. No string preSQLs
postSQLs

SQL Server di script sink

Quando si usa SQL Server come tipo di sink, lo script del flusso di dati associato è:

IncomingStream sink(allowSchemaDrift: true,
    validateSchema: false,
    deletable:false,
    insertable:true,
    updateable:true,
    upsertable:true,
    keys:['keyColumn'],
    format: 'table',
    skipDuplicateMapInputs: true,
    skipDuplicateMapOutputs: true) ~> SQLSink

Mapping dei tipi di dati per SQL Server

Quando si copiano dati da e in SQL Server, vengono usati i mapping seguenti SQL Server tipi di dati per Azure Data Factory tipi di dati provvisori. Vedere Mapping dello schema e del tipo di dati per informazioni su come l'attività di copia esegue il mapping dello schema di origine e del tipo di dati al sink.

Tipo di dati di SQL Server Tipo di dati provvisorio di Azure Data Factory
bigint Int64
BINARY Byte[]
bit Boolean
char String, Char[]
Data Datetime
Datetime Datetime
datetime2 Datetime
Datetimeoffset DateTimeOffset
Decimal Decimal
FILESTREAM attribute (varbinary(max)) Byte[]
Float Double
image Byte[]
INT Int32
money Decimal
NCHAR String, Char[]
ntext String, Char[]
NUMERIC Decimal
NVARCHAR String, Char[]
real Single
rowversion Byte[]
smalldatetime Datetime
SMALLINT Int16
SMALLMONEY Decimal
sql_variant Oggetto
text String, Char[]
time TimeSpan
timestamp Byte[]
TINYINT Int16
UNIQUEIDENTIFIER Guid
varbinary Byte[]
varchar String, Char[]
Xml Stringa

Nota

Per i tipi di dati mappati al tipo provvisorio Decimal, attualmente attività Copy precisione fino a 28. Se si hanno dati che richiedono una precisione maggiore di 28, è consigliabile convertirli in una stringa in una query SQL.

Proprietà dell'attività Lookup

Per altre informazioni sulle proprietà, vedere Attività Lookup.

Proprietà dell'attività GetMetadata

Per altre informazioni sulle proprietà, vedere Attività GetMetadata

Uso di Always Encrypted

Quando si copiano dati da/SQL Server con Always Encrypted, usare il connettore ODBC generico e il driver ODBC SQL Server tramite Integration Runtime. Questo SQL Server connettore non supporta Always Encrypted ora.

Più in particolare:

  1. Configurare un'istanza self-hosted Integration Runtime se non se ne ha una. Per informazioni dettagliate, vedere Integration Runtime self-hosted.

  2. Scaricare il driver ODBC a 64 bit per SQL Server da quie installare nel Integration Runtime computer. Per altre informazioni sul funzionamento di questo driver, vedere Using Always Encrypted with the ODBC Driver for SQL Server.

  3. Creare un servizio collegato con tipo ODBC per connettersi al database SQL. Per usare l'autenticazione SQL, specificare la stringa di connessione ODBC come indicato di seguito e selezionare Autenticazione di base per impostare il nome utente e la password.

    Driver={ODBC Driver 17 for SQL Server};Server=<serverName>;Database=<databaseName>;ColumnEncryption=Enabled;KeyStoreAuthentication=KeyVaultClientSecret;KeyStorePrincipalId=<servicePrincipalKey>;KeyStoreSecret=<servicePrincipalKey>
    
  4. Creare di conseguenza un set di dati e un'attività di copia con il tipo ODBC. Per altre informazioni, vedere l'articolo connettore ODBC.

Risolvere i problemi di connessione

  1. Configurare l'istanza SQL Server per accettare connessioni remote. Avviare SQL Server Management Studio , fare clic con il pulsante destro del mouse sul server e scegliere Proprietà. Selezionare Connessioni dall'elenco e selezionare la casella di controllo Consenti connessioni remote al server .

    Abilitare le connessioni remote

    Per i passaggi dettagliati, vedere Configurare l'opzione di configurazione del server di accesso remoto.

  2. Avviare Gestione configurazione SQL Server. Espandere Configurazione di rete SQL Server per l'istanza prevista e selezionare Protocolli per MSSQLSERVER. I protocolli vengono visualizzati nel riquadro destro. Abilitare TCP/IP facendo clic con il pulsante destro del mouse su TCP/IP e scegliendo Abilita.

    Abilitare TCP/IP

    Per altre informazioni e modi alternativi per abilitare il protocollo TCP/IP, vedere Abilitare o disabilitare un protocollo di rete del server.

  3. Nella stessa finestra fare doppio clic su TCP/IP per avviare la finestra Proprietà TCP/IP.

  4. Passare alla scheda Indirizzi IP. Scorrere verso il basso per visualizzare la sezione IPAll. Annota la porta TCP. Il valore predefinito è 1433.

  5. Creare una regola per Windows Firewall nel computer per consentire il traffico in ingresso attraverso questa porta.

  6. Verificare la connessione: per connettersi SQL Server usando un nome completo, usare SQL Server Management Studio da un computer diverso. Un esempio è "<machine>.<domain>.corp.<company>.com,1433".

Passaggi successivi

Per un elenco degli archivi dati supportati come origini e sink dall'attività di copia in Azure Data Factory, vedere Archivi dati supportati.