Najlepsze rozwiązania dotyczące bezserwerowych pul SQL w usłudze Azure Synapse Analytics

W tym artykule znajdziesz kolekcję najlepszych rozwiązań dotyczących korzystania z bezserwerowej puli SQL. Bezserwerowa pula SQL to zasób w usłudze Azure Synapse Analytics. Jeśli pracujesz z dedykowaną pulą SQL, zobacz Najlepsze rozwiązania dotyczące dedykowanych pul SQL, aby uzyskać szczegółowe wskazówki.

Bezserwerowa pula SQL umożliwia wykonywanie zapytań dotyczących plików na kontach usługi Azure Storage. Nie ma możliwości magazynu lokalnego ani pozyskiwania. Wszystkie pliki docelowe zapytań są zewnętrzne dla bezserwerowej puli SQL. Wszystko związane z odczytywaniem plików z magazynu może mieć wpływ na wydajność zapytań.

Oto niektóre ogólne wytyczne:

• Upewnij się, że aplikacje klienckie są sortowane z bezserwerową pulą SQL.
  • Jeśli używasz aplikacji klienckich spoza platformy Azure, upewnij się, że używasz bezserwerowej puli SQL w regionie zbliżonym do komputera klienckiego. Przykłady aplikacji klienckich obejmują program Power BI Desktop, program SQL Server Management Studio i program Azure Data Studio.
• Upewnij się, że magazyn i bezserwerowa pula SQL znajdują się w tym samym regionie. Przykłady magazynu obejmują usługi Azure Data Lake Storage i Azure Cosmos DB.
• Spróbuj zoptymalizować układ magazynu przy użyciu partycjonowania i przechowywania plików w zakresie od 100 MB do 10 GB.
• Jeśli zwracasz dużą liczbę wyników, upewnij się, że używasz programu SQL Server Management Studio lub Azure Data Studio, a nie usługi Azure Synapse Studio. Azure Synapse Studio to narzędzie internetowe, które nie jest przeznaczone dla dużych zestawów wyników.
• Jeśli filtrujesz wyniki według kolumny ciągu, spróbuj użyć sortowania BIN2_UTF8 . Aby uzyskać więcej informacji na temat zmieniania sortowania, zobacz Typy sortowania obsługiwane dla usługi Synapse SQL.
• Rozważ buforowanie wyników po stronie klienta przy użyciu trybu importowania usługi Power BI lub usług Azure Analysis Services i okresowego odświeżania. Bezserwerowe pule SQL nie mogą zapewnić interaktywnego środowiska w trybie direct query usługi Power BI, jeśli używasz złożonych zapytań lub przetwarzasz dużą ilość danych.
• Maksymalna współbieżność nie jest ograniczona i zależy od złożoności zapytania i ilości skanowanych danych. Jedna bezserwerowa pula SQL może jednocześnie obsługiwać 1000 aktywnych sesji, które wykonują lekkie zapytania. Liczby spadną, jeśli zapytania są bardziej złożone lub skanują większą ilość danych, więc w takim przypadku rozważ zmniejszenie współbieżności i wykonywanie zapytań w dłuższym okresie, jeśli jest to możliwe.

Aplikacje klienckie i połączenia sieciowe

Upewnij się, że aplikacja kliencka jest połączona z najbliższym możliwym obszarem roboczym usługi Azure Synapse z optymalnym połączeniem.

• Kolokuj aplikację kliencką za pomocą obszaru roboczego usługi Azure Synapse. Jeśli używasz aplikacji, takich jak Power BI lub Azure Analysis Service, upewnij się, że znajdują się one w tym samym regionie, w którym umieszczono obszar roboczy usługi Azure Synapse. W razie potrzeby utwórz oddzielne obszary robocze sparowane z aplikacjami klienckimi. Umieszczenie aplikacji klienckiej i obszaru roboczego usługi Azure Synapse w różnych regionach może spowodować większe opóźnienie i wolniejsze przesyłanie strumieniowe wyników.
• Jeśli odczytujesz dane z aplikacji lokalnej, upewnij się, że obszar roboczy usługi Azure Synapse znajduje się w regionie, który znajduje się blisko twojej lokalizacji.
• Upewnij się, że podczas odczytywania dużej ilości danych nie występują problemy z przepustowością sieci.
• Nie używaj usługi Azure Synapse Studio do zwracania dużej ilości danych. Azure Synapse Studio to narzędzie internetowe, które używa protokołu HTTPS do transferu danych. Odczytywanie dużej ilości danych przy użyciu programu Azure Data Studio lub SQL Server Management Studio.

