Wirtualizacja danych za pomocą usługi Azure SQL Managed Instance

  • Artykuł

Dotyczy:Azure SQL Managed Instance

Funkcja wirtualizacji danych usługi Azure SQL Managed Instance umożliwia wykonywanie zapytań języka Transact-SQL (T-SQL) dotyczących plików przechowywanych w typowych formatach danych w usłudze Azure Data Lake Storage Gen2 lub Azure Blob Storage oraz łączenie ich 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.

Omówienie

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.

Dostępna jest również składnia CREATE EXTERNAL TABLE AS SELECT dla usługi Azure SQL Managed Instance w celu wyeksportowania wyników instrukcji T-SQL SELECT do plików Parquet lub CSV w usłudze Azure Blob Storage lub Azure Data Lake Storage (ADLS) Gen 2 i utworzeniu zewnętrznej tabeli na podstawie tych plików.

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 magazynu

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

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

Ważne

Podany prefiks Typu lokalizacji służy do wybierania optymalnego protokołu komunikacji i wykorzystania wszelkich zaawansowanych funkcji oferowanych przez określony typ magazynu. Używanie prefiksu ogólnego https:// jest wyłączone. Zawsze używaj prefiksów specyficznych dla punktu końcowego.

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:

  • 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

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 i inne na podstawie zestawu wyników pierwszego zapytania.

Jeśli pierwsze zapytanie nie powiedzie się w wystąpieniu zarządzanym, to wystąpienie prawdopodobnie ma ograniczony dostęp do kont usługi Azure Storage i skontaktuj się z ekspertem ds. sieci, aby włączyć dostęp, zanim będzie można kontynuować wykonywanie zapytań.

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 do wystąpienia zarządzanego musi mieć autoryzację dostępu do plików przechowywanych na koncie magazynu niepublikowanego i wykonywania zapytań o niepubliczne. Kroki autoryzacji zależą od sposobu uwierzytelniania wystąpienia zarządzanego w magazynie. Typ uwierzytelniania i wszystkie powiązane parametry nie są dostarczane 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 Managed Instance obsługuje następujące typy uwierzytelniania:

Tożsamość zarządzana to funkcja identyfikatora entra firmy Microsoft (dawniej Azure Active Directory), która udostępnia usługi platformy Azure — takie jak Azure SQL Managed Instance — z tożsamością zarządzaną w usłudze Microsoft Entra ID. Ta tożsamość może służyć do autoryzacji żądań dostępu do danych na kontach magazynu niepublicowego. Usługi, takie jak Azure SQL Managed Instance, mają tożsamość zarządzaną przypisaną przez system i mogą również mieć co najmniej jedną tożsamość zarządzaną przypisaną przez użytkownika. Do wirtualizacji danych z usługą Azure SQL Managed Instance można użyć tożsamości zarządzanych przypisanych przez system lub tożsamości zarządzanych przypisanych przez użytkownika.

Administrator usługi Azure Storage musi najpierw udzielić uprawnień tożsamości zarządzanej w celu uzyskania dostępu do danych. Udziel uprawnień do przypisanej przez system tożsamości zarządzanej wystąpienia zarządzanego w taki sam sposób, w jaki uprawnienia są przyznawane innym użytkownikowi firmy Microsoft Entra. Na przykład:

  1. W witrynie Azure Portal na stronie Kontrola dostępu (zarządzanie dostępem i tożsamościami) konta magazynu wybierz pozycję Dodaj przypisanie roli.
  2. Wybierz wbudowaną rolę RBAC platformy Azure dla czytnika danych obiektów blob usługi Storage. Zapewnia to dostęp do odczytu tożsamości zarządzanej dla niezbędnych kontenerów usługi Azure Blob Storage.
    • Zamiast udzielać tożsamości zarządzanej roli RBAC usługi Storage Blob Data Reader , możesz również udzielić bardziej szczegółowych uprawnień w podzestawie plików. Wszyscy użytkownicy, którzy potrzebują dostępu do odczytu poszczególnych plików niektórych danych w tym kontenerze, również muszą mieć uprawnienie Wykonywanie we wszystkich folderach nadrzędnych do katalogu głównego (kontenera). Dowiedz się więcej na temat ustawiania list ACL w usłudze Azure Data Lake Storage Gen2.
  3. Na następnej stronie wybierz pozycję Przypisz dostęp dotożsamości zarządzanej. + Wybierz członków, a następnie na liście rozwijanej Tożsamość zarządzana wybierz żądaną tożsamość zarządzaną. Aby uzyskać więcej informacji, zobacz przypisywanie ról Azure za pomocą portalu Azure.
  4. Następnie utworzenie poświadczeń o zakresie bazy danych na potrzeby uwierzytelniania tożsamości zarządzanej jest proste. Zwróć uwagę na poniższy przykład, który 'Managed Identity' jest ciągiem zakodowanym w kodzie.
-- 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'

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://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
        CREDENTIAL = [MyCredential];
)

