Interroger Cosmos DB avec Synapse SQL

5 minutes

Outre l’utilisation d’un pool Spark, vous pouvez interroger un conteneur analytique Azure Cosmos DB à l’aide d’un pool SQL serverless intégré dans Azure Synapse Analytics. Pour ce faire, vous pouvez utiliser la fonction SQL OPENROWSET pour vous connecter au service lié pour votre base de données Azure Cosmos DB.

Utilisation d’OPENROWSET avec une clé d’authentification

Par défaut, l’accès à un compte Azure Cosmos DB est authentifié par une clé d’authentification. Vous pouvez utiliser cette clé dans le cadre d’une chaîne de connexion dans une instruction OPENROWSET pour vous connecter via un service lié à partir d’un pool SQL, comme illustré dans l’exemple suivant :

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

Conseil

Vous trouverez une clé primaire et une clé secondaire pour votre compte Cosmos DB sur sa page Clés dans le portail Azure.

Les résultats de cette requête peuvent ressembler à ce qui suit, notamment les métadonnées et les champs définis par l’application à partir des éléments du conteneur 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-...
...	...	...	...	...	...

Les données sont récupérées à partir du magasin analytique et la requête n’a pas d’impact sur le magasin opérationnel.

Utilisation d’OPENROWSET avec des informations d’identification

Au lieu d’inclure la clé d’authentification dans chaque appel à OPENROWSET, vous pouvez définir des informations d’identification qui encapsulent les informations d’authentification de votre compte Cosmos DB et utiliser les informations d’identification dans les requêtes suivantes. Pour créer des informations d’identification, utilisez l’instruction CREATE CREDENTIAL comme indiqué dans cet exemple :

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

Une fois les informations d’identification en place, vous pouvez les utiliser dans une fonction OPENROWSET comme suit :

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

Une fois de plus, les résultats incluent les métadonnées et les champs définis par l’application à partir du magasin analytique :

_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-...
...	...	...	...	...	...

Spécification d’un schéma

La syntaxe OPENROWSET inclut une clause WITH que vous pouvez utiliser pour définir un schéma pour l’ensemble de lignes résultant. Vous pouvez l’utiliser pour spécifier des champs individuels et affecter des types de données, comme illustré dans l’exemple suivant :

 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

Dans ce cas, en supposant que les champs du magasin analytique incluent productID et productName, l’ensemble de lignes résultant ressemble au tableau suivant :

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

Vous pouvez bien sûr spécifier des noms de colonnes individuels dans la clause SELECT (par exemple, SELECT productID, productName ...) ; cette possibilité de spécifier des colonnes individuelles peut donc sembler limitée. Toutefois, considérez les cas où les documents JSON sources stockés dans le magasin opérationnel incluent plusieurs niveaux de champs, comme indiqué dans l’exemple suivant :

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

La clause WITH prend en charge l’inclusion de chemins JSON explicites, ce qui vous permet de gérer les champs imbriqués et d’attribuer des alias aux noms de champs, comme illustré dans cet exemple :

 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

Les résultats de cette requête incluent la ligne suivante pour le produit 126 :

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

Création d’une vue dans une base de données

Si vous devez interroger les mêmes données fréquemment ou utiliser des outils de création de rapports et de visualisation qui s’appuient sur des instructions SELECT qui n’incluent pas la fonction OPENROWSET, vous pouvez utiliser une vue pour extraire les données. Pour créer une vue, vous devez créer une base de données dans laquelle la définir (les vues définies par l’utilisateur dans la base de données master ne sont pas prises en charge), comme illustré dans l’exemple suivant :

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

Conseil

Lors de la création d’une base de données qui accède aux données dans Cosmos DB, il est préférable d’utiliser un classement UTF-8 pour garantir la compatibilité avec les chaînes dans Cosmos DB.

Une fois la vue créée, les utilisateurs et les applications clientes peuvent l’interroger comme n’importe quelle autre vue ou table SQL :

SELECT * FROM products;

Considérations relatives aux pools SQL serverless et à Azure Cosmos DB

Lorsque vous envisagez d’utiliser un pool SQL serverless pour interroger des données dans un magasin analytique Azure Cosmos DB, tenez compte des meilleures pratiques suivantes :