Cloudera Docs

Unable to read Sqoop metastore created by an older HSQLDB version

After upgrading to certain Cloudera Data Platform (CDP) 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 CDP 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.

Perform the following steps if the Sqoop metastore is created in a separate HSQLDB instance:

  1. Login to the HSQLDB service host and stop the service. For more information, see the HSQLDB documentation.
  2. Work with your IT team to determine the HSQLDB store file that is created to store the Sqoop metastore information.
  3. 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
  4. Run the following command to upgrade and convert the Sqoop metastore database files using SqlTool:
    $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:<PATH OF SQOOP METASTORE FILE>;shutdown=true;user=SA,password=" --sql="SELECT * FROM SQOOP_ROOT;"
  5. If the HSQLDB service hosts multiple databases then each of the store files must be upgraded as described in the previous step.

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

  6. Restart the HSQLDB service. For more information, see the HSQLDB documentation.
  7. You can choose to remove the downloaded HSQLDB JAR files from the host.