Udostępnij za pomocą

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

Dotyczy:Azure SQL Managed Instance

W tym artykule opisano funkcję wirtualizacji danych usługi Azure SQL Managed Instance. Wirtualizacja danych umożliwia wykonywanie zapytań Transact-SQL (T-SQL) dotyczących plików, które przechowują dane w typowych formatach danych w usłudze Azure Data Lake Storage Gen2 lub Azure Blob Storage. Te dane można połączyć z lokalnie przechowywanymi danymi relacyjnymi przy użyciu sprzężeń. Dzięki wirtualizacji danych można w sposób niewidoczny uzyskiwać dostęp do danych zewnętrznych w trybie tylko do odczytu, zachowując ich oryginalny format i lokalizację.

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. Użyj tabel zewnętrznych na potrzeby obciążeń analitycznych i raportowania.

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

Składnia CREATE EXTERNAL TABLE AS SELECT jest również dostępna dla usługi Azure SQL Managed Instance. Jest to eksportowanie 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 utworzenie tabeli zewnętrznej 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

Przechowywanie plików w usłudze Azure Data Lake Storage Gen2 lub Azure Blob Storage. Aby wykonywać zapytania dotyczące plików, podaj lokalizację w określonym formacie i użyj prefiksu typu lokalizacji odpowiadającego typowi zewnętrznego źródła i punktu końcowego lub 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 do komunikacji i używania 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.

Wprowadzenie

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 umożliwia 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 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 WHEREklauzule , GROUP BYi inne na podstawie zestawu wyników pierwszego zapytania.

Jeśli pierwsze zapytanie nie powiedzie się w wystąpieniu zarządzanym SQL, to wystąpienie prawdopodobnie ma ograniczony dostęp do kont usługi Azure Storage. Skontaktuj się z ekspertem ds. sieci, aby włączyć dostęp przed kontynuowaniem wykonywania zapytań.

Jeśli znasz wykonywanie zapytań dotyczących publicznych zestawów danych, rozważ przełączenie na niepubliczne zestawy danych, które wymagają podania poświadczeń, udzielenia praw dostępu i skonfigurowania reguł zapory. W wielu rzeczywistych scenariuszach działasz głównie z prywatnymi zestawami danych.

Dostęp do kont magazynu niepublicowego

