Datenvirtualisierung mit Azure SQL Managed Instance

  • Artikel

Gilt für:Azure SQL Managed Instance

Mit dem Datenvirtualisierungsfeature von Azure SQL Managed Instance können Sie T-SQL-Abfragen (Transact-SQL) für Dateien ausführen, die Daten in gängigen Datenformaten in Azure Data Lake Storage Gen2 oder Azure Blob Storage speichern, und sie über Joins mit lokal gespeicherten relationalen Daten kombinieren. Auf diese Weise können Sie transparent auf externe Daten (schreibgeschützt) zugreifen, während diese in ihrem ursprünglichen Format und an ihrem ursprünglichen Speicherort verbleiben – dies wird auch als Datenvirtualisierung bezeichnet.

Übersicht

Datenvirtualisierung bietet zwei Möglichkeiten zum Abfragen von Dateien für verschiedene Szenarios:

  • OPENROWSET-Syntax: Optimiert für die Ad-hoc-Abfrage von Dateien. Wird in der Regel zum schnellen Untersuchen von Inhalt und Struktur eines neuen Dateisatzes verwendet.
  • CREATE EXTERNAL TABLE-Syntax: Optimiert für die wiederholte Abfrage von Dateien mit identischer Syntax, so als ob die Daten lokal in der Datenbank gespeichert wären. Externe Tabellen erfordern im Vergleich zur OPENROWSET-Syntax mehrere Vorbereitungsschritte, ermöglichen aber eine bessere Kontrolle über den Datenzugriff. Externe Tabellen werden in der Regel für Analyseworkloads und Berichte verwendet.

In beiden Fällen muss wie in diesem Artikel dargestellt eine externe Datenquelle mit der T-SQL-Syntax CREATE EXTERNAL DATA SOURCE erstellt werden.

Außerdem ist die Syntax CREATE EXTERNAL TABLE AS SELECT für Azure SQL Managed Instance verfügbar, um die Ergebnisse einer T-SQL SELECT-Anweisung in die Parquet- oder CSV-Dateien in Azure Blob Storage oder Azure Data Lake Storage (ADLS) Gen 2 zu exportieren und eine externe Tabelle auf diesen Dateien zu erstellen.

Dateiformate

Parquet- und CSV-Dateiformate (durch Trennzeichen getrennte Textdateien) werden direkt unterstützt. Das JSON-Dateiformat wird indirekt unterstützt, indem das CSV-Dateiformat angegeben wird, bei dem Abfragen jedes Dokument als separate Zeile zurückgeben. Sie können Zeilen mithilfe JSON_VALUE und OPENJSON weiter parsen.

Speichertypen

Dateien können in Azure Data Lake Storage Gen2 oder Azure Blob Storage gespeichert werden. Um Dateien abzufragen, müssen Sie den Speicherort in einem bestimmten Format bereitstellen und das Speicherorttyppräfix verwenden, das dem Typ der externen Quelle und des Endpunkts/Protokolls entspricht, wie z. B. in den folgenden Beispielen:

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

Wichtig

Das angegebene Standorttyppräfix wird verwendet, um das optimale Protokoll für die Kommunikation auszuwählen und alle erweiterten Funktionen zu nutzen, die von diesem bestimmten Speichertyp angeboten werden. Das Verwenden des generischen Präfix https:// ist deaktiviert. Verwenden Sie stets endpunktspezifische Präfixe.

Erste Schritte

Wenn Sie sich bisher noch nicht mit der Datenvirtualisierung beschäftigt haben und die Funktionalität schnell testen möchten, beginnen Sie mit der Abfrage öffentlicher Datasets, die in Azure Open Datasets zur Verfügung stehen. Dazu gehört beispielsweise das „Bing COVID-19“-Dataset, auf das anonym zugegriffen werden kann.

Verwenden Sie die folgenden Endpunkte, um die Bing COVID-19-Datasets abzufragen:

  • 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

