search

ccb-hms / nhanes-database

3 stars 4 forks source link

DB insert via CSV vs DBI::dbAppendTable() #175

Closed deepayan closed 6 months ago

deepayan commented 6 months ago

The data tables are currently inserted into the DB via export to CSV file, starting here:

https://github.com/ccb-hms/NHANES/blob/main/Code/R/download.R#L305

I would be interested in knowing how this compares in terms of performance with an approach based on DBI::dbCreateTable() + DBI::dbAppendTable().

deepayan commented 6 months ago

Also, at https://github.com/ccb-hms/NHANES/blob/main/Code/R/download.R#L316, changing 

createTableQuery = DBI::sqlCreateTable(DBI::ANSI(), ...)

to 

createTableQuery = DBI::sqlCreateTable(cn, ...)

should give better types, so subsequent TEXT -> VARCHAR and DOUBLE -> FLOAT conversions should no longer be needed.

nathan-palmer commented 6 months ago

Changing translation process to leverage NHANESA functionality