Redshift

Section header image mobile

Coefficient's integration with Amazon Redshift boasts an intuitive visual query builder, simplifying the process of selecting tables, columns, and applying filters. The system seamlessly translates these selections into efficient SQL queries for optimal performance on your Redshift database. Additionally, users have the flexibility to incorporate their own custom SQL queries, allowing for a personalized and tailored approach to data imports.

Connecting to Redshift

Import from Redshift

Import from Tables

Custom SQL Query

Schedule your Import, Snapshots, and Add Automations

FAQs for Redshift Integration

Connecting to Redshift

When you begin a Redshift import for the first time, you will need to go through a few steps to connect Redshift as a data source for Coefficient.

ℹ️ NOTE: Coefficient will need the following information: Redshift Host, Database name, Username, Password, and Port (The default Port for Redshift is 5439).

1. Open the Coefficient Sidebar and click the Menu.

Menu_Icon (1).png

2. Select “Connected Sources”. 

Screenshot 2023-10-10 at 5.18.29 AM.png

3. Select “Add Connection” at the bottom and then “Connect” to Redshift.Screenshot 2023-11-17 at 5.39.53 AM.png

4. Enter the required fields (Host, Database name, Username, Password, and Port)

Screenshot 2023-11-17 at 5.41.25 AM.png

5. Ensure that your Redshift cluster has the Publicly accessible setting enabled. You might also need to whitelist (All 3) Coefficient’s IP addresses (34.217.184.131, 44.234.233.60, 52.32.132.51) on your Redshift cluster’s Security Group and/or VPC network ACL. For detailed instructions, see this AWS support article

Screenshot 2023-11-17 at 5.42.19 AM.png

Import from Redshift

There are 2 different ways to import data using Coefficient from Redshift, Import from tables, and Custom SQL query. Importing from tables allows you to create imports without having to write SQL. Using a Custom SQL Query gives you additional flexibility in the data that you are importing into Coefficient.

Import from Tables

1. Open the Coefficient Sidebar and click on the “Import from…” button.

ImportFrom (1).png

2. Select the "Redshift" connection you created.

Screenshot 2023-11-17 at 5.45.48 AM.png

3. Select “From Tables & Columns”.

Screenshot 2023-11-17 at 5.46.30 AM.png

4. A Data Preview window will appear allowing you to select the table that you want to import from.Screenshot 2023-11-17 at 5.52.31 AM.png

5. After the Table is selected the fields will populate in a list on the left side. You can uncheck/check the fields to exclude/include in your import. From this preview, you can also add filters, sort, pivot, or drag and drop the fields in the view to be in the order you need. Then click “Import”.Screenshot 2023-12-02 at 2.21.43 AM.png

6. Congratulations on your successful Redshift import using Coefficient! 🎉Screenshot 2023-12-02 at 3.30.53 AM.png

Custom SQL Query

1. Open the Coefficient Sidebar and click on the “Import from…” button.

ImportFrom (1).png

2. Select Redshift as the data source.

Screenshot 2023-11-17 at 5.45.48 AM.png

3. Select “Custom SQL query”.

Screenshot 2023-11-17 at 5.47.04 AM.png

4. Enter your query in the text box provided or use AI to write a query. Then click the Import button.Screenshot 2023-12-22 at 6.11.57 AM.png

5. Add a name for your import.Screenshot 2023-12-22 at 6.15.05 AM.png

6. Congratulations on your successful Redshift import using Coefficient! 🎉Screenshot 2023-12-02 at 4.12.55 AM.png

Import from GPT SQL Builder

1. From the Sidebar select “Import from…”.

Screenshot 2024-01-03 at 4.48.23 PM.png

 

2. Select “Redshift” from the list.

Screenshot 2024-01-03 at 4.49.23 PM.png

3. Select "GPT SQL Builder".

Screenshot 2024-01-03 at 4.49.45 PM.png

4.  Enter your prompt/query in the "Describe what you want to query" box. 

 ℹ️ PRO TIPBe specific when entering your prompts so that the AI can easily understand your requirements and provide more accurate results.Screenshot 2024-01-03 at 4.52.35 PM.png5. The SQL Builder will automatically generate and write the SQL query for you in the blue text box.

