Using the Catalog in Aginity Pro


Catalog Overview

The query catalog is a unique capability of Aginity Pro allowing you to easily save, manage and use items stored in it. Let’s start with browsing the catalog.

The catalog is organized by any number of directories and sub-directories of queries as shown below.

Browse the Database Catalog

See also

Click Some link to best practice to see best practices for organizing catalogs.

Queries are given friendly names and can be used or accessed in several ways. The first way is to drag the catalog query into a query panel as shown in the two images below

Browse the Database Catalog

You can drag the query into the empty query panel

Browse the Database Catalog

After you drag the item it will present the full catalog query for edit or execution.

You can also edit queries within the catalogs. By right clicking on a query you can change the SQL itself, the description and folder options.

Browse the Database Catalog

By previewing a query you can see key metadata items and statistics about the catalog as well as take action on the item.

Browse the Database Catalog

Calling Catalog Items from SQL

One of the most innovative features of Aginity Pro is the ability to call catalog items from within SQL statements

The video and following examples below will give you basic syntax required to call a catalog item.


Single SQL SELECT Statement

In this example we have a single SQL SELECT statement in the catalog called OrderAverage.

This is the code in the Catalog.

1
2
SELECT AVG(LineTotal)
FROM sales.SalesOrderDetail

To call this from the query panel you would issue the following command. Note the highlighted reference to the object in the catalog.

1
2
SELECT *
FROM (@{OrderAverage}) a;

This is the code passed to the database.

1
2
3
4
5
SELECT *
FROM (
    SELECT AVG(lineTotal)
    FROM Sales.SalesOrderDetail
     ) a;

Using Catalog Item to Facilitate Relationships

In this example we will predefine a join in the catalog called ClientAddress and then use it in the context of another SQL statement.

This is the code in the Catalog.

1
2
3
 clients c
 JOIN addresses a
 ON c.clientsid = a.clientsid

To call this from the query panel you would issue the following command. Note the highlighted reference to the object in the catalog.

1
2
SELECT *
FROM @{ClientAddress};

This is the code passed to the database.

1
2
3
4
SELECT *
FROM clients c
JOIN addresses a
 ON c.clientsid = a.clientsid;

Creating and Reusing Calculations

In this example we will predefine a Calculations in the catalog called Residual and then use it in the context of another SQL statement.

This is the code in the Catalog.

1
 1-total

To call this from the query panel you would issue the following command. Note the highlighted reference to the object in the catalog.

1
2
3
SELECT item,
@{Residual}
FROM table;

This is the code passed to the database.

1
2
3
SELECT item,
1-total
FROM table;

Executing a Multi-Step script

In this example we will predefine a series of SQL statements in the catalog called DailyRun and then execute it by a single call. Any DML, (SELECT, CREATE, UPDATE, INSERT, DELETE) can be used.

This is the code in the Catalog.

1
2
3
CREATE TABLE xyz AS...;
CREATE TABLE abc AS...;
SELET * FROM xyz JOIN abc...;

To call this from the query panel you would issue the following command. Note the highlighted reference to the object in the catalog.

1
@{DailyRun}

This is the code passed to the database.

1
2
3
CREATE TABLE xyz AS...;
CREATE TABLE abc AS...;
SELECT * FROM xyz JOIN abc...;

Using Parameters in Catalog

In this example we will use a parameter within a catalog item called CitySelect.

This is the code in the Catalog. Note the highlighted line has the parameter in it.

1
2
3
SELECT *
FROM addresses
WHERE city = $city;

To call this from the query panel you would issue the following command. Note the highlighted reference to the object in the catalog.

1
@{CitySelect}

This is the code passed to the database.

1
2
3
SELECT *
FROM addresses
WHERE city = 'Chicago';

Note

Upon execution of the command Aginity Pro will ask you to supply a parameterized value. For more information on parameters see Using Parameters

Adding Items to Catalog

To add a new query to the catalog, highlight any part of a SQL statement(s), right click and select Save selection to catalog as shown below.

Browse the Database Catalog

Once you save you will be asked to provide metadata about the query you are adding.

Browse the Database Catalog

Note

It is important to fill out the description metadata as it will be used in searching. The more information you provide about the context of usage, key business terms and intended usage the better search results you will get.

You can also easily create new folders in the catalog or add SQL entries directly from the Catalog Browser by clicking the (+) to the right of the Catalog header.

Browse the Database Catalog

When adding a new folder you will be presented with a basic text box to create your folder as shown below.

Browse the Database Catalog

Import and Export Items from Catalog

You have the ability to Import and Export both your full catalog as well as any folder selected within the catalog.

Note

We highly recommend taking regular exports of your catalog to disk to prevent accidental loss of queries.

Export Steps

To Export your catalog start by either Right Clicking on a Catalog Folder or Using the Aginity Pro -> Export Catalog menu as shown below.

ExportMenu

Your first step is to save either an entire folder or a selection of items within a folder.

ExportMenu

The final step in exporting is to name the file and location you will save to as shown below.

ExportMenu

Import Steps

Importing is valuable for several reasons. One is to restore SQL catalog items you may have lost or more importantly the ability to collaborate with other SQL developers and share your SQL making your a more efficient and consistent SQL developer.

The first step is to Right Click on a folder you want to import to and Chooose Import Catalog as shown below.

ExportMenu

Note

You can also use the Aginity Pro Menu -> Import Catalog. The main difference between using these two capabilities is the context of where you are importing items. If you choose to import from the Aginity Pro menu you must set the context where to import. If you Right Click on a folder the context is set to that folder.

After choosing to import you will choose the file to import. By default Aginity catalog files have the extension .aginitypkg.

After choosing your file you can specify the folder to import to and what to import as shown below.

ExportMenu

Hit the Import button and your queries should be imported!

See also

Aginity also maintains a Github repository of Catalog queries to be used. These queries are either administrative queries for specific platforms, data engineering queries that do powerful transformations against generic data or analytic queries showing how to do cool things in SQL.

Access the repositories here