CODEWITHSUNDEEP
google-bigquery
James Danowski
James Danowski

Reputation: 89

How can I reduce Google BigQuery costs?

I have been searching using Google BigQuery on the GDELT database of global news. I am repeating the same search 54 times, just changing the name of an African country.

Is it possible to include all 54 searches in the same query? As I understand the billing, the cost is based on the size of the database searched, not the number of query elements. Is that correct?

Here is an example of my queries for the country of Gabon, selecting themes appearing with ICT.

SELECT theme, COUNT(*) as count
FROM (
select UNIQUE(REGEXP_REPLACE(SPLIT(V2locations,';'), r',.*', '')) theme
  from [gdelt-bq:gdeltv2.gkg]
where DATE>20150302000000 and DATE < 20200609000000 and V2locations like '%Gabon%'
AND V2themes like '%WB_133_INFORMATION_AND_COMMUNICATION_TECHNOLOGIES%'    
)
group by theme
ORDER BY 2 DESC
LIMIT 300

Upvotes: 0

Views: 127

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

The simplest way to do so without changing your query logic is to replace 

V2locations like '%Gabon%'

with 

REGEXP_MATCH(V2locations, r'Gabon|Angola|Zimbabwe')

Note: the query in question is in BigQuery LegacySQL - so obviously i would recommend migration to Standard SQL

Upvotes: 1

Related Questions