This walkthrough goes through the steps of taking data from a MySQL database using RMySQL and loading it into an existing Socrata table using write.Socrata from RSocrata

This walkthrough assumes that you already have an instance of MySQL running on your machine. If that is not the case please stop and install that before continuing.

Step 1: Install Packages

install.packages('RMySQL')
install.packages('RSocrata')

Step 2: Connect to MySQL instance

Create constants so that R can access MySQL instance

con <- dbConnect(MySQL(),
    user = 'mysql',
    password = 'YourPass',
    host = 'RDS Host',
    dbname='YourDB')

Step 3: Write to Data Frame

Use con to write to data.frame

table <- dbWriteTable(conn = con, name = 'Test')

Step 4: Identify Destination Dataset

Ensure that you have a Socrata dataset with proper fields and proper datatypes to write to. Capture the 4x4 ID for the dataset you want to publish to.

Step 5: Publish to Your Dataset

socrataEmail <- Sys.getenv("SOCRATA_EMAIL", "XXX@socrata.com")
socrataPassword <- Sys.getenv("SOCRATA_PASSWORD", "XXXXXXX")
datasetToAddToUrl <- "https://opendata.socrata.com/resource/evnp-32vr.json" 
write.socrata(table,datasetToAddToUrl,"UPSERT",socrataEmail,socrataPassword)

Step 6: Done!

Enjoy your new dataset!