Procedimientos recomendados para cargar datos en un grupo de SQL dedicado en Azure Synapse Analytics

• Iniciar una prueba gratuita de Fabric.
• Asistente de migración para almacén de datos en tejido.

En este artículo, encontrará recomendaciones y optimizaciones de rendimiento para cargar datos.

Preparación de datos en Azure Storage

Para minimizar la latencia, coloque la capa de almacenamiento y el grupo de SQL dedicado.

Al exportar datos a un formato de archivo ORC, puede experimentar errores de falta de memoria de Java cuando hay columnas de texto grandes. Para solucionar esta limitación, exporte solo un subconjunto de las columnas.

PolyBase no puede cargar filas que tengan más de 1000 000 bytes de datos. Al colocar datos en los archivos de texto en Azure Blob Storage o Azure Data Lake Store, deben tener menos de 1000 000 bytes de datos. Esta limitación de bytes es verdadera independientemente del esquema de la tabla.

Todos los formatos de archivo tienen características de rendimiento diferentes. Para obtener la carga más rápida, use archivos de texto delimitados comprimidos. La diferencia entre el rendimiento UTF-8 y UTF-16 es mínimo.

Divida archivos comprimidos grandes en archivos comprimidos más pequeños.

Ejecutar cargas con suficiente capacidad de cómputo

Para una velocidad de carga más rápida, ejecute solo un trabajo de carga a la vez. Si no es factible, ejecute un número mínimo de cargas simultáneamente. Si espera un trabajo de carga grande, considere ampliar el grupo de SQL dedicado antes de la carga.

Para ejecutar cargas con recursos computacionales adecuados, cree usuarios de carga designados para ejecutar cargas. Asigne cada usuario de carga a una clase de recurso o grupo de cargas de trabajo específico. Para ejecutar una carga, inicie sesión como uno de los usuarios de carga y, a continuación, ejecute la carga. La carga se ejecuta con la clase de recurso del usuario. Este método es más sencillo que intentar cambiar la clase de recursos de un usuario para ajustarse a la necesidad actual de la clase de recurso.

Creación de un usuario para carga de datos

En este ejemplo se crea un usuario de carga clasificado en un grupo de cargas de trabajo específico. El primer paso es conectarse al maestro y crear un inicio de sesión.

   -- Connect to master
   CREATE LOGIN loader WITH PASSWORD = 'a123STRONGpassword!';

Conéctese al grupo de SQL dedicado y cree un usuario. En el código siguiente se supone que está conectado a la base de datos denominada mySampleDataWarehouse. Muestra cómo crear un usuario denominado cargador y conceder permisos de usuario para crear tablas y cargar mediante la instrucción COPY. A continuación, clasifica al usuario al grupo de cargas de trabajo DataLoads con recursos máximos.

   -- Connect to the dedicated SQL pool
   CREATE USER loader FOR LOGIN loader;
   GRANT ADMINISTER DATABASE BULK OPERATIONS TO loader;
   GRANT INSERT ON <yourtablename> TO loader;
   GRANT SELECT ON <yourtablename> TO loader;
   GRANT CREATE TABLE TO loader;
   GRANT ALTER ON SCHEMA::dbo TO loader;
   
   CREATE WORKLOAD GROUP DataLoads
   WITH ( 
       MIN_PERCENTAGE_RESOURCE = 0
       ,CAP_PERCENTAGE_RESOURCE = 100
       ,REQUEST_MIN_RESOURCE_GRANT_PERCENT = 100
	);

   CREATE WORKLOAD CLASSIFIER [wgcELTLogin]
   WITH (
	     WORKLOAD_GROUP = 'DataLoads'
       ,MEMBERNAME = 'loader'
   );

Para ejecutar una carga con recursos para el grupo de trabajo de carga, inicie sesión como loader y ejecute la carga.

Permitir que varios usuarios carguen

A menudo, es necesario que varios usuarios carguen datos en un almacenamiento de datos. La carga con CREATE TABLE AS SELECT (Transact-SQL) requiere permisos CONTROL de la base de datos. El permiso CONTROL concede acceso de control a todos los esquemas. Es posible que no quiera que todos los usuarios que carguen tengan acceso de control en todos los esquemas. Para limitar los permisos, use la instrucción DENY CONTROL.

Por ejemplo, considere los esquemas de base de datos, schema_A para el departamento A y schema_B para el departamento B. Los usuarios de base de datos user_A y user_B están asignados para la carga de PolyBase en los departamentos A y B, respectivamente. A ambos se les han concedido permisos de base de datos CONTROL. Los creadores del esquema A y B ahora bloquean sus esquemas mediante DENY:

   DENY CONTROL ON SCHEMA :: schema_A TO user_B;
   DENY CONTROL ON SCHEMA :: schema_B TO user_A;

User_A y user_B ahora están bloqueados de la estructura de base de datos del otro departamento.

Cargar en una tabla de almacenamiento provisional

Para lograr la velocidad de carga más rápida para mover datos a una tabla de almacenamiento de datos, cargue los datos en una tabla de almacenamiento provisional. Defina la tabla de almacenamiento provisional como montón y use round robin para la opción de distribución.

