Freigeben über

Datenvirtualisierung mit Azure SQL Managed Instance

Gilt für:Azure SQL Managed Instance

In diesem Artikel wird das Datenvirtualisierungsfeature der verwalteten Azure SQL-Instanz beschrieben. Mit der Datenvirtualisierung können Sie Transact-SQL -Abfragen (T-SQL) für Dateien ausführen, die Daten in gängigen Datenformaten in Azure Data Lake Storage Gen2 oder Azure Blob Storage speichern. Sie können diese Daten mit lokal gespeicherten relationalen Daten kombinieren, indem Sie Verknüpfungen verwenden. Mit der Datenvirtualisierung können Sie transparent im schreibgeschützten Modus auf externe Daten zugreifen, während Sie sie im ursprünglichen Format und an seinem ursprünglichen Speicherort beibehalten.

Übersicht

Die Datenvirtualisierung bietet zwei Möglichkeiten zum Abfragen von Dateien, die für verschiedene Szenarien vorgesehen sind:

  • OPENROWSET-Syntax: Optimiert für Ad-hoc-Abfragen von Dateien. Wird in der Regel verwendet, um den Inhalt und die Struktur einer neuen Gruppe von Dateien schnell zu erkunden.
  • CREATE EXTERNAL TABLE-Syntax: Optimiert für wiederholte Abfragen von Dateien mit identischer Syntax als ob Daten lokal in der Datenbank gespeichert wurden. Externe Tabellen erfordern im Vergleich zur OPENROWSET-Syntax mehrere Vorbereitungsschritte, ermöglichen aber eine bessere Kontrolle über den Datenzugriff. Verwenden Sie externe Tabellen für analytische Workloads und Berichte.

Erstellen Sie in beiden Fällen eine externe Datenquelle mithilfe der CREATE EXTERNAL DATA SOURCE-Syntax in T-SQL, wie in diesem Artikel gezeigt.

CREATE EXTERNAL TABLE AS SELECT syntax ist auch für Azure SQL Managed Instance verfügbar. Dies besteht darin, die Ergebnisse einer T-SQL-Anweisung SELECT in die Parkett- oder CSV-Dateien in Azure Blob Storage oder Azure Data Lake Storage (ADLS) Gen 2 zu exportieren und eine externe Tabelle über 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

Speichern Sie Dateien in Azure Data Lake Storage Gen2 oder Azure Blob Storage. Um Dateien abzufragen, geben Sie den Speicherort in einem bestimmten Format an, und verwenden Sie das Speicherorttyppräfix, das dem Typ der externen Quelle und des Endpunkts oder Protokolls entspricht, z. B. die folgenden Beispiele:

--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 bereitgestellte Standorttyppräfix wird verwendet, um das optimale Protokoll für die Kommunikation auszuwählen und alle erweiterten Funktionen des jeweiligen Speichertyps zu verwenden. Das Verwenden des generischen Präfix https:// ist deaktiviert. Verwenden Sie stets endpunktspezifische Präfixe.

Erste Schritte

Wenn Sie mit der Datenvirtualisierung noch nicht vertraut sind und schnell Funktionen testen möchten, fragen Sie zunächst öffentliche Datensätze ab, die in Azure Open Datasets verfügbar sind, z. B. das Bing COVID-19-Dataset , das anonymen Zugriff zulässt.

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

  • Parkett: 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 zunächst eine T-SQL-Abfrage aus, um erste Einblicke in das Dataset 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 Erkundung von Datensätzen fortsetzen, indem Sie WHERE, GROUP BY und andere Klauseln basierend auf der Ergebnismenge der ersten Abfrage anfügen.

Wenn die erste Abfrage in Ihrer verwalteten SQL-Instanz fehlschlägt, hat diese Instanz wahrscheinlich eingeschränkten Zugriff auf Azure-Speicherkonten. Sprechen Sie mit Ihrem Netzwerkexperten, um den Zugriff zu ermöglichen, bevor Sie mit der Abfrage fortfahren.