Magazyn i układ zawartości

Poniżej przedstawiono najlepsze rozwiązania dotyczące magazynu i układu zawartości w bezserwerowej puli SQL.

Kolokowanie magazynu i bezserwerowej puli SQL

Aby zminimalizować opóźnienie, kolokuj konto usługi Azure Storage lub magazyn analityczny usługi Azure Cosmos DB i punkt końcowy bezserwerowej puli SQL. Konta magazynu i punkty końcowe aprowidowane podczas tworzenia obszaru roboczego znajdują się w tym samym regionie.

Aby uzyskać optymalną wydajność, jeśli uzyskujesz dostęp do innych kont magazynu z bezserwerową pulą SQL, upewnij się, że są one w tym samym regionie. Jeśli nie znajdują się one w tym samym regionie, zostanie zwiększone opóźnienie transferu sieciowego danych między regionem zdalnym a regionem punktu końcowego.

Ograniczanie przepustowości usługi Azure Storage

Wiele aplikacji i usług może uzyskiwać dostęp do konta magazynu. Ograniczanie przepustowości magazynu występuje, gdy łączna liczba operacji we/wy na sekundę lub przepływność wygenerowana przez aplikacje, usługi i bezserwerowe obciążenia puli SQL przekraczają limity konta magazynu. W rezultacie będziesz mieć znaczący negatywny wpływ na wydajność zapytań.

Po wykryciu ograniczania bezserwerowa pula SQL ma wbudowaną obsługę, aby ją rozwiązać. Bezserwerowa pula SQL wysyła żądania do magazynu w wolniejszym tempie, dopóki ograniczanie nie zostanie rozwiązane.

Przygotowywanie plików do wykonywania zapytań

Jeśli to możliwe, możesz przygotować pliki pod kątem lepszej wydajności:

• Przekonwertuj duże pliki CSV i JSON na Parquet. Parquet to format kolumnowy. Ponieważ jest skompresowany, rozmiary plików są mniejsze niż pliki CSV lub JSON, które zawierają te same dane. Bezserwerowa pula SQL pomija kolumny i wiersze, które nie są potrzebne w zapytaniu, jeśli czytasz pliki Parquet. Bezserwerowa pula SQL potrzebuje mniej czasu i mniej żądań magazynowania, aby je odczytać.
• Jeśli zapytanie dotyczy pojedynczego dużego pliku, możesz podzielić go na wiele mniejszych plików.
• Spróbuj zachować rozmiar pliku CSV z zakresu od 100 MB do 10 GB.
• Lepiej mieć pliki o takim samym rozmiarze dla pojedynczej ścieżki OPENROWSET lub lokalizacji tabeli zewnętrznej.
• Partycjonowanie danych przez przechowywanie partycji w różnych folderach lub nazwach plików. Zobacz Używanie funkcji nazwy pliku i ścieżki plików do określania docelowych partycji.

Kolokowanie magazynu analitycznego usługi Azure Cosmos DB i bezserwerowej puli SQL

Upewnij się, że magazyn analityczny usługi Azure Cosmos DB znajduje się w tym samym regionie co obszar roboczy usługi Azure Synapse. Zapytania obejmujące wiele regionów mogą powodować ogromne opóźnienia. Użyj właściwości region w parametry połączenia, aby jawnie określić region, w którym znajduje się magazyn analityczny (zobacz Wykonywanie zapytań względem usługi Azure Cosmos DB przy użyciu bezserwerowej puli SQL):account=<database account name>;database=<database name>;region=<region name>'

Optymalizacje woluminów CSV

Poniżej przedstawiono najlepsze rozwiązania dotyczące używania plików CSV w bezserwerowej puli SQL.

Wykonywanie zapytań dotyczących plików CSV przy użyciu PARSER_VERSION 2.0

Podczas wykonywania zapytań dotyczących plików CSV można użyć analizatora zoptymalizowanego pod kątem wydajności. Aby uzyskać szczegółowe informacje, zobacz PARSER_VERSION.

Ręczne tworzenie statystyk dla plików CSV

Bezserwerowa pula SQL opiera się na statystykach w celu generowania optymalnych planów wykonywania zapytań. Statystyki są tworzone automatycznie dla kolumn przy użyciu próbkowania, a w większości przypadków wartość procentowa próbkowania będzie mniejsza niż 100%. Ten przepływ jest taki sam dla każdego formatu pliku. Należy pamiętać, że podczas odczytywania pliku CSV z próbkowaniem analizatora w wersji 1.0 nie jest obsługiwane, a automatyczne tworzenie statystyk nie będzie miało miejsce z wartością procentową próbkowania mniejszą niż 100%. W przypadku małych tabel z szacowaną niską kardynalnością (liczba wierszy) automatyczne tworzenie statystyk zostanie wyzwolone z wartością procentową próbkowania 100%. Oznacza to, że funkcja fullscan jest wyzwalana, a automatyczne statystyki są tworzone nawet dla woluminów CSV z analizatorem w wersji 1.0. Jeśli statystyki nie są tworzone automatycznie, utwórz statystyki ręcznie dla kolumn używanych w zapytaniach, szczególnie tych używanych w funkcjach DISTINCT, JOIN, WHERE, ORDER BY i GROUP BY. Sprawdź statystyki w bezserwerowej puli SQL, aby uzyskać szczegółowe informacje.

Typy danych

Poniżej przedstawiono najlepsze rozwiązania dotyczące używania typów danych w bezserwerowej puli SQL.

Używanie odpowiednich typów danych

Typy danych używane w zapytaniu wpływają na wydajność i współbieżność. Jeśli przestrzegasz następujących wytycznych, możesz uzyskać lepszą wydajność:

• Użyj najmniejszego rozmiaru danych, który może pomieścić największą możliwą wartość.
  • Jeśli maksymalna długość wartości znaku wynosi 30 znaków, użyj typu danych znaków o długości 30.
  • Jeśli wszystkie wartości kolumn znaków mają stały rozmiar, użyj znaku lub nchar. W przeciwnym razie użyj wartości varchar lub nvarchar.
  • Jeśli maksymalna wartość kolumny całkowitej wynosi 500, użyj wartości smallint , ponieważ jest to najmniejszy typ danych, który może pomieścić tę wartość. Aby uzyskać więcej informacji, zobacz zakresy typów danych liczb całkowitych.
• Jeśli to możliwe, użyj wartości varchar i char zamiast nvarchar i nchar.
  • Użyj typu varchar z pewnym sortowaniem UTF8, jeśli odczytujesz dane z plików Parquet, Azure Cosmos DB, Delta Lake lub CSV z kodowaniem UTF-8.
  • Użyj typu varchar bez sortowania UTF8, jeśli odczytujesz dane z plików CSV innych niż Unicode (na przykład ASCII).
  • Użyj typu nvarchar, jeśli odczytujesz dane z pliku CSV UTF-16.
• Jeśli to możliwe, użyj typów danych opartych na liczbach całkowitych. Operacje SORT, JOIN i GROUP BY są wykonywane szybciej na liczbach całkowitych niż w danych znaków.
• Jeśli używasz wnioskowania schematu, sprawdź typy danych wywnioskowanych i zastąp je jawnie mniejszymi typami, jeśli jest to możliwe.

Sprawdzanie wywnioskowanych typów danych

Wnioskowanie schematu ułatwia szybkie pisanie zapytań i eksplorowanie danych bez znajomości schematów plików. Koszt tej wygody polega na tym, że wnioskowane typy danych mogą być większe niż rzeczywiste typy danych. Ta rozbieżność występuje, gdy w plikach źródłowych nie ma wystarczającej ilości informacji, aby upewnić się, że jest używany odpowiedni typ danych. Na przykład pliki Parquet nie zawierają metadanych dotyczących maksymalnej długości kolumny znaków. Dlatego bezserwerowa pula SQL wywnioskuje ją jako varchar(8000).