Tenga en cuenta que la carga suele ser un proceso de dos pasos en el que primero se carga en una tabla de almacenamiento provisional y, a continuación, inserta los datos en una tabla de almacenamiento de datos de producción. Si la tabla de producción usa una distribución hash, el tiempo total para cargar e insertar podría ser más rápido si define la tabla de almacenamiento provisional con la distribución hash. La carga en la tabla de puesta en escena lleva más tiempo, pero el segundo paso de insertar las filas en la tabla de producción no incurre en el movimiento de datos entre las distribuciones.

Cargar datos en un índice columnstore

Los índices de almacén de columnas requieren grandes cantidades de memoria para comprimir los datos en grupos de filas de alta calidad. Para obtener la mejor compresión y eficiencia del índice, el índice de almacén de columnas debe comprimir un máximo de 1.048.576 filas en cada grupo de filas. Cuando hay presión de memoria, es posible que el índice columnstore no pueda lograr ratios de compresión máximos. Esto afecta al rendimiento de las consultas. Para obtener una profundización, consulte Optimizaciones de memoria de almacén de columnas.

• Para asegurarse de que el usuario de carga tenga suficiente memoria para alcanzar tasas de compresión máximas, utilice usuarios de carga que sean miembros de una clase de recursos mediana o grande.
• Cargue suficientes filas para rellenar completamente nuevos grupos de filas. Durante una carga masiva, cada 1.048.576 filas se comprimen directamente en el almacén columnar como un grupo de filas completo. Las cargas con menos de 102,400 filas envían las filas al "deltastore" donde las filas se mantienen en un índice de b-tree. Si carga demasiadas filas, es posible que todas vayan al almacén delta y no se compriman inmediatamente en formato de almacén de columnas.

Aumentar el tamaño del lote al usar el API de SQLBulkCopy o BCP

La carga con la instrucción COPY proporcionará el mayor rendimiento con grupos de SQL dedicados. Si no puede usar COPY para cargar y debe usar SqLBulkCopy API o bcp, debe considerar aumentar el tamaño del lote para mejorar el rendimiento de procesamiento.

Administración de errores de carga

Se puede producir un error en una carga mediante una tabla externa con el error "Consulta anulada: se alcanzó el umbral máximo de rechazo al leer desde un origen externo". Este mensaje indica que los datos externos contienen registros sucios. Un registro de datos se considera sucio si los tipos de datos y el número de columnas no coinciden con las definiciones de columna de la tabla externa, o si los datos no se ajustan al formato de archivo externo especificado.

Para corregir los registros sucios, asegúrese de que las definiciones de formato de archivo externo y tabla externa son correctas y los datos externos se ajustan a estas definiciones. En caso de que un subconjunto de registros de datos externos esté sucio, puede optar por rechazar estos registros para las consultas mediante las opciones de rechazo de "CREATE EXTERNAL TABLE".

Insertar datos en una tabla de producción

Una carga única en una tabla pequeña con una instrucción INSERT, o incluso una recarga periódica de una búsqueda podría funcionar lo suficientemente bien con una instrucción como INSERT INTO MyLookup VALUES (1, 'Type 1'). Sin embargo, las inserciones individuales no son tan eficaces como ejecutar una carga masiva.

Si tiene miles o más inserciones únicas durante todo el día, lotee las inserciones para que pueda cargarlas de forma masiva. Desarrolle los procesos para anexar las inserciones únicas a un archivo y, a continuación, cree otro proceso que cargue periódicamente el archivo.

Creación de estadísticas después de la carga

Para mejorar el rendimiento de las consultas, es importante crear estadísticas en todas las columnas de todas las tablas después de la primera carga o se producen cambios importantes en los datos. La creación de estadísticas se puede realizar manualmente o puede habilitar la creación automática de estadísticas.

Para obtener una explicación detallada de las estadísticas, consulte Estadísticas. En el ejemplo siguiente se muestra cómo crear manualmente estadísticas en cinco columnas de la tabla Customer_Speed.

create statistics [SensorKey] on [Customer_Speed] ([SensorKey]);
create statistics [CustomerKey] on [Customer_Speed] ([CustomerKey]);
create statistics [GeographyKey] on [Customer_Speed] ([GeographyKey]);
create statistics [Speed] on [Customer_Speed] ([Speed]);
create statistics [YearMeasured] on [Customer_Speed] ([YearMeasured]);

Rotación de claves de almacenamiento

Se considera una buena práctica de seguridad cambiar regularmente la clave de acceso a tu almacenamiento de blobs. Tiene dos claves de almacenamiento para la cuenta de Blob Storage, lo que le permite realizar la transición de las claves.

Para rotar las claves de cuenta de Azure Storage:

Para cada cuenta de almacenamiento cuya clave ha cambiado, ejecute ALTER DATABASE SCOPED CREDENTIAL.

Ejemplo:

Se crea la clave original

CREATE DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key1'

Rotación de la clave 1 a la clave 2

ALTER DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key2'

No se necesitan otros cambios en los orígenes de datos externos subyacentes.

Contenido relacionado

• Para obtener más información sobre PolyBase y diseñar un proceso de extracción, carga y transformación (ELT), consulte Design ELT para Azure Synapse Analytics.
• Para ver un tutorial de carga, use PolyBase para cargar datos desde Azure Blob Storage a Azure Synapse Analytics.
• Para supervisar las cargas de datos, consulte Supervisión de la carga de trabajo mediante DMV.