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.
Once you write your code in Aginity Team you have options to run it using the following scope:
|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
- Use the #+sql command to tell the editor you will be writing SQL in this section.
- You will then use the :output file command to tell the editor you will spool the results to an output file
- In the table below is a series of parameters and descriptions to be used when spooling
- Use the #+begin statement to tell the editor you from here until the #+end command you will have SQL to execute.
- Write your SQL
- End with #+end
- 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
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
|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|
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.|
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.
- Display any words on the editor screen from above where your current cursor is
- Display SQL Syntax like SELECT, WHERE, FROM all the way to database platform specific functions like CASE, DATEDIFF or CURRENT_DATE()
- Display the database, schema, table, column as you are typing
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.
Currently we support auto-complete on Netezza, Redshift and Snowflake
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.
|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.
You can rename the query tabs by double clicking and typing in a name as shown below.
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.
Find and Replace¶
Ctrl+F (Windows) or Command+F (Mac) will bring up the find and replace box as shown below.
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.
Once you are satisfied with the new position of the column, you may release the drag and the column will lock into place.
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.
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.
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.
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.
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”.
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.