Memuat data secara bertahap dari Azure SQL Database ke penyimpanan Blob menggunakan portal Microsoft Azure

  • Artikel

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!

Dalam tutorial ini, Anda membuat Azure Data Factory dengan alur yang memuat data delta dari tabel di Azure SQL Database ke penyimpanan Azure Blob.

Anda akan melakukan langkah-langkah berikut dalam tutorial ini:

  • Siapkan penyimpanan data untuk menyimpan nilai marka air.
  • Membuat pabrik data.
  • Membuat layanan tertaut.
  • Buat himpunan data sumber, sink, dan marka air.
  • Buat alur.
  • Jalankan alur.
  • Pantau eksekusi alur.
  • Meninjau hasil
  • Tambahkan lebih banyak data ke sumber.
  • Jalankan alur lagi.
  • Memantau eksekusi alur kedua
  • Meninjau hasil dari eksekusi kedua

Gambaran Umum

Berikut adalah diagram solusi tingkat tinggi:

Incrementally load data

Berikut adalah langkah-langkah penting untuk membuat solusi ini:

  1. Pilih kolom marka air. Pilih satu kolom di penyimpanan data sumber, yang dapat digunakan untuk memotong rekaman baru atau yang diperbarui untuk setiap eksekusi. Biasanya, data di kolom yang dipilih ini (misalnya, last_modify_time atau ID) terus meningkat saat baris dibuat atau diperbarui. Nilai maksimum dalam kolom ini digunakan sebagai marka air.

  2. Siapkan penyimpanan data untuk menyimpan nilai marka air. Dalam tutorial ini, Anda akan menyimpan nilai marka air dalam database SQL.

  3. Buat alur dengan alur kerja berikut:

    Alur dalam solusi ini memiliki aktivitas berikut:

    • Buat dua aktivitas Pencarian. Gunakan aktivitas Pencarian pertama untuk mengambil nilai marka air terakhir. Gunakan aktivitas Pencarian kedua untuk mengambil nilai marka air baru. Nilai marka air ini diteruskan ke aktivitas Salin.
    • Buat aktivitas Salin yang menyalin baris dari penyimpanan data sumber dengan nilai kolom marka air yang lebih besar dari nilai marka air lama dan kurang dari nilai marka air baru. Kemudian, aktivitas ini akan menyalin data delta dari penyimpanan data sumber ke penyimpanan Blob sebagai file baru.
    • Buat aktivitas StoredProcedure yang memperbarui nilai marka air untuk alur yang berjalan selanjutnya.

Jika Anda tidak memiliki langganan Azure, buat akun gratis sebelum Anda memulai.

Prasyarat

  • Microsoft Azure SQL database. Anda menggunakan database sebagai penyimpanan data sumber. Jika Anda tidak memiliki database di Azure SQL Database, lihat Membuat database di Azure SQL Database untuk mengetahui langkah-langkah membuatnya.
  • Azure Storage. Anda menggunakan penyimpanan blob sebagai penyimpanan data sink. Jika Anda tidak memiliki akun penyimpanan, lihat Membuat akun penyimpanan untuk mengetahui langkah-langkah membuatnya. Buat kontainer dengan nama adftutorial.

Membuat tabel sumber data di database SQL Anda

  1. Buka SQL Server Management Studio. Di Server Explorer, klik kanan database, dan pilih Kueri Baru.

  2. Jalankan perintah SQL berikut terhadap database SQL untuk membuat tabel bernama data_source_table sebagai penyimpanan sumber data:

    create table data_source_table
(
    PersonID int,
    Name varchar(255),
    LastModifytime datetime
);

INSERT INTO data_source_table
    (PersonID, Name, LastModifytime)
VALUES
    (1, 'aaaa','9/1/2017 12:56:00 AM'),
    (2, 'bbbb','9/2/2017 5:23:00 AM'),
    (3, 'cccc','9/3/2017 2:36:00 AM'),
    (4, 'dddd','9/4/2017 3:21:00 AM'),
    (5, 'eeee','9/5/2017 8:06:00 AM');

    Dalam tutorial ini, Anda menggunakan LastModifytime sebagai kolom marka air. Data di penyimpanan sumber data diperlihatkan dalam tabel berikut:

    PersonID | Name | LastModifytime
-------- | ---- | --------------
1        | aaaa | 2017-09-01 00:56:00.000
2        | bbbb | 2017-09-02 05:23:00.000
3        | cccc | 2017-09-03 02:36:00.000
4        | dddd | 2017-09-04 03:21:00.000
5        | eeee | 2017-09-05 08:06:00.000

