Cara mengindeks data dari Azure SQL di Azure AI Search

Artikel
07/31/2023

Dalam artikel ini, pelajari cara mengonfigurasi pengindeks yang mengimpor konten dari Azure SQL Database atau instans terkelola Azure SQL dan membuatnya dapat dicari di Azure AI Search.

Artikel ini melengkapi Buat pengindeks dengan informasi khusus untuk Azure SQL. Ini menggunakan REST API untuk menunjukkan alur kerja tiga bagian yang umum untuk semua pengindeks: membuat sumber data, membuat indeks, membuat pengindeks.

Artikel ini juga menyediakan:

Deskripsi kebijakan deteksi perubahan yang didukung oleh pengindeks Azure SQL sehingga Anda dapat menyiapkan pengindeksan bertambah bertahap.
Bagian tanya jawab umum (FAQ) untuk jawaban atas pertanyaan tentang kompatibilitas fitur.

Catatan

Sinkronisasi data real time tidak dimungkinkan dengan pengindeks. Pengindeks dapat mengindeks kembali tabel Anda paling cepat setiap lima menit. Jika pembaruan data perlu tercermin dalam indeks lebih cepat, sebaiknya dorong baris yang diperbarui secara langsung.

Prasyarat

Database Azure SQL dengan data dalam satu tabel atau tampilan, atau SQL Managed Instance dengan titik akhir publik.

Gunakan tabel jika data Anda besar atau jika Anda memerlukan pengindeksan inkremental menggunakan kemampuan deteksi perubahan asli SQL.

Gunakan tampilan jika Anda perlu mengonsolidasikan data dari beberapa tabel. Tampilan besar tidak ideal untuk pengindeks SQL. Solusinya adalah membuat tabel baru hanya untuk penyerapan ke dalam indeks Azure AI Search Anda. Anda akan dapat menggunakan pelacakan perubahan terintegrasi SQL, yang lebih mudah diimplementasikan daripada Tanda Air Tinggi.
Izin baca. Azure AI Search mendukung autentikasi SQL Server, di mana nama pengguna dan kata sandi disediakan di string koneksi. Atau, Anda dapat menyiapkan identitas terkelola dan menggunakan peran Azure.

Untuk mengerjakan contoh dalam artikel ini, Anda memerlukan klien REST.

Pendekatan lain untuk membuat pengindeks Azure SQL termasuk Azure SDK atau wizard Impor data di portal Azure. Jika Anda menggunakan portal Azure, pastikan akses ke semua jaringan publik diaktifkan di firewall Azure SQL dan klien memiliki akses melalui aturan masuk.

Menentukan sumber data

Definisi sumber data menentukan data untuk mengindeks, kredensial, dan kebijakan untuk mengidentifikasi perubahan dalam data. Sumber data didefinisikan sebagai sumber daya independen sehingga dapat digunakan oleh beberapa pengindeks.

Buat sumber data atau Perbarui sumber data untuk mengatur definisinya:

 POST https://myservice.search.windows.net/datasources?api-version=2020-06-30
 Content-Type: application/json
 api-key: admin-key

 {
     "name" : "myazuresqldatasource",
     "description" : "A database for testing Azure AI Search indexes.",
     "type" : "azuresql",
     "credentials" : { "connectionString" : "Server=tcp:<your server>.database.windows.net,1433;Database=<your database>;User ID=<your user name>;Password=<your password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;" },
     "container" : { 
         "name" : "name of the table or view that you want to index",
         "query" : null (not supported in the Azure SQL indexer)
         },
     "dataChangeDetectionPolicy": null,
     "dataDeletionDetectionPolicy": null,
     "encryptionKey": null,
     "identity": null
 }

Berikan nama unik untuk sumber data yang mengikuti konvensi penamaan Azure AI Search.
Atur "jenis" ke "azuresql" (diperlukan).
Atur "kredensial" ke string koneksi:
- Anda bisa mendapatkan string koneksi akses penuh dari portal Azure. Gunakan ADO.NET connection string opsi tersebut. Atur nama pengguna dan kata sandi.
- Atau, Anda dapat menentukan identitas terkelola string koneksi yang tidak menyertakan rahasia database dengan format berikut: Initial Catalog|Database=<your database name>;ResourceId=/subscriptions/<your subscription ID>/resourceGroups/<your resource group name>/providers/Microsoft.Sql/servers/<your SQL Server name>/;Connection Timeout=connection timeout length;.
Untuk informasi selengkapnya, lihat Koneksi ke pengindeks Azure SQL Database menggunakan identitas terkelola.

