[Sqoop-User] 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

Reply To : Sqoop Oracle And MS Access

asked Nov 6 2015 at 00:32

Giovanni M.L.G.

2 Replies for : Sqoop Oracle And MS Access

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:[email protected]]
Sent: Friday, 6 November 2015 7:33 PM
To: [email protected]
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

Reply To : Sqoop Oracle And MS Access

answered Nov 6 2015 at 15:32

David Robson

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
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

Reply To : Sqoop Oracle And MS Access

answered Nov 6 2015 at 04:59

Sumit Sarkar

Related discussions

Mysql And MS Access

I am currently shipping a MacroMedia Authorware application with a MS Access 97 dbase. I rely upon having MS Data Access Components on the end users machine, and use the MS ODBC/SQL functionality. Can I replace the reliance using MySQL and MyODBC? If so, I am interested... could you tell me what type of a license I would need and what it would cost me? I am shipping off the shelf software.

MS Access And MySql

Greeting to the list. I am running MS Access as a front end to data stored on MySql in a Redhat 7.3 box. I have about 95% functionality so far! (The Database is of a Youth Soccer league). I am having one problem though with one of my tables. The table seems to double itself when I transfer/convert/export into MySql. The table holds 345 records and when I view it in MySql there are 690

MS Access And MySQL

Hi. I have a situation where a church I am doing some volunteer work for has a MS Access (office 97) database running on NT. They are (woohoo) upgrading to a QUBE (linux box) for a server which has mySQL installed (and I can upgrade it etc..) My job therefore is to a) port MS Access data and queries to mySQL database b) use the MS Access forms to access the data and queries through

MyODBC And MS Access:

Hi all Sometimes, I would like to ORDER BY or GROUP BY an agregate function, like COUNT(), In those cases MS access creates a "ORDER BY Count(*)" clause that MySQL doesn't like at all Example: this works in MySQL: select item.catid, cat.path, count(*) as n from item,cat where item.catid�t.id group by item.catid order by n desc; By MS Access transforms that into: SELECT

Progress And MS Access

[email protected] Hello mysql'rs: First time on this list to find some information about mysql: Our company has a Progress DB and an Access DB. We want to set up a web server running apache on unix, so our clients (customers and or company users) access the Progress DB through the web We thought about using Openlink ODBC drivers to link Progress DB and Access DB. Now

Sqoop Oracle And MS Access

Related discussions

Sqoop-commits

Sqoop-dev

Sqoop-user