Eseguire query sui dati in Azure Data Lake usando Azure Esplora datiQuery data in Azure Data Lake using Azure Data Explorer

Azure Data Lake Storage è una soluzione data Lake altamente scalabile ed economica per Big Data Analytics.Azure Data Lake Storage is a highly scalable and cost-effective data lake solution for big data analytics. Combina la potenza di una file system a prestazioni elevate con scalabilità e economia enormi che ti aiuta a ridurre i tempi di analisi.It combines the power of a high-performance file system with massive scale and economy to help you reduce your time to insight. Data Lake Storage Gen2 estende le funzionalità di archiviazione BLOB di Azure ed è ottimizzato per i carichi di lavoro di analisi.Data Lake Storage Gen2 extends Azure Blob Storage capabilities and is optimized for analytics workloads.

Azure Esplora dati si integra con l'archivio BLOB di Azure e con Azure Data Lake Storage (Gen1 e Gen2), offrendo accesso rapido, memorizzato nella cache e indicizzato ai dati archiviati in un archivio esterno.Azure Data Explorer integrates with Azure Blob Storage and Azure Data Lake Storage (Gen1 and Gen2), providing fast, cached, and indexed access to data stored in external storage. È possibile analizzare ed eseguire query sui dati senza inserimenti precedenti in Azure Esplora dati.You can analyze and query data without prior ingestion into Azure Data Explorer. È anche possibile eseguire query su dati esterni inseriti e non inseriti simultaneamente.You can also query across ingested and uningested external data simultaneously.

Suggerimento

Per ottimizzare le prestazioni delle query è necessario inserire i dati in Esplora dati di Azure.The best query performance necessitates data ingestion into Azure Data Explorer. La possibilità di eseguire query sui dati esterni senza inserimento precedente deve essere utilizzata solo per i dati cronologici o per i dati di cui viene eseguita una query raramente.The capability to query external data without prior ingestion should only be used for historical data or data that are rarely queried. Ottimizzare le prestazioni delle query dei dati esterni per ottenere risultati ottimali.Optimize your external data query performance for best results.

Creare una tabella esternaCreate an external table

Supponiamo di avere molti file CSV contenenti informazioni cronologiche sui prodotti archiviati in un magazzino e di voler eseguire un'analisi rapida per trovare i cinque prodotti più diffusi dell'anno scorso.Let's say you have lots of CSV files containing historical info on products stored in a warehouse, and you want to do a quick analysis to find the five most popular products from last year. In questo esempio, i file CSV hanno un aspetto simile al seguente:In this example, the CSV files look like:

TimestampTimestamp ProductIdProductId ProductDescriptionProductDescription
2019-01-01 11:21:002019-01-01 11:21:00 TO6050TO6050 3.5 nel disco floppy DS/HD3.5in DS/HD Floppy Disk
2019-01-01 11:30:552019-01-01 11:30:55 YDX1YDX1 Sintetizzatore Yamaha DX1Yamaha DX1 Synthesizer
...... ...... ......

I file vengono archiviati nell'archivio BLOB mycompanystorage di Azure in un contenitore denominato archivedproducts , partizionato per data:The files are stored in Azure Blob storage mycompanystorage under a container named archivedproducts, partitioned by date:

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 direttamente una query KQL su questi file CSV, usare il .create external table comando per definire una tabella esterna in Azure Esplora dati.To run a KQL query on these CSV files directly, use the .create external table command to define an external table in Azure Data Explorer. Per altre informazioni sulle opzioni di comando per la creazione di tabelle esterne, vedere comandi della tabella esterna.For more information on external table create command options, see external table commands.

.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:The external table is now visible in the left pane of the Web UI:

Tabella esterna nell'interfaccia utente Web

Autorizzazioni per tabelle esterneExternal table permissions

  • L'utente del database può creare una tabella esterna.The database user can create an external table. L'autore della tabella diventa automaticamente l'amministratore della tabella.The table creator automatically becomes the table administrator.
  • L'amministratore del cluster, del database o della tabella può modificare una tabella esistente.The cluster, database, or table administrator can edit an existing table.
  • Qualsiasi utente o lettore del database può eseguire una query su una tabella esterna.Any database user or reader can query an external table.

Esecuzione di query su una tabella esternaQuerying an external table

