Dotazování dat v Azure Data Lake pomocí Azure Data Explorer

Azure Data Lake Storage je vysoce škálovatelné a nákladově efektivní řešení Data Lake pro analýzu velkých objemů dat. Kombinuje výkon vysoce výkonného systému souborů s obrovským škálováním a úsporou, aby vám pomohl zkrátit čas na získání přehledu. Data Lake Storage Gen2 rozšiřuje možnosti Azure Blob Storage a je optimalizovaný pro analytické úlohy.

Azure Data Explorer se integruje s Azure Blob Storage a Azure Data Lake Storage (Gen1 a Gen2) a poskytuje rychlý přístup k datům uloženým v externím úložišti v mezipaměti a indexovaný přístup. Data můžete analyzovat a dotazovat bez předchozího příjmu dat do Azure Data Explorer. Můžete se také dotazovat na ingestovaná i neingestovaná externí data současně. Další informace najdete v tématu Vytvoření externí tabulky pomocí průvodce webovým uživatelským rozhraním Azure Data Explorer. Stručný přehled najdete v externích tabulkách.

Tip

Nejlepší výkon dotazů vyžaduje příjem dat do Azure Data Explorer. Schopnost dotazovat se na externí data bez předchozího příjmu dat by se měla používat pouze pro historická data nebo data, která se dotazují zřídka. Optimalizujte výkon externích dotazů na data pro zajištění nejlepších výsledků.

Vytvoření externí tabulky

Řekněme, že máte velké množství souborů CSV obsahujících historické informace o produktech uložených ve skladu a chcete provést rychlou analýzu, abyste našli pět nejoblíbenějších produktů z minulého roku. V tomto příkladu vypadají soubory CSV takto:

Timestamp	ProductId	ProductDescription
2019-01-01 11:21:00	TO6050	3.5in DS/HD Disketa
2019-01-01 11:30:55	YDX1	Yamaha DX1 Syntezátor
...	...	...

Soubory se ukládají v Úložišti objektů blob mycompanystorage v Azure v kontejneru s názvem archivedproducts, rozdělené podle data:

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

Pokud chcete přímo spustit dotaz KQL na tyto soubory CSV, pomocí .create external table příkazu definujte externí tabulku v Azure Data Explorer. Další informace o možnostech příkazu pro vytvoření externí tabulky najdete v tématu Příkazy externí tabulky.

.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'
)

Externí tabulka je teď viditelná v levém podokně webového uživatelského rozhraní Azure Data Explorer:

Oprávnění k externí tabulce

• Uživatel databáze může vytvořit externí tabulku. Tvůrce tabulky se automaticky stane správcem tabulky.
• Správce clusteru, databáze nebo tabulky může upravit existující tabulku.
• Na externí tabulku se může dotazovat libovolný uživatel databáze nebo čtenář.

Dotazování externí tabulky

Jakmile je externí tabulka definovaná, external_table() můžete na ni odkazovat pomocí funkce. Zbytek dotazu je standardní dotazovací jazyk Kusto.

external_table("ArchivedProducts")
| where Timestamp > ago(365d)
| summarize Count=count() by ProductId,
| top 5 by Count

Společné dotazování externích a přijatých dat

V rámci stejného dotazu můžete dotazovat externí i ingestované tabulky dat. Můžete join použít externí tabulku s union dalšími daty z Azure Data Explorer, SQL serverů nebo jiných zdrojů. Použijte k let( ) statement přiřazení zkráceného názvu k odkazu na externí tabulku.

V následujícím příkladu je Products tabulka ingestovaných dat a ArchivedProducts je externí tabulka, kterou jsme definovali dříve:

let T1 = external_table("ArchivedProducts") |  where TimeStamp > ago(100d);
let T = Products; //T is an internal table
T1 | join T on ProductId | take 10

Dotazování hierarchických datových formátů

Azure Data Explorer umožňuje dotazování hierarchických formátů, jako JSONjsou , ParquetAvro, a ORC. Pokud chcete mapovat hierarchické datové schéma na schéma externí tabulky (pokud se liší), použijte příkazy mapování externích tabulek. Například pokud chcete dotazovat soubory protokolu JSON v následujícím formátu:

{
  "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"
  }
}
...

Definice externí tabulky vypadá takto:

.create external table ApiCalls(Timestamp: datetime, TenantId: guid, MethodName: string)
kind=blob
dataformat=multijson
(
   h@'https://storageaccount.blob.core.windows.net/container1;StorageSecretKey'
)

Definujte mapování JSON, které mapuje datová pole na pole definice externí tabulky:

.create external table ApiCalls json mapping 'MyMapping' '[{"Column":"Timestamp","Properties":{"Path":"$.timestamp"}},{"Column":"TenantId","Properties":{"Path":"$.data.tenant"}},{"Column":"MethodName","Properties":{"Path":"$.data.method"}}]'

Při dotazování externí tabulky se vyvolá mapování a relevantní data se namapují na sloupce externí tabulky:

external_table('ApiCalls') | take 10

Další informace o syntaxi mapování najdete v tématu Mapování dat.

Dotazování na externí tabulku TaxiRides v clusteru nápovědy

K vyzkoušení různých možností azure Data Explorer použijte testovací cluster s názvem nápověda. Cluster nápovědy obsahuje definici externí tabulky pro datovou sadu taxi v New Yorku obsahující miliardy jízd taxíkem.

Vytvoření externí tabulky TaxiRides

Tato část ukazuje dotaz použitý k vytvoření externí tabulky TaxiRides v clusteru nápovědy . Vzhledem k tomu, že tato tabulka už byla vytvořena, můžete tuto část přeskočit a přejít přímo k dotazování dat externí tabulky 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'
)

Vytvořenou tabulku TaxiRides najdete v levém podokně webového uživatelského rozhraní Azure Data Explorer:

Dotazování dat externí tabulky TaxiRides

Přihlaste se k webu https://dataexplorer.azure.com/clusters/help/databases/Samples.

Dotazování externí tabulky TaxiRides bez dělení

Spuštěním tohoto dotazu v externí tabulce TaxiRides zobrazte jízdy pro každý den v týdnu v celé datové sadě.

external_table("TaxiRides")
| summarize count() by dayofweek(pickup_datetime)
| render columnchart

Tento dotaz ukazuje nejrušnější den v týdnu. Vzhledem k tomu, že data nejsou rozdělená na oddíly, může vrácení výsledků dotazu trvat až několik minut.

Dotazování na externí tabulku TaxiRides s dělením

Spuštěním tohoto dotazu v externí tabulce TaxiRides zobrazte typy taxislužby (žluté nebo zelené) použité v lednu 2017.

external_table("TaxiRides")
| where pickup_datetime between (datetime(2017-01-01) .. datetime(2017-02-01))
| summarize count() by cab_type
| render piechart

Tento dotaz používá dělení, které optimalizuje čas a výkon dotazu. Dotaz filtruje dělený sloupec (pickup_datetime) a vrátí výsledky během několika sekund.

Můžete psát další dotazy, které se mají spustit na externí tabulce TaxiRides , a získat další informace o datech.

Optimalizace výkonu dotazů

Optimalizujte výkon dotazů v jezeře pomocí následujících osvědčených postupů pro dotazování externích dat.

Formát dat

• Pro analytické dotazy použijte sloupcový formát, a to z následujících důvodů:
  • Číst je možné pouze sloupce, které jsou pro dotaz relevantní.
  • Techniky kódování sloupců můžou výrazně zmenšit velikost dat.
• Azure Data Explorer podporuje sloupcové formáty Parquet a ORC. Formát Parquet se navrhuje kvůli optimalizované implementaci.

Oblast Azure

Zkontrolujte, že se externí data nachází ve stejné oblasti Azure jako váš cluster Azure Data Explorer. Toto nastavení snižuje náklady a dobu načítání dat.

Velikost souboru

Optimální velikost souboru jsou stovky Mb (až 1 GB) na soubor. Vyhněte se mnoha malým souborům, které vyžadují nepotřebnou režii, například pomalejší proces výčtu souborů a omezené použití sloupcového formátu. Počet souborů by měl být větší než počet jader procesoru v clusteru Azure Data Explorer.

Komprese

Pomocí komprese snižte množství dat načítaných ze vzdáleného úložiště. Pro formát Parquet použijte interní kompresní mechanismus Parquet, který komprimuje skupiny sloupců samostatně a umožňuje je číst samostatně. Pokud chcete ověřit použití kompresního mechanismu, zkontrolujte, jestli jsou soubory pojmenované takto: <filename.gz.parquet> nebo <filename.snappy.parquet>, a ne <název_souboru.parquet.gz>.

Dělení

Uspořádejte data pomocí oddílů složky, které umožňují dotazu přeskočit irelevantní cesty. Při plánování dělení zvažte velikost souboru a běžné filtry v dotazech, jako je časové razítko nebo ID tenanta.

Velikost virtuálního počítače

Vyberte skladové položky virtuálních počítačů s více jádry a vyšší propustností sítě (paměť je méně důležitá). Další informace najdete v tématu Výběr správné skladové položky virtuálního počítače pro cluster Azure Data Explorer.

Související obsah

• Kurz: Seznámení s běžnými operátory KQL