The preferred way to install this extension is through Composer.
Either run
php composer.phar require kak/clickhouse "dev-master"
or add
"kak/clickhouse": "dev-master"
to the require section of your composer.json
'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
],
// ... - If clickhouse server responds with no response == 200, then you will get the exception
/** @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(); batch insert files
$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
$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 \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->getMotals()); // result WITH TOTALS
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
$q = (new \kak\clickhouse\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); // result data
var_dump($total); // result WITH TOTALS
// -----
$command = (new \kak\clickhouse\Query())
->from('test_stat')
->withTotals();
$result = $command->all(); // result data
var_dump($command->getTotals()); // result WITH TOTALSset specific options
/** @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([])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 \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();
}
}class Stat extends \kak\clickhouse\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::className(),['id' => 'user_id']);
}
}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'
],
]
];
$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')
]
]);migration mysql,mssql data >>> clickhouse
create custom console controller
// ...
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,
'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();
}Result
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
https://clickhouse.yandex/reference_en.html
- 1 Accumulated data and insert at one time, it will reduce the operations io disk
- 2 @todo how that will add...