Erstellen und Verwenden von Ansichten mit einem serverlosen SQL-Pool in Azure Synapse Analytics
In diesem Abschnitt erfahren Sie, wie Sie Ansichten erstellen und zum Umschließen von Abfragen für einen serverlosen SQL-Pool verwenden. Ansichten ermöglichen Ihnen die erneute Verwendung dieser Abfragen. Ansichten sind ebenfalls erforderlich, wenn Sie Tools wie Power BI zusammen mit einem serverlosen SQL-Pool verwenden möchten.
Voraussetzungen
Im ersten Schritt erstellen Sie eine Datenbank, in der die Ansicht erstellt werden soll, und Sie initialisieren die zum Authentifizieren beim Azure-Speicher erforderlichen Objekte, indem Sie das Setupskript für diese Datenbank ausführen. Alle Abfragen in diesem Artikel werden in Ihrer Beispieldatenbank ausgeführt.
Sichten externer Daten
Ansichten können auf die gleiche Weise wie reguläre SQL Server-Ansichten erstellt werden. Mit der folgenden Abfrage wird eine Ansicht erstellt, die die Datei population.csv liest.
Hinweis
Ändern Sie die erste Zeile in der Abfrage (d. h. [mydbname]), sodass die von Ihnen erstellte Datenbank verwendet wird.
USE [mydbname];
GO
DROP VIEW IF EXISTS populationView;
GO
CREATE VIEW populationView AS
SELECT *
FROM OPENROWSET(
BULK 'csv/population/population.csv',
DATA_SOURCE = 'SqlOnDemandDemo',
FORMAT = 'CSV',
FIELDTERMINATOR =',',
ROWTERMINATOR = '\n'
)
WITH (
[country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
[country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
[year] smallint,
[population] bigint
) AS [r];
In der Ansicht wird EXTERNAL DATA SOURCE mit einer Stamm-URL Ihres Speichers als DATA_SOURCE verwendet und den Dateien ein neuer relativer Dateipfad hinzugefügt.
Delta Lake-Sichten
Wenn Sie die Sichten für den Ordner „Delta Lake“ erstellen, müssen Sie nach der Option BULK anstelle des Dateipfads den Speicherort im Stammordner angeben.
Die OPENROWSET-Funktion, die Daten aus dem Delta Lake-Ordner liest, untersucht die Ordnerstruktur und bestimmt automatisch die Dateispeicherorte.
create or alter view CovidDeltaLake
as
select *
from openrowset(
bulk 'covid',
data_source = 'DeltaLakeStorage',
format = 'delta'
) with (
date_rep date,
cases int,
geo_id varchar(6)
) as rows
Weitere Informationen finden Sie auf der Selbsthilfeseite bei Problemen mit serverlosen Synapse-SQL-Pools und Bekannte Probleme mit Azure Synapse Analytics.
Partitionierte Sichten
Wenn Sie über Dateien verfügen, die in der hierarchischen Ordnerstruktur partitioniert sind, können Sie das Partitionsmuster mithilfe der Platzhalter im Dateipfad beschreiben. Verwenden Sie die FILEPATH-Funktion, um Teile des Ordnerpfads als Partitionierungsspalten verfügbar zu machen.
CREATE VIEW TaxiView
AS SELECT *, nyc.filepath(1) AS [year], nyc.filepath(2) AS [month]
FROM
OPENROWSET(
BULK 'parquet/taxi/year=*/month=*/*.parquet',
DATA_SOURCE = 'sqlondemanddemo',
FORMAT='PARQUET'
) AS nyc
Partitionierte Ansichten können die Leistung Ihrer Abfragen verbessern, indem sie eine Partitionseliminierung durchführen, wenn Sie sie mit Filtern auf den Partitionierungsspalten abfragen. Allerdings unterstützen nicht alle Abfragen die Partitionseliminierung, daher ist es wichtig, einige bewährte Methoden zu befolgen.
Um die Partitionseliminierung zu gewährleisten, vermeiden Sie die Verwendung von Unterabfragen in Filtern, da diese die Fähigkeit zur Partitionseliminierung beeinträchtigen können. Übergeben Sie stattdessen das Ergebnis der Unterabfrage als Variable an den Filter.
Wenn Sie JOINs in SQL-Abfragen verwenden, deklarieren Sie das Filterprädikat als NVARCHAR, um die Komplexität des Abfrageplans zu reduzieren und die Wahrscheinlichkeit einer korrekten Partitionseliminierung zu erhöhen. Partitionsspalten werden in der Regel als NVARCHAR(1024) abgeleitet. Durch die Verwendung desselben Typs für das Prädikat wird die Notwendigkeit einer impliziten Umwandlung vermieden, die die Komplexität des Abfrageplans erhöhen kann.
Partitionierte Delta Lake-Sichten
Wenn Sie die partitionierten Sichten für Delta Lake-Speicher erstellen, können Sie nur einen Delta Lake-Stammordner angeben und müssen die Spalten für die Partitionierung nicht explizit mit der Funktion FILEPATH verfügbar machen:
CREATE OR ALTER VIEW YellowTaxiView
AS SELECT *
FROM
OPENROWSET(
BULK 'yellow',
DATA_SOURCE = 'DeltaLakeStorage',
FORMAT='DELTA'
) nyc
Die OPENROWSET-Funktion untersucht die Struktur des zugrunde liegenden Delta Lake-Ordners, ermittelt automatisch die Spalten für die Partitionierung und macht sie verfügbar. Die Partitionslöschung erfolgt automatisch, wenn Sie in der WHERE-Klausel einer Abfrage die Partitionierungsspalte angeben.
Der Ordnername in der OPENROWSET-Funktion (yellow in diesem Beispiel), der mit dem in der Datenquelle DeltaLakeStorage definierten URI LOCATION verkettet ist, muss auf den Delta Lake-Stammordner verweisen, der einen Unterordner namens _delta_log enthält.
Weitere Informationen finden Sie auf der Selbsthilfeseite bei Problemen mit serverlosen Synapse-SQL-Pools und Bekannte Probleme mit Azure Synapse Analytics.
JSON-Ansichten
Die Ansichten sind die beste Wahl, wenn Sie auf der Grundlage des Resultset, das aus den Dateien abgerufen wird, weitere Verarbeitungsschritte ausführen müssen. Ein Beispiel hierfür ist das Analysieren von JSON-Dateien, bei denen Sie die JSON-Funktionen anwenden müssen, um die Werte aus den JSON-Dokumenten zu extrahieren:
CREATE OR ALTER VIEW CovidCases
AS
select
*
from openrowset(
bulk 'latest/ecdc_cases.jsonl',
data_source = 'covid',
format = 'csv',
fieldterminator ='0x0b',
fieldquote = '0x0b'
) with (doc nvarchar(max)) as rows
cross apply openjson (doc)
with ( date_rep datetime2,
cases int,
fatal int '$.deaths',
country varchar(100) '$.countries_and_territories')
Die Funktion OPENJSON analysiert jede Zeile aus der JSONL-Datei, die ein JSON-Dokument pro Zeile im Textformat enthält.
Azure Cosmos DB-Sichten für Container
Die Sichten können auf der Grundlage der Azure Cosmos DB-Container erstellt werden, wenn der analytische Azure Cosmos DB-Speicher für den Container aktiviert ist. Der Azure Cosmos DB-Kontoname, -Datenbankname und -Containername sollten als Teil der Sicht hinzugefügt werden, und der schreibgeschützte Zugriffsschlüssel muss in die datenbankspezifischen Anmeldeinformationen eingefügt werden, auf die die Sicht verweist.
CREATE DATABASE SCOPED CREDENTIAL MyCosmosDbAccountCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 's5zarR2pT0JWH9k8roipnWxUYBegOuFGjJpSjGlR36y86cW0GQ6RaaG8kGjsRAQoWMw1QKTkkX8HQtFpJjC8Hg==';
GO
CREATE OR ALTER VIEW Ecdc
AS SELECT *
FROM OPENROWSET(
PROVIDER = 'CosmosDB',
CONNECTION = 'Account=synapselink-cosmosdb-sqlsample;Database=covid',
OBJECT = 'Ecdc',
CREDENTIAL = 'MyCosmosDbAccountCredential'
) with ( date_rep varchar(20), cases bigint, geo_id varchar(6) ) as rows
Weitere Informationen finden Sie unter Abfragen von Azure Cosmos DB-Daten mit serverlosem SQL-Pool in Azure Synapse Link.
Verwenden einer Ansicht
Ansichten können in Ihren Abfragen auf die gleiche Weise verwendet werden wie in SQL Server-Abfragen.
Die folgende Abfrage veranschaulicht die Verwendung der Ansicht population_csv, die Sie unter Erstellen einer Ansicht erstellt haben. Sie gibt Länder-/Regionsnamen mit der entsprechenden Bevölkerung im Jahr 2019 in absteigender Reihenfolge zurück.
Hinweis
Ändern Sie die erste Zeile in der Abfrage (d. h. [mydbname]), sodass die von Ihnen erstellte Datenbank verwendet wird.
USE [mydbname];
GO
SELECT
country_name, population
FROM populationView
WHERE
[year] = 2019
ORDER BY
[population] DESC;
Wenn Sie die Ansicht abfragen, können Fehler oder unerwartete Ergebnisse auftreten. Dies bedeutet wahrscheinlich, dass die Ansicht auf Spalten oder Objekte verweist, die geändert wurden oder nicht mehr vorhanden sind. Sie müssen die Ansichtsdefinition manuell anpassen, um sie an die zugrunde liegenden Schemaänderungen anzupassen.
Nächste Schritte
Informationen zum Abfragen verschiedener Dateitypen finden Sie in den Artikeln zum Abfragen einer einzelnen CSV-Datei, Abfragen von Parquet-Dateien und Abfragen von JSON-Dateien.