Run Accelerate Queries Locally on the Connect Server

Overview

This is useful to determine whether an Accelerate query is functional isolated from any issues that may be caused by Connect/Accelerate, or to test query changes locally before updating the Accelerate configuration.

CSV Method - Using Apache Drill

For institutions using Connect Method 1: CSV, follow these steps to use Apache Drill locally on the Connect server:

  • In a browser on the Connect server, navigate to localhost:8047 and select Query.
  • From SS&E Admin, navigate to SIS Integration > Datafeeds and use the View button under the “Extract Queries” query button on the SIS data feeds page for a clean version of all queries used in the feed 
    (a.) To test a feed that only has one query, copy and paste the query into Drill and run it.
    (b.) To test a feed that uses setup queries/temporary tables:
             (i.) Copy/paste all setup and main queries into a text editor, with the setup queries above                       the  main query
CREATE TEMPORARY TABLE personIds as

select 'DUMMY_RECORD' as personId

union

select

COLUMNS[0] as personId

from dfs.root.`person.csv`

where nullif(COLUMNS[0], '') is not null

        (c.) Replace "CREATE TEMPORARY TABLE" with "with", and add parentheses around the select statement, replacing the end semicolon with closing parenthesis and a comma.

with personIds as (

select 'DUMMY_RECORD' as personId

union

select

COLUMNS[0] as personId

from dfs.root.`person.csv`

where nullif(COLUMNS[0], '') is not null ),

                 (i.) If there are multiple setup queries, only use “with” on the first one, and add a comma after each (except for the last setup query before the mainQuery).

     (d.) Paste the query into Drill and run it.

Direct DB Method - Querying the Database

For institutions using Connect Method 2: Direct Database, follow these steps to use your database client from the Connect server.

  • Locate pgAdmin, MS SQL Server Management Studio or Oracle SQL Developer. This should have been installed on the Connect server by the Institutional IT staff during setup/implementation.
  • Connect to the database connection using the connection details and credentials for the SS&E database user.
  • Copy and paste the Accelerate query into the SQL editor.

Articles in this section

See more
How to Contact Support
There are many ways to reach out! Click here for our support options.
Watermark Academy
Click to access the Watermark Academy for consultation, training, and implementation companion courses.
Customer Community
Can’t find the answer? Ask fellow users how they’re making the most of Watermark in our Community!