¿Qué es Azure Synapse Analytics?

Azure Synapse Analytics es un servicio de análisis ilimitado que reúne la integración, almacenamiento y análisis de datos, dando libertad de consultarlos como quiera, usando recursos sin servidor o dedicados, a gran escala.

Pool SQL Serverless

Synapse Analytics proporciona un Pool SQL Serverless que permite consultar los datos del data lake usando recursos sin servidor.

El Pool SQL serverless de Azure Synapse Analytics es un sistema de procesamiento de datos distribuido creado para datos a gran escala y funciones computacionales. Su uso proporciona las siguientes ventajas:

  • Analizar macrodatos en segundos o minutos, según la carga de trabajo.
  • Alta confiabilidad y alto índice de éxito para grandes conjuntos de datos debido a la tolerancia a errores integrada en la ejecución de consultas.
  • Un punto de conexión predeterminado en cada área de trabajo de Azure Synapse, es decir, no hay que configurar ninguna infraestructura ni mantener ningún clúster, directamente se puede empezar a consultar datos al crear el área de trabajo.
  • Solo se cobra por los datos procesados de las consultas ejecutadas, siguiendo la filosofía de pago por uso.

Conviértete tus datos en información clave para tu negocio con Azure Synapse

Escalabilidad limitada, conclusiones eficaces y seguridad inigualable son algunas de las características de Azure Synapse. Explora este servicio y da respuesta a cómo podría ayudarte a tomar decisiones sólidas para tu negocio, realizando una prueba concepto de 10 jornadas sin coste.

Quiero una prueba gratuita de Azure Synapse

Consultando Ficheros del Data Lake

Una vez conocidas las tecnologías que se utilizan y sus ventajas, se mostrarán ejemplos prácticos de como consultar distintos tipos de ficheros a través de consultas T-SQL con Azure Synapse Analytics.

pool sql serverless

Fichero CSV

Los ficheros CSV son un ejemplo sencillo de cómo realizar estas consultas serverless para analizar los datos.

Se accede desde el espacio de trabajo de Synapse Analytics a nuestro data lake.

synapse analytics

Se hace click derecho en el fichero csv que se quiere consultar y se elige la opción de “select top 100 rows” abriendose un script que utiliza la cláusula “openrowset” para consultar los datos de una localización, formato y parseador especificos.

azure data lake storage

SELECT TOP 100 * FROM

    OPENROWSET(

        BULK ’https://datalakeblogsynapse.dfs.core.windows.net/datalake/Raw/CSV

    files/file.csv’,

        FORMAT = ’CSV’,

        PARSER_VERSION=’2.0′

    ) AS [result]

main window sample
main window sample 2

El script base se puede modificar para consultar todos los datos de una carpeta cambiando la localización del archivo, permitiendo el uso de wildcards (*).

SELECT

C1 AS widget_debug,

C2 AS widget_window_title,

C3 AS widget_window_name,

C4 AS [widget_window_width],

C5 AS [widget_window_height],

C6 AS [widget_image_src],

C7 AS [widget_image_name],

C8 AS [widget_image_hOffset],

C9 AS [widget_image_vOffset],

C10 AS [widget_image_alignment],

C11 AS [widget_text_data],

C12 AS [widget_text_size],

C13 AS [widget_text_style],

C14 AS [widget_text_name],

C15 AS [widget_text_hOffset],

C16 AS [widget_text_vOffset],

C17 AS [widget_text_alignment],

C18 AS [widget_text_onMouseUp]

