Extending Data Virtualization Capabilities with Astera Query Language (AQL)

By | 2019-09-13T10:25:44+00:00 July 23rd, 2019|

What is AQL?

Astera Query Language (AQL) is an object-relational query language that enables users to access set of database tables from virtual database sources in Astera Data Virtualization. AQL supports object referencing within tables. This means that objects are nested within objects and can be used to call subsets of data into a dataflow.

Similar to the SELECT statement used in SQL, AQL uses commands and aggregate functions for data retrieval. However, the main difference lies in its ability to automate complex joins through dot notation, which simplifies query-building for developers.

AQL powers the Astera Virtual Database engine – a database provider that can be queried and used as a source in Astera Data Virtualization. The primary function of this database is to retrieve data when requested by Astera Query Engine, running on the same or different server.

Here’s how users can leverage the data virtualization capabilities of Astera Data Virtualization using AQL:

  1. It eliminates the need to write complicated queries as AQL builds joins automatically based on the relationships defined in the virtual data model.
  2. There are no constraints on the level of nesting of joins using AQL.
  3. Through AQL, you can connect to non-database sources as well, such as web services, cloud applications, Excel, delimited, email, PDF, etc.

How to Use AQL?

AQL can be used in two ways:

1. via the Multi-Table Query Source

A Multi-Table Query represents the virtual database in a relational/hierarchical view using AQL. You can call the virtual data model by using this object in a dataflow to query views. Drag -and drop the element from the toolbox and select Astera Data Model as the database provider.

Fig.1: Configuring database connection

Fig.1: Configuring database connection

Fig.2: Selecting the root table

Fig.2: Selecting the root table

Configure the source, point to the deployed VDM and select the root object in the drop-down options. The tree-like structure displays the records in the Multi-Table Query object in child-to-parent order. Users can drag and drop fields from any of the tables in the tree.

AQL is automatically generated behind the scenes by the Astera Query engine. The commands can be viewed in the Query Preview window and edited using the Query Editor window.

Fig.3: Viewing AQL in virtualization Query Preview Window

Fig.3: Viewing AQL in Query Preview Window

Through the Multi-Query Table source, you can use AQL for different functionalities including:

Filter: It allows the user to filter records based on the pre-specified criteria.

Where Clause: Using a where clause, the user can write a query to retrieve records from the table.

Sort Order: Sort records in ascending or descending order using this command.

To get a step-by-step overview of the Multi-Table Query object, go through this article.

Fig.4: Different applications of AQL in virtualization

Fig.4: Different applications of AQL

2. via the Query Editor Window

By accessing the query window in Astera Data Virtualization, the user can write an AQL query from scratch. Go to New > Files > Query to access the Query Editor.

Common AQL Commands

Let’s look at some of the common SELECT AQL commands used in Astera Data Virtualization:

The SELECT Query

The SELECT query retrieves records from a database. AQL generates dataflows at runtime to fetch data from sources in the virtual database based on the mapped fields and specified criteria.

The data is returned in the form of a table.

Syntax:

SELECT table_name.column1, table_name.column2,table_name.columnN

FROM table_name

Column1, column2, and columnN are the fields from which records are being fetched.

Let’s take an example: consider an Orders table with the following records:

Fig.5: A sample database table

Fig.5: A sample database table

The following query will fetch data from OrdersID, EmployeeID, OrdersData, RequiredData, and ShippedDate.

Fig.6: AQL query to fetch data from the ‘Orders’ table

Fig.6: AQL query to fetch data from the ‘Orders’ table

It will return the following result:

Fig.7: The resultant table

Fig.7: The resultant table

The JOIN Query

The AQL JOIN command combines records from multiple tables in a database using relationship alias between the parent and the child table.

Syntax:

SELECT table_name_(child_table).alias.column(parent_table)

FROM table_name

Where alias is the relationship between two entities.

Consider an example in which a virtual data model has multiple tables. We want to join records from the Customers and Orders table using AQL.

Fig.8: An example of virtual data model with multiple tables

Fig.8: An example of a virtual data model with multiple tables

Fig.9: Orders table

Fig.9: Orders table

Fig.10: Customers table

Fig.10: Customers table

Using the SELECT statement, we will join the CompanyName and ContactName field from the Customers table with the Orders table.

Fig.11: AQL query for the JOIN command

Fig.11: AQL query for the JOIN command

The resultant table will look like this:

Fig.12: Resultant table after executing a JOIN query

Fig.12: Resultant table after executing a JOIN query

The ORDER BY Query

This query is used to sort the records in ascending or descending order based on the columns selected. By default, the query sorts the result in ascending order. You can arrange more than one column using this command.

Syntax:

SELECT table_name.column1, table_name.column2, table_name.columnN

FROM table_name

ORDERBY column [asc | desc]

Let’s take the example of the Products table in the above-mentioned virtual data model. The following query will sort the table with respect to the grouped columns ProductID and CategoryID in ascending order.

Fig.13: AQL query for the ORDER BY command

Fig.13: AQL query for the ORDER BY command

The resultant table is shown below:

Fig.14: Resultant table after executing an ORDER BY query

Fig.14: Resultant table after executing an ORDER BY query

What You Can Do With AQL

AQL comes into action in several places, for example:

  1. When Multi-Table Query is used for accessing data from a virtual database. However, in this scenario AQL statements are self-generated.
  2. When users write custom AQL statements manually to query a virtual database.
  3. When the Query Editor window is accessed from the data source browser, users can write AQL statements to view the data present in a virtual database.

To experience firsthand how AQL can be used in Astera Data Virtualization, sign up for a trial version.