Virtualisation des données avec Azure SQL Managed Instance

  • Article

S’applique à :Azure SQL Managed Instance

La virtualisation des données avec Azure SQL Managed Instance vous permet d’exécuter des requêtes Transact-SQL (T-SQL) sur des données de fichiers stockés dans des formats communs dans Azure Data Lake Storage Gen2 ou dans Stockage Blob Azure, et de les combiner avec des données relationnelles stockées localement à l’aide de jointures. De cette façon, vous pouvez accéder de façon transparente aux données externes (en mode lecture seule) tout en les conservant dans le format et l’emplacement d’origine. C’est ce que l’on appelle également la virtualisation des données.

Vue d’ensemble

La virtualisation des données fournit deux façons d’interroger des fichiers pour différents ensembles de scénarios :

  • Syntaxe OPENROWSET : optimisée pour l’interrogation ad hoc des fichiers. Généralement utilisé pour explorer rapidement le contenu et la structure d’un nouvel ensemble de fichiers.
  • Syntaxe CREATE EXTERNAL TABLE : optimisées pour l’interrogation répétitive des fichiers avec une syntaxe identique, comme si les données étaient stockées localement dans la base de données. Les tables externes nécessitent plusieurs étapes de préparation par rapport à la syntaxe OPENROWSET, mais elles offrent plus de contrôle sur l’accès aux données. Les tables externes sont généralement utilisées pour la génération de rapports et les charges de travail analytiques.

Dans les deux cas, une source de données externe doit être créée en utilisant la syntaxe T-SQL CREATE EXTERNAL DATA SOURCE, comme illustré dans cet article.

La syntaxe CREATE EXTERNAL TABLE AS SELECT est également disponible pour Azure SQL Managed Instance, pour exporter les résultats d’une instruction T-SQL SELECT vers des fichiers Parquet ou CSV dans le Stockage Blob Azure ou Azure Data Lake Storage (ADLS) Gen 2, ou pour créer une table externe en plus de ces fichiers.

Formats de fichiers

Les formats de fichiers Parquet et texte délimité (CSV) sont directement pris en charge. Le format de fichier JSON est pris en charge indirectement en spécifiant le format de fichier CSV dans lequel les requêtes retournent chaque document sous la forme d’une ligne distincte. Vous pouvez analyser les lignes à l’aide de JSON_VALUE et OPENJSON.

Types de stockage

Les fichiers peuvent être stockés dans Azure Data Lake Storage Gen2 ou Stockage Blob Azure. Pour interroger des fichiers, vous devez fournir l’emplacement dans un format spécifique et utiliser le préfixe de type d’emplacement correspondant au type de source externe et de point de terminaison/protocole, comme les exemples suivants :

--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet

--Data Lake endpoint
adls://<container>@<storage_account>.dfs.core.windows.net/<path>/<file_name>.parquet

Important

Le préfixe de type d’emplacement fourni est utilisé pour choisir le protocole optimal pour la communication et tirer parti de toutes les fonctionnalités avancées offertes par ce type de stockage particulier. L’utilisation du préfixe générique https:// est désactivée. Utilisez toujours des préfixes spécifiques au point de terminaison.

Bien démarrer

Si vous débutez avec la virtualisation des données et souhaitez tester rapidement les fonctionnalités, commencez par interroger les jeux de données publics dans Azure Open Datasets, comme le jeu de données Bing COVID-19 autorisant l’accès anonyme.

Utilisez les points de terminaison suivants pour interroger les jeux de données Bing COVID-19 :

  • Parquet : abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet
  • CSV : abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv

Pour un démarrage rapide, exécutez cette requête T-SQL simple pour d’abord obtenir des insights sur le jeu de données. Cette requête utilise OPENROWSET pour interroger un fichier stocké dans un compte de stockage disponible publiquement :

--Quick query on a file stored in a publicly available storage account:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet',
 FORMAT = 'parquet'
) AS filerows

Vous pouvez continuer l’exploration du jeu de données en ajoutant WHERE, GROUP BY et d’autres clauses en fonction du jeu de résultats de la première requête.

Si la première requête échoue sur votre instance managée, cette instance a probablement un accès restreint aux comptes de stockage Azure, et vous devez contacter votre expert réseau pour activer l’accès avant de pouvoir procéder à l’interrogation.

