[firestore-bigquery-export] How to back fill in a partitioned table?

[Naihell]

Hey there!

I don't know if it is a silly question, but I am stuck in a problem for a few days.

I am using BigQuery export extension, but I need to backfill my table using a collection with about 900k records. My first problem happens when I try to use the 0.1.40 version, that had the option to backfill. If I try this version on small collections, everything works perfectly, but if I try the same version on my 900k records, it doesn't work. After a long time, I have no errors, but cloud function only got about 400k records, tried the same process twice.

I also tried another approach using latest extension version (0.1.43) and fs-bq-import-collection script. In this situation I got precisely all records, but I can't have my time partitioned column fulfilled.

It would be great to run backfill my data and also have my partitioned column correctly fulfilled. Any tips about how to proceed?

[KristofSochan]

Hi @Naihell,

I've run into the same issue recently using the fs-bq-import-collection script. Even if I configure the extension to use backfill, the script thinks no partitioning is set up. There's an unresolved bug report for it here that I tried to follow up on yesterday.

My solution was to write a DML query that manually writes in the column on any record it is currently missing. I can confirm that I am able to write queries that take advantage of the partitioned data now.

The query was this:

UPDATE `project-name.dataset-id.users_raw_changelog`
SET creation_timestamp = TIMESTAMP_SECONDS(CAST(JSON_VALUE(data, '$.creation_timestamp._seconds') AS INT64) +
  CAST(CAST(JSON_VALUE(data, '$.creation_timestamp._nanoseconds') AS INT64) / 1000000000 AS INT64))
WHERE creation_timestamp IS NULL
  AND JSON_VALUE(data, '$.creation_timestamp._seconds') IS NOT NULL
  AND JSON_VALUE(data, '$.creation_timestamp._nanoseconds') IS NOT NULL;

Hopefully you're able to adapt this to your use case.

[Naihell]

Hi, @KristofSochan! I really appreciate your time trying to help me here. It's been a while since my last problem using firestore extension, at that time the export function didn't work moving data between projects, but some guy had an approach in a different branch that worked for me (unfortunatelly I didn't find the link to be referenced here). Anyaway, as far as I know, current export script already have this feature.