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

By |2022-03-25T12:19:26+00:00December 30th, 2020|

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

Users can connect to the 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. 

Connect to Azure SQL Database with Astera Centerprise 

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 the 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 errors or inconsistencies. With a parallel-processing engine and intuitive visual UI, Astera Centeprise can help enterprises perform ETL using azure SQL connector and 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. 

Azure SQL database 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. 

Drag a Database Table Source object from the Toolbox and drop it on to the Designer. Next, select SQL Server as the Data Provider and put in the credentials to access your Azure SQL database. 

Configuring an Azure SQL database 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 database table

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

Next, the Layout Builder screen appears with details about the table’s schema 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 database 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 database as a destination

Using Microsoft Azure SQL as a destination

 

We are routing sales data from an Excel sheet and loading it onto two different Azure SQL database tables in the image above.  

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 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

Configuring the Azure SQL database 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 Database 

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.

Related Articles

Establish Seamless Connectivity to Oracle Database Using Astera Centerprise’s Built-in...

Today, as crucial enterprise data is dispersed across multiple locations, Astera is constantly increasing its range of out-of-the-box connectors to...
read more

Connect to the Vertica Database With Astera Centerprise

Vertica is a scalable analytics platform that can optimize both load and query performance. Vertica’s performance, high availability architecture, and...
read more

Connect to Amazon Aurora Database with Astera Centerprise

AWS Aurora Database is a fully managed relational database built for the cloud and compatible with MySQL. It is widely...
read more