Google bigquery python client library SQL select regex error

Question

I'm trying to query a google bigquery table using the regex from this blog post. Here it is, slightly modified:

pd\.([^”,\.\(\,\`) \’:\[\]\/\\={}]*)

It does not, however, work in my google bigquery python client SQL query:

query_results = client.run_sync_query(
"""
SELECT
  REGEXP_EXTRACT(SPLIT(content, '\n'),
                 r'pd\.([^”,\.\(\,\`) \’:\[\]\/\\={}]*)')
FROM
  [fh-bigquery:github_extracts.contents_py]
LIMIT 10
""")

query_results.run()

data = query_results.fetch_data()
data

BadRequest: BadRequest: 400 Failed to parse regular expression "pd.([^”,.(\,`) \’:[]/\={}]*)": invalid escape sequence: \’

Willian Fuks · Accepted Answer · 2017-01-14 11:20:45Z

2

The problem here is that BigQuery uses re2 library for its regex operations.

If you try the same regex but using the golang flavor you will see the exact same error (golang also uses re2).

So maybe if you just remove the escaping of the ' character you'll already have it working for you (as I tested here it seemed to work properly).

Another issue that you might find is that the result of the SPLIT operation is an ARRAY. That means that BigQuery won't process your query saying that the signature of REGEXP_EXTRACT does not allow ARRAY<STRING> as input. You could use REGEXP_REPLACE instead:

"""
SELECT
  REGEXP_EXTRACT(REGEXP_REPLACE(content, r'.*(\\n)', ''),
                 r'pd\.([^”,\.\(\,\`) ’:\[\]\/\\={}]*)')
FROM
  [fh-bigquery:github_extracts.contents_py]
LIMIT 10
"""

The character "\n" is replaced by "" in this operation and the result is a STRING.

answered Jan 14, 2017 at 11:20

Willian Fuks

11.9k10 gold badges55 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Over a year ago

Cheers @Will, I appreciate the solution and explanation. The REGEXP_EXTRACT appears to be working on the array, but I will experiment your method should I find that error.

Collectives™ on Stack Overflow

Google bigquery python client library SQL select regex error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related