bigquery url解码 [英] bigquery url decode
本文介绍了bigquery 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屋!
查看全文