Using the Query Panel in Aginity Team

You will spend most of your time in the Query Panel writing SQL and saving it to your catalog in Aginity Team. The video below will give you an overview of creating your first query


Writing your First Query

Each tab in the Aginity Team application can be set to different database connections. Once you establish that connection as shown below you can use the query panel like a text editor using SQL syntax specific to that database platform.

1st Query

Once you write your code in Aginity Team you have options to run it using the following scope:

1st Query
Execution Type Description
Run at cursor When executing the query it will run the statement where the cursor is place up until the semicolon
Run all parts of the script in a sequence Each statement in the window will be run sequentially and the output of each statement will be shown in the output and if the statement was a SELECT then a results panel will be displayed
Run entire script as a batch  
Abort Query After executing a query you can direct Aginity Team to send a request to the database to cancel the query.

Executing to a File

In version .17 of Aginity Pro we released the ability to use our editor commands to spool the results of a SQL statement(s) to a file(s).

To do this you must issue the following commands

  1. Use the #+sql command to tell the editor you will be writing SQL in this section.
  2. You will then use the :output file command to tell the editor you will spool the results to an output file
  3. In the table below is a series of parameters and descriptions to be used when spooling
  4. Use the #+begin statement to tell the editor you from here until the #+end command you will have SQL to execute.
  5. Write your SQL
  6. End with #+end
  7. Use the Run commands as you would any normal SQL statement as described in the Run Scope section above.

The code below is an example you can use to build off of.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
    -- This will write the SQL statement between #+begin and #+end to the file specified.

    #+sql
     :output file
        :path '/users/jschlitt/Downloads/jeff1'
        :delimiter ','
        :null_value 'NULL'
        :quote_char '"'
        :overwrite true
    #+begin
    select * from wb_pro_marketing_list
    where emaildomain = 'aginity.com'
    #+end

Note

We only support today the file type of csv. You can still change the delimiter but all files with have a csv extension

The table below illustrates the current values accepted in execute to file commands

Parameter Options
path On Mac this should be in the format “/dir/dir/filename”. On Windows it should be in the format “drive:directoryfilename”.
delimiter You can use any value but we recommend , or | or ^ or t for (tab)
null_value enter a character field for null typically ‘’ (empty string) or ‘NULL’
quote_char enter how to enclose character string typically the quotation (“)
overwrite whether or not to allow overwrite if file exists: true or false

Note

At Aginity, we produce lists for marketing purposes using a single catalog item called “Product Daily Marketing Feeds” that execute twenty five queries and spool the results to twenty five csv files which we upload to our marketing engine all with the click of the Run button in Aginity Team.


Using Parameters in your Query

Parameters can be used in Aginity Team by using the $ in any part of a SQL statement. When you execute a query the Aginity Team application will ask you to fill in the value of the parameter before it sends the query to the database.

1
2
3
4
5
6
--sample parameter query

select *
from landing_dim_customer
where gender = $fill_in_gender_at_runtime
limit 100;

You will have datatype choices at runtime as shown in the table below.

Parameter Data Type Action Taken
As Is Aginity Team will send the value exactly as written with no casting. This is often used when using the parameter to fill in database table or column names. See example 2 below.

Auto-Complete Capabilities

In Aginity Team we have implemented auto-complete on several database platforms and will be deploying across all over time. The auto-complete capability has three capabilities.

  1. Display any words on the editor screen from above where your current cursor is
  2. Display SQL Syntax like SELECT, WHERE, FROM all the way to database platform specific functions like CASE, DATEDIFF or CURRENT_DATE()
  3. Display the database, schema, table, column as you are typing
1st Query

Hint

To initiate just the database browser which shows database objects plus snippets hit the Ctrl+Space button otherwise as you type if auto-complete is supported it will automatically popup.

Note

Currently we support auto-complete on Netezza, Redshift and Snowflake

Snippet support

We have pre-wired some special snippets within the editor as well to make writing SQL simpler. The table below details those snippets that are currently available.

Snippet Action
selall will generate generic SELECT FROM
selwh will generate generic SELECT FROM WHERE
upd will generate an UPDATE statement
ins will generate an INSERT statement
del will generate a DELETE statement
expf will generate a generic export to file statement

Common Actions in Query Panel

We will highlight common functions heavily used in the editor here. There is a complete list of available editor functions which can be accessed by hitting the Command+Shift+P on Mac or the Ctrl+Shift+P button within the editor or from the View->Command Palette menu item.

rename tab

Renaming Tabs

You can rename the query tabs by double clicking and typing in a name as shown below.

rename tab

Commenting SQL

In Aginity Team you can use the typical SQL syntax to comment your code as shown below

1
2
3
4
5
6
-- This is one way to comment your SQL code

SELECT AVG(LineTotal)
FROM sales.SalesOrderDetail

/* This is another way to comment your SQL code */

Hint

If highlight multiple lines of code you can also use the Shortcut Keys Ctrl+K+C on Windows or Command+K+C on Mac to comment them out. To uncomment use Ctrl+K+U or Command+K+U.

Keeping Connection

The Keep Connection checkbox as show below will instruct Aginity Team to maintain the connection to the database if the default behavior of the database is to disconnect after the execution of a query. You are still subject to an overall database connection timeout if you session connection exceeds the permitted database setting.

Keep Connection

Find and Replace

Ctrl+F (Windows) or Command+F (Mac) will bring up the find and replace box as shown below.

Keep Connection

Interacting with Query Results

Once your query has been executed you can use the features of the result grid to interact with the result set. The typical actions you will do are listed below.

  • Reordering the Result Grid
  • Sorting the Result Grid
  • Filtering the Result Grid
  • Exporting results to CSV

Reordering the Result Grid

Once a query has executed successfully, you may rearrange the columns in the Result Grid in order to more effectively perform subsequent analyses. Rearranging the Result Grid is simple: simply click, hold, and drag the column header for the column you wish to move. As you move it left or right, the grid will automatically adjust to reposition the column.

Before rearranging query results

Once you are satisfied with the new position of the column, you may release the drag and the column will lock into place.

After rearranging query results columns

Sorting the Result Grid

After successfully executing a query, if the query has returned any rows you may sort the results in order to permit various types of analysis of your results.

Before sorting query results

In order to sort your results, simply click on the header row in the results grid. If the results are already being sorted by that column, then the sort order will toggle between ascending and descending.

After sorting query results by channel name

Filtering the Result Grid

Data in the Result Grid may be filtered to make it easier to locate and analyze certain subsets of the resultant data, without requiring you to submit an entirely separate query.

To filter the result grid by values in a particular column, bring your mouse cursor to the column header for the column to which you wish to apply a filter. In the right-hand side on the header row the filter icon (three horizontal bars) will appear. Click on the filter icon and you will be presented with a pop-up menu that will allow you to enter your filter criteria.

Popup displayed for filtering results

By default, the filter criteria will select a “Contains” filter, but several other filter types exist and you may select any of them. Once you have selected the filter type you wish to apply, type the filter into the text box. The results will immediately begin to filter.

Results filtered after entering criteria

When a column is being filtered, a funnel icon will appear in its header. To remove the filter, you may re-enter the filter mode by clicking on the filter icon (three bars) in the header row, and simply delete the filter text.

Multiple columns may have active filters at the same time. To accomplish this, simply perform these steps on each column.

Exporting results to CSV

Once you have successfully executed a query you can elect to export those results in Comma Separated Value (CSV) format to a file outside of Aginity Team. To do this, right-click anywhere in the results panel. Doing this will display a context menu from which you will select the option “Export to CSV”.

Exporting your query results to a CSV file

After selecting this option, you will be presented with a screen that permits you to select the location to which to save your results. Once you have identified the proper location, press the Save button to complete the export.

CSV files may subsequently be imported into a large number of different programs for further review and analysis.