You will spend most of your time in the Query Panel writing SQL and saving it to your catalog in Aginity Pro. The video below will give you an overview of creating your first query
(video will be added soon)
Writing your First Query
Each tab in the Aginity Pro 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 Pro you have options to run it using the following scope:
| 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 Pro 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
|
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 |
| header | true or false on whether you want to display headers in file |
| encoding | the encoding of the file UTF_8 is the only value supported currently. |
| compression | we can compress the file after it is written. Values are NONE, ZIP and GZIP. |
See also
We have a great knowledge base article on how to use the catalog to facilitate using the export as file editor feature.
Using Parameters in your Query
Parameters can be used in Aginity Pro by using the $ in any part of a SQL statement. When you execute a query the Aginity Pro 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 Pro 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. |
Hint
You can specify default values with parameters by using this syntax with your parameter $fill_in_gender_at_runtime{‘M’}
When you run the query you will see the little gear icon as shown below. Clicking that will populate the default value.
Auto-Complete Capabilities
In Aginity Pro we have implemented auto-complete on several database platforms and will be deployed 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
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 that 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.
Renaming Tabs
You can rename the query tabs by double-clicking and typing in a name as shown below.
Commenting SQL
In Aginity Pro 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 shown below will instruct Aginity Pro 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 your 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 Pro. 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.
Exporting results to TSV
A tab-separated values file is a simple text format for storing data in a tabular structure, e.g., database table or spreadsheet data, and a way of exchanging information between databases. Each record in the table is one line of the text file.