PHPackages                             cybernic/yii2-clickhouse - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. cybernic/yii2-clickhouse

ActiveYii2-extension[Database & ORM](/categories/database)

cybernic/yii2-clickhouse
========================

ClickHouse for Yii2

2.1.1(6mo ago)14MITPHPPHP >=7.3.0

Since Oct 15Pushed 6mo agoCompare

[ Source](https://github.com/cybernic/yii2-clickhouse)[ Packagist](https://packagist.org/packages/cybernic/yii2-clickhouse)[ RSS](/packages/cybernic-yii2-clickhouse/feed)WikiDiscussions master Synced 1mo ago

READMEChangelog (4)DependenciesVersions (10)Used By (0)

Yii2 ClickHouse extension
=========================

[](#yii2-clickhouse-extension)

Installation
------------

[](#installation)

### Composer

[](#composer)

The preferred way to install this extension is through [Composer](http://getcomposer.org/).

Either run

- stable `php composer.phar require cybernic/clickhouse ~1.1`
- dev `php composer.phar require cybernic/clickhouse @dev`

or add to composer.json manual

- stable `"cybernic/clickhouse": "~1.1`
- dev `"cybernic/clickhouse": "@dev"`

to the require section of your composer.json

Configuration example
---------------------

[](#configuration-example)

```
   'components' => [
        'clickhouse' => [
            'class' => 'cybernic\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
        ],
   // ...
```

Notes
-----

[](#notes)

- If clickhouse server responds with no response == 200, then you will get the exception

Usage
-----

[](#usage)

```
   /** @var \cybernic\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();
```

batch insert files

```
    /** @var \cybernic\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);
    }

```

batch insert files, batch size = 100 lines

```
    /** @var \cybernic\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);
        }
     }
```

old methods: meta, rows, countAll, statistics

```

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

    /** @var \cybernic\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);

```

old examples ORM

```
use cybernic\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());
```

[Group With Modifiers](https://clickhouse.com/docs/en/sql-reference/statements/select/group-by)

```
use cybernic\clickhouse\Query;

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

Set specific options

```
  /** @var \cybernic\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([])
```

[Select with](https://clickhouse.com/docs/en/sql-reference/statements/select/with/)

```
    use cybernic\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
*/
```

Save custom model

```
use yii\base\Model;

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

    public function save($validate = true)
    {
        /** @var \cybernic\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 model
------------------

[](#activerecord-model)

```
use cybernic\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']);
    }
}
```

Using Gii generator
===================

[](#using-gii-generator)

```