Wirtualizacja danych za pomocą usługi Azure SQL Database (wersja zapoznawcza)

Dotyczy:Azure SQL Database

Funkcja wirtualizacji danych usługi Azure SQL Database umożliwia wykonywanie zapytań Transact-SQL (T-SQL) dotyczących plików przechowujujących dane w typowych formatach danych, takich jak CSV (bez konieczności używania tekstu rozdzielanego), Parquet i delta (1.0). Te dane można wykonywać w usłudze Azure Data Lake Storage Gen2 lub Azure Blob Storage i łączyć je z lokalnie przechowywanymi danymi relacyjnymi przy użyciu sprzężeń. Dzięki temu można w sposób niewidoczny uzyskiwać dostęp do danych zewnętrznych (w trybie tylko do odczytu) przy zachowaniu oryginalnego formatu i lokalizacji — nazywanej również wirtualizacją danych.

Przegląd

Wirtualizacja danych udostępnia dwa sposoby wykonywania zapytań dotyczących plików przeznaczonych dla różnych zestawów scenariuszy:

• Składnia OPENROWSET — zoptymalizowana pod kątem wykonywania zapytań ad hoc dotyczących plików. Zazwyczaj służy do szybkiego eksplorowania zawartości i struktury nowego zestawu plików.
• CREATE EXTERNAL TABLE składnia — zoptymalizowana pod kątem powtarzalnego wykonywania zapytań dotyczących plików przy użyciu identycznej składni, tak jakby dane były przechowywane lokalnie w bazie danych. Tabele zewnętrzne wymagają kilku kroków przygotowywania w porównaniu ze składnią OPENROWSET, ale umożliwiają większą kontrolę nad dostępem do danych. Tabele zewnętrzne są zwykle używane na potrzeby obciążeń analitycznych i raportowania.

W obu przypadkach należy utworzyć zewnętrzne źródło danych przy użyciu składni języka T-SQL CREATE EXTERNAL DATA SOURCE , jak pokazano w tym artykule.

Formaty plików

Formaty plików Parquet i rozdzielane tekstem (CSV) są obsługiwane bezpośrednio. Format pliku JSON jest pośrednio obsługiwany przez określenie formatu pliku CSV, w którym zapytania zwracają każdy dokument jako oddzielny wiersz. Wiersze można analizować dalej przy użyciu poleceń JSON_VALUE i OPENJSON.

Typy magazynów

Pliki można przechowywać w usłudze Azure Data Lake Storage Gen2 lub Azure Blob Storage. Aby wykonać zapytanie dotyczące plików, należy podać lokalizację w określonym formacie i użyć prefiksu typu lokalizacji odpowiadającego typowi zewnętrznego źródła i punktu końcowego/protokołu, na przykład w następujących przykładach:

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

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

Rozpocznij

Jeśli dopiero zaczynasz wirtualizację danych i chcesz szybko testować funkcje, zacznij od wykonywania zapytań dotyczących publicznych zestawów danych dostępnych w zestawach danych usługi Azure Open, takich jak zestaw danych Bing COVID-19, który zezwala na dostęp anonimowy.

Użyj następujących punktów końcowych, aby wykonywać zapytania dotyczące zestawów danych Bing COVID-19:

• Parkiet: 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

Aby uzyskać szybki start, uruchom to proste zapytanie T-SQL, aby uzyskać pierwsze szczegółowe informacje o zestawie danych. To zapytanie używa zestawu OPENROWSET do wykonywania zapytań dotyczących pliku przechowywanego na publicznie dostępnym koncie magazynu:

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

** Możesz kontynuować eksplorację zestawu danych, dołączając klauzule WHERE, GROUP BY oraz inne, w oparciu o zestaw wyników pierwszego zapytania.

Po zapoznaniu się z wykonywaniem zapytań dotyczących publicznych zestawów danych rozważ przełączenie się na niepublikacyjne zestawy danych, które wymagają podania poświadczeń, udzielenia praw dostępu i skonfigurowania reguł zapory. W wielu rzeczywistych scenariuszach będziesz działać głównie z prywatnymi zestawami danych.

Dostęp do kont magazynu niepublicowego

Użytkownik zalogowany w usłudze Azure SQL Database musi mieć autoryzację, aby uzyskać dostęp do plików przechowywanych na niepublicznych kontach magazynowych oraz wykonywać zapytania na tych plikach. Kroki autoryzacji zależą od sposobu uwierzytelniania dostępu do magazynu przez Azure SQL Database. Typy uwierzytelniania i wszystkie powiązane parametry nie są udostępniane bezpośrednio z każdym zapytaniem. Są one hermetyzowane w obiekcie poświadczeń o zakresie bazy danych przechowywanym w bazie danych użytkownika. Poświadczenie jest używane przez bazę danych do uzyskiwania dostępu do konta magazynu w dowolnym momencie wykonywania zapytania.

Usługa Azure SQL Database obsługuje następujące typy uwierzytelniania:

• Sygnatura dostępu współdzielonego (SAS)
• Tożsamość zarządzana
• Uwierzytelnianie przelotowe Microsoft Entra za pomocą tożsamości użytkownika

Dostęp do magazynu publicznego za pośrednictwem kont anonimowych

Jeśli żądany zestaw danych zezwala na dostęp publiczny (znany również jako dostęp anonimowy), żadne poświadczenia nie są wymagane, o ile usługa Azure Storage jest prawidłowo skonfigurowana, zobacz Konfigurowanie anonimowego dostępu do odczytu dla kontenerów i obiektów blob.

Zewnętrzne źródło danych

Zewnętrzne źródło danych to abstrakcja, która umożliwia łatwe odwoływanie się do lokalizacji pliku w wielu zapytaniach. Aby wykonywać zapytania dotyczące lokalizacji publicznych, wystarczy określić podczas tworzenia zewnętrznego źródła danych lokalizację pliku:

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

Podczas uzyskiwania dostępu do kont magazynu niepublikowanego wraz z lokalizacją należy również odwołać się do poświadczeń w zakresie bazy danych z hermetyzowanymi parametrami uwierzytelniania. Poniższy skrypt tworzy zewnętrzne źródło danych wskazujące ścieżkę pliku i odwołujące się do poświadczeń o zakresie bazy danych.

--Create external data source pointing to the file path, and referencing database-scoped credential:
CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource
WITH (
    LOCATION = 'abs://<privatecontainer>@privatestorageaccount.blob.core.windows.net/dataset/' 
       CREDENTIAL = [MyCredential]
);

Wykonywanie zapytań dotyczących źródeł danych przy użyciu zestawu OPENROWSET

Składnia OPENROWSET umożliwia natychmiastowe wykonywanie zapytań ad hoc przy jednoczesnym tworzeniu tylko minimalnej liczby niezbędnych obiektów bazy danych.

OPENROWSETWymaga tylko utworzenia zewnętrznego źródła danych (i ewentualnie poświadczeń) w przeciwieństwie do podejścia do tabeli zewnętrznej, co wymaga formatu pliku zewnętrznego i samej tabeli zewnętrznej.

DATA_SOURCE Wartość parametru jest automatycznie poprzedzana parametrem BULK w celu utworzenia pełnej ścieżki do pliku.

W przypadku użycia OPENROWSET funkcji podaj format pliku, taki jak w poniższym przykładzie, który wysyła zapytanie do pojedynczego pliku:

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

Wykonywanie zapytań dotyczących wielu plików i folderów

Polecenie OPENROWSET umożliwia również wykonywanie zapytań dotyczących wielu plików lub folderów przy użyciu symboli wieloznacznych w ścieżce BULK.

W poniższym przykładzie użyto rekordu otwarcia zestawu danych NYC żółty przejazd taksówką.

Najpierw utwórz zewnętrzne źródło danych:

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

Teraz możemy wykonywać zapytania o wszystkie pliki z rozszerzeniem parquet w folderach. Na przykład w tym miejscu wykonamy zapytanie dotyczące tylko tych plików pasujących do wzorca nazwy:

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

