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

ClickHouseでStack Overflowデータを分析する

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

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

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

Stack Overflowスキーマ

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

事前準備されたデータ​

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

以下のタイミングは、eu-west-2にある96 GiB、24 vCPUのClickHouse Cloudクラスタ用です。データセットはeu-west-3にあります。

Posts​

投稿は年ごとにアクセスすることもできます。例えば https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/2020.parquet

Votes​

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

Comments​

コメントも年ごとに利用可能です。例えば https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/2020.parquet

Users​

Badges​

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の投稿ファイルは、展開された状態で約97Gを必要とします。

次のコマンドは、xmlファイルを10000行ごとに分割します。

上記を実行した後、ユーザーはそれぞれ10000行のファイルを持つセットを得ることができます。これにより、次のコマンドのメモリオーバーヘッドが過剰にならないようになります（xmlからJSONへの変換はメモリ内で行われます）。

上記のコマンドは、単一のposts.jsonファイルを生成します。

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

サンプルクエリ​

いくつかの簡単なクエリを示して、スタートを切りましょう。

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

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

最も論争を呼んだ投稿​

著作権表示​

Stack Overflowがこのデータをcc-by-sa 4.0ライセンスの下で提供していることに感謝し、彼らの努力とデータのオリジナルソースをhttps://archive.org/details/stackexchangeで認識します。