What is the appropriate way to load data from Firestore into BigQuery?

Question

I was looking at a few ways to export data out from Firestore without using export (expensive operation in the long term as it doesn't support incremental backups) to use in BigQuery and Data Studio.

1) Using Google Pub/Sub. This will probably require function to both write to pub/sub and then another to trigger to BQ.

2) Using Cloud Functions to trigger from an onCreate event to write directly to a BigQuery dataset and table. (This is using table.insert)

What would the advantage be to use Pub/Sub - other than it would appear that it will cost more in the long term?

Or is there another way I am unaware of to do this?

I'm new at this. Some advise and pro and cons of the above scenarios are much appreciated.

Answer 1

The official solution is here.

In case of using Cloud Functions to trigger from an onCreate event, what will you create? Create File on Cloud Storage or create Firestore Document?

I think that in case of using Cloud Functions you should use PubSub trigger.

I recommend asynchronous architecture like Pub/Sub. Because rerun is easy and the scope of influence is limited.

I developed sample is here. I'm using Cloud Scheduler not cron.yaml. The cost of Cloud Scheduler is here.

1. (If you want) Export Firebase Authentication users to Cloud Firestore Collection. Use Firestore, Cloud Functions(PubSub) and Cloud Scheduler.
2. Export All Cloud Firestore Collections and Specified collections to Cloud Storage. Use AppEngine and Cloud Scheduler.
3. Export Specified Cloud Firestore Collections to BigQuery( as Partitioned tables). Use AppEngine and Cloud Scheduler.