FROM

    OPENROWSET(

        BULK ’https://datalakeblogsynapse.dfs.core.windows.net/datalake/Raw/

    CSV files/*.csv’,

        FORMAT = ’CSV’,

        PARSER_VERSION=’2.0′

    ) AS [result]

Analizando el Data Lake y el Pool SQL Serverless de Synapse
Analizando el Data Lake y el Pool SQL Serverless de Synapse

Además, para la exploración de los ficheros segmentados en carpetas se puede utilizar la clausula where con las funciones filepath y filename, permitiendo una exploración muy rápida ya que se accede directamente a esos directorios.

SELECT

result.filename() as filename,

result.filepath(1) as filepath,

C1 AS widget_debug,

C2 AS widget_window_title,

C3 AS widget_window_name,

C4 AS [widget_window_width],

C5 AS [widget_window_height],

C6 AS [widget_image_src],

C7 AS [widget_image_name],

C8 AS [widget_image_hOffset],

C9 AS [widget_image_vOffset],

C10 AS [widget_image_alignment],

C11 AS [widget_text_data],

C12 AS [widget_text_size],

C13 AS [widget_text_style],

C14 AS [widget_text_name],

C15 AS [widget_text_hOffset],

C16 AS [widget_text_vOffset],

C17 AS [widget_text_alignment],

C18 AS [widget_text_onMouseUp]

FROM

    OPENROWSET(

        BULK ’https://datalakeblogsynapse.dfs.core.windows.net/datalake/Raw/*/*.csv’,

        FORMAT = ’CSV’,

        PARSER_VERSION=’2.0′

    ) AS [result]

WHERE result.filepath(1)=’CSV files’ and result.filename() = ‘file.csv’

widget debug name

Fichero JSON

Trabajar con ficheros JSON puede ser algo más tedioso ya que se debe tener claro el esquema del fichero.

Se procede a realizar la consulta del fichero con la consulta que genera por defecto al hacer click derecho, “select top 100”.

select top 100 pool sql

SELECT TOP 100 jsonContent

FROM

    OPENROWSET(

      BULK ’https://datalakeblogsynapse.dfs.core.windows.net/datalake/Raw

   /JSON files/file.json’,

      FORMAT = ‘CSV’,

      FIELDQUOTE = ‘0x0b’,

      FIELDTERMINATOR =’0x0b’,

      ROWTERMINATOR = ‘0x0b’

    )

    WITH (varchar(MAX)) AS [result]

Json content

Como se observa, es probable que haya que realizar bastantes modificaciones al script ya que muestra el contenido del json pero no da el formato que realmente que se quiere.

Puesto que el fichero es un array de jsons utilizamos la sentencia cross apply openjson para acceder a cada uno de los nodos que están debajo del nodo widgets (En caso de no tener un array de jsons no se debería utilizar cross apply). Además, utilizando la cláusula with se puede aplanar el json con el esquema especificado.

SELECT

    b.*

FROM

    OPENROWSET(

        BULK ‘https://datalakeblogsynapse.dfs.core.windows.net/datalake/file.json’,

        FORMAT = ‘CSV’,

        FIELDQUOTE = ‘0x0b’,

        FIELDTERMINATOR =’0x0b’,

        ROWTERMINATOR = ‘0x0b’

    )

    WITH (

        jsonContent varchar(MAX)

    ) AS [result]

        CROSS APPLY openjson(jsonContent, ‘$.”widgets”‘)

        WITH (

        [widget_debug] nvarchar(50) ‘$.widget.debug’,

        [widget_window_title] nvarchar(50)  ‘$.widget.window.title’,

        [widget_window_name] nvarchar(50) ‘$.widget.window.name’,

        [widget_window_width] nvarchar(50) $.widget.window.width’,

        [widget_window_height] nvarchar(50) ‘$.widget.window.height’,

        [widget_image_src] nvarchar(50) ‘$.widget.image.src’,

        [widget_image_name] nvarchar(50) ‘$.widget.image.name’,

        [widget_image_hOffset] nvarchar(50) ‘$.widget.image.hOffset’,

        [widget_image_vOffset] nvarchar(50) ‘$.widget.image.vOffset’,

        [widget_image_alignment] nvarchar(50) ‘$.widget.image.alignment’,

        [widget_text_data] nvarchar(50) ‘$.widget.text.data’,

        [widget_text_size] nvarchar(50) ‘$.widget.text.size’,

        [widget_text_style] nvarchar(50)’$.widget.text.style’,

        [widget_text_name] nvarchar(50)       ‘$.widget.text.name’,

        [widget_text_hOffset] nvarchar(50)’$.widget.text.hOffset’,

        [widget_text_vOffset] nvarchar(50)’$.widget.text.vOffset’,

        [widget_text_alignment] nvarchar(50) ‘$.widget.text.alignment’,

        [widget_text_onMouseUp] nvarchar(50) ‘$.widget.text.onMouseUp’) b

sample widget
Analizando el Data Lake y el Pool SQL Serverless de Synapse

De esta forma, utilizando un poco de código T-SQL y conociendo el esquema del json, se puede aplanar fácilmente el fichero.

Usos del Pool SQL Serverless

Utilizar el Pool SQL serverless para explorar los datos del data lake es una gran opción ya que ahorra bastante tiempo, pero, además, el Pool SQL serverless se puede utilizar como origen de datos para pipelines ETL o como origen de dimensiones y hechos sustituyendo la arquitectura del data warehouse por un lakehouse.

Analizando el Data Lake y el Pool SQL Serverless de Synapse
Analizando el Data Lake y el Pool SQL Serverless de Synapse

Ilustración 1: Arquitectura DataLake

architecture lakehouse

Ilustración 2. Arquitectura LakeHouse

Consejo al trabajar con Data Lake

Si los ficheros del data lake tienen una gran cantidad de filas, es recomendable transformar y aplanar estos ficheros a formato Parquet ya que proporcionan un gran rendimiento y una alta compresión debido a la representación columnar y el tipo de compresión snappy. Esta transformación también puede llevarse a cabo utilizando el Pool SQL serverless a través de la funcionalidad de “Create external table as select” (CETAS).

Analizando el Data Lake y el Pool SQL Serverless de Synapse

Para realizar esta transformación es necesario que en el Pool SQL serverless haya una base de datos adicional a la de master puesto que se necesitará crear procedimientos almacenados, external data sources y external file formats.

Analizando el Data Lake y el Pool SQL Serverless de Synapse

Para ello se debe crear:

  • La base de datos que permitirá realizar transformaciones a los ficheros.

         CREATE DATABASE Staging

  • El external data source apuntando al data lake que se utilizará.

        CREATE EXTERNAL DATA SOURCE [synapse_datasource] WITH (LOCATION =          N’https://datalakeblogsynapse.blob.core.windows.net/datalake/’)

        GO

  • El external file format que se utilizará para crear la tabla externa con el formato requerido. En este caso, se utilizará Parquet debido a las ventajas comentadas anteriormente.

        CREATE EXTERNAL FILE FORMAT [parquet_snappy] WITH (FORMAT_TYPE =            PARQUET, DATA_COMPRESSION =N’org.apache.hadoop.io.compress.SnappyCodec’)

        GO

Utilizando “create external table as select” (CETAS) se puede crear el fichero Parquet especificando en la cláusula with la localización que se utilizará para almacenar la tabla externa (fichero parquet), la referencia al data lake (external data source) y el tipo de datos utilizando el external file format generado anteriormente.

Además, se debe modificar la consulta del select para que utilice el data_source creado y cambiar el bulk para que utilice la ruta relativa a ese data_source.

BULK ‘Raw/JSON files/file.json’,

DATA_SOURCE = ‘synapse_datasource’

De esta forma, para transformar el fichero json el script quedaría de la siguiente forma:

CREATE EXTERNAL TABLE dbo.widgets

WITH (

                LOCATION = ‘Silver/file.parquet’,

                DATA_SOURCE = synapse_datasource,

                FILE_FORMAT = parquet_snappy

        )

        AS

SELECT

    b.*

FROM

    OPENROWSET(

        BULK ‘Raw/JSON files/file.json’,

                DATA_SOURCE = ‘synapse_datasource’,

        FORMAT = ‘CSV’,

        FIELDQUOTE = ‘0x0b’,

        FIELDTERMINATOR =’0x0b’,

        ROWTERMINATOR = ‘0x0b’

    )

    WITH (

        jsonContent varchar(MAX)

    ) AS [result]

    CROSS APPLY openjson(jsonContent, ‘$.”widgets”‘)

        WITH (

        [widget_debug] nvarchar(50) ‘$.widget.debug’,

        [widget_window_title] nvarchar(50)  ‘$.widget.window.title’,

        [widget_window_name] nvarchar(50) ‘$.widget.window.name’,

        [widget_window_width] nvarchar(50) $.widget.window.width’,

        [widget_window_height] nvarchar(50) ‘$.widget.window.height’,

        [widget_image_src] nvarchar(50) ‘$.widget.image.src’,

        [widget_image_name] nvarchar(50) ‘$.widget.image.name’,

        [widget_image_hOffset] nvarchar(50) ‘$.widget.image.hOffset’,

        [widget_image_vOffset] nvarchar(50) ‘$.widget.image.vOffset’,

        [widget_image_alignment] nvarchar(50) ‘$.widget.image.alignment’,

        [widget_text_data] nvarchar(50) ‘$.widget.text.data’,

        [widget_text_size] nvarchar(50) ‘$.widget.text.size’,

        [widget_text_style] nvarchar(50)’$.widget.text.style’,

        [widget_text_name] nvarchar(50) ‘$.widget.text.name’,

        [widget_text_hOffset] nvarchar(50)’$.widget.text.hOffset’,

        [widget_text_vOffset] nvarchar(50)’$.widget.text.vOffset’,

        [widget_text_alignment] nvarchar(50) ‘$.widget.text.alignment’,

        [widget_text_onMouseUp] nvarchar(50) ‘$.widget.text.onMouseUp’) b

Al ejecutar la creación de la tabla externa, se puede revisar que se ha creado el fichero Parquet en la ruta especificada.

Analizando el Data Lake y el Pool SQL Serverless de Synapse

Una vez creado el fichero se podría borrar la tabla externa ya que el fichero parquet seguirá en el data lake.

DROP EXTERNAL TABLE [dbo].[widgets]

Además, este fichero Parquet se puede explorar de la misma manera que se mostró con el formato csv.

Rendimiento de ficheros Parquet vs ficheros json

A continuación, se analiza el rendimiento en un proyecto real para ver la mejora que supone transformar los ficheros de formato JSON a formato Parquet snappy.

Analizando el Data Lake y el Pool SQL Serverless de Synapse

Synapse Analytics proporciona un Pool SQL Serverless que permite consultar los datos del data lake usando recursos sin servidor.

El Pool SQL serverless de Azure Synapse Analytics es un sistema de procesamiento de datos distribuido creado para datos a gran escala y funciones computacionales. Su uso proporciona las siguientes ventajas:

  • Analizar macrodatos en segundos o minutos, según la carga de trabajo.
  • Alta confiabilidad y alto índice de éxito para grandes conjuntos de datos debido a la tolerancia a errores integrada en la ejecución de consultas.
  • Un punto de conexión predeterminado en cada área de trabajo de Azure Synapse, es decir, no hay que configurar ninguna infraestructura ni mantener ningún clúster, directamente se puede empezar a consultar datos al crear el área de trabajo.
  • Solo se cobra por los datos procesados de las consultas ejecutadas, siguiendo la filosofía de pago por uso.

Conviértete tus datos en información clave para tu negocio con Azure Synapse

Escalabilidad limitada, conclusiones eficaces y seguridad inigualable son algunas de las características de Azure Synapse. Explora este servicio y da respuesta a cómo podría ayudarte a tomar decisiones sólidas para tu negocio, realizando una prueba concepto de 10 jornadas sin coste.

Quiero una prueba gratuita de Azure Synapse
0 Shares:
Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

You May Also Like
Implementando Azure databricks
Leer más

Implementando Databricks (SaaS) en Azure para evitar accesos no autorizados a los datos

Cuando trabajamos con Azure o la nube en general, tenemos que ser muy cuidadosos con la seguridad de nuestros datos y más si son datos confidenciales o que entran en el ámbito de la RGPD, por lo que en este articulo os damos unas pautas a seguir para trabajar con datos utilizando el servicio de Databricks que nos ofrece Azure, evitando que estos viajen por fuera de la red privada.
Leer más

Extended support. Pan para hoy, hambre para mañana.

Este año 2020 va a representar un reto importante para muchas organizaciones desde el punto de vista de actualizaciones/renovaciones. El soporte extendido de SQL Server 2008 terminaba el pasado 9 de Julio de 2019 y hoy 14 de Enero de 2020 termina el de Windows Server 2008 y 2008 R2. Muchas empresas son conscientes del fin de soporte y a pesar de ello, aún no tienen prevista la migración por lo que probablemente deba ser abordada en breve y con cierta urgencia (escanario ideal).