selectFromDB:: hook to real data

[famulare]

Once we have the handoff credentials set up, we need to add SQL hooks to the real database. We should also maintain the ability to connect to the simulated data for prototyping and model validation.

[famulare]

Hi @grhuynh . Cool getting the hook to the real database in! https://github.com/seattleflu/incidence-mapper/tree/master/dbViewRSQL

I think we should add this to dbViewR::selectFromDB and make the default the real database, and add a flag to pull from the simulated data instead.

If you swap the few lines with allData and the first db definition in dbViewR::selectData (

incidence-mapper/dbViewR/R/selectFromDB.R

Line 44 in d4c960a

rawData <- RCurl::getURL("https://raw.githubusercontent.com/seattleflu/simulated-data/master/simulatedSubjectDatabase.csv")

), it might "just work."

I think it'll probably take me 30 minutes to do it, so I assigned this ticket to myself. But if you want to have a go, take it!

[famulare]

How do credentials work? Is it interactive, or do I have to point it to my local credentials?

incidence-mapper/dbViewRSQL/selectFromDB_SQL.R

Line 57 in 88f63f1

db <- dbConnect(RPostgres::Postgres(), host="production.db.seattleflu.org", dbname = 'production', user='', password='')

[grhuynh]

I haven't figured out how to get it R working with local credentials, even though command line access works with local credentials - for the above literally just paste in the username and pw that Thomas sent with keybase/GPG (might have sent it to Clinton if you didn't get them yet. I think I saw that go by on Slack.

[famulare]

Okay. The cheap solution will probably be to point to a personal credential file that doesn't get checked in, but we'll see what @devclinton suggests. I'll be chipping away very slowly at some stuff over the next few days between symposium talks.

[famulare]

started a branch to work through db connection without breaking master: https://github.com/seattleflu/incidence-mapper/tree/connect-to-real-data

[tsibley]

It should definitely be possible to use a pgpass and pg_service.conf file to make the database connection line simpler and not tied to a particular set of credentials or connection details.

[grhuynh]

@famulare and @tsibley got connection to pgpass and pg_service working, you just have to type in the directory to find those files at the top of dbViewR_SQL.R. Assuming you can connect to the database at the command line, then this should work!

[famulare]

works on connect-to-real-data branch as of 12949d1. See #40 and #31 if you're having issues connecting from your local docker container with your database credentials from @tsibley .