Laravel 通过 ODBC 连接 Vertica

本文档基于 CentOS + Laravel 6.x 版本，其他环境未经过验证

一、通过 ODBC 连接 Vertica

**Vertica所给的数据库驱动有两种：JDBC 和 ODBC。JDBC 是 java语言使用，ODBC 可提供给 PHP 使用。本文档针对在Unix、Linux环境下，在php语言CI框架中使用ODBC连接Vertica的使用。

安装 unixODBC

UnixODBC 是一个连接数据库的组件，可以使在 Unix、Linux 系统下使用 ODBC

yum install unixODBC
yum install unixODBC-devel
查看版本和配置文件
odbcinst -j

结果如下：

unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

安装关于 Vertica ODBC 的驱动

Wget https://my.vertica.com/client_drivers/9.0.x/9.0.1-4/vertica-client-9.0.1-4.x86_64.rpm
rpm -ivh vertica-client-9.0.1-4.x86_64.rpm
ldd /opt/vertica/lib64/libverticaodbc.so

结果如下:

export PATH=$PATH:/opt/vertica/bin

配置 ODBC

vi /etc/odbc.ini

[ODBC Data Sources]
VerticaDSNunixodbc = vertica1

[VerticaDSNunixodbc]
Description = Vertica Database using ODBC Driver
Driver = /opt/vertica/lib64/libverticaodbc.so
Servername = vertica
Database = db1 
UserName = username #UID 
Password = password #PWD 
Port = 5433
ReadOnly = true
# ResultBufferSize =131072 (128KB)

验证是否安装配置成功

isql -v VerticaDSNunixodbc username password

结果如下：

配置高可用 vertica

vi /etc/odbc.ini

添加配置项：BackupServerNode
修改后的配置如下：
如果需要在本地进行测试，可修改/ets/hosts，使高可用域名暂时失效，测试不同node下的切换是否有效。
改后的hosts文件如下：
192.168.0.1 node1.vertica.api.local.datapaas.cn
192.168.0.2 node2.vertica.api.local.datapaas.cn
192.168.0.3 node3.vertica.api.local.datapaas.cn
192.168.0.4 node4.vertica.api.local.datapaas.cn
192.168.0.5 node5.vertica.api.local.datapaas.cn

比如测试node6是否可使用，将最后一行更改为高可用IP匹配node6域名即可
xx.xxx.xxx.xx node6.addnewer-vertica.api.local.datapaas.cn

安装 php 的 ODBC 模块

cd ext/pdo_odbc
./configure –with-php-config=/usr/local/php/bin/php-config –with-pdo-odbc=unixODBC,/usr
make 
make install

修改 php.ini 添加 extension=pdo_odbc.so

二、Laravel 配置

1. .env 文件配置

DB_VERTICA_DSN=VerticaDSNunixodbc
DB_VERTICA_HOST=datanode1
DB_VERTICA_PORT=5433
DB_VERTICA_DATABASE=db1
DB_VERTICA_USERNAME=username
DB_VERTICA_PASSWORD=password

2. config/database.php 增加配置

'vertica' => [
  'driver' => 'sqlsrv',
  'odbc' => true,
  'odbc_datasource_name' => env('DB_VERTICA_DSN'),
  'host' => env('DB_VERTICA_HOST', 'localhost'),
  'port' => env('DB_VERTICA_PORT', '5433'),
  'database' => env('DB_VERTICA_DATABASE', 'forge'),
  'username' => env('DB_VERTICA_USERNAME', 'forge'),
  'password' => env('DB_VERTICA_PASSWORD', ''),
  'charset' => 'utf8',
  'prefix' => '',
  'prefix_indexes' => true,
],

3. 新增 Vertica 类库

<?php

namespace App\Models\Common;

use Illuminate\Database\Eloquent\Model;
use Exception;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Log;

class VerticaBase extends Model
{
    protected $connection = 'vertica';

    protected $table;

    /**
     * 获取 table
     *
     * @param  string  $tableName
     * @return Builder
     */
    public function table($tableName = '')
    {
        return $this->getConnection()->table($tableName);
    }

    /**
     * 获取多条记录
     *
     * @param  string  $sql
     * @param  string  $comment
     * @return bool|Collection
     */
    public function fetchAll($sql = '', $comment = '')
    {
        if (!$sql) {
            return false;
        }
        if ($comment) {
            $sql = $this->getComment($sql, $comment) . $sql;
        }
        $data = $this->getConnection()->select($sql);
        if ($data) {
            $data = object2array($data);
        }
        return collect($data);
    }

    /**
     * 获取第一条记录
     *
     * @param  string  $sql
     * @param  string  $comment
     * @return bool|Collection
     */
    public function fetch($sql = '', $comment = '')
    {
        if (!$sql) {
            return false;
        }
        if ($comment) {
            $sql = $this->getComment($sql, $comment) . $sql;
        }
        $data = $this->getConnection()->selectOne($sql);
        if ($data) {
            $data = object2array($data);
        }
        return collect($data);
    }

    /**
     * 执行 sql
     *
     * @param  string  $sql
     * @return bool
     */
    public function execute($sql = '')
    {
        if (!$sql) {
            return false;
        }
        try {
            return $this->getConnection()->statement($sql);
        } catch (Exception $e) {
            Log::error('Vertica Execute Failed, SQL: ' . $sql . '. Exception Msg: ' . $e->getMessage());
            return false;
        }
    }

    /**
     * Drop 表
     *
     * @param  string  $tableName
     * @return bool
     */
    public function dropTable($tableName = '')
    {
        $drop_sql = "DROP TABLE IF EXISTS {$tableName};";
        return $this->execute($drop_sql);
    }

    /**
     * 通过 statement 取消 sql 执行
     *
     * @param $statement
     * @param  string  $user
     * @return bool
     */
    public function cancelQueryByStatement($statement, $user = 'rmos')
    {
        $queryInfo = $this->getSqlInfoWithStatement($statement, $user);
        if (!$queryInfo) {
            return false;
        }
        $sessionId   = Core::value($queryInfo, 'session_id');
        $statementId = Core::value($queryInfo, 'statement_id');
        return $this->interruptStatement($sessionId, $statementId);
    }

    /**
     * 根据 statement 获取当前再执行的 sql
     *
     * @param $statement
     * @param  string  $user
     * @return bool|Collection
     */
    public function getSqlInfoWithStatement($statement, $user = 'rmos')
    {
        $sql = "select session_id,statement_id, current_statement from sessions where user_name='{$user}' "
            . " and current_statement not like 'select session_id from sessions%' "
            . " and current_statement like '%{$statement}%' limit 1;";
        return $this->fetch($sql);
    }

    /**
     * 关闭会话
     *
     * @param $sessionId
     * @return bool
     */
    public function closeSession($sessionId)
    {
        if (!$sessionId) {
            return false;
        }
        $sql = "SELECT CLOSE_SESSION('{$sessionId}');";
        return $this->execute($sql);
    }

    /**
     * 取消查询
     *
     * @param $sessionId
     * @param $statementId
     * @return bool
     */
    public function interruptStatement($sessionId, $statementId)
    {
        if (!$sessionId) {
            return false;
        }
        if (!$statementId) {
            return $this->closeSession($sessionId);
        }
        $sql = "SELECT INTERRUPT_STATEMENT('{$sessionId}', '{$statementId}');";
        return $this->execute($sql);
    }
}

4. 使用方式

$vtc = new \App\Models\Vertica\Vtc();
$vtc->table('table_name')->count();
// 或者
$vtc->fetchAll('select * from table_name limit 10');

// 或者
$vtc->fetch('select * from table_name limit 1');

附录

1. PDO_ODBC varchar 超过 255 返回 null 的问题

问题描述

Reproduce code:
---------------
create table T (
    [A]          varchar(80) NOT NULL,
    [B]         tinyint NOT NULL,
    [C]           varchar(100) NOT NULL,
    [D]         smallint NOT NULL,
    [E]           varchar(1024) NOT NULL,
    [F]           varchar(255) NOT NULL,
    [G]            varchar(255) NOT NULL,
    [H]           varchar(1000) NOT NULL,
    [I]         varchar(100) NOT NULL,
    [J]            tinyint NOT NULL,
    [K]           varchar(255) NULL 
)

insert into T values ('A', '1', 'C', '2', 'E', 'F', 'G', 'H', 'I', '3', 'K')
//////////////////////////////////////////////////////////////
try {
    $db = new PDO('odbc:Driver={SQL Server};Server=HOST\INSTANCENAME;Database=XXXX;', "user", "pass");
} catch( PDOException $e ){
    die( $e->getMessage() );
}

foreach( $db->query("SELECT * FROM T ", PDO::FETCH_NUM)    as $row ) {
     echo "
<pre>"; print_r( $row );echo "</pre>";
}

Expected result:
----------------
[0] => A
[1] => 1
[2] => C
[3] => 2
[4] => E
[5] => F
[6] => G
[7] => H
[8] => I
[9] => 3
[10] => K

Actual result:
--------------
[0] => A
[1] => 1
[2] => C
[3] => 2
[4] => 
[5] => F
[6] => G
[7] => 
[8] => I
[9] => 3
[10] => K

解决思路

• 经排查此问题是由于 pdo_odbc 扩展限制导致的
• 临时解决思路为调整 pdo_odbc 扩展源码

解决步骤

1. 修改 ext/pdo_odbc/odbc_stmt.c

if (colsize < 256 && !S->going_long) {
    ...       
} else {
    ...
}
修改为
if (1) {
    ...       
} else {
    ...
}

2. 重新编译 pdo_odbc 扩展

$ /data/modules/php/bin/phpize
$ ./configure --with-php-config=/data/modules/php/bin/php-config --with-pdo-odbc=unixODBC,/usr
$ make && make install

3. 重载 php

/etc/inid.t/php-fpm restart

本作品采用《CC 协议》，转载必须注明作者和本文链接