Workbench
  • 17 Mar 2024
  • 3 Minutes to read
  • Dark
    Light
  • PDF

Workbench

  • Dark
    Light
  • PDF

Article Summary

Warning

There are differences in the syntax required when using SQL that will depend on where your data warehouse is built. For more information see the documentation for Google BigQuery or Redshift.

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.

image.png

Workbench Menu

Apart from executing queries, the workbench's functionality is accessed through three menus:

  1. Workbench toolbar:

    1. Format: Formats the written query in standard SQL format.
    2. New: Clears the query area and the results area so you can start a fresh query.
    3. Recipes: Opens the recipes menu with available recipes for your account
  2. Entities' three dots menu:

    1. Open
      1. Reports and views - Opens the definition and runs the query
      2. Tables - Runs a SELECT * query on the table
    2. Manage viewers - Define which viewer can access which entity. Not available for reports.
    3. Delete - Deletes the entity from the Panoply account
  3. Result's actions:

    1. Save - Save changes to the latest opened report/view or save as new
    2. Export - Save the results as a .csv file. For more detail, see Exporting Data
    3. Copy - Copy the SQL query or the results from the current page to the clipboard to the clipboard
Running multiple queries

When running multiple queries, users can cycle between the different results to export or save them separately

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 reports.
Panoply supports five different reports:

  • Table - Any query can be visualized as a table
  • Single Value - The result must be a single numeric value. Users can change the format between:
    • Number (100,000)
    • Short number (100K)
    • Currency ($100,000)
    • Short currency ($100K)
    • Percent (10%)
  • Pie Chart - The result must have 2 columns and the second column must be numeric.

Pie chart will show up to 6 slices. If there are more than 6 slices, the sixth will combine all the data into Other

  • 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

Every result can be saved as a report for easier future access. The saved report will retain the selected report type.


Was this article helpful?

What's Next