メインコンテンツまでスキップ
メインコンテンツまでスキップ

Analyzing Stack Overflow data with ClickHouse

このデータセットには、Stack Overflowで発生したすべての Posts, Users, Votes, Comments, Badges, PostHistory, 及び PostLinks が含まれています。

ユーザーは、2024年4月までのすべての投稿を含む事前準備されたParquetバージョンをダウンロードするか、最新のデータをXML形式でダウンロードしてロードすることができます。Stack Overflowはこのデータを定期的に更新しており、歴史的には3か月ごとに提供しています。

以下の図は、Parquet形式の利用可能なテーブルのスキーマを示しています。

このデータのスキーマの説明はこちらで見つけることができます。

事前準備されたデータ​

2024年4月時点の最新のParquet形式のデータのコピーを提供しています。行数（6000万件の投稿）に関してはClickHouseには小さいですが、このデータセットは重要なテキストの量と大きなStringカラムを含んでいます。

以下の時間は、eu-west-2にある96 GiB、24 vCPUのClickHouse Cloudクラスタのものです。データセットはeu-west-3に位置しています。

投稿​

投稿は年ごとにも利用でき、例えば https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/2020.parquet で確認できます。

投票​

投票は年ごとにも利用でき、例えば https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/votes/2020.parquet で確認できます。

コメント​

コメントは年ごとにも利用でき、例えば https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/comments/2020.parquet で確認できます。

ユーザー​

バッジ​

PostHistory​

オリジナルデータセット​

オリジナルデータセットは、https://archive.org/download/stackexchange で圧縮（7zip）されたXML形式で利用可能で、ファイルのプレフィックスは stackoverflow.com* です。

ダウンロード​

これらのファイルは最大で35GBあり、インターネット接続によってはダウンロードに約30分かかることがあります- ダウンロードサーバーは約20MB/secで制限しています。

JSONへの変換​

執筆時点で、ClickHouseはXMLを入力形式としてネイティブにサポートしていません。データをClickHouseにロードするには、まずNDJSONに変換します。

XMLをJSONに変換するには、xqというLinuxツールをお勧めします。これはXMLドキュメント用のシンプルなjqラッパーです。

xqとjqをインストールします：

上記のファイルには次の手順が適用されます。stackoverflow.com-Posts.7zファイルを例として使用します。必要に応じて変更してください。

p7zipを使用してファイルを抽出します。これにより、単一のxmlファイル（この場合はPosts.xml）が生成されます。

ファイルは約4.5倍圧縮されています。圧縮時22GBの投稿ファイルは、約97GBの展開されたサイズが必要です。

次に、xmlファイルを10000行ずつ分割して新しいファイルを作成します。

上記を実行した後、各ファイルに10000行が含まれる一連のファイルが作成されます。これにより、次のコマンドのメモリオーバーヘッドが過度になることがないようにします（xmlからJSONへの変換はメモリ内で行われます）。

上記のコマンドを実行すると、単一のposts.jsonファイルが生成されます。

次のコマンドを使用してClickHouseにロードします。スキーマはposts.jsonファイルのために指定されています。これはターゲットテーブルに合わせてデータ型ごとに調整する必要があります。

例のクエリ​

いくつかの簡単な質問で始めましょう。

最も回答数が多いユーザー (アクティブアカウント)​

アカウントにはUserIdが必要です。

最も物議を醸した投稿​

著作権表示​

Stack Overflowが提供しているこのデータに感謝し、cc-by-sa 4.0ライセンスの下でその努力と元データの出所であるhttps://archive.org/details/stackexchangeを認識します。