Fix Hadoop Sqoop Import Thrown Exception “ERROR manager.SqlManager: Error reading from database: java.sql.SQLException”

When you’re trying to import and got the kind of log message you see below, then the problem came from an non explicit declaration of driver. Although there are other reasons for this error to be thrown, like old driver, we’ll specifically solve the non-explicit declaration of driver on Sqoop.

ERROR manager.SqlManager: Error reading from database: java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@6eb58296 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries. java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@6eb58296 is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:934) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931) at com.mysql.jdbc.MysqlIO.checkForOutstandingStreamingData(MysqlIO.java:2735) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1899) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2569) at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1524) at com.mysql.jdbc.ConnectionImpl.getMaxBytesPerChar(ConnectionImpl.java:3003) at com.mysql.jdbc.Field.getMaxBytesPerCharacter(Field.java:602) at com.mysql.jdbc.ResultSetMetaData.getPrecision(ResultSetMetaData.java:445) at org.apache.sqoop.manager.SqlManager.getColumnInfoForRawQuery(SqlManager.java:286) at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:241) at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:227) at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:295) at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1845) at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1645) at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:107) at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:478) at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:605) at org.apache.sqoop.Sqoop.run(Sqoop.java:148) at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76) at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:184) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:226) at org.apache.sqoop.Sqoop.runTool(Sqoop.java:235) at org.apache.sqoop.Sqoop.main(Sqoop.java:244)

Declaring Driver Explicitly

STEP 1

To fix the issue you have to add the driver explicitly, in my case I’m using MySQL. So, I’ll add --driver=com.mysql.jdbc.Driver on my command.

sqoop import --connect jdbc:mysql://localhost/test \
             --username=root \
             --table=salaries \
             --driver=com.mysql.jdbc.Driver

Hadoop HDFS “No Such File or Directory” on `ls`

After a fresh setup and installation of hadoop then when you run hdfs dfs -ls and recieved an ls: '.': No such file or directory instead of a directory listing; you probably missed creating your user’s home folder. The default base folder everytime you use any relative paths or implicitly indicating a directory parameter, always resolves to your home folder. By default this folder’s non-existent and should be created after a fresh installation. To set it up, you only have to manually create the directory for your user.

Step 1

Create the directory tree.

hdfs dfs -mkdir -p /user/hdfsuser

Step 2

Validate the creation of the folder by reentering hdfs dfs -ls.

Setting JAVA_HOME Dynamically on Linux

There are two flavor of JDK you can get from any Linux distros, OpenJDK or from Oracle. These software can be installed manually, assigning the static path on JAVA_HOME. What if you installed it directly from yum or apt, how can you assign the correct Java directory for a managed installation?

Note: This how-to assumes you’re using bash as your shell. Feel free to apply it based on what shell you’re using.

User Implementation

STEP 1

Open your .bashrc file using your favorite editor. In this case, I’m using vim.

vi ~/.bashrc

STEP 2

At the end of your .bashrc place the following snippets.

export JAVA_HOME=$(dirname $(dirname $(readlink -f $(which javac))))

Then save.

STEP 3

Source your .bashr and check if your JAVA_HOME‘s set.

source ~/.bashrc
printenv JAVA_HOME

Your JAVA_HOME should contain the directory of you JDK installation.

System-wide Implementation

Almost same with our previous steps, but instead of editing our .bashrc we’ll be creating a script file on /etc/profile.d

STEP 1

Create a script file on /etc/profile.d and open the file, or you can do both in one go. But for the sake of this tutorial will do the long one.

touch java.sh
vi java.sh

Note: If you’re not root, you have to sudo on the commands.

STEP 2

Place this on the file

#!/usr/local/env bash

export JAVA_HOME=$(dirname $(dirname $(readlink -f $(which javac))))

Then save.

STEP 3

Source the java.sh and check if JAVA_HOME‘s set. You can also logout then log back-in again to check if the script’s loaded.

source java.sh
printenv  JAVA_HOME

Once done, make sure the output shows the location of your Java or JDK installation.

Install Latest Git Version on Centos 7 and Centos 6 server

If you happen to download the minimal iso of centos on from it’s website. You can get Git thru Centos’ default repository but you’re going to have an older version of Git. (At the time of writing the default repo has Git version 1.8)

Getting Git thru Default Repo

yum install git

Getting New Version of Git

Step 1

Install all required dependency to compile from source

yum groupinstall 'Development Tools'
yum install curl-devel expat-devel gettext-devel openssl-devel zlib-devel perl-ExtUtils-MakeMaker
yum install wget

Step 2

Download the tarbal from Git’s Github repository. Git Releases At the time of writing the latest stable version is 2.9.3.

wget https://github.com/git/git/archive/v2.9.3.tar.gz

Extract the tarbal

tar -zxf v2.9.3.tar.gz

Step 3

Go to the extracted directory and compile the source.

cd git-2.9.3
make prefix=/usr/local/git all
make prefix=/usr/local/git install
echo "export PATH=$PATH:/usr/local/git/bin" >> /etc/bashrc
source /etc/bashrc

Step 4

To verify the installation check the Git version

git --version

References

Using Linux XCLIP

xclip is a command line utility for setting and getting values on the clipboard using other command’s output or input. Instead of directly using shortcut keys on the terminal, xclip can be used likewise except you have to pipe the output and input from other commands.

In general here’s the three(3) selection for xclip

  • Primary – Used for the 3rd mouse button
  • Secondary – Act’s as an alternative to primary
  • Clipboard – GUI or window-style clipboard using the shortcut keys (ctrl+c, ctrl+shift+v)

Sample Usage

Copying directory listing to clipboard

ls | xclip -selection c
# Alternative
ls | xclip -selection clipboard

Outputing clipboard

xclip -o -selection c

Outputting clipboard content to a file

xclip -o selection c > somefile

Aliasing xclip

Since clipboard‘s the common selection for xclip we can just create an alias on our ~/.bashrc file or system-wide /etc/bashrc, if you’re using bash.

USER

echo "alias xclip='xclip -selection clipboard'" >> ~/.bashrc 
source ~/.bashrc

SYSTEM-WIDE

echo "alias xclip='xclip -selection clipboard'" >> /etc/bashrc
source /etc/bashrc

References