Menyalin dan mentransformasi data di Azure SQL Database menggunakan Azure Data Factory atau Azure Synapse Analytics
BERLAKU UNTUK:
Azure Data Factory Azure Synapse Analytics
Tip
Cobalah Data Factory di Microsoft Fabric, solusi analitik all-in-one untuk perusahaan. Microsoft Fabric mencakup semuanya mulai dari pergerakan data hingga ilmu data, analitik real time, kecerdasan bisnis, dan pelaporan. Pelajari cara memulai uji coba baru secara gratis!
Artikel ini menguraikan cara menggunakan Salin Aktivitas di alur Azure Data Factory atau Azure Synapse untuk menyalin data dari dan ke penyimpanan Azure SQL Database, dan menggunakan Data Flow untuk mentransformasi data di Azure SQL Database. Untuk mempelajari lebih lanjut, baca artikel pengantar untuk Azure Data Factory atau Azure Synapse Analytics.
Kemampuan yang didukung
Konektor Azure SQL Database ini didukung untuk kemampuan berikut:
| Kemampuan yang didukung | IR | Titik akhir privat terkelola |
|---|---|---|
| Salin aktivitas (sumber/sink) | (1) (2) | ✓ |
| Memetakan aliran data (sumber/sink) | (1) | ✓ |
| Aktivitas pencarian | (1) (2) | ✓ |
| Aktivitas GetMetadata | (1) (2) | ✓ |
| Aktivitas skrip | (1) (2) | ✓ |
| Aktivitas prosedur tersimpan | (1) (2) | ✓ |
① Runtime integrasi Azure ② Runtime integrasi yang dihost sendiri
Untuk aktivitas Salin, konektor Azure SQL Database ini mendukung fungsi-fungsi ini:
- Menyalin data dengan menggunakan autentikasi SQL dan autentikasi token Aplikasi Microsoft Entra dengan perwakilan layanan atau identitas terkelola untuk sumber daya Azure.
- Sebagai sumber, mengambil data dengan menggunakan kueri SQL atau prosedur tersimpan. Anda juga dapat memilih untuk salin paralel dari sumber Azure SQL Database, lihat bagian Salinan paralel dari database SQL untuk detailnya.
- Sebagai sink, secara otomatis membuat tabel tujuan jika tidak ada berdasarkan skema sumber; menambahkan data ke tabel atau memanggil prosedur tersimpan dengan logika kustom selama salinan.
Jika Anda menggunakan tingkat tanpa serverAzure SQL Database, catat ketika server dijeda, aktivitas berjalan gagal alih-alih menunggu resume otomatis siap. Anda dapat menambahkan aktivitas mencoba kembali atau merantai aktivitas tambahan untuk memastikan server hidup pada eksekusi aktual.
Penting
Jika Anda menyalin data dengan menggunakan runtime integrasi Azure, konfigurasikan aturan firewall tingkat server sehingga layanan Azure dapat mengakses server. Jika Anda menyalin data dengan menggunakan Runtime integrasi yang dihost sendiri, konfigurasikan firewall untuk memungkinkan rentang IP yang sesuai. Rentang ini mencakup IP komputer yang digunakan untuk menyambungkan ke Azure SQL Database.
Memulai
Untuk melakukan aktivitas Salin dengan alur, Anda dapat menggunakan salah satu alat atau SDK berikut:
- Alat Penyalinan Data
- Portal Microsoft Azure
- SDK .NET
- SDK Python
- Azure PowerShell
- REST API
- Templat Azure Resource Manager
Buat layanan tertaut Azure SQL Database menggunakan antarmuka pengguna
Gunakan langkah-langkah berikut untuk membuat layanan tertaut Azure SQL Database di antarmuka pengguna portal Microsoft Azure.
Telusuri ke tab Kelola di ruang kerja Azure Data Factory atau Synapse Anda dan pilih Layanan Tertaut, lalu klik Baru:
Cari SQL dan pilih konektor Azure SQL Database.
Konfigurasikan detail layanan, uji koneksi, dan buat layanan tertaut baru.
Detail konfigurasi konektor
Bagian berikut ini menyediakan detail tentang properti yang digunakan untuk menentukan entitas alur Azure Data Factory atau Synapse khusus untuk konektor Azure SQL Database.
Properti layanan tertaut
Properti generik ini didukung untuk layanan tertaut Azure SQL Database:
| Properti | Deskripsi | Wajib |
|---|---|---|
| jenis | Jenis properti harus diatur ke AzureSqlDatabase. | Ya |
| connectionString | Tentukan informasi yang diperlukan untuk menyambungkan ke instans Azure SQL Database untuk properti connectionString. Anda juga dapat meletakkan kata sandi atau kunci perwakilan layanan di Azure Key Vault. Jika merupakan autentikasi SQL, tarik konfigurasi password keluar dari string koneksi. Untuk informasi lebih, lihat contoh JSON setelah tabel dan info masuk Store di Azure Key Vault. |
Ya |
| azureCloudType | Untuk autentikasi perwakilan layanan, tentukan jenis lingkungan cloud Azure tempat aplikasi Microsoft Entra Anda didaftarkan. Nilai yang diizinkan adalah AzurePublic, AzureChina, AzureUsGovernment, dan AzureGermany. Secara default, pabrik data atau lingkungan cloud alur Synapse digunakan. |
No |
| alwaysEncryptedSettings | Tetapkan informasi alwaysencryptedsettings yang diperlukan untuk mengaktifkan Always Encrypted untuk melindungi data sensitif yang disimpan di server SQL dengan menggunakan identitas terkelola atau prinsip layanan. Untuk informasi selengkapnya, lihat contoh JSON mengikuti tabel dan bagian Menggunakan Always Encrypted. Jika tidak ditentukan, pengaturan default selalu dienkripsi akan dinonaktifkan. | No |
| connectVia | Runtime integrasi ini digunakan untuk menyambungkan ke penyimpanan data. Anda dapat menggunakan runtime integrasi Azure atau runtime integrasi yang dihost sendiri jika penyimpanan data Anda berada di jaringan pribadi. Jika tidak ditentukan, runtime integrasi Azure default digunakan. | No |
Untuk jenis autentikasi yang berbeda, lihat setiap bagian berikut tentang properti, prasyarat dan sampel JSON spesifik:
- Autentikasi SQL
- Autentikasi perwakilan layanan
- Autentikasi identitas terkelola yang ditetapkan sistem
- Autentikasi identitas terkelola yang ditetapkan pengguna
Tip
Jika Anda menemui kesalahan dengan kode kesalahan "UserErrorFailedToConnectToSqlServer" dan pesan seperti "Batas sesi untuk database adalah XXX dan telah tercapai," tambahkan Pooling=false ke string koneksi Anda dan coba lagi. Pooling=false juga direkomendasikan untuk pengaturan layanan tertaut jenis SHIR (Self Hosted Integration Runtime). Pooling dan parameter koneksi lainnya dapat ditambahkan sebagai nama dan nilai parameter baru di bagian Properti koneksi tambahan dari formulir pembuatan layanan tertaut.
Autentikasi SQL
Untuk menggunakan jenis autentikasi seperti autentikasi SQL, tentukan properti generik yang dijelaskan di bagian sebelumnya.
Contoh: menggunakan autentikasi 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"
}
}
}
Contoh: kata sandi di 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"
}
}
}
Contoh: Gunakan 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"
}
}
}
Autentikasi perwakilan layanan
Untuk menggunakan autentikasi perwakilan layanan, selain properti generik yang dijelaskan di bagian sebelumnya, tentukan properti berikut ini:
| Properti | Deskripsi | Wajib diisi |
|---|---|---|
| servicePrincipalId | Menentukan ID klien aplikasi. | Ya |
| servicePrincipalKey | Tentukan kunci aplikasi. Tandai bidang ini sebagai SecureString untuk menyimpannya dengan aman, atau referensikan rahasia yang disimpan di Azure Key Vault. | Ya |
| penyewa | Menentukan informasi penyewa, seperti nama domain atau ID penyewa, tempat aplikasi Anda berada. Ambil dengan mengarahkan mouse ke sudut kanan atas portal Microsoft Azure. | Ya |
Anda juga harus mengikuti langkah-langkah di bawah:
Buat aplikasi Microsoft Entra dari portal Azure. Catat nama aplikasi dan nilai berikut yang menentukan layanan tertaut:
- ID aplikasi
- Kunci Aplikasi
- ID Penyewa
Provisikan administrator Microsoft Entra untuk server Anda di portal Azure jika Anda belum melakukannya. Administrator Microsoft Entra harus merupakan pengguna Microsoft Entra atau grup Microsoft Entra, tetapi tidak dapat menjadi perwakilan layanan. Langkah ini dilakukan sehingga, pada langkah berikutnya, Anda dapat menggunakan identitas Microsoft Entra untuk membuat pengguna database mandiri untuk perwakilan layanan.
Buat pengguna database mandiri untuk perwakilan layanan. Koneksi ke database dari atau tempat Anda ingin menyalin data dengan menggunakan alat seperti SQL Server Management Studio, dengan identitas Microsoft Entra yang memiliki setidaknya izin UBAH PENGGUNA APA PUN. Jalankan T-SQL berikut:
CREATE USER [your application name] FROM EXTERNAL PROVIDER;Berikan izin yang diperlukan perwakilan layanan seperti yang biasa Anda lakukan untuk pengguna SQL atau orang lain. Jalankan kode berikut. Untuk opsi selengkapnya, lihat dokumen ini.
ALTER ROLE [role name] ADD MEMBER [your application name];Konfigurasikan layanan tertaut Azure SQL Database di ruang kerja Azure Data Factory atau Synapse.
Contoh layanan tertaut yang menggunakan autentikasi perwakilan layanan
{
"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"
}
}
}
Autentikasi identitas terkelola yang ditetapkan sistem
Pabrik data atau ruang kerja Synapse dapat dikaitkan dengan identitas terkelola yang ditetapkan sistem untuk sumber daya Azure yang mewakili layanan saat mengautentikasi ke sumber daya lain di Azure. Anda dapat menggunakan identitas terkelola ini untuk autentikasi Azure SQL Database. Pabrik yang ditunjuk atau ruang kerja Synapse dapat mengakses dan menyalin data dari atau ke database Anda dengan menggunakan identitas ini.
Untuk menggunakan autentikasi identitas terkelola yang ditetapkan sistem, tentukan properti generik yang dijelaskan di bagian sebelumnya, dan ikuti langkah-langkah ini.
Provisikan administrator Microsoft Entra untuk server Anda di portal Azure jika Anda belum melakukannya. Administrator Microsoft Entra dapat menjadi pengguna Microsoft Entra atau grup Microsoft Entra. Jika Anda memberi grup dengan identitas terkelola peran admin, lompati langkah 3 dan 4. Admin memiliki akses penuh ke database.
Buat pengguna database mandiri untuk identitas terkelola. Koneksi ke database dari atau tempat Anda ingin menyalin data dengan menggunakan alat seperti SQL Server Management Studio, dengan identitas Microsoft Entra yang memiliki setidaknya izin UBAH PENGGUNA APA PUN. Jalankan T-SQL berikut:
CREATE USER [your_resource_name] FROM EXTERNAL PROVIDER;Berikan izin yang diperlukan identitas terkelola seperti yang biasa Anda lakukan untuk pengguna SQL dan orang lain. Jalankan kode berikut. Untuk opsi selengkapnya, lihat dokumen ini.
ALTER ROLE [role name] ADD MEMBER [your_resource_name];Konfigurasikan layanan tertaut Azure SQL Database.
Contoh
{
"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"
}
}
}
Autentikasi identitas terkelola yang ditetapkan pengguna
Pabrik data atau ruang kerja Synapse dapat dikaitkan dengan identitas terkelola yang ditetapkan pengguna yang mewakili layanan saat mengautentikasi ke sumber daya lain di Azure. Anda dapat menggunakan identitas terkelola ini untuk autentikasi Azure SQL Database. Pabrik yang ditunjuk atau ruang kerja Synapse dapat mengakses dan menyalin data dari atau ke database Anda dengan menggunakan identitas ini.
Untuk menggunakan autentikasi identitas terkelola yang ditetapkan pengguna, selain properti generik yang dijelaskan di bagian sebelumnya, tentukan properti berikut ini:
| Properti | Deskripsi | Wajib diisi |
|---|---|---|
| informasi masuk | Tentukan identitas terkelola yang ditetapkan pengguna sebagai objek kredensial. | Ya |
Anda juga harus mengikuti langkah-langkah di bawah:
Provisikan administrator Microsoft Entra untuk server Anda di portal Azure jika Anda belum melakukannya. Administrator Microsoft Entra dapat menjadi pengguna Microsoft Entra atau grup Microsoft Entra. Jika Anda memberikan peran admin dengan identitas terkelola yang ditetapkan pengguna kepada grup, lewati langkah 3. Admin memiliki akses penuh ke database.
Buat pengguna database mandiri untuk identitas terkelola yang ditetapkan pengguna. Koneksi ke database dari atau tempat Anda ingin menyalin data dengan menggunakan alat seperti SQL Server Management Studio, dengan identitas Microsoft Entra yang memiliki setidaknya izin UBAH PENGGUNA APA PUN. Jalankan T-SQL berikut:
CREATE USER [your_resource_name] FROM EXTERNAL PROVIDER;Buat satu atau beberapa identitas terkelola yang ditetapkan pengguna dan berikan izin yang diperlukan identitas terkelola yang ditetapkan pengguna seperti yang biasa Anda lakukan untuk pengguna SQL dan lainnya. Jalankan kode berikut. Untuk opsi selengkapnya, lihat dokumen ini.
ALTER ROLE [role name] ADD MEMBER [your_resource_name];Tetapkan satu atau beberapa identitas terkelola yang ditetapkan pengguna ke pabrik data Anda dan buat info masuk untuk setiap identitas terkelola yang ditetapkan pengguna.
Konfigurasikan layanan tertaut Azure SQL Database.
Contoh:
{
"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"
}
}
}
Properti himpunan data
Untuk daftar lengkap bagian dan properti yang tersedia untuk menentukan himpunan data, lihat Himpunan data.
Properti berikut ini didukung untuk kumpulan data Azure SQL Database:
| Properti | Deskripsi | Wajib |
|---|---|---|
| jenis | Properti Jenis himpunan data harus diatur ke AzureSqlTable. | Ya |
| skema | Nama skema. | Tidak untuk sumber, Ya untuk sink |
| tabel | Nama tabel/tampilan. | Tidak untuk sumber, Ya untuk sink |
| tableName | Nama tabel/tampilan dengan skema. Properti ini didukung untuk kompatibilitas mundur. Untuk beban kerja baru, gunakan schema dan table. |
Tidak untuk sumber, Ya untuk sink |
Contoh properti himpunan data
{
"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>"
}
}
}
Properti aktivitas salin
Untuk daftar lengkap bagian dan properti yang tersedia untuk menentukan aktivitas, lihat Alur. Bagian ini menyediakan daftar properti yang didukung oleh sumber dan sink Azure SQL Database.
Azure SQL Database sebagai sumber
Tip
Untuk memuat data dari Azure SQL Database secara efisien dengan menggunakan partisi data, pelajari selengkapnya dari Salin paralel dari database SQL.
Untuk menyalin data dari Azure SQL Database, properti berikut ini didukung di bagian sumber aktivitas salin:
| Properti | Deskripsi | Wajib |
|---|---|---|
| jenis | Propertijenis dari sumber aktivitas salin harus diatur ke AzureSqlSource. Jenis "SqlSource" masih didukung untuk kompatibilitas mundur. | Ya |
| sqlReaderQuery | Properti ini menggunakan kueri SQL kustom untuk membaca data. Contohnya select * from MyTable. |
No |
| sqlReaderStoredProcedureName | Nama prosedur tersimpan yang membaca data dari tabel sumber. Pernyataan SQL terakhir harus merupakan pernyataan SELECT dalam prosedur tersimpan. | No |
| storedProcedureParameters | Parameter untuk prosedur tersimpan. Nilai yang diizinkan adalah pasangan nama atau nilai. Nama dan kapitalisasi parameter harus sesuai dengan nama dan kapitalisasi parameter prosedur tersimpan. |
No |
| isolationLevel | Menentukan perilaku penguncian transaksi untuk sumber SQL. Nilai yang diperbolehkan adalah: ReadCommitted, ReadUncommitted, RepeatableRead, Serializable, Rekam jepret. Jika tidak ditentukan, tingkat isolasi default database digunakan. Lihat ke dokumen ini untuk detail selengkapnya. | No |
| partitionOptions | Menentukan opsi partisi data yang digunakan untuk memuat data dari Azure SQL Database. Nilai yang diizinkan adalah: Tidak ada (default), PhysicalPartitionsOfTable, dan DynamicRange. Ketika opsi partisi diaktifkan (yaitu, bukan None), tingkat paralelisme untuk memuat data secara bersamaan dari Azure SQL Database dikendalikan oleh pengaturan parallelCopies pada aktivitas salin. |
No |
| partitionSettings | Tentukan grup pengaturan untuk pemartisian data. Terapkan saat opsi partisi bukan None. |
No |
Di bawah partitionSettings: |
||
| partitionColumnName | Tentukan nama kolom sumber dalam bilangan bulat atau jenis tanggal/waktu (int, smallint, bigint, date, smalldatetime, datetime, datetime2, atau datetimeoffset) yang akan digunakan oleh partisi rentang untuk salinan paralel. Jika tidak ditentukan, indeks atau kunci utama tabel terdeteksi secara otomatis dan digunakan sebagai kolom partisi.Terapkan saat opsi partisi adalah DynamicRange. Jika Anda menggunakan kueri untuk mengambil data sumber, kaitkan ?DfDynamicRangePartitionCondition di klausul WHERE. Misalnya, lihat bagian Penyalinan paralel dari database SQL. |
No |
| partitionUpperBound | Nilai maksimum kolom partisi untuk pemisahan rentang partisi. Nilai ini digunakan untuk menentukan langkah partisi, bukan untuk memfilter baris dalam tabel. Semua baris dalam tabel atau hasil kueri akan dipartisi dan disalin. Jika tidak ditentukan, aktivitas salin secara otomatis mendeteksi nilai. Terapkan saat opsi partisi adalah DynamicRange. Misalnya, lihat bagian Penyalinan paralel dari database SQL. |
No |
| partitionLowerBound | Nilai minimum kolom partisi untuk pemisahan rentang partisi. Nilai ini digunakan untuk menentukan langkah partisi, bukan untuk memfilter baris dalam tabel. Semua baris dalam tabel atau hasil kueri akan dipartisi dan disalin. Jika tidak ditentukan, aktivitas salin secara otomatis mendeteksi nilai. Terapkan saat opsi partisi adalah DynamicRange. Misalnya, lihat bagian Penyalinan paralel dari database SQL. |
No |
Perhatikan poin-poin berikut:
- Jika sqlReaderQuery ditentukan untuk AzureSqlSource, aktivitas salin menjalankan kueri ini terhadap sumber Azure SQL Database untuk mendapatkan data. Anda juga dapat menentukan prosedur tersimpan dengan menentukan sqlReaderStoredProcedureName dan storedProcedureParameters jika prosedur yang disimpan mengambil parameter.
- Saat menggunakan prosedur tersimpan di sumber untuk mengambil data, perhatikan apakah prosedur tersimpan dirancang sebagai mengembalikan skema yang berbeda ketika nilai parameter yang berbeda diteruskan, Anda mungkin mengalami kegagalan atau melihat hasil yang tidak terduga saat mengimpor skema dari antarmuka pengguna atau saat menyalin data ke database SQL dengan pembuatan tabel otomatis.
Contoh kueri 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>"
}
}
}
]
Contoh prosedur tersimpan
"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>"
}
}
}
]
Definisi prosedur tersimpan
CREATE PROCEDURE CopyTestSrcStoredProcedureWithParameters
(
@stringData varchar(20),
@identifier int
)
AS
SET NOCOUNT ON;
BEGIN
select *
from dbo.UnitTestSrcTable
where dbo.UnitTestSrcTable.stringData != stringData
and dbo.UnitTestSrcTable.identifier != identifier
END
GO
Azure SQL Database sebagai sink
Tip
Pelajari selengkapnya tentang perilaku tulis, konfigurasi, dan praktik terbaik yang didukung dari Praktik terbaik untuk memuat data ke Azure SQL Database.
Untuk menyalin data dari Azure SQL Database, properti berikut ini didukung di bagiansinkaktivitas salin:
| Properti | Deskripsi | Wajib |
|---|---|---|
| jenis | Properti jenis sink aktivitas salin harus diatur ke AzureSqlSink. Jenis "SqlSink" masih didukung untuk kompatibilitas mundur. | Ya |
| preCopyScript | Tentukan kueri SQL untuk aktivitas salin yang akan dijalankan sebelum menulis data ke dalam Azure SQL Database. Ini diaktifkan hanya sekali per eksekusi salinan. Gunakan properti ini untuk membersihkan data yang telah dimuat sebelumnya. | No |
| tableOption | Menentukan apakah akan membuat tabel sink secara otomatis jika tidak ada berdasarkan skema sumber. Pembuatan tabel otomatis tidak didukung saat sink menentukan prosedur tersimpan. Nilai yang diizinkan adalah: none (default), autoCreate. |
No |
| sqlWriterStoredProcedureName | Nama prosedur tersimpan yang menentukan cara menerapkan data sumber ke dalam tabel target. Prosedur tersimpan ini digunakan per batch. Untuk operasi yang hanya berjalan sekali dan tidak ada hubungannya dengan data sumber, misalnya, menghapus atau memotong, gunakan preCopyScript properti.Lihat contoh dari Menggunakan prosedur tersimpan dari sink SQL. |
No |
| storedProcedureTableTypeParameterName | Nama parameter jenis tabel yang ditentukan dalam prosedur tersimpan. | No |
| sqlWriterTableType | Tentukan nama jenis tabel yang akan digunakan dalam prosedur tersimpan. Aktivitas salin membuat data yang sedang dipindahkan tersedia dalam tabel sementara dengan jenis tabel ini. Kode prosedur tersimpan kemudian dapat menggabungkan data yang sedang disalin dengan data yang ada. | No |
| storedProcedureParameters | Parameter untuk prosedur tersimpan. Nilai yang diizinkan adalah pasangan nama dan nilai. Nama dan casing parameter harus sesuai dengan nama dan casing parameter prosedur yang disimpan. |
No |
| writeBatchSize | Jumlah baris yang akan disisipkan ke dalam tabel SQL per batch. Nilai yang diizinkan adalah bilangan bulat (jumlah baris). Secara default, layanan secara dinamis menentukan ukuran batch yang sesuai berdasarkan ukuran baris. |
No |
| writeBatchTimeout | Waktu tunggu untuk operasi sisipkan, upsert, dan prosedur tersimpan selesai sebelum waktu habis. Nilai yang diperbolehkan adalah untuk rentang waktu. Contohnya adalah "00:30:00" untuk 30 menit. Jika tidak ada nilai yang ditentukan, batas waktu default ke "00:30:00". |
No |
| disableMetricsCollection | Layanan mengumpulkan metrik seperti Azure SQL Database DTU untuk pengoptimalan dan rekomendasi kinerja salinan, yang memperkenalkan akses DB master tambahan. Jika Anda khawatir dengan perilaku ini, tentukan true untuk menonaktifkannya. |
Tidak (defaultnya adalah false) |
| maxConcurrent Koneksi ions | Batas atas koneksi bersamaan yang ditetapkan ke penyimpanan data selama eksekusi aktivitas. Menentukan nilai hanya saat Anda ingin membatasi koneksi bersamaan. | Tanpa |
| WriteBehavior | Tentukan perilaku penulisan untuk aktivitas penyalinan untuk memuat data ke Azure SQL Database. Nilai yang diizinkan adalah Insert dan Upsert. Secara default, layanan menggunakan insert untuk memuat data. |
No |
| upsertSettings | Tentukan grup pengaturan untuk perilaku penulisan. Terapkan saat opsi WriteBehavior adalah Upsert. |
No |
Di bawah upsertSettings: |
||
| useTempDB | Tentukan apakah akan menggunakan tabel sementara global atau tabel fisik sebagai tabel interim untuk upsert. Secara default, layanan menggunakan tabel sementara global sebagai tabel interim. nilai adalah true. |
No |
| interimSchemaName | Tentukan skema interim untuk membuat tabel interim jika tabel fisik digunakan. Catatan: pengguna harus memiliki izin untuk membuat dan menghapus tabel. Secara default, tabel interim akan berbagi skema yang sama dengan tabel sink. Terapkan saat opsi useTempDB adalah False. |
No |
| kunci | Tentukan nama kolom untuk identifikasi baris unik. Salah satu kunci atau serangkaian kunci dapat digunakan. Jika tidak ditentukan, kunci primer digunakan. | No |
Contoh 1: Menambahkan data
"activities":[
{
"name": "CopyToAzureSQLDatabase",
"type": "Copy",
"inputs": [
{
"referenceName": "<input dataset name>",
"type": "DatasetReference"
}
],
"outputs": [
{
"referenceName": "<Azure SQL Database output dataset name>",
"type": "DatasetReference"
}
],
"typeProperties": {
"source": {
"type": "<source type>"
},
"sink": {
"type": "AzureSqlSink",
"tableOption": "autoCreate",
"writeBatchSize": 100000
}
}
}
]
Contoh 2: Gunakan prosedur tersimpan selama salinan
Pelajari detail selengkapnya dari Gunakan prosedur tersimpan dari sink 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" }
}
}
}
}
]
Contoh 3: Data 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>"
]
},
}
}
}
]
Salin paralel dari database SQL
Konektor Azure SQL Database dalam aktivitas salin menyediakan partisi data bawaan untuk menyalin data secara paralel. Anda dapat menemukan opsi pemartisian data pada tab Sumber aktivitas salin.
Saat Anda mengaktifkan salinan terpartisi, aktivitas salin menjalankan kueri paralel terhadap sumber Azure SQL Database Anda untuk memuat data menurut partisi. Derajat paralel dikendalikan oleh pengaturan parallelCopies pada aktivitas salin. Misalnya, jika Anda mengatur parallelCopies ke empat, layanan secara bersamaan akan membuat dan menjalankan empat kueri berdasarkan opsi dan pengaturan partisi yang ditentukan, dan setiap kueri akan mengambil sebagian data dari Azure SQL Database.
Sebaiknya Anda mengaktifkan penyalinan paralel dengan partisi data terutama ketika memuat data dalam jumlah besar dari Azure SQL Database Anda. Berikut ini adalah konfigurasi yang disarankan untuk skenario yang berbeda. Saat menyalin data ke penyimpanan data berbasis file, disarankan untuk menulis ke folder sebagai beberapa file (hanya tentukan nama folder), dalam hal ini performanya lebih baik daripada menulis ke satu file.
| Skenario | Pengaturan yang disarankan |
|---|---|
| Pemuatan penuh dari tabel besar, dengan partisi fisik. | Opsi partisi: Partisi fisik tabel. Selama eksekusi, layanan secara otomatis mendeteksi partisi fisik, dan menyalin data berdasarkan partisi. Untuk memeriksa apakah tabel Anda memiliki partisi fisik atau tidak, Anda dapat merujuk ke kueri ini. |
| Pemuatan penuh dari tabel besar, tanpa partisi fisik, sedangkan dengan bilangan bulat atau kolom tanggalwaktu untuk pemartisian data. | Opsi partisi: Partisi rentang dinamis. Kolom partisi (opsional): Menentukan kolom yang digunakan untuk mempartisi data. Jika tidak ditentukan, indeks atau kolom kunci primer digunakan. Batas atas partisi dan batas bawah partisi (opsional): Menentukan apakah Anda ingin menentukan langkah partisi. Ini bukan untuk memfilter baris dalam tabel, semua baris dalam tabel akan dipartisi dan disalin. Jika tidak ditentukan, aktivitas salin secara otomatis mendeteksi nilai. Misalnya, jika kolom partisi "ID" Anda memiliki rentang nilai dari 1 hingga 100, dan Anda menetapkan batas bawah sebagai 20 dan batas atas sebagai 80, dengan salinan paralel sebagai 4, layanan mengambil data dengan 4 partisi - ID dalam rentang <=20, [21, 50], [51, 80], dan >=81, masing-masing. |
| Memuat sejumlah besar data dengan menggunakan kueri kustom, tanpa partisi fisik, sedangkan dengan kolom bilangan bulat atau tanggal/tanggalwaktu untuk pemartisian data. | Opsi partisi: Partisi rentang dinamis. Kueri: SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>.Kolom partisi: Menentukan kolom yang digunakan untuk mempartisi data. Batas atas partisi dan batas bawah partisi (opsional): Menentukan apakah Anda ingin menentukan langkah partisi. Ini bukan untuk memfilter baris dalam tabel, semua baris dalam hasil kueri akan dipartisi dan disalin. Jika tidak ditentukan, aktivitas salin secara otomatis mendeteksi nilai. Misalnya, jika kolom partisi "ID" Anda memiliki rentang nilai dari 1 hingga 100, dan Anda menetapkan batas bawah sebagai 20 dan batas atas sebagai 80, dengan salinan paralel sebagai 4, layanan mengambil data dengan 4 partisi- ID dalam rentang <=20, [21, 50], [51, 80], dan >=81, secara berurutan. Berikut adalah sampel kueri lainnya untuk skenario yang berbeda: 1. Kueri seluruh tabel: SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition2. Kueri dari tabel dengan pemilihan kolom dan filter where-clause tambahan: SELECT <column_list> FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>3. Kueri dengan subkueri: SELECT <column_list> FROM (<your_sub_query>) AS T WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>4. Kueri dengan partisi dalam subkueri: SELECT <column_list> FROM (SELECT <your_sub_query_column_list> FROM <TableName> WHERE ?DfDynamicRangePartitionCondition) AS T |
Praktik terbaik untuk memuat data dengan opsi partisi:
- Pilih kolom yang khas sebagai kolom partisi (seperti kunci primer atau kunci unik) untuk menghindari penyimpangan data.
- Jika tabel memiliki partisi bawaan, gunakan opsi partisi "Partisi fisik tabel" untuk mendapatkan performa yang lebih baik.
- Jika Anda menggunakan Azure Integration Runtime untuk menyalin data, Anda dapat mengatur "Unit Integrasi Data (DIU)" yang lebih besar (>4) untuk menggunakan lebih banyak sumber daya komputasi. Periksa skenario yang berlaku di sana.
- "Tingkat paralelisme penyalinan" mengontrol jumlah partisi, mengatur jumlah ini terlalu besar kadang menurunkan kinerja, rekomendasikan mengatur angka ini sebagai (DIU atau jumlah simpul Runtime integrasi yang dihost sendiri) * (2 hingga 4).
Contoh: pemuatan penuh dari tabel besar dengan partisi fisik
"source": {
"type": "AzureSqlSource",
"partitionOption": "PhysicalPartitionsOfTable"
}
Contoh: kueri dengan partisi rentang dinamis
"source": {
"type": "AzureSqlSource",
"query": "SELECT * FROM <TableName> WHERE ?DfDynamicRangePartitionCondition AND <your_additional_where_clause>",
"partitionOption": "DynamicRange",
"partitionSettings": {
"partitionColumnName": "<partition_column_name>",
"partitionUpperBound": "<upper_value_of_partition_column (optional) to decide the partition stride, not as data filter>",
"partitionLowerBound": "<lower_value_of_partition_column (optional) to decide the partition stride, not as data filter>"
}
}
Sampel kueri untuk memeriksa partisi fisik
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]'
Jika tabel memiliki partisi fisik, Anda akan melihat "HasPartition" sebagai "ya" seperti berikut ini.
Praktik terbaik untuk memuat data ke Azure SQL Database
Ketika Anda menyalin data ke Azure SQL Database, Anda mungkin memerlukan perilaku menulis yang berbeda:
- Tambahkan: Data sumber saya hanya memiliki rekaman baru.
- Upsert: Data sumber saya memiliki sisipan dan pembaruan.
- Timpa: Saya ingin memuat ulang seluruh tabel dimensi setiap saat.
- Menulis dengan logika kustom: Saya perlu pemrosesan ekstra sebelum penyisipan akhir ke dalam tabel tujuan.
Lihat bagian masing-masing tentang cara mengonfigurasi di layanan dan praktik terbaik.
Menambahkan data
Menambahkan data adalah perilaku default konektor wastafel Azure SQL Database ini. layanan melakukan penyisipan massal untuk menulis ke tabel Anda secara efisien. Anda dapat mengonfigurasi sumber dan sink sesuai dengan aktivitas salin.
Meng-upsert data
Aktivitas salin sekarang mendukung pemuatan data secara native ke tabel sementara database, lalu memperbarui data di tabel sink jika ada kunci dan jika tidak, memasukkan data baru. Untuk mempelajari selengkapnya tentang pengaturan upsert dalam aktivitas salin, lihat Azure SQL Database sebagai sink.
Timpa seluruh tabel
Anda dapat mengonfigurasi properti preCopyScript di sink aktivitas salin. Dalam hal ini, untuk setiap aktivitas penyalinan yang berjalan, layanan menjalankan skrip terlebih dahulu. Kemudian menjalankan penyalinan untuk menyisipkan data. Misalnya, untuk menimpa seluruh tabel dengan data terbaru, tentukan skrip untuk terlebih dahulu menghapus semua rekaman sebelum Anda memuat data baru secara massal dari sumbernya.
Menulis data dengan logika kustom
Langkah-langkah untuk menulis data dengan logika kustom mirip dengan yang dijelaskan di bagian Meng-upsert data. Ketika Anda perlu menerapkan pemrosesan ekstra sebelum penyisipan akhir data sumber ke dalam tabel tujuan, Anda dapat memuat ke tabel penahapan lalu mengaktifkan aktivitas prosedur tersimpan, atau mengaktifkan prosedur tersimpan di sink aktivitas salin untuk menerapkan data, atau menggunakan Aliran Data Pemetaan.
Memanggil prosedur tersimpan dari sink SQL
Saat menyalin data ke Azure SQL Database, Anda juga dapat mengonfigurasi dan mengaktifkan prosedur tersimpan oleh pengguna dengan parameter tambahan pada setiap batch tabel sumber. Fitur prosedur tersimpan memanfaatkan parameter bernilai tabel.
Anda dapat menggunakan prosedur tersimpan saat mekanisme salinan bawaan tidak berfungsi sebagai mana mestinya. Contohnya adalah ketika Anda ingin menerapkan pemrosesan ekstra sebelum penyisipan akhir data sumber ke dalam tabel tujuan. Beberapa contoh pemrosesan tambahan adalah saat Anda ingin menggabungkan kolom, mencari nilai tambahan, dan menyisipkan ke dalam lebih dari satu tabel.
Contoh berikut ini memperlihatkan cara menggunakan prosedur tersimpan untuk melakukan upsert ke dalam tabel dalam database Azure SQL Database. Asumsikan bahwa data input dan tabel Pemasaran sink masing-masing memiliki tiga kolom: IDProfil, Status, dan Kategori. Lakukan upsert berdasarkan kolom IDProfil, dan hanya menerapkannya untuk kategori tertentu yang disebut "ProductA".
Di database Anda, tentukan jenis tabel dengan nama yang sama dengan sqlWriterTableType. Skema jenis tabel sama dengan skema yang dikembalikan oleh data input Anda.
CREATE TYPE [dbo].[MarketingType] AS TABLE( [ProfileID] [varchar](256) NOT NULL, [State] [varchar](256) NOT NULL, [Category] [varchar](256) NOT NULL )Di database Anda, tentukan prosedur tersimpan dengan nama yang sama dengan sqlWriterStoredProcedureName. Ini menangani data input dari sumber yang Anda tentukan dan bergabung ke dalam tabel output. Nama parameter jenis tabel dalam prosedur tersimpan sama dengan tableName yang ditentukan dalam himpunan data.
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); ENDDi alur Azure Data Factory atau Synapse Anda, tentukan bagian sink SQL dalam aktivitas salin sebagai berikut:
"sink": { "type": "AzureSqlSink", "sqlWriterStoredProcedureName": "spOverwriteMarketing", "storedProcedureTableTypeParameterName": "Marketing", "sqlWriterTableType": "MarketingType", "storedProcedureParameters": { "category": { "value": "ProductA" } } }
Saat menulis data ke Azure SQL Database menggunakan prosedur tersimpan, sink membagi data sumber menjadi batch mini lalu melakukan penyisipan, sehingga kueri tambahan dalam prosedur tersimpan dapat dijalankan beberapa kali. Jika Anda memiliki kueri agar aktivitas salin berjalan sebelum menulis data ke Azure SQL Database, tidak disarankan untuk menambahkannya ke prosedur tersimpan, tambahkan dalam kotak skrip Pra-salin.
Properti pemetaan aliran data
Saat melakukan transformasi data dalam aliran data pemetaan, Anda dapat membaca dan menulis ke tabel dari Azure SQL Database. Untuk informasi selengkapnya, lihat transformasi sumber dan transformasi sink dalam aliran data pemetaan.
Transformasi sumber
Pengaturan khusus untuk Azure SQL Database tersedia di tab Opsi Sumber dari transformasi sumber.
Input: Pilih apakah Anda mengarahkan sumber Anda ke tabel (setara dengan Select * from <table-name>) atau memasukkan kueri SQL kustom.
Kueri: Jika Anda memilih Kueri di bidang input, masukkan kueri SQL untuk sumber Anda. Pengaturan ini menggantikan tabel apa pun yang telah Anda pilih dalam himpunan data. Klausa Urutkan Menurut tidak didukung, tetapi Anda dapat mengatur pernyataan SELECT FROM yang lengkap. Anda juga dapat menggunakan fungsi tabel yang ditentukan pengguna. select * from udfGetData() adalah UDF di SQL yang mengembalikan tabel. Kueri ini akan menghasilkan tabel sumber yang dapat Anda gunakan dalam aliran data Anda. Menggunakan kueri juga merupakan cara yang bagus untuk mengurangi baris untuk pengujian atau pencarian.
Tip
Ekspresi tabel umum (CTE) dalam SQL tidak didukung dalam mode Kueri aliran data pemetaan, karena prasyarat penggunaan mode ini adalah kueri dapat digunakan dalam klausul kueri SQL FROM tetapi CTE tidak dapat melakukannya. Untuk menggunakan CTE, Anda perlu membuat prosedur tersimpan menggunakan kueri berikut:
CREATE PROC CTESP @query nvarchar(max)
AS
BEGIN
EXECUTE sp_executesql @query;
END
Kemudian gunakan mode Prosedur tersimpan dalam transformasi sumber aliran data pemetaan dan atur @query seperti contoh with CTE as (select 'test' as a) select * from CTE. Kemudian Anda dapat menggunakan CTE seperti yang diharapkan.
Prosedur tersimpan: Pilih opsi ini jika Anda ingin membuat proyeksi dan data sumber dari prosedur tersimpan yang dijalankan dari database sumber Anda. Anda dapat mengetik skema, nama prosedur, dan parameter, atau mengklik Refresh untuk meminta layanan menemukan skema dan nama prosedur. Kemudian Anda dapat mengklik Impor untuk mengimpor semua parameter prosedur menggunakan formulir @paraName.
- Contoh SQL:
Select * from MyTable where customerId > 1000 and customerId < 2000 - Contoh SQL Parameter:
"select * from {$tablename} where orderyear > {$year}"
Ukuran batch: Masukkan ukuran batch untuk memotong data besar ke dalam bacaan.
Tingkat Isolasi: Default untuk sumber SQL dalam pemetaan aliran data adalah read uncommitted. Anda dapat mengubah tingkat isolasi di sini menjadi salah satu nilai berikut:
- Read Committed
- Read Uncommitted
- Dibaca Berulang
- Serializable
- Tidak ada (abaikan tingkat isolasi)
Aktifkan ekstrak bertambah bertahap: Gunakan opsi ini untuk memberi tahu ADF untuk hanya memproses baris yang telah berubah sejak terakhir kali alur dijalankan. Untuk mengaktifkan ekstrak inkremental dengan penyimpangan skema, pilih tabel berdasarkan kolom Inkremental/Marka Air daripada tabel yang diaktifkan untuk Pengambilan Data Perubahan Asli.
Kolom inkremental: Saat menggunakan fitur ekstrak inkremental, Anda harus memilih kolom tanggal/waktu atau numerik yang ingin Anda gunakan sebagai marka air di tabel sumber Anda.
Aktifkan pengambilan data perubahan asli(Pratinjau): Gunakan opsi ini untuk memberi tahu ADF agar hanya memproses data delta yang diambil oleh SQL mengubah teknologi pengambilan data sejak terakhir kali alur dijalankan. Dengan opsi ini, data delta termasuk penyisipan baris, pembaruan, dan penghapusan akan dimuat secara otomatis tanpa memerlukan kolom tambahan. Anda perlu mengaktifkan ubah pengambilan data di Azure SQL DB sebelum menggunakan opsi ini di ADF. Untuk informasi selengkapnya tentang opsi ini di ADF, lihat pengambilan data perubahan asli.
Mulai membaca dari awal: Mengatur opsi ini dengan ekstrak inkremental akan menginstruksikan ADF untuk membaca semua baris pada eksekusi pertama alur dengan ekstrak inkremental diaktifkan.
Transformasi sink
Pengaturan khusus untuk Azure SQL Database tersedia di tab Pengaturan dari transformasi sink.
Metode pembaruan: Menentukan operasi apa yang diizinkan di tujuan database Anda. Defaultnya hanya mengizinkan operasi sisipan. Untuk memperbarui, meng-upsert, atau menghapus baris, transformasi baris pengganti diperlukan untuk menandai baris untuk tindakan tersebut. Untuk pembaruan, upsert dan hapus, kolom kunci atau kolom harus diatur untuk menentukan baris mana yang akan diubah.
Nama kolom yang Anda pilih sebagai kunci akan digunakan oleh layanan sebagai bagian dari pembaruan, upsert, penghapusan berikutnya. Oleh karena itu, Anda harus memilih kolom yang ada di pemetaan Sink. Jika Anda ingin tidak menulis nilai ke kolom kunci, pilih "Lewati penulisan kolom kunci".
Anda dapat memparameterkan kolom kunci yang digunakan di sini untuk memperbarui tabel Azure SQL Database target Anda. Jika Anda memiliki beberapa kolom untuk kunci komposit, klik "Ekspresi Kustom" dan Anda akan dapat menambahkan konten dinamis menggunakan bahasa ekspresi aliran data, yang dapat menyertakan array string dengan nama kolom untuk kunci komposit.
Tindakan tabel: Menentukan apakah akan membuat ulang atau menghapus semua baris dari tabel tujuan sebelum menulis.
- Tidak Ada: Tidak ada tindakan yang akan dilakukan pada tabel.
- Buat ulang: Tabel akan dihapus dan dibuat ulang. Diperlukan jika membuat tabel baru secara dinamis.
- Kosongkan: Semua baris dari tabel target akan dihapus.
Ukuran batch: Mengontrol berapa banyak baris yang ditulis di setiap bucket. Ukuran batch yang lebih besar meningkatkan pemadatan dan pengoptimalan memori, tetapi berisiko kehabisan pengecualian memori saat penembolokan data.
Gunakan TempDB: Secara default, layanan akan menggunakan tabel sementara global untuk menyimpan data sebagai bagian dari proses pemuatan. Anda dapat secara alternatif menghapus centang opsi "Gunakan TempDB" dan sebagai gantinya, minta layanan untuk menyimpan tabel penahanan sementara dalam database pengguna yang terletak di database yang sedang digunakan untuk Sink ini.
Skrip Pra dan Pasca SQL: Masukkan skrip SQL multibaris yang akan dijalankan sebelum (prapemrosesan) dan setelah (pascapemrosesan) data ditulis ke database Sink Anda
Tip
- Disarankan untuk memecah skrip batch tunggal dengan beberapa perintah menjadi beberapa batch.
- Hanya pernyataan Bahasa Definisi Data (DDL) dan Bahasa Manipulasi Data (DML) yang menampilkan jumlah pembaruan sederhana yang dapat dijalankan sebagai bagian dari batch. Pelajari selengkapnya dari Melakukan operasi batch
Penanganan baris kesalahan
Saat menulis ke Azure SQL Database, baris data tertentu mungkin gagal karena batasan yang ditetapkan oleh tujuan. Kesalahan umum meliputi:
- Data string atau biner akan dipotong dalam tabel
- Tidak bisa menyisipkan nilai NULL ke dalam kolom
- Pernyataan INSERT berkonflik dengan batasan CHECK
Secara default, eksekusi aliran data akan gagal pada kesalahan pertama yang diperolehnya. Anda dapat memilih untuk Melanjutkan kesalahan yang memungkinkan aliran data Anda selesai meskipun baris individual memiliki kesalahan. Layanan ini menyediakan opsi berbeda bagi Anda untuk menangani baris kesalahan ini.
Transaksi Berkomitmen: Pilih apakah data Anda ditulis dalam satu transaksi atau dalam batch. Transaksi tunggal akan memberikan kinerja yang lebih buruk tetapi tidak ada data yang ditulis akan terlihat oleh orang lain sampai transaksi selesai.
Output data yang ditolak: Jika diaktifkan, Anda dapat menghasilkan baris kesalahan ke dalam file csv di Azure Blob Storage atau akun Azure Data Lake Storage Gen2 yang Anda pilih. Ini akan menulis baris kesalahan dengan tiga kolom tambahan: operasi SQL seperti INSERT atau UPDATE, kode kesalahan aliran data, dan pesan kesalahan pada baris.
Melaporkan keberhasilan pada kesalahan: Jika diaktifkan, aliran data akan ditandai sebagai keberhasilan meskipun baris kesalahan ditemukan.
Pemetaan jenis data untuk Azure SQL Database
Saat data disalin dari atau ke Azure SQL Database, pemetaan berikut ini digunakan dari jenis data Azure SQL Database ke jenis data sementara Azure Data Factory. Pemetaan yang sama digunakan oleh fitur alur Synapse, yang mengimplementasikan Azure Data Factory secara langsung. Untuk mempelajari bagaimana aktivitas salin memetakan skema sumber dan jenis data ke sink, lihat Pemetaan skema dan jenis data.
| Jenis data Azure SQL Database | Jenis data sementara Data Factory |
|---|---|
| bigint | Int64 |
| biner | Byte[] |
| bit | Boolean |
| char | String, Char[] |
| date | DateTime |
| Tanggalwaktu | DateTime |
| tanggalwaktu2 | DateTime |
| Datetimeoffset | DateTimeOffset |
| Decimal | Decimal |
| FILESTREAM attribute (varbinary(max)) | Byte[] |
| Float | Laju |
| gambar | Byte[] |
| int | Int32 |
| money | Decimal |
| nchar | String, Char[] |
| ntext | String, Char[] |
| numeric | Decimal |
| nvarchar | String, Char[] |
| real | Tunggal |
| rowversion | Byte[] |
| smalldatetime | DateTime |
| smallint | Int16 |
| smallmoney | Decimal |
| aql_variant | Objek |
| text | String, Char[] |
| waktu | TimeSpan |
| rentang waktu | Byte[] |
| tinyint | Byte |
| uniqueidentifier | Guid |
| varbinary | Byte[] |
| varchar | String, Char[] |
| xml | String |
Catatan
Untuk tipe data yang di petakan ke jenis sementara Desimal, aktivitas Salin saat ini mendukung presisi hingga 28. Jika Anda memiliki data yang memerlukan presisi lebih besar dari 28, pertimbangkan untuk mengonversi ke string dalam kueri SQL.
Properti aktivitas pencarian
Untuk mempelajari detail tentang properti, lihat Aktivitas pencarian.
Properti aktivitas GetMetadata
Untuk mempelajari rincian tentang properti ini, periksa Aktivitas GetMetadata
Menggunakan Always Encrypted
Saat menyalin data dari/ke Azure SQL Database dengan Always Encrypted, ikuti langkah-langkah berikut:
Simpan Kunci Master Kolom (CMK) di Azure Key Vault. Pelajari selengkapnya tentang cara mengonfigurasi Always Encrypted menggunakan Azure Key Vault
Pastikan untuk mendapatkan akses ke brankas kunci tempat Kunci Master Kolom (CMK) disimpan. Lihat artikel ini untuk izin yang diperlukan.
Buat layanan tertaut untuk terhubung ke database SQL Anda dan aktifkan fungsi 'Always Encrypted' menggunakan identitas terkelola atau perwakilan layanan.
Catatan
Always Encrypted Azure SQL Database mendukung skenario di bawah:
- Baik penyimpanan data sumber atau sink menggunakan identitas terkelola atau perwakilan layanan sebagai jenis autentikasi penyedia kunci.
- Penyimpanan data sumber dan sink menggunakan identitas terkelola sebagai jenis autentikasi penyedia kunci.
- Penyimpanan data sumber dan sink menggunakan perwakilan layanan yang sama dengan jenis autentikasi penyedia kunci.
Catatan
Saat ini, Always Encrypted Azure SQL Database hanya didukung untuk transformasi sumber dalam memetakan aliran data.
Pengambilan data perubahan asli
Azure Data Factory dapat mendukung kemampuan pengambilan data perubahan asli untuk SQL Server, Azure SQL DB, dan Azure SQL MI. Data yang diubah termasuk sisipan baris, pembaruan, dan penghapusan di penyimpanan SQL dapat secara otomatis dideteksi dan diekstrak oleh aliran data pemetaan ADF. Tanpa pengalaman kode dalam memetakan aliran data, pengguna dapat dengan mudah mencapai skenario replikasi data dari penyimpanan SQL dengan menambahkan database sebagai penyimpanan tujuan. Terlebih lagi, pengguna juga dapat menyusun logika transformasi data apa pun di antaranya untuk mencapai skenario ETL inkremental dari penyimpanan SQL.
Pastikan Anda menjaga agar nama alur dan aktivitas tidak berubah, sehingga titik pemeriksaan dapat direkam oleh ADF agar Anda mendapatkan data yang diubah dari yang terakhir dijalankan secara otomatis. Jika Anda mengubah nama alur atau nama aktivitas Anda, titik pemeriksaan akan diatur ulang, yang mengarahkan Anda untuk memulai dari awal atau mendapatkan perubahan mulai sekarang di eksekusi berikutnya. Jika Anda ingin mengubah nama alur atau nama aktivitas tetapi masih menyimpan titik pemeriksaan untuk mendapatkan data yang diubah dari eksekusi terakhir secara otomatis, gunakan kunci Titik Pemeriksaan Anda sendiri dalam aktivitas aliran data untuk mencapainya.
Saat Anda men-debug alur, fitur ini berfungsi sama. Ketahuilah bahwa pos pemeriksaan akan diatur ulang saat Anda menyegarkan browser selama eksekusi debug. Setelah Anda puas dengan hasil alur dari eksekusi debug, Anda dapat melanjutkan untuk menerbitkan dan memicu alur. Pada saat Anda pertama kali memicu alur yang diterbitkan, maka secara otomatis dimulai ulang dari awal atau mendapat perubahan mulai sekarang.
Di bagian pemantauan, Anda selalu memiliki kesempatan untuk menjalankan kembali alur. Saat Anda melakukannya, data yang diubah selalu diambil dari pos pemeriksaan sebelumnya dari eksekusi alur yang Anda pilih.
Contoh 1:
Ketika Anda langsung menautkan transformasi sumber yang direferensikan ke himpunan data yang diaktifkan SQL CDC dengan transformasi sink yang direferensikan ke database dalam aliran data pemetaan, perubahan yang terjadi pada sumber SQL akan secara otomatis diterapkan ke database target, sehingga Anda akan dengan mudah mendapatkan skenario replikasi data antar database. Anda dapat menggunakan metode pembaruan dalam transformasi sink untuk memilih apakah Anda ingin mengizinkan penyisipan, mengizinkan pembaruan, atau mengizinkan penghapusan pada database target. Contoh skrip dalam pemetaan aliran data adalah seperti di bawah ini.
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
Contoh 2:
Jika Anda ingin mengaktifkan skenario ETL alih-alih replikasi data antara database melalui SQL CDC, Anda dapat menggunakan ekspresi dalam memetakan aliran data termasuk isInsert(1), isUpdate(1) dan isDelete(1) untuk membedakan baris dengan jenis operasi yang berbeda. Berikut ini adalah salah satu contoh skrip untuk memetakan aliran data pada mendapatkan satu kolom dengan nilai: 1 untuk menunjukkan baris yang disisipkan, 2 untuk menunjukkan baris yang diperbarui dan 3 untuk menunjukkan baris yang dihapus untuk transformasi hilir untuk memproses data delta.
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
Batasan yang diketahui:
- Hanya perubahan bersih dari SQL CDC yang akan dimuat oleh ADF melalui cdc.fn_cdc_get_net_changes_.
Konten terkait
Untuk daftar penyimpanan data yang didukung sebagai sumber dan sink oleh aktivitas salin, lihat Format dan penyimpanan data yang didukung.