CODEWITHSUNDEEP
sqlgoogle-bigquery
Konstantin Ivanov
Konstantin Ivanov

Reputation: 33

Google BigQuery create simple count table

I want to user Google Bigquery to store number of searches for certain keyword in my site. I create table structure like this:

| date       | keyword | number_of_searches |
| 2017-03-29 | pizza   | 1                  |

I want to increment number_of_searches value if combination of date and keyword already exists.

Upvotes: 0

Views: 690

Answers (1)

Pentium10
Pentium10

Reputation: 207982

So you wanted a solution to store number of searches for certain keyword.

Using BigQuery technology you need to change some approach.

Let's discuss traditional steps:
- use a SELECT to find out if there is a row for today
- if not, then INSERT one with default
- when exists, use an UPDATE statement to increment counter

With BigQuery where the main change is that it's append only and it's not suitable to do UPDATE statements, you need to change how you analyse data and simplify the collection. Instead of doing 3 steps like above you would do one:
- insert a new row for each search

This way you will end up with multiple rows, and you can aggregate to find out the value and can run a query to find out how many searches you had for your queries, it would be something like this:

SELECT 
   myday as date,
   keyword,
   count(1) as number_of_search
FROM table
group by 1,2

Upvotes: 3

Related Questions