使用正则在Presto DB中从来源URL提取核心域名

Got it, let's tackle this problem step by step. You already have the hostnames (like google.com, google.co.il, blog.google.com, blog.google.co.il) using url_extract_host, and need to pull out the core domain "google" from all those variations. Since Presto uses Java-style regex, here's a reliable way to make it work:

Solution Code #

Use the regexp_extract function with a regex pattern that handles both single-level TLDs (like .com) and multi-level TLDs (like .co.il), plus any leading subdomains (like blog.):

SELECT 
  hostname,
  regexp_extract(hostname, '^(?:.*\\.)?([^.]+)\\.(?:[^.]+\\.[^.]+|[^.]+)$', 1) AS core_domain
FROM 
  your_table_name;

How It Works #

Let’s break down the regex pattern ^(?:.*\\.)?([^.]+)\\.(?:[^.]+\\.[^.]+|[^.]+)$:

• ^(?:.*\\.)?: This non-capturing group matches any leading subdomains (like blog.) — the ? makes it optional for hostnames without subdomains.
• ([^.]+): This capturing group grabs the core domain (e.g., "google") by matching one or more characters that aren’t a dot. This is the value we extract.
• \\.(?:[^.]+\\.[^.]+|[^.]+)$: This non-capturing group matches the TLD section — either a multi-level TLD (like co.il) or a single-level TLD (like com), ensuring we only capture the core domain before it.

Test Results #

Running this on your sample hostnames will return:

• google.com → google
• google.co.il → google
• blog.google.com → google
• blog.google.co.il → google

Edge Case Note #

If you have hostnames without a TLD (e.g., just google), this regex will return NULL since it expects at least one dot. You can adjust for this with a COALESCE if needed:

SELECT 
  hostname,
  COALESCE(
    regexp_extract(hostname, '^(?:.*\\.)?([^.]+)\\.(?:[^.]+\\.[^.]+|[^.]+)$', 1),
    hostname
  ) AS core_domain
FROM 
  your_table_name;

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