Connecting Loops to Your Data Source

This article will help you connect Loops directly to your database or data warehouse without any coding or R&D resources.

If you don’t have a database or data warehouse, we’re happy to help you set one up. We can also guide you through the process if your data resides on a platform and needs to be synced to a data source (which Loops will then connect to).

For any assistance, just schedule a call or talk to your Loops integration specialist.

Which data sources are supported?

Loops currently supports the following data sources:

Note: If there are restrictions on connecting to your data source from specific networks, you can either grant access to Loops’ production IP addresses, or provide access through a VPN.

If your organization uses a data source that’s not on this list, please get in touch with us.

How to connect Loops to a data source

The below information will help you provide Loops with read and write access to your data source. If you have any questions or need assistance, be sure to contact your Loops integration specialist.

Amazon Athena

To grant Loops access to your Athena warehouse, first create a new user with the following IAM permissions. Replace “MY-BUCKET-NAME” with your actual output S3 bucket name:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "s3:ListAllMyBuckets",
                "s3:ListBucket",
                "athena:*",
                "s3:GetBucketLocation"
            ],
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "glue:GetDatabase",
                "glue:GetDatabases",
                "glue:GetTable",
                "glue:GetTables",
                "glue:GetPartition",
                "glue:GetPartitions",
                "glue:BatchGetPartition"
            ],
            "Resource": [
                "*"
            ]
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
            "Resource": "arn:aws:s3:::MY-BUCKET-NAME/*"
        },
        {
            "Sid": "VisualEditor2",
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:ListBucket"
            ],
            "Resource": "arn:aws:s3:::MY-BUCKET-NAME"
        }
    ]
}

For the user created, create new access & secret keys (see AWS documentation).

Next, provide Loops with the following connection parameters:

  • region name – you can view this by clicking the "Explore the query editor" button at the top right corner of Athena (e.g. “us-east-1”, “eu-central-1”).
  • work group – you can view this in the Athena query editor, in the left-side navigation pane (e.g. “primary”)
  • data source (a.k.a. catalog name) – optional and only relevant if the data is not stored in the default AwsDataCatalog
  • output location in S3 - optional and only relevant for when the provided work group does not define an output location of its own
  • aws access key & aws secret key – you can find this information on your AWS IAM page. Make sure you are signed in as a root user or a user who can create other users and policies.

Amazon Redshift

Create a dedicated user account for Loops, and provide access to the relevant tables and views in your Redshift cluster, using the following parameters:

  • database – the Redshift database that Loops will query (e.g. “my_redshift_database”)
  • host – the host of your Redshift server (e.g. my-cluster.abc56defabcd.us-east-1.redshift.amazonaws.com)
  • port – the port your Redshift server is using (optional, only relevant if not “5439”)
  • user – the user that Loops will use to query data from Redshift (e.g. “loops_redshift_user”)
  • password – the password for the provided user

If your warehouse is not publicly accessible (not a VPC), you can add Loops’ production IP address to the allowlist.

BigQuery

Provide access to your BigQuery warehouse by creating a service account for integrations@getloops.ai with the following permissions:

  • bigquery.editor
  • bigquery.readsessions.create (to be used with BigQuery Storage Read API for faster data retrieval)
  • bigquery.readsessions.getData
  • bigquery.tables.list (used in the discovery process and for monitoring schema changes)
  • bigquery.jobs.create

In case the GCP project ID is different from the one provided in the given service account json file, please share the relevant project ID separately.

ClickHouse

Grant access to your ClickHouse warehouse by providing Loops with the following connection parameters:

  • host – the hostname of the machine or server where ClickHouse is installed and running
  • port – the specific network port that your ClickHouse server is bound to (defaults to 9000 if the connection is not secured, or 9440 if the connection is secured)
  • database – the database name
  • user – the username which will be used for connection
  • password – the user’s password

If your data warehouse is not publicly accessible (not a VPC), you can add Loops’ production IP address to the allowlist.

Databricks

Grant access to your Databricks warehouse by providing Loops with the following connection parameters:

  • access_token - your Databricks personal access token for the workspace for the cluster or SQL warehouse (e.g. “dapi...<the-remaining-portion-of-your-token>”)
  • http_path - the HTTP path of the cluster or SQL warehouse
    • Example for a cluster: "sql/protocolv1/o/1234567890123456/1234-567890-test123"
    • Example for an SQL warehouse: "/sql/1.0/warehouses/a1b234c567d8e9fa"
  • server_hostname - the server hostname for the cluster or SQL warehouse (e.g. “dbc-a1b2345c-d6e7.cloud.databricks.com”)

See Databricks documentation for help gathering information about the cluster or SQL warehouse you want to use.

PostgreSQL

Create a dedicated user account for Loops, and provide access to the relevant tables and views in your PostgreSQL database management system.

If your data warehouse is not publicly accessible (not a VPC), you can add Loops’ production IP address to the allowlist.

Note that Loops runs on your analytics database since running on an operational database may affect performance. And since PostgreSQL is not meant to be used as an analytical database, Loops analyses may be limited if you have an extremely large user base.

SingleStore

Grant access to your SingleStore warehouse by providing Loops with the following connection parameters:

  • host – the hostname of the machine or server where SingleStore is installed and running
  • port – the specific network port that your SingleStore server is bound to (defaults to 3306)
  • database – the database name
  • user – the username that will be used for connection
  • password – the user’s password

If your data warehouse is not publicly accessible (not a VPC), you can add Loops’ production IP address to the allowlist.

Snowflake

To grant access to your Snowflake warehouse, provide Loops with a connection string for your Snowflake cluster.

If your data warehouse is not publicly available (not a VPC), you can add Loops’ production IP address to the allowlist.

If you provide Loops with access to the Snowflake web app, please provide the following credentials (see Snowflake documentation for reference):

  • account identifier - examples: 'abc12345.us-east-1', 'de123456.west-europe.azure'
  • username
  • password
  • warehouse name
  • (optional) user role - if needed
  • (optional) catalog - only if the default catalog set for the provided user is not the right one

Vertica

Grant access to your Vertica warehouse by providing Loops with the following connection parameters:

  • user
  • password
  • host
  • database

Since Vertica is typically an on-prem solution, you will need to provide Loops with network access by adding Loops’ production IP address to the allowlist, or by using a VPN tunnel. Please contact us for more details.

Still need help? Contact Us Contact Us