Get SQLFeatureNotSupportedException: [Vertica][JDBC](10220) when using Sqoop?

Hi dr_rizz

Vertica's JDBC does not implement the BLOB/CLOB interfaces, hence the SQLFeatureNotSupportedException. The parameter passed to getBlob is the name of a binary column in the result set; it isn't very interesting to the problem at hand.

These API's are optional in JDBC. They are designed to work with very large values, making it easier to seek and read sections of the value without having to send the entire value back to the client.

Vertica supports long varchar/varbinary up to 32MB, but it does not have the underlying protocol to support arbitrary seeks, read and writes of of a partial value that are required in order to implement the JDBC BLOB/CLOB API. If Vertica ever changes to support large (i.e. gigabyte-sized) BLOB/CLOB objects, then this API may be implemented, but until then, the best way to read a binary value is using one of the two other API's for binary data.

If you have a long varbinary column, you can use ResultSet.getBinaryStream() or ResultSet.getBytes() to read the value. getBinaryStream() is probably the ideal way for long varbinary because it does not require having the whole value in memory at once. BLOB could do this too, but unlike BLOB, the value in its entirety is always streamed to the client

I am not very familiar with Sqoop, but hopefully there is a way to retrieve the data using an one of these API's instead. In other tools, there is often some size threshold where it switches to using the BLOB API, and if so perhaps it is configurable.

Another possibility may be to customize the behavior via a small sqoop plugin.
I did some quick digging into the code around the stack you provided, and at a glance, it looks like the line below is how sqoop decided to generate a class that uses the unsupported BLOB API:
https://github.com/infinidb/sqoop/blob/master/src/java/org/apache/sqoop/manager/ConnManager.java#L139

It would of course require a fair bit of effort and testing, but if that is the line to change, you should be able to wire together a simple plugin that customizes that behavior to follow the VARBINARY path instead.

I can't promise this is the exact solution, but I think something like this is the best path forward. It may even make sense to submit a bug ticket to sqoop do this by default instead of using blobs. If sqoop always intends to read the whole value (seems likely since sqoop is used to move data wholesale between db's and hadoop), the BLOB API seems like it is not the best choice for LONG VARBINARY, because not all databases support this API, and those that do probably pay a cost to support the full BLOB API semantics when only a simple, forward-only read of the value would suffice.

Tom