import io, json, zstandard
with open("dns-2026-10-16.jsonl.zst", "rb") as fh:
stream = zstandard.ZstdDecompressor().stream_reader(fh, read_across_frames=True)
for line in io.TextIOWrapper(stream, encoding="utf-8"):
row = json.loads(line)
mx = (row["DNS records"] or {}).get("MX") or []
if any((m.get("host") or "").lower().endswith(".google.com") for m in mx):
print(row["Domain"])
COPY (SELECT Domain, "PR value" FROM read_json('technologies-2026-10-16.jsonl.zst',
columns = {Domain: 'VARCHAR', "Country by IP": 'VARCHAR',
Technologies: 'JSON', "PR value": 'DOUBLE'})
WHERE "Country by IP" = 'DE' AND list_contains(CASE json_type(Technologies)
WHEN 'OBJECT' THEN json_keys(Technologies)
WHEN 'ARRAY' THEN from_json(Technologies, '["VARCHAR"]') END, 'WooCommerce')
ORDER BY "PR value" DESC
) TO 'woocommerce-de.csv';
SELECT tech, count(*) AS sites,
round(100 * count(*) / sum(count(*)) OVER (), 1) AS pct
FROM (SELECT unnest(CASE json_type(Technologies)
WHEN 'OBJECT' THEN json_keys(Technologies)
WHEN 'ARRAY' THEN from_json(Technologies, '["VARCHAR"]') END) AS tech
FROM read_json('technologies-2026-10-16.jsonl.zst', columns = {Technologies: 'JSON'}))
WHERE tech IN ('WordPress', 'Wix', 'Squarespace', 'Webflow', 'Drupal', 'Joomla')
GROUP BY tech ORDER BY sites DESC;
SELECT "Domain creation date"[:10] AS created, Domain, Registrar
FROM read_json('rdap-whois-2026-10-16.jsonl.zst', columns = {Domain: 'VARCHAR',
"Domain creation date": 'VARCHAR', Registrar: 'VARCHAR'})
WHERE Domain LIKE '%shop%'
AND "Domain creation date" >= CAST(current_date - 30 AS VARCHAR)
ORDER BY created DESC;
SELECT Domain, "Domain expiration date"[:10] AS expires
FROM read_json('websites-ranking-2026-10-16.jsonl.zst', columns = {Domain: 'VARCHAR',
"Domain expiration date": 'VARCHAR', "PR value": 'DOUBLE'})
WHERE "Domain expiration date"
BETWEEN CAST(current_date AS VARCHAR) AND CAST(current_date + 30 AS VARCHAR)
ORDER BY "PR value" DESC LIMIT 3;
CREATE OR REPLACE MACRO provider(h) AS
array_to_string(string_split(lower(rtrim(h, '.')), '.')[-2:], '.');
SELECT provider(json_extract_string("DNS records", '$.NS[0]')) AS dns,
count(*) AS domains, round(100 * count(*) / sum(count(*)) OVER (), 1) AS pct
FROM read_json('dns-2026-10-16.jsonl.zst', columns = {"DNS records": 'JSON'})
WHERE dns <> '' GROUP BY dns ORDER BY domains DESC, dns LIMIT 3;
# every domain under one TLD (de covers every suffix that ends in .de)
zstd -dc all-data-2026-10-16.jsonl.zst | jq -c 'select(.TLD == "de")' > de.jsonl
# exactly one suffix
zstd -dc all-data-2026-10-16.jsonl.zst | jq -c 'select(."TLD suffix" == "2ix.de")' > 2ix.de.jsonl
# a suffix that equals its TLD (com, de, ac) has no file of its own:
# filter the TLD slice instead
zstd -dc webatla-all-data-ac-2026-10-16.jsonl.zst | jq -c 'select(."TLD suffix" == "ac")' > ac.jsonl
COPY (
SELECT Domain AS Website, "Country by IP" AS Country
FROM read_json('webatla-technologies-WooCommerce-2026-10-16.jsonl.zst',
columns = {'Domain': 'VARCHAR', 'Country by IP': 'VARCHAR', 'PR value': 'DOUBLE'})
WHERE "Country by IP" = 'DE'
ORDER BY "PR value" DESC
) TO 'woocommerce-germany.csv';
COPY (
SELECT Domain AS Website, "Country by IP" AS Country
FROM read_json('webatla-technologies-HubSpot-2026-10-16.jsonl.zst', columns = {
'Domain': 'VARCHAR', 'Country by IP': 'VARCHAR', 'Technologies': 'JSON'})
WHERE list_contains(CASE json_type(Technologies) WHEN 'OBJECT' THEN json_keys(Technologies)
ELSE json_extract_string(Technologies, '$[*]') END, 'WordPress')
) TO 'hubspot-on-wordpress.csv';
無料の10,000行プレビューでの実際の出力
Website,Country
ateliercrescendo.ac,US
セールスオペレーション
担当エリアごとに1ファイル
切り出しをサーバーの国ごとに1つのCSVへ分割します。
COPY (
SELECT Domain AS Website, coalesce("Country by IP", 'unknown') AS Country
FROM read_json('webatla-technologies-WooCommerce-2026-10-16.jsonl.zst',
columns = {'Domain': 'VARCHAR', 'Country by IP': 'VARCHAR'})
) TO 'woocommerce-by-country'
(FORMAT csv, PARTITION_BY (Country), WRITE_PARTITION_COLUMNS true, OVERWRITE);
psql -c 'CREATE TABLE domains (doc jsonb)'
zstdcat technologies-2026-10-16.jsonl.zst \
| psql -c "\copy domains FROM STDIN WITH (FORMAT csv, QUOTE e'\x01', DELIMITER e'\x02')"
psql -c "CREATE INDEX ON domains USING gin ((doc->'Technologies'))"
psql -c "SELECT doc->>'Country by IP' AS country, count(*) AS sites FROM domains
WHERE doc->'Technologies' ? 'WordPress' GROUP BY 1 ORDER BY 2 DESC, 1 LIMIT 5"
無料の10,000行プレビューでの実際の出力
COPY 10000
country | sites
---------+-------
US | 300
DE | 110
CA | 29
CREATE OR REPLACE MACRO techs(t) AS CASE json_type(t) WHEN 'OBJECT'
THEN json_keys(t) WHEN 'ARRAY' THEN t->>'$[*]' ELSE []::VARCHAR[] END;
COPY (SELECT "Domain" AS domain, "Country by IP" AS country,
"PR value" AS pr_value, techs("Technologies") AS technologies
FROM read_json('technologies-2026-10-16.jsonl.zst', columns = {
'Domain': 'VARCHAR', 'Country by IP': 'VARCHAR',
'PR value': 'DOUBLE', 'Technologies': 'JSON'}))
TO 'technologies.parquet' (FORMAT parquet);
無料の10,000行プレビューでの実際の出力
technologies.parquet: 10000 rows
domain · country · pr_value · technologies (list of names)
手持ちのリストの拡充
DuckDB
片側にお手元のCSV、もう片側にエクスポートを置き、Domainで突き合わせます。
SELECT c.domain, w."Country by IP" AS country, w."Registrar" AS registrar,
w."HTTP status" AS http, w."Domain creation date"[:10] AS created
FROM read_csv('my-domains.csv') c
LEFT JOIN read_json('all-data-2026-10-16.jsonl.zst', columns = {
'Domain': 'VARCHAR', 'Country by IP': 'VARCHAR', 'Registrar': 'VARCHAR',
'HTTP status': 'INTEGER', 'Domain creation date': 'VARCHAR'}) w
ON w."Domain" = lower(trim(c.domain))
ORDER BY 1;
無料の10,000行プレビューでの実際の出力
domain country registrar http created
Tamm.AbuDhabi AE ADSIC Registry Operator 200 2018-07-09
easy.ac US MarkMonitor Inc. 200 2014-02-27
global.abb US CSC Corporate Domains, Inc. 301 2019-10-03