Testing Accelerate Queries Directly on the Connect Server

Overview

This article can help the SS&E Administrator/local IT/organization's SIS Integration data lead determine whether there are issues in the Accelerate query itself or whether there are issues in the Connect Job configuration.

• If the query runs successfully by itself in Drill, this means that the Connect2 task settings need additional troubleshooting in the Connect server Scheduled Task.
• If the query does not run successfully by itself, the child Accelerate query will most likely need to be updated/refactored for efficiency.
  • • This will most likely require a technical resource. To reach a technical resource, the first step is to open a support ticket.
    • Please include a summary of the reported issue, the datafeed name, and the query results so that the ticket can be routed quickly for assistance.

CSV Method - Using Apache Drill

To use Apache Drill locally on the Connect server:

1. On the Connect server, open a browser and navigate to localhost:8047, then select Query.
2. Edit the query in a text editor.
   1. From SSE Admin > SIS Integration > Datafeeds Administration, scroll to the data feed and select "View" from the Extract Queries column. This will display all the queries used for the selected data feed.
   2. To test a data feed that uses setup queries/temporary tables:
      1. Copy/paste the setup query 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

2. Replace "CREATE TEMPORARY TABLE" with "with", and add parentheses around the select statement.

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 )

3. If there are multiple setup queries, only use “with” on the first one, and add a comma after each subsequent query.
4. Paste the query into Drill and run it through Drill to check if it runs successfully or not.