👊

DuckDBでBluesky Jetstreamをクエリする

2024/11/25に公開

SQL

2024/12/04追記：説明不要なぐらい使いやすくなって再公開されました。よかったですね。

https://skyfirehose.com/

時間があったら記事を書き直します。

2024/11/29追記：最近Blueskyの100万件の投稿を用いたデータセットをHugging Face上に公開した人が盛大に炎上したせいかskyfirehose.comがなかったことになっていました。

この記事は記念に残しておきます。

Hacker NewsでDuckDBについて調べていたところ、skyfirehose.comというBluesky Jetstream with DuckDBなツールを発見したので試してみます。

https://skyfirehose.com/

作者はTobias Müller-sanです。

DuckDBが必要なので、何らかの方法でインストールします。

https://duckdb.org/docs/installation/

$ curl -sLO https://github.com/duckdb/duckdb/releases/latest/download/duckdb_cli-linux-amd64.zip
$ unzip -q duckdb_cli-linux-amd64.zip -d bin
$ rm duckdb_cli-linux-amd64.zip
$ export PATH=$PWD/bin:$PATH
$ duckdb -version
v1.1.3 19864453f7

まず最初に資格情報を標準出力に表示します。

duckdb -c "$(curl -s https://skyfirehose.com/bootstrap.sql)"

https://skyfirehose.com/bootstrap.sqlの中身はこんな感じ。

COPY (select content as SECRET from read_text('https://skyfirehose.com/00000000-0000-0000-0000-000000000000')) TO '/dev/stdout'

さらにhttps://skyfirehose.com/00000000-0000-0000-0000-000000000000の中身はこんな感じ。

CREATE OR REPLACE SECRET s3secret (
  TYPE S3, 
  KEY_ID '...', 
  SECRET '...', 
  SESSION_TOKEN '...', 
  ENDPOINT '*.r2.cloudflarestorage.com', 
  URL_STYLE 'path', 
  REGION 'auto'
);

S3 Compatibility APIなCloudflare R2の資格情報のSQL文が表示されました。
つまりこのSQL文をコピペしてインポートすることでデータベースへアクセスできるようになっているようです。
資格情報は15分間有効です。

DuckDBインスタンスを起動します。

$ duckdb

先程の資格情報のSQL文をコピペしてインポートします。

CREATE OR REPLACE SECRET s3secret (
  TYPE S3, 
  KEY_ID '...', 
  SECRET '...', 
  SESSION_TOKEN '...', 
  ENDPOINT '*.r2.cloudflarestorage.com', 
  URL_STYLE 'path', 
  REGION 'auto'
);

リモートデータベースに接続します。

ATTACH 'https://skyfirehose.com/database' AS bluesky;

スキーマを確認します。

SELECT * FROM bluesky.schema;

table_schema	table_name	column_name	data_type
main	follows	event_dt	DATE
main	follows	event_hour	VARCHAR
main	follows	event_us	BIGINT
main	follows	actor_did	VARCHAR
main	follows	operation	VARCHAR
main	follows	created_ts	TIMESTAMP
main	follows	subject_did	VARCHAR
main	jetstream	event_dt	DATE
main	jetstream	event_hour	VARCHAR
main	jetstream	event_us	BIGINT
main	jetstream	actor_did	VARCHAR
main	jetstream	commit	JSON
main	jetstream	account	JSON
main	jetstream	identity	JSON
main	likes	event_dt	DATE
main	likes	event_hour	VARCHAR
main	likes	event_us	BIGINT
main	likes	actor_did	VARCHAR
main	likes	operation	VARCHAR
main	likes	created_ts	TIMESTAMP
main	likes	uri	VARCHAR
main	posts	event_dt	DATE
main	posts	event_hour	VARCHAR
main	posts	event_us	BIGINT
main	posts	actor_did	VARCHAR
main	posts	operation	VARCHAR
main	posts	created_ts	TIMESTAMP
main	posts	text	VARCHAR
main	posts	language	VARCHAR
main	posts	is_reply	BOOLEAN
main	posts	reply	JSON
main	posts	embed	JSON
main	reposts	event_dt	DATE
main	reposts	event_hour	VARCHAR
main	reposts	event_us	BIGINT
main	reposts	actor_did	VARCHAR
main	reposts	operation	VARCHAR
main	reposts	created_ts	TIMESTAMP
main	reposts	uri	VARCHAR
main	tables	table_schema	VARCHAR
main	tables	table_name	VARCHAR
main	tables	column_name	VARCHAR
main	tables	data_type	VARCHAR

2024年11月18日の12時に発生したlikesイベント数をカウントします。

SELECT count(*) FROM bluesky.likes WHERE event_dt = '2024-11-18' and event_hour = '12';

count_star()
671484

エラーが表示された？

HTTP Error: HTTP GET error on '/?encoding-type=url&list-type=2&prefix=optimized%2Fevent_type%3Dlike%2Fevent_dt%3D' (HTTP 404)

上記エラーが出た場合は資格情報が読み込まれていませんので、最初に紹介した資格情報をコピペして、コンソール内のDuckDBインスタンスに貼り付ける必要があります。

D CREATE OR REPLACE SECRET s3secret (
...
  );

Success
true

Catalog Error: Table with name schema does not exist!
Did you mean "temp.information_schema.schemata"?
LINE 1: SELECT * FROM bluesky.schema;
                      ^

Catalog Error: Table with name likes does not exist!
Did you mean "temp.information_schema.views"?
LINE 1: SELECT count(*) FROM bluesky.likes WHERE event_dt = '2024-11...
                             ^

上記エラーが出た場合はDuckDBインスタンスがリモートデータベースに接続していないため、先にhttps://skyfirehose.com/databaseへアタッチする必要があります。

D ATTACH 'https://skyfirehose.com/database' AS bluesky;

それ以外の理由で動かない場合はskyfirehose.comやCloudflareが不安定になっているか、Cloudflare R2の利用枠を使いすぎたのかもしれないので作者のBlueskyを確認したりしてみましょう。

あとは普段DuckDBやSQLiteで書いている通り、スキーマを確認しながらSQL文を書くだけです。

ただ日付が昇順でソートされているため、LIMITで範囲指定しても最新のデータを取得できず、ORDER BYもめちゃくちゃ時間がかかるのでWHEREで日時を絞って取得したほうがよさそうですね。

bluesky.postsのtext列でLIKEを使えば検索エンジンとして使えなくもないです。

.mode list
.header off
.output result.txt
SELECT text FROM bluesky.posts WHERE text LIKE '%ActivityPub%';

Discussion