ODBC sample for Cloudera Impala

odbc_impala.sh

# This is tested on CDH 5.4/CentOS6.4
# C part is based on the samples from http://www.easysoft.com/developer/languages/c/odbc_tutorial.html
 
# Create target Table via Impala
impala-shell <<EOF
create table test (c1 int);
insert into test values (1);
EOF
 
# Package Installation
yum -y install git gcc gcc-c++ unixODBC-devel
wget https://downloads.cloudera.com/connectors/impala_odbc_2.5.26.1027/Linux/EL6/ClouderaImpalaODBC-2.5.26.1027-1.el6.x86_64.rpm
yum -y install ClouderaImpalaODBC-2.5.26.1027-1.el6.x86_64.rpm
 
echo "export LD_LIBRARY_PATH=\$LD_LIBRARY_PATH:/usr/local/lib:/opt/cloudera/impalaodbc/lib/64:/usr/local/lib" >> ~/.bashrc
export LD_LIBRARY_PATH=\$LD_LIBRARY_PATH:/usr/local/lib:/opt/cloudera/impalaodbc/lib/64:/usr/local/lib
 
echo "export CLOUDERAIMPALAINI=~/.cloudera.impalaodbc.ini" >> ~/.bashrc
export CLOUDERAIMPALAINI=~/.cloudera.impalaodbc.ini
 
 
# Create setting Files
## cloudera.impalaodbc.ini
cat <<EOF >~/.cloudera.impalaodbc.ini
[Driver]
 
## - Note that this default DriverManagerEncoding of UTF-32 is for iODBC.
## - unixODBC uses UTF-16 by default.
## - If unixODBC was compiled with -DSQL_WCHART_CONVERT, then UTF-32 is the correct value.
##   Execute 'odbc_config --cflags' to determine if you need UTF-32 or UTF-16 on unixODBC
## - SimbaDM can be used with UTF-8 or UTF-16.
##   The DriverUnicodeEncoding setting will cause SimbaDM to run in UTF-8 when set to 2 or UTF-16 when set to 1.
 
DriverManagerEncoding=UTF-32
ErrorMessagesPath=/opt/cloudera/impalaodbc/ErrorMessages/
LogLevel=0
LogPath=
 
 
## - Uncomment the ODBCInstLib corresponding to the Driver Manager being used.
## - Note that the path to your ODBC Driver Manager must be specified in LD_LIBRARY_PATH (LIBPATH for AIX).
 
#   SimbaDM / unixODBC
ODBCInstLib=libodbcinst.so
EOF
 
## odbcinst.ini
cat <<EOF >~/.odbcinst.ini
[ODBC Drivers]
Cloudera ODBC Driver for Impala 64-bit=Installed
 
[Cloudera ODBC Driver for Impala 64-bit]
Description=Cloudera ODBC Driver for Impala (64-bit)
Driver=/opt/cloudera/impalaodbc/lib/64/libclouderaimpalaodbc64.so
EOF
 
## odbc.ini
cat <<EOF >~/.odbc.ini
[Impala]
 
# Description: DSN Description.
# This key is not necessary and is only to give a description of the data source.
Description=Cloudera ODBC Driver for Apache Impala (64-bit) DSN
 
# Driver: The location where the ODBC driver is installed to.
Driver=/opt/cloudera/impalaodbc/lib/64/libclouderaimpalaodbc64.so
 
# Values for HOST, PORT, HS2HostFQDN, and HS2KrbServiceName should be set here.
# They can also be specified on the connection string.
HOST=`hostname -A`
PORT=21050
EOF
 
 
# Connection test
cat <<EOF > conn.c
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>

/*
 * see Retrieving ODBC Diagnostics
 * for a definition of extract_error().
 */
static void extract_error(
    char *fn,
    SQLHANDLE handle,
    SQLSMALLINT type);

void extract_error(
    char *fn,
    SQLHANDLE handle,
    SQLSMALLINT type)
{
    SQLINTEGER	 i = 0;
    SQLINTEGER	 native;
    SQLCHAR	 state[ 7 ];
    SQLCHAR	 text[256];
    SQLSMALLINT	 len;
    SQLRETURN	 ret;

    fprintf(stderr,
            "\n"
            "The driver reported the following diagnostics whilst running "
            "%s\n\n",
            fn);

    do
    {
        ret = SQLGetDiagRec(type, handle, ++i, state, &native, text,
                            sizeof(text), &len );
        if (SQL_SUCCEEDED(ret))
            printf("%s:%ld:%ld:%s\n", state, i, native, text);
    }
    while( ret == SQL_SUCCESS );
}

main() {
  SQLHENV env;
  SQLHDBC dbc;
  SQLHSTMT stmt;
  SQLRETURN ret; /* ODBC API return status */
  SQLCHAR outstr[1024];
  SQLSMALLINT outstrlen;

  /* Allocate an environment handle */
  SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
  /* We want ODBC 3 support */
  SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
  /* Allocate a connection handle */
  SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
  /* Connect to the DSN mydsn */
  ret = SQLDriverConnect(dbc, NULL, "DSN=Impala;", SQL_NTS,
			 outstr, sizeof(outstr), &outstrlen,
			 SQL_DRIVER_COMPLETE);
  if (SQL_SUCCEEDED(ret)) {
    printf("Connected\n");
    printf("Returned connection string was:\n\t%s\n", outstr);
    if (ret == SQL_SUCCESS_WITH_INFO) {
      printf("Driver reported the following diagnostics\n");
      extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
    }
    SQLDisconnect(dbc);
  } else {
    fprintf(stderr, "Failed to connect\n");
    extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
  }
  /* free up allocated handles */
  SQLFreeHandle(SQL_HANDLE_DBC, dbc);
  SQLFreeHandle(SQL_HANDLE_ENV, env);
}
EOF
 
## Query test
cat <<EOF > query.c
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>

main() {
SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;
SQLRETURN ret; /* ODBC API return status */
SQLSMALLINT columns; /* number of columns in result-set */
int row = 0;

/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
/* We want ODBC 3 support */
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
/* Connect to the DSN mydsn */
/* You will need to change mydsn to one you have created and tested */
SQLDriverConnect(dbc, NULL, "DSN=Impala;", SQL_NTS,
                 NULL, 0, NULL, SQL_DRIVER_COMPLETE);
/* Allocate a statement handle */
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
/* Retrieve a list of tables */
//SQLTables(stmt, NULL, 0, NULL, 0, NULL, 0, "TABLE", SQL_NTS);
SQLExecDirect(stmt, "select * from test", SQL_NTS);
/* How many columns are there */
SQLNumResultCols(stmt, &columns);
/* Loop through the rows in the result-set */
while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) {
    SQLUSMALLINT i;
    printf("Row %d\n", row++);
    /* Loop through the columns */
    for (i = 1; i <= columns; i++) {
        SQLINTEGER indicator;
        char buf[512];
        /* retrieve column data as a string */
	ret = SQLGetData(stmt, i, SQL_C_CHAR,
                         buf, sizeof(buf), &indicator);
        if (SQL_SUCCEEDED(ret)) {
            /* Handle null columns */
            if (indicator == SQL_NULL_DATA) strcpy(buf, "NULL");
	    printf("  Column %u : %s\n", i, buf);
        }
    }
  }
}
EOF
 
 
#connection test
gcc conn.c -o conn -lodbc
chmod 755 ./conn
./conn
 
#query test
gcc query.c -o query -lodbc
chmod 755 ./query
./query