Copiare dati da e verso Azure Synapse Analytics usando Azure Data Factory

Nota

Le informazioni di questo articolo sono valide per la versione 1 di Data Factory. Se si usa la versione corrente del servizio Data Factory, vedere Azure Synapse Analytics connector nella versione 2.

Questo articolo illustra come usare l'attività di copia in Azure Data Factory spostare i dati da e verso Azure Synapse Analytics. Si basa sull'articolo Attività di spostamento dati, che presenta una panoramica generale dello spostamento dei dati con l'attività di copia.

Suggerimento

Per ottenere prestazioni ottimali, usare PolyBase per caricare i dati in Azure Synapse Analytics. Per i dettagli, vedere la sezione Usare PolyBase per caricare dati in Azure Synapse Analytics. Per una procedura dettagliata con un caso d'uso, vedere Caricare 1 TB di dati in Azure Synapse Analytics in meno di 15 minuti con Azure Data Factory.

Scenari supportati

È possibile copiare dati da Azure Synapse Analytics agli archivi dati seguenti:

Category Archivio dati
Azure Archivio BLOB di Azure
Azure Data Lake Storage Gen1
Azure Cosmos DB (API SQL)
Database SQL di Azure
Azure Synapse Analytics
Indice di Ricerca cognitiva di Azure
Archivio tabelle di Azure
Database SQL Server
Oracle
File File system

È possibile copiare dati dagli archivi dati seguenti in Azure Synapse Analytics:

Category Archivio dati
Azure Archivio BLOB di Azure
Azure Cosmos DB (API SQL)
Azure Data Lake Storage Gen1
Database SQL di Azure
Azure Synapse Analytics
Archivio tabelle di Azure
Database Amazon Redshift
DB2
MySQL
Oracle
PostgreSQL
SAP Business Warehouse
SAP HANA
SQL Server
Sybase
Teradata
NoSQL Cassandra
MongoDB
File Amazon S3
File system
FTP
HDFS
SFTP
Altro HTTP generico
OData generico
ODBC generico
Salesforce
Tabella Web (tabella da HTML)

Suggerimento

Quando si copiano dati da SQL Server o database SQL di Azure a Azure Synapse Analytics, se la tabella non esiste nell'archivio di destinazione, Data Factory può creare automaticamente la tabella in Azure Synapse Analytics usando lo schema della tabella nell'archivio dati di origine. Per informazioni dettagliate vedere Creazione automatica della tabella.

Tipo di autenticazione supportato

Azure Synapse Analytics connettore supporta l'autenticazione di base.

Guida introduttiva

È possibile creare una pipeline con un'attività di copia che sposta i dati da e verso un Azure Synapse Analytics usando strumenti/API diversi.

Il modo più semplice per creare una pipeline che copia i dati da e verso Azure Synapse Analytics è usare la Copia guidata dati. Vedere Esercitazione: Caricare dati in Azure Synapse Analytics con Data Factory per una rapida procedura dettagliata sulla creazione di una pipeline usando la Copia guidata dati.

È anche possibile usare gli strumenti seguenti per creare una pipeline: Visual Studio, Azure PowerShell, Azure Resource Manager, API .NET e API REST. Vedere attività Copy tutorial per istruzioni dettagliate per creare una pipeline con un'attività di copia.

Se si usano gli strumenti o le API, eseguire la procedura seguente per creare una pipeline che sposta i dati da un archivio dati di origine a un archivio dati sink:

  1. Creare un data factory. Una data factory può contenere una o più pipeline.
  2. Creare i servizi collegati per collegare gli archivi di dati di input e output alla data factory. Ad esempio, se si copiano dati da un archivio BLOB di Azure a un Azure Synapse Analytics, si creano due servizi collegati per collegare l'account di archiviazione di Azure e Azure Synapse Analytics al data factory. Per le proprietà del servizio collegato specifiche per Azure Synapse Analytics, vedere la sezione proprietà del servizio collegato.
  3. Creare set di dati per rappresentare i dati di input e output per l'operazione di copia. Nell'esempio citato nel passaggio precedente, si crea un set di dati per specificare un contenitore BLOB e la cartella che contiene i dati di input. E si crea un altro set di dati per specificare la tabella nel Azure Synapse Analytics che contiene i dati copiati dall'archivio BLOB. Per le proprietà del set di dati specifiche per Azure Synapse Analytics, vedere la sezione relativa alle proprietà del set di dati.
  4. Creare una pipeline con un'attività di copia che accetta un set di dati come input e un set di dati come output. Nell'esempio indicato in precedenza si usa BlobSource come origine e SqlDWSink come sink per l'attività di copia. Analogamente, se si copia da Azure Synapse Analytics a Archiviazione BLOB di Azure, si usano SqlDWSource e BlobSink nell'attività di copia. Per le proprietà dell'attività di copia specifiche Azure Synapse Analytics, vedere la sezione proprietà dell'attività di copia. Per informazioni dettagliate su come usare un archivio dati come origine o come sink, fare clic sul collegamento nella sezione precedente per l'archivio dati.

Quando si usa la procedura guidata, le definizioni JSON per queste entità di data factory (servizi, set di dati e pipeline collegati) vengono create automaticamente. Quando si usano gli strumenti o le API, ad eccezione delle API .NET, usare il formato JSON per definire le entità di data factory. Per esempi con definizioni JSON per Data Factory che vengono usate per copiare dati da e verso Azure Synapse Analytics, vedere la sezione Esempi JSON di questo articolo.

Le sezioni seguenti forniscono informazioni dettagliate sulle proprietà JSON usate per definire Data Factory entità specifiche per Azure Synapse Analytics:

Proprietà del servizio collegato

Nella tabella seguente viene fornita una descrizione per gli elementi JSON specifici Azure Synapse Analytics servizio collegato.

Proprietà Descrizione Obbligatoria
type La proprietà type deve essere impostata su: AzureSqlDW
connectionString Specificare le informazioni necessarie per connettersi all'istanza Azure Synapse Analytics per la proprietà connectionString. È supportata solo l'autenticazione di base.

Importante

Configurare il firewall del database SQL di Azure e il server di database in modo da consentire ai servizi di Azure di accedere al server. Inoltre, se si copiano dati in Azure Synapse Analytics dall'esterno di Azure, anche da origini dati locali con gateway data factory, configurare l'intervallo di indirizzi IP appropriato per il computer che invia i dati Azure Synapse Analytics.

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 sulla creazione di set di dati. Le sezioni come struttura, disponibilità e criteri di un set di dati JSON sono simili per tutti i tipi di set di dati, ad esempio Azure SQL, BLOB di Azure, tabelle di Azure e così via.

La sezione typeProperties è diversa per ogni tipo di set di dati e contiene informazioni sulla posizione dei dati nell'archivio dati. La sezione typeProperties per il set di dati di tipo AzureSqlDWTable presenta le proprietà seguenti:

Proprietà Descrizione Obbligatoria
tableName Nome della tabella o della vista nel database Azure Synapse Analytics a cui fa riferimento il servizio collegato.

Proprietà dell'attività di copia

Per un elenco completo delle sezioni e delle proprietà disponibili per la definizione delle attività, fare riferimento all'articolo Creazione di pipeline. Per tutti i tipi di attività sono disponibili proprietà come nome, descrizione, tabelle di input e output e criteri.

Nota

L'attività di copia accetta solo un input e produce solo un output.

Le proprietà disponibili nella sezione typeProperties dell'attività variano invece in base al tipo di attività. Per l'attività di copia variano in base ai tipi di origine e sink.

SqlDWSource

In caso di origine di tipo SqlDWSource, nella sezione typeProperties sono disponibili le proprietà seguenti:

Proprietà Descrizione Valori consentiti Obbligatoria
sqlReaderQuery Usare la query personalizzata per leggere i dati. Stringa di query SQL. Ad esempio: selezionare * da MyTable. No
sqlReaderStoredProcedureName Nome della stored procedure che legge i dati dalla tabella di origine. Nome della stored procedure. L'ultima istruzione SQL deve essere un'istruzione SELECT nella stored procedure. No
storedProcedureParameters Parametri per la stored procedure. 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

Se sqlReaderQuery viene specificato per SqlDWSource, l'attività di copia esegue questa query sull'origine Azure Synapse Analytics per ottenere i dati.

In alternativa, è possibile specificare una stored procedure indicando i parametri sqlReaderStoredProcedureName e storedProcedureParameters (se la stored procedure accetta parametri).

Se non si specifica sqlReaderQuery o sqlReaderStoredProcedureName, le colonne definite nella sezione structure del set di dati JSON vengono usate per compilare una query da eseguire su Azure Synapse Analytics. Esempio: select column1, column2 from mytable. Se la definizione del set di dati non dispone della struttura, vengono selezionate tutte le colonne della tabella.

Esempio SqlDWSource

"source": {
    "type": "SqlDWSource",
    "sqlReaderStoredProcedureName": "CopyTestSrcStoredProcedureWithParameters",
    "storedProcedureParameters": {
        "stringData": { "value": "str3" },
        "identifier": { "value": "$$Text.Format('{0:yyyy}', SliceStart)", "type": "Int"}
    }
}

La stored procedure seguente:

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

SqlDWSink

SqlDWSink supporta le proprietà seguenti:

Proprietà Descrizione Valori consentiti Obbligatoria
sqlWriterCleanupScript Specificare una query da eseguire nell'attività di copia per pulire i dati di una sezione specifica. Per informazioni dettagliate, vedere la sezione relativa alla ripetibilità. Istruzione di query. No
allowPolyBase Indica se usare PolyBase, quando applicabile, invece del meccanismo BULKINSERT.

L'uso di PolyBase è il modo consigliato per caricare i dati Azure Synapse Analytics. Per informazioni dettagliate, vedere Usare PolyBase per caricare Azure Synapse Analytics dati nella sezione.
True
False (impostazione predefinita)
No
polyBaseSettings Gruppo di proprietà che è possibile specificare quando la proprietà allowPolybase è impostata su true.   No
rejectValue Specifica il numero o la percentuale di righe che è possibile rifiutare prima che la query abbia esito negativo.

