Utiliser les fichiers webatla

SELECT Domain, TLD, "Country by IP", "HTTP status"
FROM read_json('all-data-2026-10-16.jsonl.zst',
  columns = {Domain: 'VARCHAR', TLD: 'VARCHAR',
             "Country by IP": 'VARCHAR', "HTTP status": 'INTEGER'})
LIMIT 3;

zstdcat dns-2026-10-16.jsonl.zst | head -n 1 | jq .

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"])

zstd -dc all-data-2026-10-16.jsonl.zst | wc -l
zstd -dc all-data-2026-10-16.jsonl.zst | head -n 1 | jq -r 'keys_unsorted[]'

zstd -dc all-data-2026-10-16.jsonl.zst | shuf -n 1000 > random-1000.jsonl
wc -l < random-1000.jsonl

# 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

zstd -dc all-data-2026-10-16.jsonl.zst \
  | grep -F '"Country by IP":"DE"' \
  | jq -c 'select(."IP address" != null and ."Country by IP" == "DE")' > de-websites.jsonl

zstd -dc all-data-2026-10-16.jsonl.zst | jq -nr '
  ["Domain","TLD suffix","HTTP status","Technologies"],
  (inputs | [.Domain, ."TLD suffix", ."HTTP status",
             (.Technologies | if . then tojson else null end)])
  | @csv' > all-data.csv

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';

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"

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);

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;