CODEWITHSUNDEEP
sqlgoogle-bigquerysql-insert
Mikel
Mikel

Reputation: 33

BigQuery INSERT INTO when query doesn't return results

I am saving in a BigQuery table (table_a) some events. At 00:00 of all days I am doing a scheduled query to save in another table (table_b) the counter of a specific event that I have received that day. This scheduled query hasn't got any problems when I received this event, but in case that in that day I don´t receive any of this specific event I received in the query "This query returned no results.", but I want to add to the table_b the INSIGHT_VALUE with 0 value.

How can I make this?

This is the scheduled query that I am using:

INSERT INTO
  `my_project.my_dataset.table_b` (
    DATESTAMP,
    INSIGHT_VALUE)
SELECT
  CURRENT_TIMESTAMP() AS DATESTAMP,
  COUNT(*) AS INSIGHT_VALUE
FROM
  `my_project.my_dataset.table_a`
WHERE
  DATE(event_datestamp, 'Europe/Madrid') = CURRENT_DATE()
  AND event_message LIKE '%specific_event%'
GROUP BY
  DATESTAMP

Upvotes: 0

Views: 809

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Remove the GROUP BY:

INSERT INTO `my_project.my_dataset.table_b` (DATESTAMP, INSIGHT_VALUE)
    SELECT CURRENT_TIMESTAMP AS DATESTAMP,
           COUNT(*) AS INSIGHT_VALUE
    FROM `my_project.my_dataset.table_a`
    WHERE DATE(event_datestamp, 'Europe/Madrid') = CURRENT_DATE() AND
          event_message LIKE '%specific_event%'

An aggregation query with no GROUP BY always returns one row. Most aggregation functions return NULL values, but COUNT() returns 0.

CURRENT_TIMESTAMP is a "constant" in the query, so it does not need to be included in the aggregation -- the value is simply calculated on each row return (well, on the row that is returned).

Upvotes: 2

Related Questions