Wykonywanie zapytań dotyczących danych w usłudze Azure Data Lake przy użyciu usługi Azure Data Explorer
Usługa Azure Data Lake Storage to wysoce skalowalne i ekonomiczne rozwiązanie typu Data Lake do analizy danych big data. Łączy ona możliwości systemu plików o wysokiej wydajności z ogromną skalą i gospodarką, aby ułatwić skrócenie czasu na szczegółowe informacje. Usługa Data Lake Storage 2. generacji rozszerza możliwości usługi Azure Blob Storage i jest zoptymalizowana pod kątem obciążeń analitycznych.
Usługa Azure Data Explorer integruje się z usługami Azure Blob Storage i Azure Data Lake Storage (Gen1 i Gen2), zapewniając szybki, buforowany i indeksowany dostęp do danych przechowywanych w magazynie zewnętrznym. Możesz analizować i wykonywać zapytania dotyczące danych bez wcześniejszego pozyskiwania danych w usłudze Azure Data Explorer. Możesz również wykonywać zapytania dotyczące pozyskanych i nieuzyskowanych danych zewnętrznych jednocześnie. Aby uzyskać więcej informacji, zobacz jak utworzyć tabelę zewnętrzną przy użyciu kreatora internetowego interfejsu użytkownika usługi Azure Data Explorer. Aby zapoznać się z krótkim omówieniem, zobacz tabele zewnętrzne.
Napiwek
Najlepsza wydajność zapytań wymaga pozyskiwania danych w usłudze Azure Data Explorer. Możliwość wykonywania zapytań dotyczących danych zewnętrznych bez wcześniejszego pozyskiwania powinna być używana tylko w przypadku danych historycznych lub danych, które rzadko są odpytywane. Zoptymalizuj wydajność zapytań dotyczących danych zewnętrznych, aby uzyskać najlepsze wyniki.
Tworzenie tabeli zewnętrznej
Załóżmy, że masz wiele plików CSV zawierających informacje historyczne dotyczące produktów przechowywanych w magazynie i chcesz przeprowadzić szybką analizę, aby znaleźć pięć najpopularniejszych produktów z ubiegłego roku. W tym przykładzie pliki CSV wyglądają następująco:
| Sygnatura czasowa | Identyfikator produktu | ProductDescription |
|---|---|---|
| 2019-01-01 11:21:00 | TO6050 | 3.5in DS/HD Floppy Disk |
| 2019-01-01 11:30:55 | YDX1 | Syntezator Yamaha DX1 |
| ... | ... | ... |
Pliki są przechowywane w usłudze Azure Blob Storage mycompanystorage w kontenerze o nazwie archivedproducts, podzielone na partycje według daty:
https://mycompanystorage.blob.core.windows.net/archivedproducts/2019/01/01/part-00000-7e967c99-cf2b-4dbb-8c53-ce388389470d.csv.gz
https://mycompanystorage.blob.core.windows.net/archivedproducts/2019/01/01/part-00001-ba356fa4-f85f-430a-8b5a-afd64f128ca4.csv.gz
https://mycompanystorage.blob.core.windows.net/archivedproducts/2019/01/01/part-00002-acb644dc-2fc6-467c-ab80-d1590b23fc31.csv.gz
https://mycompanystorage.blob.core.windows.net/archivedproducts/2019/01/01/part-00003-cd5fad16-a45e-4f8c-a2d0-5ea5de2f4e02.csv.gz
https://mycompanystorage.blob.core.windows.net/archivedproducts/2019/01/02/part-00000-ffc72d50-ff98-423c-913b-75482ba9ec86.csv.gz
...
Aby bezpośrednio uruchomić zapytanie KQL na tych plikach CSV, użyj .create external table polecenia , aby zdefiniować tabelę zewnętrzną w usłudze Azure Data Explorer. Aby uzyskać więcej informacji na temat opcji poleceń tworzenia tabeli zewnętrznej, zobacz polecenia tabeli zewnętrznej.
.create external table ArchivedProducts(Timestamp:datetime, ProductId:string, ProductDescription:string)
kind=blob
partition by (Date:datetime = bin(Timestamp, 1d))
dataformat=csv
(
h@'https://mycompanystorage.blob.core.windows.net/archivedproducts;StorageSecretKey'
)
Tabela zewnętrzna jest teraz widoczna w lewym okienku internetowego interfejsu użytkownika usługi Azure Data Explorer:
Uprawnienia tabeli zewnętrznej
- Użytkownik bazy danych może utworzyć tabelę zewnętrzną. Twórca tabeli automatycznie staje się administratorem tabeli.
- Administrator klastra, bazy danych lub tabeli może edytować istniejącą tabelę.
- Każdy użytkownik bazy danych lub czytelnik może wykonywać zapytania dotyczące tabeli zewnętrznej.
Wykonywanie zapytań względem tabeli zewnętrznej
Po zdefiniowaniu tabeli zewnętrznej external_table() funkcja może służyć do odwoływania się do niej. Pozostała część zapytania jest standardową język zapytań Kusto.
external_table("ArchivedProducts")
| where Timestamp > ago(365d)
| summarize Count=count() by ProductId,
| top 5 by Count
Wykonywanie zapytań względem danych zewnętrznych i pozyskanych razem
W ramach tego samego zapytania można wykonywać zapytania zarówno względem tabel zewnętrznych, jak i pozyskanych tabel danych. Możesz join lub union tabelę zewnętrzną z innymi danymi z usługi Azure Data Explorer, serwerów SQL lub innych źródeł. Użyj elementu , let( ) statement aby przypisać skróconą nazwę do zewnętrznej dokumentacji tabeli.
W poniższym przykładzie produkty to pozyskana tabela danych, a ArchivedProducts to tabela zewnętrzna, którą zdefiniowaliśmy wcześniej:
let T1 = external_table("ArchivedProducts") | where TimeStamp > ago(100d);
let T = Products; //T is an internal table
T1 | join T on ProductId | take 10
Wykonywanie zapytań względem formatów danych hierarchicznych
Usługa Azure Data Explorer umożliwia wykonywanie zapytań dotyczących formatów hierarchicznych, takich jak JSON, Parquet, Avroi ORC. Aby zamapować schemat danych hierarchicznych na schemat tabeli zewnętrznej (jeśli jest inny), użyj poleceń mapowania tabel zewnętrznych. Jeśli na przykład chcesz wykonywać zapytania dotyczące plików dziennika JSON w następującym formacie:
{
"timestamp": "2019-01-01 10:00:00.238521",
"data": {
"tenant": "e1ef54a6-c6f2-4389-836e-d289b37bcfe0",
"method": "RefreshTableMetadata"
}
}
{
"timestamp": "2019-01-01 10:00:01.845423",
"data": {
"tenant": "9b49d0d7-b3e6-4467-bb35-fa420a25d324",
"method": "GetFileList"
}
}
...
Definicja tabeli zewnętrznej wygląda następująco:
.create external table ApiCalls(Timestamp: datetime, TenantId: guid, MethodName: string)
kind=blob
dataformat=multijson
(
h@'https://storageaccount.blob.core.windows.net/container1;StorageSecretKey'
)
Zdefiniuj mapowanie JSON mapujące pola danych na pola definicji tabeli zewnętrznej:
.create external table ApiCalls json mapping 'MyMapping' '[{"Column":"Timestamp","Properties":{"Path":"$.timestamp"}},{"Column":"TenantId","Properties":{"Path":"$.data.tenant"}},{"Column":"MethodName","Properties":{"Path":"$.data.method"}}]'
Podczas wykonywania zapytań względem tabeli zewnętrznej mapowanie zostanie wywołane, a odpowiednie dane zostaną zamapowane na zewnętrzne kolumny tabeli:
external_table('ApiCalls') | take 10
Aby uzyskać więcej informacji na temat składni mapowania, zobacz mapowania danych.
Wykonywanie zapytań względem tabeli zewnętrznej TaxiRides w klastrze pomocy
Użyj klastra testowego o nazwie pomoc , aby wypróbować różne możliwości usługi Azure Data Explorer. Klaster pomocy zawiera zewnętrzną definicję tabeli dla zestawu danych taksówek w Nowym Jorku zawierającego miliardy przejazdów taksówkami.
Tworzenie tabeli zewnętrznej TaxiRides
W tej sekcji przedstawiono zapytanie użyte do utworzenia tabeli zewnętrznej TaxiRides w klastrze pomocy . Ponieważ ta tabela została już utworzona, możesz pominąć tę sekcję i przejść bezpośrednio do zapytania o dane tabeli zewnętrznej TaxiRides.
.create external table TaxiRides
(
trip_id: long,
vendor_id: string,
pickup_datetime: datetime,
dropoff_datetime: datetime,
store_and_fwd_flag: string,
rate_code_id: int,
pickup_longitude: real,
pickup_latitude: real,
dropoff_longitude: real,
dropoff_latitude: real,
passenger_count: int,
trip_distance: real,
fare_amount: real,
extra: real,
mta_tax: real,
tip_amount: real,
tolls_amount: real,
ehail_fee: real,
improvement_surcharge: real,
total_amount: real,
payment_type: string,
trip_type: int,
pickup: string,
dropoff: string,
cab_type: string,
precipitation: int,
snow_depth: int,
snowfall: int,
max_temperature: int,
min_temperature: int,
average_wind_speed: int,
pickup_nyct2010_gid: int,
pickup_ctlabel: string,
pickup_borocode: int,
pickup_boroname: string,
pickup_ct2010: string,
pickup_boroct2010: string,
pickup_cdeligibil: string,
pickup_ntacode: string,
pickup_ntaname: string,
pickup_puma: string,
dropoff_nyct2010_gid: int,
dropoff_ctlabel: string,
dropoff_borocode: int,
dropoff_boroname: string,
dropoff_ct2010: string,
dropoff_boroct2010: string,
dropoff_cdeligibil: string,
dropoff_ntacode: string,
dropoff_ntaname: string,
dropoff_puma: string
)
kind=blob
partition by (Date:datetime = bin(pickup_datetime, 1d))
dataformat=csv
(
h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)
Utworzoną tabelę TaxiRides można znaleźć, przeglądając lewe okienko internetowego interfejsu użytkownika usługi Azure Data Explorer:
Wykonywanie zapytań o dane tabeli zewnętrznej TaxiRides
Zaloguj się do https://dataexplorer.azure.com/clusters/help/databases/Samples.
Kwerenda TaxiRides tabeli zewnętrznej bez partycjonowania
Uruchom to zapytanie w tabeli zewnętrznej TaxiRides , aby wyświetlić przejazdy dla każdego dnia tygodnia w całym zestawie danych.
external_table("TaxiRides")
| summarize count() by dayofweek(pickup_datetime)
| render columnchart
To zapytanie pokazuje najbardziej ruchliwy dzień tygodnia. Ponieważ dane nie są partycjonowane, zwrócenie wyników zapytania może potrwać do kilku minut.
Kwerenda TaxiRides tabeli zewnętrznej z partycjonowaniem
Uruchom to zapytanie w tabeli zewnętrznej TaxiRides , aby wyświetlić typy taksówki (żółty lub zielony) używane w styczniu 2017 roku.
external_table("TaxiRides")
| where pickup_datetime between (datetime(2017-01-01) .. datetime(2017-02-01))
| summarize count() by cab_type
| render piechart
To zapytanie używa partycjonowania, które optymalizuje czas i wydajność zapytań. Zapytanie filtruje kolumnę partycjonowaną (pickup_datetime) i zwraca wyniki w ciągu kilku sekund.
Możesz napisać inne zapytania do uruchomienia w tabeli zewnętrznej TaxiRides i dowiedzieć się więcej o danych.
Optymalizowanie wydajności zapytań
Zoptymalizuj wydajność zapytań w usłudze Lake, korzystając z poniższych najlepszych rozwiązań dotyczących wykonywania zapytań dotyczących danych zewnętrznych.
Format danych
- Użyj formatu kolumnowego dla zapytań analitycznych z następujących powodów:
- Można odczytać tylko kolumny istotne dla zapytania.
- Techniki kodowania kolumn mogą znacznie zmniejszyć rozmiar danych.
- Usługa Azure Data Explorer obsługuje formaty kolumn Parquet i ORC. Sugerowany jest format Parquet ze względu na zoptymalizowaną implementację.
Region platformy Azure
Sprawdź, czy dane zewnętrzne są w tym samym regionie świadczenia usługi Azure co klaster usługi Azure Data Explorer. Ta konfiguracja zmniejsza koszt i czas pobierania danych.
Rozmiar pliku
Optymalny rozmiar pliku to setki Mb (do 1 GB) na plik. Unikaj wielu małych plików, które wymagają niepotrzebnego obciążenia, takich jak wolniejszy proces wyliczania plików i ograniczone użycie formatu kolumnowego. Liczba plików powinna być większa niż liczba rdzeni procesora CPU w klastrze usługi Azure Data Explorer.
Kompresja
Użyj kompresji, aby zmniejszyć ilość danych pobieranych z magazynu zdalnego. W przypadku formatu Parquet należy użyć wewnętrznego mechanizmu kompresji Parquet, który kompresuje grupy kolumn oddzielnie, umożliwiając ich oddzielne odczytywanie. Aby zweryfikować użycie mechanizmu kompresji, sprawdź, czy pliki są nazwane w następujący sposób: nazwa pliku>.gz.parquet lub <nazwa_>pliku.snappy.parquet, a nie <nazwa pliku>.parquet.gz.<
Partycjonowanie
Organizuj dane przy użyciu partycji "folder", które umożliwiają zapytanie pomijanie nieistotnych ścieżek. Podczas planowania partycjonowania rozważ użycie rozmiaru pliku i typowych filtrów w zapytaniach, takich jak sygnatura czasowa lub identyfikator dzierżawy.
Rozmiar maszyny wirtualnej
Wybierz jednostki SKU maszyn wirtualnych z większą przepływnością rdzeni i wyższą przepływnością sieci (pamięć jest mniej ważna). Aby uzyskać więcej informacji, zobacz Wybieranie odpowiedniej jednostki SKU maszyny wirtualnej dla klastra usługi Azure Data Explorer.