Laravel 通過 ODBC 連線 Vertica

Jliu發表於2020-02-28

本文件基於 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 擴充套件原始碼

解決步驟

修改 ext/pdo_odbc/odbc_stmt.c

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

重新編譯 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

過載 php

/etc/inid.t/php-fpm restart

本作品採用《CC 協議》，轉載必須註明作者和本文連結

oracle連線SQLserver--通過ODBC
2011-12-06
OracleSQLServer
Qt中通過ODBC連線MSSQL資料庫
2010-04-07
QTSQL資料庫
通過ODBC連線windows下SQLServer到UNIX oracle
2012-02-07
WindowsSQLServerOracle
資料庫連線＝＝odbc
2007-07-19
資料庫
mysql 通過IP連線
2015-04-17
MySql
建立 ODBC DSN ASP 連線DSN
2010-08-26
linux下配置ODBC連線MySql
2016-01-11
LinuxMySql
java 連線oracle （jdbc-odbc橋）
2014-06-20
JavaOracleJDBC
通過跳板機連線MySQL
2020-09-24
MySql
Oracle使用DG4ODBC連線MySQL
2015-04-13
OracleMySql
jdbc-odbc連線資料庫 (轉)
2007-12-14
JDBC資料庫
ODBC客戶端連線ORACLE透過PowerDesigner匯出表ER圖
2015-03-04
客戶端Oracle
藉助Windows自帶的ODBC工具驗證資料庫連線是否通暢
2019-04-01
Windows資料庫
通過cman連線報ora-12564拒絕連線
2008-08-19
用Jdbc-odbc直接連線access檔案 (轉)
2007-11-28
JDBC
em可以用了，orcl可以連線到odbc了
2007-09-17
Linux下如何通過命令連線wifi
2020-01-08
LinuxWiFi
通過外來鍵連線多個表
2020-05-04
通過驅動建立與MySQL的連線
2018-05-07
MySql
通過因特網連線Beaglebone Black
2016-01-13
通過單例模式模擬RAC連線
2015-03-16
單例模式
通過vnc連線linux伺服器
2012-04-12
VNCLinux伺服器
Laravel redis 連不同的連線
2021-03-09
LaravelRedis
Oracle透過ODBC連線SQL Server資料庫後ORA-12514
2022-11-17
OracleSQLServer資料庫
laravel建立軟連線
2018-09-21
Laravel
免安裝Oracle連線資料庫（odbc驅動）
2015-08-14
Oracle資料庫
1.3.3. 通過SQL*Plus 連線資料庫
2020-02-20
SQL資料庫
【LISTENER】Oracle通過監聽連線緩慢分析
2021-12-28
Oracle
怎樣通過驅動程式連線到access
2018-04-09
通過cmd命令列連線mysql資料庫
2017-09-11
命令列MySql資料庫
java通過jdbc連線oracle報錯No suitable driver
2010-06-13
JavaJDBCOracleUI
CentOS下配置ODBC連線MySQL資料庫 V2.0
2016-07-26
CentOSMySql資料庫
ODBC資料庫連線------java學習筆記之3
2007-05-12
資料庫Java筆記
C#通過ODBC查詢HANA資料庫資料
2014-06-06
C#資料庫
windows 通過cmd命令（netsh wlan命令）連線wifi
2020-03-19
WindowsWiFi
java通過kerberos認證連線hdfs並寫數
2019-01-22
JavaROS
通過php的MongoDB driver連線Azure的DocumentDB PaaS
2016-10-04
PHPMongoDB
通過Java程式測試資料庫連線資訊
2016-10-26
Java資料庫