Skip to content

Instantly share code, notes, and snippets.

@hannes
Created October 18, 2015 14:01
Show Gist options
  • Save hannes/34fd99a0184270d5f20f to your computer and use it in GitHub Desktop.
Save hannes/34fd99a0184270d5f20f to your computer and use it in GitHub Desktop.
library(MonetDB.R)
files <- dir("/Users/hannes/Desktop/PAMAP2_Dataset/Protocol/", pattern="*.dat", full.names=T)
tnames <- paste0("pamap2_", sub("^([^.]*).*", "\\1", basename(files)))
snames <- sub(".*(\\d{3})$", "\\1", tnames)
loadsql <- c("DROP VIEW pamap2", paste0("DROP TABLE ",tnames,"; "), paste0("CREATE TABLE ",tnames," (timestamp float, activityID int, heart_rate int, hand_temp float, hand_acc_16_1 float, hand_acc_16_2 float, hand_acc_16_3 float, hand_acc_6_1 float, hand_acc_6_2 float, hand_acc_6_3 float, hand_gyro_1 float, hand_gyro_2 float, hand_gyro_3 float, hand_mag_1 float, hand_mag_2 float, hand_mag_3 float, hand_or_1 float, hand_or_2 float, hand_or_3 float, hand_or_4 float, chest_temp float, chest_acc_16_1 float, chest_acc_16_2 float, chest_acc_16_3 float, chest_acc_6_1 float, chest_acc_6_2 float, chest_acc_6_3 float, chest_gyro_1 float, chest_gyro_2 float, chest_gyro_3 float, chest_mag_1 float, chest_mag_2 float, chest_mag_3 float, chest_or_1 float, chest_or_2 float, chest_or_3 float, chest_or_4 float, ankle_temp float, ankle_acc_16_1 float, ankle_acc_16_2 float, ankle_acc_16_3 float, ankle_acc_6_1 float, ankle_acc_6_2 float, ankle_acc_6_3 float, ankle_gyro_1 float, ankle_gyro_2 float, ankle_gyro_3 float, ankle_mag_1 float, ankle_mag_2 float, ankle_mag_3 float, ankle_or_1 float, ankle_or_2 float, ankle_or_3 float, ankle_or_4 float)"), paste0("COPY INTO ",tnames," FROM '",files,"' USING DELIMITERS ' ','\\n','' NULL AS 'NaN'"), paste0("CREATE VIEW pamap2 AS ", paste0("SELECT '",snames,"' AS subject, ",tnames,".* FROM ", tnames, collapse=" UNION ALL "), ";"))
con <- dbConnect(dbDriver("MonetDB"), dbname="pamap2")
system.time({
dbBegin(con)
lapply(loadsql, function(s) dbSendQuery(con, s))
dbCommit(con)
})
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment