Consultar dados no Azure Data Lake com o Azure Data Explorer

Azure Data Lake Storage é uma solução de data lake altamente dimensionável e económica para análise de macrodados. Combina o poder de um sistema de ficheiros de alto desempenho com uma grande escala e economia para o ajudar a reduzir o seu tempo de informação. Data Lake Storage Gen2 expande Armazenamento de Blobs do Azure capacidades e está otimizado para cargas de trabalho de análise.

O Azure Data Explorer integra-se com Armazenamento de Blobs do Azure e Azure Data Lake Storage (Gen1 e Gen2), proporcionando acesso rápido, em cache e indexado aos dados armazenados no armazenamento externo. Pode analisar e consultar dados sem ingestão prévia no Azure Data Explorer. Também pode consultar dados externos ingeridos e não processados em simultâneo. Para obter mais informações, veja como criar uma tabela externa com o assistente de IU da Web do Azure Data Explorer. Para obter uma breve descrição geral, veja tabelas externas.

Criar uma tabela externa

Digamos que tem muitos ficheiros CSV que contêm informações históricas sobre produtos armazenados num armazém e quer fazer uma análise rápida para encontrar os cinco produtos mais populares do ano passado. Neste exemplo, os ficheiros CSV têm o seguinte aspeto:

Os ficheiros são armazenados no armazenamento mycompanystorage de Blobs do Azure num contentor denominado 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 diretamente uma consulta KQL nestes ficheiros CSV, utilize o .create external table comando para definir uma tabela externa no Azure Data Explorer. Para obter mais informações sobre as opções de comandos de criação de tabelas externas, veja 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 está agora visível no painel esquerdo da IU web do Azure Data Explorer:

Permissões de tabela externa

• O utilizador da base de dados pode criar uma tabela externa. O criador da tabela torna-se automaticamente o administrador de tabelas.
• O administrador do cluster, da base de dados ou da tabela pode editar uma tabela existente.
• Qualquer utilizador ou leitor de base de dados pode consultar uma tabela externa.

Consultar uma tabela externa

Assim que uma tabela externa for definida, a external_table() função pode ser utilizada para se referir à mesma. O resto da consulta é Linguagem de Pesquisa Kusto padrão.

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

Consultar dados externos e ingeridos em conjunto

Pode consultar tabelas externas e tabelas de dados ingeridas na mesma consulta. Pode join ou union a tabela externa com outros dados do Azure Data Explorer, servidores SQL ou outras origens. Utilize um let( ) statement para atribuir um nome abreviado a uma referência de tabela externa.

No exemplo abaixo, Products é uma tabela de dados ingerida 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

Consultar formatos de dados hierárquicos

O Azure Data Explorer permite consultar formatos hierárquicos, como JSON, Parquet, Avroe ORC. Para mapear o esquema de dados hierárquicos para um esquema de tabela externo (se for diferente), utilize comandos de mapeamento de tabelas externos. Por exemplo, se quiser consultar ficheiros de registo 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 tem o seguinte aspeto:

.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 mapeie campos de dados para campos de definição de tabela externos:

.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 consulta 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, veja mapeamentos de dados.

Consultar a tabela externa TaxiRides no cluster de ajuda

Utilize o cluster de teste denominado ajuda para experimentar diferentes capacidades de Data Explorer do Azure. O cluster de ajuda contém uma definição de tabela externa para um conjunto de dados de táxis de Nova Iorque que contém milhares de milhões de passeios de táxi.

Criar taxiRides de tabela externa

Esta secção mostra a consulta utilizada para criar a tabela externa TaxiRides no cluster de ajuda . Uma vez que esta tabela já foi criada, pode ignorar esta secçã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'
)

Pode encontrar a tabela TaxiRides criada ao observar o painel esquerdo da IU web do Azure Data Explorer:

Consultar dados de tabelas externas TaxiRides

Inicie sessão em https://dataexplorer.azure.com/clusters/help/databases/Samples.

Consultar a tabela externa TaxiRides sem criação de partições

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

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

Esta consulta mostra o dia mais movimentado da semana. Uma vez que os dados não estão particionados, a consulta pode demorar vários minutos a devolver resultados.

Consultar a tabela externa TaxiRides com a criação de partições

Execute esta consulta na tabela externa TaxiRides para mostrar os tipos de táxi (amarelo ou verde) utilizados 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

Esta consulta utiliza a criação de partições, o que otimiza o tempo e o desempenho da consulta. A consulta filtra uma coluna particionada (pickup_datetime) e devolve resultados em alguns segundos.

Pode escrever outras consultas para serem executadas na tabela externa TaxiRides e saber mais sobre os dados.

Otimizar o desempenho da consulta

Otimize o desempenho das consultas no lake com as seguintes melhores práticas para consultar dados externos.

Formato de dados

• Utilize um formato columnar para consultas analíticas, pelos seguintes motivos:
  • Apenas as colunas relevantes para uma consulta podem ser lidas.
  • As técnicas de codificação de colunas podem reduzir significativamente o tamanho dos dados.
• O Azure Data Explorer suporta formatos de colunas Parquet e ORC. O formato Parquet é 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. Esta configuração reduz o custo e o tempo de obtenção de dados.

Tamanho dos ficheiros

O tamanho de ficheiro ideal é centenas de Mb (até 1 GB) por ficheiro. Evite muitos ficheiros pequenos que exijam uma sobrecarga desnecessária, como um processo de enumeração de ficheiros mais lento e uma utilização limitada do formato columnar. O número de ficheiros deve ser superior ao número de núcleos de CPU no cluster do Azure Data Explorer.

Compressão

Utilize a compressão para reduzir a quantidade de dados que estão a ser obtidos a partir do armazenamento remoto. Para o formato Parquet, utilize o mecanismo de compressão parquet interno que comprime os grupos de colunas separadamente, permitindo-lhe lê-los separadamente. Para validar a utilização do mecanismo de compressão, verifique se os ficheiros têm o seguinte nome: <filename.gz.parquet> ou <filename.snappy.parquet> e não <filename.parquet.gz>.

Criação de partições

Organize os seus dados com partições de "pasta" que permitem que a consulta ignore caminhos irrelevantes. Ao planear a criação de partições, considere o tamanho do ficheiro e os filtros comuns nas suas consultas, como o carimbo de data/hora ou o ID do inquilino.

Tamanho da VM

Selecione SKUs de VM com mais núcleos e débito de rede mais elevado (a memória é menos importante). Para obter mais informações, veja Select the correct VM SKU for your Azure Data Explorer cluster (Selecionar o SKU da VM correto para o cluster do Azure Data Explorer).

Conteúdo relacionado

• Tutorial: Aprender operadores KQL comuns