Führen Sie für einen Schnellstart diese einfache T-SQL-Abfrage aus, um erste Einblicke in den Datensatz zu erhalten. Diese Abfrage verwendet OPENROWSET, um eine Datei abzufragen, die in einem öffentlich verfügbaren Speicherkonto gespeichert ist:

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

Sie können die Datensatzsuche fortsetzen, indem Sie basierend auf dem Ergebnissatz der ersten Abfrage „WHERE“, „GROUP BY“ und andere Klauseln anfügen.

Wenn bei der ersten Abfrage in Ihrer verwalteten Instanz ein Fehler auftritt, hat diese Instanz wahrscheinlich eingeschränkten Zugriff auf Azure Storage-Konten. Wenden Sie sich an Ihre Netzwerkexpert*innen, um den Zugriff zu aktivieren, damit Sie mit der Abfrage fortfahren können.

Wenn Sie mit dem Abfragen öffentlicher Datensätze vertraut sind, sollten Sie mit nicht öffentlichen Datensätzen fortfahren, die Anmeldeinformationen, Zugriffsrechte und das Konfigurieren von Firewallregeln benötigen. In vielen realen Szenarios arbeiten Sie in erster Linie mit privaten Datensätzen.

Zugriff auf nicht öffentliche Speicherkonten

Auf einer verwalteten Instanz angemeldete Benutzer*innen müssen berechtigt sein, auf in einem nicht öffentlichen Speicherkonto gespeicherte Dateien zuzugreifen und sie abzufragen. Die Autorisierungsschritte hängen davon ab, wie die verwaltete Instanz beim Speicher authentifiziert wird. Der Authentifizierungstyp und alle damit verbundenen Parameter werden nicht direkt mit jeder Abfrage bereitgestellt. Sie werden in das datenbankbezogene Anmeldeinformationenobjekt gekapselt, das in der Benutzerdatenbank gespeichert ist. Die Anmeldeinformationen werden von der Datenbank verwendet, um auf das Speicherkonto zuzugreifen, wann immer die Abfrage ausgeführt wird. Azure SQL Managed Instance unterstützt die folgenden Authentifizierungstypen:

Eine verwaltete Identität ist ein Feature von Microsoft Entra ID (ehemals Azure Active Directory), das Azure-Dienste, wie Azure SQL Managed Instance, mit einer in Microsoft Entra ID verwalteten Identität bereitstellt. Diese Identität kann verwendet werden, um Anforderungen für den Datenzugriff in nicht öffentlichen Speicherkonten zu autorisieren. Dienste wie Azure SQL Managed Instance verfügen über eine systemseitig zugewiesene verwaltete Identität und können auch eine oder mehrere benutzerseitig zugewiesene verwaltete Identitäten haben. Sie können entweder systemseitig zugewiesene verwaltete Identitäten oder benutzerseitig zugewiesene verwaltete Identitäten für die Datenvirtualisierung mit Azure SQL Managed Instance verwenden.

