Skip to content

loads USDA Branded Food Products csv into a SQL database

License

Notifications You must be signed in to change notification settings

littlebunch/bfpd-sql

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

17 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

bfpd-sql

Simple command line utility for loading Branded Food Products csv into a MySQL/MariaDB database.

What's here

  • ./config -- go type definitions for configuring the app.
  • ./ds -- datasource interface implemented for mariadb. Feel free to implement a ds for your favorite database like postgres.
  • ./ingest -- interface implemented for branded foods, where the csv gets translated into the model
  • ./model -- go type definitions for loading the csv data
  • ./schema -- schema for the mariadb database

How to run

  1. Install go v 13 or greater and a recent version of mariadb
  2. Download the branded foods and 'Supporting data for Downloads' csv from Food Data Central and unzip into a directory of your choice.
  3. Clone this repo into a directory of your choice and build the program: go build -o bfpdloader main.go
  4. Create a schema: mysql -u your-user -pyour-password -e'create schema bfpd'
  5. Install the schema using the one provided in the ./schema directory: mysql -u your-user -pyour-password bfpd < bfpd-schema.sql
  6. In the build directory, create a config.yaml which matches the schema you just created:
    url: 127.0.0.1
    db: bfpd
    pwd: your-password
    user: your-user
  7. Load the nutrients table: ./bfpdloader -t NUT -i /your-csv-install-path/nutrient.csv
  8. Load the derivations table: ./bfpdloader -t DERV -i /your-csv-install-path/food_nutrient_derivation.csv
  9. Load the branded foods database ./bfpdloader -t BFPD -i /your-csv-install-path/bfpd/