3 ms·
It is very easy to get this dataset directly from HN API. Let me just post it here: Table definition: CREATE TABLE hackernews_history ( update
by zX41ZdbW 2y ago
It is very easy to get this dataset directly from HN API.
Let me just post it here:
Table definition:
CREATE TABLE hackernews_history
(
update_time DateTime DEFAULT now(),
id UInt32,
deleted UInt8,
type Enum('story' = 1, 'comment' = 2, 'poll' = 3, 'pollopt' = 4, 'job' = 5),
by LowCardinality(String),
time DateTime,
text String,
dead UInt8,
parent UInt32,
poll UInt32,
kids Array(UInt32),
url String,
score Int32,
title String,
parts Array(UInt32),
descendants Int32
)
ENGINE = MergeTree(update_time) ORDER BY id;
A shell script:
BATCH_SIZE=1000
TWEAKS="--optimize_trivial_insert_select 0 --http_skip_not_found_url_for_globs 1 --http_make_head_request 0 --engine_url_skip_empty_files 1 --http_max_tries 10 --max_download_threads 1 --max_threads $BATCH_SIZE"
rm -f maxitem.json
wget --no-verbose https://hacker-news.firebaseio.com/v0/maxitem.json
clickhouse-local --query "
SELECT arrayStringConcat(groupArray(number), ',') FROM numbers(1, $(cat maxitem.json))
GROUP BY number DIV ${BATCH_SIZE} ORDER BY any(number) DESC" |
while read ITEMS
do
echo $ITEMS
clickhouse-client $TWEAKS --query "
INSERT INTO hackernews_history SELECT * FROM url('https://hacker-news.firebaseio.com/v0/item/{$ITEMS}.json')"
done
It takes a few hours to download the data and fill the table.
- zX41ZdbW 2y agoAlso, a proof that it is updated in real-time: https://play.clickhouse.com/play?user=play#U0VMRUNUICogRlJPTSBoYWNrZXJuZXdzX2hpc3RvcnkgV0hFUkUgdGV4dCBJTElLRSAnJUNsaWNrSG91c2UlJyBPUkRFUiBCWSB0aW1lIERFU0MgTElNSVQgMTAw https://play.clickhouse.com/play?user=play#U0VMRUNUICogRlJPT...
- strooper 2y agoWhile trying the script, I am getting the following error - <Trace> ReadWriteBufferFromHTTP: Failed to make request to 'https://hacker-news.firebaseio.com/v0/item/40298680.json https://hacker-news.firebaseio.com/v0/item/40298680.json'. Error: Timeout: connect timed out: 216.239.32.107:443. Failed at try 3/10. Will retry with current backoff wait is 200/10000 ms. I googled with no luck. I was wondering if you have a solution for it.
- zX41ZdbW 2y agoIt makes many requests in parallel, and that's why some of them could be retried. It logs every retry, e.g., "Failed at try 3/10". It will throw an error only if it fails all ten tries. The number of retries is defined in the script. Example of how it should work: $ ch -q "SELECT * FROM url('https://hacker-news.firebaseio.com/v0/item/40298680.json')" --format Vertical Row 1: ────── by: octopoc id: 40298680 parent: 40297716 text: Oops, thanks. I guess Marx was being referenced? I had thought Marx was English but apparently he was German-Jewish[1]<p>[1] <a href="https://en.wikipedia.org/wiki/Karl_Marx" rel="nofollow">https://en.wikipedia.org/wiki/Karl_Marx</a> time: 1715179584 type: comment
- tarasglek 2y agoMay I hijack this thread for a related q. I love the public up-to-date hn dataset. I saw recursive cte blog post..but this doesn't seem to work your hn dataset https://play.clickhouse.com/play?user=play#V0lUSCBSRUNVUlNJVkUgQW5jZXN0b3IgQVMgKAogICAgU0VMRUNUIGlkLCBwYXJlbnQsIHR5cGUsIDEgYXMgZGVwdGgKICAgIEZST00gaGFja2VybmV3c19jaGFuZ2VzX2l0ZW1zCiAgICBXSEVSRSBpZCA9IDQwMzIzNDM2CiAgICAKICAgIFVOSU9OIEFMTAogICAgCiAgICBTRUxFQ1QgYS5pZCwgYS5wYXJlbnQsIGEudHlwZSwgYS5kZXB0aCArIDEgQVMgZGVwdGgKICAgIEZST00gQW5jZXN0b3IgQVMgYSwgaGFja2VybmV3c19jaGFuZ2VzX2l0ZW1zIEFTIGgKICAgIHdoZXJlIGgucGFyZW50ID0gYS5pZCBhbmQgZGVwdGggPCAyCikKU0VMRUNUICoKRlJPTSBBbmNlc3Rvcgo= https://play.clickhouse.com/play?user=play#V0lUSCBSRUNVUlNJV... Are recursive ctes disabled on this instance or am i doing something wrong?
- zX41ZdbW 2y agoDone, and now it works perfectly.
- tarasglek 2y agowhat was broken?
- zX41ZdbW 2y agoThis is unclear to me, I will ask the author.
- zX41ZdbW 2y agoThe reason is trivial - I disabled the new feature flag on the playground service long ago (when it was in development). I will enable it back and send an example.