Podczas wykonywania zapytań dotyczących wielu plików lub folderów wszystkie pliki, do których uzyskuje się dostęp, OPENROWSET muszą mieć taką samą strukturę (na przykład taką samą liczbę kolumn i typów danych). Nie można przechodzić rekursywnie folderów.

Wnioskowanie schematu

Automatyczne wnioskowanie schematu ułatwia szybkie pisanie zapytań i eksplorowanie danych, gdy nie znasz schematów plików. Wnioskowanie schematu działa tylko z plikami parquet.

Chociaż wygodne, wnioskowane typy danych mogą być większe niż rzeczywiste typy danych, ponieważ w plikach źródłowych może być wystarczająca ilość informacji, aby zapewnić użycie odpowiedniego typu danych. Może to prowadzić do niskiej wydajności zapytań. Na przykład pliki parquet nie zawierają metadanych o maksymalnej długości kolumny znaków, więc wystąpienie wywnioskuje je jako varchar(8000).

Użyj procedury składowanej sp_describe_first_results_set, aby sprawdzić wynikowe typy danych zapytania, tak jak w poniższym przykładzie:

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

Gdy znasz typy danych, możesz określić je przy użyciu klauzuli WITH , aby zwiększyć wydajność:

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;

Ponieważ nie można automatycznie określić schematu plików CSV, kolumny muszą być zawsze określone przy użyciu klauzuli 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;

Funkcje metadanych pliku

Podczas wykonywania zapytań dotyczących wielu plików lub folderów można użyć filepath() funkcji i filename() do odczytywania metadanych pliku oraz pobierania części ścieżki lub pełnej ścieżki i nazwy pliku pochodzącego z wiersza w zestawie wyników:

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

Po wywołaniu bez parametru filepath() funkcja zwraca ścieżkę pliku, z którego pochodzi wiersz. Gdy DATA_SOURCE jest używany w OPENROWSETpliku , zwraca ścieżkę względną do DATA_SOURCE, w przeciwnym razie zwraca pełną ścieżkę pliku.

Po wywołaniu z parametrem zwraca część ścieżki, która pasuje do symbolu wieloznacznego określonego w parametrze. Na przykład wartość parametru 1 zwróci część ścieżki zgodnej z pierwszym symbolem wieloznacznymi.

Funkcja filepath() może być również używana do filtrowania i agregowania wierszy:

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;

Tworzenie widoku w górnej części zestawu OPENROWSET

Możesz tworzyć widoki i używać ich do zawijania zapytań OPENROWSET, aby można było łatwo ponownie użyć bazowego zapytania:

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

Wygodne jest również dodawanie kolumn z danymi lokalizacji pliku do widoku przy użyciu funkcji w celu łatwiejszego filepath() i bardziej wydajnego filtrowania. Użycie widoków może zmniejszyć liczbę plików i ilość danych, które zapytanie musi być odczytywane i przetwarzane w przypadku filtrowania według dowolnej z tych kolumn:

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;

Widoki umożliwiają również raportowanie i narzędzia analityczne, takie jak usługa Power BI, aby korzystać z OPENROWSETwyników .

Tabele zewnętrzne

Tabele zewnętrzne hermetyzują dostęp do plików, dzięki czemu środowisko wykonywania zapytań jest niemal identyczne z wykonywaniem zapytań dotyczących lokalnych danych relacyjnych przechowywanych w tabelach użytkowników. Utworzenie tabeli zewnętrznej wymaga istnienia zewnętrznych obiektów formatu danych i plików zewnętrznych:

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

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

Po utworzeniu tabeli zewnętrznej możesz wykonać zapytanie w taki sam sposób, jak każda inna tabela:

SELECT TOP 10 *
FROM tbl_TaxiRides;

Podobnie jak OPENROWSETtabele zewnętrzne umożliwiają wykonywanie zapytań dotyczących wielu plików i folderów przy użyciu symboli wieloznacznych. Wnioskowanie schematu nie jest obsługiwane w przypadku tabel zewnętrznych.

Zagadnienia dotyczące wydajności

