Seq Lab: Migrate Firestore Flowcell Report from Firestore to BigQuery

tim-snyder-wustl commented 1 month ago

[x] For now, copy BigQuery resource for a testing resource. This will be updated with a second resource that will require a full data dump from Firestore around time of transition
[x] Add BigQuery sdk/creds to ESP
[x] Fork report scripts to pull from BigQuery instead of Firestore
[x] Update demux endpoints to additionally write data to BigQuery - This could actually be the endpoint because users would be able to run them from ESP.
[x] Create gtac webtool for users to run query outside of ESP that uses report scripts to create downloadable report

Using BigQuery consolidates DB resources without adding another Database app.
BigQuery is a more appropriate resource than a relational DB app.
There is already basic development completed for using BigQuery
This will serve as a backup to FireStore and remove the need to migrate if Firestore is ever discontinued.
Much easier to migrate if BigQuery is ever discontinued.
A complete ingest of everything in FireStore and the addition of new data will provide an easily queryable resource with all historic data (minus Smartsheet for now), this will supplement the QC app gold standard data set. And be a much better alternative to FS.
The foundations of this more basic project will most likely be the basis for the QC app as their is significant overlap which will enable that project to move forward in tandem. And a sooner completion time frame for the QC app as the foundational requirements will be in place
In the event Flowcell query becomes obsolete in the very near future there is an improved resource available.
Reduced run times, no user should be required to wait 10 minutes for a DB query to complete.

billtell commented 1 month ago

Firestore DB Dump

[ ] maintain firestore field names (no need for conversion dicts)
[ ] use complete_df
[ ] grab SR fields:
[ ] accommodate new library fields (i.e. don't trim fields, bring in everything)

To resolve:

check database dump merge for duplicate columns between flowcell_df and complete_df
illumina_sequence_analysisaws_s3_bucket
index_level_0

billtell commented 1 month ago

Endpoint Plugin and Testing

[x] new endpoint Class function - convert to df , send to BigQuery
[x] add to 3 demux endpoints

LMT337 commented 1 month ago

Verify output_folder field is imported correctly before live import from FS to BQ.

billtell commented 1 month ago

make sure index2_file brought into table

[x] added to schema json
[x] added to type_conversion dict in bigquery endpoint class

tim-snyder-wustl commented 1 month ago

[ ] Fix the download link in the UI so it's not so wonky

billtell commented 2 weeks ago

Initialize process

[x] Dump Firestore db
[x] drop unused columns:
json_version
illumina_sequence_analysisaws_s3_bucket
duplicatate
[x] Use table_load_with_schema_config.ipynb to load the table
make sure table name is changed to permanent name

billtell commented 2 weeks ago

[x] create new endpoint log files for post_bq era
[x] add ic logging statement
[x] add ic error statements in PayloadBigQuery class

billtell commented 1 week ago

Adding:

# Replace None with 0 in numeric columns specified in TYPE_CONVERSION_DICT
for col, dtype in self.TYPE_CONVERSION_DICT.items():
    if dtype in ["int64", "float64"]:
        df[col] = df[col].fillna(0)

to address this error: TypeError: int() argument must be a string, a bytes-like object or a number, not 'NoneType'

GTAC-MGI / GTAC-ESP-LIMS