Connect to Azure SQL Database and ETL Your Data with Astera Centerprise’s Built-in Connector

By |2020-12-30T07:22:59+00:00December 30th, 2020|

Azure SQL is a cloud-based relational database that has been built for scalability. The database’s speed, ease of use, and low downtime make it a popular option among enterprises dealing with large volumes of data.  

With Astera Centerprise’s built-in connectors, users can connect to Azure SQL database and integrate their data with multiple cloud platforms, databases, and data warehouses to improve their decision-making abilities. Astera Centerprise also features out-of-the-box transformations including Aggregate, Filter, and Lookup transformations that can make your data analysis-ready in no time. 

Understanding Astera Centerprise’s Azure SQL Connector 

Astera Centerprise’s Azure SQL connector allows seamless code-free connectivity to the cloud database. With Astera Centerprise’s drag-and-drop designeryou can read and write Azure SQL data, define schema for your database tables, and use custom expressions to clean, validate, and standardize your data to gain more insight and make quicker decisions.  

Astera Centerprise also gives users the freedom to delete, insert, update, or upsert data in their Azure SQL tables to maintain integrity and reduce the chance of errors or inconsistencies. With a parallel-processing engine and intuitive visual UI, Astera Centeprise can help enterprises create efficient data pipelines for any integration project regardless of volume.  

Reduce Time to Value by Automating Azure SQL Data Extraction  

The screenshot below illustrates how Astera Centerprise’s connector can be used to read data from an Azure SQL database. 

Using Azure SQL as a source

A dataflow showing data from an Azure SQL table being filtered

Here, we have connected to an Azure SQL database table called Sales Order Detailfiltered out data using a Filter transformation, and loaded this transformed data to an SQL Server table called Discounted Items. 

 

In order to do this, drag a Database Table Source object from the Toolbox and drop it on to the Designer. Next, selected SQL Server as the Data Provider and put in the credentials to access your Azure SQL database. 

Configuring an Azure SQL source

Configuring an Azure SQL source

 

Once the connection to the database is establishedpick the required source table from the drop-down list. Here, you can also select the read strategy for your table. For this example, we selected Full Load as our read strategy instead of Incremental Load to ensure that all of the data from the source table is extracted in one go. 

Selecting the Azure SQL table

Picking the Azure SQL table from which data needs to be extracted

Next, the Layout Builder screen appears with details about the schema of the table showing the datatypes, Db types, lengths of each field. You can also view information about the primary key for the table and see whether null values are allowed in any of the fields. 

Azure SQL table schema

The layout of the source table with information on data types and maximum lengths of each field

Once the Database Table Source object has been configured completely, you can apply transformations to modify or enrich your data according to business rules.  

Drag and drop the transformation of your choice from the Toolbox and map the fields from the Database Table Source to the transformation. Since we only wanted to load records with discounted prices in our final Database Table Destinationwe have used a Filter transformation.  

In the properties of the Filter transformation, we entered a custom expression to ensure that any records that do not meet our criteria are filtered out.  

Filter transformation

Properties of the Filter transformation

You can then map the output of the transformation object to more transformations or a destination object depending on your use case.  

Load Data to Azure SQL to Benefit from High Availability and Scalability 

Connecting to an Azure SQL database table to load data is equally easy. All you need to do is configure the source object, apply transformations if needed, and map it to your Azure SQL table. 

Using Azure SQL as a destination

Using Microsoft Azure SQL as a destination

 

In the image above, we are routing sales data from an Excel sheet and loading it on to two different Azure SQL database tables.  

Two different rules are specified in the properties of the Route transformation to differentiate payments made in cash and payments made using credit or debit cards. The Route transformation has two outputs for each of these rules and a Default output in case unchanged records are needed for any further processing.  

Route transformation properties

Properties of the Route transformation

For both destination tables i.e. Zone1CashPayment and Zone1CardPayment, the Azure SQL database table is configured with SQL Server as the Data Provider as follows 

Configuring the Azure SQL destination table

Configuring the Microsoft Azure SQL destination table

Mapping the records to the destination tables completes the Microsoft Azure integration for this example.  

Reduce Time to Value of Your Enterprise Data with Bidirectional Connectivity to Azure SQL  

Astera Centerprise’s out-of-the-box transformations and visual interface allow users to make efficient data pipelines to the cloud in no time. Automate Azure integration, consolidate data from heterogeneous sources, maintain an up-to-date database, and get analysis-ready data to improve business process efficiency 

Download the trial version of Astera Centerprise to experience the power of our connectors firsthand.