Query's uitvoeren op gegevens in Azure Data Lake met Azure Data Explorer

Azure Data Lake Storage is een zeer schaalbare en rendabele data lake voor big data analyse. Het combineert de kracht van een bestandssysteem met hoge prestaties met grote schaal en economie om u te helpen uw inzichttijd te verminderen. Data Lake Storage Gen2 breidt de mogelijkheden van Azure Blob Storage uit en is geoptimaliseerd voor analyseworkloads.

Azure Data Explorer kan worden geïntegreerd met Azure Blob Storage en Azure Data Lake Storage (Gen1 en Gen2). Dit biedt snelle, in cache opgeslagen en geïndexeerde toegang tot gegevens die zijn opgeslagen in externe opslag. U kunt gegevens analyseren en er query's op uitvoeren zonder voorafgaande opname in Azure Data Explorer. U kunt ook tegelijkertijd query's uitvoeren op opgenomen en niet-opgenomen externe gegevens.

Een externe tabel maken

Stel dat u veel CSV-bestanden hebt met historische informatie over producten die zijn opgeslagen in een magazijn en u een snelle analyse wilt uitvoeren om de vijf populairste producten van het afgelopen jaar te vinden. In dit voorbeeld zien de CSV-bestanden er als volgende uit:

De bestanden worden opgeslagen in Azure Blob Storage mycompanystorage onder een container met de naam , archivedproducts gepartities op 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
...

Als u rechtstreeks een KQL-query wilt uitvoeren op deze CSV-bestanden, gebruikt u de opdracht om een externe tabel in de .create external table Azure Data Explorer. Zie externe tabelopdrachten voor meer informatie over opdrachtopties voor het maken van externe tabel.

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

De externe tabel is nu zichtbaar in het linkerdeelvenster van de webinterface:

Machtigingen voor externe tabel

• De databasegebruiker kan een externe tabel maken. De tabelmaker wordt automatisch de tabelbeheerder.
• De cluster-, database- of tabelbeheerder kan een bestaande tabel bewerken.
• Elke databasegebruiker of lezer kan een query uitvoeren op een externe tabel.

Query's uitvoeren op een externe tabel

Zodra een externe tabel is gedefinieerd, kan external_table() de functie worden gebruikt om er naar te verwijzen. De rest van de query is de standaard Kusto-querytaal.

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

Query's uitvoeren op externe en opgenomen gegevens samen

U kunt zowel externe tabellen als opgenomen gegevenstabellen in dezelfde query opvragen. U kunt join of de externe tabel met aanvullende gegevens van union Azure Data Explorer, SQL servers of andere bronnen. Gebruik een let( ) statement om een verkorte naam toe te wijzen aan een externe tabelverwijzing.

In het onderstaande voorbeeld is Products een opgenomen gegevenstabel en ArchivedProducts een externe tabel die we eerder hebben gedefinieerd:

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

Query's uitvoeren op hiërarchische gegevensindelingen

Azure Data Explorer kunt query's uitvoeren op hiërarchische indelingen, zoals JSONParquet , , en AvroORC . Als u een hiërarchisch gegevensschema wilt toewijzen aan een extern tabelschema (als dit anders is), gebruikt u opdrachten voor externe tabeltoewijzingen. Bijvoorbeeld als u een query wilt uitvoeren op JSON-logboekbestanden met de volgende indeling:

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

De definitie van de externe tabel ziet er als volgende uit:

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

Definieer een JSON-toewijzing die gegevensvelden toewijst aan externe tabeldefinitievelden:

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

Wanneer u een query uitvoert op de externe tabel, wordt de toewijzing aangeroepen en worden relevante gegevens toegewezen aan de externe tabelkolommen:

external_table('ApiCalls') | take 10

Zie gegevenstoewijzingen voor meer informatie over toewijzingssyntaxis.

Query uitvoeren op TaxiRides externe tabel in het Help-cluster

Gebruik het testcluster help om verschillende mogelijkheden Azure Data Explorer proberen. Het Help-cluster bevat een definitie van een externe tabel voor een gegevensset met taxi's in New York city met miljarden taxiritten.

Externe tabel TaxiRides maken

In deze sectie ziet u de query die wordt gebruikt voor het maken van de externe tabel TaxiRides in het Help-cluster. Omdat deze tabel al is gemaakt, kunt u deze sectie overslaan en rechtstreeks naar de query TaxiRides external table data gaan.

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

U kunt de gemaakte tabel TaxiRides vinden door te kijken naar het linkerdeelvenster van de webinterface:

Query TaxiRides external table data

Meld u aan bij https://dataexplorer.azure.com/clusters/help/databases/Samples.

Query's uitvoeren op taxi's Externe tabel zonder partitioneren

Voer deze query uit op de externe tabel TaxiRides om ritten weer te geven voor elke dag van de week, in de hele gegevensset.

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

Deze query toont de drukste dag van de week. Omdat de gegevens niet zijn gepart partitioneerd, kan het enkele minuten duren voordat de query resultaten retourneerde.

Query uitvoeren op TaxiRides externe tabel met partitionering

Voer deze query uit op de externe tabel TaxiRides om taxi taxitypen (geel of groen) weer te geven die in januari 2017 zijn gebruikt.

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

Deze query maakt gebruik van partitionering, waardoor de querytijd en prestaties worden geoptimaliseerd. De query filtert op een gepart partitioneerde kolom (pickup_datetime) en retourneert resultaten over een paar seconden.

U kunt extra query's schrijven om uit te voeren op de externe tabel TaxiRides en meer informatie over de gegevens.

Uw queryprestaties optimaliseren

Optimaliseer de queryprestaties in de lake met behulp van de volgende best practices voor het uitvoeren van query's op externe gegevens.

Gegevensindeling

• Gebruik om de volgende redenen een kolomindeling voor analytische query's:
  • Alleen de kolommen die relevant zijn voor een query kunnen worden gelezen.
  • Technieken voor kolomcoderen kunnen de gegevensgrootte aanzienlijk verminderen.
• Azure Data Explorer ondersteunt parquet- en ORC-kolomindelingen. De Parquet-indeling wordt aanbevolen vanwege een geoptimaliseerde implementatie.

Azure-regio

Controleer of externe gegevens zich in dezelfde Azure-regio als uw Azure Data Explorer cluster. Deze installatie vermindert de kosten en de tijd voor het ophalen van gegevens.

Bestandsgrootte

De optimale bestandsgrootte is honderden Mb (maximaal 1 GB) per bestand. Vermijd veel kleine bestanden waarvoor onnodige overhead is vereist, zoals een langzamer bestandsinsoratieproces en beperkt gebruik van de kolomindeling. Het aantal bestanden moet groter zijn dan het aantal CPU-kernen in uw Azure Data Explorer cluster.

Compressie

Gebruik compressie om de hoeveelheid gegevens te verminderen die uit de externe opslag worden opgehaald. Gebruik voor parquet-indeling het interne Parquet-compressiemechanisme dat kolomgroepen afzonderlijk comprimeert, zodat u ze afzonderlijk kunt lezen. Als u het gebruik van het compressiemechanisme wilt valideren, controleert u of de bestanden de volgende naam hebben: > .gz.parquet of > en niet de bestandsnaam <

Partitionering

Organiseer uw gegevens met behulp van mappartities waarmee de query irrelevante paden kan overslaan. Overweeg bij het plannen van partitionering de bestandsgrootte en algemene filters in uw query's, zoals tijdstempel of tenant-id.

VM-grootte

Selecteer VM-SKU's met meer kernen en hogere netwerkdoorvoer (geheugen is minder belangrijk). Zie Select the correct VM SKU for your Azure Data Explorer cluster (De juiste VM-SKU voor uw cluster Azure Data Explorer selecteren) voor meer informatie.

Volgende stappen

• Query's uitvoeren op uw gegevens in Azure Data Lake met behulp Azure Data Explorer. Meer informatie over het schrijven van query's en het afleiden van aanvullende inzichten uit uw gegevens.