Categories
Azure Synapse Analytics Dedicated SQL Pool (Data Warehouse) SQL Database

Azure Link For SQL

Azure Link For SQL enables near real time analytics over operational data in an external SQL Database.

Azure Link can now be used with SQL Server 2022 and Azure Database, and it works by replicating the specified tables into a Synapse SQL Pool automatically, in an incremental way. This allows to avoid creating a (possibly) complex ETL process to keep the Datawarehouse in sync.

This integration can be done with a SQL Server and with an Azure SQL Database. For SQL Server a Master key needs to be created both in the Datawarehouse and the SQL Server, and it is required to use a Self-Hosted integration runtime hosted in a machine with access to the server.

For Azure SQL Database the process is much simpler, but there’s a pricing limitation as Synapse Link can only be used in Databases with at least Standard tier (so no Basic tier) and 100 DTUs. The cost of this is 147.19 USD every month, so this limitation is something to keep in mind:

Minimum specification to be able to use Synapse link. Lowering the Data max size doesn’t have an impact on the cost.

Creating a Synapse Link for Azure SQL Database

In this case I’m going to do this example using the “Azure SQL Database” path as it’s simpler. So the first step is to create a Linked Service connecting to the Azure SQL Database as usual (in this case I’ve used System Managed Identity for authentication). As stated previously, the Database needs to be at least Standard Tier and have 100 DTUs and Managed Identity enabled.

After that we can create the Link:

During the process you can select in which SQL Pool do you want to copy, and which tables are going to replicated. It is important to keep in mind that only tables with a primary key can be used.

With the link connection created, you have the chance to further customize the process, renaming the tables, changing the distribution type and the structure type (which need to be set to ‘Heap’ whenever your table contains nvarchar(max) columns), and deciding whether you want to drop already existing tables or not. Once everything is set up the link connection can be published and started. After this, no further steps needs to be taken in Synapse. You can stop the link at any point and monitor the connection:

After this, every insertion, update or deletion in the source will be reflected in the SQL Pool in less than a minute.

Categories
Azure Synapse Analytics

Data Anonymization with Synapse Analytics  

Dataset anonymization is a transformation that can be a very beneficial part of the pre-processing stage when dealing with vast amounts of data in a project. It aims to transform an initial sensitive dataset by adding dummy content randomly but preserving data coherence in order to generate an equivalent one with non-real-world information.  

This process can be applied to a dataset before any employees develop or tests solutions around it, adding an extra layer of security to the development process. Once the product seems ready, the real dataset is connected instead for production purposes. 

Using this transformation in the develop and testing environments is considered a best practice. 

Azure Synapse Analytics 

There are a lot of tools available for big data analytics, but we will focus on Azure Synapse Analytics, from Microsoft. They self-define as a limitless analytics service that brings together data integration, enterprise data warehousing, and big data analytics. In the application main page, there are three main sections: Data, Develop and Integrate.  

The first one is where all the storage containers are located, from unstructured ones as Data Lakes to structured ones as SQL databases.

The Develop section is intended to create, store, and execute many kinds of data manipulation or analysis scripts in the cloud, using the advantages of Apache Spark. For example, Notebooks are an immensely powerful tool since they allow general purpose languages such as Python, Scala, or C# to be used along with standard data science libraries such as Pandas. 

Finally, the Integrate section is intended to automate complex processes related to the extraction, transformation, and storage of data. Pipelines are another powerful tool since they define sets of processing data elements connected in series, where the output of one element is the input of the next. Also, they can be set up in order to automatically execute periodically or as response to triggered predefined events. 

Dataset anonymization tool 

Let us review an implementation done in a Synapse Analytics Notebook using Python and Pandas. It reads an excel file from the Data Lake Storage Gen2 and creates another one with the result. The input file is required to have the following structure:  

  • First excel sheet: Contains all the column headers in the first row, and the values that do not require any modification. 
  • Second excel sheet: Contains headers and desired values from the columns that need to be anonymized but are independent from the others. 
  • The rest excel sheets: Each one contains a set of headers and desired values from columns whose values are dependent from each other. This is useful to maintain coherence in the resulting dataset. 

To give a more detailed explanation of the execution, an example dataset of 1000 rows related to business sales was generated using the Mockaroo web application. The following image is a preview of the sensitive data file that needs to be anonymized. 

Imagine the Global Sales Organization wants to create a series of dashboards related to this information. If they want to add an extra layer of security, these datasets should be anonymized before starting to develop any dashboard. The first step would be for a person with access to the real data, to modify the file to follow the required format.

Input file formatting

Since there is no need to apply any transformation to the Start Date and End Date fields, their values can remain on the first sheet.

In the second sheet, independent fields to be anonymized are included, in this case these are Company and Business Unit. These column cells contain all the possible values for the final anonymized dataset, therefore here is where sensitive data can be suppressed or substituted. Since each column is independent, they do not need to share size.

Note that these two fields could be dependent on each other, but for this example they are kept independent, otherwise they should be on the third or a higher index sheet.

The fields Market, Country Code and Currency fields are dependent on each other and therefore they are put in the third sheet. Notice that in dependent column sheets (dimensionals) all the columns must share size.

Same thing goes with Spends, Income and Profit fields since they are dependent on each other. Values are filled with non-real examples but considering that Income – Spends = Profit to maintain coherence.

Once the input file is correctly formatted and uploaded into the Data Lake Gen2, it is time to execute the Anonymization Dataset Tool Notebook.

Notebook manual execution

Once the Notebook is open, attach to a valid execution pool and after filling the first cell input and output file path parameters, press on run all.

In the second cell there are two dependencies being imported. Pandas, a standard data analysis library built on top of NumPy, and the Random module.

After this point, every cell follows a try-except structure to facilitate more comprehensible error messages. The next cell reads the input file and outputs the sheet names found.

Then, some Pandas data frames are created to store content related to each excel sheet. Also, the first sheet data is printed.

The next cell contains two methods to help detect formatting errors in the input file. The first is used to check that every column header has a matching header on the first sheet. The second function is used to verify dimensional sheets (index >= 3) format.

Now all the data frames previously created will be processed in order to clean, organize, and check their values. At first, the values sheet data (index = 2) is processed storing the name, size, and unique values for each column. The output in the image shows part of the information related to the Company field.

A similar process is applied to each dimensional sheet but allowing duplicate values. Some information related to the DimFinancial sheet can be seen.

The next and most important step is to incorporate the previously stored values into the fact data frame by iterating its rows and assigning a random value or group of values to each field. Also, a preview of this result is printed.

To finalize, the resulting data frame is written into an excel file and stored in the output file path set in the very first cell.

The output file has a single sheet, and it contains the freshly anonymized data.

Now this file can act as consistent dummy data to develop solutions around it without exposing real-world sensitive data.

Execution through a Pipeline

To automate the execution of this notebook, it can be executed using a Synapse Analytics Pipeline. Select a Synapse Notebook Activity and set up the correct input and output file paths parameters.

The example can be run by pressing the Debug button. If something goes wrong, the execution will end with a Failed status. The error message can be seen by hovering over the name and clicking on the message icon.

Otherwise, if everything goes as expected the Notebook will finish its execution with a Success status. 

In case there was the need to store the anonymized dataset in a SQL database, a previously created Pipeline pl_upload_CSV_to_DW can be helpful. It takes the input folder path, input csv file name, table schema and table name parameters. This can be added as another step of the previous Pipeline.

Notice that the parameters section shows some sort of code expression. This is because the main Pipeline is parameterized, and those portions of code are references to the main parameters. These can be displayed by clicking on the blank area, in the parameter section.

Of course, also the Notebook item parameters should change since we want to use the main ones.

This time, the value field contains a little more complex expression. This is because the main parameters include the folder paths and the file names as separate values, while the Notebook parameters take the full file paths as single parameters. Then, the expression is creating the full file path by concatenating folder path and file name in the right format.

Categories
Azure Synapse Analytics Dedicated SQL Pool (Data Warehouse)

How to connect from Excel to Azure Synapse Datawarehouse (Dedicated SQL Pool)

Select in the ribbon the option “Data”

Pic1

Select “Get Data”

Select the option “From Database” and then “From SQL Server Database”

Pic3

A small window will pop up, here you need to introduce the Server URL and the name of the Database even it is said to be optional.

Pic4

Go to “Database” and add the credentials given.

Pic5

Select one by one the tables you need.

Pic6

Once you select the table you can “Load” into Excel or choose “Transform Data” to apply changes in Power Query.

Pc7

Categories
Azure Synapse Analytics

Azure Synapse Analytics and telemetry data

Since the recently announced public preview of Azure Synapse data explorer, the possibilities of IoT analytics solutions are brought to a new level inside the Azure stack. See this article here.