include_once 'article_01_userevent.php';
// Конфигурация
$config = ['host' => '192.168.1.20', 'port' => '8123', 'username' => 'default', 'password' => ''];
$client = new \ClickHouseDB\Client($config);
//$client->write('DROP TABLE IF EXISTS articles.events');
if (!$client->isExists('articles', 'events')) {
$client->write('DROP TABLE IF EXISTS articles.events');
$client->write('CREATE DATABASE IF NOT EXISTS articles');
$client->write("\n CREATE TABLE articles.events (\n event_date Date DEFAULT toDate(event_time),\n event_time DateTime,\n event_type Enum8('VIEWS' = 1, 'CLICKS' = 2),\n site_id Int32,\n article_id Int32,\n ip String,\n city String,\n user_uuid String,\n referer String,\n utm String DEFAULT extractURLParameter(referer, 'utm_campaign')\n ) ENGINE = MergeTree(event_date, (site_id,event_type, article_id), 8192)\n");
// ---------------------------- создадим тестовый набор данных ---------------
$userEvent = new UserEvent();
@unlink($fileName);
echo "Write data to : " . $fileName . "\n\n";
for ($z = 0; $z < $count_rows; $z++) {
$row = ['event_date' => $userEvent->getDate(), 'event_time' => $userEvent->getTime(), 'event_type' => $userEvent->getType(), 'site_id' => $userEvent->getSiteId(), 'article_id' => $userEvent->getArticleId(), 'ip' => $userEvent->getIp(), 'city' => $userEvent->getCity(), 'user_uuid' => $userEvent->getUserUuid(), 'referer' => $userEvent->getReferer(), 'utm' => $userEvent->getUtm()];
file_put_contents($fileName, \ClickHouseDB\FormatLine::TSV($row) . "\n", FILE_APPEND);
if ($z % 100 == 0) {
echo "{$z}\r";
}
}
// Включаем сжатие
$client->setTimeout(300);
$client->database('articles');
$client->enableHttpCompression(true);
echo "\n> insertBatchFiles....\n";
$result_insert = $client->insertBatchTSVFiles('events', [$fileName], ['event_date', 'event_time', 'event_type', 'site_id', 'article_id', 'ip', 'city', 'user_uuid', 'referer', 'utm']);
echo "insert done\n";
echo $fileName . " : " . $result_insert[$fileName]->totalTimeRequest() . "\n";
}
$client->database('articles');
// Допустим нам нужно посчитать сколько уникальных пользователей просмотрело за сутки
/**
* Вставить массив
*
* @param $table
* @param $values
* @param array $columns
* @return Statement
*/
public function insert($table, $values, $columns = [])
{
$sql = 'INSERT INTO ' . $table;
if (0 !== count($columns)) {
$sql .= ' (' . implode(',', $columns) . ') ';
}
$sql .= ' VALUES ';
foreach ($values as $row) {
$sql .= ' (' . FormatLine::Insert($row) . '), ';
}
$sql = trim($sql, ', ');
return $this->transport()->write($sql);
}
public function testRFCCSVAndTSVWrite()
{
$fileName = $this->tmp_path . '__testRFCCSVWrite';
$array_value_test = "\n1\n2's'";
$this->db->write("DROP TABLE IF EXISTS testRFCCSVWrite");
$this->db->write('CREATE TABLE testRFCCSVWrite (
event_date Date DEFAULT toDate(event_time),
event_time DateTime,
strs String,
flos Float32,
ints Int32,
arr1 Array(UInt8),
arrs Array(String)
) ENGINE = Log(event_date, (event_time, keyz,keyb), 8192)');
@unlink($fileName);
$data = [['event_time' => date('Y-m-d H:i:s'), 'strs' => 'SOME STRING', 'flos' => 1.1, 'ints' => 1, 'arr1' => [1, 2, 3], 'arrs' => ["A", "B"]], ['event_time' => date('Y-m-d H:i:s'), 'strs' => 'SOME STRING', 'flos' => 2.3, 'ints' => 2, 'arr1' => [1, 2, 3], 'arrs' => ["A", "B"]], ['event_time' => date('Y-m-d H:i:s'), 'strs' => 'SOME\'STRING', 'flos' => 0, 'ints' => 0, 'arr1' => [1, 2, 3], 'arrs' => ["A", "B"]], ['event_time' => date('Y-m-d H:i:s'), 'strs' => "SOMET\nRI\n\"N\"G\\XX_ABCDEFG", 'flos' => 0, 'ints' => 0, 'arr1' => [1, 2, 3], 'arrs' => ["A", "B\nD\nC"]], ['event_time' => date('Y-m-d H:i:s'), 'strs' => "ID_ARRAY", 'flos' => 0, 'ints' => 0, 'arr1' => [1, 2, 3], 'arrs' => ["A", "B\nD\nC", $array_value_test]]];
// 1.1 + 2.3 = 3.3999999761581
//
foreach ($data as $row) {
file_put_contents($fileName, \ClickHouseDB\FormatLine::CSV($row) . "\n", FILE_APPEND);
}
$this->db->insertBatchFiles('testRFCCSVWrite', [$fileName], ['event_time', 'strs', 'flos', 'ints', 'arr1', 'arrs']);
$st = $this->db->select('SELECT sipHash64(strs) as hash FROM testRFCCSVWrite WHERE like(strs,\'%ABCDEFG%\') ');
$this->assertEquals('5774439760453101066', $st->fetchOne('hash'));
$ID_ARRAY = $this->db->select('SELECT * FROM testRFCCSVWrite WHERE strs=\'ID_ARRAY\'')->fetchOne('arrs')[2];
$this->assertEquals($array_value_test, $ID_ARRAY);
$row = $this->db->select('SELECT round(sum(flos),1) as flos,round(sum(ints),1) as ints FROM testRFCCSVWrite')->fetchOne();
$this->assertEquals(3, $row['ints']);
$this->assertEquals(3.4, $row['flos']);
unlink($fileName);
$this->db->write("DROP TABLE IF EXISTS testRFCCSVWrite");
$this->db->write('CREATE TABLE testRFCCSVWrite (
event_date Date DEFAULT toDate(event_time),
event_time DateTime,
strs String,
flos Float32,
ints Int32,
arr1 Array(UInt8),
arrs Array(String)
) ENGINE = Log(event_date, (event_time, keyz,keyb), 8192)');
foreach ($data as $row) {
file_put_contents($fileName, \ClickHouseDB\FormatLine::TSV($row) . "\n", FILE_APPEND);
}
$this->db->insertBatchTSVFiles('testRFCCSVWrite', [$fileName], ['event_time', 'strs', 'flos', 'ints', 'arr1', 'arrs']);
$row = $this->db->select('SELECT round(sum(flos),1) as flos,round(sum(ints),1) as ints FROM testRFCCSVWrite')->fetchOne();
$st = $this->db->select('SELECT sipHash64(strs) as hash FROM testRFCCSVWrite WHERE like(strs,\'%ABCDEFG%\') ');
$this->assertEquals('17721988568158798984', $st->fetchOne('hash'));
$ID_ARRAY = $this->db->select('SELECT * FROM testRFCCSVWrite WHERE strs=\'ID_ARRAY\'')->fetchOne('arrs')[2];
$this->assertEquals($array_value_test, $ID_ARRAY);
$row = $this->db->select('SELECT round(sum(flos),1) as flos,round(sum(ints),1) as ints FROM testRFCCSVWrite')->fetchOne();
$this->assertEquals(3, $row['ints']);
$this->assertEquals(3.4, $row['flos']);
$this->db->write("DROP TABLE IF EXISTS testRFCCSVWrite");
unlink($fileName);
return true;
}