Bigquery Standard Dialect REGEXP_REPLACE input type

Question

I am exploring the power of Google Biguery with the GDELT database using this tutorial however the sql dialect is in 'legacy' and I would like to use the standard dialect.

In legacy dialect:

SELECT
  theme,
  COUNT(*) AS count
FROM (
  SELECT
    REGEXP_REPLACE(SPLIT(V2Themes,';'), r',.*',"") theme
from [gdelt-bq:gdeltv2.gkg]
where DATE>20150302000000 and DATE < 20150304000000 and V2Persons like '%Netanyahu%'
)
group by theme
ORDER BY 2 DESC
LIMIT 300

and when I try to translate into standard dialect:

SELECT
  theme,
  COUNT(*) AS count
FROM (
  SELECT
    REGEXP_REPLACE(SPLIT(V2Themes,';') , r',.*', " ") AS theme
    FROM
      `gdelt-bq.gdeltv2.gkg`
    WHERE
      DATE>20150302000000
      AND DATE < 20150304000000
      AND V2Persons LIKE '%Netanyahu%' )
  GROUP BY
    theme
  ORDER BY
    2 DESC
  LIMIT
    300

it throws the following error:

No matching signature for function REGEXP_REPLACE for argument types: ARRAY<STRING>, STRING, STRING. Supported signatures: REGEXP_REPLACE(STRING, STRING, STRING); REGEXP_REPLACE(BYTES, BYTES, BYTES) at [6:5]

it seems like I have to cast the result of the SPLIT() operation as a string. How do I do this?

UPDATE: I found a talk explaining the unnest operation:

SELECT
  COUNT(*),
  REGEXP_REPLACE(themes,",.*","") AS theme
FROM
  `gdelt-bq.gdeltv2.gkg_partitioned`,
  UNNEST( SPLIT(V2Themes,";") ) AS themes
WHERE
  _PARTITIONTIME >= "2018-08-09 00:00:00"
  AND _PARTITIONTIME < "2018-08-10 00:00:00"
  AND V2Persons LIKE '%Netanyahu%'
GROUP BY
  theme
ORDER BY
  2 DESC
LIMIT
  100

Martin Weitzmann · Accepted Answer · 2018-08-09 14:54:32Z

5

Flatten the array first:

SELECT
  REGEXP_REPLACE(theme , r',.*', " ") AS theme,
  COUNT(*) AS count
FROM
  `gdelt-bq.gdeltv2.gkg`,
  UNNEST(SPLIT(V2Themes,';')) AS theme
WHERE
  DATE>20150302000000
  AND DATE < 20150304000000
  AND V2Persons LIKE '%Netanyahu%' 
GROUP BY
  theme
ORDER BY
  2 DESC
LIMIT
  300

The legacy SQL equivalent in your question actually has the effect of flattening the array as well, although it's implicit in the GROUP BY on the theme.

edited Aug 9, 2018 at 14:54

Martin Weitzmann

4,75612 silver badges22 bronze badges

answered Aug 9, 2018 at 14:05

Elliott Brossard

34k2 gold badges75 silver badges105 bronze badges

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

3 Comments

Rutger Hofste Over a year ago

the code does not work "as is". I just copied it in the query editor and get the error: "Values referenced in UNNEST must be arrays. UNNEST contains expression of type STRING at [6:10]"

Martin Weitzmann Over a year ago

I edited it and moved the split, which creates the array, to the cross join

Elliott Brossard Over a year ago

Ah, right, I didn't read closely enough. I was focused on the V2Themes, which I was thinking was an array. Thanks for the edit!

Collectives™ on Stack Overflow

Bigquery Standard Dialect REGEXP_REPLACE input type

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related