Nie ma twardego limitu liczby plików lub ilości danych, które mogą być odpytywane, ale wydajność zapytań zależy od ilości danych, formatu danych, sposobu organizowania danych i złożoności zapytań i sprzężeń.

Wykonywanie zapytań dotyczących danych partycjonowanych

Dane są często zorganizowane w podfolderach nazywanych również partycjami. Możesz poinstruować zapytanie, aby odczytywało tylko określone foldery i pliki. Dzięki temu zmniejsza liczbę plików i ilość danych, które zapytanie musi odczytywać i przetwarzać, co skutkuje lepszą wydajnością. Ten typ optymalizacji zapytań jest znany jako oczyszczanie partycji lub eliminacja partycji. Możesz wyeliminować partycje z wykonania zapytań, używając funkcji filepath() w klauzuli WHERE zapytania.

Następujące przykładowe zapytanie odczytuje pliki danych NYC Yellow Taxi tylko w ciągu ostatnich trzech miesięcy 2017 r.:

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;

Jeśli przechowywane dane nie są partycjonowane, rozważ podzielenie ich na partycje, aby zwiększyć wydajność zapytań.

Jeśli używasz tabel zewnętrznych, funkcje filepath() i filename() są obsługiwane, ale nie w klauzuli WHERE.

Rozwiązywanie problemów

Problemy z wykonywaniem zapytań są zwykle spowodowane tym, że usługa Azure SQL Database nie może uzyskać dostępu do lokalizacji plików. Powiązane komunikaty o błędach mogą zgłaszać niewystarczające prawa dostępu, nieistniejącą lokalizację lub ścieżkę pliku, plik używany przez inny proces lub nie można wymienić tego katalogu. W większości przypadków oznacza to, że dostęp do plików jest blokowany przez zasady kontroli ruchu sieciowego lub z powodu braku praw dostępu. Należy to sprawdzić:

• Nieprawidłowa lub błędnie wpisana ścieżka lokalizacji.
• Ważność klucza sygnatury dostępu współdzielonego: może ona zostać wygasła, zawierająca literówkę, zaczynając od znaku zapytania.
• Dozwolone uprawnienia klucza sygnatury dostępu współdzielonego: Odczyt co najmniej i Lista , jeśli są używane symbole wieloznaczne.
• Zablokowano ruch przychodzący na koncie magazynu. Zobacz Zarządzanie regułami sieci wirtualnej dla usługi Azure Storage.
• Prawa dostępu tożsamości zarządzanej: upewnij się, że tożsamości zarządzanej w usłudze Azure SQL Database zostały przyznane prawa dostępu do konta przechowywania.
• Poziom zgodności bazy danych musi być 130 lub wyższy, aby zapytania wirtualizacji danych działały.

Ograniczenia

• Obecnie statystyki dotyczące tabel zewnętrznych nie są obsługiwane w usłudze Azure SQL Database.
• CREATE EXTERNAL TABLE AS SELECT Obecnie nie jest dostępna w usłudze Azure SQL Database.
• Funkcja zabezpieczeń na poziomie wiersza nie jest obsługiwana w przypadku tabel zewnętrznych.
• Nie można zdefiniować reguły dynamicznego maskowania danych dla kolumny w tabeli zewnętrznej.
• Tożsamość Zarządzana nie obsługuje scenariuszy międzydzierżawowych; jeśli Twoje konto usługi Azure Storage znajduje się w innej dzierżawie, obsługiwaną metodą jest użycie sygnatury dostępu współdzielonego.

Znane problemy

• Gdy parametryzacja funkcji Always Encrypted jest włączona w programie SQL Server Management Studio (SSMS), zapytania wirtualizacji danych kończą się niepowodzeniem z komunikatem Incorrect syntax near 'PUSHDOWN' o błędzie.

Treści powiązane

• OPENROWSET T-SQL
• UTWÓRZ TABELĘ ZEWNĘTRZNĄ
• UTWÓRZ ZEWNĘTRZNY FORMAT PLIKU
• TWORZENIE ZEWNĘTRZNEGO ŹRÓDŁA DANYCH