bigquery url解码 [英] bigquery url decode

问题描述

有没有一种简单的方法在BigQuery查询语言中进行URL解码？我正在处理一个包含一些值中包含URL编码字符串的列的表。例如：

  http://xyz.com/example.php?url=http%3A%2F%2Fwww.example .com％2Fhello％3Fv％3D12345& foo = bar& abc = xyz 
  

参数如下：

  SELECT REGEXP_EXTRACT（column_name，url =（[^&] +））as url 
 from [mydataset.mytable] 
  

这给了我：

  http％3A％2F％2Fwww.example.com％2Fhello％3Fv％3D12345 
  

我想要做的是这样的：

  SELECT URL_DECODE（REGEXP_EXTRACT （从[mydataset.mytable]] 
  

$ b $从[url]（[url] b

返回：

  http://www.example.com/hello?v=12345 
  

我想避免使用多个REGEXP_REPLACE（）语句（替换％20，％3A等）。 ..）如果可能的话。

点子？

解决方案

另一个解决方法是使用用户定义的函数。 $ b

#standardSQL
CREATE TEMPORARY FUNCTION URL_DECODE（enc STRING）
RETURNS STRING
LANGUAGE js AS
try {
return decodeURI（enc）;;
} catch（e）{return null}
return null;
;

SELECT ven_session，
URL_DECODE（REGEXP_EXTRACT（para，r'& kw =（\w | [^&] *）'））AS q
FROM raas_system .weblog_20170327
其中像'％& kw =％'
极限10


Is there an easy way to do URL decoding within the BigQuery query language? I'm working with a table that has a column containing URL-encoded strings in some values. For example:

http://xyz.com/example.php?url=http%3A%2F%2Fwww.example.com%2Fhello%3Fv%3D12345&foo=bar&abc=xyz

I extract the "url" parameter like so:

SELECT REGEXP_EXTRACT(column_name, "url=([^&]+)") as url 
from [mydataset.mytable]

which gives me:

http%3A%2F%2Fwww.example.com%2Fhello%3Fv%3D12345

What I would like to do is something like:

SELECT URL_DECODE(REGEXP_EXTRACT(column_name, "url=([^&]+)")) as url 
from [mydataset.mytable]

thereby returning:

http://www.example.com/hello?v=12345

I would like to avoid using multiple REGEXP_REPLACE() statements (replacing %20, %3A, etc...) if possible.

Ideas?

解决方案

One more workaround is using a user-defined function.

#standardSQL
CREATE TEMPORARY FUNCTION URL_DECODE(enc STRING)
RETURNS STRING
LANGUAGE js AS """
  try { 
    return decodeURI(enc);;
  } catch (e) { return null }
  return null;
""";

SELECT ven_session, 
  URL_DECODE(REGEXP_EXTRACT(para,r'&kw=(\w|[^&]*)')) AS q
FROM raas_system.weblog_20170327 
WHERE para like '%&kw=%'
LIMIT 10

这篇关于bigquery url解码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！