SQL Explorer is Peak’s built in SQL client and data visualization tool
It enables you to create, edit and save SQL queries and then explore and visualize your data using a range of ready-made charts.
Getting to the screens
Go to Dock > SQL Explorer.
The SQL Queries screen appears.
SQL Queries Screen
From the SQL Queries screen, you can:
- View and search your saved queries
- Delete queries
- Launch the SQL Editor to edit saved queries or create new ones.
SQL Queries screen functions
|Shows information about the query:|
|Launches the SQL Editor to edit the query.|
|Deletes the query.|
SQL Editor Features
Peak's SQL Editor has the following key features:
- Multi-tab environment
- Unresponsive queries will not affect other queries
- Autoformat SQL code
- Query history
- Support for multiple databases and schemas
- Visualize query responses
- Copy results
- Row limits
- Select and execute specific queries from a script
- Save query outputs as a .CSV on S3
- DDL commands for Redshift
- Copying queries
The interface uses tabs so that multiple queries can be edited and executed simultaneously.
Unresponsive queries will not affect other queries
If you are running multiple queries and one becomes unresponsive, a new worker node will automatically start up to handle your ongoing queries.
You can then stop the misbehaving query and make any necessary changes to it, such as reducing the row limit.
Autoformat SQL code
SQL editor comes with a code library to assist with query writing. Features include, code-completion, suggested keys and table / column autocomplete.
History of executed queries is available that includes their success/failure status, start time, duration, and progress (if still running).
Support for multiple databases and schemas
SQL Explorer supports multiple types of database including Redshift and PeakDataModel.
Users can view the schemas for the selected database type and can preview the schema tables along with their attribute lists and datatypes.
Visualize query responses
- If a query has been run and there is no result.
- If multiple queries are running and a query has not been selected.
- If you change the schema, database or interact anywhere in the current tab after running a query
Once your query has run successfully, click the COPY RESULTS button to copy a query output and paste it into an editor without loosing its tabular formatting.
You can select query result row limits of:
- No Limit
If you have selected No Limit, you can specify your own limits in your queries.
In addition, if you are running queries with no limits, you can run Top queries on your datasets.
If you are using the No Limit option while running queries for a large output dataset, the system might experience performance issues and the tab can go into an unresponsive state. It might impact other running queries as well.
The system will return results on UI that are up to 5Mb in size or 10,000 rows, whichever is less.
Select and execute specific queries from a script
Scrips can contain multiple SQL queries within them.
SQL editor makes it possible to select specific queries from a script and execute them and also to abort queries that are running.|
Save query outputs as a .CSV on S3
Queries can be saved as an .sql file and accessed directly from Downloads.
To get to Downloads, go to Dock and scroll down to the Tools section.
DDL commands for Redshift
The system supports the DDL command for the Redshift database according to the access permissions that have been granted. The DDL command will auto-commit once it has been executed.
This helps to improve the appearance of your queries by adding spaces, tabs and new lines wherever they are needed. It is especially useful for SELECT statements, but can also handle INSERT, UPDATE and DELETE statements. It is agnostic of the database that is targeted.
The Beautify feature supports multiple SQL dialects:
- Standard SQL
SQL dialect detection
- The system automatically detects the SQL dialect based on the database that you select. For example, if you selected “RedshiftDB” from the database dropdown, the system formats the SQL query based on a Redshift configuration.
Beautify per tab
- The Beautify feature works per tab, meaning that each tab can have different SQL dialects and the formatting will be based on the selected database within that tab.
The following formatting convention is used:
- Indentation of two spaces
- Separation of multiple queries by an empty line
- If the system is unable to detect the SQL dialect, it falls back to standard SQL format
- You can copy queries by clicking the Copy Query button.
This enables you to preserve the formatting when pasting a query.