bashkarev / clickhouse Goto Github PK

View Code? Open in Web Editor NEW

22.0 4.0 15.0 194 KB

Extension ClickHouse for Yii 2

License: MIT License

PHP 100.00%

clickhouse php yii2 yii2-extension

clickhouse's Introduction

Extension ClickHouse for Yii 2

This extension provides the ClickHouse integration for the Yii framework 2.0. Main features:

SQL commands
Query builder
Schema builder
Migrations
Batch Insert
Parallel insert from large CSV files
Valid handling of UInt64 type in PHP
Supports Decimals and Nullable fields

Installation

The preferred way to install this extension is through composer.

Either run

composer require bashkarev/clickhouse

Configuration

To use this extension, simply add the following code in your application configuration:

return [
    //....
    'clickhouse' => [
        'class' => 'bashkarev\clickhouse\Connection',
        'dsn' => 'host=localhost;port=8123;database=default;connect_timeout_with_failover_ms=10',
        'username' => 'default',
        'password' => '',
    ],
];

All settings

Using DebugPanel

Add the following to you application config to enable it (if you already have the debug module enabled, it is sufficient to just add the panels configuration):

    // ...
    'bootstrap' => ['debug'],
    'modules' => [
        'debug' => [
            'class' => 'yii\\debug\\Module',
            'panels' => [
                'clickhouse' => [
                    'class' => 'bashkarev\clickhouse\debug\Panel',
                    // 'db' => 'clickhouse', // ClickHouse component ID, defaults to `db`. Uncomment and change this line, if you registered component with a different ID.
                ],
            ],
        ],
    ],
    // ...

Using Migrations

In order to enable this command you should adjust the configuration of your console application:

return [
    // ...
    'controllerMap' => [
        'clickhouse-migrate' => 'bashkarev\clickhouse\console\controllers\MigrateController'
    ],
];

# creates a new migration named 'create_target'
yii clickhouse-migrate/create create_target

# applies ALL new migrations
yii clickhouse-migrate

# reverts the last applied migration
yii clickhouse-migrate/down

Access to native SMI2 ClickHouse client

$client = \Yii::$app->clickhouse->getClient();

Insert csv files

Files are uploaded in parallel.

$db = \Yii::$app->clickhouse;
$client = $db->getClient();

$results = $client->insertBatchFiles('table_name', ['file_with_data.csv']);

$state = $results['file_with_data.csv'];
$isSuccess = !$state->isError();
$uploadInfo = $state->responseInfo();

print_r($uploadInfo);

clickhouse's People

Contributors

Stargazers

Watchers

Forkers

fingligroup mysterydragon arkhamvm sartor tonisormisson olexainf fgh151 vistarsvo wojiukankan alexeitsurkan zkelo perfectpanel ddkirawan

clickhouse's Issues

Error while using OFFSET

SELECT `statistic`.* FROM `statistic` ALL INNER JOIN `copyright_owner` ON statistic.copyright_owner_id=copyright_owner.id ORDER BY `statistic`.`viewed_at` LIMIT 20 OFFSET 1;

Syntax error: failed at position 165:

SELECT `statistic`.* FROM `statistic` ALL INNER JOIN `copyright_owner` ON statistic.copyright_owner_id=copyright_owner.id ORDER BY `statistic`.`viewed_at` LIMIT 20 OFFSET 1;

Expected one of: UNION ALL, Comma, BY, SETTINGS, INTO OUTFILE, FORMAT, LIMIT, token

How to increase max query size?

I have query where in (ids) is a big items. How I can set options to increase during querying?

Impossible to set database

Consider to add 'database' property to Connection or improve DSN syntax similar to regular connections (mysql for example): 'dsn' => 'tcp://localhost:8823;dbname=test'

Fix error in getClient()

Connection.php

change

    public function getClient(): Client
    {
        return $this->_client;
    }

    public function getClient(): Client
    {
        $this->open();
        
        return $this->_client;
    }

Maual not in line with v2

the manual of v2 refers to batchInsertFiles() which does not seem to exist and throws an exception

Command::batchInsert is a standard feature, but ClickHouse can insert data from uploaded files via Http Transport for better performance.
It would be nice to have this functionality. A signature may be similar to this Command::batchInsertFiles($table, array $files)

PHP 7.2 compatibility

Changed column name in ClickHouse 1.1.54237+

If I get it correctly, starting from ClickHouse 1.1.54237 in system.columns default_type was renamed to default_kind, so it lead to some mistakes, for example here.

Migration Table column date as 1970 year alwas

Table structure:

'version' => 'String',
 'date' => 'Date',
 'apply_time' => 'UInt32',
 'is_deleted' => 'UInt8' //0 active 1 //deleted

Migration history insert:

$command->insert($this->migrationTable, [
            'version' => $version,
            'apply_time' => time(),
            'is_deleted' => 0
        ])->execute();

So, no Date in insert...

Exception 'yii\db\Exception' with message 'Specify engine type'

Hello. I make a migration:
use bashkarev\clickhouse\Migration;
class m230706_140057_create_target extends Migration
{
const TABLE_NAME = 'first_table';
public function up()
{
$this->createTable(
self::TABLE_NAME,
[
'id' => $this->primaryKey(),
'account' => $this->string(35),
'account_state' => $this->tinyInteger(),
]
);

...
run
yii clickhouse-migrate
and get error:

create table first_table ...Exception 'yii\db\Exception' with message 'Specify engine type'

Need sample options string

Yii2 migration error - This syntax for *MergeTree engine is deprecated

...

Sockets vs. Curl

Which are the points to use "low-level" sockets instead of yii-httpclient for example?
Performance? Could you provide a link to some tests or articles?
Otherwise, it looks like the wheel reinventing :)

bashkarev / clickhouse Goto Github PK

clickhouse's Introduction

Extension ClickHouse for Yii 2

Installation

Configuration

Using DebugPanel

Using Migrations

Access to native SMI2 ClickHouse client

Insert csv files

clickhouse's People

Contributors

Stargazers

Watchers

Forkers

clickhouse's Issues

Recommend Projects

Recommend Topics

Recommend Org