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 協議》，轉載必須註明作者和本文連結

ubuntu wps odbc 連線sqlite
2024-06-24
UbuntuSQLite
mysql odbc delphi連線問題
2024-11-23
MySql
C#/C++ 透過ODBC連線OceanBase Oracle租戶
2024-05-07
C#C++Oracle
Oracle透過ODBC連線SQL Server資料庫後ORA-12514
2022-11-17
OracleSQLServer資料庫
通過跳板機連線MySQL
2020-09-24
MySql
【YashanDB資料庫】PHP無法透過ODBC連線到資料庫
2024-08-08
資料庫PHP
[20181224]使用odbc連線oracle資料庫.txt
2018-12-24
Oracle資料庫
藉助Windows自帶的ODBC工具驗證資料庫連線是否通暢
2019-04-01
Windows資料庫
Linux下如何通過命令連線wifi
2020-01-08
LinuxWiFi
通過驅動建立與MySQL的連線
2018-05-07
MySql
通過外來鍵連線多個表
2020-05-04
Linux下ODBC連線HGDB報Could not SQLConnect錯解決方法
2022-04-06
LinuxSQL
Laravel redis 連不同的連線
2021-03-09
LaravelRedis
laravel建立軟連線
2018-09-21
Laravel
怎樣通過驅動程式連線到access
2018-04-09
windows 通過cmd命令（netsh wlan命令）連線wifi
2020-03-19
WindowsWiFi
1.3.3. 通過SQL*Plus 連線資料庫
2020-02-20
SQL資料庫
【LISTENER】Oracle通過監聽連線緩慢分析
2021-12-28
Oracle
java通過kerberos認證連線hdfs並寫數
2019-01-22
JavaROS
root 使用者通過 sqlplus 連線資料庫
2020-10-16
SQL資料庫
vertica 如何實現儲存過程？
2020-05-21
儲存過程
laravel 通過 rpc 呼叫 golang 程式
2021-03-10
LaravelRPCGolang
[Oracle-> MySQL] Oracle通過dblink連線MySQL--Oracle 19c連線到MySQL 5.7
2019-05-11
OracleMySql
Java的JDBC通過SSH Tunnel連線MySQL資料庫
2019-03-14
JavaJDBCMySql資料庫
Laravel——通過郵件找回密碼
2019-03-04
Laravel密碼
如何本機通過 SSH 通道連線雲伺服器 MySQL
2018-10-25
伺服器MySql
root使用者無法通過ssh連線Linux系統
2018-11-13
Linux
通過觸發器記錄資料庫連線資訊
2018-04-11
觸發器資料庫
Windows 下 Laravel 7.0 連線 sql server
2020-06-18
WindowsLaravelSQLServer
使用 Excel 讀取 SAP ABAP CDS View 通過 ODBC 暴露出來的資料
2021-09-09
ExcelView
0459-如何使用SQuirreL通過JDBC連線CDH的Hive（方式一）
2018-11-24
UIJDBCHive
通過 VGA 介面連線顯示器時解析度不正確
2020-04-10
1.6.5.2. 通過密碼檔案驗證連線資料庫
2020-02-21
密碼資料庫
Oracle/MySQL透過odbc訪問PostgreSQL for LightDB
2022-11-08
OracleMySql
樹莓派已經通過網路連線通過串列埠通訊在串列埠除錯小助手列印與操作
2020-10-18
樹莓派串列埠除錯
Laravel 通過 Macros 實現列印原生 MySQL
2020-02-24
LaravelMacROSMySql
Laravel 使用多個資料庫連線
2018-08-27
Laravel資料庫
通過本地直接連線linux伺服器的mysql資料庫
2018-10-26
Linux伺服器MySql資料庫