时间戳

首页 > 解决方案 > read_gbq 函数中 REGEXP_EXTRACT 中的正则表达式失败

python - read_gbq 函数中 REGEXP_EXTRACT 中的正则表达式失败

问题描述

我未能在 read_gbq 函数中成功执行正则表达式函数（即 REGEXP_EXTRACT）。

read_gbq 来自 pandas_gbq 模块。

我的 Python 程序中的 import 语句是：from pandas_gbq import read_gbq。

我的环境中pandas-gbq的版本是：0.8.0

我相信，由于无法识别双引号上的黑色斜线转义字符，正则表达式失败了。

此正则表达式在 Big Query 和使用 Python 的在线 RegEx 测试器中运行良好（参见下面的代码部分）。

感谢您的时间和关注

component = 'CO_ORDER_SUMMARY'
def Read_CO_Order_Summary():                            

        query = ('select co.timestamp, co.jsonPayload._userid_ as co_SVOC, co.jsonPayload.response,  \
                 REGEXP_EXTRACT(co.jsonPayload.response, customerOrderId\":\"([^\"]*)\".*) as CustomerOrderID \
        from `exported_logs_v2.mcc_checkout_service_servicelog_20190623` co  '
             'where co.jsonPayload.component = ' '"' + component + '"' 

      'order by co.timestamp, co.jsonPayload._userid_ '
      'limit 1'
                  )

        co_agg = read_gbq(query, projectid, dialect='standard')

        return(co_agg)
co_agg = Read_CO_Order_Summary()

**ERROR MESSAGE**
GenericGBQException: Reason: 400 Syntax error: Expected “)” but got string literal “:” at [1:253]

****************************************
IN REGEX101.com TESTER (using the Python "flavor" setting)
REGEX
customerOrderId\":\"([^\"]*)\".*
STRING
{"lastModifiedDate":"2019-06-23 16:50:18.212","localStoreId":1515,"cartId":"HC100006597310","customerOrderId":"W838207358","
RESULT
Match 1     
Full match  customerOrderId":"W838207358"," 
Group 1.    W838207358
******************* Big Query ******************
SELECT timestamp, jsonpayload._userid_, jsonpayload.response, 
 -- REGEXP_EXTRACT(jsonPayload.response, r'\"customerOrderId\":\"(.*?)\","')  as CustomerOrderID,    ## All 3 of these work 
 -- REGEXP_EXTRACT(jsonPayload.response, r"customerOrderId\":\"(.*?)\",")     as CustomerOrderID  
    REGEXP_EXTRACT(jsonPayload.response, r"customerOrderId\":\"([^\"]*)\".*") as CustomerOrderID  
FROM `exported_logs_v2.mcc_checkout_service_servicelog_201906*`
   where jsonpayload.component like '%CO_ORDER_SUMMARY%'   ##'%CO_ORDER_SUMMARY%'  or   '%CO_SECURE_LOGON%'
     and ( _TABLE_SUFFIX between "23" and "23" )
     and   jsonPayload._userid_ = "0516CFC3D4B001FB0S" 
 order by timestamp asc

标签： pythonregexgoogle-bigqueryescaping

解决方案

如果co.jsonPayload.response是有效的 JSON 字符串，您可以使用JSON_EXTRACT_SCALAR(co.jsonPayload.response, '$.customerOrderId').

推荐阅读