Extensions: firestore-bigquery-export ability to export to partitioned tables

[kevinkaar]

This feature request is for extension: firestore-bigquery-export

Would really help with billing to be able to export firestore collections to BigQuery using partitioned (e.g by ingestion time, daily) tables.

[russellwheatley]

Hi @kevinkaar, thanks for the suggestion. As far as I can tell from the BigQuery nodejs documentation, it doesn't seem possible to be able to create partitioned tables. It would have to be via the API. Not sure if this is something the Firebase Extensions will be considering in the short term, but I'm sure they will consider it.

[seansaleh]

It does appear to be possible, a link from their docs: https://github.com/googleapis/nodejs-bigquery/blob/master/samples/createTablePartitioned.js

const options = {
      schema: schema,
      location: 'US',
      timePartitioning: {
        type: 'DAY',
        expirationMs: '7776000000',
        field: 'date',
      },
    };

[russellwheatley]

@seansaleh nice, I didn't see that 😄

[russellwheatley]

Hey @kevinkaar, the Firebase Extension's team have reviewed this idea. As you're probably aware, there are a multitude of features available for BigQuery and that scope has to be limited to a degree. Therefore, the use of partitioned tables has been deemed outside the purview of the BigQuery Firestore extension. Thanks once again for the suggestion though, if you do have anymore, feel free to raise another issue.

[mustafaekim]

Hi, if the extension cannot create a partitioned table, what is the right approach to export a collection of millions of objects into a big query table? If I use the extension as it is today, the queries become very slow due to the full scan. Before I started to use the extension, I was thinking like I would add indexes into the bigquery table but that does not exist at all. So what is the solution?

[amirulmanaf]

Hi, if the extension cannot create a partitioned table, what is the right approach to export a collection of millions of objects into a big query table? If I use the extension as it is today, the queries become very slow due to the full scan. Before I started to use the extension, I was thinking like I would add indexes into the bigquery table but that does not exist at all. So what is the solution?

agree. there's a growing concern here because the tables keep growing and the nature of the exported bq table from firestore is such that they aren't immediately usable without a scheduled query running to clean up and rearranging the data on it in order to be consumed by datastudio for example. and when the table is too large for the BI engine to support, it becomes a requirement that the connecting tables become partitioned ones. i understand we can easily run those scheduled transforms to feed a partitioned table, but the source table fed by the extension is quickly becoming this gigantic monster of a non-partitioned table, which would then be really expensive to run the scheduled transforms on.

[amirulmanaf]

Hi, if the extension cannot create a partitioned table, what is the right approach to export a collection of millions of objects into a big query table? If I use the extension as it is today, the queries become very slow due to the full scan. Before I started to use the extension, I was thinking like I would add indexes into the bigquery table but that does not exist at all. So what is the solution?

agree. there's a growing concern here because the tables keep growing and the nature of the exported bq table from firestore is such that they aren't immediately usable without a scheduled query running to clean up and rearranging the data on it in order to be consumed by datastudio for example. and when the table is too large for the BI engine to support, it becomes a requirement that the connecting tables become partitioned ones. i understand we can easily run those scheduled transforms to feed a partitioned table, but the source table fed by the extension is quickly becoming this gigantic monster of a non-partitioned table, which would then be really expensive to run the scheduled transforms on.

found a workaround to this. all we have to do is create a timestamp-partitioned table with the exact same contents the original target table fed by the extension, delete the original table, and replace it with another partitioned table with the same name as the original.

what happens now is the extension will start to perform streaming inserts to the new partitioned table and it does so more efficiently by looking at those partitions and inserting them to the correct one as per the timestamp. this also means our scheduled transforms and BI engine / datastudio would also be much more cost-efficient when drawing from these tables.

[russellwheatley]

Hi @amirulmanaf, as a quick update on this. The FE team are reconsidering allowing partitioned tables as an option (on fresh extension installations, you cannot change table type once instantiated). One of the concerns will be to assess whether there are any unforeseen side effects to allowing partitioned tables.

I've ran some preliminary tests and there doesn't appear to be an issue. But I'd be grateful if you could also update this issue feed if you run into any problems of your own. Thanks!

[amirulmanaf]

hi @russellwheatley. so far i've not seen any issues on my side with the extension working on those partitioned tables. will post here as we find them.

many thanks and superb job on the extensions, FE team!