Hacker News 데이터셋
Hacker News 데이터셋
Hacker News 데이터 2800만 행을 CSV와 Parquet 형식으로 ClickHouse 테이블에 삽입하고 간단한 쿼리로 데이터를 탐색하는 튜토리얼입니다. 스키마 추론(schema inference), 텍스트 인덱스, clickhouse-local 활용까지 다룹니다.
출처: 문서
본문
이 튜토리얼에서는 Hacker News 데이터 2800만 행을 CSV와 Parquet 형식으로 ClickHouse 테이블에 삽입하고, 데이터를 탐색하기 위해 몇 가지 간단한 쿼리를 실행할 것입니다.
CSV
- CSV 다운로드
데이터셋의 CSV 버전은 공용 S3 버킷에서 내려받거나 다음 명령을 실행해 얻을 수 있습니다:
wget https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.csv.gz
4.6GB, 2800만 행 크기의 이 압축 파일은 다운로드하는 데 5~10분이 걸려야 합니다.
- 데이터 샘플링
clickhouse-local을 사용하면 ClickHouse 서버를 배포하고 구성하지 않고도 로컬 파일에서 빠르게 처리할 수 있습니다. ClickHouse에 데이터를 저장하기 전에 clickhouse-local로 파일을 샘플링해 봅시다. 콘솔에서 실행하세요:
clickhouse-local
다음으로, 데이터를 탐색하려면 다음 명령을 실행하세요: Query
SELECT *
FROM file('hacknernews.csv.gz', CSVWithNames)
LIMIT 2
SETTINGS input_format_try_infer_datetimes = 0
FORMAT Vertical
Response
Row 1:
──────
id: 344065
deleted: 0
type: comment
by: callmeed
time: 2008-10-26 05:06:58
text: What kind of reports do you need?<p>ActiveMerchant just connects your app to a gateway for cc approval and processing.<p>Braintree has very nice reports on transactions and it's very easy to refund a payment.<p>Beyond that, you are dealing with Rails after all–it's pretty easy to scaffold out some reports from your subscriber base.
dead: 0
parent: 344038
poll: 0
kids: []
url:
score: 0
title:
parts: []
descendants: 0
Row 2:
──────
id: 344066
deleted: 0
type: story
by: acangiano
time: 2008-10-26 05:07:59
text:
dead: 0
parent: 0
poll: 0
kids: [344111,344202,344329,344606]
url: http://antoniocangiano.com/2008/10/26/what-arc-should-learn-from-ruby/
score: 33
title: What Arc should learn from Ruby
parts: []
descendants: 10
이 명령에는 미묘한 기능이 많습니다. file 연산자는 형식 CSVWithNames만 지정해 로컬 디스크에서 파일을 읽을 수 있게 해 줍니다. 가장 중요하게는 스키마가 파일 내용에서 자동으로 추론됩니다. 또한 clickhouse-local이 압축 파일을 읽고 확장자에서 gzip 형식을 추론하는 것도 주목하세요. Vertical 형식은 각 컬럼의 데이터를 더 쉽게 볼 수 있게 합니다.
- 스키마 추론으로 데이터 로드
데이터 로딩을 위한 가장 간단하고 강력한 도구는 풍부한 기능을 갖춘 네이티브 커맨드라인 클라이언트인 clickhouse-client입니다. 데이터를 로드할 때 다시 스키마 추론을 활용할 수 있으며, ClickHouse가 컬럼의 타입을 결정하도록 합니다. 다음 명령을 실행해 url 함수를 통해 원격 CSV 파일의 내용에 접근해 테이블을 만들고 데이터를 직접 삽입하세요. 스키마는 자동으로 추론됩니다:
CREATE TABLE hackernews ENGINE = MergeTree ORDER BY tuple
(
) EMPTY AS SELECT * FROM url('https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.csv.gz', 'CSVWithNames');
이것은 데이터에서 추론된 스키마를 사용해 빈 테이블을 만듭니다. DESCRIBE TABLE 명령으로 할당된 타입을 이해할 수 있습니다.
Query
DESCRIBE TABLE hackernews
Response
┌─name────────┬─type─────────────────────┬
│ id │ Nullable(Float64) │
│ deleted │ Nullable(Float64) │
│ type │ Nullable(String) │
│ by │ Nullable(String) │
│ time │ Nullable(String) │
│ text │ Nullable(String) │
│ dead │ Nullable(Float64) │
│ parent │ Nullable(Float64) │
│ poll │ Nullable(Float64) │
│ kids │ Array(Nullable(Float64)) │
│ url │ Nullable(String) │
│ score │ Nullable(Float64) │
│ title │ Nullable(String) │
│ parts │ Array(Nullable(Float64)) │
│ descendants │ Nullable(Float64) │
└─────────────┴──────────────────────────┴
이 테이블에 데이터를 삽입하려면 INSERT INTO, SELECT 명령을 사용하세요. url 함수와 함께 사용하면 데이터가 URL에서 직접 스트리밍됩니다:
INSERT INTO hackernews SELECT *
FROM url('https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.csv.gz', 'CSVWithNames')
단일 명령으로 ClickHouse에 2800만 행을 성공적으로 삽입했습니다!
- 데이터 탐색
다음 쿼리를 실행해 Hacker News 스토리와 특정 컬럼을 샘플링합니다: Query
SELECT
id,
title,
type,
by,
time,
url,
score
FROM hackernews
WHERE type = 'story'
LIMIT 3
FORMAT Vertical
Response
Row 1:
──────
id: 2596866
title: WordPress capture users last login date and time
type: story
by: wpsnipp
time: 1306685252
url: http://wpsnipp.com/index.php/date/capture-users-last-login-date-and-time/
score: 1
...
(원문에는 3개의 행 결과가 포함되어 있습니다.)
스키마 추론은 초기 데이터 탐색에는 훌륭한 도구이지만 "best effort"이며 데이터에 대한 최적 스키마를 정의하는 장기적인 대안은 아닙니다.
- 스키마 정의
명백한 즉시 최적화는 각 필드에 타입을 정의하는 것입니다. time 필드를 DateTime 타입으로 선언하는 것 외에, 기존 데이터셋을 삭제한 뒤 아래 각 필드에 적절한 타입을 정의합니다. ClickHouse에서 데이터의 기본 키 id는 ORDER BY 절로 정의됩니다. 적절한 타입을 선택하고 ORDER BY 절에 포함할 컬럼을 정하면 쿼리 속도와 압축을 개선하는 데 도움이 됩니다. 아래 쿼리를 실행해 이전 스키마를 삭제하고 개선된 스키마를 만듭니다:
Query
DROP TABLE IF EXISTS hackernews;
CREATE TABLE hackernews
(
`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
ORDER BY id
최적화된 스키마로 이제 로컬 파일시스템에서 데이터를 삽입할 수 있습니다. 다시 clickhouse-client를 사용해 명시적인 INSERT INTO와 함께 INFILE 절로 파일을 삽입하세요.
Query
INSERT INTO hackernews FROM INFILE '/data/hacknernews.csv.gz' FORMAT CSVWithNames
- 샘플 쿼리 실행
몇 가지 샘플 쿼리를 제시해 직접 쿼리를 작성할 영감을 드립니다.
Hacker News에서 "ClickHouse"라는 주제가 얼마나 널리 퍼져 있나?
score 필드는 스토리의 인기 지표를 제공하고, id 필드와 || 연결 연산자를 사용해 원본 게시물 링크를 만들 수 있습니다.
Query
SELECT
time,
score,
descendants,
title,
url,
'https://news.ycombinator.com/item?id=' || toString(id) AS hn_url
FROM hackernews
WHERE (type = 'story') AND (title ILIKE '%ClickHouse%')
ORDER BY score DESC
LIMIT 5 FORMAT Vertical
(원문에는 상위 5개 결과가 포함되어 있습니다.)
ClickHouse가 시간이 지남에 따라 더 많은 소음을 발생시키고 있나요? 여기서 time 필드를 DateTime으로 정의한 유용성이 드러나는데, 적절한 데이터 타입을 사용하면 toYYYYMM() 함수를 사용할 수 있습니다:
Query
SELECT
toYYYYMM(time) AS monthYear,
bar(count(), 0, 120, 20)
FROM hackernews
WHERE (type IN ('story', 'comment')) AND ((title ILIKE '%ClickHouse%') OR (text ILIKE '%ClickHouse%'))
GROUP BY monthYear
ORDER BY monthYear ASC
(원문에는 월별 막대그래프 결과가 포함되어 있습니다.)
시간이 지남에 따라 "ClickHouse"의 인기가 커지고 있는 것 같습니다.
ClickHouse 관련 기사에 가장 많이 댓글을 다는 사람은 누구인가?
Query
SELECT
by,
count() AS comments
FROM hackernews
WHERE (type IN ('story', 'comment')) AND ((title ILIKE '%ClickHouse%') OR (text ILIKE '%ClickHouse%'))
GROUP BY by
ORDER BY comments DESC
LIMIT 5
Response
┌─by──────────┬─comments─┐
│ hodgesrm │ 78 │
│ zX41ZdbW │ 45 │
│ manigandham │ 39 │
│ pachico │ 35 │
│ valyala │ 27 │
└─────────────┴──────────┘
어떤 댓글이 가장 많은 관심을 끌까?
Query
SELECT
by,
sum(score) AS total_score,
sum(length(kids)) AS total_sub_comments
FROM hackernews
WHERE (type IN ('story', 'comment')) AND ((title ILIKE '%ClickHouse%') OR (text ILIKE '%ClickHouse%'))
GROUP BY by
ORDER BY total_score DESC
LIMIT 5
Response
┌─by───────┬─total_score─┬─total_sub_comments─┐
│ zX41ZdbW │ 571 │ 50 │
│ jetter │ 386 │ 30 │
│ hodgesrm │ 312 │ 50 │
│ mechmind │ 243 │ 16 │
│ tosh │ 198 │ 12 │
└──────────┴─────────────┴────────────────────┘
Parquet
ClickHouse의 강점 중 하나는 수많은 형식을 처리할 수 있다는 것입니다. CSV는 상당히 이상적인 사용 사례이며, 데이터 교환에는 가장 효율적이지는 않습니다.
다음으로 효율적인 컬럼 지향 형식인 Parquet 파일에서 데이터를 로드할 것입니다.
Parquet에는 ClickHouse가 존중해야 하는 최소한의 타입이 있으며, 이 타입 정보는 형식 자체에 인코딩됩니다. Parquet 파일에서의 타입 추론은 CSV 파일의 스키마와 항상 약간 다를 것입니다.
- 데이터 삽입
다음 쿼리를 실행해 같은 데이터를 Parquet 형식으로 읽습니다. 다시 url 함수로 원격 데이터를 읽습니다:
DROP TABLE IF EXISTS hackernews;
CREATE TABLE hackernews
ENGINE = MergeTree
ORDER BY id
SETTINGS allow_nullable_key = 1 EMPTY AS
SELECT *
FROM url('https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.parquet', 'Parquet');
INSERT INTO hackernews SELECT *
FROM url('https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.parquet', 'Parquet');
Parquet의 Null 키: 추론된 스키마가 컬럼을 Nullable로 만들므로, 이 데이터셋에 null id가 없더라도 allow_nullable_key가 필요합니다.
추론된 스키마를 보려면 다음 명령을 실행하세요: Query
DESCRIBE TABLE hackernews;
Response
┌─name────────┬─type───────────────────┬
│ id │ Nullable(Int64) │
│ deleted │ Nullable(UInt8) │
│ type │ Nullable(String) │
│ by │ Nullable(String) │
│ time │ Nullable(Int64) │
│ text │ Nullable(String) │
│ dead │ Nullable(UInt8) │
│ parent │ Nullable(Int64) │
│ poll │ Nullable(Int64) │
│ kids │ Array(Nullable(Int64)) │
│ url │ Nullable(String) │
│ score │ Nullable(Int32) │
│ title │ Nullable(String) │
│ parts │ Array(Nullable(Int64)) │
│ descendants │ Nullable(Int32) │
└─────────────┴────────────────────────┴
남은 단계는 author, comment 같은 더 명확한 컬럼 이름을 사용하므로, 수동으로 지정한 스키마로 계속 진행합니다. 먼저 추론된 테이블을 삭제한 뒤, 테이블을 만들고 공용 S3 버킷에서 직접 데이터를 삽입합니다:
DROP TABLE IF EXISTS hackernews;
CREATE TABLE hackernews
(
`id` UInt64,
`deleted` UInt8,
`type` String,
`author` String,
`timestamp` DateTime,
`comment` String,
`dead` UInt8,
`parent` UInt64,
`poll` UInt64,
`children` Array(UInt32),
`url` String,
`score` UInt32,
`title` String,
`parts` Array(UInt32),
`descendants` UInt32
)
ENGINE = MergeTree
ORDER BY (type, author);
INSERT INTO hackernews
SELECT * FROM s3(
'https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.parquet',
NOSIGN,
'Parquet',
'id UInt64,
deleted UInt8,
type String,
by String,
time DateTime,
text String,
dead UInt8,
parent UInt64,
poll UInt64,
kids Array(UInt32),
url String,
score UInt32,
title String,
parts Array(UInt32),
descendants UInt32');
- 텍스트 인덱스를 추가해 검색 가속화
"ClickHouse"를 언급하는 댓글이 몇 개인지 알아보려면 다음 쿼리를 실행하세요: Query
SELECT count(*)
FROM hackernews
WHERE hasAnyTokens(lower(comment), 'clickhouse');
Response
┌─count()─┐
│ 1145 │
└─────────┘
1 row in set. Elapsed: 3.251 sec. Processed 28.74 million rows, 9.60 GB (8.84 million rows/s., 2.95 GB/s.)
다음으로 comment 컬럼에 텍스트 인덱스를 만들어 이 쿼리를 가속화합니다. 텍스트 인덱스는 토큰을 해당 토큰을 포함하는 행에 매핑하는 역인덱스(inverted index)를 사용합니다. splitByNonAlpha 토크나이저는 비알파벳 문자에서 텍스트를 분리합니다. 인덱스와 쿼리는 소문자 검색어와 함께 lower(comment)를 사용하므로 매칭이 대소문자를 구분하지 않습니다. 쿼리 표현식은 인덱스된 표현식과 일치해야 합니다. 인덱스를 만들려면 다음 명령을 실행하세요:
ALTER TABLE hackernews
ADD INDEX comment_idx lower(comment)
TYPE text(tokenizer = splitByNonAlpha);
ALTER TABLE hackernews
MATERIALIZE INDEX comment_idx
SETTINGS mutations_sync = 2;
실체화(materialization)는 기존 데이터에 대한 인덱스를 구축합니다. mutations_sync 설정은 실체화가 끝날 때까지 기다립니다. 인덱스 정의는 system.data_skipping_indices 테이블에서 확인할 수 있습니다. 인덱스가 실체화된 후 같은 쿼리를 다시 실행하세요:
Query
SELECT count(*)
FROM hackernews
WHERE hasAnyTokens(lower(comment), 'clickhouse');
Response
┌─count()─┐
│ 1145 │
└─────────┘
1 row in set. Elapsed: 0.019 sec. Processed 4.48 million rows, 4.48 MB (232.23 million rows/s., 232.23 MB/s.)
결과는 동일하게 유지되는데, 그 이유는 인덱스가 어떤 행이 매칭되는지가 아니라 ClickHouse가 매칭 행을 찾는 방식을 바꾸기 때문입니다. 인덱스된 쿼리는 훨씬 적은 데이터를 처리하고 훨씬 빠르게 완료됩니다. ClickHouse가 인덱스를 적용할 계획인지 확인하려면 EXPLAIN을 사용하세요:
Query
EXPLAIN indexes = 1
SELECT count(*)
FROM hackernews
WHERE hasAnyTokens(lower(comment), 'clickhouse');
Response
Output: count()
Aggregating
│ Keys:
│ Aggregates: count()
│ Skip merging: 0
└──Filter
│ Filter column: __text_index_comment_idx_hasAnyTokens_7b9491bd22343c822f64c95a9bda20f9
└──ReadFromMergeTree (default.hackernews)
Read type: Default
Parts: 4 | Granules: 547
Output: __text_index_comment_idx_hasAnyTokens_7b9491bd22343c822f64c95a9bda20f9
Indexes:
PrimaryKey
Condition: true
Parts: 4/4
Granules: 3527/3527
Skip
Name: comment_idx
Description: text GRANULARITY 100000000
Condition: (mode: Any; tokens: ["clickhouse"])
Parts: 4/4
Granules: 547/3527
Ranges: 437
comment_idx 항목은 ClickHouse가 텍스트 인덱스를 적용할 계획임을 보여줍니다. 이 예제에서 계획은 3527개 중 547개 granule을 선택해 검사하는 데이터 양을 크게 줄입니다. 또한 여러 토큰 중 일부 또는 전부를 검색할 수 있습니다. 이 함수들은 인덱스 토크나이저가 생성한 완전한 토큰을 매칭합니다. 적어도 하나의 토큰이 매칭되어야 할 때 hasAnyTokens를 사용하세요:
Query
SELECT count(*)
FROM hackernews
WHERE hasAnyTokens(lower(comment), 'oltp olap');
Response
┌─count()─┐
│ 2020 │
└─────────┘
모든 토큰이 어떤 순서로든 매칭되어야 할 때 hasAllTokens를 사용하세요:
Query
SELECT count(*)
FROM hackernews
WHERE hasAllTokens(lower(comment), 'avx sve');
Response
┌─count()─┐
│ 22 │
└─────────┘