Partager via

Virtualisation des données avec Azure SQL Managed Instance

S’applique à :Azure SQL Managed Instance

Cet article décrit la fonctionnalité de virtualisation des données d’Azure SQL Managed Instance. La virtualisation des données vous permet d’exécuter des requêtes Transact-SQL (T-SQL) sur des fichiers qui stockent des données dans des formats de données courants dans Azure Data Lake Storage Gen2 ou Stockage Blob Azure. Vous pouvez combiner ces données avec des données relationnelles stockées localement à l’aide de jointures. Avec la virtualisation des données, vous pouvez accéder de manière transparente aux données externes en mode lecture seule, tout en la conservant dans son format d’origine et son emplacement.

Vue d’ensemble

La virtualisation des données offre deux façons d’interroger des fichiers destinés à différents ensembles de scénarios :

  • Syntaxe OPENROWSET : optimisée pour l’interrogation ad hoc de fichiers. Généralement utilisé pour explorer rapidement le contenu et la structure d’un nouvel ensemble de fichiers.
  • Syntaxe CREATE EXTERNAL TABLE : optimisée pour l’interrogation répétitive de fichiers à l’aide d’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. Utilisez des tables externes pour les charges de travail analytiques et les rapports.

Dans les deux cas, créez une source de données externe à l’aide de 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. Il s’agit d’exporter les résultats d’une instruction T-SQL SELECT dans des fichiers Parquet ou CSV dans Azure Storage Blob ou Azure Data Lake Storage (ADLS) Gen 2 et de créer une table externe à partir 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

Stockez des fichiers dans Azure Data Lake Storage Gen2 ou Stockage Blob Azure. Pour interroger des fichiers, fournissez l’emplacement dans un format spécifique et utilisez le préfixe de type d’emplacement qui correspond au type de source externe et de point de terminaison ou de 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

Importante

Le préfixe de type d’emplacement fourni permet de choisir le protocole optimal pour la communication et d’utiliser toutes les fonctionnalités avancées offertes par le 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 que vous souhaitez tester rapidement les fonctionnalités, commencez par interroger des jeux de données publics disponibles dans Azure Open Datasets, comme le jeu de données Bing COVID-19 qui autorise 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 une requête T-SQL pour obtenir d’abord 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 poursuivre l’exploration du jeu de données en ajoutant WHERE, GROUP BYet 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 SQL, cette instance a probablement un accès restreint aux comptes de stockage Azure. Contactez votre expert réseau pour activer l’accès avant de poursuivre l’interrogation.

Lorsque vous êtes familiarisé avec l’interrogation des jeux de données publics, envisagez de passer à des jeux de données non publics qui nécessitent de fournir des informations d’identification, d’accorder des droits d’accès et de configurer des règles de pare-feu. Dans de nombreux scénarios réels, vous utilisez principalement des jeux de données privés.

Accès à des comptes de stockage non publics

Un utilisateur qui se connecte à une instance managée SQL 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 SQL s’authentifie auprès du compte de stockage. Le type d’authentification et les paramètres associés ne sont pas fournis directement avec chaque requête. L'objet de crédentiel à portée de base de données stocké dans la base de données utilisateur regroupe ces informations. La base de données utilise les informations d’identification 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 :

  • Identité managée
  • Signature d’accès partagé (SAP)

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. Vous pouvez utiliser cette identité pour autoriser les demandes d’accès aux données dans les 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 l’instance managée SQL de la même façon que vous accordez des autorisations à tout 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. Ce rôle fournit un accès en lecture à l’identité managée pour les conteneurs 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 ont besoin d’accéder à Lire les fichiers individuels dans ces données doivent également disposer de la permission d’exécution sur tous les dossiers parents jusqu’à la racine (le conteneur). Pour plus d’informations, consultez Définir des listes de contrôle d’accès dans Azure Data Lake Storage Gen2.
  3. Sur la page suivante, sélectionnez Attribuer l'accès àl'identité managée. Sélectionnez + 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. Ensuite, créez les informations d’identification délimitées à la base de données pour l’authentification d’identité managée. 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 fournit une référence simple à un emplacement de fichier sur plusieurs requêtes. Pour interroger des emplacements publics, spécifiez l’emplacement du fichier lorsque vous créez une source de données externe :

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

