前提

安装ODBC相关软件包以及ImpalaODBC驱动

使用ODBC连接数据库，分为两个主要部分:

安装ODBC管理程序(例如unixODBC,iODBC等)，这里管理程序选择unixODBC
安装每个数据库对应的ODBC驱动程序，而Impala的ODBC驱动则参考这里 Cloudera-ODBC-Driver-for-Impala-Install-Guide

安装unixODBC

ClouderaImpalaODBC 到Cloudera官网下载


yum install -y unixODBC*

rpm -ivh ClouderaImpalaODBC-2.5.15.1015-1.el6.x86_64.rpm

重新编译PHP以支持ODBC


php_root=/usr/local/php/

mysql_root=/usr/local/mysql/

./configure --prefix=$php_root --with-config-file-path=/etc --with-mysql=$mysql_root --with-pdo-mysql=$mysql_root/bin/mysql_config --with-mysqli=$mysql_root/bin/mysql_config --with-iconv-dir=/usr/local --with-freetype-dir --with-jpeg-dir --with-png-dir --enable-gd-native-ttf --enable-zip --with-zlib --with-gd --disable-rpath --enable-bcmath --enable-shmop --enable-sysvsem --with-curl --with-curlwrappers --enable-mbstring --with-mcrypt --disable-ipv6 --enable-static --enable-maintainer-zts --enable-sockets --enable-soap --with-openssl --without-pdo-sqlite --enable-fpm --with-unixODBC=/usr/

配置ODBC

ODBC配置需要配置多个文件

编辑 /etc/odbc.ini，添加如下内容：


[ODBC Data Sources]

Cloudera Impala DSN = Cloudera Impala ODBC Driver

[Cloudera Impala DSN]

Driver = /opt/cloudera/impalaodbc/lib/64/libclouderaimpalaodbc64.so

HOST = 192.168.8.207

PORT = 21050

AuthMech = 0

PostgreSQL]

Description = ODBC for PostgreSQL

Driver = /usr/lib/psqlodbc.so

Setup = /usr/lib/libodbcpsqlS.so

Driver64 = /usr/lib64/psqlodbc.so

Setup64 = /usr/lib64/libodbcpsqlS.so

FileUsage = 1

[MySQL]

Description = ODBC for MySQL

Driver = /usr/lib/libmyodbc5.so

Setup = /usr/lib/libodbcmyS.so

Driver64 = /usr/lib64/libmyodbc5.so

Setup64 = /usr/lib64/libodbcmyS.so

FileUsage = 1

[Impala]

Description = ODBC for impala

Driver = /opt/cloudera/impalaodbc/lib/64/libclouderaimpalaodbc64.so

[ODBC Drivers]

Impala = Installed

[ODBC]

Trace = Yes

TracFile = /tmp/odbc_sql.log

ForceTrace = Yes

DEBUG = 1

编辑 /etc/odbcins.ini，添加如下内容：


[Impala]

Description = ODBC for impala

Driver = /opt/cloudera/impalaodbc/lib/64/libclouderaimpalaodbc64.so

[ODBC Drivers]

Impala = Installed

编辑 /etc/cloudera.impalaodbc.ini，添加如下内容


[Driver]

ODBCInstLib=libodbcinst.so

DriverManagerEncoding=UTF-16

LogLevel=4

LogPath=/tmp

ErrorMessagesPath=/opt/cloudera/impalaodbc/ErrorMessages/

修改环境变量


export ODBCINI=/etc/odbc.ini

export SIMBADN=/etc/cloudera.impalaodbc.ini

修改php-fpm配置

vi /usr/local/php/etc/php-fpm.conf


env[ODBCSYSINI] = /etc

env[ODBCINI] = /etc/odbc.ini

env[CLOUDERAIMPALAINI] = /etc/cloudera.impalaodbc.ini

运行测试

编译测试文件 testImpalaODBC.php 并运行


<?php

$conn = odbc_connect("Cloudera Impala DSN", "", "");

echo "开始时间:" . time() . "\r\n";

$sql = "select item_id,sum(amount) as amount, action as mtype, zero_dateline from tgzt_t_log_item where start_time between 1388505600 and 1393603200 group by item_id, action, zero_dateline";

echo "准备执行sql语句:$sql\r\n";

$rs = odbc_exec($conn, $sql);

echo "语句执行完成,准备开始获取结果集\r\n";

$result = [];

while ($row=odbc_fetch_array($rs)) {

$result[] = $row;

}

echo "结果获取完毕\r\n";

echo "总记录数量:".sizeof($result). "\r\n";

odbc_close($conn);

var_dump($conn);

echo "结束时间:" . time() . "\r\n";

测试结果

开始时间:1403179722

准备执行sql语句:select item_id,sum(amount) as amount, action as mtype, zero_dateline from tgzt_t_log_item where start_time between 1388505600 and 1393603200 group by item_id, action, zero_dateline

语句执行完成,准备开始获取结果集

结果获取完毕

总记录数量:53194

resource(4) of type (odbc link)

结束时间:1403179724

问题

检查配置文件


/etc/odbc.ini

/etc/odbcinst.ini

/etc/cloudera.impalaodbc.ini (终端能连web端不能检查)

检查环境变量

/etc/profile


export ODBCINI=/etc/odbc.ini

export SIMBADN=/etc/cloudera.impalaodbc.ini

export CLOUDERAIMPALAINI=/etc/cloudera.impalaodbc.ini

检查php-fpm配置

(终端能连web端不能检查)

/usr/local/php/etc/php-fpm.conf


env[ODBCSYSINI] = /etc

env[ODBCINI] = /etc/odbc.ini

env[CLOUDERAIMPALAINI] = /etc/cloudera.impalaodbc.ini

外网检查防火墙端口

isql连接测试


isql "Cloudera Impala DSN" -v

使用PHP连接Impala

前提