Per altre informazioni sulle opzioni di rifiuto di PolyBase, vedere la sezione Argomenti dell'argomento CREATE EXTERNAL TABLE (Transact-SQL).
0 (impostazione predefinita), 1, 2, … No
rejectType Indica se l'opzione rejectValue viene specificata come valore letterale o come percentuale. Value (impostazione predefinita), Percentage No
rejectSampleValue Determina il numero di righe da recuperare prima che PolyBase ricalcoli la percentuale di righe rifiutate. 1, 2, … Sì se rejectType è percentage
useTypeDefault Specifica come gestire valori mancanti nei file di testo delimitato quando PolyBase recupera i dati dal file di testo.

Per altre informazioni su questa proprietà, vedere la sezione Arguments (Argomenti) in CREATE EXTERNAL FILE FORMAT (Transact-SQL).
True, False (valore predefinito) No
writeBatchSize Inserisce dati nella tabella SQL quando la dimensione del buffer raggiunge writeBatchSize. Numero intero (numero di righe) No (valore predefinito: 10000)
writeBatchTimeout Tempo di attesa per l'operazione di inserimento batch da completare prima del timeout. timespan

Esempio: "00:30:00" (30 minuti).
No

Esempio SqlDWSink

"sink": {
    "type": "SqlDWSink",
    "allowPolyBase": true
}

Usare PolyBase per caricare i dati in Azure Synapse Analytics

L'uso di PolyBase è un modo efficiente per caricare grandi quantità di dati Azure Synapse Analytics con velocità effettiva elevata. L'uso di PolyBase consente un miglioramento significativo della velocità effettiva rispetto al meccanismo BULKINSERT predefinito. Vedere Copiare il numero di riferimento prestazioni con il confronto dettagliato. Per una procedura dettagliata con un caso d'uso, vedere Caricare 1 TB di dati in Azure Synapse Analytics in meno di 15 minuti con Azure Data Factory.

  • Se i dati di origine si trova nel BLOB di Azure o in Azure Data Lake Store e il formato è compatibile con PolyBase, è possibile copiare direttamente in Azure Synapse Analytics usando PolyBase. Vedere Copia diretta tramite PolyBase con i relativi dettagli.
  • Se l'archivio e il formato dei dati di origine non sono supportati in origine da PolyBase, è possibile usare la funzione copia di staging tramite PolyBase. Viene inoltre generata una migliore velocità effettiva tramite la conversione automatica dei dati nel formato compatibile con PolyBase e l'archiviazione dei dati in Archiviazione BLOB di Azure. Carica quindi i dati in Azure Synapse Analytics.

Impostare la proprietà su true come illustrato nell'esempio seguente per Azure Data Factory allowPolyBase usare PolyBase per copiare i dati Azure Synapse Analytics. Quando si imposta allowPolyBase su true, è possibile specificare proprietà specifiche di PolyBase usando il gruppo di proprietà polyBaseSettings. Per informazioni dettagliate sulle proprietà che è possibile usare con polyBaseSettings, vedere la sezione SqlDWSink .

"sink": {
    "type": "SqlDWSink",
    "allowPolyBase": true,
    "polyBaseSettings":
    {
        "rejectType": "percentage",
        "rejectValue": 10.0,
        "rejectSampleValue": 100,
        "useTypeDefault": true
    }
}

Copia diretta tramite PolyBase

