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