SIS Integration Troubleshooting: Apache Drill Errors (CSV Only)

Overview

Apache Drill is a third-party tool installed on the Connect Server and used to query CSV files using SQL. These errors apply only to institutions using Connect Method 1: CSV as institutions using Connect Method 2: Direct Database Connection do not use Apache Drill.

First Steps

If you suspect issues with Apache Drill, you will typically see an Error in External Logging within SS&E (SS&E Administration > Advanced > External Logging  https://{yourinstitution}.avisoapp.com/aviso/logMessage/list).

First, ensure that Apache Drill is running on the Connect Server by Using Chrome on the Connect Server, attempt to visit http://localhost:8047. If the page does NOT load, see Drill Service Troubleshooting for more information and troubleshooting.

If Apache Drill is running but you are still experiencing errors, see common errors and solutions below. 

Common Apache Drill Service Errors

This site can’t provide a secure connection

When connecting to localhost:8047 on the Connect Server via a web browser, this error may happen when https:// is entered in front of localhost:8047.

Solution

Remove the https:// from the url.

Error: Failure in connecting to Drill - One Feed Only

This error occurs only on one feed. This could indicate that the C: drive is full. This can happen due to Drill temp files filling up the C: drive. 

Failure in connecting to Drill: oadd.org.apache.drill.exec.rpc.RpcException: CONNECTION : oadd.io.netty.channel.AbstractChannel$AnnotatedConnectException: Connection refused: no further information: /127.0.0.1:31010

Solution

See How to clear space on the Connect Server C drive.

Error: Failure in connecting to Drill - All Feeds

This error occurs on all feeds.

Failure in connecting to Drill: oadd.org.apache.drill.exec.rpc.RpcException: CONNECTION : oadd.io.netty.channel.AbstractChannel$AnnotatedConnectException: Connection refused: no further information: /127.0.0.1:31010

This error happens for all feeds when the Drill service isn’t running. This may happen if the user running Drill's password has changed or the user is disabled or locked. Most of the time, this will be the SS&E/Aviso service account user and local admin on the Connect Server. The C: drive being full can also cause this issue. See  How to clear space on the Connect Server C drive. 

Solution

1. Ensure that the user running Drill is not disabled or locked.
2. Update the user’s password in NSSM.
3. If enabling the user or correcting the password in NSSM does not resolve the issue, Drill may need to be reinstalled. Please submit a request to the Support team for help removing and reinstalling a compatible version of Drill.
4. Check C:\tmp\drill\profiles to see if the folder is filling up the C: drive. See How to clear space on the Connect Server C drive. 

Error: System Error: ChannelClosedException: Channel closed

SYSTEM ERROR: ChannelClosedException: Channel closed /127.0.0.1:62242 <--> /127.0.0.1:31010. 
Query submission to Drillbit failed. Please, refer to logs for more information. 
[Error Id: 5cb959ea-db30-473a-8b09-6d738f235cdb ]

Solution

C: Drive may be full. Check C:\tmp\drill\profiles to see if the folder is filling up the C: drive. See How to clear space on the Connect Server C drive.

Error: DATA_READ ERROR

Error

DATA_READ ERROR: C:\Aviso\csv\transcript_course.csv 
(The process cannot access the file because it is being used by another process) 
File Path file:/C:/Aviso/csv/transcript_course.csv Fragment 0:0 
[Error Id: 4350160f-ae61-414f-aee8-5515c970d6bb on SERVERNAME]

Solution

The file may be open by another user or process, like the CSV extracts or reports. Try rescheduling extracts/reports to ensure they are not writing to the file while Connect is running.

Error: PARSE ERROR: Encountered "as" at line

This error occurs with some newer versions of Drill (first identified ~6/2023).

org.apache.drill.common.exceptions.UserRemoteException: PARSE ERROR: Encountered "as" at line 13, column 31.
cast(csse.COLUMNS[10] as date) as expirationDate,
lower(trim(csse.COLUMNS[11])) as monday,
                                 ^
lower(trim(csse.COLUMNS[12])) as tuesday,

Solution

cast(csse.COLUMNS[10] as date) as expirationDate,
lower(trim(csse.COLUMNS[11])) as `monday`,
lower(trim(csse.COLUMNS[12])) as `tuesday`,

Use ticks on certain reserved field names like days of the week.

Error: java.lang.StackOverflowError

This error is related to text parsing, which indicates something is wrong with the CSV file format.

2023-10-17 09:17:26.615 INFO 2984 --- [Client-1] o.a.d.j.i.DrillCursor$ResultsListener : [#178] Query failed:
oadd.org.apache.drill.common.exceptions.UserRemoteException: EXECUTION_ERROR ERROR: null
Read failed for reader: CompliantTextBatchReaderFragment: 0:0
Caused by: java.lang.StackOverflowError: at org.apache.drill.exec.store.easy.text.reader.TextReader.parseQuotedValue(TextReader.java:251) ~[na:na] 
at org.apache.drill.exec.store.easy.text.reader.TextReader.parseQuotedValue(TextReader.java:317) ~[na:na]

Solution

1. In some cases, this happened because the file format was changed from CSV/comma-delimited to tab-delimited. 
2. For institutions using Informer 5, updates to Informer have caused the file format to change.

Error: Validation Error

Error: java.sql.SQLException: VALIDATION ERROR: Schema [[dfs, root]] is not valid with respect to either root schema or current default schema.

Solution

Verify the dfs.root location in the drill configuration is pointing to the correct location where CSV files are stored and that the CSV file exists in that location.

1. Open Apache Drill from a web browser on the Connect Server: localhost:8047
2. Navigate to Storage > Enabled Storage Plugins > dfs > Update

It should typically look like this, with the path set to the correct CSV location on the Connect Server:

"root": {
"location": "/Aviso/csv",
"writable": false,
"defaultInputFormat": null,
"allowAccessOutsideWorkspace": false
}

Note: Define the path relative to the C:\ and use forward slashes ( / ) instead of backslashes. Example C:\Aviso\CSV would be /Aviso/csv

Issue: \r Character in JSON Data

\r character is included in the JSON data, but is not present in the Drill UI results locally on the Connect Server.

Solution

1. Using double quotes in all CSV fields will solve this as the \r is outside of the end quote. In addition, double quotes prevent special characters from causing format issues with the CSV (commas within fields, etc.)
2. Alternatively, the Apache Drill dfs Storage plugin can be configured to set the csv > lineDelimiter value to "\r\n" instead of "\n".
   1. Open Apache Drill from a web browser on the Connect Server: localhost:8047
   2. Navigate to Storage > Enabled Storage Plugins > dfs > Update

Error: Already had POJO for id

Occurs on personPhoneAllIds feed when there are duplicate IDs in the CSV and they are not made unique by the Accelerate query. May occur in other feeds as well.

SYSTEM ERROR: IllegalStateException: Already had POJO for id (java.lang.Integer) 
[[ObjectId: key=29, type=com.fasterxml.jackson.databind.deser.impl.PropertyBasedObjectIdGenerator, scope=java.lang.Object]] 
Fragment 2:0 Please, refer to logs for more information. [Error Id: c393917c-f610-45ff-8d66-4a6df910a9c6 on SERVERNAME]

Solution

There are a few possible solutions.

1. The CSV file must have unique IDs.
2. If unique IDs are not available, concatenate values (like personId, number, type) in the CSV extract to create a unique ID.
3. If there are still duplicate records, use other strategies or logic in Accelerate to make a unique ID or filter out records. 
   1. Select only one phone number per phone type and filter out the rest.
   2. Use row numbers to create unique records. Some examples:

concat(COLUMNS[0], '-', row_number() over(partition by COLUMNS[0] order by COLUMNS[0])) as id,
--OR--
min(COLUMNS[0]) as id,
--OR--
max(COLUMNS[0]) as id,

Additional Information

There may be other types of errors that do not involve Apache Drill. See these quick guides and other SIS Integration troubleshooting articles:

• Quick Guide: SIS Integration Success & Failure
• Quick Guide: SIS Integration Troubleshooting
• Quick Guide: Data Integration Changes
• Data Stream Out of Date Troubleshooting
• SIS Integration Troubleshooting Guide

For Apache Drill Errors not listed here, see Apache Drill Troubleshooting Documentation or submit a request to Watermark Support for assistance.