Aktivitas Prosedur Tersimpan SQL Server

  • Artikel
  • 11 menit untuk membaca

Catatan

Artikel ini berlaku untuk versi 1 Azure Data Factory. Jika Anda menggunakan versi layanan Data Factory saat ini, lihat mengubah data menggunakan aktivitas prosedur tersimpan di Data Factory.

Gambaran Umum

Anda menggunakan aktivitas transformasi data di alur Data Factory untuk mengubah dan memproses data mentah menjadi prediksi dan wawasan. Aktivitas Prosedur Tersimpan merupakan salah satu aktivitas transformasi yang didukung oleh Data Factory. Artikel ini dibuat berdasarkan artikel aktivitas transformasi data, yang memberikan gambaran umum tentang transformasi data dan aktivitas transformasi yang didukung di Data Factory.

Anda dapat menggunakan Aktivitas Prosedur Tersimpan untuk memanggil prosedur tersimpan di salah satu penyimpanan data berikut di perusahaan Anda atau di mesin virtual (VM) Azure:

  • Azure SQL Database
  • Azure Synapse Analytics
  • Database SQL Server. Jika Anda menggunakan SQL Server, instal Gateway Manajemen Data di mesin yang sama yang menghosting database atau di mesin terpisah yang memiliki akses ke database. Gateway Manajemen Data adalah komponen yang menghubungkan sumber data lokal/di Azure VM dengan layanan cloud dengan cara yang aman dan terkelola. Lihat artikel Gateway Manajemen Data untuk detailnya.

Penting

Saat menyalin data ke Azure SQL Database atau SQL Server, Anda dapat mengonfigurasi SqlSink dalam aktivitas salin untuk mengaktifkan prosedur tersimpan dengan menggunakan properti sqlWriterStoredProcedureName. Untuk mengetahui informasi selengkapnya, lihat Memanggil prosedur tersimpan dari aktivitas salin. Untuk detail tentang properti, lihat artikel konektor berikut: Azure SQL Database, SQL Server. Memanggil prosedur tersimpan saat menyalin data ke Azure Synapse Analytics dengan menggunakan aktivitas salinan tidak didukung. Namun, Anda dapat menggunakan aktivitas prosedur tersimpan untuk memanggil prosedur tersimpan di Azure Synapse Analytics.

Saat menyalin data dari Azure SQL Database, SQL Server, atau Azure Synapse Analytics, Anda dapat mengonfigurasi SqlSource dalam aktivitas salin untuk mengaktifkan prosedur tersimpan untuk membaca data dari database sumber dengan menggunakan properti sqlReaderStoredProcedureName. Untuk mengetahui informasi selengkapnya, lihat artikel konektor berikut: Azure SQL Database, SQL Server, Azure Synapse Analytics

Panduan berikut menggunakan Aktivitas Prosedur Tersimpan dalam alur untuk memanggil prosedur tersimpan di Azure SQL Database.

Panduan

Sampel tabel dan prosedur tersimpan

  1. Buat tabel berikut di Azure SQL Database Anda menggunakan SQL Server Management Studio atau alat lain yang nyaman bagi Anda. Kolom datetimestamp adalah tanggal dan waktu saat ID terkait dibuat.

    CREATE TABLE dbo.sampletable
(
    Id uniqueidentifier,
    datetimestamp nvarchar(127)
)
GO

CREATE CLUSTERED INDEX ClusteredID ON dbo.sampletable(Id);
GO

    Id adalah pengenal unik, dan kolom datetimestamp adalah tanggal dan waktu saat ID terkait dibuat.

    Sample data

    Dalam sampel ini, prosedur tersimpan berada dalam Azure SQL Database. Jika prosedur tersimpan ada di Azure Synapse Analytics dan SQL Server Database, pendekatannya serupa. Untuk database SQL Server, Anda harus menginstal Gateway Manajemen Data.

  2. Buat prosedur tersimpan berikut yang menyisipkan data ke dalam sampletable.

    CREATE PROCEDURE usp_sample @DateTime nvarchar(127)
AS

BEGIN
    INSERT INTO [sampletable]
    VALUES (newid(), @DateTime)
END

    Penting

    Nama dan kapitalisasi parameter (DateTime dalam contoh ini) harus cocok dengan parameter yang ditentukan dalam JSON alur/aktivitas. Dalam definisi prosedur tersimpan, pastikan bahwa @ digunakan sebagai awalan untuk parameter.

Membuat pabrik data

  1. Masuk ke portal Microsoft Azure.

  2. Klik BARU di menu sebelah kiri, klik Kecerdasan + Analitik, lalu klik Azure Data Factory.

    New data factory 1

  3. Di bilah Pabrik data baru, masukkan SProcDF untuk Nama. Nama Azure Data Factory unik secara global. Anda perlu memberi awalan pada nama pabrik data dengan nama Anda, untuk memungkinkan keberhasilan pembuatan pabrik.

    New data factory 2

  4. Pilih Langganan Azure Anda.

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

    1. Klik Buat baru dan masukkan nama untuk grup sumber daya.
    2. Pilih Gunakan yang sudah ada untuk memilih grup sumber daya yang sudah ada.

  6. Pilih lokasi untuk pabrik data tersebut.

  7. Pilih Sematkan ke dasbor sehingga Anda dapat melihat pabrik data di dasbor saat masuk lagi.

  8. Klik Buat di bilah Pabrik data baru.

  9. Anda melihat pabrik data sedang dibuat di dasbor portal Microsoft Azure. Setelah pabrik data berhasil dibuat, Anda akan melihat halaman pabrik data, yang menampilkan konten pabrik data kepada Anda.

    Data Factory home page

Membuat layanan tertaut Azure SQL

Setelah membuat pabrik data, Anda membuat layanan tertaut Azure SQL yang menautkan database Anda di Azure SQL Database, yang berisi tabel yang dapat dibuat sampel dan prosedur tersimpan usp_sample, ke pabrik data Anda.

  1. Klik Tulis dan sebarkan pada bilah Data Factory untuk SProcDF untuk meluncurkan Editor Data Factory.

  2. Pilih Penyimpanan data baru pada bilah perintah, lalu pilih Azure SQL Database. Anda akan melihat skrip JSON untuk membuat layanan tertaut Azure SQL di bagian editor.

    New data store 1

  3. Di skrip JSON, buat perubahan berikut:

    1. Ganti <servername> dengan nama server Anda.

    2. Ganti <databasename> dengan database tempat Anda membuat tabel dan prosedur tersimpan.

    3. Ganti <username@servername> dengan akun pengguna yang memiliki akses ke database.

    4. Ganti <password> dengan kata sandi akun pengguna.

      New data store 2

  4. Klik Sebarkan pada bilah perintah untuk menyebarkan layanan yang ditautkan. Konfirmasikan bahwa Anda melihat AzureSqlLinkedService dalam tampilan pohon di sebelah kiri.

    tree view with linked service 1

Membuat himpunan data output

