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.