Une fois que vous êtes familiarisé avec l’interrogation des jeux de données publics, passez à des jeux de données non publics qui nécessitent des informations d’identification, en accordant des droits d’accès et en configurant des règles de pare-feu. Dans de nombreux scénarios réels, vous allez travailler principalement avec des jeux de données privés.

Accès à des comptes de stockage non publics

Un utilisateur connecté à une instance managée doit être autorisé à accéder aux fichiers stockés dans un compte de stockage non public et à les interroger. Les étapes d’autorisation dépendent de la façon dont l’instance managée s’authentifie auprès du stockage. Le type d’authentification et tous les paramètres associés ne sont pas fournis directement avec chaque requête. Ils sont encapsulés dans l’objet d’informations d’identification pour la base de données stocké dans la base de données utilisateur. Les informations d’identification sont utilisées par la base de données pour accéder au compte de stockage chaque fois que la requête s’exécute. Azure SQL Managed Instance prend en charge les types d’authentification suivants :

Une identité managée est une fonctionnalité de Microsoft Entra ID (anciennement Azure Active Directory) qui fournit des services Azure, comme Azure SQL Managed Instance, avec une identité managée dans Microsoft Entra ID. Cette identité peut être utilisée pour autoriser les demandes d’accès aux données dans des comptes de stockage non publics. Des services comme Azure SQL Managed Instance ont une identité managée affectée par le système et peuvent également avoir une ou plusieurs identités managées affectées par l’utilisateur. Vous pouvez utiliser des identités managées affectées par le système ou par l’utilisateur pour la virtualisation des données avec Azure SQL Managed Instance.

L’administrateur du stockage Azure doit d’abord accorder des autorisations à l’identité managée pour accéder aux données. Accordez des autorisations à l’identité managée affectée par le système de la Managed Instance de la même façon que pour un autre utilisateur Microsoft Entra. Par exemple :

  1. Dans le portail Azure, dans la page Contrôle d’accès (IAM) d’un compte de stockage, sélectionnez Ajouter une attribution de rôle.
  2. Choisissez le rôle RBAC Azure intégré Lecteur des données blob du stockage. Elle permet d’accéder en lecture à l’identité managée pour les conteneurs de stockage Blob Azure nécessaires.
    • Au lieu d’attribuer à l’identité managée le rôle RBAC Azure Lecteur des données blob du stockage, vous pouvez également octroyer des autorisations plus précises sur un sous-ensemble de fichiers. Tous les utilisateurs qui doivent accéder en Lecture à des fichiers individuels dans ce conteneur doivent également avoir l’autorisation Exécuter sur tous les dossiers parents jusqu’à la racine (le conteneur). En savoir plus sur la définition des listes de contrôle d’accès dans Azure Data Lake Storage Gen2.
  3. Dans la page suivante, sélectionnez Attribuer l’accès àIdentité managée. + Sélectionner des membres, puis sous la liste déroulante Identité managée, sélectionnez l’identité managée souhaitée. Pour plus d’informations, consultez Attribuer des rôles Azure en utilisant le portail Azure.
  4. La création d’informations d’identification délimitées à la base de données pour l’authentification de l’identité managée est simple. Notez dans l’exemple suivant que 'Managed Identity' est une chaîne codée en dur.
-- Optional: Create MASTER KEY if it doesn't exist in the database:
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>'
GO
CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'Managed Identity'

Source de données externe

Une source de données externe est une abstraction qui permet un référencement facile d’un emplacement de fichier sur plusieurs requêtes. Pour interroger des emplacements publics, tout ce que vous devez spécifier lors de la création d’une source de données externe est l’emplacement du fichier :

CREATE EXTERNAL DATA SOURCE MyExternalDataSource
WITH (
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
)

Lorsque vous accédez à des comptes de stockage non publics, vous devez, outre l’emplacement, référencer un identifiant étendu à une base de données avec des paramètres d’authentification encapsulés. Le script suivant crée une source de données externe pointant vers le chemin du fichier et référençant un identifiant étendu à une base de données.

--Create external data source pointing to the file path, and referencing database-scoped credential:
CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource
WITH (
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
        CREDENTIAL = [MyCredential];
)

