Connecting to Hive from Lua using an ODBC driver

Introduction

Let’s talk about connecting to your database in Lua. For those who aren’t aware, Lua is a lightweight, powerful and efficient scripting language designed primarily for embedded systems and clients, used in many industrial applications, games (like World of Warcraft, Angry Birds) etc.,

In Lua, to be able to connect to your database, you can use an ODBC driver, but you need to install an ODBC Lua module to interact with ODBC drivers. This tutorial will walk you through how you can connect to your Hive instance using Progress DataDirect Hive ODBC driver.

Setting up the environment

Before you start, this tutorial assumes that you are using an Ubuntu, have a Hive instance to connect to and basic programming knowledge.

Open your Terminal and run the below commands to install required tools for building the code and using ODBC drivers.

sudo apt-get update
sudo apt-get install make
sudo apt-get install gcc g++
sudo apt-get install libreadline-dev
sudo apt-get install unixodbc unixodbc-dev

 

Install Lua

  1. Run the below commands in your terminal to download Lua and extract the package
  2. tar zxf lua-5.3.4.tar.gz

  3. Build the code, by running the below commands.
  4. cd lua-5.3.4
    make linux test

  5. Move lua executables to /usr/local/bin and headers to /usr/local/include/lua/5.3/ by running the following commands.
  6. cd src
    sudo mv lua luac /usr/local/bin/
    sudo mkdir -p /usr/local/include/lua/5.3
    sudo mv * /usr/local/include/lua/5.3/




Install Lua ODBC Module

  1. To install Lua ODBC module, we need to install LuaRocks, which is a package manager for Lua modules.

  2. Open your terminal and run the following commands to download and install LuaRocks.

  3. tar zxpf luarocks-2.4.3.tar.gz
    cd luarocks-2.4.3
    ./configure; sudo make bootstrap
    sudo luarocks install luasocket

  4. Install Lua ODBC module by running the below command in your terminal

  5. sudo luarocks install odbc


Download and Install Progress DataDirect Hive ODBC driver

  1. Download DataDirect Hive ODBC driver from here.

  2. Run the following commands in the terminal to extract the package and run the installer

  3. tar -zxf PROGRESS_DATADIRECT_ODBC_HIVE_LINUX_64.tgz
    ./PROGRESS_DATADIRECT_ODBC_8.0_LINUX_64_INSTALL.bin

  4. This will start the installer, go through the simple install instructions to install the driver successfully.
  5. Go to /installpath/Progress/ DataDirect/ODBC_80_64bit and run the following commands in your terminal to add environment variables ODBCINI, ODBCINST and LD_LIBRARY_PATH.

  6. sudo chmod +x odbc.sh
    ./odbc.sh

  7. Verify if the variables ODBCINI, ODBCINST and LD_LIBRARY_PATH are installed successfully by running the following command. You should see the path for the file odbc.ini

  8. echo $ODBCINI

  9. If the environment variables aren’t added properly then add these values to ~/.bashrc

  10. export LD_LIBRARY_PATH=/install_path/Progress/ DataDirect/ODBC_80_64bit/lib
     
    export ODBCINI=/install_path/Progress/ DataDirect/ODBC_80_64bit/odbc.ini
     
    export ODBCINST=/ install_path/Progress/ DataDirect/ODBC_80_64bit/odbcinst.ini

  11. Refresh the terminal configuration by running the following command.
  12. source ~/.bashrc


Configure ODBC Data Source

Open odbc.ini file in the path  /installpath/Progress/ DataDirect/ODBC_80_64bit and configure your Hive connection details as shown in the below sample.

[ODBC]
IANAAppCodePage=4
InstallDir=/home/progress/Progress/DataDirect/ODBC_80_64bit
Trace=0
TraceFile=odbctrace.out
TraceDll=/home/progress/Progress/DataDirect/ODBC_80_64bit/lib/ddtrc28.so

[Hive1]
ApplicationUsingThreads=1
Description=DataDirect 8.0 Apache Hive Wire Protocol
Driver=/home/progress/Progress/DataDirect/ODBC_80_64bit/lib/ddhive28.so
ArraySize=150000
AuthenticationMethod=0
BatchMechanism=2
CatalogMode=0
Database=<Database Name>
DefaultLongDataBuffLen=1024
EnableDescribeParam=0
HostName=<hostname>
KeepAlive=0
LoginTimeout=15
LogonID=
MaxStringSize=2147483647
PortNumber=10000
RemoveColumnQualifiers=0
StringDescribeType=-9
TransactionMode=0
UseCurrentSchema=0
ValidateServerCertificate=1


Save ODBC.ini file and exit.

Connect to Hive

Open a text editor and paste the following code:

odbc = require "odbc"
dbassert = odbc.assert
 
cnn = odbc.connect('Hive1', '<User>', '<Password>')
 
stmt = cnn:execute('SELECT activityid, activitydate, url FROM pmkdatalake.activity_pageview limit 10')
stmt:foreach(function(f1, f2, f3)
  print(string.format("activityid: %d, url: %s, activitydate: %s", f1, f3, f2));
 
end)
assert(stmt:closed()) -- foreach close cursor
assert(not stmt:destroyed()) -- statement valid


The above code connects to Hive using Progress DataDirect Hive ODBC driver, executes a query and prints the data. Make necessary changes to the above code that suits your need.

Open your terminal and run the below command to execute the program and you should the results printed on your terminal.

lua <filename>.lua

luaodbc


We hope this tutorial helped you to connect to Hive using Progress DataDirect ODBC driver from Lua. You can use similar steps to connect to Salesforce, SQL Server, Oracle, DB2, etc.

Note that we recommend and require use of the Progress ODBC driver manager, which is shipped with the Progress ODBC drivers. Progress DataDirect ODBC drivers are not certified with the unixODBC Driver Manager and reported issues must be reproducible with a supported version of the DataDirect ODBC Driver Manager for Unix/Linux.

Feel free to try any of the free evaluation Progress DataDirect drivers with Lua.

ODBC TUTORIAL

Connecting to Hive from Lua using an ODBC driver

DataDirect
connectors

Connect any data source to any application

DataDirect
Tutorials

Discussions, tips and tricks for
DataDirect Connect drivers