Pour accéder aux comptes de stockage non publics, spécifiez l’emplacement et référencez l’information d’identification au niveau de la base de données contenant des paramètres d’authentification encapsulés. Le script suivant crée une source de données externe qui pointe vers le chemin du fichier et fait référence à des informations d’identification délimitées à la base de données :

-- Create external data source that points to the file path, and that references a 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.

Lorsque vous utilisez OPENROWSET, fournissez le format du fichier, tel que l’exemple suivant, qui interroge un seul fichier :

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, vous pouvez interroger tous les fichiers avec .parquet une extension dans des dossiers. Par exemple, la requête suivante concerne 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 qu’il soit pratique, les types de données déduits peuvent être plus volumineux que les types de données réels, car il se peut qu’il n’y ait pas suffisamment d’informations dans les fichiers sources pour s’assurer 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, et donc l’instance la déduit comme 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, spécifiez-les à l’aide de la WITH clause 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, spécifiez toujours les colonnes à l’aide de la WITH clause :

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 du paramètre 1 retourne une partie du chemin qui correspond au premier joker.

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 de vues peut réduire le nombre de fichiers et la quantité de données, la requête au-dessus de la vue doit lire et traiter lorsqu’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, de sorte qu’elles se sentent presque identiques à l’interrogation des données relationnelles locales stockées dans les tables utilisateur. Pour créer une table externe, vous devez disposer d’une source de données externe et d’objets de format de fichier externe en place :

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

Après avoir créé la table externe, vous pouvez l’interroger comme n’importe quelle autre table :

SELECT TOP 10 *
FROM tbl_TaxiRides;

Comme OPENROWSET, les tables externes prennent en charge l’interrogation de plusieurs fichiers et dossiers avec des caractères génériques. Toutefois, les tables externes ne prennent pas en charge l’inférence de schéma.

Considérations relatives aux performances

Il n’existe aucune limite difficile au nombre de fichiers ou à la quantité de données que vous pouvez interroger, mais les performances des requêtes dépendent de la quantité de données, du format de données, de la façon dont les données sont organisées et de la complexité des requêtes et des jointures.

Interroger des données partitionnées

Les données sont souvent organisées dans les sous-dossiers, également appelées partitions. Vous pouvez demander à l’instance managée SQL 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 requête à l’aide de la fonction filepath() de métadonnées dans la WHERE clause 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, comme 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 sur une seule colonne pour le chemin d’accès OPENROWSET peuvent être créées à l’aide de la procédure stockée sys.sp_create_openrowset_statistics, en passant la requête SELECT contenant 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 à plusieurs colonnes ne sont pas prises en charge.

Dépanner

Les problèmes liés à l’exécution des requêtes se produisent généralement lorsque l’instance managée SQL ne peut pas accéder à l’emplacement du fichier. Les messages d’erreur associés peuvent signaler des droits d’accès insuffisants, un emplacement qui n’existe pas, un fichier utilisé par un autre processus ou ce répertoire ne peut pas être répertorié. Dans la plupart des cas, ces erreurs indiquent que les stratégies de contrôle du trafic réseau bloquent l’accès aux fichiers ou que l’utilisateur n’a pas de droits d’accès. Vérifiez les éléments suivants :

  • Chemin d’emplacement incorrect ou mal orthographié.
  • Validité de la clé SAP. Elle peut être expirée, contient une faute de frappe ou commence par un point d’interrogation.
  • Autorisations de clé SAP autorisées. Lisez au minimum et répertoriez si des caractères génériques sont utilisés.
  • Trafic entrant bloqué sur le compte de stockage. Vérifiez Gestion des règles du réseau virtuel pour Azure Storage pour plus d’informations et assurez-vous que l’accès à partir du réseau virtuel de l’instance managée SQL est autorisé.
  • Blocage du trafic sortant sur l’instance managée SQL à 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 aux identités managées. Vérifiez que l’identité managée de l’instance dispose de droits d’accès au 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 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 sur 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 T-SQL SELECT dans la table externe créée. Il existe un risque d’exfiltration des données avec ces fonctionnalités. Par conséquent, Azure SQL Managed Instance désactive CETAS par défaut. 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

  • Last updated on