Menambahkan bidang pencarian ke indeks

Dalam indeks pencarian, tambahkan bidang yang sesuai dengan bidang dalam database SQL. Pastikan bahwa skema indeks pencarian kompatibel dengan skema sumber dengan menggunakan jenis data yang setara.

Buat atau perbarui indeks untuk menentukan bidang pencarian yang akan menyimpan data:

POST https://[service name].search.windows.net/indexes?api-version=2020-06-30
Content-Type: application/json
api-key: [Search service admin key]
{
    "name": "mysearchindex",
    "fields": [{
        "name": "id",
        "type": "Edm.String",
        "key": true,
        "searchable": false
    }, 
    {
        "name": "description",
        "type": "Edm.String",
        "filterable": false,
        "searchable": true,
        "sortable": false,
        "facetable": false,
        "suggestions": true
    }
  ]
}

Buat bidang kunci dokumen ("kunci": true) yang secara unik mengidentifikasi setiap dokumen pencarian. Ini adalah satu-satunya bidang yang diperlukan dalam indeks pencarian. Biasanya, kunci primer tabel dipetakan ke bidang kunci indeks. Kunci dokumen harus unik dan tidak null. Nilai dapat berupa numerik dalam data sumber, tetapi dalam indeks pencarian, kunci selalu berupa string.
Buat lebih banyak bidang untuk menambahkan lebih banyak konten yang dapat dicari. Lihat Membuat indeks untuk panduan.

Jenis data pemetaan

Tipe data SQL	Jenis bidang Pencarian Azure AI	Catatan
bit	Edm.Boolean, Edm.String
int, smallint, tinyint	Edm.Int32, Edm.Int64, Edm.String
bigint	Edm.Int64, Edm.String
nyata, mengambang	Edm.Double, Edm.String
smallmoney, numerik desimal uang	Edm.String	Pencarian Azure AI tidak mendukung konversi jenis desimal ke karena `Edm.Double` melakukannya akan kehilangan presisi
char, nchar, varchar, nvarchar	Edm.String Kumpulan(Edm.String)	String SQL dapat digunakan untuk mengisi bidang Collection(`Edm.String`) jika string mewakili array string JSON: `["red", "white", "blue"]`
smalldatetime, datetime, datetime2, date, datetimeoffset	Edm.DateTimeOffset, Edm.String
uniqueidentifer	Edm.String
geografi	Edm.GeographyPoint	Hanya instans geografi jenis POINT dengan SRID 4326 (yang merupakan default) yang didukung
rowversion	Tidak berlaku	Kolom versi baris tidak dapat disimpan dalam indeks pencarian, tetapi dapat digunakan untuk pelacakan perubahan
waktu, jangka waktu, biner, varbinary, gambar, xml, geometri, jenis CLR	Tidak berlaku	Tidak didukung

Mengonfigurasi dan menjalankan pengindeks Azure SQL

Setelah indeks dan sumber data dibuat, Anda siap untuk membuat pengindeks. Konfigurasi pengindeks menentukan input, parameter, dan properti yang mengontrol perilaku run time.

Buat atau perbarui pengindeks dengan memberinya nama dan mereferensikan sumber data dan indeks target:

POST https://[service name].search.windows.net/indexers?api-version=2020-06-30
Content-Type: application/json
api-key: [search service admin key]
{
    "name" : "[my-sqldb-indexer]",
    "dataSourceName" : "[my-sqldb-ds]",
    "targetIndexName" : "[my-search-index]",
    "disabled": null,
    "schedule": null,
    "parameters": {
        "batchSize": null,
        "maxFailedItems": 0,
        "maxFailedItemsPerBatch": 0,
        "base64EncodeKeys": false,
        "configuration": {
            "queryTimeout": "00:04:00",
            "convertHighWaterMarkToRowVersion": false,
            "disableOrderByHighWaterMarkColumn": false
        }
    },
    "fieldMappings": [],
    "encryptionKey": null
}