Azure Synapse Analytics PolyBase supporta direttamente BLOB di Azure e Azure Data Lake Store (usando l'entità servizio) come origine e con requisiti di formato di file specifici. Se i dati di origine soddisfano i criteri descritti in questa sezione, è possibile copiare direttamente dall'archivio dati di origine Azure Synapse Analytics usando PolyBase. In caso contrario è possibile usare la copia di staging tramite PolyBase.

Suggerimento

Per copiare i dati da Data Lake Store a Azure Synapse Analytics in modo efficiente, vedere Azure Data Factory rende ancora più semplice e pratico individuare informazioni dettagliate dai dati quando si usa Data Lake Store con Azure Synapse Analytics.

Se i requisiti non vengono soddisfatti, Azure Data Factory controlla le impostazioni e usa automaticamente il meccanismo BULKINSERT per lo spostamento dei dati.

  1. Il servizio collegato di origine è di tipo AzureStorage o AzureDataLakeStore con autenticazione dell'entità servizio.

  2. Il set di dati di input è di tipo AzureBlob o AzureDataLakeStore e il tipo di formato nelle proprietà type è OrcFormat****ParquetFormat o TextFormat, con le configurazioni seguenti:

    1. rowDelimiter deve essere \n.

    2. nullValue è impostato su stringa vuota ("") o treatEmptyAsNull è impostato su true.

    3. encodingName è impostato su utf-8, ovvero il valore predefinito.

    4. escapeChar, quoteChar, firstRowAsHeader e skipLineCount non sono specificati.

    5. compression può essere no compression, GZip o Deflate.

      "typeProperties": {
       "folderPath": "<blobpath>",
       "format": {
           "type": "TextFormat",
           "columnDelimiter": "<any delimiter>",
           "rowDelimiter": "\n",
           "nullValue": "",
           "encodingName": "utf-8"
       },
       "compression": {
           "type": "GZip",
           "level": "Optimal"
       }
      },
      
  3. Non è disponibile alcuna impostazione skipHeaderLineCount in BlobSource o AzureDataLakeStore per l'attività di copia nella pipeline.

  4. Non è disponibile alcuna impostazione sliceIdentifierColumnName in SqlDWSink per l'attività di copia nella pipeline. PolyBase garantisce che tutti i dati verranno aggiornati o che nessun dato verrà aggiornato in una singola esecuzione. Per ottenere la ripetibilità, è possibile usare sqlWriterCleanupScript.

  5. Nell'attività di copia associata non viene usato alcun valore columnMapping.

copia di staging tramite PolyBase

Quando i dati di origine non soddisfano i criteri introdotti nella sezione precedente, è possibile abilitare la copia dei dati tramite una gestione temporanea temporanea Archiviazione BLOB di Azure (non può essere Archiviazione Premium). In questo caso, Azure Data Factory esegue automaticamente trasformazioni sui dati per soddisfare i requisiti di formato dei dati di PolyBase, quindi usa PolyBase per caricare i dati in Azure Synapse Analytics e infine pulire i dati temporanei dall'archivio BLOB. Per informazioni dettagliate sul funzionamento generale della copia dei dati tramite un BLOB di Azure di staging, vedere la sezione Copia di staging .

Nota

Quando si copiano dati da un archivio dati locale in Azure Synapse Analytics usando PolyBase e gestione temporanea, se la versione del gateway Gestione dati è inferiore alla 2.4, JRE (Java Runtime Environment) è necessario nel computer gateway usato per trasformare i dati di origine nel formato corretto. È consigliabile aggiornare il gateway installando la versione più recente per evitare tale dipendenza.

Per usare questa funzionalità, creare un servizio collegato Archiviazione di Azure che faccia riferimento all'account di archiviazione di Azure contenente l'archivio BLOB provvisorio e quindi specificare le proprietà enableStaging e stagingSettings per l'attività di copia come illustrato nel codice seguente:

"activities":[
{
    "name": "Sample copy activity from SQL Server to Azure Synapse Analytics via PolyBase",
    "type": "Copy",
    "inputs": [{ "name": "OnpremisesSQLServerInput" }],
    "outputs": [{ "name": "AzureSQLDWOutput" }],
    "typeProperties": {
        "source": {
            "type": "SqlSource",
        },
        "sink": {
            "type": "SqlDwSink",
            "allowPolyBase": true
        },
        "enableStaging": true,
        "stagingSettings": {
            "linkedServiceName": "MyStagingBlob"
        }
    }
}
]

Procedure consigliate per l'uso di PolyBase

Le sezioni seguenti forniscono procedure consigliate aggiuntive a quelle indicate in Procedure consigliate perAzure Synapse Analytics .

Autorizzazione database obbligatoria

Per usare PolyBase, è necessario che l'utente venga usato per caricare i dati Azure Synapse Analytics dispone dell'autorizzazione "CONTROL" per il database di destinazione. Un modo per ottenere questo risultato consiste nell'aggiungere tale utente come membro del ruolo "db_owner". Informazioni su come eseguire questa operazione sono disponibili nella sezione seguente.

Limitazione alle dimensioni di righe e al tipo di dati

Le operazioni di caricamento di PolyBase sono limitate al caricamento di righe inferiori a 1 MB che non possono essere caricate in VARCHR(MAX), NVARCHAR(MAX) o VARBINARY(MAX). Vedere qui.

Se sono presenti dati di origine con righe di dimensioni superiori a 1 MB, è consigliabile suddividere verticalmente le tabelle di origine in tabelle più piccole, in cui le dimensioni massime delle righe di ogni tabella non superano il limite previsto. Le tabelle più piccole possono quindi essere caricate usando PolyBase e unite in Azure Synapse Analytics.

Azure Synapse Analytics classe di risorse

Per ottenere la velocità effettiva migliore possibile, è consigliabile assegnare una classe di risorse più grande all'utente usato per caricare i dati Azure Synapse Analytics tramite PolyBase. Per eseguire questa operazione, seguire la procedura descritta in Esempio di modifica della classe di risorse di un utente.

tableName in Azure Synapse Analytics

La tabella seguente fornisce esempi relativi a come specificare la proprietà tableName nel set di dati JSON per diverse combinazioni di nomi di schema e di tabella.

Schema di database Nome tabella Proprietà JSON tableName
dbo MyTable MyTable o dbo.MyTable o [dbo].[MyTable]
dbo1 MyTable dbo1.MyTable o [dbo1].[MyTable]
dbo My.Table [My.Table] o [dbo].[My.Table]
dbo1 My.Table [dbo1].[My.Table]

Se viene visualizzato l'errore seguente, potrebbe essersi verificato un problema con il valore specificato per la proprietà tableName. Per informazioni sul modo corretto di specificare i valori per la proprietà JSON tableName, vedere la relativa tabella.

Type=System.Data.SqlClient.SqlException,Message=Invalid object name 'stg.Account_test'.,Source=.Net SqlClient Data Provider

Colonne con valori predefiniti

La funzionalità PolyBase in Data Factory accetta attualmente lo stesso numero di colonne disponibili nella tabella di destinazione. Se si ha una tabella con quattro colonne di cui una definita con un valore predefinito, ad esempio, i dati di input dovranno comunque contenere quattro colonne. Se si specifica un set di dati di input con 3 colonne, si verificherà un errore simile al messaggio seguente:

All columns of the table must be specified in the INSERT BULK statement.

Il valore NULL è una forma speciale di valore predefinito. Se la colonna ammette valori Null, i dati di input (nel BLOB) per tale colonna possono essere vuoti, ma non possono essere mancanti dal set di dati di input. PolyBase inserisce NULL nel Azure Synapse Analytics.

Creazione automatica della tabella

Se si usa la Copia guidata per copiare dati da SQL Server o database SQL di Azure a Azure Synapse Analytics e la tabella che corrisponde alla tabella di origine non esiste nell'archivio di destinazione, Data Factory può creare automaticamente la tabella nel data warehouse usando lo schema della tabella di origine.

Data Factory crea la tabella nell'archivio di destinazione con lo stesso nome della tabella nell'archivio dati di origine. I tipi di dati per le colonne vengono scelti in base al mapping dei tipi seguenti. Se necessario, esegue le conversioni del tipo per risolvere eventuali incompatibilità tra gli archivi di origine e di destinazione. Usa inoltre la distribuzione di tabella Round Robin.

Tipo di colonna di origine del Database SQL Tipo Azure Synapse Analytics colonna di destinazione (limitazione delle dimensioni)
Int Int
BigInt BigInt
SmallInt SmallInt
TinyInt TinyInt
bit bit
Decimal Decimal
Numeric Decimal
Float Float
Money Money
Real Real
SmallMoney SmallMoney
Binary Binary
Varbinary Varbinary (fino a 8000)
Data Data
Datetime Datetime
DateTime2 DateTime2
Ora Ora
DateTimeOffset DateTimeOffset
SmallDateTime SmallDateTime
Testo Varchar (fino a 8000)
NText NVarChar (fino a 4000)
Immagine VarBinary (fino a 8000)
UniqueIdentifier UniqueIdentifier
Char Char
NChar NChar
VarChar VarChar (fino a 8000)
NVarChar NVarChar (fino a 4000)
Xml Varchar (fino a 8000)

Ripetibilità durante la copia

Quando si copiano dati in un database di SQL Server/SQL di Azure da altri archivi dati, è necessario definire criteri di ripetibilità per evitare risultati imprevisti.

Quando si copiano dati in un database SQL Server/SQL di Azure, per impostazione predefinita l'attività di copia ACCODA il set di dati alla tabella di sink predefinita. Ad esempio, quando si copiano dati da un'origine file con estensione csv (valori delimitati da virgole) composta da due record in un database di SQL Server/SQL di Azure, la tabella assume l'aspetto seguente:

ID    Product        Quantity    ModifiedDate
...    ...            ...            ...
6    Flat Washer    3            2015-05-01 00:00:00
7     Down Tube    2            2015-05-01 00:00:00

Si supponga di aver trovato degli errori nel file di origine e di aver aggiornato la quantità di Down Tube da 2 a 4 nel file di origine. Se si esegue nuovamente la sezione di dati per quel periodo, saranno disponibili due nuovi record accodati al database di SQL Server/SQL di Azure. L'esempio seguente presuppone che in nessuna delle colonne della tabella sia presente il vincolo di chiave primaria.

ID    Product        Quantity    ModifiedDate
...    ...            ...            ...
6    Flat Washer    3            2015-05-01 00:00:00
7     Down Tube    2            2015-05-01 00:00:00
6    Flat Washer    3            2015-05-01 00:00:00
7     Down Tube    4            2015-05-01 00:00:00

Per evitare questa situazione, è necessario specificare la semantica UPSERT usando uno dei due meccanismi illustrati di seguito.

Nota

In base ai criteri di ripetizione dei tentativi specificati, è possibile ripetere automaticamente l'esecuzione di una sezione anche in Data Factory di Azure.

Meccanismo 1

È possibile usare la proprietà sqlWriterCleanupScript per eseguire un'azione di pulizia prima dell'esecuzione di una sezione.

"sink":  
{ 
  "type": "SqlSink", 
  "sqlWriterCleanupScript": "$$Text.Format('DELETE FROM table WHERE ModifiedDate >= \\'{0:yyyy-MM-dd HH:mm}\\' AND ModifiedDate < \\'{1:yyyy-MM-dd HH:mm}\\'', WindowStart, WindowEnd)"
}

In questo caso, durante la copia di una sezione viene prima eseguito uno script di pulizia che elimina i dati dalla tabella SQL corrispondente alla sezione. L'attività di copia inserirà i dati nella tabella SQL in un momento successivo.

Se la sezione viene eseguita nuovamente, la quantità risulterà aggiornata come desiderato.

ID    Product        Quantity    ModifiedDate
...    ...            ...            ...
6    Flat Washer    3            2015-05-01 00:00:00
7     Down Tube    4            2015-05-01 00:00:00

Si supponga, ad esempio, che il record Flat Washer venga rimosso dal file con estensione csv originale. Se la sezione viene eseguita nuovamente, si ottiene il risultato seguente:

ID    Product        Quantity    ModifiedDate
...    ...            ...            ...
7     Down Tube    4            2015-05-01 00:00:00

Nessuna nuova operazione è stata eseguita. L'attività di copia ha eseguito lo script di pulizia per eliminare i dati corrispondenti alla sezione. Quindi, ha letto l'input dal CSV (che conteneva solo 1 record) e lo ha inserito nella tabella.

Meccanismo 2

Importante

Attualmente sliceIdentifierColumnName non è supportato per Azure Synapse Analytics.

Un altro meccanismo per ottenere la ripetibilità prevede la disponibilità di una colonna dedicata (sliceIdentifierColumnName) nella tabella di destinazione. Questa colonna viene usata da Data factory di Azure per garantire che l'origine e la destinazione rimangano sincronizzate. Questo approccio può essere usato solo quando è disponibile una certa flessibilità nella modifica o nella definizione dello schema della tabella SQL di destinazione.

La colonna viene usata da Data factory di Azure per scopi di ripetibilità e nel corso del processo Data factory di Azure non apporterà alcuna modifica allo schema della tabella. Per applicare questo approccio, è possibile seguire questa procedura:

  1. Definire una colonna di tipo binario (32) nella tabella SQL di destinazione, in cui non sia presente alcun vincolo. Ai fini di questo esempio, la colonna viene denominata "ColumnForADFuseOnly".

  2. Usarla nell'attività di copia come segue:

    "sink":  
    { 
    
        "type": "SqlSink", 
        "sliceIdentifierColumnName": "ColumnForADFuseOnly"
    }
    

Data factory di Azure popolerà la colonna in modo che l'origine e la destinazione risultino sincronizzate. L'utente non potrà usare i valori della colonna al di fuori di questo contesto.

Analogamente al meccanismo 1, l'attività di copia pulisce prima i dati della sezione specificata dalla tabella SQL di destinazione, quindi esegue normalmente l'attività di copia per inserire i dati dall'origine alla destinazione della sezione.

Mapping dei tipi per Azure Synapse Analytics

Come accennato nell'articolo sulle attività di spostamento dei dati , l'attività di copia esegue conversioni automatiche da tipi di origine a tipi di sink con l'approccio seguente in 2 passaggi:

  1. Conversione dai tipi di origine nativi al tipo .NET
  2. Conversione dal tipo .NET al tipo di sink nativo

Quando si spostano dati & da Azure Synapse Analytics, vengono usati i mapping seguenti dal tipo SQL al tipo .NET e viceversa.

Il mapping è identico a quello SQL Server mapping dei tipi di dati per ADO.NET.

Tipo di motore di database di SQL Server Tipo .NET Framework
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 Object *
text String, Char[]
time TimeSpan
timestamp Byte[]
TINYINT Byte
UNIQUEIDENTIFIER Guid
varbinary Byte[]
varchar String, Char[]
Xml Xml

È anche possibile eseguire il mapping delle colonne del set di dati di origine alle colonne del set di dati sink nella definizione dell'attività di copia. Per altre informazioni, vedere Mapping delle colonne del set di dati in Azure Data Factory.

Esempi JSON per la copia di dati da e verso Azure Synapse Analytics

Gli esempi seguenti forniscono definizioni JSON di esempio che è possibile usare per creare una pipeline usando Visual Studio o Azure PowerShell. Illustrano come copiare dati da e verso Azure Synapse Analytics e Archiviazione BLOB di Azure. Tuttavia, i dati possono essere copiati direttamente da una delle origini in qualsiasi sink dichiarato qui usando l'attività di copia in Data factory di Azure.

Esempio: Copiare dati da Azure Synapse Analytics blob di Azure

L'esempio definisce le entità di Data Factory seguenti:

  1. Un servizio collegato di tipo AzureSqlDW.
  2. Un servizio collegato di tipo AzureStorage.
  3. Un set di dati di input di tipo AzureSqlDWTable.
  4. Un set di dati di output di tipo AzureBlob.
  5. Una pipeline con attività di copia che usa SqlDWSource e BlobSink.

L'esempio copia i dati di una serie temporale (oraria, giornaliera e così via) da una tabella Azure Synapse Analytics database a un BLOB ogni ora. Le proprietà JSON usate in questi esempi sono descritte nelle sezioni riportate dopo gli esempi.

Azure Synapse Analytics servizio collegato:

{
  "name": "AzureSqlDWLinkedService",
  "properties": {
    "type": "AzureSqlDW",
    "typeProperties": {
      "connectionString": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;User ID=<username>@<servername>;Password=<password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30"
    }
  }
}

Servizio collegato di archiviazione BLOB di Azure:

{
  "name": "StorageLinkedService",
  "properties": {
    "type": "AzureStorage",
    "typeProperties": {
      "connectionString": "DefaultEndpointsProtocol=https;AccountName=<accountname>;AccountKey=<accountkey>"
    }
  }
}

Azure Synapse Analytics di dati di input:

Nell'esempio si presuppone che sia stata creata una tabella "MyTable" in Azure Synapse Analytics e che contenga una colonna denominata "timestampcolumn" per i dati delle serie temporali.

Impostando "external": "true" si comunica al servizio Data Factory che il set di dati è esterno alla data factory e non è prodotto da un'attività al suo interno.

{
  "name": "AzureSqlDWInput",
  "properties": {
    "type": "AzureSqlDWTable",
    "linkedServiceName": "AzureSqlDWLinkedService",
    "typeProperties": {
      "tableName": "MyTable"
    },
    "external": true,
    "availability": {
      "frequency": "Hour",
      "interval": 1
    },
    "policy": {
      "externalData": {
        "retryInterval": "00:01:00",
        "retryTimeout": "00:10:00",
        "maximumRetry": 3
      }
    }
  }
}

Set di dati di output del BLOB di Azure:

I dati vengono scritti in un nuovo BLOB ogni ora (frequenza: ora, intervallo: 1). Il percorso della cartella per il BLOB viene valutato dinamicamente in base all'ora di inizio della sezione in fase di elaborazione. Il percorso della cartella usa le parti anno, mese, giorno e ora dell'ora di inizio.

{
  "name": "AzureBlobOutput",
  "properties": {
    "type": "AzureBlob",
    "linkedServiceName": "StorageLinkedService",
    "typeProperties": {
      "folderPath": "mycontainer/myfolder/yearno={Year}/monthno={Month}/dayno={Day}/hourno={Hour}",
      "partitionedBy": [
        {
          "name": "Year",
          "value": {
            "type": "DateTime",
            "date": "SliceStart",
            "format": "yyyy"
          }
        },
        {
          "name": "Month",
          "value": {
            "type": "DateTime",
            "date": "SliceStart",
            "format": "MM"
          }
        },
        {
          "name": "Day",
          "value": {
            "type": "DateTime",
            "date": "SliceStart",
            "format": "dd"
          }
        },
        {
          "name": "Hour",
          "value": {
            "type": "DateTime",
            "date": "SliceStart",
            "format": "HH"
          }
        }
      ],
      "format": {
        "type": "TextFormat",
        "columnDelimiter": "\t",
        "rowDelimiter": "\n"
      }
    },
    "availability": {
      "frequency": "Hour",
      "interval": 1
    }
  }
}

Attività di copia in una pipeline con SqlDWSource e BlobSink:

La pipeline contiene un'attività di copia configurata per usare i set di dati di input e output ed è programmata per essere eseguita ogni ora. Nella definizione JSON della pipeline, il tipo di origine è impostato su SqlDWSource e il tipo di sink è impostato su BlobSink. La query SQL specificata per la proprietà SqlReaderQuery consente di selezionare i dati da copiare nell'ultima ora.

{
  "name":"SamplePipeline",
  "properties":{
    "start":"2014-06-01T18:00:00",
    "end":"2014-06-01T19:00:00",
    "description":"pipeline for copy activity",
    "activities":[
      {
        "name": "AzureSQLDWtoBlob",
        "description": "copy activity",
        "type": "Copy",
        "inputs": [
          {
            "name": "AzureSqlDWInput"
          }
        ],
        "outputs": [
          {
            "name": "AzureBlobOutput"
          }
        ],
        "typeProperties": {
          "source": {
            "type": "SqlDWSource",
            "sqlReaderQuery": "$$Text.Format('select * from MyTable where timestampcolumn >= \\'{0:yyyy-MM-dd HH:mm}\\' AND timestampcolumn < \\'{1:yyyy-MM-dd HH:mm}\\'', WindowStart, WindowEnd)"
          },
          "sink": {
            "type": "BlobSink"
          }
        },
        "scheduler": {
          "frequency": "Hour",
          "interval": 1
        },
        "policy": {
          "concurrency": 1,
          "executionPriorityOrder": "OldestFirst",
          "retry": 0,
          "timeout": "01:00:00"
        }
      }
    ]
  }
}

Nota

Nell'esempio, la proprietà sqlReaderQuery è specificata per SqlDWSource. L'attività di copia esegue questa query sull Azure Synapse Analytics di origine per ottenere i dati.

In alternativa, è possibile specificare una stored procedure indicando i parametri sqlReaderStoredProcedureName e storedProcedureParameters (se la stored procedure accetta parametri).

Se non si specifica sqlReaderQuery o sqlReaderStoredProcedureName, le colonne definite nella sezione structure del set di dati JSON vengono usate per compilare una query (selezionare column1, column2 da mytable) per l'esecuzione in Azure Synapse Analytics. Se la definizione del set di dati non dispone della struttura, vengono selezionate tutte le colonne della tabella.

Esempio: Copiare dati da BLOB di Azure a Azure Synapse Analytics

L'esempio definisce le entità di Data Factory seguenti:

  1. Un servizio collegato di tipo AzureSqlDW.
  2. Un servizio collegato di tipo AzureStorage.
  3. Un set di dati di input di tipo AzureBlob.
  4. Un set di dati di output di tipo AzureSqlDWTable.
  5. Una pipeline con attività di copia che usa BlobSource e SqlDWSink.

L'esempio copia i dati di una serie temporale (oraria, giornaliera e così via) dal BLOB di Azure a una tabella in un database Azure Synapse Analytics ogni ora. Le proprietà JSON usate in questi esempi sono descritte nelle sezioni riportate dopo gli esempi.

Azure Synapse Analytics servizio collegato:

{
  "name": "AzureSqlDWLinkedService",
  "properties": {
    "type": "AzureSqlDW",
    "typeProperties": {
      "connectionString": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;User ID=<username>@<servername>;Password=<password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30"
    }
  }
}

Servizio collegato di archiviazione BLOB di Azure:

{
  "name": "StorageLinkedService",
  "properties": {
    "type": "AzureStorage",
    "typeProperties": {
      "connectionString": "DefaultEndpointsProtocol=https;AccountName=<accountname>;AccountKey=<accountkey>"
    }
  }
}

Set di dati di input del BLOB di Azure:

I dati vengono prelevati da un nuovo BLOB ogni ora (frequenza: ora, intervallo: 1). Il percorso della cartella e il nome del file per il BLOB vengono valutati dinamicamente in base all'ora di inizio della sezione in fase di elaborazione. Il percorso della cartella usa le parti anno, mese, e giorno dell'ora di inizio e il nome del file usa la parte dell'ora di inizio relativa all'ora. "external": l'impostazione "true" informa il servizio Data Factory che questa tabella è esterna al data factory e non viene prodotta da un'attività nel data factory.

{
  "name": "AzureBlobInput",
  "properties": {
    "type": "AzureBlob",
    "linkedServiceName": "StorageLinkedService",
    "typeProperties": {
      "folderPath": "mycontainer/myfolder/yearno={Year}/monthno={Month}/dayno={Day}",
      "fileName": "{Hour}.csv",
      "partitionedBy": [
        {
          "name": "Year",
          "value": {
            "type": "DateTime",
            "date": "SliceStart",
            "format": "yyyy"
          }
        },
        {
          "name": "Month",
          "value": {
            "type": "DateTime",
            "date": "SliceStart",
            "format": "MM"
          }
        },
        {
          "name": "Day",
          "value": {
            "type": "DateTime",
            "date": "SliceStart",
            "format": "dd"
          }
        },
        {
          "name": "Hour",
          "value": {
            "type": "DateTime",
            "date": "SliceStart",
            "format": "HH"
          }
        }
      ],
      "format": {
        "type": "TextFormat",
        "columnDelimiter": ",",
        "rowDelimiter": "\n"
      }
    },
    "external": true,
    "availability": {
      "frequency": "Hour",
      "interval": 1
    },
    "policy": {
      "externalData": {
        "retryInterval": "00:01:00",
        "retryTimeout": "00:10:00",
        "maximumRetry": 3
      }
    }
  }
}

Azure Synapse Analytics set di dati di output:

L'esempio copia i dati in una tabella denominata "MyTable" in Azure Synapse Analytics. Creare la tabella in Azure Synapse Analytics con lo stesso numero di colonne previsto per il file CSV BLOB. Alla tabella vengono aggiunte nuove righe ogni ora.

{
  "name": "AzureSqlDWOutput",
  "properties": {
    "type": "AzureSqlDWTable",
    "linkedServiceName": "AzureSqlDWLinkedService",
    "typeProperties": {
      "tableName": "MyOutputTable"
    },
    "availability": {
      "frequency": "Hour",
      "interval": 1
    }
  }
}

Copiare attività in una pipeline con BlobSource e SqlDWSink:

La pipeline contiene un'attività di copia configurata per usare i set di dati di input e output ed è programmata per essere eseguita ogni ora. Nella definizione JSON della pipeline il tipo di origine è impostato su BlobSource e il tipo di sink è impostato su SqlDWSink.

{
  "name":"SamplePipeline",
  "properties":{
    "start":"2014-06-01T18:00:00",
    "end":"2014-06-01T19:00:00",
    "description":"pipeline with copy activity",
    "activities":[
      {
        "name": "AzureBlobtoSQLDW",
        "description": "Copy Activity",
        "type": "Copy",
        "inputs": [
          {
            "name": "AzureBlobInput"
          }
        ],
        "outputs": [
          {
            "name": "AzureSqlDWOutput"
          }
        ],
        "typeProperties": {
          "source": {
            "type": "BlobSource",
            "blobColumnSeparators": ","
          },
          "sink": {
            "type": "SqlDWSink",
            "allowPolyBase": true
          }
        },
        "scheduler": {
          "frequency": "Hour",
          "interval": 1
        },
        "policy": {
          "concurrency": 1,
          "executionPriorityOrder": "OldestFirst",
          "retry": 0,
          "timeout": "01:00:00"
        }
      }
    ]
  }
}

Per una procedura dettagliata, vedere l'articolo Caricare 1 TB in Azure Synapse Analytics in meno di 15 minuti con Azure Data Factory e Caricare dati con Azure Data Factory nella documentazione di Azure Synapse Analytics.

Ottimizzazione delle prestazioni

Per informazioni sui fattori chiave che influiscono sulle prestazioni dello spostamento dei dati, ovvero dell'attività di copia, in Azure Data Factory e sui vari modi per ottimizzare tali prestazioni, vedere la Guida alle prestazioni delle attività di copia e all'ottimizzazione.