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:
- 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.
- 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.
- 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.
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 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
By default, when you create a new view from the Workbench page, the view is saved in the
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.
From the Workbench 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:
- Click the menu icon .
- Select Export.
- 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.
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.