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:
- It eliminates the need to write complicated queries as AQL builds joins automatically based on the relationships defined in the virtual data model.
- There are no constraints on the level of nesting of joins using AQL.
- 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.
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.
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.
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.
SELECT table_name.column1, table_name.column2,table_name.columnN
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:
The following query will fetch data from OrdersID, EmployeeID, OrdersData, RequiredData, and ShippedDate.
It will return the following result:
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.
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.
Using the SELECT statement, we will join the CompanyName and ContactName field from the Customers table with the Orders table.
The resultant table will look like this:
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.
SELECT table_name.column1, table_name.column2, table_name.columnN
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.
The resultant table is shown below:
What You Can Do With AQL
AQL comes into action in several places, for example:
- When Multi-Table Query is used for accessing data from a virtual database. However, in this scenario AQL statements are self-generated.
- When users write custom AQL statements manually to query a virtual database.
- 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.