Can Microsoft/Hortonworks ODBC Driver provide bi-directional connectivity with Microsoft Access?

[#1]

Customer has an Access database application and they would like to (a) connect to Hive and extract data, and also (b) take resulting Access DB data and ODBC connect & load it into an existing Hive table from within Microsoft Access (not via Sqoop or via flat file). I am currently installing the driver and will soon test out option (a), but would like to know if option (b) is possible.

Thanks in advance!

[#2]

Yes, it can.

Source - http://hortonworks.com/wp-content/uploads/2015/10/Hortonworks-Hive-ODBC-Driver-User-Guide.pdf

Snippet - (Page 46)

The Hortonworks Hive ODBC Driver with SQL Connector supports translation for INSERT,
UPDATE, and DELETE syntax when connecting to a Hive Server 2 instance that is running
Hive 0.14 or later.

[#3]

Yes it can do Inserts, but it turns out that they're RBAR updates. We may try SIMBA drivers to see if they improve the performance (allow batching) but we are in the meantime moving ahead with Inserts via Access -> SQL Server -> Hive via Sqoop.

[#4]

@bpreachuk

I am not sure if there is direct path from Access to Hive. I have done Access to RDBMS then eventually Hive.

[#5]

@Neeraj Sabharwal Just wondering if you tried and found that the ODBC driver does not work with MsAccess? Usually ODBC driver that works for Excel works for Access.

[#6]

@bsaini It should work but migration from Access DB to Hive is in the question

[#7]

Whats the migration part? Hope I am not over simplifying but it appears to be a simple case of a) Select & b) Insert / update.

[#8]

@bsaini Link

DML is not the option

[#9]

You mean DDL? Yeah, agreed. But OP is asking "load it into an existing Hive table" - so just insert.

[#10]

There is a desire to do it from within Access so we will definitely work that angle first. From a business standpoint it is cleaner that way - the data is processed in Access and once processed it would indeed be a simple Insert into Hive. I'll let you guys know the end result. The Access developer (part time at this client) will be doing the work so it may be a while before it's all done (FYI).

[#11]

Thanks Birender and Neeraj. I was looking into the Access -> SQL Server -> Hive approach since we already have a SQL Server on the box - but if I can get it to work via MS Access directly I will do so. I'll update the ticket here with how it all turns out. I will write up what worked if I am able to easily get the Access -> Hive Insert connection working.

[#12]

Great, looking forward to hearing the results.

[#13 · Accepted solution]

@bpreachuk Using ODBC driver is the cleanest way to do it.

Access --> RDBMS --> Hive using Sqoop.

Looking forward to see the final update

Kudos to this guy Link

[#14]

Here is an update on this task:

ODBC read from Hive -> Access works fine (not a surprise).

ODBC from Access to Hive runs as RBAR - one row at a time. This is sub-optimal in the RDBMS world, but REALLY crummy in the Hadoop world. This means 1 Hive session per row updated. We were getting about 150 rows updated every 10 minutes.

When sending data form Access to Hive, we will be implementing using Access -> SQL Server -> Sqoop into Hive.

We may try out Simba drivers in the future - more for curiosity - to see if they perform better with updates - or have the ability to batch updates into Hive.

Thanks Neeraj and Birender!

[#15]

Thanks @bpreachuk for updating the thread!!!