Export Firebase to Bigquery dataset time to live

Question

Update: I found a solution for my first question meaning changing to never expires:

I applied this code to my dataset and the firesbase console now showing never expires

bq update --default_partition_expiration 0 myotherproject:mydataset

But there is still the second question which is how to retrieve back the data that got expired because the default option was to keep only the last 60 days. ( before someone ask , yes I did start the export and the table was available 3 month ago , it's not a problem of activating the functionality but more a problem that the table expired as we can see on my first screenshot it was written 170 days of expired data , I wonder how to get them back )

End of update

I have activated the export from firebase to google bigquery but the time to live is block at 60 days even though I am using the blaze plan cf screenshot :

Is there a way to change the dataset to live to never expire as I am losing my historical data. Note that i was able to have that by default some time back on another project that looked like this but I cannot find any feature to change it on firebase console

Note that I have also change the expiry time on data retention in GA4F to 14 month but it doesn't change anything

Answer 1

If you are not on a paid plan, then the retention of Google Analytics data in BigQuery is set permanently to 60 days.

If you are on a paid plan, then tables created after you set the TTL will observe the setting.

For pre-existing tables you can generate commands to update them with a query like the following:

SELECT CONCAT("ALTER TABLE `<PROJECT_ID>.analytics_<PROPERTY_NUM>.", table_id, "` SET OPTIONS (expiration_timestamp=NULL);") AS cmd
FROM `<PROJECT_ID>.analytics_<PROPERTY_NUM>.__TABLES_SUMMARY__`
WHERE table_id LIKE 'events_2021%'

As mentioned in comments, you can also update partitions from the gcloud CLI with a command similar to this:

bq update --default_partition_expiration 0 projectid:datasetname

Answer 2

I faced the same issue and here's the complete step to change "Dataset time to live" to "Does not expire". Before going into the solution, please be aware that the analytics data in BigQuery is structured as below.

Project

? Dataset (e.g. analytics_<PROPERTY_NUM>)

?? Partitioned Table (e.g. event_)

Step1. Update the Dataset's default table expiration

The official doc is here: https://cloud.google.com/bigquery/docs/updating-datasets#table-expiration

You can run the below command or update from the GCP console.

When using GCP console, make sure to open the "Dataset Details" by clicking 3 dots right to the dataset name. If you click table (e.g. events_(60)), that will open the individual partitioned table setting and you cannot update the dataset property!!

bq update --default_table_expiration 0 <PROJECT ID>:analytics_<PROPERTY_NUM>

Step 2. Update the Dataset's default partition expiration time

The official doc is here: https://cloud.google.com/bigquery/docs/updating-datasets#partition-expiration

You can run below command. This cannot be done in GCP Console.

bq update --default_partition_expiration 0 <PROJECT ID>:analytics_<PROPERTY_NUM>

Step 3. Update the existing Partitioned Table's expiration time

You can use the command answered in https://stackoverflow.com/a/68353669/18035137 by Kato.

SELECT CONCAT("ALTER TABLE `<PROJECT_ID>.analytics_<PROPERTY_NUM>.", table_id, "` SET OPTIONS (expiration_timestamp=NULL);") AS cmd
FROM `<PROJECT_ID>.analytics_<PROPERTY_NUM>.__TABLES_SUMMARY__`
WHERE table_id LIKE 'events_2021%'

Once you finish the above 3 steps, you can check Firebase project setting and the "Dataset time to live" is immediately changed to "Does not expire".