Der Azure-Storage-Administrator muss der verwalteten Identität zunächst Berechtigungen für den Zugriff auf die Daten erteilen. Die Erteilung von Berechtigungen für die vom System zugewiesene verwaltete Identität der verwalteten Instanz erfolgt auf dieselbe Weise wie die Erteilung von Berechtigungen für jeden anderen Microsoft Entra-Benutzer. Beispiel:

  1. Wählen Sie im Azure-Portal auf der Seite Zugriffssteuerung (IAM) eines Speicherkontos die Option Rollenzuweisung hinzufügen aus.
  2. Wählen Sie die integrierte Azure RBAC-Rolle Storage-Blobdatenleser aus. Dadurch gewähren Sie Lesezugriff auf die verwaltete Identität für die erforderlichen Azure Blob Storage-Container.
    • Anstatt der verwalteten Identität die Azure RBAC-Rolle Storage-Blobdatenleser zuzuweisen, können Sie auch präzisere Berechtigungen für eine Teilmenge der Daten erteilen. Alle Benutzer, die Lesezugriff auf einzelne Dateien oder Daten in diesem Container benötigen, müssen auch über die EXECUTE-Berechtigung für alle übergeordneten Ordner bis hinauf zum Stammordner (dem Container) verfügen. Hier erfahren Sie mehr zum Festlegen von ACLs in Azure Data Lake Storage Gen2.
  3. Wählen Sie auf der nächsten Seite Zugriff zuweisen zuVerwaltete Identität aus. Klicken Sie auf + Mitglieder auswählen, und wählen Sie in der Dropdownliste Verwaltete Identität die gewünschte verwaltete Identität aus. Weitere Informationen finden Sie unter Hinzufügen oder Entfernen von Azure-Rollenzuweisungen über das Azure-Portal.
  4. Das Erstellen von datenbankbezogenen Anmeldeinformationen für die Authentifizierung der verwalteten Identität ist dann problemlos möglich. Beachten Sie im folgenden Beispiel, dass es sich bei 'Managed Identity' um eine hartcodierte Zeichenfolge handelt.
-- 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'

Externe Datenquelle

Bei einer externen Datenquelle handelt es sich um eine Abstraktion, die eine einfache Referenzierung eines Dateispeicherorts über mehrere Abfragen ermöglicht. Zum Abfragen öffentlicher Speicherorte müssen Sie beim Erstellen einer externen Datenquelle nur den Dateispeicherort angeben:

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

Wenn Sie auf nicht öffentliche Speicherkonten zugreifen, müssen Sie sowohl auf den Speicherort als auch auf datenbankbezogene Anmeldeinformationen mit gekapselten Authentifizierungsparametern verweisen. Das folgende Skript erstellt eine externe Datenquelle, die auf den Dateipfad zeigt und verweist auf eine datenbankbezogene Anmeldeinformationen.

--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];
)

Abfragen von Datenquellen mit OPENROWSET

Die OPENROWSET-Syntax ermöglicht sofortige Ad-hoc-Abfragen, wobei nur eine minimale Anzahl von Datenbankobjekten erstellt werden muss.

OPENROWSET erfordert lediglich die Erstellung der externen Datenquelle (und möglicherweise der Anmeldeinformationen), wohingegen beim Ansatz mit externen Tabellen ein externes Dateiformat und die externe Tabelle selbst benötigt werden.

Der Wert des Parameters DATA_SOURCE wird automatisch dem Parameter BULK vorangestellt, um den vollständigen Pfad zur Datei zu bilden.

Geben Sie bei Verwendung von OPENROWSET das Format der Datei an, wie im folgenden Beispiel, das eine einzelne Datei abfragt:

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

Abfragen mehrerer Dateien und Ordner

Mit dem Befehl OPENROWSET können auch mehrere Dateien oder Ordner abgefragt werden, indem Platzhalter im BULK-Pfad verwendet werden.

Das folgende Beispiel verwendet das offene Dataset mit NYC Yellow Taxi-Fahrtenaufzeichnungen.

Erstellen Sie zunächst die externe Datenquelle:

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

Jetzt können wir alle Dateien mit der Erweiterung .parquet in Ordnern abfragen. Hier werden beispielsweise nur die Dateien abfragt, die einem Namensmuster entsprechen:

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

Bei der Abfrage mehrerer Dateien oder Ordner müssen alle Dateien, auf die mit einem einzelnen OPENROWSET zugegriffen wird, die gleiche Struktur aufweisen (z. B. die gleiche Anzahl von Spalten und Datentypen). Ordner können nicht rekursiv durchlaufen werden.

Schemarückschluss

Mithilfe des automatischen Schemarückschlusses können Sie auch ohne Kenntnis des Dateischemas schnell Abfragen schreiben und Daten untersuchen. Der Schemarückschluss funktioniert nur mit Parquet-Dateien.

