5 ms·
Just for fun. Speed testing awk vs Java. awk -F';' '{ station = $1 temperature = $2 sum[station] += temperature count[station]++
by solarized 3y ago
Just for fun. Speed testing awk vs Java.
awk -F';' '{
station = $1
temperature = $2
sum[station] += temperature
count[station]++
if (temperature < min[station] || count[station] == 1) {
min[station] = temperature
}
if (temperature > max[station] || count[station] == 1) {
max[station] = temperature
}
}
END {
for (s in sum) {
mean = sum[s] / count[s]
printf "{%s=%.1f/%.1f/%.1f", s, min[s], mean, max[s]
printf (s == PROCINFO["sorted_in"][length(PROCINFO["sorted_in"])] ? "}\n" : ", ")
}
}' measurement.txt
- arkh 3y agoI'd like to see it speed tested against an instance of Postgres using the file Foreign Data Wrapper https://www.postgresql.org/docs/current/file-fdw.html https://www.postgresql.org/docs/current/file-fdw.html CREATE EXTENSION file_fdw; CREATE SERVER stations FOREIGN DATA WRAPPER file_fdw; CREATE FOREIGN TABLE records ( station_name text, temperature float ) SERVER stations OPTIONS (filename 'path/to/file.csv', format 'csv', delimiter ';'); SELECT station_name, MIN(temperature) AS temp_min, AVG(temperature) AS temp_mean, MAX(temperature) AS temp_max FROM records GROUP BY station_name ORDER BY station_name;
- nathancahill 3y agoMan, Postgres is so cool and powerful.
- aargh_aargh 3y agoUsing FDWs on a daily basis I fully realize their power and appeal but at this exclamation I paused and thought - is this really how we think today? That reading a CSV file directly is a cool feature, state of the art? Sure, FDWs are much more than that, but I would assume we could achieve much more with Machine Learning, and not even just the current wave of LLMs. Why not have the machine consider the data it is currently seeing (type, even actual values), think about what end-to-end operation is required, how often it needs to be repeated, make a time estimate (then verify the estimate, change it for the next run if needed, keep a history for future needs), choose one of methods it has at its disposal (index autogeneration, conversion of raw data, denormalization, efficient allocation of memory hierarchy, ...). Yeah, I'm not focusing on this specific one billion rows challenge but rather what computers today should be able to do for us.
- ftisiot 3y agoJust modified the original post to add the file_fdw. Again, none of the instances (PG or ClickHouse) were optimised for the workload https://ftisiot.net/posts/1brows/ https://ftisiot.net/posts/1brows/
- yencabulator 3y agoNot including this in the benchmark time is cheating: \copy TEST(CITY, TEMPERATURE) FROM 'measurements.txt' DELIMITER ';' CSV;
- ftisiot 3y agoThe loading time it's included in both examples In the first one, the table is dropped, recreated, populated and queries. In the second example, the table is created from a file FDW to the CSV file. In both examples the loading time is included in the total time
- rustforlinux 3y ago>The result is 44.465s! Very nice! Is this time for the first run or second? Is there a big difference between the first and second run, please?
- ftisiot 3y agofirst run! I just rerun the experiment: - ~46 secs on the first run - ~22 secs on the following runs
- DiggyJohnson 3y agoFor some reason I'm impressed that the previous page in the documentation is https://www.postgresql.org/docs/current/earthdistance.html https://www.postgresql.org/docs/current/earthdistance.html. Lotta fun stuff in Postgres these days.
- jackhalford 3y agoYour sum variable could get pretty large, consider using the streaming mean function, something like: new_mean = ((n*old_mean)+temp)/(n+1)
- apwheele 3y agoHad this same thought as well. Definitely comes up when calculating variance this way in streaming data. https://stats.stackexchange.com/a/235151/1036 https://stats.stackexchange.com/a/235151/1036 I am not sure if `n*old_mean` is a good idea. Wellford's is typically something like inside the loop count += 1; delta = current - mean; mean += delta/count;
- Algunenano 3y agoUsing ClickHouse local: time clickhouse local -q "SELECT concat('{', arrayStringConcat(groupArray(v), ', '), '}') FROM ( SELECT concat(station, '=', min(t), '/', max(t), '/', avg(t)) AS v FROM file('measurements.txt', 'CSV', 'station String, t Float32') GROUP BY station ORDER BY station ASC ) SETTINGS format_csv_delimiter = ';', max_threads = 8" >/dev/null real 0m15.201s user 2m16.124s sys 0m2.351 Most of the time is spent parsing the file