[SOL-551] Advanced Query Tool (AQT) Created: 27.04.2017  Updated: 03.01.2020  Resolved: 03.01.2020

Status: Obsolete
Project: Solution Center
Component/s: 3rd Party Tools
Affects Version/s: EXASolution 4.0.0, EXASOL 6.0.0, Exasol 6.1.0, EXASolution 5.0.0
Fix Version/s: None

Type: How To
Reporter: Captain EXASOL Assignee: Captain EXASOL
Labels: None

Attachments: JPEG File 01_aqt_odbc.jpg     JPEG File 02_aqt_login.jpg     JPEG File 03_aqt_browser.jpg     JPEG File 04_aqt_editor.jpg     JPEG File 05_aqt_resultset.jpg     JPEG File 06_aqt_charting.jpg     JPEG File 07_aqt_querybuilder.jpg     JPEG File 08_aqt_compare_1.jpg     JPEG File 09_aqt_compare_2.jpg    
Issue Links:
Using
Solution:

Note: This solution is no longer maintained. Please find the latest information in our documentation portal:

https://docs.exasol.com/connect_exasol/sql_clients/aqt.htm

----------------------------------------------------------------------------------------------------------------------------------------

Advanced Query Tool

AQT is database client for windows, which can be used for querying a multitude of database management systems, e.g. EXASOL.

In addition to a database browser and the possibility to execute SQL statements, AQT offers (amongst other features) a query builder, a chart editor as well as the possibility to compare tables or rather resultsets from queries.

Installation

This tutorial is written for version 9.0.6. The latest version can be found on the AQT download website.
After downloading the zip file you have to unpack the archive and to start the installer. Afterwards you install the ODBC driver for EXASOL which can be found in our download area. Subsequently an ODBC data source has to be set up.
01_aqt_odbc.jpg

After starting AQT you have to select the ODBC data source and to log in. Having already entered the login data while creating the odbc data source, it is not necessary to enter username and password again.
02_aqt_login.jpg

Basic functions

After logging in, you find a 3-column view. Database schemas are displayed on the left, therein contained tables and views in the middle, and the corresponding columns on the right.
03_aqt_browser.jpg

In the icon bar at the top you can reach the SQL editor by clicking on "SQL". There, you can write queries. An auto-completion and a query editor which generates queries with a few mouse clicks are available.
04_aqt_editor.jpg

Clicking on "Run" the query is started and the result set appears.
05_aqt_resultset.jpg

Chart editor

Also, charts can be generated directly from SQL queries. The chart shown here was derived from the adjacent query. If you are in the SQL editor, with a click on "Chart" in the icon bar a graph belonging to the query can be created. The following settings are important:

  • "Data Columns: data for the Y-axis
  • "Label Column": labeling for the X-axis
  • "Chart Type": chart type (here: "bar")

 

SELECT
	n_name,
	count_customers
FROM
		(
			SELECT
				COUNT(*) count_customers,
				c_nationkey
			FROM
				tpc.customer
			WHERE
				c_custkey < 200
			GROUP BY
				c_nationkey
		)
	JOIN
		tpc.nation
	ON
		c_nationkey = n_nationkey
ORDER BY
	count_customers DESC;

Query builder

The SQL query from the section "Basic functions" can also be created graphically with the query builder.
First, you click on "Query Builder" in the icon bar, selects the tables and views involved and then confirms with "OK".
After that, it is possible to formulate the join conditions using drag-and-drop. Then you chooses the values ​​to be selected clicking the corresponding checkboxes. In the sub-menus, join types, where conditions and other options can be set amongst others.
Clicking on "Run" in the icon bar, you send the query.
07_aqt_querybuilder.jpg

Comparing result sets

With AQT, two result sets can be compared. By clicking on "Compare" in the icon bar you can access the configuration menu, the result sets to be compared can be set and whether they should come directly from a query or from a table. It is also important to specify a "Unique Key". With "Generate Script" you can also generate SQL queries, which modifies one of the two tables so that it is equivalent to the other (with INSERT, UPDATE, and DELETE commands).
08_aqt_compare_1.jpg

By clicking on "Compare", the compare view opens. There, lines are marked differently, depending on how they vary:

  • row only existing in left table
  • row only existing in right table
  • row existing in both tables, but with different values
  • row being the the same in both tables

 

Summary

AQT is particularly interesting when you want to work across databases, since a variety of manufacturers and products are supported. Because of its wide range of features you can use it for various tasks instead of using several tools.

AQT is available in a Standard Edition and Extended Edition, the latter containing mainly features for administration. A complete overview of the various features can be found here: http://advancedquerytool.com/features.html

Category 1: 3rd Party Tools - Vizualization & Analytics
Category 2: Clients, Interfaces & Drivers - ODBC
Generated at Wed Sep 23 02:45:02 CEST 2020 using Jira 7.13.13#713013-sha1:4c0f5f5e3383570393fbbf3d2fd5de1eb4057c36.