Create Data Hackathon DB

CodeForPhilly / ops

File an issue here to deploy an application or create a new repo

3 stars 4 forks source link

Create Data Hackathon DB #45

Closed mariekers closed 4 years ago

mariekers commented 5 years ago

Need a protected db (SQL-based, don't care what tech) to house data generated for the upcoming data hack. Data may contain non-public datasets (no PII or PHI) so cannot be available in git repo.

mariekers commented 5 years ago

Will need to have user levels:

read/write
read only

themightychris commented 5 years ago

@lottspot feel like taking cockroachdb for a spin? I think this request is intended just for a temporary deployment of a month or so we wouldn't have to worry about long-term maint

mariekers commented 4 years ago

@themightychris @lottspot the gentlest of prods. A puff of wind against your back of prods.

machow commented 4 years ago

@themightychris I agreed to take up this task from the hackathon crew side! Can you update me on where things are?

Took a rough pass at outlining task below, but very open to modification

Key checkpoints

[ ] Marieke or Michael can access all raw data sent by partners
- [ ] TODO (@mariekers): list out each data source and where it lives
[x] database exists
[ ] strategy for putting data in db (either format you need, or credentials for one of us to do it)
[ ] test run taking partner data and putting in db (even if it involves us taking their data, and outputting fake data into db)
[ ] tiny example analyses in R and Python
[ ] a hackathon attendee, who has signed the BAA, runs the example analyses on their laptop.

Nice to haves

ability for a one person or a couple people to update / add data sources (either tables or materialized views?)

themightychris commented 4 years ago

@machow let me check, the ops team had a session to deploy this and we ran into our k8s cluster that was set up with Linode's alpha offering had support dropped so we had to destroy it...and then our Linode account didn't have the beta k8s service enabled so we opened a support ticket...and they got it turned on for us a couple hours later but I'm not sure whether anyone was still around to finish the deploy at that point

we should have a clear path to wrap it up now though

lottspot commented 4 years ago

Passed connection info to @machow via slack. Awaiting confirmation that access works as expected

machow commented 4 years ago

Thanks @lottspot! Was able to connect to the db and add a table using this python code

from sqlalchemy import create_engine
import pandas as pd
engine = create_engine("postgresql://{USERNAME}:{PASSWORD}@45.79.16.246:30001/postgres")

mtcars = pd.DataFrame({'a': [1,2,3], 'b': [4,5,6]})
mtcars.to_sql("mtcars", engine, if_exists = "replace")

I'll sync with @mariekers to lay out how the hackathon wants to use the db and how they want to get the data in.