Troubleshooting Apache SqoopPDF version

Unable to read Sqoop metastore created by an older HSQLDB version

After upgrading to certain Cloudera versions, you may encounter issues in reading the Sqoop metastore that was created using an older version of HyperSQL Database (HSQLDB). Learn how to upgrade the Sqoop metastore to resolve this issue.

Cloudera upgraded the HSQLDB dependency in the following versions to the newest HSQLDB 2.7.1 version:
  • CDP Private Cloud Base 7.1.7 SP2
  • CDP Private Cloud Base 7.1.8 Cumulative hotfix 4
  • CDP Public Cloud 7.2.16

The version upgrade causes incompatibility issues in Sqoop jobs that are stored in HSQLDB. Therefore, the Sqoop metastore should be upgraded after you upgrade to the above-mentioned Cloudera versions or higher.

Choose one of the following procedures based on how the Sqoop metastore is created:

Perform the following steps if the Sqoop metastore is created using Sqoop:

  1. If the Sqoop metastore is started as a service then stop the service by running this command on the host where the service is running:
    /opt/cloudera/parcels/CDH/lib/sqoop/stop-metastore.sh -p <path to Sqoop metastore pid file>
  2. Download HSQLDB 2.3.6 and SqlTool JAR files to the host by running the following commands:
    wget https://repo1.maven.org/maven2/org/hsqldb/sqltool/2.3.6/sqltool-2.3.6.jar
wget https://repo1.maven.org/maven2/org/hsqldb/hsqldb/2.3.6/hsqldb-2.3.6.jar
  3. Run the following command to upgrade and convert the Sqoop metastore database files using SqlTool:
    For on-demand Sqoop metastore:
    $JAVA_HOME/bin/java -cp "sqltool-2.3.6.jar:hsqldb-2.3.6.jar" org.hsqldb.cmdline.SqlTool --driver org.hsqldb.jdbc.JDBCDriver  --inlineRc="url=jdbc:hsqldb:file:~/.sqoop/metastore.db;shutdown=true;user=SA,password=" --sql="SELECT * FROM SQOOP_ROOT;"
    For Sqoop metastore as a service:
    $JAVA_HOME/bin/java -cp "sqltool-2.3.6.jar:hsqldb-2.3.6.jar" org.hsqldb.cmdline.SqlTool --driver org.hsqldb.jdbc.JDBCDriver  --inlineRc="url=jdbc:hsqldb:file:~/.sqoop/shared-metastore.db;shutdown=true;user=SA,password=" --sql="SELECT * FROM SQOOP_ROOT;"

    The Sqoop metastore is upgraded and the database files are converted to a format that can easily be read by HSQLDB 2.7.1.

  4. If the Sqoop metastore service was stopped earlier, restart the service:
    /opt/cloudera/parcels/CDH/lib/sqoop/start-metastore.sh -p <path to Sqoop metastore pid file> -l <path to Sqoop metastore logs dir>
  5. You can choose to remove the downloaded HSQLDB JAR files from the host.