使用正则表达式在Google BigQuery中从URL提取完整域名

Question

请问您能否帮忙构建一个正则表达式，以便在Google Big Query中使用REGEXP_EXTRACT解析给定输入url的完整域名？

解析条件：

• 开始捕获应为：
  • 如果url中有//：在第一个//之后
  • 如果没有//：从字符串开头
• 结束捕获应为：在第一个?或第一个/或第一个&之后，如果没有找到?、/或&，则一直到字符串结尾

一些例子：

htp://www.google.com --> www.google.com
htp://www.google.com/item/ --> www.google.com
htp://www.google.com?source=google --> www.google.com
htp://www.google.com&source=google --> www.google.com
www.google.com --> www.google.com
www.google.com/item/ --> www.google.com
www.google.com?source=google --> www.google.com
www.google.com&source=google --> www.google.com
http://google.com&source=google --> google.com
https://www.example-code.com/vb/string.asp --> www.example-code.com

我创建了这个正则表达式：

REGEXP_EXTRACT('google.it?medium=cpc?cobranded=google&keywor‌​d=foo';, r'//([^/|^?|^&]+)')

但是它仅适用于包含//的URL，我无法得到一个可以在URL中没有//的情况下也起作用的正则表达式。

- Jonk

Answer 1

BigQuery提供以下三个函数：

HOST() -- 给定URL，返回主机名作为字符串。

DOMAIN()-- 给定URL，返回域名作为字符串。

TLD() -- 给定URL，返回URL中的顶级域和任何国家域。

- tenideas

---

BigQuery现在使用NET.HOST()和NET.REG_DOMAIN()代替先前的方法。 - RDRR

@RDRR 现在应该将此标记为主要答案。 - Naveen Kumar

Answer 2

对于任何寻求使用标准SQL解决方案的人，HOST()函数现在位于NET命名空间下，使用NET.HOST(url)语法: https://cloud.google.com/bigquery/docs/reference/standard-sql/net_functions#nethost

WITH
  examples AS (
  SELECT "https://some.domain.com/path?query=param#hash" AS example
  UNION ALL
  SELECT "some.domain.com/path?query=param#hash" AS example)
SELECT
  NET.HOST(example)
FROM
  examples

返回：

some.domain.com
some.domain.com

- Lewis Hemens

Answer 3

'//([^/|^?|^&]+)'

以'//'开头的正则表达式 => 结果需要以'//'开头

你可以这样做

'(?://)([^/|^?|^&]+)'

使用 '()' 创建一个匹配组，但是使用 ?: 这个匹配组将不会出现在结果中。

- baddger964

---

感谢反馈和解释，但是这样做只适用于没有“//”的URL，比如www.google.com，但是对于像http://www.google.com这样的URL就不再起作用了，因为它会捕获到“http:”。 - Jonk

Answer 4

为了证明这个问题与BigQuery标签有关（而不仅仅是正则表达式）-请考虑以下选项

BigQuery Legacy SQL支持一组URL函数
以下是在您的情况下使用的示例

SELECT 
  url, 
  HOST(REPLACE(CASE WHEN url CONTAINS '//' THEN url ELSE 'http://' + url END, '&', '?')) AS output
FROM
  (SELECT 'http://www.google.com' AS url),
  (SELECT 'htp://www.google.com/item/' AS url),
  (SELECT 'htp://www.google.com?source=google' AS url),
  (SELECT 'htp://www.google.com&source=google' AS url),
  (SELECT 'www.google.com' AS url),
  (SELECT 'www.google.com/item/' AS url),
  (SELECT 'www.google.com?source=google' AS url),
  (SELECT 'www.google.com&source=google' AS url),
  (SELECT 'http://google.com&source=google' AS url)

- Mikhail Berlyant

---

我本来更愿意学习如何创建一个正则表达式来解决这个问题，但这种方法也是解决相同问题的好办法，谢谢，如果找不到正则表达式，我会使用它！ - Jonk

明白了。给你一个快速建议 - 学习和提问开放性问题是两回事。如果你想学习 - 你应该先尝试一些东西 - 然后提出具体的问题，询问如何修复或解决这个问题。这样你有机会学到东西。相反，你把学习外包给别人 - 进展的机会就不多了。只是觉得这个评论会帮助你改变使用SO的方式。 - Mikhail Berlyant

这些链接可能会对你更有帮助：如何提问和什么是最小完整可验证示例。 - Mikhail Berlyant

嗨Mikhail，你说得对，我是新来的论坛用户，我应该把我的不起作用的解决方案放在消息正文中（我刚刚做到了）。在主题中，我询问了一个使用正则表达式的解决方案，我以为这已经足够了！无论如何，我很高兴得到了你提供的解决问题的方法，如果我找不到任何正则表达式，我会使用它的，谢谢！ - Jonk

Answer 5

可能是类似于这样的东西

(w{0,3}\.*[a-z]+\.[a-z]*)

Explanation

应该匹配任何带有或不带有www的URL

- Anton Balaniuc

---

谢谢你的帮助！ 很抱歉，我的示例可能不够清楚（我刚刚编辑了消息），因为它还应该适用于任何其他域名，比如那些不以www开头的域名。例如，在"http://google.com&source=google"的情况下，它应该提供"google.com"。 - Jonk

或者甚至是 w{0,3}\.{0,1}。 - Anton Balaniuc

嗨，感谢反馈！我觉得我可以删除第一部分，对吗？我的意思是，只使用：([a-z]+.[a-z]*)我唯一看到的问题是它不能与包含"-"（这是一个允许的字符）的域名一起工作，比如https://www.example-code.com/vb/string.asp我应该修改成这样吗：([a-z|-]+.[a-z|-]*)？谢谢！ - Jonk

@Jonk 你可以直接使用 (w{0,3}\.{0,1}[a-z-]+\.[a-z-]*)。 - Anton Balaniuc

Answer 6

这个能行吗？

/b[\w.-]+(?:com|edu)

只适用于 '.com' 和 'edu' 地址，但或许可以进一步修改。

****更新****

忍不住玩弄了一下。这里有一个将域名分组为捕获组的方法：

([\w.-]++(?!:)).*+

需要支持前瞻并假设每个URL之间有换行符。

基本上它会找到任何由字母、数字、句点或破折号组成且后面没有冒号的序列。

冒号的目的是防止它找到http:

'.*+'是为了消耗行的剩余部分，以便在第一次分组后不继续寻找匹配项。

- shrug