Quick Guide: Run Accelerate Queries Locally

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 on the Connect Server 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.
  • To test a feed that only has one query, copy and paste the query into Drill and run it.
  • To test a feed that uses setup queries/temporary tables:

    • 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

    • 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 ),

    • 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).
    • 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.
• From SS&E Admin, navigate to SIS Integration > Datafeeds and select View under the Extract Queries query button on the SIS data feeds page.
• Select "Copy to Clipboard" to copy all queries for the feed.
• Paste the query into the SQL editor from the Connect Server and run the query.