Di bawah parameter, bagian konfigurasi memiliki parameter khusus untuk Azure SQL:
- Batas waktu kueri default untuk eksekusi kueri SQL adalah 5 menit, yang dapat Anda ambil alih.
- "convertHighWaterMarkToRowVersion" mengoptimalkan kebijakan deteksi perubahan Marka Air Tinggi. Kebijakan deteksi perubahan diatur di sumber data. Jika Anda menggunakan kebijakan deteksi perubahan asli, parameter ini tidak berpengaruh.
- "disableOrderByHighWaterMarkColumn" menyebabkan kueri SQL yang digunakan oleh kebijakan tanda air tinggi menghilangkan klausa ORDER BY. Jika Anda menggunakan kebijakan deteksi perubahan asli, parameter ini tidak berpengaruh.
Tentukan pemetaan bidang jika ada perbedaan dalam nama atau jenis bidang, atau jika Anda memerlukan beberapa versi bidang sumber dalam indeks pencarian.
Lihat Membuat pengindeks untuk informasi selengkapnya tentang properti lain.

Pengindeks berjalan secara otomatis saat dibuat. Anda dapat mencegahnya dengan mengatur "dinonaktifkan" ke true. Untuk mengontrol eksekusi pengindeks, jalankan pengindeks sesuai permintaan atau letakkan sesuai jadwal.

Periksa status pengindeks

Untuk memantau status pengindeks dan riwayat eksekusi, kirim permintaan Dapatkan Status Pengindeks:

GET https://myservice.search.windows.net/indexers/myindexer/status?api-version=2020-06-30
  Content-Type: application/json  
  api-key: [admin key]

Respons mencakup status dan jumlah item yang diproses. Ini akan terlihat mirip dengan contoh berikut:

    {
        "status":"running",
        "lastResult": {
            "status":"success",
            "errorMessage":null,
            "startTime":"2022-02-21T00:23:24.957Z",
            "endTime":"2022-02-21T00:36:47.752Z",
            "errors":[],
            "itemsProcessed":1599501,
            "itemsFailed":0,
            "initialTrackingState":null,
            "finalTrackingState":null
        },
        "executionHistory":
        [
            {
                "status":"success",
                "errorMessage":null,
                "startTime":"2022-02-21T00:23:24.957Z",
                "endTime":"2022-02-21T00:36:47.752Z",
                "errors":[],
                "itemsProcessed":1599501,
                "itemsFailed":0,
                "initialTrackingState":null,
                "finalTrackingState":null
            },
            ... earlier history items
        ]
    }

Riwayat eksekusi berisi hingga 50 eksekusi yang terakhir selesai, yang diurutkan dalam urutan kronologis terbalik sehingga eksekusi terbaru menjadi yang pertama.

Mengindeks baris baru, diubah, dan dihapus

Jika database SQL Anda mendukung pelacakan perubahan, pengindeks pencarian hanya dapat mengambil konten baru dan yang diperbarui pada pengindeks berikutnya yang dijalankan.

Untuk mengaktifkan pengindeksan inkremental, atur properti "dataChangeDetectionPolicy" dalam definisi sumber data Anda. Properti ini memberi tahu pengindeks mekanisme pelacakan perubahan mana yang digunakan pada tabel atau tampilan Anda.

Untuk pengindeks Azure SQL, ada dua kebijakan deteksi perubahan:

"SqlIntegratedChangeTrackingPolicy" (hanya berlaku untuk tabel)
"HighWaterMarkChangeDetectionPolicy" (berfungsi untuk tabel dan tampilan)

Kebijakan Pelacakan Perubahan Terintegrasi SQL

Sebaiknya gunakan "SqlIntegratedChangeTrackingPolicy" untuk efisiensi dan kemampuannya untuk mengidentifikasi baris yang dihapus.

Persyaratan database:

SQL Server 2012 SP3 dan yang lebih baru, jika Anda menggunakan SQL Server di Azure VM
Azure SQL Database atau SQL Managed Instance
Tabel saja (tanpa tampilan)
Pada database, aktifkan pelacakan perubahan untuk tabel
Tidak ada kunci primer komposit (kunci primer yang berisi lebih dari satu kolom) pada tabel
Tidak ada indeks berkluster pada tabel. Sebagai solusinya, indeks berkluster apa pun harus dihilangkan dan dibuat ulang sebagai indeks non-kluster, namun, performa dapat terpengaruh di sumber dibandingkan dengan memiliki indeks berkluster

