Regex extract in BigQuery issue

0
votes

I'm trying to simplify a column in BigQuery by using BigQuery extract on it but I am having a bit of an issue.

Here are two examples of the data I'm extracting from:

dc_pre=CLXk_aigyOMCFQb2dwod4dYCZw;gtm=2wg7f1;gcldc=;gclaw=;gac=UA-5815571-8:;auiddc=;u1=OVERDRFT;u2=undefined;u3=undefined;u4=undefined;u5=SSA;u6=undefined;u7=na;u8=undefined;u9=undefined;u10=undefined;u11=undefined;~oref=https://www.online.bank.co.za/onlineContent/ga_bridge.html

dc_pre=COztt4-tyOMCFcji7Qod440PCw;gtm=2wg7f1;gcldc=;gclaw=;gac=UA-5815571-8:;auiddc=;u1=DDA13;u2=undefined;u3=undefined;u4=undefined;u5=SSA;u6=undefined;u7=na;u8=undefined;u9=undefined;u10=undefined;u11=undefined;~oref=https://www.online.support.co.za/onlineContent/ga_bridge.html

I want to extract the portion between ;u1= and ;u2

Running the following legacy SQL Query

SELECT

      Date(Event_Time),
      Activity_ID,
      REGEXP_EXTRACT(Other_Data, r'(?<=u1=)(.*\n?)(?=;u2)')
    FROM
      [sprt-data-transfer:dtftv2_sprt.p_activity_166401]
    WHERE
      Activity_ID in ('8179851')
      AND Site_ID_DCM NOT IN ('2134603','2136502','2539719','2136304','2134604','2134602','2136701','2378406') 
      AND Event_Time BETWEEN 1563746400000000 AND 1563832799000000

I get the error...

Failed to parse regular expression "(?<=u1=)(.*\n?)(?=;u2)": invalid perl operator: (?<

And this is where my talent runs out, is the error being caused because I'm using legacy SQL? Or is an unsupported format for REGEX?

2
sqlregexgoogle-bigquery
Works: regex101.com/r/07urzB/1 - CinCout
In pure Regex it does work, but when using the query in BigQuery then it won't work. - Charl
Maybe you are supposed to escape a few characters? - CinCout
That's where my talent ran out :) I can find my way around Regex in isolation, and I am ok in BigQuery, but marrying the two worlds caused the issue. - Charl
If the input string always contains u1=[the string you want];u2.., why can't you just use r'u1=(.*?);u2'? btw, consider migrating to standard SQL. - Graham Polley

2 Answers

1
votes

Just tried this, and it worked, but with "Standart SQL" enabled.

select
    other_data,
    regexp_extract(other_data, ';u1=(.+?);u2') as some_part
from
    unnest([
        'dc_pre=CLXk_aigyOMCFQb2dwod4dYCZw;gtm=2wg7f1;gcldc=;gclaw=;gac=UA-5815571-8:;auiddc=;u1=OVERDRFT;u2=undefined;u3=undefined;u4=undefined;u5=SSA;u6=undefined;u7=na;u8=undefined;u9=undefined;u10=undefined;u11=undefined;~oref=https://www.online.bank.co.za/onlineContent/ga_bridge.html',
        'dc_pre=COztt4-tyOMCFcji7Qod440PCw;gtm=2wg7f1;gcldc=;gclaw=;gac=UA-5815571-8:;auiddc=;u1=DDA13;u2=undefined;u3=undefined;u4=undefined;u5=SSA;u6=undefined;u7=na;u8=undefined;u9=undefined;u10=undefined;u11=undefined;~oref=https://www.online.support.co.za/onlineContent/ga_bridge.html'
    ]) as other_data
0
votes

Not using regex but it still works...

with test as (
select 1 as id, 'dc_pre=CLXk_aigyOMCFQb2dwod4dYCZw;gtm=2wg7f1;gcldc=;gclaw=;gac=UA-5815571-8:;auiddc=;u1=OVERDRFT;u2=undefined;u3=undefined;u4=undefined;u5=SSA;u6=undefined;u7=na;u8=undefined;u9=undefined;u10=undefined;u11=undefined;~oref=https://www.online.bank.co.za/onlineContent/ga_bridge.html' as my_str UNION ALL
select 2 as id, 'dc_pre=COztt4-tyOMCFcji7Qod440PCw;gtm=2wg7f1;gcldc=;gclaw=;gac=UA-5815571-8:;auiddc=;u1=DDA13;u2=undefined;u3=undefined;u4=undefined;u5=SSA;u6=undefined;u7=na;u8=undefined;u9=undefined;u10=undefined;u11=undefined;~oref=https://www.online.support.co.za/onlineContent/ga_bridge.html'
),
temp as (
  select 
    id,
    split(my_str,';') as items
  from test
),
flattened as (
  select
    id,
    split(i,'=')[SAFE_OFFSET(0)] as left_side,
    split(i,'=')[SAFE_OFFSET(1)] as right_side
  from temp
  left join unnest(items) i
)
select * from flattened
where left_side = 'u1'