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 Fabric gestiona 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. El motor paraleliza los escaneos a nivel de archivo Parquet. Demasiados archivos pequeños aumentan la sobrecarga de archivos y metadatos, mientras que muy pocos archivos grandes pueden limitar el paralelismo de escaneo.

Para las tablas escritas por Spark, usa la configuración predeterminada en Fabric Spark runtime 2.0 o posterior. Estos tiempos de ejecución permiten por defecto un tamaño adaptativo de archivo destino para seleccionar el tamaño de archivo objetivo más óptimo por tabla, desde 128 MB para tablas más pequeñas hasta 1 GB para las tablas más grandes. Evita establecer objetivos estáticos o un límite arbitrario de recuento de filas sobre las configuraciones predeterminadas. Un límite de filas no tiene en cuenta el ancho de fila y puede crear archivos pequeños para tablas estrechas.

Si usas Fabric Spark runtime 1.3, activa el tamaño adaptativo del archivo objetivo y los objetivos de compactación a nivel de archivo, que están disponibles como funciones opt-in.

No necesitas V-Order para mejorar el rendimiento de los endpoints analíticos de SQL, porque Spark escribe archivos Parquet comprimidos con Snappy para reducir la E/S tanto de lectura como de escritura.

Los ajustes de escritura por defecto no sustituyen el mantenimiento de tablas. Utiliza las siguientes prácticas para mantener una distribución saludable a medida que cambian las mesas:

• Activa la autocompactación para cargas de trabajo donde la latencia periódica añadida de escritura síncrona sea aceptable. La autocompactación es una función de Spark que solo se ejecuta cuando hay demasiados archivos pequeños en una tabla.
• Programe tareas periódicas OPTIMIZE para cargas de trabajo en las que la latencia periódica adicional de la compactación automática no cumpla los SLA de actualización de datos.
• Ejecuta VACUUM según tus requisitos de retención y desplazamiento temporal para eliminar los archivos a los que el registro Delta ya no hace referencia. VACUUM reduce el almacenamiento retenido pero no mejora la disposición activa del archivo.
• Evita la partición de alta cardinalidad y las configuraciones personalizadas de escritura que generan muchos archivos pequeños.

Si no usas la autocompactación, para identificar tablas que necesitan mantenimiento, utiliza una tubería de datos y el sys.sp_get_table_health_metrics procedimiento almacenado T-SQL antes de ejecutar OPTIMIZE. Para ver un tutorial, consulte Optimize Lakehouse tables based on health checks (Optimización de tablas de Lakehouse en función de las comprobaciones de estado).

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 al tiempo que tarda en sincronizar los cambios con el endpoint de analítica SQL. 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. Sigue las mejores prácticas para el mantenimiento y particiónde 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. Podrías terminar con un gran número de archivos Parquet en una tabla Delta por una o más 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

Utiliza el siguiente cuaderno para imprimir un informe detallando el tamaño y los detalles de las particiones que sustentan una tabla Delta.

1. Primero, proporciona la ruta ABFSS para tu 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 la tabla 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