Una volta definita una tabella esterna, è external_table() possibile usare la funzione per farvi riferimento.Once an external table is defined, the external_table() function can be used to refer to it. Il resto della query è il linguaggio di query kusto standard.The rest of the query is standard Kusto Query Language.

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

Esecuzione di query su dati esterni e inseriti insiemeQuerying external and ingested data together

È possibile eseguire query su tabelle esterne e tabelle dati inserite all'interno della stessa query.You can query both external tables and ingested data tables within the same query. È possibile join o union la tabella esterna con dati aggiuntivi da Esplora dati di Azure, da SQL Server o da altre origini.You can join or union the external table with additional data from Azure Data Explorer, SQL servers, or other sources. Usare un oggetto let( ) statement per assegnare un nome abbreviato a un riferimento a una tabella esterna.Use a let( ) statement to assign a shorthand name to an external table reference.

Nell'esempio seguente, Products è una tabella di dati inserita e ArchivedProducts è una tabella esterna definita in precedenza:In the example below, Products is an ingested data table and ArchivedProducts is an external table that we've defined previously:

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 sui formati di dati gerarchiciQuerying hierarchical data formats

Azure Esplora dati consente l'esecuzione di query su formati gerarchici, ad esempio JSON ,, Parquet Avro e ORC .Azure Data Explorer allows querying hierarchical formats, such as JSON, Parquet, Avro, and ORC. Per eseguire il mapping dello schema dei dati gerarchici a uno schema di tabella esterno (se diverso), usare i comandi di mapping della tabella esterna.To map hierarchical data schema to an external table schema (if it's different), use external table mappings commands. Ad esempio, se si desidera eseguire una query sui file di log JSON con il formato seguente:For instance, if you want to query JSON log files with the following 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"   
  }   
}
...

La definizione della tabella esterna è simile alla seguente:The external table definition looks like this:

.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 che esegue il mapping dei campi dati ai campi di definizione della tabella esterna:Define a JSON mapping that maps data fields to external table definition fields:

.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, il mapping verrà richiamato e verrà eseguito il mapping dei dati rilevanti alle colonne della tabella esterna:When you query the external table, the mapping will be invoked, and relevant data will be mapped to the external table columns:

external_table('ApiCalls') | take 10

Per altre informazioni sulla sintassi di mapping, vedere mapping dei dati.For more info on mapping syntax, see data mappings.

Eseguire query su una tabella esterna TaxiRides nel cluster della GuidaQuery TaxiRides external table in the help cluster

Usare il cluster di test denominato Guida per provare diverse funzionalità di Azure Esplora dati.Use the test cluster called help to try out different Azure Data Explorer capabilities. Il cluster della Guida contiene una definizione di tabella esterna per un set di dati del taxi di New York City contenente miliardi di corse di taxi.The help cluster contains an external table definition for a New York City taxi dataset containing billions of taxi rides.

Creare una tabella esterna TaxiRidesCreate external table TaxiRides

In questa sezione viene illustrata la query utilizzata per creare la tabella esterna TaxiRides nel cluster della Guida .This section shows the query used to create the TaxiRides external table in the help cluster. Poiché questa tabella è già stata creata, è possibile ignorare questa sezione e passare direttamente a query TaxiRides dati della tabella esterna.Since this table has already been created, you can skip this section and go directly to query TaxiRides external table data.

.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 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:You can find the created TaxiRides table by looking at the left pane of the Web UI:

Tabella esterna dei taxi

Eseguire query sui dati della tabella esterna TaxiRidesQuery TaxiRides external table data

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

Eseguire query su una tabella esterna TaxiRides senza partizionamentoQuery TaxiRides external table without partitioning

Eseguire questa query sulla tabella esterna TaxiRides per visualizzare le corse per ogni giorno della settimana, nell'intero set di dati.Run this query on the external table TaxiRides to show rides for each day of the week, across the entire data set.

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

Questa query Mostra il giorno della settimana più occupato.This query shows the busiest day of the week. Poiché i dati non sono partizionati, per la restituzione dei risultati la query potrebbe richiedere fino a diversi minuti.Since the data isn't partitioned, the query may take up to several minutes to return results.

eseguire il rendering di una query non partizionata

Eseguire query su una tabella esterna TaxiRides con il partizionamentoQuery TaxiRides external table with partitioning

Eseguire questa query sulla tabella esterna TaxiRides per visualizzare i tipi di taxi cab (giallo o verde) usati nel gennaio 2017.Run this query on the external table TaxiRides to show taxi cab types (yellow or green) used in January of 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 consente di ottimizzare le prestazioni e il tempo di esecuzione delle query.This query uses partitioning, which optimizes query time and performance. La query Filtra in una colonna partizionata (pickup_datetime) e restituisce i risultati in pochi secondi.The query filters on a partitioned column (pickup_datetime) and returns results in a few seconds.

Esegui rendering query partizionata

È possibile scrivere query aggiuntive da eseguire nella tabella esterna TaxiRides e ottenere ulteriori informazioni sui dati.You can write additional queries to run on the external table TaxiRides and learn more about the data.

Ottimizzare le prestazioni delle queryOptimize your query performance

Ottimizzare le prestazioni delle query nel lago usando le seguenti procedure consigliate per eseguire query sui dati esterni.Optimize your query performance in the lake by using the following best practices for querying external data.

Formato datiData format

  • Usare un formato a colonne per le query analitiche, per i motivi seguenti:Use a columnar format for analytical queries, for the following reasons:
    • È possibile leggere solo le colonne attinenti a una query.Only the columns relevant to a query can be read.
    • Le tecniche di codifica della colonna possono ridurre significativamente le dimensioni dei dati.Column encoding techniques can reduce data size significantly.
  • Azure Esplora dati supporta formati a colonne parquet e ORC.Azure Data Explorer supports Parquet and ORC columnar formats. Il formato parquet è consigliato a causa dell'implementazione ottimizzata.Parquet format is suggested because of optimized implementation.

Area di AzureAzure region

Controllare che i dati esterni si trovino nella stessa area di Azure del cluster di Azure Esplora dati.Check that external data is in the same Azure region as your Azure Data Explorer cluster. Questa configurazione riduce i costi e i tempi di recupero dei dati.This setup reduces cost and data fetch time.

Dimensione del fileFile size

Le dimensioni ottimali del file sono pari a centinaia di MB (fino a 1 GB) per ogni file.The optimal file size is hundreds of Mb (up to 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.Avoid many small files that require unneeded overhead, such as slower file enumeration process and limited use of columnar format. Il numero di file deve essere maggiore del numero di core CPU nel cluster di Azure Esplora dati.The number of files should be greater than the number of CPU cores in your Azure Data Explorer cluster.

CompressioneCompression

Usare la compressione per ridurre la quantità di dati recuperati dall'archiviazione remota.Use compression to reduce the amount of data being fetched from the remote storage. Per il formato parquet, usare il meccanismo di compressione parquet interno che comprime i gruppi di colonne separatamente, consentendo di leggerli separatamente.For Parquet format, use the internal Parquet compression mechanism that compresses column groups separately, allowing you to read them separately. Per convalidare l'uso del meccanismo di compressione, verificare che i file siano denominati come segue: * < nomefile > . gz. parquet* o * < filename > . Snapper. parquet* e non * < filename. > parquet. gz*.To validate use of compression mechanism, check that the files are named as follows: <filename>.gz.parquet or <filename>.snappy.parquet and not <filename>.parquet.gz.

PartizionamentoPartitioning

Organizzare i dati usando partizioni "cartella" che consentono alla query di ignorare i percorsi irrilevanti.Organize your data using "folder" partitions that enable the query to skip irrelevant paths. Quando si pianifica il partizionamento, prendere in considerazione le dimensioni dei file e i filtri comuni nelle query come timestamp o ID tenant.When planning partitioning, consider file size and common filters in your queries such as timestamp or tenant ID.

Dimensioni macchina virtualeVM size

Selezionare SKU VM con più core e una velocità effettiva di rete superiore (la memoria è meno importante).Select VM SKUs with more cores and higher network throughput (memory is less important). Per altre informazioni, vedere selezionare lo SKU di VM corretto per il cluster di Azure Esplora dati.For more information, see Select the correct VM SKU for your Azure Data Explorer cluster.

Passaggi successiviNext steps

  • Eseguire query sui dati nel Azure Data Lake usando Esplora dati di Azure.Query your data in the Azure Data Lake using Azure Data Explorer. Informazioni su come scrivere query e derivare informazioni aggiuntive dai dati.Learn to write queries and derive additional insights from your data.