Membuat tabel lain di database SQL Anda untuk menyimpan nilai marka air yang tinggi

  1. Jalankan perintah SQL berikut terhadap database SQL Anda untuk membuat tabel bernama watermarktable guna menyimpan nilai marka air:

    create table watermarktable
(

TableName varchar(255),
WatermarkValue datetime,
);

  2. Atur nilai default marka air tinggi dengan nama tabel penyimpanan data sumber. Dalam tutorial ini, nama tabel adalah data_source_table.

    INSERT INTO watermarktable
VALUES ('data_source_table','1/1/2010 12:00:00 AM')

  3. Tinjau data dalam tabel watermarktable.

    Select * from watermarktable

    Output:

    TableName  | WatermarkValue
----------  | --------------
data_source_table | 2010-01-01 00:00:00.000

Membuat prosedur tersimpan di database SQL Anda

Jalankan perintah berikut ini untuk membuat prosedur tersimpan di database SQL Anda:

CREATE PROCEDURE usp_write_watermark @LastModifiedtime datetime, @TableName varchar(50)
AS

BEGIN

UPDATE watermarktable
SET [WatermarkValue] = @LastModifiedtime
WHERE [TableName] = @TableName

END

Membuat pabrik data

  1. Luncurkan browser web Microsoft Edge atau Google Chrome. Saat ini, antarmuka pengguna Data Factory hanya didukung di browser web Microsoft Edge dan Google Chrome.

  2. Di menu sebelah kiri, pilih Buat sumber daya>Integrasi>Data Factory:

    Data Factory selection in the "New" pane

  3. Di halaman Pabrik data baru, masukkan ADFIncCopyTutorialDF untuk namanya.

    Nama Azure Data Factory harus unik secara global. Jika Anda melihat tanda seru merah dengan kesalahan berikut, ubah nama pabrik data (misalnya, yournameADFIncCopyTutorialDF) dan coba buat lagi. Lihat artikel Azure Data Factory - Aturan Penamaan untuk aturan penamaan artefak Azure Data Factory.

    Nama pabrik data "ADFIncCopyTutorialDF" tidak tersedia

  4. Pilih langganan Azure tempat Anda ingin membuat pabrik data tersebut.

  5. Untuk Grup Sumber Daya, lakukan salah satu langkah berikut:

  6. Pilih V2 untuk versinya.

  7. Pilih lokasi untuk pabrik data tersebut. Hanya lokasi yang didukung yang ditampilkan di daftar drop-down. Penyimpanan data (Azure Storage, Azure SQL Database, Azure SQL Managed Instance, dan lain-lain) dan komputasi (HDInsight, dll.) yang digunakan pabrik data dapat berada di wilayah lain.

  8. Klik Buat.

  9. Setelah pembuatan selesai, Anda akan melihat halaman Azure Data Factory seperti yang ditunjukkan di gambar.

    Home page for the Azure Data Factory, with the Open Azure Data Factory Studio tile.

  10. Pilih Buka pada petak Buka Azure Data Factory Studio untuk meluncurkan antarmuka (UI) pengguna Azure Data Factory pada tab browser terpisah.

Buat alur