Użytkownik, który loguje się do wystąpienia zarządzanego SQL, musi być autoryzowany do dostępu i wykonywania zapytań dotyczących plików przechowywanych na niepublicznym koncie magazynu. Kroki autoryzacyjne zależą od sposobu uwierzytelniania wystąpienia zarządzanego SQL do konta magazynu. Typ uwierzytelniania i wszystkie powiązane parametry nie są dostarczane bezpośrednio z każdym zapytaniem. Obiekt poświadczeń bazy danych, przechowywany w bazie danych użytkownika, hermetyzuje te informacje. Baza danych używa poświadczeń, aby uzyskać dostęp do konta magazynu za każdym razem, gdy zapytanie zostanie wykonane.

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

  • Tożsamość zarządzana
  • Sygnatura dostępu współdzielonego (SAS)

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. Tej tożsamości można użyć do autoryzowania żądań dostępu do danych na niepublicznych kontach magazynowych. 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 SQL w taki sam sposób, jak w przypadku każdego innego użytkownika 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. Ta rola zapewnia 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 w tym zestawie danych, muszą mieć również uprawnienia do Uruchomienia we wszystkich folderach nadrzędnych do katalogu głównego (kontenera). Aby uzyskać więcej informacji, zobacz Ustawianie list kontrol dostępu (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 pozycję + 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 utwórz poświadczenia o zakresie bazy danych na potrzeby uwierzytelniania tożsamości zarządzanej. 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 zapewnia łatwe odwołanie do lokalizacji pliku w wielu zapytaniach. Aby wykonać zapytanie o lokalizacje publiczne, określ lokalizację pliku podczas tworzenia zewnętrznego źródła danych:

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

Aby uzyskać dostęp do kont magazynowych o ograniczonym dostępie, podaj lokalizację i odwołaj się do poświadczeń z zakresu bazy danych z hermetyzowanymi parametrami uwierzytelniania. Poniższy skrypt tworzy zewnętrzne źródło danych wskazujące na ścieżkę pliku i odwołuje się do poświadczenia ograniczonego do zakresu bazy danych.

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

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 korzystania z programu OPENROWSETpodaj 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żesz wykonywać zapytania dotyczące wszystkich plików z .parquet rozszerzeniem w folderach. Na przykład następujące zapytanie dotyczy 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ć za mało 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, określ 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, zawsze należy określić kolumny 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 zwraca część ścieżki zgodnej z pierwszym symbolem wieloznacznym.

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 odczytywać i przetwarzać podczas 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 zapewniają dostęp do plików, więc wykonywanie zapytań na nich jest niemal takie samo jak wykonywanie zapytań dotyczących danych relacyjnych przechowywanych w tabelach użytkowników. Aby utworzyć tabelę zewnętrzną, musisz mieć obiekty zewnętrznego źródła danych i zewnętrznego formatu plików:

--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 względem niej tak samo jak w przypadku każdej innej tabeli:

SELECT TOP 10 *
FROM tbl_TaxiRides;

Podobnie jak OPENROWSETtabele zewnętrzne obsługują wykonywanie zapytań dotyczących wielu plików i folderów z symbolami wieloznacznymi. Jednak tabele zewnętrzne nie obsługują wnioskowania schematu.

Zagadnienia dotyczące wydajności

Nie ma twardego ograniczenia liczby plików ani rozmiaru danych, które można objąć zapytaniem, ale wydajność zapytań zależy od rozmiaru danych, formatu danych, organizacji danych oraz złożoności zapytań i łączeń.

Wykonywanie zapytań dotyczących danych partycjonowanych

Dane są często zorganizowane w podfolderach, nazywanych również partycjami. Możesz poinstruować wystąpienie zarządzane SQL, 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. Można wyeliminować partycje podczas wykonywania zapytania, używając 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, funkcje filepath() oraz filename() są obsługiwane, ale nie w klauzuli WHERE. Nadal można filtrować według filename lub filepath w przypadku użycia ich w obliczonych kolumnach, takich jak pokazano w poniższym przykładzie:

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

Statystyka

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

Jednokolumnowe statystyki dla ścieżki OPENROWSET można utworzyć za pomocą procedury składowanej sys.sp_create_openrowset_statistics, przekazując zapytanie select z pojedynczą 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 wielokolumnowe nie są obsługiwane.

Rozwiązywanie problemów

Problemy z wykonywaniem zapytań zwykle występują, gdy wystąpienie zarządzane SQL nie może uzyskać dostępu do lokalizacji pliku. Powiązane komunikaty o błędach mogą zgłaszać niewystarczające prawa dostępu, lokalizację, która nie istnieje, plik używany przez inny proces lub nie można wymienić tego katalogu. W większości przypadków te błędy wskazują, że zasady kontroli ruchu sieciowego blokują dostęp do plików lub użytkownik nie ma praw dostępu. Sprawdź następujące elementy:

  • Nieprawidłowa lub błędnie wpisana ścieżka lokalizacji.
  • Ważność klucza SAS. Może to być wygasłe, zawiera literówkę lub zaczyna się od znaku zapytania.
  • Dozwolone uprawnienia klucza sygnatury dostępu współdzielonego. Odczytaj co najmniej wartości i Wyświetl , 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 dla szczegółowych informacji i upewnij się, że dostęp z sieci wirtualnej wystąpienia zarządzanego SQL jest dozwolony.
  • Ruch wychodzący został zablokowany w zarządzanym wystąpieniu SQL przy użyciu polityki punktu końcowego pamięci. Zezwalaj na ruch wychodzący do konta magazynu.
  • Uprawnienia dostępu tożsamości zarządzanej. Upewnij się, że tożsamość zarządzana wystąpienia ma prawa dostępu do konta magazynowego.
  • Poziom zgodności bazy danych musi być 130 lub wyższy, aby zapytania wirtualizacji danych działały.

UTWÓRZ TABELĘ ZEWNĘTRZNĄ NA PODSTAWIE ZAPYTANIA (CETAS)

CREATE EXTERNAL TABLE AS SELECT (CETAS) umożliwia eksportowanie danych z zarządzanego wystąpienia SQL do zewnętrznego konta magazynu. Za pomocą CETAS można utworzyć zewnętrzną tabelę na bazie plików Parquet lub CSV znajdujących się w Azure Blob Storage lub Azure Data Lake Storage (ADLS) Gen2. CETAS może również równolegle eksportować wyniki polecenia T-SQL SELECT do utworzonej tabeli zewnętrznej. Istnieje możliwość wystąpienia ryzyka eksfiltracji danych ze względu na te możliwości, więc usługa Azure SQL Managed Instance domyślnie wyłącza funkcję CETAS. 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ść

  • Last updated on