ODBC TUTORIAL

Connecting to Hive from Lua using an ODBC driver

Updated: 31 Jul 2024

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

Run the below commands in your terminal to download Lua and extract the package

wget http://www.lua.org/ftp/lua-5.3.4.tar.gz

tar zxf lua-5.3.4.tar.gz

Build the code, by running the below commands.

cd lua-5.3.4

make linux test

Move lua executables to /usr/local/bin and headers to /usr/local/include/lua/5.3/ by running the following commands.

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

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

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

wget https://luarocks.org/releases/luarocks-2.4.3.tar.gz

tar zxpf luarocks-2.4.3.tar.gz

cd luarocks-2.4.3

./configure; sudo make bootstrap

sudo luarocks install luasocket

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

sudo luarocks install odbc

Download and Install Progress DataDirect Hive ODBC driver

Download DataDirect Hive ODBC driver from here.

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

tar -zxf PROGRESS_DATADIRECT_ODBC_HIVE_LINUX_64.tgz

./PROGRESS_DATADIRECT_ODBC_8.0_LINUX_64_INSTALL.bin

This will start the installer, go through the simple install instructions to install the driver successfully.
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.

sudo chmod +x odbc.sh

./odbc.sh

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

echo $ODBCINI

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

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

Refresh the terminal configuration by running the following command.

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

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.