Anda harus menentukan himpunan data output untuk aktivitas prosedur tersimpan meskipun prosedur tersimpan tidak menghasilkan data apa pun. Hal ini karena ini merupakan himpunan data output yang mendorong jadwal aktivitas (seberapa sering aktivitas dijalankan - per jam, harian, dll.). Himpunan data output harus menggunakan layanan tertaut yang merujuk ke Azure SQL Database atau Azure Synapse Analytics atau SQL Server Database tempat Anda ingin prosedur tersimpan dijalankan. Himpunan data output dapat berfungsi sebagai cara untuk meneruskan hasil prosedur tersimpan untuk pemrosesan berikutnya oleh aktivitas lain (aktivitas rantai di alur. Namun, Data Factory tidak otomatis menulis output prosedur tersimpan ke himpunan data ini. Ini adalah prosedur tersimpan yang menulis ke tabel SQL yang ditunjukkan oleh himpunan data output. Dalam beberapa kasus, himpunan data output dapat menjadi himpunan data dummy (himpunan data yang menunjuk ke tabel yang tidak benar-benar menyimpan output dari prosedur tersimpan). Himpunan data dummy ini hanya digunakan untuk menentukan jadwal untuk menjalankan aktivitas prosedur tersimpan.

  1. Klik ... Selengkapnya di toolbar, klik Himpunan data baru, lalu klik Azure SQL. Himpunan data baru di bilah perintah lalu pilih Azure SQL.

    tree view with linked service 2

  2. Salin/tempel skrip JSON berikut ke editor JSON.

    {
    "name": "sprocsampleout",
    "properties": {
        "type": "AzureSqlTable",
        "linkedServiceName": "AzureSqlLinkedService",
        "typeProperties": {
            "tableName": "sampletable"
        },
        "availability": {
            "frequency": "Hour",
            "interval": 1
        }
    }
}

  3. Klik Sebarkan di bilah perintah untuk menyebarkan himpunan data. Konfirmasikan bahwa Anda melihat himpunan data dalam tampilan pohon.

    tree view with linked services

Membuat alur dengan aktivitas SqlServerStoredProcedure

Sekarang, mari kita buat alur dengan aktivitas prosedur tersimpan.

Perhatikan properti berikut:

  • Properti type diatur ke SqlServerStoredProcedure.
  • Properti storedProcedureName di properti type diatur ke usp_sample (nama prosedur tersimpan).
  • Bagian StoredProcedureParameters berisi satu parameter bernama DateTime. Nama dan kapitalisasi parameter di JSON harus sesuai dengan nama dan kapitalisasi parameter dalam definisi prosedur tersimpan. Jika Anda perlu meneruskan null untuk parameter, gunakan sintaksis: "param1": null (semua huruf kecil).

  1. Klik ... Selengkapnya di bilah perintah, lalu klik Alur baru.

  2. Salin/tempel cuplikan JSON berikut:

    {
    "name": "SprocActivitySamplePipeline",
    "properties": {
        "activities": [
            {
                "type": "SqlServerStoredProcedure",
                "typeProperties": {
                    "storedProcedureName": "usp_sample",
                    "storedProcedureParameters": {
                        "DateTime": "$$Text.Format('{0:yyyy-MM-dd HH:mm:ss}', SliceStart)"
                    }
                },
                "outputs": [
                    {
                        "name": "sprocsampleout"
                    }
                ],
                "scheduler": {
                    "frequency": "Hour",
                    "interval": 1
                },
                "name": "SprocActivitySample"
            }
        ],
        "start": "2017-04-02T00:00:00Z",
        "end": "2017-04-02T05:00:00Z",
        "isPaused": false
    }
}

  3. Untuk menyebarkan alur, klik Sebarkan di toolbar.

Monitor saluran pipa

  1. Klik X untuk menutup bilah Editor Data Factory dan untuk menavigasi kembali ke bilah Data Factory, lalu klik Diagram.

    diagram tile 1

  2. Dalam Tampilan Diagram, Anda akan melihat gambaran umum alur, dan himpunan data yang digunakan dalam tutorial ini.

    diagram tile 2

  3. Dalam Tampilan Diagram, klik dua kali himpunan data sprocsampleout. Anda melihat irisan dalam status Siap. Harus ada lima irisan karena satu irisan dihasilkan untuk setiap jam antara waktu mulai dan waktu akhir dari JSON.

    diagram tile 3

  4. Saat irisan dalam status Siap, jalankan kueri select * from sampletable terhadap database untuk memverifikasi bahwa data disisipkan ke tabel menurut prosedur tersimpan.

    Output data

    Lihat Memantau alur untuk mengetahui informasi mendetail tentang pemantauan alur Azure Data Factory.

Menentukan himpunan data input

Di panduan, aktivitas prosedur tersimpan tidak memiliki himpunan data input. Jika Anda menentukan himpunan data input, aktivitas prosedur tersimpan tidak berjalan hingga irisan himpunan data input tersedia (dalam status Siap). Himpunan data dapat menjadi himpunan data eksternal (yang tidak dihasilkan oleh aktivitas lain dalam alur yang sama) atau himpunan data internal yang dihasilkan oleh aktivitas upstream (aktivitas yang berjalan sebelum aktivitas ini). Anda dapat menentukan beberapa himpunan data input untuk aktivitas prosedur tersimpan. Jika Anda melakukannya, aktivitas prosedur tersimpan hanya berjalan saat semua irisan himpunan data input tersedia (dalam status Siap). Himpunan data input tidak dapat digunakan dalam prosedur tersimpan sebagai parameter. Ini hanya digunakan untuk memeriksa dependensi sebelum memulai aktivitas prosedur tersimpan.

Menautkan dengan aktivitas lain

Jika Anda ingin menautkan aktivitas upstream dengan aktivitas ini, tentukan output aktivitas upstream sebagai input dari aktivitas ini. Saat Anda melakukannya, aktivitas prosedur tersimpan tidak berjalan sampai aktivitas upstream selesai dan himpunan data output dari aktivitas upstream tersedia (dalam status Siap). Anda dapat menentukan himpunan data output dari beberapa aktivitas upstream sebagai himpunan data input dari aktivitas prosedur tersimpan. Saat Anda melakukannya, aktivitas prosedur tersimpan hanya berjalan saat semua irisan himpunan data input tersedia (dalam status Siap).

Dalam contoh berikut, output aktivitas salin adalah: OutputDataset, yang merupakan input dari aktivitas prosedur tersimpan. Oleh karena itu, aktivitas prosedur tersimpan tidak berjalan sampai aktivitas salinan selesai dan irisan OutputDataset tersedia (dalam status Siap). Jika Anda menentukan beberapa himpunan data input, aktivitas prosedur tersimpan tidak berjalan hingga semua irisan himpunan data input tersedia (dalam status Siap). Himpunan data input tidak dapat digunakan langsung sebagai parameter untuk aktivitas prosedur tersimpan.

Untuk mengetahui informasi selengkapnya tentang aktivitas penautan, lihat beberapa aktivitas dalam alur

{
    "name": "ADFTutorialPipeline",
    "properties": {
        "description": "Copy data from a blob to blob",
        "activities": [
            {
                "type": "Copy",
                "typeProperties": {
                    "source": {
                        "type": "BlobSource"
                    },
                    "sink": {
                        "type": "BlobSink",
                        "writeBatchSize": 0,
                        "writeBatchTimeout": "00:00:00"
                    }
                },
                "inputs": [ { "name": "InputDataset" } ],
                "outputs": [ { "name": "OutputDataset" } ],
                "policy": {
                    "timeout": "01:00:00",
                    "concurrency": 1,
                    "executionPriorityOrder": "NewestFirst"
                },
                "name": "CopyFromBlobToSQL"
            },
            {
                "type": "SqlServerStoredProcedure",
                "typeProperties": {
                    "storedProcedureName": "SPSproc"
                },
                "inputs": [ { "name": "OutputDataset" } ],
                "outputs": [ { "name": "SQLOutputDataset" } ],
                "policy": {
                    "timeout": "01:00:00",
                    "concurrency": 1,
                    "retry": 3
                },
                "name": "RunStoredProcedure"
            }
        ],
        "start": "2017-04-12T00:00:00Z",
        "end": "2017-04-13T00:00:00Z",
        "isPaused": false,
    }
}

Demikian pula, untuk menghubungkan aktivitas prosedur penyimpanan dengan aktivitas downstream (aktivitas yang berjalan setelah aktivitas prosedur tersimpan selesai), tentukan himpunan data output dari aktivitas prosedur tersimpan sebagai input aktivitas downstream dalam alur.

Penting

Saat menyalin data ke Azure SQL Database atau SQL Server, Anda dapat mengonfigurasi SqlSink dalam aktivitas salin untuk mengaktifkan prosedur tersimpan dengan menggunakan properti sqlWriterStoredProcedureName. Untuk mengetahui informasi selengkapnya, lihat Memanggil prosedur tersimpan dari aktivitas salin. Untuk detail tentang properti, lihat artikel konektor berikut: Azure SQL Database, SQL Server.

Saat menyalin data dari Azure SQL Database, SQL Server, atau Azure Synapse Analytics, Anda dapat mengonfigurasi SqlSource dalam aktivitas salin untuk memanggil prosedur tersimpan untuk membaca data dari database sumber dengan menggunakan properti sqlReaderStoredProcedureName. Untuk mengetahui informasi selengkapnya, lihat artikel konektor berikut: Azure SQL Database, SQL Server, Azure Synapse Analytics

Format JSON

Berikut adalah format JSON untuk menetukan Aktivitas Prosedur Tersimpan:

{
    "name": "SQLSPROCActivity",
    "description": "description",
    "type": "SqlServerStoredProcedure",
    "inputs": [ { "name": "inputtable" } ],
    "outputs": [ { "name": "outputtable" } ],
    "typeProperties":
    {
        "storedProcedureName": "<name of the stored procedure>",
        "storedProcedureParameters":
        {
            "param1": "param1Value"
            …
        }
    }
}

Tabel berikut menjelaskan properti JSON ini:

Properti Deskripsi Diperlukan
nama Nama aktivitas Ya
deskripsi Teks yang menjelaskan untuk apa aktivitas tersebut digunakan Tidak
jenis Harus ditetapkan ke: SqlServerStoredProcedure Ya
inputs Opsional. Jika Anda menentukan himpunan data input, himpunan data tersebut harus tersedia (dalam status 'Siap') agar aktivitas prosedur tersimpan dapat dijalankan. Himpunan data input tidak dapat digunakan dalam prosedur tersimpan sebagai parameter. Ini hanya digunakan untuk memeriksa dependensi sebelum memulai aktivitas prosedur tersimpan. Tidak
outputs Anda harus menentukan himpunan data output untuk aktivitas prosedur tersimpan. Himpunan data output menentukan jadwal untuk aktivitas prosedur tersimpan (per jam, mingguan, bulanan, dll.).

Himpunan data output harus menggunakan layanan tertaut yang merujuk ke Azure SQL Database atau Azure Synapse Analytics atau SQL Server Database tempat Anda ingin prosedur tersimpan dijalankan.

Himpunan data output dapat berfungsi sebagai cara untuk meneruskan hasil prosedur tersimpan untuk pemrosesan berikutnya oleh aktivitas lain (aktivitas rantai di alur. Namun, Data Factory tidak otomatis menulis output prosedur tersimpan ke himpunan data ini. Ini adalah prosedur tersimpan yang menulis ke tabel SQL yang ditunjukkan oleh himpunan data output.

Dalam beberapa kasus, himpunan data output dapat menjadi himpunan data dummy, yang digunakan hanya untuk menentukan jadwal untuk menjalankan aktivitas prosedur tersimpan.		 Ya
storedProcedureName Tentukan nama prosedur tersimpan di Azure SQL Database, Azure Synapse Analytics, atau SQL Server yang diwakili oleh layanan tertaut yang digunakan tabel output. Ya
storedProcedureParameters Tentukan nilai untuk parameter prosedur tersimpan. Jika Anda perlu meneruskan null untuk parameter, gunakan sintaksis: "param1": null (semua huruf kecil). Lihat contoh berikut untuk mempelajari tentang menggunakan properti ini. Tidak

Meneruskan nilai statis

Sekarang, mari kita pertimbangkan untuk menambahkan kolom lain bernama 'Skenario' dalam tabel yang berisi nilai statis yang disebut 'Sampel dokumen'.

Sample data 2

Tabel:

CREATE TABLE dbo.sampletable2
(
    Id uniqueidentifier,
    datetimestamp nvarchar(127),
    scenario nvarchar(127)
)
GO

CREATE CLUSTERED INDEX ClusteredID ON dbo.sampletable2(Id);

Prosedur tersimpan:

CREATE PROCEDURE usp_sample2 @DateTime nvarchar(127) , @Scenario nvarchar(127)

AS

BEGIN
    INSERT INTO [sampletable2]
    VALUES (newid(), @DateTime, @Scenario)
END

Sekarang, teruskan parameter Scenario dan nilai dari aktivitas prosedur tersimpan. Bagian typeProperties dalam sampel sebelumnya terlihat seperti cuplikan berikut:

"typeProperties":
{
    "storedProcedureName": "usp_sample",
    "storedProcedureParameters":
    {
        "DateTime": "$$Text.Format('{0:yyyy-MM-dd HH:mm:ss}', SliceStart)",
        "Scenario": "Document sample"
    }
}

Himpunan data untuk Data Factory:

{
    "name": "sprocsampleout2",
    "properties": {
        "published": false,
        "type": "AzureSqlTable",
        "linkedServiceName": "AzureSqlLinkedService",
        "typeProperties": {
            "tableName": "sampletable2"
        },
        "availability": {
            "frequency": "Hour",
            "interval": 1
        }
    }
}

Alur Data Factory

{
    "name": "SprocActivitySamplePipeline2",
    "properties": {
        "activities": [
            {
                "type": "SqlServerStoredProcedure",
                "typeProperties": {
                    "storedProcedureName": "usp_sample2",
                    "storedProcedureParameters": {
                        "DateTime": "$$Text.Format('{0:yyyy-MM-dd HH:mm:ss}', SliceStart)",
                        "Scenario": "Document sample"
                    }
                },
                "outputs": [
                    {
                        "name": "sprocsampleout2"
                    }
                ],
                "scheduler": {
                    "frequency": "Hour",
                    "interval": 1
                },
                "name": "SprocActivitySample"
            }
        ],
        "start": "2016-10-02T00:00:00Z",
        "end": "2016-10-02T05:00:00Z"
    }
}