PHP and Vertica (on linux)
Moderator: NorbertKrupa
PHP and Vertica (on linux)
I plan on doing some integration with Vertica in the near future. There currently isn't any built in support from PHP but you should be able to use isql from unixodbc. I plan on using this thread to share my findings.
Last edited by Tom on Mon Mar 25, 2013 2:46 pm, edited 1 time in total.
-
- Newbie
- Posts: 16
- Joined: Fri May 11, 2012 12:25 pm
Re: PHP and Vertica (on linux)
Cool. Good luck.
Your bottleneck most likely won't be the Vertica cluster, but your PHP/Apache setup as PHP doesn't support connection pooling. Just something to keep in mind.
You could use DNS round robin on the clusters so when you connect, you'll be randomly distributed to one of the Vertica nodes so you don't overwhelm a single node with too many connections.
Your bottleneck most likely won't be the Vertica cluster, but your PHP/Apache setup as PHP doesn't support connection pooling. Just something to keep in mind.
You could use DNS round robin on the clusters so when you connect, you'll be randomly distributed to one of the Vertica nodes so you don't overwhelm a single node with too many connections.
-
- Newbie
- Posts: 1
- Joined: Thu Nov 08, 2012 2:29 am
Re: PHP and Vertica (on linux)
HI DataWhisperer,
Considering I have the setup you described, what do I need to access Vertica using PHP? Do I need to have the odbc.ini and vertica.ini described in the Python section? Could I just use the regular PHP ODBC http://php.net/manual/en/function.odbc-execute.php?
Thanks
Marcello
Considering I have the setup you described, what do I need to access Vertica using PHP? Do I need to have the odbc.ini and vertica.ini described in the Python section? Could I just use the regular PHP ODBC http://php.net/manual/en/function.odbc-execute.php?
Thanks
Marcello
-
- Newbie
- Posts: 1
- Joined: Thu Jan 10, 2013 11:01 am
- Contact:
Re: PHP and Vertica (on linux)
Tom, could you update a bit about your status in this topic? i will follow you for a bit i'm interested
Re: PHP and Vertica (on linux)
Here's how you can connect to Vertica with PHP:
Install PHP, as root:
Create an odbc.ini configuration file, for instance: /home/dbadmin/odbc.ini
Modify the path to libverticaodbc.so based on your installed version.
You may want to tweak the threading in /etc/odbcinst.ini
Then, you need to make sure your environment knows where to find the files. Add the following to your .bashrc
You can then log out and then back in or simply run this:
And finally, here's a small program to test that everything works:
Patrice
Install PHP, as root:
Code: Select all
yum install php
yum install php-odbc
Modify the path to libverticaodbc.so based on your installed version.
Code: Select all
[VerticaDSN]
Description = VerticaDSN ODBC driver
Driver = /opt/vertica/lib64/libverticaodbc.so.6.1.1
Database = vertica
Servername = localhost
UserName = dbadmin
Password =
Port = 5433
ConnSettings =
SSLKeyFile = /home/dbadmin/client.key
SSLCertFile = /home/dbadmin/client.crt
Locale = en_US@collation=binary
[Driver]
ODBCInstLib = /usr/lib64/libodbcinst.so
ErrorMessagesPath = /opt/vertica/lib64/
DriverManagerEncoding = UTF-16
LogPath = /tmp
LogNameSpace =
LogLevel = 0
Code: Select all
# Example driver definitions
# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[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
# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[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
[ODBC]
Threading = 1
Code: Select all
export LD_LIBRARY_PATH=/opt/vertica/lib64:/usr/lib64:/usr/local/lib64
export VERTICAINI=/home/dbadmin/odbc.ini
export ODBCINI=/home/dbadmin/odbc.ini
Code: Select all
source /home/dbadmin/.bashrc
Code: Select all
#!/usr/bin/php -q
<?php
# Turn on error reporting
error_reporting(E_ERROR | E_WARNING | E_PARSE | E_NOTICE);
# A simple function to trap errors from queries
function errortrap_odbc($conn, $sql) {
if(!$rs = odbc_exec($conn,$sql)) {
echo "Failed to execute SQL: $sql" . odbc_errormsg($conn) . "\n";
} else {
echo "Success: " . $sql . "\n";
}
return $rs;
}
# Connect to the Database
$dsn = "VerticaDSN";
$conn = odbc_connect($dsn,'','') or die ("CONNECTION ERROR");
echo "Connected with DSN: $dsn" . "\n";
# Create a table
$sql = "CREATE TABLE TEST(
C_ID INT,
C_FP FLOAT,
C_VARCHAR VARCHAR(100),
C_DATE DATE,
C_TIME TIME,
C_TS TIMESTAMP,
C_BOOL BOOL)";
$result = errortrap_odbc($conn, $sql);
# Insert data into the table with a standard SQL statement
$sql = "INSERT into test values(1,1.1,'abcdefg1234567890','1901-01-01','23:12:34 ','1901-01-01 09:00:09','t')";
$result = errortrap_odbc($conn, $sql);
# Insert data into the table with odbc_prepare and odbc_execute
$values = array(2,2.28,'abcdefg1234567890','1901-01-01','23:12:34','1901-01-01 09:00:09','t');
$statement = odbc_prepare($conn,"INSERT into test values(?,?,?,?,?,?,?)");
if(!$result = odbc_execute($statement, $values)) {
echo "odbc_execute Failed!" . "\n";
} else {
echo "Success: odbc_execute." . "\n";
}
# Get the data from the table and display it
$sql = "SELECT * FROM TEST";
if($result = errortrap_odbc($conn, $sql)) {
while($row = odbc_fetch_array($result) ) {
print_r($row);
}
}
# Drop the table and projection
$sql = "DROP TABLE TEST CASCADE";
$result = errortrap_odbc($conn, $sql);
# Close the ODBC connection
odbc_close($conn);
?>