Interroger des sources de données avec OPENROWSET

La syntaxe OPENROWSET permet les requêtes ad hoc instantanées tout en créant uniquement le nombre minimal d’objets de base de données nécessaires.

OPENROWSET nécessite uniquement la création de la source de données externe (voire des informations d’identification), contrairement à l’approche de table externe qui nécessite un format de fichier externe et la table externe elle-même.

La valeur du paramètre DATA_SOURCE est automatiquement ajoutée au début du paramètre BULK pour former le chemin complet du fichier.

Quand vous utilisez OPENROWSET, indiquez le format du fichier, comme dans l’exemple suivant, qui interroge un fichier unique :

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'bing_covid-19_data.parquet',
 DATA_SOURCE = 'MyExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

Interroger plusieurs fichiers et dossiers

La commande OPENROWSET permet également d’interroger plusieurs fichiers ou dossiers en utilisant des caractères génériques dans le chemin BULK.

L’exemple suivant utilise le jeu de données ouvert NYC Yellow Taxi.

Dans un premier temps, créez la source de données externe :

--Create the data source first:
CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

À présent, nous pouvons interroger tous les fichiers présentant l’extension .parquet dans les dossiers. Par exemple, pour ce cas, nous allons interroger uniquement ces fichiers correspondant à un modèle de nom :

--Query all files with .parquet extension in folders matching name pattern:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

Lors de l’interrogation de plusieurs fichiers ou dossiers, tous les fichiers accessibles avec la fonction OPENROWSET unique doivent avoir la même structure (par exemple le même nombre de colonnes et les mêmes types de données). Les dossiers ne peuvent pas être parcourus de manière récursive.

Inférence de schéma

L’inférence de schéma automatique vous permet d’écrire rapidement des requêtes et d’explorer des données quand vous ignorez les schémas de fichier. L’inférence de schéma fonctionne uniquement avec les fichiers Parquet.

Bien que pratiques, les types de données déduits peuvent être plus volumineux que les types de données réels, car il peut y avoir suffisamment d’informations dans les fichiers sources pour garantir que le type de données approprié est utilisé. Cela peut entraîner une baisse des performances des requêtes. Par exemple, les fichiers Parquet ne contiennent pas de métadonnées sur la longueur maximale des colonnes de caractères, l’instance déduit donc qu’il s’agit d’une valeur varchar(8000).

Utilisez la procédure stockée sp_describe_first_results_set pour vérifier les types de données résultants de votre requête, comme dans l’exemple suivant :

EXEC sp_describe_first_result_set N'
 SELECT
 vendorID, tpepPickupDateTime, passengerCount
 FROM
 OPENROWSET(
  BULK ''yellow/*/*/*.parquet'',
  DATA_SOURCE = ''NYCTaxiExternalDataSource'',
  FORMAT=''parquet''
 ) AS nyc';

Une fois que vous connaissez les types de données, vous pouvez les spécifier à l’aide de la clause WITH pour améliorer les performances :

SELECT TOP 100
 vendorID, tpepPickupDateTime, passengerCount
FROM
OPENROWSET(
 BULK 'yellow/*/*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT='PARQUET'
 )
WITH (
vendorID varchar(4), -- we're using length of 4 instead of the inferred 8000
tpepPickupDateTime datetime2,
passengerCount int
) AS nyc;

Étant donné que le schéma des fichiers CSV ne peut pas être déterminé automatiquement, les colonnes doivent toujours être spécifiées avec la clause WITH :

SELECT TOP 10 id, updated, confirmed, confirmed_change
FROM OPENROWSET(
 BULK 'bing_covid-19_data.csv',
 DATA_SOURCE = 'MyExternalDataSource',
 FORMAT = 'CSV',
 FIRSTROW = 2
)
WITH (
 id int,
 updated date,
 confirmed int,
 confirmed_change int
) AS filerows;

Fonctions de métadonnées de fichier

Lors de l’interrogation de plusieurs fichiers ou dossiers, vous pouvez utiliser les fonctions filepath() et filename() pour lire les métadonnées de fichier et obtenir une partie du chemin d’accès ou le chemin complet, ainsi que le nom du fichier d’où provient la ligne du jeu de résultats :

