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.


Contents


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

Hover over a feed to access the following functions:

Shows information about the query:
  • The user that created it
  • When it was last updated
  • Query path (can be copied)
  • CSV path
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

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.

Query history

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

SQL editor enables you to visualize your query outputs using a range of ready-made charts.

The VISUALIZE button is only available once a SQL query has executed successfully and a response is available.

The VISUALIZE button is disabled in these conditions:

  • 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


Copy results

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. 


Row limits

You can select query result row limits of:

  • 500
  • 1000
  • 2000
  • 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.


Beautifier

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.


Features

The Beautify feature supports multiple SQL dialects: 

  • PostgreSQL
  • Redshift
  • Snowflake
  • 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.

  • Format configuration

  • 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

  • Copying queries

  • You can copy queries by clicking the Copy Query button.
    This enables you to preserve the formatting when pasting a query.