Connecting ETL Tools

Connecting ETL Tools

Panoply documentation is moving! Click here to view the new site!  __________________________________________________________________________________________________________________

Panoply works with several partners to provide additional data sources beyond those built into the Panoply Platform.

For data warehouses built on Google BigQuery, Panoply supports connections with Stitch. Additional tools will be added in the future.

For data warehouses on Redshift, Panoply is compatible with any data connector or ETL tool that uses a standard ODBC/JDBC connection, Postgres connection, or AWS Redshift connection.

To determine whether your data warehouse is built on BigQuery or Redshift, see the BI Connect Page on the Panoply platform.

You can find a list of ETL tools in the ETL section of the Data Integration listings. You can also find a list of all the Partner Data Sources that are from one of our Premier ETL Partners. Chat with us any time to learn more about Partner Data Sources and to get them set up in your account.

Google BigQuery

Connecting Stitch

  1. In the Panoply navigation menu, click BI Connection. This displays your Panoply data warehouse’s connection details. 
  2. Download the .json credentials file.
  3. Sign into your Stitch account.
  4. Click the Destination tab.
  5. Click the Google BigQuery icon.
  6. Scroll to the Your service account section.
  7. In the Your Key File field, click the icon and locate the JSON credentials file you downloaded in Step 3. Once uploaded, the BigQuery Project Name field will automatically populate with the name of the GCP project in the JSON project key file.
  8. Select a Google Storage Location by using the Google Cloud Storage Location dropdown, then select a US region.  

Note: Changing this setting will result in replication issues if data migration isn’t completed correctly.

9. There are two definitions for the loading behavior:

  • Upsert: Existing rows will be updated with the most recent version of the record from the source. With this option, only the most recent version of a record will exist in Google BigQuery.
  • Append: Existing rows aren’t updated. Newer versions of existing records are added as new rows to the end of tables. With this option, many versions of the record will exist in Google BigQuery, capturing how a record changed over time.
  • Refer to the Understanding loading behavior guide for more info and examples.

Note: Loading behavior can’t be changed after the destination is created. To change Google BigQuery loading behavior, you’ll need to delete and re-create the destination.

This setting may impact your Google BigQuery costs. Learn more.

10. Click Check and Save. Stitch will perform a connection test to the Google BigQuery database; if successful, a Success amessage will display at the top of the screen. This test may take a few minutes to complete.

Redshift

Connecting Stitch

  1. In the Panoply navigation menu, click BI Connection. This displays your Panoply data warehouse’s connection details. 
  2. Copy your host, port, database name, and user name.
  3. Sign into your Stitch account.
  4. Head to the destination tab and click the Panoply icon.
  5. When prompted, enter the information you collected from the Panoply BI Connection section into the corresponding fields in Stitch:
    • Host (Endpoint): The host name you received from Panoply (db.panoply.io)
    • Port: The port number you received from Panoply (5439)
    • Username: The user name you set for your Panoply account
    • Password: The password you use to log into Panoply
    • Database: The name of the Panoply database to load data into (be sure to specify the correct database name if you have multiple databases attached to your account)

Connecting Fivetran

  1. In the Panoply navigation menu, click BI Connection. This displays your Panoply data warehouse’s connection details. 
  2. Copy your host, database name, and user name.
  3. Sign into your Fivetran account.
  4. Pause all integrations in the Fivetran dashboard.
  5. Go to your account page and click the +WAREHOUSE button.
  6. Under Select Warehouse, scroll down until you find Panoply.
  7. Fivetran will automatically add Panoply’s Port information, but you will need to enter Panoply’s Host name (db.panoply.io), your Panoply User namme, the password associated with your Panoply account, and the Database you want to use Fivetran to load data into.
  8. Click SAVE & TEST at the bottom of the screen. When the test is successful, a box at the bottom of the screen says “All connection tests passesd!”
  9. Click VIEW WAREHOUSE. You should see the details of your Panoply warehouse in the next page. You can now load data into Panoply using Fivetran.
Getting started is easy! Get all your data in one place in minutes.
Try Free