Connect Your Spreadsheet to Redshift

Table of Contents

• Connect Row Zero to Redshift
• Write a query to import data
• Build out your connected spreadsheet
• Export to Redshift

Connect Row Zero to Redshift

In a Row Zero workbook, click on the 'Data' icon in the upper right-hand corner and then click the button to 'Add connection.'

The Redshift connector requires a few pieces of information.

1. Connection name - This field can be anything that helps identify the data source being connected. (e.g. "Production Data" or "Product Metrics")
2. Host - This is the Redshift address that typically takes the form of a string like '12345xyz.redshift.amazonaws.com'.
3. Port - This is always 5439.
4. User - This is the username for your Redshift database.
5. Password - The password used to log into your Redshift Database.
6. Database - The name of the Redshift database being connected to.

Once all the information is entered, hit 'Test connection' to ensure the information is correct. If the test connection icon turns green and says 'Connected', proceed by clicking on '+ Add Source.'

Write a query to import data

Now that Redshift is connected to your spreadsheet, click the "+" sign next to your Redshift connection to insert a connected table.This adds a data table to your spreadsheet that is connected to Redshift and opens a query editor. Select the 'Schema' from the dropdown and then write a SQL query. The easiest query to write is a 'select *' statement, which pulls in the entire table. For example:

select * from table_name

Click 'Run' to execute the query and import the queried Redshift data into your spreadsheet.

The data imports directly into a connected table in the spreadsheet, which provides a dynamic, condensed view of the data and makes it easy to filter, sort, format, pivot, chart, etc. You can also add calculated columns and reference the data in formula functions throughout your workbook. Double-click on the connected table to re-run your query for the latest data or schedule auto-refresh and everything built on connected data automatically updates and stays in sync. Here is more info on how to build connected spreadsheets.

Build out your Redshift connected spreadsheet

Once you've imported your Redshift data, you can build out your connected spreadsheet. Row Zero works like Excel and Google Sheets - just a lot more powerful. Connected tables make it easy to view, filter, sort, format, pivot, and chart big datasets.You easily add calculated columns to your table by entering a formula in the first column to the right of your table.You can also reference the connected data in formula functions throughout your workbook.Right-click on the connected table to open a context menu with additional options. You can manage columns, cut, copy, create pivot tables, etc.Easily create connected charts by selecting cells in your connected table or pivot table and going to 'Insert', 'Chart' in the header navigation.Once you've built out your spreadsheet, you can double-click on the connected table to re-run your query for the latest data or schedule auto-updates.This updates everything built on your connected data and keeps your whole spreadsheet in sync with the latest data.

Export your spreadsheet data to Redshift

With a connected Redshift account, you can easily export spreadsheet data to Redshift as a new table. You can write-back cell ranges, pivot tables, data tables, sheets, etc. This is also a convenient way to preview and clean files before importing to Redshift. Once you've defined the data you would like to export to Redshift, right-click on the data table or selected range, navigate to 'Export to', and select your Redshift connection.

The export to Redshift modal will pop and ask you to select the schema and specify a table name. New table names are forced to start with 'rz_' to avoid overwriting any tables already in your Redshift instance. Hit export and Row Zero will create a new table in Redshift and import the data. When the export is complete, you will see a notification in the upper right-hand corner that says "Export finished".

To verify your table is in Redshift you can use either of the commands below.

show tables

select * from rz_TABLENAME