Mengkueri Cosmos DB dengan Synapse SQL

  • 5 menit

Selain menggunakan kumpulan Spark, Anda juga dapat mengkueri kontainer analitik Azure Cosmos DB dengan menggunakan kumpulan SQL tanpa server bawaan di Azure Synapse Analytics. Untuk melakukan ini, Anda dapat menggunakan OPENROWSET fungsi SQL untuk menyambungkan ke layanan tertaut untuk database Azure Cosmos DB Anda.

Menggunakan OPENROWSET dengan kunci autentikasi

Secara default, akses ke akun Azure Cosmos DB diautentikasi oleh kunci autentikasi. Anda dapat menggunakan kunci ini sebagai bagian dari string koneksi dalam pernyataan OPENROWSET untuk menyambungkan melalui layanan tertaut dari kumpulan SQL, seperti yang ditunjukkan dalam contoh berikut:

SELECT *
FROM OPENROWSET(​
    'CosmosDB',
    'Account=my-cosmos-db;Database=my-db;Key=abcd1234....==',
    [my-container]) AS products_data

Tip

Anda dapat menemukan kunci primer dan sekunder untuk akun Cosmos DB Anda di halaman Kunci di portal Azure.

Hasil kueri ini mungkin terlihat seperti berikut ini, termasuk metadata dan bidang yang ditentukan aplikasi dari item dalam kontainer Azure Cosmos DB:

_rid _ts productID productName id _etag
mjMaAL...== 1655414791 123 Widget 7248f072-11c3-42b1-a368-... 54004b09-0000-2300-...
mjMaAL...== 1655414829 124 Wotsit dc33131c-65c7-421a-a0f7-... 5400ca09-0000-2300-...
mjMaAL...== 1655414835 125 Thingumy ce22351d-78c7-428a-a1h5-... 5400ca09-0000-2300-...
... ... ... ... ... ...

Data diambil dari penyimpanan analitik, dan kuerinya tidak akan memengaruhi penyimpanan operasional.

Menggunakan OPENROWSET dengan informasi masuk

Alih-alih menyertakan kunci autentikasi di setiap panggilan ke OPENROWSET, Anda dapat menentukan informasi masuk yang merangkum informasi autentikasi untuk akun Cosmos DB Anda, dan menggunakan informasi masuk dalam kueri berikutnya. Untuk membuat informasi masuk, gunakan pernyataan CREATE CREDENTIAL, seperti yang ditunjukkan dalam contoh berikut:

 CREATE CREDENTIAL my_credential
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = 'abcd1234....==';

Setelah informasi masuk sudah siap, Anda dapat menggunakannya dalam fungsi OPENROWSET, seperti keterangan berikut:

SELECT *
FROM OPENROWSET(​PROVIDER = 'CosmosDB',
                CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                OBJECT = 'my-container',
                SERVER_CREDENTIAL = 'my_credential'
) AS products_data

Sekali lagi, hasilnya menyertakan metadata dan bidang yang ditentukan aplikasi dari penyimpanan analitik:

_rid _ts productID productName id _etag
mjMaAL...== 1655414791 123 Widget 7248f072-11c3-42b1-a368-... 54004b09-0000-2300-...
mjMaAL...== 1655414829 124 Wotsit dc33131c-65c7-421a-a0f7-... 5400ca09-0000-2300-...
mjMaAL...== 1655414835 125 Thingumy ce22351d-78c7-428a-a1h5-... 5400ca09-0000-2300-...
... ... ... ... ... ...

Menentukan skema

Sintaks OPENROWSET menyertakan klausul WITH yang dapat Anda gunakan guna menentukan skema untuk set baris atau rowset yang dihasilkan. Anda bisa menggunakan sintaks ini untuk menetapkan bidang individual dan menetapkan tipe data, seperti yang diperlihatkan dalam contoh berikut:

 SELECT *
 FROM OPENROWSET(​PROVIDER = 'CosmosDB',
                 CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                 OBJECT = 'my-container',
                 SERVER_CREDENTIAL = 'my_credential'
 )
 WITH (
    productID INT,
    productName VARCHAR(20)
 ) AS products_data

Dalam hal ini, dengan asumsi bahwa bidang di penyimpanan analitik menyertakan productID dan productName, rowset yang dihasilkan akan menyerupai tabel berikut:

productID productName
123 Widget
124 Wotsit
125 Thingumy
... ...

Tentu saja, Anda dapat menentukan nama kolom individual dalam klausul SELECT (misalnya SELECT productID, productName ...), sehingga kemampuan dalam menentukan kolom individual ini terlihat terbatas dalam penggunaannya. Namun, pertimbangkan kasus saat dokumen JSON sumber yang disimpan di penyimpanan operasional menyertakan beberapa tingkat bidang, seperti yang ditunjukkan dalam contoh berikut:

{
    "productID": 126,
    "productName": "Sprocket",
    "supplier": {
        "supplierName": "Contoso",
        "supplierPhone": "555-123-4567"
    }
    "id": "62588f072-11c3-42b1-a738-...",
    "_rid": "mjMaAL...==",
    ...
}

Klausul WITH ini mendukung penyertaan jalur JSON eksplisit, sehingga Anda dapat menangani bidang berlapis dan menetapkan alias ke nama bidang, seperti yang ditunjukkan dalam contoh berikut:

 SELECT *
 FROM OPENROWSET(​PROVIDER = 'CosmosDB',
                 CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                 OBJECT = 'my-container',
                 SERVER_CREDENTIAL = 'my_credential'
 )
 WITH (
    ProductNo INT '$.productID',
    ProductName VARCHAR(20) '$.productName',
    Supplier VARCHAR(20) '$.supplier.supplierName',
    SupplierPhoneNo VARCHAR(15) '$.supplier.supplierPhone'
 ) AS products_data

Hasil kueri ini akan menyertakan baris berikut untuk produk 126:

ProductNo ProductName Pemasok SupplierPhoneNo
126 Sprocket Contoso 555-123-4567

Membuat tampilan dalam database

Jika Anda sering memerlukan untuk mengkueri data yang sama, atau apabila Anda perlu menggunakan alat pelaporan dan visualisasi yang mengandalkan pernyataan SELECT yang tidak menyertakan fungsi OPENROWSET, Anda bisa menggunakan tampilan untuk mengabstraksi data. Untuk membuat tampilan, Anda harus membuat database baru untuk menentukannya (tampilan yang ditentukan pengguna dalam database master tidak didukung), seperti yang diperlihatkan dalam contoh berikut:

CREATE DATABASE sales_db
   COLLATE Latin1_General_100_BIN2_UTF8;
 GO;

 USE sales_db;
 GO;

 CREATE VIEW products
 AS
 SELECT *
 FROM OPENROWSET(​PROVIDER = 'CosmosDB',
                 CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                 OBJECT = 'my-container',
                 SERVER_CREDENTIAL = 'my_credential'
 )
 WITH (
    ProductNo INT '$.productID',
    ProductName VARCHAR(20) '$.productName',
    Supplier VARCHAR(20) '$.supplier.supplierName',
    SupplierPhoneNo VARCHAR(15) '$.supplier.supplierPhone'
 ) AS products_data
 GO

Tip

Saat membuat database yang akan mengakses data di Cosmos DB, pilihan yang terbaik adalah dengan menggunakan kolase berbasis UTF-8 untuk memastikan kompatibilitas dengan string di Cosmos DB.

Setelah tampilan dibuat, pengguna dan aplikasi klien dapat mengkuerinya seperti tampilan atau tabel SQL lainnya:

SELECT * FROM products;

Pertimbangan untuk kumpulan SQL Tanpa Server dan Azure Cosmos DB

Saat berencana menggunakan kumpulan SQL tanpa server untuk mengkueri data di penyimpanan analitik Azure Cosmos DB, pertimbangkan praktik terbaik berikut:

  • Provisikan penyimpanan analitik Azure Cosmos DB Anda dan aplikasi klien apa pun (misalnya Microsoft Power BI) di wilayah yang sama dengan kumpulan SQL tanpa server.

    Kontainer Azure Cosmos DB dapat direplikasi ke beberapa wilayah. Jika Anda memiliki kontainer multi-wilayah, Anda dapat menentukan parameter region dalam string koneksi OPENROWSET untuk memastikan bahwa kueri dikirim ke replika regional kontainer tertentu.

  • Saat menggunakan kolom string, gunakan fungsi OPENROWSET dengan klausul WITH eksplisit, dan tentukan panjang data yang sesuai untuk data string.