Consultar dados no Azure Data Lake usando o Azure Data Explorer

O Azure Data Lake Storage é uma solução de Data Lake altamente escalonável e econômica para análise de Big Data. Ele combina o poder de um sistema de arquivos de alto desempenho com grande escala e economia a fim de ajudar a reduzir o tempo para obter insights. O Data Lake Storage Gen2 estende as funcionalidades do Armazenamento de Blobs do Azure e é otimizado para cargas de trabalho de análise.

O Azure Data Explorer integra-se ao Armazenamento de Blobs do Azure e ao Azure Data Lake Storage (Gen1 e Gen2), fornecendo acesso rápido, armazenado em cache e indexado aos dados armazenados em armazenamento externo. Você pode analisar e consultar os dados sem ingestão anterior no Azure Data Explorer. Você também pode consultar dados externos ingeridos e não ingeridos simultaneamente. Para obter mais informações, veja como criar uma tabela externa usando o assistente de interface do usuário da Web do Azure Data Explorer. Para obter uma breve visão geral, consulte tabelas externas.

Dica

O melhor desempenho de consulta exige a ingestão de dados no Azure Data Explorer. A capacidade de consultar dados externos sem ingestão prévia deve ser usada somente para dados históricos ou para dados que raramente são consultados. Otimize o desempenho da consulta de dados externos para obter melhores resultados.

Criar uma tabela externa

Digamos que você tenha muitos arquivos CSV contendo informações históricas sobre produtos armazenados em um depósito e queira fazer uma análise rápida para encontrar os cinco produtos mais populares do ano anterior. Neste exemplo, os arquivos CSV são semelhantes ao seguinte:

Timestamp	ProductId	ProductDescription
2019-01-01 11:21:00	TO6050	Disquete DS/HD de 3,5 pol.
2019-01-01 11:30:55	YDX1	Sintetizador DX1 Yamaha
...	...	...

Os arquivos são armazenados no Armazenamento de Blobs do Azure mycompanystorage em um contêiner chamado archivedproducts, particionado por 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
...

Para executar uma consulta KQL nesses arquivos CSV diretamente, use o comando .create external table a fim de definir uma tabela externa no Azure Data Explorer. Para obter mais informações sobre as opções de comando para criação de tabela externa, confira Comandos de tabela externa.

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

A tabela externa agora está visível no painel esquerdo da interface do usuário Web do Azure Data Explorer:

Permissões da tabela externa

• O usuário do banco de dados pode criar uma tabela externa. O criador da tabela se torna automaticamente o administrador da tabela.
• O administrador do cluster, do banco de dados ou da tabela pode editar uma tabela existente.
• Qualquer usuário ou leitor de banco de dados pode consultar uma tabela externa.

Consultando uma tabela externa

Depois que uma tabela externa é definida, a função external_table() pode ser usada para fazer referência a ela. O restante da consulta corresponde à linguagem de consulta do Azure Data Explorer padrão.

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

Consultando dados externos e ingeridos juntos

Você pode consultar tabelas externas e tabelas de dados ingeridos dentro da mesma consulta. Você pode join ou union a tabela externa com outros dados do Azure Data Explorer, dos servidores SQL ou de outras fontes. Use um let( ) statement para atribuir um nome abreviado a uma referência de tabela externa.

No exemplo abaixo, Produtos é uma tabela de dados ingeridos e ArchivedProducts é uma tabela externa que definimos anteriormente:

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

Consultando formatos de dados hierárquicos

O Azure Data Explorer permite que formatos hierárquicos sejam consultados, como JSON, Parquet, Avro e ORC. Para mapear o esquema de dados hierárquicos para um esquema de tabela externa (se for diferente), use os comandos de mapeamento de tabela externa. Por exemplo, se você quiser consultar arquivos de log JSON com o seguinte formato:

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

A definição da tabela externa será semelhante a esta:

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

Defina um mapeamento JSON que correlacione os campos de dados aos campos de definição da tabela externa:

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

Sempre que você consultar a tabela externa, o mapeamento será invocado e os dados relevantes serão mapeados para as colunas da tabela externa:

external_table('ApiCalls') | take 10

Para obter mais informações sobre a sintaxe de mapeamento, confira mapeamentos de dados.

Consultar a tabela externa TaxiRides no cluster ajuda

Use o cluster de teste chamado ajuda para experimentar diferentes funcionalidades do Azure Data Explorer. O cluster ajuda contém uma definição de tabela externa para um conjunto de dados de táxis da cidade de Nova York que inclui bilhões de corridas de táxi.

Criar a tabela externa TaxiRides

Esta seção mostra a consulta usada para criar a tabela externa TaxiRides no cluster ajuda. Como essa tabela já foi criada, você pode ignorar esta seção e ir diretamente para Consultar os dados da tabela externa 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'
)

Você pode encontrar a tabela TaxiRides criada examinando o painel esquerdo da interface do usuário Web do Azure Data Explorer:

Consultar os dados da tabela externa TaxiRides

Entrar no https://dataexplorer.azure.com/clusters/help/databases/Samples.

Consultar a tabela externa TaxiRides sem particionamento

Execute essa consulta na tabela externa TaxiRides para mostrar corridas para cada dia da semana, em todo o conjunto de dados.

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

Essa consulta mostra o dia da semana mais ocupado. Como os dados não estão particionados, a consulta pode levar vários minutos para retornar os resultados.

Consultar a tabela externa TaxiRides com particionamento

Execute esta consulta na tabela externa TaxiRides para mostrar os tipos de táxi (amarelo ou verde) usados em janeiro de 2017.

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

Essa consulta usa particionamento, o que otimiza o tempo e o desempenho da consulta. A consulta filtra com base em uma coluna particionada (pickup_datetime) e retorna os resultados em poucos segundos.

Você pode escrever outras consultas a serem executadas na tabela externa TaxiRides e saber mais sobre os dados.

Otimizar o desempenho da consulta

Otimize o desempenho da consulta no Lake usando as práticas recomendadas a seguir para consultar dados externos.

Formato de dados

• Use um formato de coluna para consultas analíticas, pelos seguintes motivos:
  • Somente as colunas relevantes para uma consulta podem ser lidas.
  • As técnicas de codificação de coluna podem reduzir significativamente o tamanho dos dados.
• O Azure Data Explorer é compatível com os formatos de coluna Parquet e ORC. O formato Parquet é o sugerido devido à implementação otimizada.

Região do Azure

Verifique se os dados externos estão na mesma região do Azure que o cluster do Azure Data Explorer. Essa configuração reduz o custo e o tempo de busca de dados.

Tamanho do arquivo

O tamanho do arquivo ideal é de centenas de MB (até 1 GB) por arquivo. Evite muitos arquivos pequenos que exijam sobrecarga desnecessária, como o processo de enumeração de arquivos mais lento e o uso limitado do formato de coluna. O número de arquivos deve ser maior que o número de núcleos de CPU no cluster do Azure Data Explorer.

Compactação

Use a compactação para reduzir a quantidade de dados que estão sendo buscados do armazenamento remoto. Para o formato Parquet, use o mecanismo de compactação interno do Parquet que compacta os grupos de colunas separadamente, permitindo que você os leia separadamente. Para validar o uso do mecanismo de compactação, verifique se os arquivos estão nomeados da seguinte maneira: <filename>.gz.parquet ou <filename>.snappy.parquet e não <filename>.parquet.gz.

Particionamento

Organize os dados usando partições de "pasta" que permitam à consulta ignorar caminhos irrelevantes. Ao planejar o particionamento, considere o tamanho do arquivo e os filtros comuns das consultas, como carimbo de data/hora ou ID do locatário.

Tamanho da VM

Selecione os SKUs de VM com mais núcleos e maior taxa de transferência de rede (a memória é menos importante). Para obter mais informações, confira Selecionar o SKU de VM correto para o cluster do Azure Data Explorer.

Conteúdo relacionado

• Tutorial: Aprender operadores KQL comuns