YouTube 싫어요(dislikes) 데이터셋
YouTube 싫어요(dislikes) 데이터셋 (YouTube dataset of dislikes)
2021년 11월 YouTube가 공개 싫어요(dislike) 수를 제거하기 전까지의 영상 데이터를 담은 데이터셋입니다. 45.5억 개가 넘는 레코드로, S3의 JSON 파일에서 s3cluster 테이블 함수로 ClickHouse Cloud에 스트리밍해 넣는 과정을 보여줍니다.
출처: 문서
본문
2021년 11월 YouTube는 모든 영상에서 공개 싫어요 수를 제거했습니다. 크리에이터는 여전히 싫어요 수를 볼 수 있지만, 시청자는 영상이 받은 좋아요 수만 볼 수 있습니다.
이 데이터셋은 45.5억 개가 넘는 레코드를 가지므로, 리소스가 그 규모를 처리할 수 없다면 아래 명령을 그대로 복사·붙여넣기 할 때 주의하세요. 아래 명령들은 ClickHouse Cloud의 프로덕션 인스턴스에서 실행되었습니다.
데이터는 JSON 형식이며 archive.org에서 내려받을 수 있습니다. 우리는 이 같은 데이터를 S3에서도 제공하므로 ClickHouse Cloud 인스턴스에 더 효율적으로 내려받을 수 있습니다.
ClickHouse Cloud에 테이블을 만들고 데이터를 삽입하는 단계는 다음과 같습니다.
아래 단계는 로컬 ClickHouse 설치에서도 쉽게 동작합니다. 유일한 변경은 s3cluster 대신 s3 함수를 사용하는 것입니다(클러스터를 구성했다면 default를 클러스터 이름으로 바꾸세요).
단계별 지침 (Step-by-step instructions)
- 데이터 탐색
데이터가 어떻게 생겼는지 봅시다. s3cluster 테이블 함수는 테이블을 반환하므로 결과를 DESCRIBE할 수 있습니다:
DESCRIBE s3(
'https://clickhouse-public-datasets.s3.amazonaws.com/youtube/original/files/*.zst',
'JSONLines'
);
ClickHouse가 JSON 파일에서 다음 스키마를 추론합니다:
┌─name────────────────┬─type───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Nullable(String) │ │ │ │ │ │
│ fetch_date │ Nullable(String) │ │ │ │ │ │
│ upload_date │ Nullable(String) │ │ │ │ │ │
│ title │ Nullable(String) │ │ │ │ │ │
│ uploader_id │ Nullable(String) │ │ │ │ │ │
│ uploader │ Nullable(String) │ │ │ │ │ │
│ uploader_sub_count │ Nullable(Int64) │ │ │ │ │ │
│ is_age_limit │ Nullable(Bool) │ │ │ │ │ │
│ view_count │ Nullable(Int64) │ │ │ │ │ │
│ like_count │ Nullable(Int64) │ │ │ │ │ │
│ dislike_count │ Nullable(Int64) │ │ │ │ │ │
│ is_crawlable │ Nullable(Bool) │ │ │ │ │ │
│ is_live_content │ Nullable(Bool) │ │ │ │ │ │
│ has_subtitles │ Nullable(Bool) │ │ │ │ │ │
│ is_ads_enabled │ Nullable(Bool) │ │ │ │ │ │
│ is_comments_enabled │ Nullable(Bool) │ │ │ │ │ │
│ description │ Nullable(String) │ │ │ │ │ │
│ rich_metadata │ Array(Tuple(call Nullable(String), content Nullable(String), subtitle Nullable(String), title Nullable(String), url Nullable(String))) │ │ │ │ │ │
│ super_titles │ Array(Tuple(text Nullable(String), url Nullable(String))) │ │ │ │ │ │
│ uploader_badges │ Nullable(String) │ │ │ │ │ │
│ video_badges │ Nullable(String) │ │ │ │ │ │
└─────────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
- 테이블 생성
추론된 스키마를 기반으로 데이터 타입을 정리하고 기본 키를 추가했습니다. 다음 테이블을 정의하세요:
CREATE TABLE youtube
(
`id` String,
`fetch_date` DateTime,
`upload_date_str` String,
`upload_date` Date,
`title` String,
`uploader_id` String,
`uploader` String,
`uploader_sub_count` Int64,
`is_age_limit` Bool,
`view_count` Int64,
`like_count` Int64,
`dislike_count` Int64,
`is_crawlable` Bool,
`has_subtitles` Bool,
`is_ads_enabled` Bool,
`is_comments_enabled` Bool,
`description` String,
`rich_metadata` Array(Tuple(call String, content String, subtitle String, title String, url String)),
`super_titles` Array(Tuple(text String, url String)),
`uploader_badges` String,
`video_badges` String
)
ENGINE = MergeTree
ORDER BY (uploader, upload_date)
- 데이터 삽입
다음 명령은 S3 파일의 레코드를 youtube 테이블로 스트리밍합니다.
이것은 많은 데이터인 46.5억 행을 삽입합니다. 전체 데이터셋을 원하지 않으면 원하는 행 수와 함께 LIMIT 절을 추가하세요.
INSERT INTO youtube
SETTINGS input_format_null_as_default = 1
SELECT
id,
parseDateTimeBestEffortUSOrZero(toString(fetch_date)) AS fetch_date,
upload_date AS upload_date_str,
toDate(parseDateTimeBestEffortUSOrZero(upload_date::String)) AS upload_date,
ifNull(title, '') AS title,
uploader_id,
ifNull(uploader, '') AS uploader,
uploader_sub_count,
is_age_limit,
view_count,
like_count,
dislike_count,
is_crawlable,
has_subtitles,
is_ads_enabled,
is_comments_enabled,
ifNull(description, '') AS description,
rich_metadata,
super_titles,
ifNull(uploader_badges, '') AS uploader_badges,
ifNull(video_badges, '') AS video_badges
FROM s3(
'https://clickhouse-public-datasets.s3.amazonaws.com/youtube/original/files/*.zst',
'JSONLines'
)
우리의 INSERT 명령에 대한 몇 가지 참고사항:
parseDateTimeBestEffortUSOrZero함수는 들어오는 날짜 필드가 올바른 형식이 아닐 수 있을 때 유용합니다.fetch_date가 제대로 파싱되지 않으면0으로 설정됩니다.upload_date컬럼은 유효한 날짜를 포함하지만 "4 hours ago" 같은 문자열도 포함합니다 — 확실히 유효한 날짜는 아닙니다. 우리는 원본 값을upload_date_str에 저장하고toDate(parseDateTimeBestEffortUSOrZero(upload_date::String))로 파싱을 시도하기로 했습니다. 파싱이 실패하면0을 얻습니다.- 테이블에서
NULL값을 피하기 위해ifNull을 사용했습니다. 들어오는 값이NULL이면ifNull함수가 값을 빈 문자열로 설정합니다.
- 행 수 세기
ClickHouse Cloud의 SQL Console(또는 새 clickhouse-client 창)에서 새 탭을 열고 수가 증가하는 것을 지켜보세요. 서버 리소스에 따라 46.5억 행을 삽입하는 데 꽤 오래 걸릴 것입니다. (설정을 전혀 조정하지 않으면 약 4.5시간이 걸립니다.)
SELECT formatReadableQuantity(count())
FROM youtube
┌─formatReadableQuantity(count())─┐
│ 4.56 billion │
└─────────────────────────────────┘
- 데이터 탐색
데이터가 삽입되면 좋아하는 영상이나 채널의 싫어요 수를 세어 보세요. ClickHouse가 업로드한 영상이 몇 개인지 봅시다:
SELECT count()
FROM youtube
WHERE uploader = 'ClickHouse';
┌─count()─┐
│ 84 │
└─────────┘
1 row in set. Elapsed: 0.570 sec. Processed 237.57 thousand rows, 5.77 MB (416.54 thousand rows/s., 10.12 MB/s.)
위 쿼리는 기본 키의 첫 번째 컬럼으로 uploader를 선택했기 때문에 매우 빠르게 실행됩니다 — 그래서 237k 행만 처리했습니다.
ClickHouse 영상의 좋아요·싫어요를 살펴봅시다:
SELECT
title,
like_count,
dislike_count
FROM youtube
WHERE uploader = 'ClickHouse'
ORDER BY dislike_count DESC;
응답은 다음과 같습니다:
┌─title────────────────────────────────────────────────────────────────────────────────────────────────┬─like_count─┬─dislike_count─┐
│ ClickHouse v21.11 Release Webinar │ 52 │ 3 │
│ ClickHouse Introduction │ 97 │ 3 │
│ Casa Modelo Algarve │ 180 │ 3 │
│ Профайлер запросов: трудный путь │ 33 │ 3 │
│ ClickHouse в Курсометре │ 4 │ 2 │
│ 10 Good Reasons to Use ClickHouse │ 27 │ 2 │
...
└──────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────┴───────────────┘
84 rows in set. Elapsed: 0.013 sec. Processed 155.65 thousand rows, 16.94 MB (11.96 million rows/s., 1.30 GB/s.)
title 또는 description 필드에 ClickHouse가 있는 영상 검색은 다음과 같습니다:
SELECT
view_count,
like_count,
dislike_count,
concat('https://youtu.be/', id) AS url,
title
FROM youtube
WHERE (title ILIKE '%ClickHouse%') OR (description ILIKE '%ClickHouse%')
ORDER BY
like_count DESC,
view_count DESC;
이 쿼리는 모든 행을 처리하고 두 개의 문자열 컬럼도 파싱해야 합니다. 그래도 4.15M rows/second의 훌륭한 성능을 얻습니다:
1174 rows in set. Elapsed: 1099.368 sec. Processed 4.56 billion rows, 1.98 TB (4.15 million rows/s., 1.80 GB/s.)
결과는 다음과 같습니다:
┌─view_count─┬─like_count─┬─dislike_count─┬─url──────────────────────────┬─title──────────────────────────────────────────────────────────────────────────────────────────────────┐
│ 1919 │ 63 │ 1 │ https://youtu.be/b9MeoOtAivQ │ ClickHouse v21.10 Release Webinar │
│ 8710 │ 62 │ 4 │ https://youtu.be/PeV1mC2z--M │ What is JDBC DriverManager? | JDBC │
│ 3534 │ 62 │ 1 │ https://youtu.be/8nWRhK9gw10 │ CLICKHOUSE - Arquitetura Modular │
└───────────┴─────────────┴───────────────┴──────────────────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────┘
질문 (Questions)
누군가 댓글을 비활성화하면 실제로 좋아요나 싫어요를 누를 가능성이 낮아질까?
댓글이 비활성화되면 사람들이 영상에 대한 감정을 표현하기 위해 좋아요나 싫어요를 더 누를 가능성이 있을까요?
SELECT
concat('< ', formatReadableQuantity(view_range)) AS views,
is_comments_enabled,
total_clicks / num_views AS prob_like_dislike
FROM
(
SELECT
is_comments_enabled,
power(10, CEILING(log10(view_count + 1))) AS view_range,
sum(like_count + dislike_count) AS total_clicks,
sum(view_count) AS num_views
FROM youtube
GROUP BY
view_range,
is_comments_enabled
) WHERE view_range > 1
ORDER BY
is_comments_enabled ASC,
num_views ASC;
┌─views─────────────┬─is_comments_enabled─┬────prob_like_dislike─┐
│ < 10.00 │ false │ 0.08224180712685371 │
│ < 100.00 │ false │ 0.06346337759167248 │
│ < 1.00 thousand │ false │ 0.03201883652987105 │
│ < 10.00 billion │ false │ 0.004555639481829971 │
│ < 1.00 billion │ false │ 0.004761811192464957 │
│ < 10.00 │ true │ 0.09819517478134059 │
│ ...（원문에 22개 결과 전체 포함）
└───────────────────┴─────────────────────┴──────────────────────┘
22 rows in set. Elapsed: 8.460 sec. Processed 4.56 billion rows, 77.48 GB (538.73 million rows/s., 9.16 GB/s.)
댓글을 활성화하는 것은 더 높은 참여율과 상관관계가 있는 것 같습니다.
영상 수가 시간에 따라 어떻게 변하는가 — 주목할 만한 사건?
SELECT
toStartOfMonth(toDateTime(upload_date)) AS month,
uniq(uploader_id) AS uploaders,
count() AS num_videos,
sum(view_count) AS view_count
FROM youtube
GROUP BY month
ORDER BY month ASC;
┌──────month─┬─uploaders─┬─num_videos─┬───view_count─┐
│ 2005-04-01 │ 5 │ 6 │ 213597737 │
│ 2005-05-01 │ 6 │ 9 │ 2944005 │
│ 2005-06-01 │ 165 │ 351 │ 18624981 │
│ ...（원문에 여러 월별 결과 포함）
└────────────┴───────────┴────────────┴───────────────┘
시간에 따른 더 많은 자막과 시점
음성 인식의 발전으로 영상에 자막을 만드는 것이 그 어느 때보다 쉬워졌고, YouTube는 2009년 후반에 자동 캡션을 추가했습니다 — 그때 점프가 있었나요?
SELECT
toStartOfMonth(upload_date) AS month,
countIf(has_subtitles) / count() AS percent_subtitles,
percent_subtitles - any(percent_subtitles) OVER (
ORDER BY month ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
) AS previous
FROM youtube
GROUP BY month
ORDER BY month ASC;
┌──────month─┬───percent_subtitles─┬────────────────previous─┐
│ 2015-01-01 │ 0.2652653881082824 │ 0.2652653881082824 │
│ 2015-02-01 │ 0.3147556050309162 │ 0.049490216922633834 │
│ ...（원문에 여러 월별 결과 포함）
└────────────┴─────────────────────┴────────────────────────┘
데이터 결과는 2009년에 급증을 보여줍니다. 당시 YouTube는 다른 사람의 영상에 캡션을 업로드할 수 있게 했던 커뮤니티 캡션 기능을 제거하고 있었습니다. 이것은 크리에이터들이 난청·청각 장애 시청자를 위해 영상에 캡션을 추가하도록 하는 매우 성공적인 캠페인을 촉발했습니다.
시간에 따른 상위 업로더
WITH uploaders AS
(
SELECT uploader
FROM youtube
GROUP BY uploader
ORDER BY sum(view_count) DESC
LIMIT 10
)
SELECT
month,
uploader,
sum(view_count) AS total_views,
avg(dislike_count / like_count) AS like_to_dislike_ratio
FROM youtube
WHERE uploader IN (uploaders)
GROUP BY
toStartOfMonth(upload_date) AS month,
uploader
ORDER BY
month ASC,
total_views DESC;
┌──────month─┬─uploader───────────────────┬─total_views─┬─like_to_dislike_ratio─┐
│ 1970-01-01 │ T-Series │ 10957099 │ 0.022784656361208206 │
│ 1970-01-01 │ Ryan's World │ 0 │ 0.003035559410234172 │
│ 2006-09-01 │ Cocomelon - Nursery Rhymes │ 256406497 │ 0.7005566715978622 │
│ ...（원문에 여러 결과 포함）
└────────────┴────────────────────────────┴─────────────┴───────────────────────┘
좋아요 비율은 조회수가 올라감에 따라 어떻게 변할까?
SELECT
concat('< ', formatReadableQuantity(view_range)) AS view_range,
is_comments_enabled,
round(like_ratio, 2) AS like_ratio
FROM
(
SELECT
power(10, CEILING(log10(view_count + 1))) AS view_range,
is_comments_enabled,
avg(like_count / dislike_count) AS like_ratio
FROM youtube WHERE dislike_count > 0
GROUP BY
view_range,
is_comments_enabled HAVING view_range > 1
ORDER BY
view_range ASC,
is_comments_enabled ASC
);
┌─view_range────────┬─is_comments_enabled─┬─like_ratio─┐
│ < 10.00 │ false │ 0.66 │
│ < 10.00 │ true │ 0.66 │
│ < 100.00 │ false │ 3 │
│ < 100.00 │ true │ 3.95 │
│ ...（원문에 20개 결과 전체 포함）
└───────────────────┴─────────────────────┴────────────┘
조회수는 어떻게 분포되어 있을까?
SELECT
labels AS percentile,
round(quantiles) AS views
FROM
(
SELECT
quantiles(0.999, 0.99, 0.95, 0.9, 0.8, 0.7, 0.6, 0.5, 0.4, 0.3, 0.2, 0.1)(view_count) AS quantiles,
['99.9th', '99th', '95th', '90th', '80th', '70th','60th', '50th', '40th', '30th', '20th', '10th'] AS labels
FROM youtube
)
ARRAY JOIN
quantiles,
labels;
┌─percentile─┬───views─┐
│ 99.9th │ 1216624 │
│ 99th │ 143519 │
│ 95th │ 13542 │
│ 90th │ 4054 │
│ 80th │ 950 │
│ 70th │ 363 │
│ 60th │ 177 │
│ 50th │ 97 │
│ 40th │ 57 │
│ 30th │ 32 │
│ 20th │ 16 │
│ 10th │ 6 │
└────────────┴─────────┘