)
ENGINE = SummingMergeTree(event_date, (site_id,hash_id, event_time, event_date), 8192)
');
echo "Table EXISTS:" . json_encode($db->showTables()) . "\n";
echo "----------------------------------- CREATE csv file -----------------------------------------------------------------\n";
$file_data_names = ['/tmp/clickHouseDB_test.part.1.data', '/tmp/clickHouseDB_test.part.2.data', '/tmp/clickHouseDB_test.part.3.data'];
$c = 0;
foreach ($file_data_names as $file_name) {
$c++;
makeSomeDataFileBigOldDates($file_name, $c);
}
echo "--------------------------------------- insert -------------------------------------------------------------\n";
echo "insert ALL file async + GZIP:\n";
$db->enableHttpCompression(true);
$time_start = microtime(true);
$result_insert = $db->insertBatchFiles('summing_partions_views', $file_data_names, ['event_time', 'site_id', 'hash_id', 'views']);
echo "use time:" . round(microtime(true) - $time_start, 2) . " sec.\n";
foreach ($result_insert as $fileName => $state) {
echo "{$fileName} => " . json_encode($state->info_upload()) . "\n";
}
}
echo "--------------------------------------- select -------------------------------------------------------------\n";
print_r($db->select('select min(event_date),max(event_date) from summing_partions_views ')->rows());
echo "--------------------------------------- list partitions -------------------------------------------------------------\n";
echo "databaseSize : " . json_encode($db->databaseSize()) . "\n";
echo "tableSize : " . json_encode($db->tableSize('summing_partions_views')) . "\n";
echo "partitions : " . json_encode($db->partitions('summing_partions_views', 2)) . "\n";
echo "--------------------------------------- drop partitions -------------------------------------------------------------\n";
echo "dropOldPartitions -30 days : " . json_encode($db->dropOldPartitions('summing_partions_views', 30)) . "\n";
echo "--------------------------------------- list partitions -------------------------------------------------------------\n";
echo "databaseSize : " . json_encode($db->databaseSize()) . "\n";
ENGINE = SummingMergeTree(event_date, (site_id, url_hash, event_time, event_date), 8192)
');
echo "Table EXISTS:" . json_encode($db->showTables()) . "\n";
// ------------------------------------------------------------------------------------------------------
echo "----------------------------------- CREATE big csv file -----------------------------------------------------------------\n";
$file_data_names = ['/tmp/clickHouseDB_test.b.1.data', '/tmp/clickHouseDB_test.b.2.data', '/tmp/clickHouseDB_test.b.3.data', '/tmp/clickHouseDB_test.b.4.data', '/tmp/clickHouseDB_test.b.5.data'];
$c = 0;
foreach ($file_data_names as $file_name) {
$c++;
makeSomeDataFileBig($file_name, 40 * $c);
}
echo "----------------------------------------------------------------------------------------------------\n";
echo "insert ALL file async NO gzip:\n";
$db->settings()->max_execution_time(200);
$time_start = microtime(true);
$result_insert = $db->insertBatchFiles('summing_url_views', $file_data_names, ['event_time', 'url_hash', 'site_id', 'views', 'v_00', 'v_55']);
echo "use time:" . round(microtime(true) - $time_start, 2) . "\n";
foreach ($result_insert as $state) {
echo "Info : " . json_encode($state->info_upload()) . "\n";
}
print_r($db->select('select sum(views) from summing_url_views')->rows());
echo "--------------------------------------- enableHttpCompression -------------------------------------------------------------\n";
echo "insert ALL file async + GZIP:\n";
$db->enableHttpCompression(true);
$time_start = microtime(true);
$result_insert = $db->insertBatchFiles('summing_url_views', $file_data_names, ['event_time', 'url_hash', 'site_id', 'views', 'v_00', 'v_55']);
echo "use time:" . round(microtime(true) - $time_start, 2) . "\n";
foreach ($result_insert as $fileName => $state) {
echo "{$fileName} => " . json_encode($state->info_upload()) . "\n";
}
print_r($db->select('select sum(views) from summing_url_views')->rows());
v_00 Int32,
v_55 Int32
)
ENGINE = SummingMergeTree(event_date, (site_id, url_hash, event_time, event_date), 8192)
');
echo "Table EXISTS: " . json_encode($db->showTables()) . "\n";
// -------------------------------- CREATE csv file ----------------------------------------------------------------
// ----------------------------------------------------------------------------------------------------
$file_data_names = ['/tmp/clickHouseDB_test.1.data', '/tmp/clickHouseDB_test.2.data', '/tmp/clickHouseDB_test.3.data', '/tmp/clickHouseDB_test.4.data', '/tmp/clickHouseDB_test.5.data'];
foreach ($file_data_names as $file_name) {
makeSomeDataFile($file_name, 5);
}
// ----------------------------------------------------------------------------------------------------
echo "insert ONE file:\n";
$time_start = microtime(true);
$stat = $db->insertBatchFiles('summing_url_views', ['/tmp/clickHouseDB_test.1.data'], ['event_time', 'url_hash', 'site_id', 'views', 'v_00', 'v_55']);
echo "use time:" . round(microtime(true) - $time_start, 2) . "\n";
print_r($db->select('select sum(views) from summing_url_views')->rows());
echo "insert ALL file async:\n";
$time_start = microtime(true);
$result_insert = $db->insertBatchFiles('summing_url_views', $file_data_names, ['event_time', 'url_hash', 'site_id', 'views', 'v_00', 'v_55']);
echo "use time:" . round(microtime(true) - $time_start, 2) . "\n";
print_r($db->select('select sum(views) from summing_url_views')->rows());
// ------------------------------------------------------------------------------------------------
foreach ($file_data_names as $fileName) {
echo $fileName . " : " . $result_insert[$fileName]->totalTimeRequest() . "\n";
}
// ------------------------------------------------------------------------------------------------
/*
Table EXISTSs:[{"name":"summing_url_views"}]
Created file [/tmp/clickHouseDB_test.1.data]: 22200 rows...
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' => 'SOME\'"TRING', '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"]]];
//// 1.1 + 2.3 = 3.3999999761581
//
foreach ($data as $row) {
file_put_contents($fileName, \ClickHouseDB\FormatLine::CSV($row) . "\n", FILE_APPEND);
}
//
echo "FILE:\n\n";
echo file_get_contents($fileName) . "\n\n----\n";
//
$db->insertBatchFiles('testRFCCSVWrite', [$fileName], ['event_time', 'strs', 'flos', 'ints', 'arr1', 'arrs']);
$st = $db->select('SELECT * FROM testRFCCSVWrite');
print_r($st->rows());
//
echo "\n<<<<< TAB >>>>\n";
$fileName = '/tmp/testRFCCSVWrite.TAB';
@unlink($fileName);
$db->write("DROP TABLE IF EXISTS testTABWrite");
$db->write('CREATE TABLE testTABWrite (
event_date Date DEFAULT toDate(event_time),
event_time DateTime,
strs String,
flos Float32,
ints Int32,
arr1 Array(UInt8),
arrs Array(String)
