Consideraciones sobre el rendimiento del punto de conexión de SQL Analytics

El punto de conexión de SQL Analytics permite consultar datos en lakehouse mediante el lenguaje T-SQL y el protocolo TDS.

Cada lakehouse tiene un punto de análisis SQL. El número de puntos de conexión de SQL Analytics en un área de trabajo coincide con el número de lakehouses y bases de datos reflejadas aprovisionadas en dicha área de trabajo.

Un proceso en segundo plano se encarga de examinar el lakehouse en busca de cambios y de mantener actualizado el punto de conexión de análisis de SQL para todos los cambios guardados en los lakehouses de un área de trabajo. La plataforma Microsoft Fabric administra de forma transparente el proceso de sincronización. Cuando se detecta un cambio en un lakehouse, un proceso en segundo plano actualiza los metadatos y el punto de conexión de SQL Analytics refleja los cambios confirmados en las tablas del lakehouse. En condiciones de funcionamiento normales, el retraso entre un punto de conexión de lakehouse y SQL Analytics es inferior a un minuto. La duración real del tiempo puede variar de unos segundos a minutos en función de muchos factores que se describen en este artículo. El proceso en segundo plano solo se ejecuta cuando el punto de conexión de SQL Analytics está activo y se detiene después de 15 minutos de inactividad.

Instrucciones

• La detección automática de metadatos realiza un seguimiento de los cambios confirmados en lakehouses, y hay una sola instancia por espacio de trabajo de Fabric. Si observa una mayor latencia en la sincronización de los cambios entre los lakehouses y el punto de conexión de análisis de SQL, podría deberse a un gran número de lakehouses en un área de trabajo. En este escenario, considere migrar cada lakehouse a un espacio de trabajo independiente, ya que este enfoque permite ampliar a escala el descubrimiento automático de metadatos.
• Los archivos Parquet son inmutables por su diseño. Cuando hay una operación de actualización o eliminación, una tabla Delta agrega nuevos archivos Parquet con el conjunto de cambios, lo que aumenta el número de archivos a lo largo del tiempo, en función de la frecuencia de actualizaciones y eliminaciones. Si no programa el mantenimiento, este patrón crea finalmente una sobrecarga de lectura y esta condición afecta al tiempo necesario para sincronizar los cambios en el punto de conexión de SQL Analytics. Para solucionar este problema, programe las operaciones de mantenimiento de tablas de Lakehouse normales.
• En algunos escenarios, podría observar que los cambios confirmados en un lakehouse no son visibles en el endpoint de SQL Analytics asociado. Por ejemplo, puede crear una nueva tabla en lakehouse, pero aún no aparece en el punto de conexión de SQL Analytics. O bien, podría insertar un gran número de filas en una tabla de un lakehouse, pero estos datos aún no están visibles en el punto de conexión de análisis de SQL. Tiene la opción de iniciar la sincronización de metadatos a petición.
• El proceso de sincronización automática no admite todas las características delta. Para obtener más información sobre la funcionalidad admitida por cada motor en Fabric, consulte Interoperabilidad con formato de tabla delta Lake.
• Si hay un volumen extremadamente grande de cambios en las tablas durante el procesamiento de extracción, transformación y carga (ETL), se produce el retraso previsto hasta que se hayan procesado todos los cambios.

Optimización de tablas de lakehouse para consultar el punto de conexión de SQL Analytics

Cuando el punto de conexión de SQL Analytics lee las tablas almacenadas en un lago, el rendimiento de las consultas depende en gran medida del diseño físico de los archivos Parquet subyacentes.

Un gran número de archivos Parquet pequeños crea sobrecarga y afecta negativamente al rendimiento de las consultas. Para garantizar un rendimiento predecible y eficaz, mantenga el almacenamiento de tablas para que cada archivo Parquet contenga dos millones de filas. Este recuento de filas proporciona un nivel equilibrado de paralelismo sin fragmentar el conjunto de datos en segmentos excesivamente pequeños.

Además de la guía de recuento de filas, el tamaño del archivo es igualmente importante. El punto de conexión de análisis de SQL ofrece el mejor rendimiento cuando los archivos Parquet son lo suficientemente grandes como para minimizar la sobrecarga de procesamiento de archivos, pero no tan grandes como para limitar la eficacia de la exploración paralela. Para la mayoría de las cargas de trabajo, mantener los archivos Parquet individuales en torno a los 400 MB ofrece el mejor equilibrio. Para lograr este equilibrio, siga estos pasos:

1. Establézcalo maxRecordsPerFile en 2000 000 antes de que se produzcan cambios en los datos.
2. Realice los cambios de datos (ingesta de datos, actualizaciones, eliminaciones).
3. Establézcalo maxFileSize en 4 GB.
4. Ejecute OPTIMIZE. Para más información sobre el uso de OPTIMIZE, consulte Ejecutar el mantenimiento de tablas desde Lakehouse.

El siguiente script proporciona una plantilla para estos pasos y se debe ejecutar en un lakehouse:

from delta.tables import DeltaTable

# 1. CONFIGURE LIMITS

# Cap files to 2M rows during writes. This should be done before data ingestion occurs. 
spark.conf.set("spark.sql.files.maxRecordsPerFile", 2000000)

# 2. INGEST DATA
# Here, you ingest data into your table 

# 3. CAP FILE SIZE (~4GB)
spark.conf.set("spark.databricks.delta.optimize.maxFileSize", 4 * 1024 * 1024 * 1024)

# 4. RUN OPTIMIZE (bin-packing)
spark.sql("""
    OPTIMIZE myTable
""")

Para mantener tamaños de archivo correctos, ejecute periódicamente operaciones de optimización delta, como OPTIMIZE, especialmente para tablas que reciben inserciones, actualizaciones y eliminaciones incrementales frecuentes. Estas operaciones de mantenimiento compactan archivos pequeños en los de tamaño adecuado, lo que ayuda a garantizar que el punto de conexión de SQL Analytics pueda procesar consultas de forma eficaz.

Consideraciones sobre el tamaño de partición

La elección de la columna de partición para una tabla Delta en un lakehouse también afecta el tiempo necesario para sincronizar los cambios en el punto final de SQL Analytics. El número y el tamaño de las particiones de la columna de partición son importantes para el rendimiento:

• Una columna con alta cardinalidad (principalmente o completamente hecha de valores únicos) da como resultado un gran número de particiones. Un gran número de particiones afecta negativamente al rendimiento del examen de detección de metadatos para los cambios. Si la cardinalidad de una columna es alta, elija otra columna para la creación de particiones.
• El tamaño de cada partición también puede afectar al rendimiento. Use una columna que da como resultado una partición de al menos (o cerca de) 1 GB. Siga los procedimientos recomendados para el mantenimiento y la optimización de tablas delta. Para obtener un script de Python para evaluar particiones, consulte script de ejemplo con detalles sobre las particiones.

Un gran volumen de archivos de Parquet de tamaño pequeño aumenta el tiempo necesario para sincronizar los cambios entre un lakehouse y su punto de acceso de SQL Analytics asociado. Es posible que termine con un gran número de archivos de parquet en una tabla delta por una o varias razones:

• Si elige una partición para una tabla Delta con un gran número de valores únicos, la tabla se particiona por cada valor único y podría tener particiones excesivas. Elija una columna de partición que no tenga una cardinalidad alta y da como resultado particiones individuales al menos 1 GB cada una.
• Las tasas de ingesta de datos por lotes y streaming también pueden resultar en archivos pequeños dependiendo de la frecuencia y el tamaño de los cambios que se escriben en un lakehouse. Por ejemplo, puede haber un pequeño volumen de cambios que llegan a la casa del lago, lo que da lugar a pequeños archivos parquet. Para solucionar este problema, implemente el mantenimiento periódico de las tablas de lakehouse.

Script de ejemplo para los detalles de la partición

Use el cuaderno siguiente para imprimir un informe que detalle el tamaño y proporciona información sobre las particiones que respaldan una tabla Delta.

1. En primer lugar, proporcione la ruta de acceso de ABFSS para la tabla delta en la variable delta_table_path.
   • Puede obtener la ruta de acceso ABFSS de una tabla Delta desde el Explorador del portal de Microsoft Fabric. Haga clic con el botón derecho en el nombre de la tabla y, a continuación, seleccione COPY PATH en la lista de opciones.
2. El script genera todas las particiones de la tabla Delta.
3. El script recorre en iteración cada partición para calcular el tamaño total y el número de archivos.
4. El script genera los detalles de las particiones, los archivos por partición y el tamaño por partición en GB.

Puede copiar el script completo desde el siguiente bloque de código:

# Purpose: Print out details of partitions, files per partitions, and size per partition in GB.
from notebookutils import mssparkutils

# Define ABFSS path for your delta table. You can get ABFSS path of a delta table by simply right-clicking on table name and selecting COPY PATH from the list of options.
delta_table_path = "abfss://<workspace id>@<onelake>.dfs.fabric.microsoft.com/<lakehouse id>/Tables/<tablename>"

# List all partitions for given delta table
partitions = mssparkutils.fs.ls(delta_table_path)

# Initialize a dictionary to store partition details
partition_details = {}

# Iterate through each partition
for partition in partitions:
  if partition.isDir:
      partition_name = partition.name
      partition_path = partition.path
      files = mssparkutils.fs.ls(partition_path)
      
      # Calculate the total size of the partition

      total_size = sum(file.size for file in files if not file.isDir)
      
      # Count the number of files

      file_count = sum(1 for file in files if not file.isDir)
      
      # Write partition details

      partition_details[partition_name] = {
          "size_bytes": total_size,
          "file_count": file_count
      }
      
# Print the partition details
for partition_name, details in partition_details.items():
  print(f"{partition_name}, Size: {details['size_bytes']:.2f} bytes, Number of files: {details['file_count']}")

Esquema generado automáticamente en el punto de conexión de análisis SQL del Lakehouse

Para cada tabla Delta en tu Lakehouse, el punto de conexión de análisis SQL genera automáticamente una tabla en el esquema adecuado. El motor de punto de conexión de SQL Analytics se basa en el motor de Fabric Data Warehouse.

Para más información, consulte Sincronización de metadatos del punto de conexión SQL Analytics. También puede forzar mediante programación que se actualice la exploración automática de metadatos mediante la API REST para actualizar los metadatos del punto de conexión SQL.

Contenido relacionado

• Mantenimiento y optimización de tablas entre diferentes cargas de trabajo
• Optimización de tablas de Delta Lake y orden V
• Compactación de tabla
• Ajuste del tamaño del archivo
• Casos de uso del extremo de análisis SQL de Lakehouse