Die abgeleiteten Datentypen sind zwar praktisch, können aber größer sein als die tatsächlichen Datentypen, da in den Quelldateien möglicherweise genügend Informationen vorhanden sind, um sicherzustellen, dass der richtige Datentyp verwendet wird. Dies kann die Abfrageleistung beeinträchtigen. Beispielsweise enthalten Parquet-Dateien keine Metadaten zur maximalen Länge von Zeichenspalten, deshalb leitet die Instanz diese als „varchar(8000)“ ab.

Verwenden Sie die gespeicherte Prozedur sp_describe_first_results_set, um die resultierenden Datentypen Ihrer Abfrage zu überprüfen, wie im folgenden Beispiel gezeigt:

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

Sobald Sie die Datentypen kennen, können Sie sie mithilfe der WITH-Klausel angeben, um die Leistung zu verbessern:

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;

Da das Schema von CSV-Dateien nicht automatisch bestimmt werden kann, müssen Spalten immer mit der WITH-Klausel angegeben werden:

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;

Datei-Metadatenfunktionen

Bei der Abfrage mehrerer Dateien oder Ordner können Sie mit den Funktionen filepath() und filename() Dateimetadaten lesen und einen Teil oder den vollständigen Pfad und Namen der Datei abrufen, aus der die Zeile im Resultset stammt:

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

Beim Aufruf ohne Parameter gibt die Funktion filepath() den Dateipfad zurück, aus dem die Zeile stammt. Bei Verwendung von DATA_SOURCE in OPENROWSET wird der Pfad relativ zu DATA_SOURCE zurückgegeben, andernfalls der vollständige Dateipfad.

Beim Aufruf mit Parameter wird ein Teil eines Pfads zurückgegeben, der dem Platzhalterzeichen an der im Parameter angegebenen Position entspricht. Der Parameterwert 1 würde z. B. den Teil eines Pfads zurückgeben, der dem ersten Platzhalterzeichen entspricht.

Die Funktion filepath() kann auch zum Filtern und Aggregieren von Zeilen verwendet werden:

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;

Erstellen einer Ansicht oberhalb von OPENROWSET

Sie können Ansichten erstellen und verwenden, um OPENROWSET-Abfragen zu umschließen, sodass die zugrunde liegende Abfrage problemlos wiederverwendet werden kann:

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

Es ist ebenfalls praktisch, einer Ansicht mithilfe der Funktion filepath() Spalten mit Daten zum Dateispeicherort hinzuzufügen, um eine einfachere und leistungsfähigere Filterung zu ermöglichen. Durch die Verwendung von Sichten kann die Anzahl der Dateien und die von der Abfrage oberhalb der Ansicht zu lesende und zu verarbeitende Datenmenge verringert werden, wenn nach einer dieser Spalten gefiltert wird:

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

Darüber hinaus können Berichts- und Analysetools wie Power BI mithilfe von Sichten die Ergebnisse von OPENROWSET nutzen.

Externe Tabellen

Externe Tabellen kapseln den Zugriff auf Dateien, wodurch die Abfrage fast identisch mit der Abfrage lokaler relationaler Daten ist, die in Benutzertabellen gespeichert sind. Zum Erstellen einer externen Tabelle müssen die externe Datenquelle und die externen Dateiformatobjekte vorhanden sein:

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

Nachdem die externe Tabelle erstellt wurde, können Sie sie wie jede andere Tabelle abfragen:

SELECT TOP 10 *
FROM tbl_TaxiRides;

Genau wie OPENROWSET ermöglichen externe Tabellen die Abfrage mehrerer Dateien und Ordner mithilfe von Platzhaltern. Schemainference wird bei externen Tabellen nicht unterstützt.

Überlegungen zur Leistung

Es gibt keine feste Grenze für die Anzahl der Dateien oder die Menge an Daten, die abgefragt werden kann. Die Abfrageleistung hängt jedoch von der Datenmenge, dem Datenformat, der Art, wie die Daten organisiert sind, und der Komplexität der Abfragen und Joins ab.

