CODEWITHSUNDEEP
sqlgoogle-bigquery
Luiscri
Luiscri

Reputation: 1037

Get the most recent Timestamp value

I have a pipeline which reads from a BigQuery table, performs some processing to the data and saves it into a new BigQuery table. This is a batch process performed on a weekly basis through a cron. Entries keep being added on the source table, so I want that whenever I start the ETL process it only process the new rows which have been added since the last time the ETL job was launched.

In order to achieve this, I have thought about making a query to my sink table asking for the most recent timestamp it contains. Then, as a data source I will perform another query to the source table filtering and asking for the entries having a timestamp higher than the one I have just recovered. Both my source and sink table are time partitioned ones.

The query I am using for getting the latest entry on my sink table is the following one:

SELECT Timestamp
FROM `myproject.mydataset.mytable`
ORDER BY Timestamp DESC
LIMIT 1

It gives me the correct value, but I feel like if it is not the most efficient way of querying it. Does this query take advantage of the partitioned feature of my table? Is there any better way of retrieving the most recent timestamp from my table?

Upvotes: 0

Views: 3205

Answers (1)

Korean_Of_the_Mountain
Korean_Of_the_Mountain

Reputation: 1577

I'm going to refer to the timestamp field as ts_field for your example.

To get the latest timestamp, I would run the following query:

SELECT max(ts_field)
FROM `myproject.mydataset.mytable`

If your table is also partitioned on the timestamp field, you can do something like this to scan even less bytes:

SELECT max(ts_field)
FROM `myproject.mydataset.mytable`
WHERE date(ts_field) = current_date()

Upvotes: 1

Related Questions