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
  1. 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 )
  1. If there are multiple setup queries, only use “with” on the first one, and add a comma after each subsequent query.
  2. Paste the query into Drill and run it through Drill to check if it runs successfully or not.  

 

Was this article helpful?
0 out of 0 found this helpful

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.