Periscope Data Documentation

Connecting to Periscope

First Time Connecting

Connection Definitions

Common Connection Errors

Private Network SSH Tunnels

Integrations

Overview

Getting Started

Keyboard Shortcuts

Charts & Dashboards

Chart Overview

Chart Options

Dashboard Overview

Refresh Mechanics

Organizing Charts

Organizing Dashboards

Table-like Charts

Table Overview

Cohort Grids

Pivot Tables

Graph-like Charts

Overview & General Settings

Chart Types and Settings

Mixing Chart Types

Map Charts

Map Chart Overview

Heatmaps

Clusters and Bubbles

Satellite Maps

GeoJSON

Other Charts & Customizations

Pie Charts

Image Charts

Chart Annotations

Site Administration

Adding Users

Removing Users

User Types

User Groups

Two-Factor Authentication

Managing User Permissions

Dashboard Permissions

Site Preferences

Query Management

Color Themes

Dashboard Refresh Management

Usage Data

Periscope Cache

Caching Strategies

Querying the Periscope Cache

Cross-Database Joins

CSV Upload

Materializing Views

SQL Formatters

Using SQL Formatters

Composing SQL Formatters

Dollars and Percentages

Time Zone Formatters

Date Aggregation

Date Parts

Basic Filters

Custom Filters

Date Range Filters

Aggregation Filter

Adding, Moving, and Hiding Filters

Advanced Filters

Default Filters

Drilldowns

Parent-Child Filters

Matching Filter Values

Time Zone Conversions

Direct Replacement

User Friendly Names

Views

SQL Views

Joining to Views

More SQL Tools

SQL Snippets

Parameterized SQL Snippets

SQL Alerts

Automatic Joins

Version History

Sharing and Embedding

Sharing Dashboards Externally

Email Reports

Easy Embedding

Embed API

Embed API Options

Public CSV URL

Slack API

Slack Linking Reports

Data Management

Adding and Deleting Datasource

Spaces: Data-Level Permissions

Improving Query Throughput

Revoking Table Permissions

Salesforce Datasource

Schema Browser

SSO Integrations

Overview

Okta

OneLogin

Google Apps

Connecting to Periscope

To get started with Periscope, first fill out the Connect Form to establish an account within the Periscope platform. Once a successful connection is established, this form is the first step to making the account accessible.

Note: Fill out the form once to establish an account. Additional databases can be added through database settings once logged in.

General Steps to Take Before Connecting

  • Ensure that the database is publicly addressable and currently up & running. If this is not the case, then a connection via SSH may be required. The documentation page on setting up an SSH tunnel breaks down the steps to help get connected to private databases.
  • Verify that the database is not hosted on a local machine. Periscope cannot connect to databases hosted on local machines (e.g. laptops, PCs, etc.).
  • Ensure that Periscope supports the database type attempting to be connected. A list of supported integrations is available here.

Successful Connection Requirements

A successful connection to Periscope can be done in a few steps:

  1. Whitelisting IPs - Allowing Periscope IPs into the server that the database is hosted on.
  2. Granting Permissions to Tables - Allowing the user that Periscope is acting as to query from tables in the database.

Instructions by Database

MySQL

Redshift

PostgreSQL

SQL Server

BigQuery

Snowflake

1. Whitelisting IPs

Whitelisting IPs is the process to allow Periscope's two IP addresses (107.23.195.228 and 54.236.224.46) to pass through the firewall of the server where the database is hosted. As long as the database is hosted on a publicly accessible server, or a privately accessible server that can be accessed using an SSH tunnel, Periscope will be able to connect with this server. Please find below some sample server host types and whitelisting can be acheived.

Company Server

If the database is hosted on the Company's server, check to see if this server is publicly accessible by the internet. If not, check with the IT department on how to whitelist IPs on the Company's firewall. In some cases, an SSH tunnel will be required if the database is hosted on an internal network such as a VPN.

Amazon AWS EC2 or RDS

When the server is hosted on Amazon AWS, but not on a VPC, the first step is to log into the AWS console as an admin. Afterwards, the database instance needs to be set as publicly accessible. A Security Group on the EC2 or RDS client will need to be configured to accept connections from Periscope's IPs. The IP requirement is a CIDR/IP, and thus the IPs that need to be added are 107.23.195.228/32 and 54.236.224.46/32. After configuring this Security Group, it must be applied to the instance of the database that Periscope will connect to.

If the database instance is hosted on a Virtual Private Cloud (VPC), there may be a secondary Security Group that needs to be configured at the network level to accept connections from Periscope's IP. In the case where the user only has a private IP for accessing the VPC within AWS, the user may be required to set up a SSH tunnel.

Note: Periscope does not support Amazon S3, as that is a cloud storage service rather than a database server.

Segment & Amplitude

Segment and Amplitude typically host databases on Amazon Redshift through AWS or on Heroku. These databases are typically publicly accessible. If not, check with respective Segment and Amplitude representatives to help with whitelisting Periscope's IPs.

Heroku

Heroku doesn't require whitelisting IPs as its databases are publicly addressable.

Microsoft Azure

For Azure, follow the following detailed steps to make sure that the SQL Server Database is accessible by the public Internet

  1. On the Azure Management Portal, click on VIRTUAL MACHINES.
  2. Select the virtual machine.
  3. Near the top of the page, select the ENDPOINTS page, and then at the bottom of the page, click ADD.
  4. On the Add an Endpoint to a Virtual Machine page, click Add a Stand-alone Endpoint, and then click the Next arrow to continue.
  5. On the Specify the details of the endpoint page, provide the name (MSSQL), public port (1433) and private port (1433).
  6. Click check mark to continue. The endpoint is created.

Now determine the public name of the host, called the DNS name.

  1. In the Azure Management Portal (or from the previous step), select VIRTUAL MACHINES.
  2. On the VIRTUAL MACHINE INSTANCES page, under the Quick Glance column, find and copy the DNS name for the virtual machine.

Snowflake Data Warehouse

By default, Snowflake allows users to connect to the service from any computer or device IP address. Connections to Snowflake data warehouses require a special encryption. As such, no IP whitelisting is necessary.

2. Granting Permissions to Tables

Depending on the set up, the user may have opted to create a new user to connect exclusively to Periscope as a security measure. Even if this special user wasn't created, the connected user must have been granted SELECT permissions on these tables in order to access certain tables in Periscope. The commands will vary slightly depending on the type of SQL database, and the examples below assume that the Periscope user has been named 'periscope'.

MySQL

GRANT SELECT ON my_database.schema_name TO 'periscope'@'%';

Redshift

GRANT USAGE ON SCHEMA public TO periscope;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO periscope;

Postgres

GRANT USAGE ON SCHEMA public TO periscope;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO periscope;

SQL Server

GRANT SELECT my_table TO periscope;

Snowflake

Snowflake allows granting privileges to roles rather than individual users. A role may need to be created exclusively for Periscope access as well.

GRANT ROLE periscope_role TO USER periscope;
GRANT USAGE ON DATABASE my_database TO ROLE periscope_role;
GRANT USAGE ON WAREHOUSE my_warehouse TO ROLE periscope_role;
GRANT USAGE ON SCHEMA public TO periscope_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO periscope_role; 

Athena

The Athena user requires the AmazonAthenaFullAccess policy as well as both read and write access to the S3 bucket.