hash_id Int32, views Int32 ) 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";
$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'); // Допустим нам нужно посчитать сколько уникальных пользователей просмотрело за сутки print_r($client->select(' SELECT event_date, uniqCombined(user_uuid) as count_users FROM events WHERE site_id=1