Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-551

Advanced Query Tool (AQT)

    XMLWordPrintable

    Details

    • Type: How To
    • Status: Obsolete
    • Affects Version/s: EXASolution 4.0.0, EXASOL 6.0.0, Exasol 6.1.0, EXASolution 5.0.0
    • Fix Version/s: None
    • Component/s: 3rd Party Tools
    • Labels:
      None
    • Solution:
      Hide

      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

      Show
      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

      Attachments

        Issue Links

        1. 01_aqt_odbc.jpg
          44 kB
          Franz Schwab
        2. 02_aqt_login.jpg
          51 kB
          Franz Schwab
        3. 03_aqt_browser.jpg
          87 kB
          Franz Schwab
        4. 04_aqt_editor.jpg
          88 kB
          Franz Schwab
        5. 05_aqt_resultset.jpg
          89 kB
          Franz Schwab
        6. 06_aqt_charting.jpg
          117 kB
          Franz Schwab
        7. 07_aqt_querybuilder.jpg
          75 kB
          Franz Schwab
        8. 08_aqt_compare_1.jpg
          82 kB
          Franz Schwab
        9. 09_aqt_compare_2.jpg
          132 kB
          Franz Schwab

          Activity

            People

            • Assignee:
              CaptainEXA Captain EXASOL
              Reporter:
              CaptainEXA Captain EXASOL
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: