Saving a view as a table

In some cases, the extraction of data from a view can take some time. This might be because the view's logic is complicated or simply because there is a lot of data in the underlying tables.

In Panoply, you can save the view's result as a table instead and refresh it regularly. You can achieve that using our data sources. To accomplish that, follow these steps:

1. Create a Redshift data source in Panoply that pulls data from the saved view and ingest it into the destination table you set. In configuring the data source, use the following:
   
   • us-east-1 (unless your Panoply account is on a different region) for the region;
   • db.panoply.io:5439/your_db_name for the host address. Replace your_db_name with the name of your database; and
   • your Panoply account's username and password for the User authentication fields.
   
 
2. Click Next to authenticate. Once authenticated, select or search the view that you want to be saved as a table.
   
 
3. In the Advanced options, set the following:
   • The name for your destination table. Provide a different name than the original view.
    
   • The primary key (PK) for your table. This is to prevent duplication of data. It can be a single field or a composite of multiple ones. In any case, each field in the PK needs to be wrapped in curly brackets.
     
     For example:
     • PK with one field: {field1}
     • PK with two fields: {field1}-{field2}
    
   • If the data returning from the view changes over time and you do not want to keep old data that is not returning from the view anymore, we recommend turning on the Truncate option for this Redshift data source.
    
   
 
4. Finally, click Collect to start the process.
 

This configuration can come in handy in a few different scenarios, like having a slow view or even just to save historical data for a view/table.