Estrategias de carga de datos para el grupo de SQL dedicado en Azure Synapse Analytics
Los grupos de SQL dedicados de SMP tradicionales usan un proceso de extracción, transformación y carga (ETL) para cargar los datos. Synapse SQL, en Azure Synapse Analytics, usa una arquitectura de procesamiento de consultas distribuidas que aprovecha la escalabilidad y la flexibilidad de los recursos de proceso y almacenamiento.
El uso de un proceso de extracción, carga y transformación (ELT) aprovecha las ventajas de las funcionalidades integradas de procesamiento de consultas distribuidas y elimina los recursos necesarios para transformar los datos antes de cargarlos.
Aunque los grupos de SQL dedicados admiten muchos métodos de carga, entre los que se incluyen las conocidas opciones de SQL Server como bcp y SqlBulkCopy API, la manera más rápida y escalable de cargar datos es a través de tablas externas de PolyBase y la instrucción COPY.
Con PolyBase y la instrucción COPY, es posible acceder a datos externos almacenados en Azure Blob Storage o Azure Data Lake Store mediante el lenguaje T-SQL. Para obtener la máxima flexibilidad al realizar la carga, se recomienda usar la instrucción COPY.
¿Qué es ELT?
Extracción, carga y transformación (ELT) es un proceso mediante el que se extraen datos de un sistema de origen, se cargan en un grupo de SQL dedicado y, después, se transforman.
Los pasos básicos para implementar ELT son los siguientes:
- Extraer los datos de origen en archivos de texto.
- Llevar los datos a Azure Blob Storage o Azure Data Lake Store.
- Preparar los datos para la carga.
- Cargar los datos en las tablas de almacenamiento provisional con PolyBase o el comando COPY.
- Transformar los datos.
- Insertar los datos en tablas de producción.
Para obtener un tutorial sobre la carga, vea Carga de datos desde Azure Blob Storage.
1. Extraer los datos de origen en archivos de texto
La obtención de datos del sistema de origen depende de la ubicación del almacenamiento. El objetivo consiste en mover los datos a archivos de texto delimitados compatibles o CSV.
Formatos de archivos admitidos
Con PolyBase y la instrucción COPY, puede cargar datos a archivos CSV o de texto delimitados con codificación UTF-8 y UTF-16. Además de los archivos CSV o de texto delimitados, carga datos desde formatos de archivos Hadoop, como ORC y Parquet. PolyBase y la instrucción COPY también pueden cargar datos desde archivos comprimidos Gzip y Snappy.
No se admiten el formato ASCII extendido, el formato de ancho fijo ni los formatos anidados como WinZip o XML. Si va a exportar desde SQL Server, puede usar la herramienta de línea de comandos bcp para exportar los datos en archivos de texto delimitados.
2. Llevar los datos a Azure Blob Storage o Azure Data Lake Store
Para llevar los datos a Azure Storage, puede moverlos a Azure Blob Storage o Azure Data Lake Store Gen2. Los datos deben almacenarse en archivos de texto en ambas ubicaciones. PolyBase y la instrucción COPY se pueden cargar desde cualquier ubicación.
Herramientas y servicios que puede usar para mover datos a Azure Storage:
- El servicio Azure ExpressRoute mejora el rendimiento de la red, el rendimiento en general y la capacidad de predicción. ExpressRoute es un servicio que enruta los datos a Azure a través de una conexión privada dedicada. Las conexiones ExpressRoute no enrutan datos a través de la red pública de Internet. Estas conexiones son más fiables y ofrecen velocidades más altas, menores latencias y mayor seguridad que las conexiones habituales a través de Internet.
- La utilidad AzCopy mueve los datos a Azure Storage a través de la red pública de Internet. Esto funciona si el tamaño de los datos es de menos de 10 TB. Para realizar cargas de forma regular con AzCopy, pruebe la velocidad de la red para ver si es aceptable.
- Azure Data Factory (ADF) tiene una puerta de enlace que se puede instalar en el servidor local. A continuación, puede crear una canalización para llevar los datos desde el servidor local hasta Azure Storage. Para usar Data Factory con un grupo de SQL dedicado, consulte Carga de datos en grupos de SQL dedicados.
3. Preparar los datos para la carga
Deberá preparar y limpiar los datos de la cuenta de almacenamiento antes de cargarlos. Puede preparar los datos mientras están en el origen y a medida que los exporta a archivos de texto o después de que estén en Azure Storage. Si trabaja con los datos en la parte inicial el proceso, será más fácil manejarlos.
Definición de las tablas
En primer lugar, se deben definir las tablas que se van a cargar en el grupo de SQL dedicado cuando se use la instrucción COPY.
Si usa PolyBase, tiene que definir tablas externas en el grupo de SQL dedicado antes de realizar la carga. PolyBase emplea tablas externas para obtener acceso y definir los datos en Azure Storage. Una tabla externa es similar a una vista de base de datos. La tabla externa contiene el esquema de tabla y apunta a los datos que se almacenan fuera del grupo de SQL dedicado.
Si define tablas externas debe especificar el origen de datos, el formato de los archivos de texto y las definiciones de tabla. Los artículos de referencia de sintaxis de T-SQL que necesitará son los siguientes:
Use la asignación siguiente de tipo de datos SQL al cargar archivos de Parquet:
| Tipo de Parquet | Tipo lógico de Parquet (anotación) | Tipo de datos de SQL |
|---|---|---|
| BOOLEAN | bit | |
| BINARY/BYTE_ARRAY | varbinary | |
| DOUBLE | FLOAT | |
| FLOAT | real | |
| INT32 | int | |
| INT64 | bigint | |
| INT96 | datetime2 | |
| FIXED_LEN_BYTE_ARRAY | binary | |
| BINARY | UTF8 | NVARCHAR |
| BINARY | STRING | NVARCHAR |
| BINARY | ENUM | NVARCHAR |
| BINARY | UUID | UNIQUEIDENTIFIER |
| BINARY | DECIMAL | Decimal |
| BINARY | JSON | nvarchar(MAX) |
| BINARY | BSON | varbinary(max) |
| FIXED_LEN_BYTE_ARRAY | DECIMAL | Decimal |
| BYTE_ARRAY | INTERVAL | varchar(max), |
| INT32 | INT(8, true) | SMALLINT |
| INT32 | INT(16, true) | SMALLINT |
| INT32 | INT(32, true) | int |
| INT32 | INT(8, false) | TINYINT |
| INT32 | INT(16, false) | int |
| INT32 | INT(32, false) | bigint |
| INT32 | DATE | date |
| INT32 | DECIMAL | Decimal |
| INT32 | TIME (MILLIS) | time |
| INT64 | INT(64, true) | bigint |
| INT64 | INT(64, false) | decimal(20,0) |
| INT64 | DECIMAL | Decimal |
| INT64 | TIME (MILLIS) | time |
| INT64 | TIMESTAMP (MILLIS) | datetime2 |
| Tipo complejo | LISTA | ntext |
| Tipo complejo | MAP | ntext |
Importante
- Los grupos dedicados de SQL no admiten actualmente tipos de datos Parquet con precisión MICROS y NANOS.
- Puede experimentar el siguiente error si los tipos no coinciden entre Parquet y SQL, o si tiene tipos de datos Parquet no admitidos:
HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: ClassCastException:... - No se admite la carga de un valor que no esté en el intervalo 0-127 en una columna tinyint para el formato de archivo Parquet y ORC.
Para obtener un ejemplo de creación de objetos externos, vea Creación de tablas externas.
Formato de los archivos de texto
Si utiliza PolyBase, los objetos externos definidos necesitan alinear las filas de los archivos de texto con la tabla externa y la definición de formato de archivo. Los datos de cada fila del archivo de texto deben alinearse con la definición de tabla. Para dar formato a los archivos de texto:
- Si los datos provienen de un origen no relacional, debe transformarlos en filas y columnas. Si los datos son de un origen relacional o no relacional, se deben transformar para alinearlos con las definiciones de columna de la tabla en la que va a cargar los datos.
- Debe dar formato a datos en el archivo de texto que se alineará con los tipos de datos y columnas en la tabla de destino. Si se desalinean los tipos de datos en los archivos de texto externos y la tabla del grupo de SQL dedicado, las filas se rechazarán durante la carga.
- Debe separar los campos en el archivo de texto con un terminador. Asegúrese de usar un carácter o una secuencia de caracteres que no se encuentre en los datos de origen. Use el terminador que especificó con la instrucción CREATE EXTERNAL FILE FORMAT.
4. Cargar los datos mediante PolyBase o la instrucción COPY
Es una práctica recomendada para cargar datos en una tabla de almacenamiento provisional. Las tablas de almacenamiento provisional le permiten controlar los errores sin interferir con las tablas de producción. Asimismo, una tabla de almacenamiento provisional también ofrece la posibilidad de usar la arquitectura de procesamiento paralelo del grupo de SQL dedicado para transformaciones de datos antes de insertar estos datos en tablas de producción.
Opciones de carga
Para cargar datos, puede usar cualquiera de estas opciones de carga:
- La instrucción COPY es la utilidad de carga recomendada, ya que permite cargar datos de forma fluida y flexible. La instrucción tiene muchas capacidades de carga adicionales que PolyBase no proporciona. En el tutorial de COPY de taxis de Nueva York encontrará un tutorial de ejemplo.
- Polybase con T-SQL requiere que se definan los objetos de datos externos.
- PolyBase y la instrucción COPY con Azure Data Factory (ADF) es otra herramienta de orquestación. Define una canalización y programa trabajos.
- Polybase con SSIS funciona bien cuando los datos de origen están en SQL Server. SSIS define las asignaciones de la tabla de origen a la de destino y también organiza la carga. Si ya dispone de paquetes SSIS, puede modificar los paquetes con los que vaya a trabajar con el nuevo destino del almacenamiento de datos.
- PolyBase con Azure Databricks transfiere los datos de una tabla para una trama de datos de Databricks o escribe datos de una trama de datos de Databricks en una tabla con PolyBase.
Otras opciones de carga
Además de PolyBase y la instrucción COPY, puede usar bcp o SqlBulkCopy API. El formato bcp carga datos directamente sin tener que pasar por Azure Blob Storage y está diseñado únicamente para cargas pequeñas.
Nota
El rendimiento de la carga de estas opciones es más lento que el de PolyBase y la instrucción COPY.
5. Transformar los datos
Mientras los datos estén en una tabla de almacenamiento provisional, podrá realizar las transformaciones que sean necesarias para la carga de trabajo. A continuación, lleve los datos a una tabla de producción.
6. Insertar los datos en tablas de producción
La instrucción INSERT INTO ... SELECT lleva los datos de la tabla de almacenamiento provisional a la tabla permanente.
Cuando diseñe un proceso ETL, intente ejecutar el proceso con una pequeña muestra de prueba. Intente extraer 1000 filas de la tabla a un archivo, muévalo a Azure y, a continuación, intente cargarlo en una tabla de almacenamiento provisional.
Soluciones de carga de asociados
Muchos de nuestros asociados tienen soluciones de carga. Para obtener más información, consulte una lista de nuestros asociados de soluciones.
Pasos siguientes
Para obtener instrucciones sobre la carga, vea Procedimientos recomendados de carga de datos.