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.