Fråga efter data i Azure Data Lake med Hjälp av Azure Data Explorer

Azure Data Lake Storage är en mycket skalbar och kostnadseffektiv datasjölösning för stordataanalys. Det kombinerar kraften i ett högpresterande filsystem med massiv skala och ekonomi som hjälper dig att minska din tid till insikt. Data Lake Storage Gen2 utökar Azure Blob Storage funktioner och är optimerad för analysarbetsbelastningar.

Azure Data Explorer integreras med Azure Blob Storage och Azure Data Lake Storage (Gen1 och Gen2), vilket ger snabb, cachelagrad och indexerad åtkomst till data som lagras i extern lagring. Du kan analysera och fråga efter data utan föregående inmatning till Azure Data Explorer. Du kan också köra frågor mot inmatade och oestade externa data samtidigt. Mer information finns i hur du skapar en extern tabell med hjälp av azure Data Explorer-guiden för webbgränssnitt. En kort översikt finns i externa tabeller.

Tips

Bästa frågeprestanda kräver datainmatning i Azure Data Explorer. Möjligheten att fråga externa data utan föregående inmatning bör endast användas för historiska data eller data som sällan efterfrågas. Optimera dina externa datafrågeprestanda för bästa resultat.

Skapa en extern tabell

Anta att du har massor av CSV-filer som innehåller historisk information om produkter som lagras i ett lager, och du vill göra en snabb analys för att hitta de fem mest populära produkterna från förra året. I det här exemplet ser CSV-filerna ut så här:

Timestamp	ProductId	ProductDescription
2019-01-01 11:21:00	TO6050	3,5 tum DS/HD-diskett
2019-01-01 11:30:55	YDX1	Yamaha DX1 Synthesizer
...	...	...

Filerna lagras i Azure Blob Storage mycompanystorage under en container med namnet archivedproducts, partitionerad efter datum:

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

Om du vill köra en KQL-fråga direkt på dessa CSV-filer använder du .create external table kommandot för att definiera en extern tabell i Azure Data Explorer. Mer information om kommandoalternativ för att skapa externa tabeller finns i externa tabellkommandon.

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

Den externa tabellen visas nu i den vänstra rutan i Azure Data Explorer-webbgränssnittet:

Behörigheter för extern tabell

• Databasanvändaren kan skapa en extern tabell. Tabellskapare blir automatiskt tabelladministratör.
• Kluster-, databas- eller tabelladministratören kan redigera en befintlig tabell.
• Alla databasanvändare eller läsare kan köra frågor mot en extern tabell.

Köra frågor mot en extern tabell

När en extern tabell har definierats external_table() kan funktionen användas för att referera till den. Resten av frågan är standard Kusto-frågespråk.

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

Köra frågor mot externa och inmatade data tillsammans

Du kan fråga både externa tabeller och inmatade datatabeller i samma fråga. Du kan join eller union den externa tabellen med andra data från Azure Data Explorer, SQL-servrar eller andra källor. Använd a let( ) statement för att tilldela ett kortnamn till en extern tabellreferens.

I exemplet nedan är Produkter en inmatad datatabell och ArchivedProducts är en extern tabell som vi har definierat tidigare:

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

Köra frågor mot hierarkiska dataformat

Med Azure Data Explorer kan du köra frågor mot hierarkiska format, till exempel JSON, Parquet, Avrooch ORC. Om du vill mappa hierarkiskt dataschema till ett externt tabellschema (om det är annorlunda) använder du kommandon för externa tabellmappningar. Om du till exempel vill köra frågor mot JSON-loggfiler med följande format:

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

Definitionen för den externa tabellen ser ut så här:

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

Definiera en JSON-mappning som mappar datafält till externa tabelldefinitionsfält:

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

När du frågar den externa tabellen anropas mappningen och relevanta data mappas till de externa tabellkolumnerna:

external_table('ApiCalls') | take 10

Mer information om mappningssyntax finns i datamappningar.

Fråga den externa tabellen TaxiRides i hjälpklustret

Använd testklustret med namnet hjälp för att prova olika Funktioner i Azure Data Explorer. Hjälpklustret innehåller en extern tabelldefinition för en taxidatauppsättning i New York City som innehåller miljarder taxiresor.

Skapa extern tabell TaxiRides

Det här avsnittet visar frågan som används för att skapa den externa tabellen TaxiRides i hjälpklustret . Eftersom den här tabellen redan har skapats kan du hoppa över det här avsnittet och gå direkt till fråga TaxiRides externa tabelldata.

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

Du hittar tabellen TaxiRides som skapats genom att titta på den vänstra rutan i Webbgränssnittet för Azure Data Explorer:

Köra frågor mot externa tabelldata för TaxiRides

Logga in på https://dataexplorer.azure.com/clusters/help/databases/Samples.

Fråga den externa tabellen TaxiRides utan partitionering

Kör den här frågan i den externa tabellen TaxiRides för att visa turer för varje dag i veckan, över hela datauppsättningen.

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

Den här frågan visar den mest hektiska dagen i veckan. Eftersom data inte är partitionerade kan det ta upp till flera minuter för frågan att returnera resultat.

Köra frågor mot en extern Tabell för TaxiRides med partitionering

Kör den här frågan i den externa tabellen TaxiRides för att visa taxityper (gul eller grön) som användes i januari 2017.

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

Den här frågan använder partitionering, vilket optimerar frågetid och prestanda. Frågan filtrerar på en partitionerad kolumn (pickup_datetime) och returnerar resultat om några sekunder.

Du kan skriva andra frågor för att köra på den externa tabellen TaxiRides och lära dig mer om data.

Optimera frågeprestanda

Optimera frågeprestandan i sjön med hjälp av följande metodtips för att fråga externa data.

Dataformat

• Använd ett kolumnformat för analysfrågor av följande skäl:
  • Endast de kolumner som är relevanta för en fråga kan läsas.
  • Kolumnkodningstekniker kan minska datastorleken avsevärt.
• Azure Data Explorer stöder kolumnformaten Parquet och ORC. Parquet-format föreslås på grund av optimerad implementering.

Azure-region

Kontrollera att externa data finns i samma Azure-region som ditt Azure Data Explorer-kluster. Den här konfigurationen minskar kostnaden och datahämtningstiden.

Filstorlek

Den optimala filstorleken är hundratals Mb (upp till 1 GB) per fil. Undvik många små filer som kräver onödiga omkostnader, till exempel långsammare filuppräkningsprocess och begränsad användning av kolumnformat. Antalet filer ska vara större än antalet CPU-kärnor i azure-Data Explorer-klustret.

Komprimering

Använd komprimering för att minska mängden data som hämtas från fjärrlagringen. För Parquet-format använder du den interna Parquet-komprimeringsmekanismen som komprimerar kolumngrupper separat, så att du kan läsa dem separat. Kontrollera att filerna har följande namn för att verifiera användningen av komprimeringsmekanismen: <filename.gz.parquet> eller <filename.snappy.parquet> och inte <filename.parquet.gz>.

Partitionering

Organisera dina data med hjälp av "mapppartitioner" som gör att frågan kan hoppa över irrelevanta sökvägar. När du planerar partitionering bör du överväga filstorlek och vanliga filter i dina frågor, till exempel tidsstämpel eller klientorganisations-ID.

Storlek på virtuell dator

Välj VM-SKU:er med fler kärnor och högre nätverksdataflöde (minne är mindre viktigt). Mer information finns i Välj rätt VM SKU för ditt Azure Data Explorer-kluster.

Relaterat innehåll

• Självstudie: Lär dig vanliga KQL-operatorer