Wenn Sie mit der Abfrage öffentlicher Datensätze vertraut sind, sollten Sie zu nichtöffentlichen Datensätzen wechseln, bei denen Sie Anmeldeinformationen bereitstellen, Zugriffsrechte vergeben und Firewall-Regeln konfigurieren müssen. In vielen realen Szenarien arbeiten Sie in erster Linie mit privaten Datensätzen.

Zugriff auf nicht öffentliche Speicherkonten

Ein Benutzer, der sich bei einer verwalteten SQL-Instanz anmeldet, muss autorisiert sein, auf dateien zuzugreifen, die in einem nicht öffentlichen Speicherkonto gespeichert sind. Die Autorisierungsschritte hängen davon ab, wie sich die von SQL verwaltete Instanz beim Speicherkonto authentifiziert. Der Authentifizierungstyp und alle zugehörigen Parameter werden nicht direkt mit jeder Abfrage bereitgestellt. Das datenbankbezogene Anmeldeobjekt, das in der Benutzerdatenbank gespeichert ist, kapselt diese Informationen. Die Datenbank verwendet die Anmeldeinformationen, um jedes Mal auf das Speicherkonto zuzugreifen, wenn die Abfrage ausgeführt wird.

Azure SQL Managed Instance unterstützt die folgenden Authentifizierungstypen:

  • Verwaltete Identität
  • Shared Access Signature (SAS)

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. Sie können diese Identität verwenden, um Anforderungen für den Datenzugriff in nicht lizenzierten 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. Erteilen Sie Berechtigungen für die vom System zugewiesene verwaltete Identität der sql-verwalteten Instanz auf die gleiche Weise, wie Sie jedem anderen Microsoft Entra-Benutzer Berechtigungen erteilen. Zum 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. Diese Rolle bietet 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 Zugriff auf das Lesen einzelner Dateien in diesen Daten benötigen, müssen auch über die Berechtigung "Ausführen" für alle übergeordneten Ordner bis zum Stamm (dem Container) verfügen. Weitere Informationen finden Sie unter Festlegen von ACLs in Azure Data Lake Storage Gen2.
  3. Wählen Sie auf der nächsten Seite unter Zugriff zuweisen zu die Option Verwaltete Identität aus. Wählen Sie + Mitglieder auswählen, und wählen Sie unter 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. Erstellen Sie die datenbank-spezifischen Anmeldeinformationen für die Authentifizierung von verwalteten Identitäten. 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

Eine externe Datenquelle ist eine Abstraktion, die einen einfachen Verweis auf einen Dateispeicherort über mehrere Abfragen hinweg bietet. Um öffentliche Speicherorte abzufragen, geben Sie den Dateispeicherort an, wenn Sie eine externe Datenquelle erstellen:

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

Um auf nicht öffentliche Speicherkonten zuzugreifen, geben Sie den Speicherort an und verweisen Sie auf eine datenbankbereichsbezogene Anmeldeinformation mit gekapselten Authentifizierungsparametern. Das folgende Skript erstellt eine externe Datenquelle, die auf den Dateipfad verweist und auf eine datenbankbezogene Anmeldeinformationen verweist:

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

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 OPENROWSETdas Format der Datei an, z. B. das folgende 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 Sie alle Dateien mit .parquet der Erweiterung in Ordnern abfragen. Zum Beispiel ist die folgende Abfrage nur für Dateien, die einem bestimmten 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.

Obwohl dies praktisch ist, sind abgeleitete Datentypen möglicherweise größer als die tatsächlichen Datentypen, da möglicherweise nicht genügend Informationen in den Quelldateien vorhanden sind, um sicherzustellen, dass der entsprechende Datentyp verwendet wird. Dies kann die Abfrageleistung beeinträchtigen. Beispielsweise enthalten Parkettdateien keine Metadaten zur maximalen Zeichenspaltenlänge, sodass die Instanz sie als varchar(8000) ableiten lässt.

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

Nachdem Sie die Datentypen kennen, geben Sie diese mithilfe der Klausel an, um die WITH 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, geben Sie immer Spalten mithilfe der WITH Klausel an:

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 gibt z. B. einen Teil des Pfads zurück, der mit dem ersten Wildcard übereinstimmt.

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. Mithilfe von Ansichten kann die Anzahl der Dateien und die Datenmenge reduziert werden, die Abfrage über der Ansicht muss gelesen und verarbeitet werden, wenn sie 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, sodass die Abfrage fast identisch ist wie das Abfragen lokaler relationaler Daten, die in Benutzertabellen gespeichert sind. Zum Erstellen einer externen Tabelle müssen Sie über eine externe Datenquelle und externe Dateiformatobjekte verfügen:

--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 Sie die externe Tabelle erstellt haben, können Sie sie wie jede andere Tabelle abfragen:

SELECT TOP 10 *
FROM tbl_TaxiRides;

Wie OPENROWSET unterstützen externe Tabellen das Abfragen mehrerer Dateien und Ordner mit Wildcards. Externe Tabellen unterstützen jedoch keine Schemaferenz.

Überlegungen zur Leistung

Es gibt keine harte Beschränkung auf die Anzahl der Dateien oder die Datenmenge, die Sie abfragen können, aber die Abfrageleistung hängt von der Datenmenge, dem Datenformat, der Art der Organisation von Daten und der Komplexität von Abfragen und Verknüpfungen ab.

Abfragen von partitionierten Daten

Daten werden häufig in Unterordnern organisiert, auch als Partitionen bezeichnet. Sie können sql managed instance 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, wie im folgenden Beispiel gezeigt.

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

Einzelspaltenstatistiken für den OPENROWSET Pfad können mithilfe der sys.sp_create_openrowset_statistics gespeicherten Prozedur erstellt werden, indem die Auswahlabfrage 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.
  • Mehrspaltige Statistiken werden nicht unterstützt.

Problembehandlung

Probleme mit der Abfrageausführung treten in der Regel auf, wenn die von SQL verwaltete Instanz nicht auf den Dateispeicherort zugreifen kann. Verwandte Fehlermeldungen melden möglicherweise unzureichende Zugriffsrechte, einen Speicherort, der nicht vorhanden ist, eine Datei, die von einem anderen Prozess verwendet wird, oder dieses Verzeichnis kann nicht aufgeführt werden. In den meisten Fällen weisen diese Fehler darauf hin, dass Richtlinien zur Netzwerkdatenverkehrskontrolle den Zugriff auf Dateien blockieren oder der Benutzer keine Zugriffsrechte hat. Überprüfen Sie die folgenden Elemente:

  • Falscher oder falsch eingegebener Pfad zum Speicherort.
  • Gültigkeit des SAS-Schlüssels. Es könnte abgelaufen sein, enthält einen Tippfehler oder beginnt mit einem Fragezeichen.
  • SAS-Schlüsselberechtigungen sind zulässig. Mindestens lesen und auflisten , wenn Wildcards verwendet werden.
  • Blockierter eingehender Datenverkehr für das Speicherkonto. Überprüfen Sie die Verwaltung virtueller Netzwerkregeln für Azure Storage auf Details, und stellen Sie sicher, dass der Zugriff über die verwaltete SQL-Instanz-VNet zulässig ist.
  • Blockierter ausgehender Datenverkehr in der SQL-verwalteten Instanz mithilfe der Speicherendpunktrichtlinie. Lassen Sie ausgehenden Datenverkehr an das Speicherkonto zu.
  • Verwaltete Identitätszugriffsrechte. Stellen Sie sicher, dass die verwaltete Identität der Instanz Über Zugriffsrechte für das Speicherkonto verfügt.
  • Der Kompatibilitätsgrad der Datenbank muss mindestens 130 betragen, damit Datenvirtualisierungsabfragen funktionieren.

EXTERNE TABELLE ALS SELECT ERSTELLEN (CETAS)

CREATE EXTERNAL TABLE AS SELECT (CETAS) ermöglicht es Ihnen, Daten aus Ihrer sql-verwalteten Instanz in ein externes Speicherkonto zu exportieren. Sie können CETAS verwenden, um eine externe Tabelle über 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-Anweisung SELECT in die erstellte externe Tabelle exportieren. Mit diesen Funktionen besteht das Potenzial für Datenexfiltrationsrisiken, sodass die von Azure SQL verwaltete Instanz CETAS standardmäßig deaktiviert. 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

  • Last updated on