Workbench

The Panoply workbench, accessible by clicking Workbench in the navigation menu, is a web-based sandbox for testing and running queries. You can also save your queries as views in order to reuse them in this workbench, an external workbench, a visualization tool, or any analytics tool that you want to connect to Panoply.

The workbench consists of:

1. Query area: Any valid SQL syntax will work here. To execute your query, click the Run icon above the query area, or press Shift + Enter. Additionally, highlight part of the query to only run that part.
2. Table list: Your tables and views are organized by schema. Expand a table or view to view the list of related fields.
3. Results :The results of your executed query appear here.

You can adjust the height of the query area and the results area. You can show or hide the table list by clicking the grid icon above the query area.

Workbench Menu

Apart from executing queries, most of the workbench's functionality is accessed through the workbench menu. To open the workbench menu, click the menu icon.

The Panoply workbench menu consists of the following items:

• Format: Formats the written query in standard SQL format.
• New: Clears the query area and the results area so you can start a fresh query.
• Open: Loads a saved view. You can select from any view in the database, including views saved through this Workbench page and views created through external workbenches. The Open menu item displays the query, but does not run the query immediately.
• Save: Saves the currently loaded (opened) view. If the query has not been saved as a view before, selecting Save will actually open the Save as menu so that you can name the view. The main purpose of re-saving a view is modify the existing query logic (for example, by adding a filter). When re-saving a view, note the following constraints:
  • The number of columns must remain the same.
  • Data types must remain the same.
  • Column names must remain the same.
  • You must be the owner of the view.
• Save as: Saves the current query or view as a new view.
• Delete: Eliminates the view from the database. You must be the owner of the view in order to delete the view.
• Export: Save the results as a .csv file. For more detail, see Exporting Data.

Creating Views Outside of the public Schema (Redshift)

By default, when you create a new view from the Workbench page, the view is saved in the public schema.

To create a view under another schema, use the create view syntax. For example, if you want to create a view called countlogins under your schema called schema2, you would enter the following SQL and then execute the query:

CREATE VIEW schema2.countlogins
AS
  SELECT count(1) AS count
  FROM   login;

Your new view is created when you run the query. Do not use the Save menu item because this would attempt to create this view, including the CREATE VIEW line, in the public schema, which is not the desired outcome in this case.

Exporting Data

From the Workbench page, you can export the results of an executed query or view as a .csv file. This approach is ideal for relatively small results because of the following limitations:

• Redshift:
  • The number of returned rows is limited to 1M.
  • The output file size is limited to 100MB.
• BigQuery:
  • The number of returned rows is limited to 1M.
  • The output file size is limited to 1GB.

To export data:

1. Run the desired query to verify the results.
2. Click the arrow icon above the results and select Export.
3. Do one of the following:
   • Stay on the Workbench page until the results file is generated and the download occurs.
   • Navigate elsewhere in the Panoply platform, and download the file later from the Jobs page. Click the cloud download icon to download the results file.
     • You can use the filters to select only the Export items.

In Redshift: You can also use the UNLOAD command with a SELECT statement to unload data from database tables to a set of files in an Amazon S3 bucket. This approach can handle result sets larger than 100 MB. However, be aware that the Amazon S3 bucket where Redshift writes the output files must be in the same region as your Panoply deployment, which is most commonly in the us-east-1 region. To learn more, reference the UNLOAD command documentation, see some UNLOAD examples, and read about Unloading Data to Amazon S3 in the Redshift Database Developer Guide.

Visualizing results

In Panoply's workbench, users can transform the result of a query into charts.
Panoply supports three different charts:

• Pie Chart - The result must have 2 columns and the second column must be numeric.

• Column Chart - the result must have 2-4 columns and the second, third and fourth must be numeric
• Line chart - the result must have 2-4 columns and the second, third and fourth must be numeric