Consulta de datos en Azure Data Lake con Azure Data Explorer
Azure Data Lake Storage es una solución de lago de datos rentable y muy escalable para el análisis de macrodatos. Combina el potencial de un sistema de archivos de alto rendimiento con una escala masiva y rentabilidad para ayudarle a obtener conclusiones con rapidez. Data Lake Storage Gen2 amplía la funcionalidad de Azure Blob Storage y está optimizado para cargas de trabajo analíticas.
Azure Data Explorer se integra con Azure Blob Storage y Azure Data Lake Storage (Gen1 y Gen2) para ofrecer un acceso rápido, indexado y en caché a los datos almacenados en una solución de almacenamiento externo. Los datos se pueden analizar y consultar sin una ingesta previa en Azure Data Explorer. También es posible consultar simultáneamente datos externos ingeridos y sin ingerir. Para más información, consulte cómo crear una tabla externa mediante el asistente para la interfaz de usuario web de Azure Data Explorer. Para obtener información general breve, consulte tablas externas.
Sugerencia
Para obtener el mejor rendimiento de consultas, se necesita la ingesta de datos en Azure Data Explorer. Una consulta de datos externos sin ingesta previa solo se debe realizar para datos históricos o datos que se consultan con poca frecuencia. Optimice el rendimiento de las consultas para obtener los mejores resultados.
Creación de una tabla externa
Supongamos que tiene muchos archivos CSV con información histórica sobre los productos almacenados en un almacén y desea realizar un análisis rápido para encontrar los cinco productos más populares del año anterior. En este ejemplo, los archivos CSV tienen el siguiente aspecto:
| Marca de tiempo | ProductId | ProductDescription |
|---|---|---|
| 2019-01-01 11:21:00 | TO6050 | Disquete DS/HD de 3,5 pulgadas |
| 2019-01-01 11:30:55 | YDX1 | Sintetizador Yamaha DX1 |
| ... | ... | ... |
Los archivos están almacenados en Azure Blob Storage, en mycompanystorage, dentro de un contenedor llamado archivedproducts, con particiones por fecha:
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 ejecutar una consulta KQL en estos archivos CSV directamente, utilice el comando .create external table para definir una tabla externa en Azure Data Explorer. Para más información sobre la creación de una tabla externa con opciones de comando, consulte el artículo sobre la creación y modificación de tablas externas.
.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 tabla externa está visible en el panel izquierdo de la interfaz de usuario web de Azure Data Explorer:
Permisos de tabla externa
- El usuario de la base de datos puede crear una tabla externa. El creador de la tabla se convierte automáticamente en el administrador de la tabla.
- El administrador del clúster, la base de datos o la tabla puede editar una tabla existente.
- Cualquier usuario o lector de la base de datos puede consultar una tabla externa.
Consulta de una tabla externa
Una vez definida una tabla externa, se puede usar la función external_table() para hacer referencia a ella. El resto de la consulta está en lenguaje de consulta Kusto estándar.
external_table("ArchivedProducts")
| where Timestamp > ago(365d)
| summarize Count=count() by ProductId,
| top 5 by Count
Consulta conjunta de datos externos e ingeridos
Puede usar la misma consulta para consultar tablas externas y tablas de datos ingeridos. Puede utilizar los operadores join o union en la tabla externa con otros datos procedentes de Azure Data Explorer, servidores de SQL Server u otros orígenes. Use una instrucción de tipo let( ) statement para asignar un nombre abreviado a una referencia de tabla externa.
En el ejemplo siguiente, Products es una tabla de datos ingeridos y ArchivedProducts es una tabla externa que hemos definido previamente:
let T1 = external_table("ArchivedProducts") | where TimeStamp > ago(100d);
let T = Products; //T is an internal table
T1 | join T on ProductId | take 10
Consulta de formatos de datos jerárquicos
Azure Data Explorer permite consultar formatos de datos jerárquicos, como JSON, Parquet, Avro y ORC. Para asignar un esquema de datos jerárquico a un esquema de tabla externa (en el caso de que sea diferente), utilice comandos de asignación de tablas externas. Por ejemplo, si desea hacer consultas en archivos de registro JSON con el siguiente 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"
}
}
...
La definición de tabla externa tiene el siguiente aspecto:
.create external table ApiCalls(Timestamp: datetime, TenantId: guid, MethodName: string)
kind=blob
dataformat=multijson
(
h@'https://storageaccount.blob.core.windows.net/container1;StorageSecretKey'
)
Defina una asignación JSON que asigne campos de datos a campos de definición de tabla 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"}}]'
Al consultar la tabla externa, se invocará la asignación y se asignarán los datos pertinentes a las columnas de la tabla externa:
external_table('ApiCalls') | take 10
Para más información sobre la sintaxis de asignación, consulte asignaciones de datos.
Consulta de la tabla externa TaxiRides en el clúster de ayuda
Utilice el clúster de prueba llamado help para probar diferentes funciones de Azure Data Explorer. El clúster help contiene una definición de tabla externa para un conjunto de datos vinculado a los taxis de la ciudad de Nueva York, que contiene miles de millones de carreras de taxi.
Creación de la tabla externa TaxiRides
Esta sección muestra la consulta utilizada para crear la tabla externa TaxiRides en el clúster help. Puesto que la tabla ya se ha creado, puede omitir esta sección y realizar la consulta en los datos de la tabla externaTaxiRides.
.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'
)
La tabla TaxiRides que se ha creado está disponible en el panel izquierdo de la interfaz de usuario web de Azure Data Explorer:
Consulta de los datos de la tabla externa TaxiRides
Inicie sesión en https://dataexplorer.azure.com/clusters/help/databases/Samples.
Consulta de la tabla externa TaxiRides sin particiones
Ejecute esta consulta en la tabla externa TaxiRides para mostrar los paseos de cada día de la semana, en todo el conjunto de datos.
external_table("TaxiRides")
| summarize count() by dayofweek(pickup_datetime)
| render columnchart
Esta consulta muestra el día más ajetreado de la semana. Dado que no se han creado particiones de los datos, la consulta puede tardar varios minutos en devolver resultados.
Consulta de la tabla externa TaxiRides con particiones
Ejecute esta consulta en la tabla externa TaxiRides para mostrar los tipos de taxis (amarillos o verdes) que se usaron en enero 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 usa particiones, lo que optimiza el rendimiento y el tiempo de consulta. La consulta filtra en función de una columna con particiones (pickup_datetime) y devuelve los resultados en pocos segundos.
Puede escribir otras consultas para ejecutarlas en la tabla externa TaxiRides y obtener más información sobre los datos.
Optimización del rendimiento de las consultas
Optimice el rendimiento de las consultas en el lago mediante los siguientes procedimientos recomendados para consultar datos externos.
Formato de datos
- Use un formato de columna para las consultas analíticas, por los siguientes motivos:
- Solo se pueden leer las columnas pertinentes para una consulta.
- Las técnicas de codificación de columnas pueden reducir considerablemente el tamaño de los datos.
- Azure Data Explorer admite los formatos de columna Parquet y ORC. Se sugiere utilizar el formato Parquet debido a la implementación optimizada.
Región de Azure
Compruebe que los datos externos se encuentran en la misma región de Azure que el clúster de Azure Data Explorer. Esta configuración reduce el costo y el tiempo de captura de datos.
Tamaño de archivo
El tamaño óptimo de archivo es de cientos de MB (hasta 1 GB) por archivo. Evite muchos archivos pequeños que requieran una sobrecarga innecesaria, como un proceso de enumeración de archivos más lento y el uso limitado del formato de las columnas. El número de archivos debe ser mayor que el número de núcleos de CPU del clúster de Azure Data Explorer.
Compresión
Use la compresión para reducir la cantidad de datos que se capturan desde el almacenamiento remoto. Para el formato Parquet, utilice el mecanismo interno de compresión de Parquet, que comprime grupos de columnas por separado y también permite leerlos por separado. Para validar el uso del mecanismo de compresión, compruebe que los archivos tienen la nomenclatura <filename>.gz.parquet o <filename>.snappy.parquet, y no <filename>.parquet.gz.
Partitioning
Organice los datos con particiones de "carpeta" para que la consulta omita las rutas de acceso irrelevantes. Al planear la creación de particiones, tenga en cuenta el tamaño de archivo y los filtros comunes en las consultas, como la marca de tiempo o el identificador de inquilino.
Tamaño de VM
Seleccione las SKU de máquinas virtuales con más núcleos y mayor rendimiento de la red (la memoria es menos importante). Para más información, consulte Selección de la SKU de máquina virtual correcta para el clúster de Azure Data Explorer.