Compare prev_execution_date in Airflow to timestamp in BigQuery using SQL

1
votes

I am trying to insert data from one BigQuery table to another using an Airflow DAG. I want to filter data such that the updateDate in my source table is greater than the previous execution date of my DAG run.

The updateDate in my source table looks like this: 2021-04-09T20:11:11Zand is of STRING data type whereasprev_execution_datelooks like this:2021-04-10T11:00:00+00:00which is why I am trying to convert myupdateDate` to TIMESTAMP first and then to ISO format as shown below.

SELECT *
FROM source_table
WHERE FORMAT_TIMESTAMP("%Y-%m-%dT%X%Ez", TIMESTAMP(UpdateDate)) > TIMESTAMP('{{ prev_execution_date }}')

But I am getting the error message: No matching signature for operator > for argument types: STRING, TIMESTAMP. Supported signature: ANY > ANY. Clearly the left hand side of my WHERE-clause above is of type STRING. How can I convert it to TIMESTAMP or to a correct format for that matter to be able to compare to prev_execution_date?

I have also tried with the following:

WHERE FORMAT_TIMESTAMP("%Y-%m-%dT%X%Ez", TIMESTAMP(UpdatedWhen)) > STRING('{{ prev_execution_date }}')

which results in the error message: Could not cast literal "2021-04-11T11:50:31.284349+00:00" to type DATE

I would appreciate some help regarding how to write my BigQuery SQL query to compare the String timestamp to previous execution date of Airflow DAG.

1
google-bigqueryairflow

1 Answers

0
votes

Probably you wanted to try parse_timestamp instead:

SELECT *
FROM source_table
WHERE PARSE_TIMESTAMP("%Y-%m-%dT%X%Ez", UpdateDate) > TIMESTAMP('{{ prev_execution_date }}')

although looks like it will work even without it

SELECT *
FROM source_table
WHERE TIMESTAMP(UpdateDate) > TIMESTAMP('{{ prev_execution_date }}')