--Query all files and project file path and file name information for each row:
SELECT TOP 10 filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder],
filerows.filename() as [File_name], filerows.filepath() as [Full_Path], *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet') AS filerows;
--List all paths:
SELECT DISTINCT filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet') AS filerows;

En cas d’appel sans paramètre, la fonction filepath() retourne le chemin du fichier dont provient la ligne. Quand DATA_SOURCE est utilisé dans OPENROWSET, il retourne le chemin relatif à DATA_SOURCE. Dans le cas contraire, il retourne le chemin de fichier complet.

En cas d’appel avec paramètre, elle retourne une partie du chemin qui correspond au caractère générique occupant la position spécifiée dans le paramètre. Par exemple, la valeur de paramètre 1 retourne une partie du chemin qui correspond au premier caractère générique.

La fonction filepath() peut également être utilisée pour le filtrage et l’agrégation de lignes :

SELECT
 r.filepath() AS filepath
 ,r.filepath(1) AS [year]
 ,r.filepath(2) AS [month]
 ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
DATA_SOURCE = 'NYCTaxiExternalDataSource',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('2017')
 AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
 r.filepath()
 ,r.filepath(1)
 ,r.filepath(2)
ORDER BY
 filepath;

Créer une vue sur OPENROWSET

Vous pouvez créer et utiliser les vues pour inclure les requêtes OPENROWSET dans un wrapper afin de pouvoir réutiliser facilement la requête sous-jacente :

CREATE VIEW TaxiRides AS
SELECT *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows

Il est également pratique d’ajouter des colonnes avec les données d’emplacement de fichier à une vue à l’aide de la fonction filepath() pour un filtrage plus facile et plus performant. L’utilisation des vues permet de réduire le nombre de fichiers et la quantité de données que la requête doit lire et traiter sur la vue quand elle est filtrée par l’une de ces colonnes :

CREATE VIEW TaxiRides AS
SELECT *
 , filerows.filepath(1) AS [year]
 , filerows.filepath(2) AS [month]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows

Les vues permettent également d’utiliser des outils de création de rapports et analytiques comme Power BI pour consommer les résultats de OPENROWSET.

Tables externes

Les tables externes encapsulent l’accès aux fichiers, ce qui rend l’expérience d’interrogation presque identique à l’interrogation des données relationnelles locales stockées dans les tables utilisateur. Pour créer une table externe, la source de données externe et les objets de format de fichier externe doivent exister :

--Create external file format
CREATE EXTERNAL FILE FORMAT DemoFileFormat
WITH (
 FORMAT_TYPE=PARQUET
)
GO

--Create external table:
CREATE EXTERNAL TABLE tbl_TaxiRides(
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2,
 tpepPickupDateTime DATETIME2,
 tpepDropoffDateTime DATETIME2,
 passengerCount INT,
 tripDistance FLOAT,
 puLocationId VARCHAR(8000),
 doLocationId VARCHAR(8000),
 startLon FLOAT,
 startLat FLOAT,
 endLon FLOAT,
 endLat FLOAT,
 rateCodeId SMALLINT,
 storeAndFwdFlag VARCHAR(8000),
 paymentType VARCHAR(8000),
 fareAmount FLOAT,
 extra FLOAT,
 mtaTax FLOAT,
 improvementSurcharge VARCHAR(8000),
 tipAmount FLOAT,
 tollsAmount FLOAT,
 totalAmount FLOAT
)
WITH (
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = NYCTaxiExternalDataSource,
 FILE_FORMAT = DemoFileFormat
);
GO

Une fois la table externe créée, vous pouvez l’interroger comme toute autre table :

SELECT TOP 10 *
FROM tbl_TaxiRides;

Tout comme OPENROWSET, les tables externes autorisent l’interrogation de plusieurs fichiers et dossiers à l’aide de caractères génériques. L’inférence de schéma n’est pas prise en charge avec les tables externes.

Considérations relatives aux performances

Il n’existe pas de limite inconditionnelle en termes de nombre de fichiers ou de données pouvant être interrogés. Toutefois, les performances des requêtes dépendent de la quantité et du format des données, de la façon dont les données sont organisées, ainsi que de la complexité des requêtes et des jointures.

Interroger des données partitionnées

