Environment
CentOS 5.5
PHP 5.3.10
(This article also works for PHP 5.3.3 on CentOS 6).
Dependencies
UnixODBC
UnixODBC can be installed from yum repo
|
1 |
yum install unixODBC unixODBC-devel |
I built a unixODBC 2.3.2 from source, installed to /usr/local/unixODBC
ODBC Connectors
Cloudera offers ODBC libs for both Hive and Impala:
http://www.cloudera.com/content/support/en/downloads/connectors/impala/impala-odbc-v2-5-15.html
http://www.cloudera.com/content/support/en/downloads/connectors/hive/hive-odbc-v2-5-9.html
Follow the install guide on urls above, only wget and yum –nogpgcheck localinstall xxx.rpm required.
PHP Extensions
You can install PHP-ODBC or PHP-PDO-ODBC, either from source or yum repo.
If you meet some error building PHP-ODBC, this article might be useful: Build Old PHP-ODBC from Source
Configure Connectors
Connectors are installed under /opt/cloudera, both of them provide a folder named ‘Setup‘ with three files in it:
- cloudera.impalaodbc.ini
- odbc.ini
- odbcinst.ini
cloudera.impalaodbc.ini
I’m using the unixODBC built from source:
|
1 |
ODBCInstLib=/usr/local/unixODBC/lib/libodbcinst.so |
And comment all rest lines about ODBCInstLib.
odbc.ini
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
[ODBC Data Sources] MyImpala=Cloudera ODBC Driver for Impala 64-bit [MyImpala] Description=My Impala DSN Driver=/opt/cloudera/impalaodbc/lib/64/libclouderaimpalaodbc64.so # The DriverUnicodeEncoding setting is only used for SimbaDM # When set to 1, SimbaDM runs in UTF-16 mode. # When set to 2, SimbaDM runs in UTF-8 mode. DriverUnicodeEncoding=2 # Values for HOST, PORT, KrbFQDN, and KrbServiceName should be set here. # They can also be specified on the connection string. HOST=a1.hadoop.xxx.com PORT=21050 Database=xxx_db # The authentication mechanism. # 0 - no authentication. # 1 - Kerberos authentication # 2 - Username authentication. # 3 - Username/password authentication. # 4 - Username/password authentication with SSL. AuthMech=0 # Kerberos related settings. #KrbFQDN= #KrbRealm= #KrbServiceName= # Username/password authentication with SSL settings. #UID= #PWD= #CAIssuedCertNamesMismatch=1 #TrustedCerts=/opt/cloudera/impalaodbc/lib/64/cacerts.pem # Specify the proxy user ID to use. #DelegationUID= # General settings TSaslTransportBufSize=1000 RowsFetchedPerBlock=1000 SocketTimeout=0 StringColumnLength=32767 UseNativeQuery=0 |
odbcinst.ini
nothing changed.
Test
LD_LIBRARY_PATH
If you have your unixODBC installed from yum:
|
1 |
export LD_LIBRARY_PATH=/opt/cloudera/impalaodbc/lib/64:$LD_LIBRARY_PATH |
If built from source:
|
1 |
export LD_LIBRARY_PATH=/opt/cloudera/impalaodbc/lib/64:/usr/local/unixODBC/lib:$LD_LIBRARY_PATH |
ENV
Guide says ini-s can be copied to ~ and prepend a ‘.‘ to make it ‘invisible‘.
But I prefer to set up ENV:
|
1 2 3 |
export ODBCSYSINI=/opt/cloudera/impalaodbc/etc export ODBCINI=/opt/cloudera/impalaodbc/etc/odbc.ini export SIMBAINI=/opt/cloudera/impalaodbc/etc/cloudera.impalaodbc.ini |
These can be set inside PHP script with putenv(),
I’ll paste test script later.
Script
Test script using PHP-PDO-ODBC and PHP-ODBC are pasted below.
Remember to use DSN=MyImpala instead of shared object file path(from install guide), Driver=(from php doc), …
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
<?php putenv('ODBCSYSINI=/opt/cloudera/odbc/etc'); putenv('ODBCINI=/opt/cloudera/odbc/etc/odbc.ini'); putenv('SIMBAINI=/opt/cloudera/odbc/etc/cloudera.odbc.ini'); $sql = 'SELECT * FROM wallets limit 10'; $username = ""; $password = ""; try { $dbh = new PDO("odbc:DSN=MyImpala;", $username, $password ); echo var_dump($dbh); # unset($dbh); $t0 = microtime(1); $result = $dbh->query($sql); var_dump(microtime(1) - $t0); var_dump($dbh->errorInfo()); $all = $result->fetchAll(PDO::FETCH_ASSOC); if ($all) { $keys = array_keys($all[0]); foreach ($keys as $col) { echo $col, "\t"; } echo "\n"; foreach ($all as $row) { foreach ($row as $k=>$v) { echo $v, "\t"; } echo "\n"; } } } catch (PDOException $exception) { echo $exception->getMessage(), "\n\n"; # exit; } exit; echo "Show tables from MyImpala\n"; $dsn = "DSN=MyImpala;"; $conn = odbc_connect($dsn, $username, $password); $result = odbc_exec($conn, "SHOW TABLES"); while(odbc_fetch_row($result)){ for($i=1;$i<=odbc_num_fields($result);$i++){ echo "\t".odbc_result($result,$i) . "\n"; } } exit; |
Trouble Shooting
Can’t open cursor lib
Error message may like
|
1 |
SQLSTATE[01000] SQLDriverConnect: 0 [unixODBC][Driver Manager]Can't open cursor lib 'libodbccr' : file not found |
or
|
1 |
SQLSTATE[01000] SQLDriverConnect: 0 [unixODBC][Driver Manager]Can't open cursor lib '/opt/cloudera/odbc/libodbccr.so' : file not found |
Solution is create a symbolic link:
|
1 2 3 4 5 6 |
# ls /usr/lib64/libodbccr.so* -lh lrwxrwxrwx 1 root root 18 Jul 15 15:10 /usr/lib64/libodbccr.so -> libodbccr.so.2.0.0 lrwxrwxrwx 1 root root 18 Jul 15 15:10 /usr/lib64/libodbccr.so.2 -> libodbccr.so.2.0.0 -rwxr-xr-x 1 root root 40K Nov 29 2012 /usr/lib64/libodbccr.so.2.0.0 # ln -s /usr/lib64/libodbccr.so.2.0.0 /usr/lib64/libodbccr.so.1 |
Segmentation fault
If you meet these, go back and check your configurations and DSN syntax.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Program received signal SIGSEGV, Segmentation fault. 0x00002aaaaf80b101 in extract_sql_error_rec (head=0x12cf938, sqlstate=0x7fffffffb7a0 "00000", rec_number=1, native_error=0x7fffffffb7a8, message_text=0x7fffffffb3a0 "", buffer_length=1023, text_length=0x7fffffffb7ae) at SQLGetDiagRec.c:427 427 else if ( !__get_connection( head ) -> unicode_driver && (gdb) where #0 0x00002aaaaf80b101 in extract_sql_error_rec (head=0x12cf938, sqlstate=0x7fffffffb7a0 "00000", rec_number=1, native_error=0x7fffffffb7a8, message_text=0x7fffffffb3a0 "", buffer_length=1023, text_length=0x7fffffffb7ae) at SQLGetDiagRec.c:427 #1 0x00002aaaaf80b4ae in SQLGetDiagRec (handle_type=<value optimized out>, handle=0x12cf360, rec_number=2, sqlstate=0x7fffffffb7a0 "00000", native=0x7fffffffb7a8, message_text=0x7fffffffb3a0 "", buffer_length=1023, text_length_ptr=0x7fffffffb7ae) at SQLGetDiagRec.c:686 #2 0x00002aaaaf5eb6ab in pdo_odbc_error (dbh=<value optimized out>, stmt=<value optimized out>, statement=<value optimized out>, what=0x2aaaaf5ed9e8 "SQLConnect", file=0x2aaaaf5ed9f8 "/opt/download/php-5.3.10/ext/pdo_odbc/odbc_driver.c", line=464) at /opt/download/php-5.3.10/ext/pdo_odbc/odbc_driver.c:120 #3 0x00002aaaaf5ec144 in pdo_odbc_handle_factory (dbh=0x11d2088, driver_options=0x0) at /opt/download/php-5.3.10/ext/pdo_odbc/odbc_driver.c:464 #4 0x00000000005aa771 in zim_PDO_dbh_constructor (ht=<value optimized out>, return_value=<value optimized out>, return_value_ptr=<value optimized out>, this_ptr=0x11d0830, return_value_used=<value optimized out>) at /opt/download/php-5.3.10/ext/pdo/pdo_dbh.c:378 #5 0x0000000000789c19 in zend_do_fcall_common_helper_SPEC (execute_data=0x2aaaafc63050) at /opt/download/php-5.3.10/Zend/zend_vm_execute.h:320 #6 0x000000000078921c in execute (op_array=0x11d1008) at /opt/download/php-5.3.10/Zend/zend_vm_execute.h:107 #7 0x000000000076399d in zend_execute_scripts (type=8, retval=0x0, file_count=3) at /opt/download/php-5.3.10/Zend/zend.c:1236 #8 0x0000000000712a8d in php_execute_script (primary_file=0x7fffffffe620) at /opt/download/php-5.3.10/main/main.c:2308 #9 0x00000000007e8d1c in main (argc=4, argv=0x7fffffffe898) at /opt/download/php-5.3.10/sapi/cli/php_cli.c:1184 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Program received signal SIGSEGV, Segmentation fault. 0x00007ffff0e1425e in ?? () from /usr/lib64/libodbc.so.2 (gdb) where #0 0x00007ffff0e1425e in ?? () from /usr/lib64/libodbc.so.2 #1 0x00007ffff0e14660 in SQLGetDiagRec () from /usr/lib64/libodbc.so.2 #2 0x00007ffff07d2528 in ?? () from /usr/lib64/php/modules/pdo_odbc.so #3 0x00007ffff07d2dfd in ?? () from /usr/lib64/php/modules/pdo_odbc.so #4 0x00007ffff09df15c in ?? () from /usr/lib64/php/modules/pdo.so #5 0x00000000005f67e8 in ?? () #6 0x00000000005cd930 in execute () #7 0x00000000005a7b3d in zend_execute_scripts () #8 0x0000000000555bc8 in php_execute_script () #9 0x0000000000631f95 in ?? () #10 0x00000032b381ed1d in __libc_start_main () from /lib64/libc.so.6 #11 0x00000000004220c9 in _start () |
zend_mm_heap corrupted
This occurs when using php-odbc based on unixODBC 2.2.11 from yum on centos 5.5.
Build the latest unixODBC from www.unixodbc.org.
Useful links
http://d.hatena.ne.jp/shimooka/20140422/1398159246
PHP ODBC Connect Cloudera Impala and Hive by @sskaje: https://sskaje.me/2014/07/php-odbc-connect-cloudera-impala-hive/
Incoming search terms:
Link to this post!