Abfragen von partitionierten Daten

Daten werden häufig in Unterordnern organisiert, die auch Partitionen genannt werden. Sie können die verwaltete Instanz anweisen, nur bestimmte Ordner und Dateien abzufragen. Dies reduziert die Anzahl der Dateien und die Datenmenge, die die Abfrage lesen und verarbeiten muss, was zu einer besseren Leistung führt. Diese Art der Abfrageoptimierung wird als Partitionsbereinigung oder Partitionsentfernung bezeichnet. Sie können Partitionen aus der Abfrageausführung entfernen, indem Sie die Metadatenfunktion filepath() in der „WHERE“-Klausel der Abfrage verwenden.

Die folgende Beispielabfrage liest nur die Datendateien von „NYC Yellow Taxi“ für die letzten drei Monate des Jahres 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;

Wenn Ihre gespeicherten Daten nicht partitioniert sind, sollten Sie sie für eine verbesserte Abfrageleistung partitionieren.

Wenn Sie externe Tabellen verwenden, werden die Funktionen filepath() und filename() unterstützt, aber nicht in der WHERE-Klausel. Sie können weiterhin nach filename oder filepath filtern, wenn Sie diese in berechneten Spalten verwenden. Dies wird im folgenden Beispiel veranschaulicht:

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

Wenn Ihre gespeicherten Daten nicht partitioniert sind, sollten Sie sie für eine verbesserte Abfrageleistung partitionieren.

Statistik

Das Erfassen von Statistiken zu externen Daten ist eine der wichtigsten Maßnahmen für die Abfrageoptimierung. Je mehr Informationen der Instanz über Ihre Daten vorliegen, desto schneller kann sie Abfragen ausführen. Der Abfrageoptimierer der SQL-Engine arbeitet kostenorientiert. Die Kosten der verschiedenen Abfragepläne werden verglichen, und dann wird der Plan mit den geringsten Kosten gewählt. In den meisten Fällen wird der Plan gewählt, der am schnellsten ausgeführt wird.

Automatische Erstellung von Statistiken

Azure SQL Managed Instance analysiert eingehende Benutzerabfragen auf fehlende Statistiken. Wenn Statistiken fehlen, erstellt der Abfrageoptimierer automatisch Statistiken für einzelne Spalten im Abfrageprädikat oder der Verknüpfungsbedingung, um Kardinalitätsschätzungen für den Abfrageplan zu verbessern. Die automatische Erstellung von Statistiken erfolgt synchron, sodass möglicherweise eine geringfügige Verlangsamung bei der Abfrageleistung auftreten kann, sofern in Ihren Spalten keine Statistiken enthalten sind. Die Zeit zum Erstellen von Statistiken für eine einzelne Spalte hängt von der Größe der Zieldateien ab.

Manuelle OPENROWSET-Statistiken

Statistiken zu einzelnen Spalten für den Pfad OPENROWSET können mit der gespeicherten Prozedur sys.sp_create_openrowset_statistics erstellt werden, indem die SELECT-Abfrage mit einer einzelnen Spalte als Parameter übergeben wird:

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

Standardmäßig verwendet die Instanz 100 % der im Dataset bereitgestellten Daten, um Statistiken zu erstellen. Optional können Sie die Stichprobengröße mithilfe der TABLESAMPLE-Optionen als Prozentsatz angeben. Um Einzelspaltenstatistiken für mehrere Spalten zu erstellen, führen Sie sys.sp_create_openrowset_statistics für jede der Spalten aus. Für den Pfad OPENROWSET können keine Mehrspaltenstatistiken erstellt werden.

Um vorhandene Statistiken zu aktualisieren, löschen Sie diese zunächst mit der gespeicherten Prozedur sys.sp_drop_openrowset_statistics, und erstellen Sie sie dann mithilfe von sys.sp_create_openrowset_statistics neu:

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

Manuelle Statistiken zu externen Tabellen

Die Syntax für die Erstellung von Statistiken zu externen Tabellen ähnelt der Syntax für reguläre Benutzertabellen. Zum Erstellen von Statistiken zu einer Spalte geben Sie einen Namen für das Statistikobjekt und den Namen der Spalte an:

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

Die WITH-Optionen müssen angegeben werden, und für die Stichprobengröße sind die Optionen FULLSCAN und SAMPLE n Prozent zulässig.

  • Um Einzelspaltenstatistiken für mehrere Spalten zu erstellen, führen Sie CREATE STATISTICS für jede der Spalten aus.
  • Mehrspaltenstatistiken werden nicht unterstützt.

Problembehandlung

Probleme bei der Abfrageausführung werden in der Regel dadurch verursacht, dass die verwaltete Instanz nicht auf den Dateispeicherort zugreifen kann. Die zugehörigen Fehlermeldungen verweisen möglicherweise auf unzureichende Zugriffsrechte, einen nicht vorhandenen Speicherort oder Dateipfad, eine Datei, die von einem anderen Prozess verwendet wird, oder ein Verzeichnis, das nicht aufgelistet werden kann. In den meisten Fällen bedeutet dies, dass der Zugriff auf Dateien durch Richtlinien zur Steuerung des Netzwerkdatenverkehrs oder aufgrund fehlender Zugriffsrechte blockiert wird. Folgendes sollte überprüft werden:

  • Falscher oder falsch eingegebener Pfad zum Speicherort.
  • Gültigkeit des SAS-Schlüssels: Er könnte abgelaufen sein, einen Rechtschreibfehler enthalten oder mit einem Fragezeichen beginnen.
  • Zugelassene SAS-Schlüsselberechtigungen: Mindestens Lesen und Auflisten, wenn Platzhalter verwendet werden.
  • Blockierter eingehender Datenverkehr für das Speicherkonto. Prüfen Sie die Informationen unter Verwalten von VNet-Regeln für Azure Storage, und stellen Sie sicher, dass der Zugriff über das VNet der verwalteten Instanz erlaubt ist.
  • Ausgehender Datenverkehr auf der verwalteten Instanz wird durch die Speicherendpunktrichtlinie blockiert. Lassen Sie ausgehenden Datenverkehr an das Speicherkonto zu.
  • Zugriffsrechte für verwaltete Identitäten: Stellen Sie sicher, dass der verwalteten Identität der Instanz Zugriffsrechte auf das Speicherkonto gewährt werden.
  • Der Kompatibilitätsgrad der Datenbank muss mindestens 130 betragen, damit Datenvirtualisierungsabfragen funktionieren.

CREATE EXTERNAL TABLE AS SELECT (CETAS)

CREATE EXTERNAL TABLE AS SELECT (CETAS) ermöglicht Ihnen, Daten aus Ihrer verwalteten SQL-Datenbank-Instanz in ein externes Speicherkonto zu exportieren. Sie können CETAS verwenden, um eine externe Tabelle auf der Grundlage von Parquet- oder CSV-Dateien in Azure Blob Storage oder Azure Data Lake Storage (ADLS) Gen2 zu erstellen. CETAS kann auch parallel die Ergebnisse einer T-SQL-SELECT-Anweisung in die erstellte externe Tabelle exportieren. Bei diesen Funktionen besteht ein potenzielles Risiko der Datenexfiltration, sodass CETAS bei Azure SQL Managed Instance standardmäßig deaktiviert ist. Informationen zur Aktivierung finden Sie unter CREATE EXTERNAL TABLE AS SELECT (CETAS).

Einschränkungen

Bekannte Probleme

  • Wenn Parametrisierung für Always Encrypted in SQL Server Management Studio (SSMS) aktiviert ist, schlagen die Datenvirtualisierungsabfragen mit der Fehlermeldung Incorrect syntax near 'PUSHDOWN' fehl.

Zugehöriger Inhalt