Kebijakan deteksi perubahan ditambahkan ke definisi sumber data. Untuk menggunakan kebijakan ini, buat atau perbarui sumber data Anda seperti ini:

POST https://myservice.search.windows.net/datasources?api-version=2020-06-30
Content-Type: application/json
api-key: admin-key
    {
        "name" : "myazuresqldatasource",
        "type" : "azuresql",
        "credentials" : { "connectionString" : "connection string" },
        "container" : { "name" : "table name" },
        "dataChangeDetectionPolicy" : {
            "@odata.type" : "#Microsoft.Azure.Search.SqlIntegratedChangeTrackingPolicy"
    }

Saat menggunakan kebijakan pelacakan perubahan terintegrasi SQL, jangan tentukan kebijakan deteksi penghapusan data terpisah. Kebijakan pelacakan perubahan terintegrasi SQL memiliki dukungan bawaan untuk mengidentifikasi baris yang dihapus. Namun, agar baris yang dihapus terdeteksi secara otomatis, kunci dokumen dalam indeks pencarian Anda harus sama dengan kunci utama dalam tabel SQL.

Catatan

Saat menggunakan TRUNCATE TABLE untuk menghapus sejumlah besar baris dari tabel SQL, pengindeks harus di-reset untuk me-reset status pelacakan perubahan untuk mengambil penghapusan baris.

Kebijakan Deteksi Perubahan Tanda Air Tinggi

Kebijakan deteksi perubahan ini bergantung pada kolom "tanda air tinggi" di tabel atau tampilan Anda yang mengambil versi atau waktu saat baris terakhir diperbarui. Jika Anda menggunakan tampilan, Anda harus menggunakan kebijakan high water mark.

Kolom tanda air tinggi harus memenuhi persyaratan berikut:

Semua sisipan menentukan nilai untuk kolom.
Semua pembaruan pada item juga mengubah nilai kolom.
Nilai kolom ini meningkat seiring masing-masing sisipan atau pembaruan.
Kueri dengan klausa WHERE dan ORDER BY berikut dapat dieksekusi secara efisien: WHERE [High Water Mark Column] > [Current High Water Mark Value] ORDER BY [High Water Mark Column]

Catatan

Kami sangat menyarankan penggunaan jenis data rowversion untuk kolom tanda air tinggi. Jika ada jenis data lain yang digunakan, pelacakan perubahan tidak dijamin untuk menangkap semua perubahan di hadapan transaksi yang dijalankan bersamaan dengan kueri pengindeks. Saat menggunakan rowversion dalam konfigurasi dengan replika baca-saja, Anda harus mengarahkan pengindeks ke replika utama. Hanya replika utama yang dapat digunakan untuk skenario sinkronisasi data.

Kebijakan deteksi perubahan ditambahkan ke definisi sumber data. Untuk menggunakan kebijakan ini, buat atau perbarui sumber data Anda seperti ini:

POST https://myservice.search.windows.net/datasources?api-version=2020-06-30
Content-Type: application/json
api-key: admin-key
    {
        "name" : "myazuresqldatasource",
        "type" : "azuresql",
        "credentials" : { "connectionString" : "connection string" },
        "container" : { "name" : "table or view name" },
        "dataChangeDetectionPolicy" : {
            "@odata.type" : "#Microsoft.Azure.Search.HighWaterMarkChangeDetectionPolicy",
            "highWaterMarkColumnName" : "[a rowversion or last_updated column name]"
        }
    }

Catatan

Jika tabel sumber tidak memiliki indeks pada kolom tanda air tinggi, kueri yang digunakan oleh pengindeks SQL mungkin kehabisan waktu. Secara khusus, ORDER BY [High Water Mark Column] klausa mengharuskan indeks berjalan secara efisien ketika tabel berisi banyak baris.

convertHighWaterMarkToRowVersion

Jika Anda menggunakan jenis data rowversion untuk kolom tanda air tinggi, pertimbangkan untuk mengatur convertHighWaterMarkToRowVersion properti dalam konfigurasi pengindeks. Mengatur properti ini ke true menghasilkan perilaku berikut:

Menggunakan jenis data rowversion untuk kolom tanda air tinggi dalam kueri SQL pengindeks. Penggunaan jenis data yang benar meningkatkan kinerja kueri pengindeks.
Mengurangi satu dari nilai rowversion sebelum kueri pengindeks berjalan. Tampilan dengan gabungan satu-ke-banyak mungkin memiliki baris dengan nilai rowversion duplikat. Mengurangi satu memastikan kueri pengindeks tidak melewatkan baris ini.

Untuk mengaktifkan properti ini, buat atau perbarui pengindeks dengan konfigurasi berikut:

    {
      ... other indexer definition properties
     "parameters" : {
            "configuration" : { "convertHighWaterMarkToRowVersion" : true } }
    }

queryTimeout

Jika Anda mengalami kesalahan waktu habis, atur queryTimeout pengaturan konfigurasi pengindeks ke nilai yang lebih tinggi dari batas waktu default 5 menit. Contohnya, untuk mengatur waktu habis menjadi 10 menit, buat atau perbarui pengindeks dengan konfigurasi berikut:

    {
      ... other indexer definition properties
     "parameters" : {
            "configuration" : { "queryTimeout" : "00:10:00" } }
    }

disableOrderByHighWaterMarkColumn

Anda juga dapat menonaktifkan klausa ORDER BY [High Water Mark Column]. Namun, ini tidak disarankan karena jika eksekusi pengindeks terganggu oleh kesalahan, pengindeks harus memproses ulang semua baris jika berjalan nanti, bahkan jika pengindeks telah memproses hampir semua baris pada saat itu terganggu. Untuk menonaktifkan klausa ORDER BY, gunakan pengaturan disableOrderByHighWaterMarkColumn dalam definisi pengindeks:

    {
     ... other indexer definition properties
     "parameters" : {
            "configuration" : { "disableOrderByHighWaterMarkColumn" : true } }
    }

Kebijakan Deteksi Penghapusan Kolom Penghapusan Sementara

Ketika baris dihapus dari tabel sumber, Anda biasanya juga ingin menghapus baris tersebut dari indeks pencarian. Jika Anda menggunakan kebijakan pelacakan perubahan terintegrasi SQL, penghapusan ini akan dilakukan otomatis. Akan tetapi, kebijakan pelacakan perubahan tanda air tinggi tidak akan membantu Anda dengan baris yang dihapus. Apa yang harus dilakukan?

Jika baris dihapus secara fisik dari tabel, Azure AI Search tidak memiliki cara untuk menyimpulkan adanya rekaman yang tidak ada lagi. Akan tetapi, Anda dapat menggunakan teknik "penghapusan sementara" untuk menghapus baris secara logis tanpa menghapusnya dari tabel. Tambahkan kolom ke tabel Anda atau tampilkan dan tandai baris sebagai dihapus menggunakan kolom tersebut.

Ketika menggunakan teknik penghapusan sementara, Anda dapat menentukan kebijakan penghapusan sementara sebagai berikut saat membuat atau memperbarui sumber data:

    {
        …,
        "dataDeletionDetectionPolicy" : {
           "@odata.type" : "#Microsoft.Azure.Search.SoftDeleteColumnDeletionDetectionPolicy",
           "softDeleteColumnName" : "[a column name]",
           "softDeleteMarkerValue" : "[the value that indicates that a row is deleted]"
        }
    }

softDeleteMarkerValue harus berupa string dalam representasi JSON sumber data Anda. Gunakan representasi string dari nilai aktual Anda. Contohnya, jika Anda memiliki kolom bilangan bulat di mana baris yang dihapus ditandai dengan nilai 1, gunakan "1". Jika Anda memiliki kolom BIT di mana baris yang dihapus ditandai dengan nilai Boolean sejati, gunakan string literal "True" atau "true", tanpa memedulikan masalahnya.

Jika Anda menyiapkan kebijakan penghapusan sementara dari portal Azure, jangan tambahkan tanda kutip di sekitar nilai penanda penghapusan sementara. Konten bidang sudah dipahami sebagai string dan akan diterjemahkan secara otomatis ke dalam string JSON untuk Anda. Dalam contoh di atas, cukup ketik 1, True atau true ke bidang portal.

FAQ

T: Bisakah saya mengindeks kolom Always Encrypted?

Tidak. Kolom Always Encrypted saat ini tidak didukung oleh pengindeks Azure AI Search.

T: Bisakah saya menggunakan pengindeks Azure SQL dengan database SQL yang berjalan di IaaS VM di Azure?

Ya. Akan tetapi, Anda perlu mengizinkan layanan pencarian Anda terhubung ke database Anda. Untuk informasi selengkapnya, lihat Mengonfigurasi koneksi dari pengindeks Azure AI Search ke SQL Server di Azure VM.

T: Bisakah saya menggunakan pengindeks Azure SQL dengan database SQL yang berjalan on-premise?

Tidak secara langsung. Kami tidak merekomendasikan atau mendukung koneksi langsung, karena hal itu akan mengharuskan Anda untuk membuka database Anda ke lalu lintas Internet. Konsumen telah berhasil dengan skenario penggunaan teknologi jembatan seperti Azure Data Factory ini. Untuk informasi selengkapnya, lihat Mendorong data ke indeks Pencarian Azure AI menggunakan Azure Data Factory.

T: Bisakah saya menggunakan replika sekunder dalam kluster perlindungan kegagalan sebagai sumber data?

Tergantung. Untuk pengindeksan penuh dari tabel atau tampilan, Anda bisa menggunakan replika sekunder.

Untuk pengindeksan bertahap, Azure AI Search mendukung dua kebijakan deteksi perubahan: Pelacakan perubahan terintegrasi SQL dan Tanda Air Tinggi.

Pada replika baca-saja, SQL Database tidak mendukung pelacakan perubahan terintegrasi. Oleh karena itu, Anda harus menggunakan kebijakan Tanda Air Tinggi.

Rekomendasi standar kami adalah menggunakan jenis data rowversion untuk kolom tanda air tinggi. Namun, menggunakan rowversion bergantung pada MIN_ACTIVE_ROWVERSION fungsi , yang tidak didukung pada replika baca-saja. Oleh karena itu, Anda harus mengarahkan pengindeks ke replika utama jika Anda menggunakan rowversion.

Jika Anda mencoba menggunakan rowversion pada replika baca-saja, Anda akan melihat kesalahan berikut:

"Menggunakan kolom rowversion untuk pelacakan perubahan tidak didukung pada replika ketersediaan sekunder (baca-saja). Harap perbarui sumber data dan tentukan koneksi ke replika ketersediaan utama. Properti 'Kemampuan Pembaruan' database saat ini adalah 'READ_ONLY'".

T: Bisakah saya menggunakan kolom alternatif non-rowversion untuk pelacakan perubahan tanda air tinggi?

Tidak disarankan. Sinkronisasi data yang andal hanya dimungkinkan oleh rowversion. Akan tetapi, tergantung pada logika aplikasi Anda, mungkin aman jika:

Anda dapat memastikan bahwa ketika pengindeks berjalan, tidak ada transaksi terutang pada tabel yang sedang diindeks (misalnya, semua pembaruan tabel terjadi sebagai batch pada jadwal, dan jadwal pengindeks Azure AI Search diatur untuk menghindari tumpang tindih dengan jadwal pembaruan tabel).
Anda secara berkala melakukan pengindeksan ulang penuh untuk mengambil baris yang terlewat.

Cara mengindeks data dari Azure SQL di Azure AI Search

Prasyarat

Menentukan sumber data

Menambahkan bidang pencarian ke indeks

Jenis data pemetaan

Mengonfigurasi dan menjalankan pengindeks Azure SQL

Periksa status pengindeks

Mengindeks baris baru, diubah, dan dihapus

Kebijakan Pelacakan Perubahan Terintegrasi SQL

Kebijakan Deteksi Perubahan Tanda Air Tinggi

convertHighWaterMarkToRowVersion

queryTimeout

disableOrderByHighWaterMarkColumn

Kebijakan Deteksi Penghapusan Kolom Penghapusan Sementara

FAQ

Sumber Daya Tambahan: