Using the Workbench

Using the Workbench

The Panoply workbench, accessible by clicking Analyze 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.
  2. Table list - Your tables and views are organized by schema. Expand a table or view to view the list of related fields. You can drag-and-drop items from this area into the query area.
  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:

  • 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 Analyze 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.
  • Set Alert - Use the current query or view to create an alert. For more detail, see Setting Alerts.

Creating Views Outside of the public Schema

By default, when you create a new view from the Analyze 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 Analyze page, you can export the results of an executed query or view as a .csv file. This approach is ideal for small results because the output file size is limited to 100 MB.

To export data:

  1. Click the menu icon .
  2. Select Export.
  3. Do one of the following:
    • Stay on the Analyze 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.

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-2 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.