YouTubeデータセットの嫌い
2021年11月、YouTubeはすべての動画から公開された嫌いの数を削除しました。クリエイターは依然として嫌いの数を確認できますが、視聴者は動画が受け取ったいいねの数のみを見ることができます。
データセットには45億以上のレコードが含まれているため、リソースがその量を処理できない限り、以下のコマンドをコピー＆ペーストする際は注意してください。以下のコマンドは、ClickHouse Cloudのプロダクションインスタンスで実行されました。
データはJSON形式で、archive.orgからダウンロードできます。同じデータをS3に用意したので、ClickHouse Cloudインスタンスにより効率的にダウンロードできます。
以下は、ClickHouse Cloudにテーブルを作成し、データを挿入する手順です。
以下の手順は、ClickHouseのローカルインストールでも簡単に機能します。唯一の変更点は、s3clusterの代わりにs3関数を使用することであり、クラスターが構成されている場合はdefaultをクラスターの名前に変更してください。
ステップバイステップの手順
- データがどのように見えるか確認しましょう。
s3clusterテーブル関数はテーブルを返すため、結果をDESCRIBEできます。
ClickHouseはJSONファイルから以下のスキーマを推測します：
- 推測されたスキーマに基づいて、データ型を整理し、主キーを追加しました。次のテーブルを定義します：
- 次のコマンドはS3ファイルからレコードを
youtubeテーブルにストリームします。
これにより、多くのデータ - 46.5億行が挿入されます。データセット全体が必要ない場合は、LIMIT句を追加して希望の行数に制限できます。
INSERTコマンドに関するいくつかのコメント：
parseDateTimeBestEffortUSOrZero関数は、受信する日付フィールドが正しい形式でない場合に便利です。fetch_dateが正しく解析されない場合、0に設定されます。upload_dateカラムには有効な日付が含まれますが、「4時間前」などの文字列も含まれています - これは有効な日付ではありません。元の値をupload_date_strに保存し、toDate(parseDateTimeBestEffortUSOrZero(upload_date::String))で解析を試みることにしました。解析が失敗した場合は、0を取得します。ifNullを使用して、テーブル内でNULL値が取得されないようにしました。受信した値がNULLの場合、ifNull関数は値を空文字列に設定します。
- ClickHouse CloudのSQLコンソールで新しいタブを開く（または新しい
clickhouse-clientウィンドウを開く）と、カウントが増加するのを見ることができます。46.5億行を挿入するには、サーバーリソースに応じてしばらく時間がかかります。（設定の調整なしでは、約4.5時間かかります。）
- データが挿入されたら、お気に入りの動画やチャンネルの嫌いの数をカウントしてみましょう。ClickHouseがアップロードした動画の数を見てみましょう：
上記のクエリがこれほど素早く実行されるのは、uploaderを主キーの最初のカラムとして選択したためで、237k行しか処理する必要がなかったからです。
- ClickHouseの動画のいいねと嫌いを見てみましょう：
レスポンスは次のようになります：
titleまたはdescriptionフィールドにClickHouseが含まれる動画を検索します：
このクエリはすべての行を処理し、2つの文字列のカラムを解析する必要があります。それでも、毎秒4.15M行という良好なパフォーマンスが得られます：
結果は次のようになります：
質問
誰かがコメントを無効にすると、それが実際にいいねや嫌いをクリックする可能性を下げますか？
コメントが無効になると、人々はその動画についての感情を表現するためにいいねや嫌いをクリックする可能性が高くなるのでしょうか？
コメントを有効にすることは、エンゲージメントの率が高くなることと相関関係があるようです。
動画の数は時間とともにどのように変化しますか - 注目すべきイベントは？
コロナ周りのアップローダーの急増が目立ちます。こちらを参照してください。
時間とともに字幕が増えているでしょうか、いつですか？
音声認識の進展により、自動キャプションが2009年末に追加されたことで動画への字幕の作成がかつてないほど容易になりました - その際に急増があったのでしょうか？
データ結果は2009年の急増を示しています。その時、YouTubeは他の人の動画に字幕をアップロードできるコミュニティキャプション機能を削除しました。 これにより、聴覚障害者や耳の不自由な視聴者のために、クリエイターが自分の動画にキャプションを追加するように促す非常に成功したキャンペーンが行われました。