Les données sont souvent organisées dans des sous-dossiers, aussi appelés partitions. Vous pouvez demander à l’instance managée d’interroger uniquement des dossiers et des fichiers particuliers. En procédant ainsi, vous réduisez le nombre de fichiers et la quantité de données que la requête doit lire et traiter, et augmentez les performances. Ce type d’optimisation de requête est connu sous le nom de nettoyage de partition ou d’élimination de partition. Vous pouvez éliminer les partitions de l’exécution de la requête à l’aide de la fonction de métadonnées filepath() dans la clause WHERE de la requête.

L’exemple de requête suivant lit les fichiers de données NYC Yellow Taxi uniquement pour les trois derniers mois de 2017 :

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'yellow/puYear=*/puMonth=*/*.parquet',
        DATA_SOURCE = 'NYCTaxiExternalDataSource',
        FORMAT = 'parquet'
    )
WITH (
    vendorID INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

Si vos données stockées ne sont pas partitionnées, envisagez de les partitionner pour améliorer les performances des requêtes.

Si vous utilisez des tables externes, les fonctions filepath() et filename() sont prises en charge, mais pas dans la clause WHERE. Vous pouvez toujours filtrer par filename ou filepath si vous les utilisez dans des colonnes calculées. Cela est illustré par l’exemple suivant :

CREATE EXTERNAL TABLE tbl_TaxiRides (
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2,
 tpepPickupDateTime DATETIME2,
 tpepDropoffDateTime DATETIME2,
 passengerCount INT,
 tripDistance FLOAT,
 puLocationId VARCHAR(8000),
 doLocationId VARCHAR(8000),
 startLon FLOAT,
 startLat FLOAT,
 endLon FLOAT,
 endLat FLOAT,
 rateCodeId SMALLINT,
 storeAndFwdFlag VARCHAR(8000),
 paymentType VARCHAR(8000),
 fareAmount FLOAT,
 extra FLOAT,
 mtaTax FLOAT,
 improvementSurcharge VARCHAR(8000),
 tipAmount FLOAT,
 tollsAmount FLOAT,
 totalAmount FLOAT,
 [Year]  AS CAST(filepath(1) AS INT), --use filepath() for partitioning
 [Month]  AS CAST(filepath(2) AS INT) --use filepath() for partitioning
)
WITH (
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = NYCTaxiExternalDataSource,
 FILE_FORMAT = DemoFileFormat
);
GO

SELECT *
      FROM tbl_TaxiRides
WHERE
      [year]=2017            
      AND [month] in (10,11,12);

Si vos données stockées ne sont pas partitionnées, envisagez de les partitionner pour améliorer les performances des requêtes.

Statistiques

La collecte de statistiques sur vos données externes est l’une des actions les plus importantes pour optimiser vos requêtes. Plus l’instance a d’informations sur vos données, plus elle peut exécuter de requêtes rapidement. L’optimiseur de requête du moteur SQL est un optimiseur basé sur les coûts. Il compare le coût de différents plans de requête, puis choisit le plan avec le coût le plus bas. Dans la plupart des cas, il choisit le plan qui s’exécute le plus rapidement.

Création automatique de statistiques

Azure SQL Managed Instance analyse les requêtes utilisateur entrantes pour repérer les statistiques manquantes. Si des statistiques manquent, l’optimiseur de requête crée automatiquement des statistiques sur des colonnes individuelles dans le prédicat de requête ou la condition de jointure afin d’améliorer les estimations de cardinalité pour le plan de requête. La création automatique de statistiques étant effectuée de façon synchrone, les performances des requêtes risquent de subir une légère détérioration si des statistiques manquent dans vos colonnes. La durée de création de statistiques pour une seule colonne dépend de la taille des fichiers ciblés.

Statistiques manuelles OPENROWSET

Les statistiques à une seule colonne pour le chemin OPENROWSET peuvent être créées à l’aide de la procédure stockée sys.sp_create_openrowset_statistics, en passant la requête SELECT avec une seule colonne en tant que paramètre :

EXEC sys.sp_create_openrowset_statistics N'
SELECT pickup_datetime
FROM OPENROWSET(
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'',
 FORMAT = ''parquet'') AS filerows
';

Par défaut, l’instance utilise 100 % des données fournies dans le jeu de données pour créer des statistiques. Vous pouvez également spécifier la taille de l’échantillon en pourcentage à l’aide des options TABLESAMPLE. Pour créer des statistiques de colonne unique pour plusieurs colonnes, exécutez sys.sp_create_openrowset_statistics pour chacune des colonnes. Vous ne pouvez pas créer de statistiques sur plusieurs colonnes pour le chemin OPENROWSET.

Pour mettre à jour les statistiques existantes, supprimez-les d’abord à l’aide de la procédure stockée sys.sp_drop_openrowset_statistics, puis recréez-les à l’aide de sys.sp_create_openrowset_statistics :

EXEC sys.sp_drop_openrowset_statistics N'
SELECT pickup_datetime
FROM OPENROWSET(
 BULK ''abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/*.parquet'',
 FORMAT = ''parquet'') AS filerows
';

Statistiques manuelles des tables externes

La syntaxe pour créer des statistiques sur des tables externes est semblable à celle utilisée pour les tables utilisateur ordinaires. Pour créer des statistiques sur une colonne, indiquez le nom de l’objet de statistiques, ainsi que celui de la colonne :

CREATE STATISTICS sVendor
ON tbl_TaxiRides (vendorID)
WITH FULLSCAN, NORECOMPUTE;

Les options WITH sont obligatoires et, pour la taille de l’échantillon, les options autorisées sont FULLSCAN et SAMPLE n pourcent.

  • Pour créer des statistiques de colonne unique pour plusieurs colonnes, exécutez CREATE STATISTICS pour chacune des colonnes.
  • Les statistiques sur plusieurs colonnes ne sont pas prises en charge.

Dépanner

Les problèmes liés à l’exécution de la requête sont généralement dus au fait que l’instance gérée ne peut pas accéder à l’emplacement des fichiers. Les messages d’erreur associés peuvent signaler des droits d’accès insuffisants, un emplacement ou un chemin de fichier inexistants ou un fichier utilisé par un autre processus, ou indiquer que l’annuaire ne peut pas être répertorié. Dans la plupart des cas, cela indique que l’accès aux fichiers est bloqué par les stratégies de contrôle du trafic réseau ou en raison d’un manque de droits d’accès. Voici les éléments devant être vérifiés :

  • Chemin d’emplacement incorrect ou mal orthographié.
  • Validité de la clé SAS : elle peut avoir expiré, contenir une faute de frappe ou commencer par un point d’interrogation.
  • Autorisations de clé SAS accordées : Lecture au minimum et Liste si des caractères génériques sont utilisés.
  • Trafic entrant bloqué sur le compte de stockage. Pour plus d’informations, consultez Gestion des règles de réseau virtuel pour Stockage Azure et vérifiez que l’accès à partir du réseau virtuel de l’instance gérée est autorisé.
  • Trafic sortant bloqué sur l’instance gérée à l’aide de la stratégie de point de terminaison de stockage. Autorisez le trafic sortant vers le compte de stockage.
  • Droits d’accès à l’identité managée : assurez-vous que l’identité managée de l’instance dispose des droits d’accès accordés sur le compte de stockage.
  • Le niveau de compatibilité de la base de données doit être égal ou supérieur à 130 pour que les requêtes de virtualisation des données fonctionnent.

CREATE EXTERNAL TABLE AS SELECT (CETAS)

CREATE EXTERNAL TABLE AS SELECT (CETAS) vous permet d’exporter des données de votre instance managée SQL vers un compte de stockage externe. Vous pouvez utiliser CETAS pour créer une table externe par-dessus des fichiers Parquet ou CSV dans le Stockage Blob Azure ou Azure Data Lake Storage (ADLS) Gen2. CETAS peut également exporter, en parallèle, les résultats d’une instruction SELECT T-SQL dans la table externe créée. Comme il y a un risque potentiel d’exfiltration des données avec ces fonctionnalités. CETAS est désactivé par défaut pour Azure SQL Managed Instance. Pour l’activer, consultez CREATE EXTERNAL TABLE AS SELECT (CETAS).

Limites

Problèmes connus

  • Quand le paramétrage pour Always Encrypted est activé dans SQL Server Management Studio (SSMS), les requêtes de virtualisation des données échouent avec le message d’erreur Incorrect syntax near 'PUSHDOWN'.

Contenu connexe