PHP code example of stajor / clickhouse-builder
1. Go to this page and download the library: Download stajor/clickhouse-builder library . Choose the download type require .
2. Extract the ZIP file and open the index.php.
3. Add this code to the index.php.
<?php
require_once ('vendor/autoload.php' );
stajor / clickhouse-builder example snippets
$server = new Tinderbox\Clickhouse\Server('127.0.0.1' , '8123' , 'default' , 'user' , 'pass' );
$serverProvider = (new Tinderbox\Clickhouse\ServerProvider())->addServer($server);
$client = new Tinderbox\Clickhouse\Client($serverProvider);
$builder = new Builder($client);
$builder->select('column' , 'column2' , 'column3 as alias' );
$builder->select(['column' , 'column2' , 'column3 as alias' ]);
$builder->select(['column' , 'column2' , 'column3' => 'alias' ]);
$builder->select(function ($column) {
$column->name('time' )->sumIf('time' , '>' , 10 );
});
$builder->select(function ($column) {
$column->as('alias' )
->query()
->select('column' )
->from('table' );
});
$1 = $builder->select(function ($column) {
$column->as('alias' )
->query(function ($query) {
$query->select('column' )->from('table' );
})
});
$2 = $builder->select(function ($column) {
$column->as('alias' )
->query($builder->select('column' )->from('table' ));
});
$builder->select('column' )->from('table' , 'alias' );
$builder->from(function ($from) {
$from->query()->select('column' )->from('table' );
});
$builder->from(function ($from) {
$from->query(function ($query) {
$query->select('column' )->from('table' );
});
});
$builder->from(function ($from) {
$from->query($builder->select('column' )->from('table' ));
});
$builder->from($builder->select('column' )->from('table' ));
$builder->select('column' )->from('table' )->sample(0.1 );
$builder->from('table' )->join('another_table' , 'any' , 'left' , ['column1' , 'column2' ], true );
$builder->from('table' )->join(function ($query) {
$query->select('column1' , 'column2' )->from('table2' );
}, 'any' , 'left' , ['column1' , 'column2' ]);
$builder->from('table' )->join($builder->select('column1' , 'column2' )->from('table2' ), 'any' , 'left' , ['column1' , 'column2' ]);
$builder->from('table' )->anyLeftJoin('table' , ['column' ]);
$builder->from('table' )->allLeftJoin('table' , ['column' ]);
$builder->from('table' )->allInnerJoin('table' , ['column' ]);
$builder->from('table' )->anyInnerJoin('table' , ['column' ]);
$buulder->from('table' )->leftJoin('table' , 'any' , ['column' ]);
$buulder->from('table' )->innerJoin('table' , 'all' , ['column' ]);
$builder->addFile(new TempTable('numbersTable' , 'numbers.tsv' , ['number' => 'UInt64' ], Format::TSV));
$builder->table(raw('numbers(0,1000)' )->whereIn('number' , 'numbersTable' )->get();
$builder->from('table' )->where('column' , '=' , 'value' );
$builder->from('table' )->where('column' , 'value' );
$builder->from('table' )->where(function ($query) {
$query->where('column1' , 'value' )->where('column2' , 'value' );
});
$builder->from('table' )->where(function ($query) {
$query->select('column' )->from('table' );
})
$builder->from('table' )->where('column' , 'IN' , function ($query) {
$query->select('column' )->from('table' );
});
$builder->where();
$builder->orWhere();
$builder->whereRaw();
$builer->orWhereRaw();
$builder->whereIn();
$builder->orWhereIn();
$builder->whereGlobalIn();
$builder->orWhereGlobalIn();
$builder->whereGlobalNotIn();
$builder->orWhereGlobalNotIn();
$builder->whereNotIn();
$builder->orWhereNotIn();
$builder->whereBetween();
$builder->orWhereBetween();
$builder->whereNotBetween();
$builder->orWhereNotBetween();
$builder->whereBetweenColumns();
$builder->orWhereBetweenColumns();
$builder->whereNotBetweenColumns();
$builder->orWhereNotBetweenColumns();
$builder->whereDict('dict' , 'attribute' , 'key' , '=' , 'value' );
$builder->whereDict('dict' , 'attribute' , [new Identifier('column' ), 'string value' ], '=' , 'value' );
$builder->from('table' )->select('column' , raw('count()' )->groupBy('attribute' );
$builder->from('table' )->orderBy('column' , 'asc' , 'fr' );
$builder->orderByAsc('column' );
$builder->orderByDesc('column' );
$builder->from('table' )->limitBy(1 , 'column1' , 'column2' );
$builder->from('table' )->limit(10 , 100 );
$builder->from('table' )->limit(10 , 100 );
$builder->from('table' )->unionAll(function ($query) {
$query->select('column1' )->from('table' );
})->unionAll($builder->select('column2' )->from('table' ));
$builder->from('table' )->asyncWithQuery(function ($query) {
$query->from('table' );
});
$builder->from('table' )->asyncWithQuery($builder->from('table' ));
$builder->from('table' )->asyncWithQuery()->from('table' );
'providers' => [
...
\Tinderbox\ClickhouseBuilder\Integrations\Laravel\ClickhouseServiceProvider::class,
...
]
$app->register(\Tinderbox\ClickhouseBuilder\Integrations\Laravel\ClickhouseServiceProvider::class);
'connections' => [
'clickhouse' => [
'driver' => 'clickhouse' ,
'host' => 'ch-00.domain.com' ,
'port' => '' ,
'database' => '' ,
'username' => '' ,
'password' => '' ,
'options' => [
'timeout' => 10 ,
'protocol' => 'https'
]
]
]
'connections' => [
'clickhouse' => [
'driver' => 'clickhouse' ,
'servers' => [
[
'host' => 'ch-00.domain.com' ,
'port' => '' ,
'database' => '' ,
'username' => '' ,
'password' => '' ,
'options' => [
'timeout' => 10 ,
'protocol' => 'https'
]
],
[
'host' => 'ch-01.domain.com' ,
'port' => '' ,
'database' => '' ,
'username' => '' ,
'password' => '' ,
'options' => [
'timeout' => 10 ,
'protocol' => 'https'
]
]
]
]
]
'connections' => [
'clickhouse' => [
'driver' => 'clickhouse' ,
'clusters' => [
'cluster-name' => [
[
'host' => '' ,
'port' => '' ,
'database' => '' ,
'username' => '' ,
'password' => '' ,
'options' => [
'timeout' => 10 ,
'protocol' => 'https'
]
],
[
'host' => '' ,
'port' => '' ,
'database' => '' ,
'username' => '' ,
'password' => '' ,
'options' => [
'timeout' => 10 ,
'protocol' => 'https'
]
]
]
]
]
]
DB::connection('clickhouse' )->using('ch-01.domain.com' )->select(...);
DB::connection('clickhouse' )->usingRandomServer()->select(...);
DB::connection('clickhouse' )->onCluster('test' )->select(...);
sql
SELECT sumIf(`time`, time > 10 )