Dalam tutorial ini, Anda akan membuat alur dengan dua aktivitas Pencarian, satu aktivitas Salin, dan satu aktivitas StoredProcedure yang diikat dalam satu alur.

  1. Di beranda UI Azure Data Factory, klik petak Orkestra.

    Screenshot that shows the data factory home page with the Orchestrate button highlighted.

  2. Di panel Umum di bawah Properti, tetapkan IncrementalCopyPipeline untuk Namanya. Kemudian ciutkan panel dengan mengeklik ikon Properti di pojok kanan atas.

  3. Mari kita tambahkan pencarian terlebih dulu untuk mendapatkan nilai watermark lama. Di kotak alat Aktivitas, buka Umjm, dan tarik-lepas aktivitas Pencarian untuk permukaan perancang alur. Ubah nama aktivitas ke LookupOldWaterMarkActivity.

    First lookup activity - name

  4. Beralih ke tab Pengaturan, dan klik Baru untuk Himpunan Data Sumber. Dalam langkah ini, Anda membuat himpunan data untuk mewakili data dalam watermarktable. Tabel ini berisi watermark lama yang digunakan dalam operasi salinan sebelumnya.

  5. Di jendela Himpunan Data Baru, pilih Azure SQL Database, lalu klik Lanjutkan. Anda melihat jendela baru yang dibuka untuk himpunan data.

  6. Di jendela Atur properti untuk himpunan data, masukkan WatermarkDataset untuk Nama.

  7. Untuk Layanan Tertaut, pilih Baru, lalu lakukan langkah-langkah berikut ini:

    1. Masukkan AzureSqlDatabaseLinkedService untuk Nama.

    2. Pilih server Anda untuk Nama server.

    3. Pilih Nama database dari daftar dropdown.

    4. Masukkan Nama pengguna &kata sandi Anda.

    5. Untuk menguji koneksi ke database SQL Anda, klik Uji koneksi.

    6. Klik Selesai.

    7. Konfirmasikan bahwa AzureSqlDatabaseLinkedService dipilih untuk Layanan tertaut.

      New linked service window

    8. Pilih Selesai.

  8. Pada tab Koneksi, pilih [dbo].[watermarktable] untuk Tabel. Jika Anda ingin melihat pratinjau data dalam tabel, pilih Lihat pratinjau data.

    Watermark dataset - connection settings

  9. Beralih ke editor alur dengan mengeklik tab alur di bagian atas atau dengan mengeklik nama alur di tampilan pohon di sebelah kiri. Di jendela properti untuk aktivitas Pencarian, konfirmasikan bahwa WatermarkDataset dipilih untuk bidang Himpunan Data Sumber.

  10. Di kotak alat Aktivitas, perluas Umum, dan tarik-lepas aktivitas Pencarian lain ke permukaan perancang saluran alur, dan atur nama untuk ke LookupNewWaterMarkActivity di tab Umum dari jendela properti. Aktivitas Pencarian ini mendapatkan nilai watermark baru dari tabel dengan data sumber yang disalin ke tujuan.

  11. Di jendela properti untuk aktivitas Pencarian kedua, beralih ke tab Pengaturan, dan klik Baru. Anda membuat himpunan data untuk menunjuk ke tabel sumber yang berisi nilai marka air baru (nilai maksimum LastModifyTime).

  12. Di jendela Himpunan Data Baru, pilih Azure SQL Database, lalu klik Lanjutkan.

  13. Di jendela Atur properti, masukkan SourceDataset untuk Namanya. Pilih AzureSqlDatabaseLinkedService untuk Layanan tertaut.

  14. Pilih [dbo].[data_source_table] untuk Tabel. Anda menentukan kueri pada himpunan data ini nanti dalam tutorial. Kueri lebih diutamakan daripada tabel yang Anda tentukan dalam langkah ini.

  15. Pilih Selesai.

  16. Beralih ke editor alur dengan mengeklik tab alur di bagian atas atau dengan mengeklik nama alur di tampilan pohon di sebelah kiri. Di jendela properti untuk aktivitas Pencarian, konfirmasikan bahwa SourceDataset dipilih untuk bidang Himpunan Data Sumber.

  17. Pilih Kueri untuk bidang Gunakan Kueri, dan masukkan kueri berikut ini: Anda hanya memilih nilai maksimum LastModifytime dari data_source_table. Pastikan Anda juga sudah memeriksa Hanya baris pertama.

    select MAX(LastModifytime) as NewWatermarkvalue from data_source_table

    Second lookup activity - query

  18. Di kotak alat Aktivitas, perluas Pindahkan & Transformasi, dan tarik-lepas aktivitas Salin dari kotak alat Aktivitas, dan tetapkan nama IncrementalCopyActivity.

  19. Hubungkan kedua aktivitas Pencarian ke aktivitas Salin dengan menyeret tombol hijau yang dilampirkan ke aktivitas Pencarian ke aktivitas Salin. Rilis tombol mouse saat Anda melihat warna batas aktivitas Salin berubah menjadi biru.

    Connection Lookup activities to Copy activity

  20. Pilih aktivitas Salin dan konfirmasikan bahwa Anda melihat properti untuk aktivitas di jendela Properti.

  21. Beralih ke tab Sumber di jendela Properti, lalu lakukan langkah-langkah berikut:

    1. Pilih SourceDataset untuk bidang Himpunan Data Sumber.

    2. Pilih Kueri untuk bidang Gunakan Kueri.

    3. Masukkan kueri SQL berikut ini untuk bidang Kueri.

      select * from data_source_table where LastModifytime > '@{activity('LookupOldWaterMarkActivity').output.firstRow.WatermarkValue}' and LastModifytime <= '@{activity('LookupNewWaterMarkActivity').output.firstRow.NewWatermarkvalue}'

      Copy activity - source

  22. Beralih ke tab Sink, dan klik + Baru untuk bidang Sink Himpunan Data.

  23. Dalam tutorial ini, sink penyimpanan data adalah jenis Azure Blob Storage. Oleh karena itu, pilih Azure Blob Storage, dan klik Lanjutkan di jendela Himpunan Data Baru.

  24. Di jendela Pilih Format, pilih jenis format data Anda, lalu klik Lanjutkan.

  25. Di jendela Atur properti, masukkan SinkDataset untuk Namanya. Untuk Layanan Tertaut, pilih + Baru. Di langkah ini, Anda membuat koneksi (layanan tertaut) ke Azu Blob storage Anda.

  26. Di jendela Layanan Tertaut Baru (Azure Blob Storage), lakukan langkah-langkah berikut ini:

    1. Masukkan AzureStorageLinkedService untuk Nama.
    2. Pilih akun Azure Storage Anda untuk nama akun Azure Storage.
    3. Uji Koneksi, lalu klik Selesai.

  27. Di jendela Atur Properti, konfirmasikan bahwa AzureStorageLinkedService dipilih untuk Layanan tertaut. Selanjutnya pilih Selesai.

  28. Buka tab Koneksi di SinkDataset dan lakukan langkah-langkah berikut ini:

    1. Untuk bidang Jalur file, masukkan adftutorial/inkrementalcopy. adftutorial adalah nama kontainer blob dan incrementalcopy adalah nama folder. Cuplikan ini mengasumsikan Anda memiliki kontainer blob bernama adftutorial di penyimpanan blob Anda. Buat kontainer jika tidak ada, atau atur ke nama kontainer yang sudah ada. Azure Data Factory secara otomatis membuat folder output incrementalcopy jika tidak ada. Anda juga dapat menggunakan tombol Telusurijalur File untuk menavigasi ke folder dalam kontainer blob.
    2. Untuk bagian File di bidang Jalur file, pilih Tambahkan konten dinamis [Alt+P], lalu masukkan @CONCAT('Incremental-', pipeline().RunId, '.txt') di jendela yang terbuka. Selanjutnya pilih Selesai. Nama file dibuat secara dinamis menggunakan ekspresi. Setiap eksekusi alur memiliki ID unik. Aktivitas Salin menggunakan eksekusi ID untuk menghasilkan nama file.

  29. Beralih ke editor alur dengan mengeklik tab alur di bagian atas atau dengan mengeklik nama alur di tampilan pohon di sebelah kiri.

  30. Di panel Aktivitas, perluas Umum dan tarik-lepas aktivitas Prosedur Tersimpan dari kotak alat Aktivitas ke permukaan perancang alur. Sambungkan output hijau (Sukses) dari aktivitas Salin ke aktivitas Prosedur Tersimpan.

  31. Pilih Aktivitas Prosedur Tersimpan di perancang alur, ubah namanya menjadi StoredProceduretoWriteWatermarkActivity.

  32. Beralih ke tab Akun SQL, lalu pilih AzureSqlDatabaseLinkedService untuk Layanan tertaut.

  33. Beralih ke tab Prosedur Tersimpan, dan lakukan langkah-langkah berikut:

    1. Untuk Nama prosedur tersimpan, pilih usp_write_watermark.

    2. Untuk menentukan nilai untuk parameter prosedur tersimpan, klik Impor parameter dan masukkan nilai-nilai berikut untuk parameter:

      Nama Jenis Nilai
      LastModifiedtime DateTime @{activity('LookupNewWaterMarkActivity').output.firstRow.NewWatermarkvalue}
      TableName String @{activity('LookupOldWaterMarkActivity').output.firstRow.TableName}

      Stored Procedure Activity - stored procedure settings

  34. Untuk memvalidasi pengaturan alur, pilih Validasi pada toolbar. Konfirmasikan bahwa tidak ada kesalahan validasi. Untuk menutup jendela Laporan Validasi Alur, klik >>.

  35. Terbitkan entitas (layanan tertaut, himpunan data, dan alur) ke layanan Azure Data Factory dengan memilih tombol Terbitkan Semua. Tunggu hingga Anda melihat pesan penerbitan berhasil.

Memicu proses alur

  1. Klik Tambahkan Pemicu pada toolbar, dan klik Picu Sekarang.

  2. Di jendela Ekseskusi Alur, pilih Selesai.

Memantau eksekusi alur

  1. Beralih ke tab Monitor di bagian kiri. Anda melihat status eksekusi alur yang dipicu oleh pemicu manual. Anda dapat menggunakan tautan di bawah kolomNAMA ALUR untuk menampilkan detail eksekusi dan untuk menjalankan ulang alur.

  2. Untuk melihat eksekusi aktivitas yang terkait dengan eksekusi alur, pilih tautan di bawah kolom NAMA ALUR. Untuk detail tentang eksekusi aktivitas, pilih tautan Detail (ikon kacamata) di bawah kolom NAMA AKTIVITAS. Pilih Semua eksekusi alur di bagian atas untuk kembali ke tampilan Eksekusi Alur. Untuk me-refresh tampilan, pilih Refresh.

Meninjau hasil

  1. Sambungkan ke Akun Azure Storage Anda menggunakan alat seperti Azure Storage Explorer. Pastikan file output dibuat di folder incrementalcopy di kontainer adftutorial.

    First output file

  2. Buka file output dan perhatikan bahwa semua data disalin dari data_source_table ke file blob.

    1,aaaa,2017-09-01 00:56:00.0000000