Wykonywanie zapytań dotyczących źródeł danych przy użyciu funkcji 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;
--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;

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 za pomocą parametru zwraca część ścieżki zgodnej z symbolem wieloznacznymi na pozycji określonej 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
)
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

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ć wystąpienie zarządzane, aby wysyłało zapytania tylko do określonych folderów i plików. 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. Partycje z wykonywania zapytań można wyeliminować przy użyciu funkcji filepath() metadanych 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 i filename() funkcje są obsługiwane, filepath() ale nie w klauzuli WHERE. Nadal można filtrować według filename lub filepath jeśli są one używane w kolumnach obliczeniowych. W poniższym przykładzie pokazano następujące kwestie:

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

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

Statystyki

Zbieranie statystyk dotyczących danych zewnętrznych jest jedną z najważniejszych czynności, które można wykonać w celu optymalizacji zapytań. Tym bardziej wystąpienie wie o danych, tym szybciej może wykonywać zapytania. Optymalizator zapytań aparatu SQL jest optymalizatorem opartym na kosztach. Porównuje koszt różnych planów zapytań, a następnie wybiera plan z najniższym kosztem. W większości przypadków wybiera plan, który jest wykonywany najszybciej.

Automatyczne tworzenie statystyk

Usługa Azure SQL Managed Instance analizuje przychodzące zapytania użytkowników w celu uzyskania brakujących statystyk. Jeśli brakuje statystyk, optymalizator zapytań automatycznie tworzy statystyki dotyczące poszczególnych kolumn w predykacie zapytania lub warunku sprzężenia w celu zwiększenia oszacowań kardynalności dla planu zapytania. Automatyczne tworzenie statystyk jest wykonywane synchronicznie, dzięki czemu wydajność zapytań może być nieco obniżona, jeśli w kolumnach brakuje statystyk. Czas tworzenia statystyk dla pojedynczej kolumny zależy od rozmiaru plików docelowych.

Statystyki ręczne OPENROWSET

Statystyki z jedną kolumną dla OPENROWSET ścieżki można utworzyć przy użyciu sys.sp_create_openrowset_statistics procedury składowanej, przekazując zapytanie wyboru z jedną kolumną jako parametr:

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

Domyślnie wystąpienie używa 100% danych podanych w zestawie danych do tworzenia statystyk. Opcjonalnie możesz określić rozmiar próbki jako wartość procentową, korzystając z TABLESAMPLE opcji. Aby utworzyć statystyki z jedną kolumną dla wielu kolumn, wykonaj sys.sp_create_openrowset_statistics dla każdej z kolumn. Nie można utworzyć statystyk z wieloma kolumnami dla ścieżki OPENROWSET .

Aby zaktualizować istniejące statystyki, upuść je najpierw przy użyciu sys.sp_drop_openrowset_statistics procedury składowanej, a następnie utwórz je ponownie przy użyciu polecenia 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
';

Statystyki ręczne tabeli zewnętrznej

Składnia tworzenia statystyk dotyczących tabel zewnętrznych przypomina składnię używaną dla zwykłych tabel użytkownika. Aby utworzyć statystyki dla kolumny, podaj nazwę obiektu statystyk i nazwę kolumny:

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

Opcje WITH są obowiązkowe, a dla rozmiaru próbki dozwolone opcje to FULLSCAN i SAMPLE n procent.

  • Aby utworzyć statystyki z jedną kolumną dla wielu kolumn, wykonaj CREATE STATISTICS dla każdej z kolumn.
  • Statystyki wielokolumna nie są obsługiwane.

Rozwiązywanie problemów

Problemy z wykonywaniem zapytań są zwykle spowodowane tym, że wystąpienie zarządzane nie może uzyskać dostępu do lokalizacji pliku. 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. Aby uzyskać więcej informacji, zobacz Zarządzanie regułami sieci wirtualnej dla usługi Azure Storage i upewnij się, że dostęp z sieci wirtualnej wystąpienia zarządzanego jest dozwolony.
  • Ruch wychodzący zablokowany w wystąpieniu zarządzanym przy użyciu zasad punktu końcowego magazynu. Zezwalaj na ruch wychodzący do konta magazynu.
  • Prawa dostępu tożsamości zarządzanej: upewnij się, że tożsamość zarządzana wystąpienia ma przyznane prawa dostępu do konta magazynu.
  • Poziom zgodności bazy danych musi być 130 lub wyższy, aby zapytania wirtualizacji danych działały.

CREATE EXTERNAL TABLE AS SELECT (CETAS)

OPCJA CREATE EXTERNAL TABLE AS SELECT (CETAS) umożliwia eksportowanie danych z wystąpienia zarządzanego SQL do zewnętrznego konta magazynu. Za pomocą instrukcji CETAS można utworzyć tabelę zewnętrzną na podstawie plików Parquet lub CSV usługi Azure Blob Storage lub Azure Data Lake Storage (ADLS) Gen2. Instrukcje CETAS mogą również eksportować równolegle wyniki instrukcji T-SQL SELECT do utworzonej tabeli zewnętrznej. Istnieje możliwość wystąpienia ryzyka eksfiltracji danych z tymi możliwościami, więc opcja CETAS jest domyślnie wyłączona dla usługi Azure SQL Managed Instance. Aby włączyć, zobacz CREATE EXTERNAL TABLE AS SELECT (CETAS).

Ograniczenia

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.

Powiązana zawartość