As you know BigQuery user interface lets you do all kinds of things like run an interactive query or batch, save as Table, export to table, etc. — but there is no scheduler yet to schedule a query to run at a specific time or periodicity.
To be clear: once BigQuery has scheduled queries, you want to use that, so that you can keep your data in BigQuery and take advantage of power. However, if you are doing transformations (the T in ETL), then consider this approach:
- In the BigQuery UI, save the desired query as a View.
- In Cloud Dataprep, write a new recipe, with a BigQuery source. Optionally, add some transforms to your recipe. For example, you might want to add some formulas, de-deduplication, transformations, etc.
- An export result of the transformation to a BigQuery table or CSV file on Cloud Storage
- Schedule the Dataprep flow to run periodically
Go to the “Flows” section of the Dataprep UI and click on the three buttons next to your new Flow. You’ll see an option to add a schedule:
If the UI is different when you try to replicate the steps, just hunt around a bit. The functionality is likely to be there, just in a different place.
Options include daily, weekly, etc. but also a crontab format for further flexibility that’s it.
Have a nice querying.