ℹ️  NOTE: Click "Refresh Preview" to display a sample of your data results (only 50 rows are shown) or to update the results of the preview if you make any changes to the query.Screenshot 2024-01-03 at 4.53.03 PM.png6. You will be prompted to give your import a name. Remember it MUST be UNIQUE as it will also be the name of the tab in your Google Sheet when imported. (You can always change the name later if needed).Screenshot 2024-01-03 at 4.53.22 PM.png7. Congratulations on your Redshift import using Coefficient's GPT SQL Builder!  🎉

Screenshot 2024-01-03 at 4.53.54 PM.png

ℹ️ See GPT SQL Builder to learn more!

 

Schedule your Import, Snapshots, and Add Automations

Once you have pulled your data into Sheets using Coefficient, you can set up the following:

1. Schedule your Importa.png

2. Turn on Snapshotsb.png

3. Set Up Automations

c.png

FAQs for Redshift Integration

I keep getting an error when I try to connect Redshift to Coefficient. What is wrong?

Please review the following, then try connecting again:

  • Make sure that you have entered the correct Hostname, Database Name, Username, Password, and Port for your Redshift cluster. To find the Hostname and Port:
    • Sign in to AWS Console
    • Type “Amazon Redshift” into the search bar and click on “Amazon Redshift” (listed under Services)
    • Select the cluster you would like to connect to
    • The **Endpoint ****URL contains the Hostname and Port of your Redshift cluster
  • Ensure that your Redshift cluster has the Publicly accessible setting enabled. You might also need to whitelist Coefficient’s IP addresses on your Redshift cluster’s Security Group and/or VPC network ACL. For detailed instructions, see this AWS support article.
    • Coefficient IP Addresses (All 3 need to be whitelisted) 
      • 34.217.184.131
      • 44.234.233.60
      • 52.32.132.51

When you connect Coefficient to Redshift do you maintain access?

When Coefficient needs to run a query, we establish a connection to your database, run the query on your behalf, and terminate the connection once the query completes.

I added a table (or column) in my Redshift database; why is it not showing up in Coefficient?

To deliver a snappy experience when you set up imports from Redshift, we cache your database schema for up to 24 hours. If you recently changed your database schema (e.g. added a table/column, renamed a table/column, etc.), and you don't see the change reflected in Coefficient, you can force a schema reload:

  1. Open the Coefficient sidebar in Google Sheets
  2. Click on the ≣ menu in the top right of the sidebar, then click on “Connected Sources”
  3. Click on your Redshift connection to see its Connection Settings page
  4. Click on the︙button near the top right, choose “Reload Schema”, and click “Reload” on the confirmation dialog

My custom SQL script seems to run longer than expected and sometimes, I see a "SQL Error - canceling statement due to statement timeout" error when I refresh my import, what should I do?

The error message you're seeing indicates that the SQL query you're trying to execute is being canceled due to a statement timeout. This means that the query is taking too long to execute, and your database server is configured to cancel any query that exceeds a certain execution time threshold.
Here are some steps you can take to understand and fix this issue:

  1. Examine the Execution Plan: Use the EXPLAIN command to get the execution plan for your query. This will show you where the query might be inefficient, such as performing full table scans or using nested loops that could be optimized. (Click here to learn more about the EXPLAIN command with Redshift).
  2. Optimize the Query: Look for ways to make the query more efficient. This could involve adding indexes to the columns used in the WHERE clause and the ILIKE conditions, rewriting the query to reduce complexity, or breaking it into smaller parts.
  3. Reduce the Dataset: If possible, limit the scope of the query. For example, if you're querying a large date range or a large number of rows, see if you can reduce that range with LIMIT.
  4. Increase the Statement Timeout (not recommended): If you have control over the database server settings, you can increase the statement timeout value. This is a temporary solution and may not be ideal if the query is inherently inefficient.
Was this article helpful? 0 out of 0 found this helpful