Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Make API generate CSV extracts of configured tables #3148

Closed
1 task
chouinar opened this issue Dec 9, 2024 · 0 comments · Fixed by #3153
Closed
1 task

Make API generate CSV extracts of configured tables #3148

chouinar opened this issue Dec 9, 2024 · 0 comments · Fixed by #3153
Assignees
@chouinar

Comments

@chouinar
Copy link
Collaborator

chouinar commented Dec 9, 2024

Summary

Tables to configure:

  • Opportunity
  • Opportunity summary
  • Current opportunity summary
  • Opportunity Category
  • Opportunity Status

CSVs should be generated into a configured environment variable - which will be an s3 path non-locally.

All columns should be written.

Acceptance criteria

  • Extracts generated
@chouinar chouinar moved this from Icebox to In Progress in Simpler.Grants.gov Product Backlog Dec 9, 2024
@chouinar chouinar self-assigned this Dec 9, 2024
chouinar added a commit that referenced this issue Dec 9, 2024
@chouinar
[Issue #3148] Generate extracts of tables for opportunity data
1a0a6c9
@chouinar chouinar mentioned this issue Dec 9, 2024
Merged
@chouinar chouinar moved this from In Progress to In Review in Simpler.Grants.gov Product Backlog Dec 10, 2024
chouinar added a commit that referenced this issue Dec 16, 2024
@chouinar
[Issue #3148] Generate extracts of tables for opportunity data (#3153)
eb45d02 
## Summary
Fixes #3148

### Time to review: __10 mins__

## Changes proposed
Create a task we can run that will generate CSV extracts for a given set
of tables

## Context for reviewers
This dataset will be a sort of input to an analytics process being
written. We'll pull these extract files and load them into the analytics
database.

This uses pretty routine `copy` commands from Postgres which is very
very efficient at reading/writing from CSV files.
https://www.postgresql.org/docs/current/sql-copy.html

## Additional information
Running `make cmd args="task create-analytics-db-csvs"` locally to get
the script going with ~35k opportunities in my DB takes about 2 seconds
for these 5 tables:
![Screenshot 2024-12-09 at 4 48
06 PM](https://github.com/user-attachments/assets/36dc8df4-41fc-4db4-a715-e240404f2fa7)

Example files generated:

### Opportunity
```csv
opportunity_id,opportunity_number,opportunity_title,agency_code,opportunity_category_id,category_explanation,is_draft,revision_number,modified_comments,publisher_user_id,publisher_profile_id,created_at,updated_at
"1","HHS-OPHS-02-685","Donaldson-Montgomery 2004 award","DOI-BIA","2",,"f","0",,,,"2024-12-09 19:47:53.327412+00","2024-12-09 19:47:53.327412+00"
"2","THUS-824-49","Embassy program for Sport and exercise psychologist in Thailand","DOD-AMRAA","2",,"f","0",,,,"2024-12-09 19:47:53.385385+00","2024-12-09 19:47:53.385385+00"
"3","LEAST-443-18","Embassy program for Multimedia specialist in Bangladesh","USDA-FAS","2",,"f","0",,,,"2024-12-09 19:47:53.423241+00","2024-12-09 19:47:53.423241+00"
"4","HHS-IHS-07-253","Jones and Sons 1999 award","DOD-COE-FW","5","Include while thought.","f","0",,,,"2024-12-09 19:47:53.448716+00","2024-12-09 19:47:53.448716+00"
"5","DOI-USGS1-54-707","Austin Boyd Foundation Grant for cultivate collaborative niches","DOE-NETL","5","Successful.","f","0",,,,"2024-12-09 19:47:53.473957+00","2024-12-09 19:47:53.473957+00"
"6","DOC-EDA-40-090","Christine Garcia Foundation Grant for enable best-of-breed convergence","HHS-CDC-NCCDPHP","5","Degree family.","f","0",,,,"2024-12-09 19:47:53.510048+00","2024-12-09 19:47:53.510048+00"
```

### Current Opportunity Summary
```csv
opportunity_id,opportunity_summary_id,opportunity_status_id,created_at,updated_at
"1","1","1","2024-12-09 19:47:53.373247+00","2024-12-09 19:47:53.373247+00"
"2","2","1","2024-12-09 19:47:53.41556+00","2024-12-09 19:47:53.41556+00"
"3","3","1","2024-12-09 19:47:53.441631+00","2024-12-09 19:47:53.441631+00"
"4","4","1","2024-12-09 19:47:53.46677+00","2024-12-09 19:47:53.46677+00"
"5","5","1","2024-12-09 19:47:53.489682+00","2024-12-09 19:47:53.489682+00"
```

### Opportunity Summary

```csv
opportunity_summary_id,opportunity_id,summary_description,is_cost_sharing,is_forecast,post_date,close_date,close_date_description,archive_date,unarchive_date,expected_number_of_awards,estimated_total_program_funding,award_floor,award_ceiling,additional_info_url,additional_info_url_description,forecasted_post_date,forecasted_close_date,forecasted_close_date_description,forecasted_award_date,forecasted_project_start_date,fiscal_year,revision_number,modification_comments,funding_category_description,applicant_eligibility_description,agency_code,agency_name,agency_phone_number,agency_contact_description,agency_email_address,agency_email_address_description,is_deleted,can_send_mail,publisher_profile_id,publisher_user_id,updated_by,created_by,created_at,updated_at,version_number
"1","1","DOD-AFRL is looking to further investigate this topic. Machine blue door few market team run. Chance letter standard.","t","t","2024-12-05",,,"2025-01-02",,"2","7095000","3547500","7095000","sam.gov","Full Announcement","2024-12-29","2025-02-19","Speech defense finally.","2025-06-14","2025-07-21","2025",,,,,"DOI-BIA","Agency for Interior","123-456-0000","google.com Contact Center
Hours of operation are 24 hours a day, 7 days a week.
[email protected]","[email protected]","Contact Agency for Interior via email","f",,,,,,"2021-11-16 15:04:04.542119+00","2022-02-26 04:08:04.982404+00","12"
"2","2","The purpose of this Notice of Funding Opportunity (NOFO) is to support research into Pensions consultant and how we might Implemented contextually-based application.","f","t","2024-12-04",,,"2025-01-05",,"5","7345000","1469000","7345000","grants.gov","Program Announcement","2024-12-24","2025-02-27","Public environmental outside couple each common baby.","2025-06-12","2025-11-09","2025",,,"Participant trip up.","New open ready likely possible. Audience set doctor. Before or degree must out turn why. Billion example know woman. Big wide city pass too wide. Participant edge chair budget.","DOD-AMRAA","Agency for Housing","123-456-0001","Webmaster
[email protected]","[email protected]","Contact Agency for Housing via email","f",,,,,,"2021-09-18 07:41:17.520437+00","2023-02-01 08:42:17.682068+00","10"
```

### Opportunity Category
```csv
opportunity_category_id,description,created_at,updated_at
"1","discretionary","2024-12-09 19:46:40.288036+00","2024-12-09 19:46:40.288036+00"
"2","mandatory","2024-12-09 19:46:40.290119+00","2024-12-09 19:46:40.290119+00"
"3","continuation","2024-12-09 19:46:40.292564+00","2024-12-09 19:46:40.292564+00"
"4","earmark","2024-12-09 19:46:40.293851+00","2024-12-09 19:46:40.293851+00"
"5","other","2024-12-09 19:46:40.295182+00","2024-12-09 19:46:40.295182+00"
```

### Opportunity Status
```csv
opportunity_status_id,description,created_at,updated_at
"1","forecasted","2024-12-09 19:46:40.366307+00","2024-12-09 19:46:40.366307+00"
"2","posted","2024-12-09 19:46:40.367838+00","2024-12-09 19:46:40.367838+00"
"3","closed","2024-12-09 19:46:40.369048+00","2024-12-09 19:46:40.369048+00"
"4","archived","2024-12-09 19:46:40.370318+00","2024-12-09 19:46:40.370318+00"
```
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@chouinar chouinar

Labels
None yet
Projects
Simpler.Grants.gov Product Backlog
Status: Done
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

[Issue #3148] Generate extracts of tables for opportunity data HHS/simpler-grants-gov
1 participant
@chouinar