Exporting collections and subcollections from Firestore to BigQuery

First step is to select your project if not selected. Here, my project id is bigquery-sandbox-281102 and the highlighted yellow text in project id shows that it is already selected. If it is not activated, you have to select project id by typing:

gcloud config set project [project_id]

My project is activated. The yellow highlighted text is my projectid.

After selecting your project, make sure you have necessary access of bigquery and firebase to import and export data. Your service account json file stores the credentials of the permissions to access cloud services. I created dataexport folder to keep the necessary files for this task. You can do same to keep files in an organized way. You should have the following three files in a directory: importbq.ts, package-lock.json and service-account-key.json. If npm is not installed, you have to make sure you installed npm to install dependencies. Install the dependencies mentioned below staying at this particular directory. You will find the file importbq.ts and package-lock.json in the link below. You only need to have service account json of your own for your access to services.

Files needed in the same directory.

Link to the above files: https://github.com/iampujan/firestore2bq

service account json file should be your own. Now you have to install the following dependencies one by one in the same cloud shell.

npm install typescript
npm install — save firebase-admin
npm install commander --save
npm install inquirer
npm i @firebaseextensions/firestore-bigquery-change-tracker

After successfully installing these dependencies, now you are ready to enter the final command. Type the command below in your google shell giving your own path for service account JSON file.

GOOGLE_APPLICATION_CREDENTIALS=”/home/tpujan15/dataexport/service-account-key.json” npx ts-node importbq.ts

After executing this file successfully, you will be asked about your firebase project id which is the same project id you used above.

Then you will be asked to enter collections/subcollections name. collections/sub-collections name should be unique. Do not be confused about the path of sub-collections. Just simply type the name of sub collections. It will recognize sub-collection as sub-collection should be unique.

Then, type dataset name you want in your bigquery.

After giving name to your dataset, you can give name to the table for your dataset of your choice.

This steps successfully loads your Firestore database to BigQuery loading all existing data of the given collection/subcollection.

Note: This cycle of steps loads one collection/sub-collection at a time. To load another collection/sub-collection repeat this cycle giving different collection/sub-collection name.

The updated data changes are not shown. To update the data, you should use the following extension giving the proper path name of collection/sub-collection. If there is documents in middle of collection and sub-collection, use wildcards in the middle. Click here to go to the extension link. There is a billing charge for using this extension. You will also get proper way to configure extension in the documentation from the same link.

Extension to export collections/subcollections to BiqQuery

Remember, this extension won’t load existing data. The use of script is to load existing data and the use of this extension is to retrieve new data as new data are not load by the script or you have to keep running script whenever you want to load but the extension will retrieve data automatically from Firestore.