Eseguire query sui dati in Azure Data Lake usando Esplora dati di Azure

Azure Data Lake Storage è una soluzione per data lake a scalabilità elevata ed economicamente conveniente per l'analisi dei Big Data. Combina la potenza di un file system ad alte prestazioni con scalabilità elevatissima e costi contenuti per ridurre i tempi di analisi. Data Lake Storage Gen2 estende le funzionalità di Archiviazione BLOB di Azure ed è ottimizzato per i carichi di lavoro di analisi.

Azure Data Explorer si integra con Archiviazione BLOB di Azure e Azure Data Lake Storage (Gen1 e Gen2) per fornire un accesso rapido, indicizzato e basato sulla cache ai dati archiviati in un archivio dati esterno. È possibile analizzare ed eseguire query sui dati senza prima inserirli in Esplora dati di Azure. È anche possibile eseguire query simultaneamente su dati esterni inseriti e non inseriti. Per altre informazioni, vedere come creare una tabella esterna usando la procedura guidata dell'interfaccia utente Web di Azure Esplora dati. Per una breve panoramica, vedere tabelle esterne.

Suggerimento

Per ottenere prestazioni delle query ottimali, è necessario inserire i dati in Esplora dati di Azure. La possibilità di eseguire query su dati esterni senza prima inserirli deve essere sfruttata solo per i dati cronologici o per i dati su cui vengono eseguite query raramente. Per ottenere risultati ottimali, ottimizzare le prestazioni delle query sui dati esterni.

Creare una tabella esterna

Si supponga di avere molti file CSV contenenti informazioni cronologiche sui prodotti depositati in un magazzino e di voler eseguire un'analisi rapida per trovare i cinque prodotti più popolari dell'anno precedente. I file CSV sono simili all'esempio seguente:

Timestamp	ProductId	ProductDescription
2019-01-01 11:21:00	TO6050	3.5in DS/HD Floppy Disk
2019-01-01 11:30:55	YDX1	Yamaha DX1 Synthesizer
...	...	...

I file sono archiviati nell'archivio BLOB di Azure mycompanystorage in un contenitore denominato archivedproducts, partizionato per 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
...

Per eseguire una query KQL direttamente su questi file CSV, usare il comando .create external table per definire una tabella esterna in Esplora dati di Azure. Per altre informazioni sulle opzioni del comando .create external table, vedere Creare e modificare tabelle esterne in Archiviazione di Azure o Azure Data Lake.

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

La tabella esterna è ora visibile nel riquadro sinistro dell'interfaccia utente Web di Azure Esplora dati:

Autorizzazioni per le tabelle esterne

• L'utente del database può creare una tabella esterna. L'autore della tabella ne diventa automaticamente l'amministratore.
• L'amministratore del cluster, del database o della tabella può modificare una tabella esistente.
• Qualsiasi utente o lettore del database può eseguire query su una tabella esterna.

Esecuzione di query su una tabella esterna

Dopo aver definito una tabella esterna, è possibile usare la funzione external_table() per farvi riferimento. Il resto della query è una query KQL (Kusto Query Language) standard.

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

Esecuzione di query simultaneamente su dati esterni e inseriti

È possibile eseguire la stessa query simultaneamente su tabelle esterne e tabelle di dati inseriti. È possibile join o union la tabella esterna con altri dati di Azure Esplora dati, sql server o altre origini. Usare un'let( ) statement per assegnare un nome abbreviato a un riferimento a una tabella esterna.

Nell'esempio seguente Products è una tabella di dati inseriti e ArchivedProducts è una tabella esterna definita in precedenza:

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

Esecuzione di query su formati di dati gerarchici

Esplora dati di Azure consente di eseguire query su formati gerarchici, come JSON, Parquet, Avro e ORC. Per eseguire il mapping dello schema dei dati gerarchici a uno schema di tabella esterna (se diverso), usare i comandi di mapping delle tabelle esterne. Ad esempio, per eseguire query sui file di log JSON con il formato seguente:

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

La definizione di tabella esterna ha un aspetto simile al seguente:

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

Definire un mapping JSON dai campi dati ai campi di definizione della tabella esterna:

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

Quando si esegue una query sulla tabella esterna, viene richiamato il mapping e i dati pertinenti vengono mappati alle colonne della tabella esterna:

external_table('ApiCalls') | take 10

Per altre informazioni sulla sintassi di mapping, vedere Mapping dei dati.

Eseguire query sulla tabella esterna TaxiRides nel cluster help

Usare il cluster di test denominato help per provare diverse funzionalità di Esplora dati di Azure. Il cluster help contiene una definizione di tabella esterna per un set di dati dei taxi di New York City contenente miliardi di corse di taxi.

Creare la tabella esterna TaxiRides

Questa sezione illustra la query usata per creare la tabella esterna TaxiRides nel cluster help. Poiché questa tabella è già stata creata, è possibile ignorare questa sezione e passare direttamente a Eseguire query sui dati della tabella esterna 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'
)

È possibile trovare la tabella TaxiRides creata esaminando il riquadro sinistro dell'interfaccia utente Web di Azure Esplora dati:

Eseguire query sui dati della tabella esterna TaxiRides

Accedere a https://dataexplorer.azure.com/clusters/help/databases/Samples.

Eseguire query sulla tabella esterna TaxiRides senza partizionamento

Eseguire questa query sulla tabella esterna TaxiRides per visualizzare le corse per ogni giorno della settimana nell'intero set di dati.

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

Questa query mostra il giorno della settimana con il numero di corse più elevato. Poiché i dati non sono partizionati, la restituzione dei risultati della query potrebbe richiedere diversi minuti.

Eseguire query sulla tabella esterna TaxiRides con partizionamento

Eseguire questa query sulla tabella esterna TaxiRides per visualizzare i tipi di taxi (giallo o verde) usati nel gennaio 2017.

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

Questa query usa il partizionamento, che ne ottimizza le prestazioni e il tempo di esecuzione. La query applica un filtro a una colonna partizionata (pickup_datetime) e restituisce i risultati in pochi secondi.

È possibile scrivere altre query da eseguire nella tabella esterna TaxiRides e ottenere altre informazioni sui dati.

Ottimizzare le prestazioni della query

Ottimizzare le prestazioni della query nel data lake usando le procedure consigliate seguenti per l'esecuzione di query sui dati esterni.

Formato dati

• Per le query analitiche occorre usare un formato a colonne, per i motivi seguenti:
  • Possono essere lette solo le colonne pertinenti a una query.
  • Le tecniche di codifica delle colonne possono ridurre in modo significativo le dimensioni dei dati.
• Esplora dati di Azure supporta i formati a colonne Parquet e ORC. Il formato Parquet è quello consigliato per via dell'implementazione ottimizzata.

Area di Azure

Verificare che i dati esterni siano nella stessa area di Azure in cui si trova il cluster di Esplora dati di Azure. Questa configurazione riduce i costi e i tempi di recupero dei dati.

Dimensione del file

Le dimensioni ottimali dei file sono pari a centinaia di MB (fino a 1 GB) per file. Evitare molti file di piccole dimensioni che richiedono un sovraccarico non necessario, ad esempio un processo di enumerazione dei file più lento e un uso limitato del formato a colonne. Il numero di file deve essere maggiore del numero di core CPU presenti nel cluster di Esplora dati di Azure.

Compressione

Usare la compressione per ridurre la quantità di dati da recuperare dall'archivio remoto. Per il formato Parquet, usare il meccanismo di compressione di Parquet interno che comprime gruppi di colonne separatamente, in modo da poterli leggere separatamente. Per convalidare l'uso del meccanismo di compressione, verificare che i file siano denominati nel modo seguente: <nomefile>.gz.parquet o <nomefile>.snappy.parquet e non <nomefile>.parquet.gz.

Partizionamento

Organizzare i dati usando partizioni di "cartella" che consentono alla query di ignorare i percorsi non rilevanti. Nella pianificazione del partizionamento, considerare le dimensioni dei file e i filtri comuni nelle query, come il timestamp o l'ID tenant.

Dimensioni macchina virtuale

Selezionare SKU di VM con più core e velocità effettiva di rete superiore (la memoria è meno importante). Per altre informazioni, vedere Selezionare lo SKU di VM corretto per il cluster di Esplora dati di Azure.

Contenuti correlati

• Esercitazione: Informazioni sugli operatori KQL comuni