troshyn/kak-clickhouse-fork

为 Yii2 的 ClickHouse

维护者

详细信息

github.com/fgjjkhkkoljgffkkjbvb/kak-clickhouse

源代码

安装数: 17,627

依赖项: 0

建议者: 0

安全性: 0

星星: 0

关注者: 0

分支: 42

类型:yii2-extension

v1.4.0 2023-05-21 12:22 UTC

Requires

MIT 824bae8bb67f794c173cba935d3d549373922faf

  • Tutik Alexsandr <sanchezzzhak.woop@ya.ru>

yii2clickhouseClickHouse ActiveRecord Yii2

This package is auto-updated.

Last update: 2024-09-21 15:20:07 UTC

README

安装

Composer

安装此扩展的首选方式是通过 Composer。

运行以下命令之一

  • 稳定版 php composer.phar require kak/clickhouse ~1.1
  • 开发版 php composer.phar require kak/clickhouse @dev

或手动添加到 composer.json 文件中

  • 稳定版 "kak/clickhouse": "~1.1
  • 开发版 "kak/clickhouse": "@dev"

到 composer.json 的 require 部分中

配置示例

   'components' => [
        'clickhouse' => [
            'class' => 'kak\clickhouse\Connection',
            'dsn' => '127.0.0.1',
            'port' => '8123',
           // 'database' => 'default',  // use other database name
            'username' => 'web',
            'password' => '123',
            'enableSchemaCache' => true,
            'schemaCache' => 'cache',
            'schemaCacheDuration' => 86400
        ],
   // ...

说明

  • 如果 ClickHouse 服务器响应无响应 == 200，则您将获得异常

使用方法

   /** @var \kak\clickhouse\Connection $client */
    $client = \Yii::$app->clickhouse;
    $sql = 'select * from stat where counter_id=:counter_id';
    $client->createCommand($sql, [
        ':counter_id' => 122
    ])->queryAll();
    
    // ====== insert data ORM ======
    
    $client->createCommand(null)
    ->insert('stat', [
        'event_data' => date('Y-m-d'),
        'counter_id' => 122
    ])
    ->execute();

批量插入文件

    /** @var \kak\clickhouse\Connection $clickhouse */
    $clickhouse = \Yii::$app->clickhouse;

    $files = [
        'dump_20170502' => Yii::getAlias('@app/dump_20170502.csv'),
        'dump_20170503' => Yii::getAlias('@app/dump_20170503.csv'),
        'dump_20170504' => Yii::getAlias('@app/dump_20170504.csv'),
    ];	
    		
    $responses = $clickhouse->createCommand(null)
    ->batchInsertFiles('stat', null, [
        $files
    ], 'CSV');

    foreach ($responses as $keyId => $response) {
        var_dump($keyId . ' ' . $response->isOk);
    }

批量插入文件，批量大小 = 100 行

    /** @var \kak\clickhouse\Connection $clickhouse */
    $clickhouse = \Yii::$app->clickhouse;

    $responses = $clickhouse->createCommand(null)
    ->batchInsertFilesDataSize('stat', null, [
        $files
    ], 'CSV', 100);	
     foreach ($responses as $keyId => $parts) {
        foreach ($parts as $partId => $response) {
            var_dump($keyId . '_' . $partId. ' ' . $response->isOk);
        }
     }

旧方法：meta、rows、countAll、statistics

   	
    $sql = 'SELECT 
        user_id, sum(income) AS sum_income
        FROM stat
        GROUP BY event_date
        WITH TOTALS
        LIMIT 10
    '; 	

    /** @var \kak\clickhouse\Connection $clickhouse */
    $clickhouse = \Yii::$app->clickhouse;
    
    $command = $clickhouse->createCommand($sql);  	
    $result = $command->queryAll();
    
    var_dump($command->getMeta());  	      // columns meta info (columnName, dataType)
    var_dump($command->getTotals());          // get totals rows to read
    var_dump($command->getData());  	      // get rows data
    var_dump($command->getRows());  	      // rows count current result
    var_dump($command->getCountAll());        // rows count before limit at least	
    var_dump($command->getExtremes());  	
    var_dump($command->getStatistics());      // stat query 
    
 //or
     
    $command = $clickhouse->createCommand($sql);  
    $result = $command->queryAll($command::FETCH_MODE_ALL);
    var_dump($result);

旧示例 ORM

use kak\clickhouse\Query;

$q = (new Query())
    ->from('stat')
    ->withTotals()
    ->where(['event_date' => '2017-05-01' , 'user_id' => 5])
    ->offset(2)
    ->limit(1);

$command = $q->createCommand();
$result  = $command->queryAll();
$total   = $command->getTotals();

var_dump($result);
var_dump($total); 

// -----
$command = (new Query())
    ->select(['event_stat', 'count()'])
    ->from('test_stat')
    ->groupBy('event_date')
    ->limit(1)
    ->withTotals();
    
$result =  $command->all();
var_dump($command->getTotals());

使用修饰符进行分组

use kak\clickhouse\Query;

$command = (new Query());
// ...
$command->withTotals();
// or
$command->withCube();
// or
$command->withRollup();

设置特定选项

  /** @var \kak\clickhouse\Connection $client */
    $client = \Yii::$app->clickhouse;
    $sql = 'select * from stat where counter_id=:counter_id';
    $client->createCommand($sql, [
        ':counter_id' => 122
    ])->setOptions([
        'max_threads' => 2
    ])->queryAll();

// add options use method
// ->addOptions([])

使用 ... 选择

    use kak\clickhouse\Query;
    // ...

    $db = \Yii::$app->clickhouse;
    $query = new Query();
    // first argument scalar var or Query object
    $query->withQuery($db->quoteValue('2021-10-05'), 'date1');
    $query->select('*');
    $query->from('stat');
    $query->where('event_stat < date1');
    $query->all();
/*
    WITH '2020-07-26' AS date1 SELECT * FROM stat WHERE event_stat < date1
*/

保存自定义模型

use yii\base\Model;

class Stat extends Model
{
    public $event_date; // Date;
    public $counter_id  = 0; // Int32,

    public function save($validate = true)
    {
        /** @var \kak\clickhouse\Connection $client */
        $client = \Yii::$app->clickhouse;
        $this->event_date = date('Y-m-d');

        if ($validate && !$this->validate()) {
            return false;
        }

        $attributes = $this->getAttributes();
        $client->createCommand(null)
            ->insert('stat', $attributes)
            ->execute();

        return true;	
    }
}

ActiveRecord 模型

use kak\clickhouse\ActiveRecord;
use app\models\User;

class Stat extends ActiveRecord
{
    // pls overwrite method is config section !=clickhouse
    // default clickhouse
	public static function getDb()
	{
	    return \Yii::$app->clickhouse;
	}


    public static function tableName()
    {
        return 'stat';
    }
    
    // use relation in mysql (Only with, do not use joinWith)
    public function getUser()
    {
    	return $this->hasOne(User::class, ['id' => 'user_id']);
    }
}

使用 Gii 生成器

<?php
return [
    //....
    'modules' => [
        // ...
        'gii' => [
            'class' => 'yii\gii\Module',
            'allowedIPs' => [
                '127.0.0.1',
                '::1',
                '192.168.*',
                '10.*',
            ],
            'generators' => [
                'clickhouseDbModel' => [
                    'class' => 'kak\clickhouse\gii\model\Generator'
                ]
            ],
        ],
    ]
];

使用调试面板

$config['bootstrap'][] = 'debug';
    $config['modules']['debug'] = [
        'class' => 'yii\debug\Module',
        'allowedIPs' => [
            '127.0.0.1',
            '::1',
            '192.168.*',
            '10.*',
        ],
        'panels' => [
            'clickhouse' => [
                'class' => 'kak\clickhouse\debug\Panel',
                'db' => 'clickhouse'
            ],
        ]

    ];

使用 SqlDataProvider

$sql = 'select * from stat where counter_id=:counter_id and event_date=:date';
$provider = new \kak\clickhouse\data\SqlDataProvider([
    'db' => 'clickhouse',
    'sql' => $sql,
    'params' => [
        ':counter_id' => 1,
        ':date' => date('Y-m-d')
    ]
]);

使用迁移数据

将 schema mysql >>> 转换为 clickhouse
创建自定义控制台控制器

    // ...
    public function actionIndex()
    {
        $exportSchemaCommand = new \kak\clickhouse\console\MigrationSchemaCommand([
            'sourceTable' => 'stat',
            'sourceDb' => \Yii::$app->db,
            'excludeSourceColumns' => [
                'id',
            ]
            'columns' => [
                '`event_date` Date' 
            ]
        ]);
        // result string SQL schema  
        $sql = $exportSchemaCommand->run();
        echo $sql;
    }

迁移 mysql、mssql 数据 >>> clickhouse
创建自定义控制台控制器

  // ...
    public function actionIndex()
    {
        $exportDataCommand = new \kak\clickhouse\console\MigrationDataCommand([
            'sourceQuery' => (new Query())->select('*')->from('stat'),
            'sourceDb' => \Yii::$app->db,
            'storeTable' => 'test_stat',
            'storeDb' => \Yii::$app->clickhouse,
            'batchSize' => 10000,
            'filterSourceRow' => function($data){
                // if result false then skip save row
                $time = strtotime($data['hour_at']);
                return $time > 0;
            },
            'mapData' => [
                // key storeTable column => sourceTable column|call function 
                'event_date' => function($data){
                    return date('Y-m-d',strtotime($data['hour_at']));
                },
                'time' => function($data){
                    return strtotime($data['hour_at']);
                },
                'user_id' => 'partner_id'
            ]    
        ]);
        $exportDataCommand->run();  
     
    }

结果

php yii export-test/index

total count rows source table 38585
part data files count 4
save files dir: /home/user/test-project/www/runtime/clickhouse/stat
parts:
 >>> part0.data time 4.749
 >>> part1.data time 4.734
 >>> part2.data time 4.771
 >>> part3.data time 4.089
insert files
 <<< part0.data  time 3.289
 <<< part1.data  time 2.024
 <<< part2.data  time 1.938
 <<< part3.data  time 3.359
done

ClickHouse 参考手册

https://clickhouse.yandex/reference_en.html

插入数据建议总结

  • 1 累积数据并一次性插入，这将减少 io 磁盘操作
  • 2 @todo 如何添加...

运行测试

  • 1 克隆仓库 https://github.com/sanchezzzhak/kak-clickhouse.git
  • 2 composer install --ignore-platform-reqs
  • 3 创建 ClickHouse 配置 touch tests/_config/clickhouse.php 如果您有非标准的服务器连接访问
<?php

return [
    'class' => 'kak\clickhouse\Connection',
    'dsn' => '127.0.0.1',
    'port' => '8123',
    'username' => 'web',
    'password' => '123',
    'enableSchemaCache' => true,
    'schemaCache' => 'cache',
    'schemaCacheDuration' => 86400
];
  • 4 运行测试 php vendor/bin/codecept run