How to export Firestore data to BigQuery?

 

Step 1: Set Up Firebase and BigQuery

 

Make sure you have a Firebase project and a Google Cloud project. Ensure that both Firestore and BigQuery services are enabled. You need Firestore to have data to export and BigQuery to be active for data import.

 

Step 2: Link Firebase to your Google Cloud Project

 

1. Open the Firebase console.
2. Navigate to the Project Settings by clicking on the gear icon.
3. Ensure your Firebase project is linked to a Google Cloud project. If not, link it.
4. Access the service accounts tab and generate a new private key. Download the JSON file after generating.

 

Step 3: Enable Billing and BigQuery API

 

1. Go to the Google Cloud Console.
2. Enable billing for your project if it's not already enabled.
3. Navigate to the APIs & Services.
4. Search for and enable the BigQuery API.

 

Step 4: Set Up Firestore Export Function

 

You need to write a Cloud Function to trigger data export from Firestore to Cloud Storage. Later, you will load this data into BigQuery.

const functions = require('firebase-functions');
const admin = require('firebase-admin');
const { BigQuery } = require('@google-cloud/bigquery');
const { Firestore } = require('@google-cloud/firestore');
const firestore = new Firestore();

admin.initializeApp();

exports.exportFirestoreToBigQuery = functions.pubsub
    .schedule('every 24 hours')
    .onRun(async (context) => {
      const datasetId = 'your_dataset_id';
      const tableId = 'your_table_id';
      const collectionName = 'your_collection_name';

      const bigquery = new BigQuery();

      const query = firestore.collection(collectionName);
      const snapshot = await query.get();
       
      const rows = [];
      snapshot.forEach(doc => {
        rows.push(doc.data());
      });

      await bigquery
        .dataset(datasetId)
        .table(tableId)
        .insert(rows);

      console.log(`Exported ${rows.length} rows to BigQuery`);
      return null;
    });

 

Step 5: Deploy Your Cloud Function

 

Make sure your Firebase environment is set up to deploy functions.

firebase deploy --only functions:exportFirestoreToBigQuery

 

Step 6: Verify Data in BigQuery

 

1. Navigate to Google Cloud Console.
2. Access BigQuery.
3. Check your dataset and table to confirm that the Firestore data has been exported.

 

Step 7: Set Up Scheduled Exports (Optional)

 

If you need to regularly update BigQuery with new Firestore data, consider setting up a Cloud Scheduler. This has been partially accomplished with the use of the functions.pubsub.schedule that already schedules the function.

1. Go to the Google Cloud console.
2. Navigate to Cloud Scheduler.
3. Set up a new scheduled job if needed outside the current schedule.

 

Conclusion

 

This step-by-step guide helps you export Firestore data to BigQuery. Ensure all service accounts, permissions, and billing configurations are correctly set to avoid interruptions in data transfers.