Impala中的REGEXP_EXTRACT [英] REGEXP_EXTRACT in Impala

问题描述

我试图弄清楚如何从看起来像这样的字符串中提取客户ID:

{"param":"success","value":"10","level":"0","error_code":"101","customer_id":"5b0e9b23e423b0d33c9f7ddfd", "purchases": "13", "last_activity_ts": "123523465"}

我正在尝试从包含错误代码101的字符串中提取具有以下代码的客户ID:

select regexp_extract(field, '\"customer_id":"(.*)', 0) from table_name
where field rlike '"error_code":"101"'

但这给了我以下结果:

"customer_id":"5b0e9b23e423b0d33c9f7ddfd", "purchases": "13", "last_activity_ts": "123523465"}

预期结果:

5b0e9b23e423b0d33c9f7ddfd

您能帮我吗?

解决方案

您可以在正则表达式下面使用:

"customer_id":"([\w\d]+)"

演示: https://regex101.com/r/MEOGw8/1

测试:

{"param":"success","value":"10","level":"0","error_code":"101","customer_id":"5b0e9b23e423b0d33c9f7ddfd", "purchases": "13", "last_activity_ts": "123523465"}

匹配:

Match 1
Full match  63-104  `"customer_id":"5b0e9b23e423b0d33c9f7ddfd"`
Group 1.    78-103  `5b0e9b23e423b0d33c9f7ddfd`

SQL语句:

select regexp_extract(field, '"customer_id":"([\w\d]+)"',1, 1) from table_name
where field rlike '"error_code":"101"'

I am trying to figure out how to extract customer ID from string that looks loke this:

{"param":"success","value":"10","level":"0","error_code":"101","customer_id":"5b0e9b23e423b0d33c9f7ddfd", "purchases": "13", "last_activity_ts": "123523465"}

I am trying to extract customer ID from strings that contain error code 101 with following code:

select regexp_extract(field, '\"customer_id":"(.*)', 0) from table_name
where field rlike '"error_code":"101"'

But this gives me a following result:

"customer_id":"5b0e9b23e423b0d33c9f7ddfd", "purchases": "13", "last_activity_ts": "123523465"}

Expected result:

5b0e9b23e423b0d33c9f7ddfd

Could you please help me with this?

解决方案

You can use below regex:

"customer_id":"([\w\d]+)"

Demo : https://regex101.com/r/MEOGw8/1

Test:

{"param":"success","value":"10","level":"0","error_code":"101","customer_id":"5b0e9b23e423b0d33c9f7ddfd", "purchases": "13", "last_activity_ts": "123523465"}

Match:

Match 1
Full match  63-104  `"customer_id":"5b0e9b23e423b0d33c9f7ddfd"`
Group 1.    78-103  `5b0e9b23e423b0d33c9f7ddfd`

SQL Statement:

select regexp_extract(field, '"customer_id":"([\w\d]+)"',1, 1) from table_name
where field rlike '"error_code":"101"'

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