[ROBERT WIP] Data Engine on Your Warehouse - Redshift

👤 This documentation page is intended for customers who are connecting their own Redshift database into Periscope as a writable destination. If you have a Snowflake database for this purpose, please see the documentation page here.

Periscope normally only requires read access to your warehouses. In order to use Data Engine on Your Warehouse (DEYW), Periscope also requires read-write access to parts of your warehouse. To avoid any accidental issues, it's important that you carefully grant write permissions to only the necessary resources. See the Acknowledgements section at the bottom of this page for more information.

The following instructions will walk through establishing a Redshift database connection via a read-user, and then defining and applying a write-access user.

1. Enforce SSL

The Redshift cluster must force SSL upon connections. This is a hard requirement, as unsecured connections are not allowed. To configure the cluster to require SSL, see the documentation page here:

  1. https://docs.aws.amazon.com/redshift/latest/mgmt/connecting-ssl-support.html

2. Prepare an Uncached Space

“Data Engine on Your Warehouse” will only work on sites/spaces that do not have a cached database. A quick way to check whether a space is cached is to see if the “Cache” tab is available underneath in the “Settings” gear icon:

If the site is not cached, then proceed to Step #3.

If the site has the Periscope cache, then a new space must be created. This new space will be used for all subsequent steps. By default, the new space will cache upon connecting a database. After completing Step #2, please contact Periscope Support to un-cache that space. In the interim, you may proceed with the following steps.

3. Connect the Redshift Database (Read User)

If the Redshift database has not already been connected within the Periscope account, the following documentation page walks through how to add it:

  1. https://doc.periscopedata.com/article/connecting-to-periscope-menu#Whitelist

The database should be connected in the same space verified from Step #2. The database user must have read-access at a minimum. Periscope recommends that this be the same database that the user will query and build charts against.

Note: It is possible to configure the same Redshift database across multiple spaces for DEYW purposes. If this is a use-case you would like to support, please keep the following in mind:

  • All materialized views and CSV’s are stored in a “periscope_views” schema. Views and CSV’s cannot share names across spaces with the same DEYW Redshift database.
  • If the same READ user is used in more than one space, it will be able to access materialized views that are not present on that specific space via the SQL editor.
  • If different WRITE users are used, you will need to GRANT the same permissions for that user that were granted for the others.

4. Create and Permission Write User/Group

Fill out the form below and press "Apply" to generate the SQL script to execute in Redshift.

<table>
 <tr><th>Admin Username:</th><th><input type="text" id="admin_user" value="periscope_admin"></th>
 <tr><th>Admin Password:</th><th><input type="password" id="admin_password"></th>
 <tr><th>Read Username:</th><th><input type="text" id="read_user" value="periscope_read"></th>
 <tr><th>Database:</th><th><input type="text" id="database" placeholder="Same as Step #3"></th>
 <tr><th>Schemas:</th><th><textarea id="schemas" rows="4" cols="50" placeholder="Enter your database schemas on separate lines."></textarea></th>
</table>
<button type="button" id="byow_apply" style="height: 30px; line-height: 30px; border-radius: 2px; font-size: 12px; background: #59cec5;">Apply</button>

After pressing apply, copy and paste the SQL code and run it in Redshift as an administrator.

<blockquote id="sql_script">CREATE SCHEMA periscope_views;<br>
CREATE USER <span class="admin_user">&ltperiscope_write&gt</span> PASSWORD '<span class="admin_password">&ltpassword&gt</span>';<br>
GRANT USAGE ON SCHEMA periscope_views TO <span class="admin_user">&ltperiscope_write&gt</span> WITH GRANT OPTION;<br>
GRANT CREATE ON SCHEMA periscope_views TO <span class="admin_user">&ltperiscope_write&gt</span>;<br>
GRANT USAGE ON SCHEMA periscope_views TO <span class="read_user">&ltperiscope_read&gt</span>;<br>
GRANT SELECT ON ALL TABLES IN SCHEMA periscope_views TO <span class="read_user">&ltperiscope_read&gt</span>;<br><br>
-- Optionally, create a user group for easy management<br>
-- CREATE GROUP &ltperiscope_group&gt WITH USER <span class="admin_user">&ltperiscope_write&gt</span>, <span class="read_user">&ltperiscope_read&gt</span>;
<span id="schema_grants"></span>
</blockquote>

5. Apply the Write User

Click on the “Settings” gear icon in the lower-left corner of Periscope, and navigate to the same “Database” page you used to connect the database in Step #3. If there is a drop-down available, ensure that the database from Step #3 has been selected.

At the bottom of the form, there should be a section to input the “Warehouse Admin Credentials”. This will be the username and password configured in Step #5. Fill in the text boxes with the correct credentials and hit the “Submit” button.

Acknowledgements

Capitalized terms used herein shall have the meanings assigned to them in database language.

DEYW Service”: Periscope’s Data Engine on Your Warehouse (“DEYW”) Service allows and offers a User, in addition to, connecting an origin Database where they can only QUERY that database; bring User’s choice of warehouse, where User can leverage Periscope’s technology to extract tables from other sources, and MATERIALIZE view into said warehouse.

USER”: Any entity, including but not limited to, a prospect or an existing customer of Periscope allowing Periscope access as set forth herein to perform the DEYW Service.

Periscope”: Periscope, Inc. The Service Provider for DEYW Service.

READ”: The ability to DISPLAY, LIST, VIEW.

WRITE”: The ability to modify, to the extent that such command shall permit but are not limited to, ADD, CREATE, DELETE, RENAME, UPDATE.

As expressly set forth herein, User grants Periscope READ, and WRITE permissions in order to perform the DEYW Service. This permission allows Periscope to READ existing schema, tables, and rows and allows Periscope to WRITE a schema, WRITE tables within that schema, and WRITE to those tables within User’s data warehouse of choice.

By virtue of executing the aforementioned instructions and providing Periscope READ, WRITE access to your warehouse, you expressly accept the DEYW Service governed by Periscope's Terms & Conditions.


<script>
function update_sql_script(){
 console.log('SQL');
 admin_user = document.getElementById("admin_user").value;
 admin_password = document.getElementById("admin_password").value;
 read_user = document.getElementById("read_user").value;
 database = document.getElementById("database").value;
 schemas = document.getElementById("schemas").value;
 console.log(schemas);
 Array.from(document.getElementsByClassName("admin_user")).forEach(function(e){
   e.innerHTML = admin_user;
 });
 Array.from(document.getElementsByClassName("admin_password")).forEach(function(e){
   e.innerHTML = admin_password;
 });
 Array.from(document.getElementsByClassName("read_user")).forEach(function(e){
   e.innerHTML = read_user;
 });
 Array.from(document.getElementsByClassName("database")).forEach(function(e){
   e.innerHTML = database;
 });
 schema_grants = document.getElementById("schema_grants");
 schema_grants.innerHTML = '';
 schemas = schemas.split('\n');
 Array.from(schemas).forEach(function(schema){
   schema_grants.innerHTML = schema_grants.innerHTML + '<br><br>GRANT USAGE ON SCHEMA ' + schema + ' TO ' + admin_user + ';<br>';
   schema_grants.innerHTML = schema_grants.innerHTML + 'GRANT SELECT ON ALL TABLES IN SCHEMA ' + schema + ' TO ' + admin_user + ';<br>';
   schema_grants.innerHTML = schema_grants.innerHTML + '<br>GRANT USAGE ON SCHEMA ' + schema + ' TO ' + read_user + ';<br>';
   schema_grants.innerHTML = schema_grants.innerHTML + 'GRANT SELECT ON ALL TABLES IN SCHEMA ' + schema + ' TO ' + read_user + ';';
 });
}
document.getElementById('byow_apply').addEventListener('click', update_sql_script);
</script>

Our support team is ready to help