PROBLEM DESCRIPTION:


Some of the Oracle-Logs based Ingestion job will fail with the below error message:


Exception in thread "main" net.sf.jsqlparser.JSQLParserException
  at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:56)
  at name.jacktang.JsqlparserTest.main(JsqlparserTest.java:16)
Caused by: net.sf.jsqlparser.parser.ParseException:


This Article helps to update the jsqlparser jars from present version(0.* version) to the latest version 1.1 version to handle special characters present in the data while ingestion.


SOLUTION


Please follow the below steps to accomplish the jsqlparser upgrade:


1. Download the latest JSQLParser jars using the below command in Edge Node

cd $IW_HOME
wget https://s3.amazonaws.com/infoworks-resources/jsqlparser-1.1.jar


2. Remove the Older version of JSQLParser Jar( jsqlparser-0.8.0.jar) from $IW_HOME/lib/jsqlparser/ directory

rm $IW_HOME/lib/jsqlparser/jsqlparser-0.8.0.jar


3. Navigate to Infowork's Conf Directory

cd $IW_HOME/conf/


4. Grep for Iw_Temp value in conf.properties file using the below command

 cat conf.properties | grep "iw_hdfs_temp_home"


5. Copy the result of the previous command i.e below command

cat conf.properties | OUTPUT_OF_STEP 4


7. Run the below command to validate the existence of older jsql parser jars in IW_TEMP directory of Infoworks in HDFS

 hadoop fs -ls /path_from_step_4/iwjobs/libjars/path_from_step_5/lib/jsqlparser/


8. If that folder exists then it should have

 jsqlparser-0.8.0.jar


9. Delete the folder 

hadoop fs -rm -r /path_from_step_4/iwjobs/libjars/path_from_step_5/lib/jsqlparser


10. mv downloaded jsqlparser-1.1.jar to 

mv $IW_HOME/jsqlparser-1.1.jar $IW_HOME/lib/jsqlparser/


After the above steps latest JSQLParser is copied into Infoworks lib folder and cache related to older version of JSQLParser jar have been removed.

Users can submit the same job to successfully ingest the data.