2,bbbb,2017-09-02 05:23:00.0000000
3,cccc,2017-09-03 02:36:00.0000000
4,dddd,2017-09-04 03:21:00.0000000
5,eeee,2017-09-05 08:06:00.0000000

  3. Periksa nilai terbaru dari watermarktable. Anda melihat nilai marka air sudah diperbarui.

    Select * from watermarktable

    Berikut adalah outputnya:

    | TableName | WatermarkValue |
| --------- | -------------- |
| data_source_table | 2017-09-05	8:06:00.000 |

Menambahkan lebih banyak data ke sumber

Sisipkan data baru ke database Anda (penyimpanan sumber data).

INSERT INTO data_source_table
VALUES (6, 'newdata','9/6/2017 2:23:00 AM')

INSERT INTO data_source_table
VALUES (7, 'newdata','9/7/2017 9:01:00 AM')

Data yang diperbarui dalam database Anda adalah:

PersonID | Name | LastModifytime
-------- | ---- | --------------
1 | aaaa | 2017-09-01 00:56:00.000
2 | bbbb | 2017-09-02 05:23:00.000
3 | cccc | 2017-09-03 02:36:00.000
4 | dddd | 2017-09-04 03:21:00.000
5 | eeee | 2017-09-05 08:06:00.000
6 | newdata | 2017-09-06 02:23:00.000
7 | newdata | 2017-09-07 09:01:00.000

Memicu eksekusi alur lainnya

  1. Beralih ke tab Edit. Klik alur dalam tampilan pohon jika tidak dibuka di perancang.

  2. Klik Tambahkan Pemicu pada toolbar, dan klik Picu Sekarang.

Memantau eksekusi alur kedua

  1. Beralih ke tab Monitor di bagian kiri. Anda melihat status eksekusi alur yang dipicu oleh pemicu manual. Anda dapat menggunakan tautan di bawah kolom NAMA ALUR untuk menampilkan detail aktivitas dan untuk menjalankan ulang alur.

  2. Untuk melihat eksekusi aktivitas yang terkait dengan eksekusi alur, pilih tautan di bawah kolom NAMA ALUR. Untuk detail tentang eksekusi aktivitas, pilih tautan Detail (ikon kacamata) di bawah kolom NAMA AKTIVITAS. Pilih Semua eksekusi alur di bagian atas untuk kembali ke tampilan Eksekusi Alur. Untuk me-refresh tampilan, pilih Refresh.

Memverifikasi output kedua

  1. Dalam penyimpanan blob, Anda melihat file lain dibuat. Dalam tutorial ini, nama file baru tersebut adalah Incremental-<GUID>.txt. Buka file tersebut, dan Anda akan melihat dua baris rekaman di dalamnya.

    6,newdata,2017-09-06 02:23:00.0000000
7,newdata,2017-09-07 09:01:00.0000000

  2. Periksa nilai terbaru dari watermarktable. Anda melihat nilai marka air telah diperbarui kembali.

    Select * from watermarktable

    Contoh output:

    | TableName | WatermarkValue |
| --------- | -------------- |
| data_source_table | 2017-09-07 09:01:00.000 |

Konten terkait

Anda melakukan langkah-langkah berikut dalam tutorial ini:

  • Siapkan penyimpanan data untuk menyimpan nilai marka air.
  • Membuat pabrik data.
  • Membuat layanan tertaut.
  • Buat himpunan data sumber, sink, dan marka air.
  • Buat alur.
  • Jalankan alur.
  • Pantau eksekusi alur.
  • Meninjau hasil
  • Tambahkan lebih banyak data ke sumber.
  • Jalankan alur lagi.
  • Memantau eksekusi alur kedua
  • Meninjau hasil dari eksekusi kedua

Dalam tutorial ini, alur menyalin data dari satu tabel di Azure SQL Database ke penyimpanan Blob. Lanjutkan ke tutorial berikut untuk mempelajari cara menyalin data dari beberapa tabel dalam database SQL Server ke Azure SQL Database.

Memuat data secara bertambah bertahap dari beberapa tabel di SQL Server ke Azure SQL Database