question Re: Best way to migrate MS Access Databases to Hadoop in Archives of Support Questions (Read Only)

Best way to migrate MS Access Databases to Hadoop

nasghar — Wed, 24 Feb 2016 04:56:41 GMT

Team,

One of customer has thousands of MS Access DBs. What is best way to migrate them to Hive/Hadoop?

Any tools/experience in the space?

Thanks

Re: Best way to migrate MS Access Databases to Hadoop

SQLShaw — Wed, 24 Feb 2016 05:08:37 GMT

@nasghar though you can export MS Access to a csv and import that into Hive I would suggest instead importing the data into SQL Server and use Sqoop.

Re: Best way to migrate MS Access Databases to Hadoop

nsabharwal — Wed, 24 Feb 2016 07:59:00 GMT

@nasghar

See this thread https://community.hortonworks.com/questions/4249/can-microsofthortonworks-odbc-driver-provide-bi-di.html#comment-4259

Re: Best way to migrate MS Access Databases to Hadoop

bpreachuk — Wed, 24 Feb 2016 19:40:31 GMT

I agree with Scott. Bringing data from Hadoop into Access is no big deal. We had an Access database use ODBC to retrieve data from Hadoop with effective results (fast enough).

BUT if you try to send data from Access into Hadoop - painfully slow. Row by Row. Sending it off to Sql Server requires very little code in Access, and then you have a durable store of your data. Sending it from SQL Server to Hadoop is effective and fast.

Here's a simple sample of a Sqoop script to send data from SQL Server to Hadoop. It reads table Customer from SQL Server DB SQLTestDB and copies it into Hadoop database TestDB tablename Customer. It overwrites any existing data in the Hive table. It also uses 1 mapper.

sqoop import --connect "jdbc:sqlserver://<IP Address>:1433;database=SQLTestDB" \
--username root \
--password hadoop \
--table Customer \
--hive-import --hive-overwrite \
--hive-table TestDB.Customer \
-m 1

Re: Best way to migrate MS Access Databases to Hadoop

paul_boal — Thu, 25 Feb 2016 03:38:20 GMT

If you can work your way through SQL Server and sqoop, I agree that's probably the cleanest option. If you're looking for something that you can automate entirely on the cluster or don't have the luxury of pushing the data through SQL Server, then here's another option.

There's a very simple Open Source toolset called mdbtools that makes it really easy to extract metadata and data from MS Access databases. In a series of about 10 lines of a shell script, you can get a list of tables in the mdb, dump the data out to text files, import those to HDFS, and wrap a generic Hive schema around the files.

Since you're going through intermediate text files, you might not be able to support some character sets and could run into an issues or two with file formats that can be cleaned up with a secondary sed or perl script. If you don't want to go through SQL Server to get the data transferred over, though, this might be a good solution for you.