Należy pamiętać, że sytuacja może się różnić w przypadku współużytkowanych tabel zarządzanych i zewnętrznych platform Spark uwidocznionych w a engine SQL jako tabel zewnętrznych. Tabele platformy Spark udostępniają różne typy danych niż aparaty SQL usługi Synapse. Mapowanie między typami danych tabeli platformy Spark i typami SQL można znaleźć tutaj.

Aby sprawdzić wynikowe typy danych zapytania, można użyć procedury składowanej systemu sp_describe_first_results_set .

W poniższym przykładzie pokazano, jak zoptymalizować wnioskowane typy danych. Ta procedura służy do pokazywania wywnioskowanych typów danych:

EXEC sp_describe_first_result_set N'
    SELECT
        vendor_id, pickup_datetime, passenger_count
    FROM  
        OPENROWSET(
            BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/*/*/*'',
            FORMAT=''PARQUET''
        ) AS nyc';

Oto zestaw wyników:

Po zapoznaniu się z wywnioskowanym typem danych dla zapytania można określić odpowiednie typy danych:

SELECT
    vendorID, tpepPickupDateTime, passengerCount
FROM  
    OPENROWSET(
        BULK 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/puYear=2018/puMonth=*/*.snappy.parquet',
        FORMAT='PARQUET'
    )  
    WITH (
        vendorID varchar(4), -- we used length of 4 instead of the inferred 8000
        tpepPickupDateTime datetime2,
        passengerCount int
    ) AS nyc;

Optymalizacja filtrów

Poniżej przedstawiono najlepsze rozwiązania dotyczące używania zapytań w bezserwerowej puli SQL.

Wypychanie symboli wieloznacznych do niższych poziomów w ścieżce

Możesz użyć symboli wieloznacznych w ścieżce do wykonywania zapytań dotyczących wielu plików i folderów. Bezserwerowa pula SQL wyświetla pliki na koncie magazynu, począwszy od pierwszej gwiazdki (*), przy użyciu interfejsu API magazynu. Pozwala to wyeliminować pliki, które nie pasują do określonej ścieżki. Zmniejszenie początkowej listy plików może zwiększyć wydajność, jeśli istnieje wiele plików pasujących do określonej ścieżki aż do pierwszego symbolu wieloznacznego.

Używanie funkcji nazwy pliku i ścieżki plików do określania lokalizacji docelowej określonych partycji

Dane są często zorganizowane w partycjach. Możesz poinstruować bezserwerową pulę SQL, aby wysyłała zapytania o określone foldery i pliki. Dzięki temu zmniejsza liczbę plików i ilość danych, które zapytanie musi odczytywać i przetwarzać. Dodatkową premią jest to, że uzyskasz lepszą wydajność.

Aby uzyskać więcej informacji, przeczytaj o nazwach plików i funkcjach filepath i zobacz przykłady wykonywania zapytań dotyczących określonych plików.

Funkcje używane do eliminacji partycji, ścieżki plików i nazwy pliku nie są obecnie obsługiwane w przypadku tabel zewnętrznych, innych niż te utworzone automatycznie dla każdej tabeli utworzonej w usłudze Apache Spark dla usługi Azure Synapse Analytics.

Jeśli przechowywane dane nie są partycjonowane, rozważ podzielenie ich na partycje. Dzięki temu można użyć tych funkcji do optymalizacji zapytań przeznaczonych dla tych plików. Podczas wykonywania zapytań dotyczących partycjonowanych tabel platformy Apache Spark dla usługi Azure Synapse z bezserwerowej puli SQL zapytanie automatycznie jest przeznaczone tylko dla niezbędnych plików.

Użyj odpowiedniego sortowania, aby użyć wypychania predykatu dla kolumn znaków

Dane w pliku Parquet są zorganizowane w grupach wierszy. Bezserwerowa pula SQL pomija grupy wierszy na podstawie określonego predykatu w klauzuli WHERE, co zmniejsza liczbę operacji we/wy. W rezultacie zwiększa się wydajność zapytań.

Wypychanie predykatu dla kolumn znaków w plikach Parquet jest obsługiwane tylko w przypadku sortowania Latin1_General_100_BIN2_UTF8. Sortowanie dla określonej kolumny można określić przy użyciu klauzuli WITH. Jeśli nie określisz tego sortowania przy użyciu klauzuli WITH, będzie używane sortowanie bazy danych.

Optymalizowanie powtarzających się zapytań

Poniżej przedstawiono najlepsze rozwiązania dotyczące używania instrukcji CETAS w bezserwerowej puli SQL.

Używanie instrukcji CETAS w celu zwiększenia wydajności zapytań i sprzężeń

CETAS to jedna z najważniejszych funkcji dostępnych w bezserwerowej puli SQL. CETAS to równoległa operacja, która tworzy metadane tabeli zewnętrznej i eksportuje wyniki zapytania SELECT do zestawu plików na koncie magazynu.

Instrukcje CETAS umożliwiają zmaterializowanie często używanych części zapytań, takich jak połączone tabele odwołań, do nowego zestawu plików. Następnie można połączyć się z tą pojedynczą tabelą zewnętrzną zamiast powtarzać typowe sprzężenia w wielu zapytaniach.

Ponieważ cetAS generuje pliki Parquet, statystyki są tworzone automatycznie, gdy pierwsze zapytanie dotyczy tej tabeli zewnętrznej. Wynik jest lepszy w przypadku kolejnych zapytań przeznaczonych dla tabeli docelowej wygenerowanej przy użyciu instrukcji CETAS.

Wykonywanie zapytań dotyczących danych platformy Azure

Bezserwerowe pule SQL umożliwiają wykonywanie zapytań dotyczących danych w usłudze Azure Storage lub Azure Cosmos DB przy użyciu tabel zewnętrznych i funkcji OPENROWSET. Upewnij się, że masz odpowiednie uprawnienia skonfigurowane w magazynie.

Wykonywanie zapytań dotyczących danych CSV

Dowiedz się, jak wykonywać zapytania dotyczące pojedynczego pliku CSV lub folderów i wielu plików CSV. Możesz również wykonywać zapytania dotyczące partycjonowanych plików

Wykonywanie zapytań względem danych Parquet

Dowiedz się, jak wykonywać zapytania dotyczące plików Parquet za pomocą zagnieżdżonych typów. Można również wykonywać zapytania dotyczące partycjonowanych plików.

Wykonywanie zapytań w usłudze Delta Lake

Dowiedz się, jak wykonywać zapytania dotyczące plików usługi Delta Lake przy użyciu typów zagnieżdżonych.

Wykonywanie zapytań względem danych usługi Azure Cosmos DB

Dowiedz się, jak wykonywać zapytania dotyczące magazynu analitycznego usługi Azure Cosmos DB. Generator online umożliwia wygenerowanie klauzuli WITH na podstawie przykładowego dokumentu usługi Azure Cosmos DB. Widoki można tworzyć na podstawie kontenerów usługi Azure Cosmos DB.

Wykonywanie zapytań dotyczących danych JSON

Dowiedz się, jak wykonywać zapytania dotyczące plików JSON. Można również wykonywać zapytania dotyczące partycjonowanych plików.

Tworzenie widoków, tabel i innych obiektów bazy danych

Dowiedz się, jak tworzyć widoki i tabele zewnętrzne oraz konfigurować zabezpieczenia na poziomie wiersza. Jeśli masz partycjonowane pliki, upewnij się, że używasz widoków podzielonych na partycje.

Kopiowanie i przekształcanie danych (CETAS)

Dowiedz się, jak przechowywać wyniki zapytań w magazynie przy użyciu polecenia CETAS.

Następne kroki

• Zapoznaj się z artykułem dotyczącym rozwiązywania problemów z bezserwerowych pul SQL, aby uzyskać rozwiązania typowych problemów.
• Jeśli pracujesz z dedykowaną pulą SQL, a nie bezserwerową pulą SQL, zobacz Najlepsze rozwiązania dotyczące dedykowanych pul SQL, aby uzyskać szczegółowe wskazówki.
• Azure Synapse Analytics — często zadawane pytania
• Udzielanie uprawnień do tożsamość zarządzanej przez obszar roboczy