如何在PrestoDB（AWS Athena）中提取顶级域名（TLD）？

由于正则无法准确适配所有注册局的TLD规则，必须依赖publicsuffix.org的官方列表实现精准拆分。以下是适配Athena（Presto）的无循环解决方案：

步骤1：导入Public Suffix列表到Athena表 #

先将publicsuffix.org的TLD列表保存为文本文件（每行一个TLD，包含co.uk、github.io这类多级后缀），上传到S3存储桶，再创建Athena表：

CREATE TABLE tld_list (
  tld STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\n'
LOCATION 's3://your-bucket/path/to/tld-list/';

性能优化提示：将文本表转换为Parquet列式存储，提升关联效率：

CREATE TABLE tld_list_parquet
WITH (format = 'PARQUET')
AS SELECT tld FROM tld_list;

步骤2：核心查询实现拆分 #

通过数组操作、窗口函数替代循环逻辑，找到每个Host的最长匹配TLD，进而拆分出子域名、主域名：

WITH host_component AS (
  SELECT
    host,
    split(host, '.') AS parts,
    cardinality(split(host, '.')) AS total_parts
  FROM your_url_dataset_table -- 替换为你的URL数据集表名
),
candidate_suffixes AS (
  SELECT
    host,
    parts,
    total_parts,
    -- 生成从短到长的所有后缀候选
    array_join(slice(parts, total_parts - pos + 1, pos), '.') AS candidate_tld,
    pos AS suffix_length
  FROM host_component
  CROSS JOIN UNNEST(sequence(1, total_parts)) AS t(pos)
),
matched_suffixes AS (
  SELECT
    host,
    parts,
    total_parts,
    candidate_tld AS tld,
    suffix_length,
    -- 标记最长匹配的TLD（优先级最高）
    ROW_NUMBER() OVER (PARTITION BY host ORDER BY suffix_length DESC) AS rank
  FROM candidate_suffixes
  JOIN tld_list_parquet ON candidate_tld = tld_list_parquet.tld -- 使用优化后的Parquet表
)
SELECT
  host,
  -- 子域名：若剩余组件数大于1（主域名+TLD），拼接前面的所有部分
  CASE
    WHEN total_parts - suffix_length > 1 THEN array_join(slice(parts, 1, total_parts - suffix_length - 1), '.')
    ELSE ''
  END AS subdomain,
  -- 主域名：TLD前紧邻的一个组件
  array_join(slice(parts, total_parts - suffix_length, 1), '.') AS domain,
  tld
FROM matched_suffixes
WHERE rank = 1
-- 兜底处理未匹配到的自定义/小众TLD（默认取最后一个组件为TLD）
UNION ALL
SELECT
  host,
  '' AS subdomain,
  array_join(slice(parts, 1, total_parts - 1), '.') AS domain,
  parts[total_parts] AS tld
FROM host_component
WHERE host NOT IN (SELECT host FROM matched_suffixes);

关键逻辑说明 #

1. 拆分Host组件：将Host按.拆分为字符串数组，例如aws.amazon.com拆为['aws','amazon','com']
2. 生成候选后缀：通过UNNEST和sequence生成所有可能的后缀组合（从最短的com到最长的aws.amazon.com）
3. 匹配最长TLD：关联TLD表后，用窗口函数取每个Host的最长匹配后缀（确保符合注册局规则，如co.uk会优先于uk被匹配）
4. 拆分域名部分：根据最长TLD的长度，分别提取子域名（TLD前的所有前缀）、主域名（TLD前的紧邻组件）

注意事项 #

• 定期更新TLD表：publicsuffix.org的列表会持续更新，建议每月同步一次
• 处理国际化域名：若包含非ASCII域名，需先用domain_to_punycode(host)转换为Punycode后再拆分
• 性能调优：对于超大规模数据集，可对URL表按Host前缀分区，或启用Athena查询加速功能

内容的提问来源于stack exchange，提问作者broti