Wykonywanie zapytań względem usługi Cosmos DB przy użyciu usługi Synapse SQL

  • 5 min

Oprócz korzystania z puli Spark można również wykonywać zapytania względem kontenera analitycznego usługi Azure Cosmos DB przy użyciu wbudowanej bezserwerowej puli SQL w usłudze Azure Synapse Analytics. W tym celu możesz użyć OPENROWSET funkcji SQL, aby nawiązać połączenie z połączoną usługą dla bazy danych usługi Azure Cosmos DB.

Używanie zestawu OPENROWSET z kluczem uwierzytelniania

Domyślnie dostęp do konta usługi Azure Cosmos DB jest uwierzytelniany za pomocą klucza uwierzytelniania. Ten klucz można użyć w ramach parametry połączenia w instrukcji w OPENROWSET celu nawiązania połączenia za pośrednictwem połączonej usługi z puli SQL, jak pokazano w poniższym przykładzie:

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

Napiwek

Klucz podstawowy i pomocniczy dla konta usługi Cosmos DB można znaleźć na stronie Klucze w witrynie Azure Portal.

Wyniki tego zapytania mogą wyglądać podobnie do następujących, w tym metadanych i pól zdefiniowanych przez aplikację z elementów w kontenerze usługi Azure Cosmos DB:

_rid _ts productID productName identyfikator _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-...
... ... ... ... ... ...

Dane są pobierane z magazynu analitycznego, a zapytanie nie ma wpływu na magazyn operacyjny.

Używanie zestawu OPENROWSET z poświadczeniami

Zamiast dołączać klucz uwierzytelniania w każdym wywołaniu metody OPENROWSET, można zdefiniować poświadczenia , które hermetyzują informacje uwierzytelniania dla konta usługi Cosmos DB, i użyć poświadczeń w kolejnych zapytaniach. Aby utworzyć poświadczenia, użyj instrukcji CREATE CREDENTIAL , jak pokazano w tym przykładzie:

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

Poświadczenie można użyć go w funkcji podobnej do następującej OPENROWSET :

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

Po raz kolejny wyniki obejmują metadane i pola zdefiniowane przez aplikację z magazynu analitycznego:

_rid _ts productID productName identyfikator _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-...
... ... ... ... ... ...

Określanie schematu

Składnia OPENROWSET zawiera klauzulę WITH , której można użyć do zdefiniowania schematu dla wynikowego zestawu wierszy. Za pomocą tej opcji można określić poszczególne pola i przypisać typy danych, jak pokazano w poniższym przykładzie:

 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

W takim przypadku przy założeniu, że pola w magazynie analitycznym obejmują productID i productName, wynikowy zestaw wierszy będzie przypominać następującą tabelę:

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

Można oczywiście określić nazwy poszczególnych kolumn w klauzuli SELECT (na przykład SELECT productID, productName ...), więc możliwość określenia poszczególnych kolumn może wydawać się ograniczona. Rozważ jednak przypadki, w których źródłowe dokumenty JSON przechowywane w magazynie operacyjnym zawierają wiele poziomów pól, jak pokazano w poniższym przykładzie:

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

Klauzula WITH obsługuje dołączanie jawnych ścieżek JSON, umożliwiając obsługę zagnieżdżonych pól i przypisywanie aliasów do nazw pól, jak pokazano w tym przykładzie:

 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

Wyniki tego zapytania obejmują następujący wiersz dla produktu 126:

ProductNo ProductName Dostawca Dostawca Telefon No
126 Zębatki Contoso 555-123-4567

Tworzenie widoku w bazie danych

Jeśli chcesz często wykonywać zapytania dotyczące tych samych danych lub musisz użyć narzędzi do raportowania i wizualizacji, które opierają się na SELECT instrukcjach, które nie zawierają OPENROWSET funkcji, możesz użyć widoku do abstrakcji danych. Aby utworzyć widok, należy utworzyć nową bazę danych, w której ma zostać zdefiniowana (widoki zdefiniowane przez użytkownika w bazie danych master nie są obsługiwane), jak pokazano w poniższym przykładzie:

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

Napiwek

Podczas tworzenia bazy danych, która będzie uzyskiwać dostęp do danych w usłudze Cosmos DB, najlepiej używać sortowania opartego na protokole UTF-8 w celu zapewnienia zgodności z ciągami w usłudze Cosmos DB.

Po utworzeniu widoku użytkownicy i aplikacje klienckie mogą wysyłać do niego zapytania, podobnie jak w przypadku dowolnego innego widoku LUB tabeli SQL:

SELECT * FROM products;

Zagadnienia dotyczące bezserwerowych pul SQL i usługi Azure Cosmos DB

Podczas planowania używania bezserwerowej puli SQL do wykonywania zapytań dotyczących danych w magazynie analitycznym usługi Azure Cosmos DB należy wziąć pod uwagę następujące najlepsze rozwiązania:

  • Aprowizuj magazyn analityczny usługi Azure Cosmos DB i wszystkie aplikacje klienckie (na przykład Microsoft Power BI) w tym samym regionie co bezserwerowa pula SQL.

    Kontenery usługi Azure Cosmos DB można replikować do wielu regionów. Jeśli masz kontener z wieloma regionami, możesz określić region parametr w parametry połączenia OPENROWSET, aby upewnić się, że zapytania są wysyłane do określonej repliki regionalnej kontenera.

  • Podczas pracy z kolumnami ciągów użyj funkcji OPENROWSET z jawną klauzulą WITH i określ odpowiednią długość danych dla danych ciągu.