Connecting MS Access to Cloudera Impala through ODBC connection

MS Access can be connected to Cloudera Impala through ODBC Connector. Our version of Impala is ver 1.3 and that of ODBC Connector is ver 2.5.5.1005 (64 bit). ODBC Connector (msi file) can be downloaded from Cloudera Impala site from here. Download and install ODBC Connector as usual on your Windows machine.

After installation, click Start->All Programs  to look for ‘Cloudera ODBC Driver for Impala‘ folder. Within it, click ‘64-bit ODBC Administrator‘ link to open ‘ODBC Data Source Administrator‘ window. Click on the tab ‘User DSN’. Next, click Add button to open ‘Create New Data Source’ window. Here, you should see among other ODBC connectors,  ‘Cloudera ODBC Driver for Impala‘. Select it and click Finish button.  A configuration window will open. Fill up some (any) name for the ‘Data Source Name‘ and write some Description. Against Host, write the address of your Cloudera server machine. Leave the default values in other text boxes. Click Test.. button to test the connection. Click OK to close the DSN setup.

Figure 1: Configuring User DSN in Impala ODBC Connector

Figure 1: Configuring User DSN in Impala ODBC Connector

Start MS Access. Create a new ‘Blank Database‘ as usual. MS Access opens with one default table Table 1. Click on ‘External Data–>ODBC Database‘.  In the Get External Data wizard, select Link to the data source by creating a linked table. As generally data hosted on hadoop is huge, import of source data into a new table in MS Access is not possible. Default size of a table in MS Access is just 2GB. Linking is the only option. Hopefully data extracted from queries will be of much less size and can be accommodated in an MS Access database. Click OK. In the Select Data Source dialog box, click the tab ‘Machine Data Source‘ and select the data source you just created above. Click OK.

Figure 2: Select the data source you just created above and click OK.

Figure 2: Select the data source you just created above and click OK.

You will be presented with all the tables from Impala. Select as many as you want. MS Access will show them as linked tables (see figure below).

Figure 3: Walmart transaction table from Impala.

Figure 3: Walmart transaction table from Impala. Click to enlarge image.

You can now query Impala from MS Access as usual. You should also be able to create a .Net program from MS Access as usual.

 

Advertisements

Tags: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: