Sqoop Oracle and MS Access

["Giovanni M.L.G." <gi...@gmail.com>]

Hi,

I've been using sqoop for a while and have recently come across two issues:


   1. We have a use case where we import data as avro from an oracle
   database (so it's a standard use case).

   The problem is that there are really a lot of tables, and most of these
   tables contain fields with "strange" types such as "Timestamp(6) with
   Timezone".

   By default sqoop complains about these fields and breaks, unless one
   manually specifies how to convert these columns with "--map-column-java".
   (We tried this for a few tables, mapping unsupported types to String).

   As I mentioned the number of tables is so large that using this
   workaround is not a viable option.

   Is there a way to tell sqoop to convert unknown types to String when
   importing as avro? [I couldn't find anything on the documentation].


   2. Another use case we are facing is importing data from Microsoft
   Access Dbs. Is there a way to do this with sqoop? [I tried using a few jdbc
   drivers for Access, but with no success]


Thanks for the support!

Cheers,


GG

Re: Sqoop Oracle and MS Access

[Sumit Sarkar <Su...@progress.com>]

Giovanni,
What is the issue you are facing with Ms Access through the JDBC drivers?  We are working on some contributions to make Sqoop work better with third party JDBC drivers.
Regarding the Oracle question, the DataDirect Oracle JDBC driver has a property to convert those types to string if you cannot find a way through Sqoop.  However, it is not a free solution.

http://media.datadirect.com/download/docs/jdbc/alljdbc/help.html?_ga=1.23504382.576670223.1301850762#page/userguide/rfi1359986091890.html#

FetchTSWTZasTimestamp
Purpose
Determines whether column values with the TIMESTAMP WITH TIME ZONE data type are returned as a JDBC CHAR or TIMESTAMP data type.
This property only applies to connections to Oracle9i and higher.
Valid Values
true | false
Behavior
If set to true, column values with the TIMESTAMP WITH TIME ZONE data type are returned as a JDBC TIMESTAMP data type.
If set to false, column values with the TIMESTAMP WITH TIME ZONE data type are returned as a JDBC VARCHAR data type.
Default
false
Data Type
boolean
See also

Sent from my iPad

On Nov 6, 2015, at 3:33 AM, "Giovanni M.L.G." <gi...@gmail.com>> wrote:

Hi,

I've been using sqoop for a while and have recently come across two issues:


  1.  We have a use case where we import data as avro from an oracle database (so it's a standard use case).

The problem is that there are really a lot of tables, and most of these tables contain fields with "strange" types such as "Timestamp(6) with Timezone".

By default sqoop complains about these fields and breaks, unless one manually specifies how to convert these columns with "--map-column-java". (We tried this for a few tables, mapping unsupported types to String).

As I mentioned the number of tables is so large that using this workaround is not a viable option.

Is there a way to tell sqoop to convert unknown types to String when importing as avro? [I couldn't find anything on the documentation].


  2.  Another use case we are facing is importing data from Microsoft Access Dbs. Is there a way to do this with sqoop? [I tried using a few jdbc drivers for Access, but with no success]

Thanks for the support!

Cheers,


GG

RE: Sqoop Oracle and MS Access

[David Robson <Da...@software.dell.com>]

Sqoop 1.4.5 with direct should work be fine using TIMESTAMP WITH TIME ZONE data types.

Could you please share the command you are running and add the “—verbose” flag and share the log as well? Preferably the log from the Sqoop client as well as the map tasks. If possible could you also share the create table statement you are using?

From: Giovanni M.L.G. [mailto:giovanni.gargioni@gmail.com]
Sent: Monday, 9 November 2015 8:30 PM
To: user@sqoop.apache.org
Subject: Re: Sqoop Oracle and MS Access

Hi David and Sumit,

thanks for the quick answer.

Regarding oracle:

we are using sqoop 1.4.5-cdh5.4.4 with the --direct option. We have no problem importing dates and timestamps with no timezone info.

The problem comes in when timezone info is there [We get something like "Cannot convert SQL-type -102" error].
The only workoround we found is the one i mentioned [using --map-column-java].

Do you think things change with sqoop 1.4.6?

Regarding Access:

our use case is the following: an .mdb or .accdb file gets shipped to a node in our cluster with sftp [we can't connect directly to the windows server] and we should import all the tables in that file into hive.

I haven't tried the easysoft driver because it seemed that it required the .mdb to be in a windows server

I have tried with the UCanAccess jdbc driver.

Since the access db file is actually already in our cluster I don't even know if sqoop would be the right tool for reading the data from it.

Any suggestions would be greatly appreciated.

Cheers,

GG

Re: Sqoop Oracle and MS Access

["Giovanni M.L.G." <gi...@gmail.com>]

Hi David and Sumit,

thanks for the quick answer.

Regarding oracle:


we are using sqoop 1.4.5-cdh5.4.4 with the --direct option. We have no
problem importing dates and timestamps with no timezone info.

The problem comes in when timezone info is there [We get something like
"Cannot convert SQL-type -102" error].
The only workoround we found is the one i mentioned [using
--map-column-java].


Do you think things change with sqoop 1.4.6?

Regarding Access:


our use case is the following: an .mdb or .accdb file gets shipped to a
node in our cluster with sftp [we can't connect directly to the windows
server] and we should import all the tables in that file into hive.

I haven't tried the easysoft driver because it seemed that it required the
.mdb to be in a windows server


I have tried with the UCanAccess jdbc driver.


Since the access db file is actually already in our cluster I don't even
know if sqoop would be the right tool for reading the data from it.

Any suggestions would be greatly appreciated.

Cheers,

GG

RE: Sqoop Oracle and MS Access

[David Robson <Da...@software.dell.com>]

Giovanni,

Are you using Sqoop 1.4.6? Have you tried using the “—direct” option for Oracle in order to utilise the Data Connector for Oracle and Hadoop?

You can get more details in the documentation here: http://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html#_data_connector_for_oracle_and_hadoop

This has much better support for Oracle data types and also better performance. By default it will convert timestamp with timezone to String as Sqoop normally stores dates as a number which loses the timezone information. There is a parameter documented here if you prefer to store the data as a number http://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html#_java_sql_timestamp

In regards to Access I have never tried this with Sqoop but did you try the JDBC to ODBC bridge driver? From memory the easysoft driver lets you run a bridge on a Windows server that you can connect to from the Linux server so it may work.

David

From: Giovanni M.L.G. [mailto:giovanni.gargioni@gmail.com]
Sent: Friday, 6 November 2015 7:33 PM
To: user@sqoop.apache.org
Subject: Sqoop Oracle and MS Access

Hi,

I've been using sqoop for a while and have recently come across two issues:


  1.  We have a use case where we import data as avro from an oracle database (so it's a standard use case).

The problem is that there are really a lot of tables, and most of these tables contain fields with "strange" types such as "Timestamp(6) with Timezone".

By default sqoop complains about these fields and breaks, unless one manually specifies how to convert these columns with "--map-column-java". (We tried this for a few tables, mapping unsupported types to String).

As I mentioned the number of tables is so large that using this workaround is not a viable option.

Is there a way to tell sqoop to convert unknown types to String when importing as avro? [I couldn't find anything on the documentation].

  2.  Another use case we are facing is importing data from Microsoft Access Dbs. Is there a way to do this with sqoop? [I tried using a few jdbc drivers for Access, but with no success]

Thanks for the support!

Cheers,


GG