- Table of contents
- Mapping a new table in VegBank--a SQL plots datasource
Mapping a new table in VegBank--a SQL plots datasource¶
what is needed from the user¶
- datasource folder name
- table name
- mappings to VegCore
- code to create source-specific derived columns
- position to add table in
import_order.txt
- position to add table in left-join
- position to add columns in validation subset
steps¶
underlined: user input needed (other steps can be automated)
set vars¶
- start a subshell so the vars only affect these commands:
$0
- set vars:
datasrc=VegBank table=project
map the table¶
- add subdir:
make inputs/$datasrc/$table/add "cp" -f inputs/.NCBI/nodes/run inputs/$datasrc/$table/ # add new-style import files
- install the staging table and create the map spreadsheet:
inputs/$datasrc/$table/run
- prevent column collisions upon left-join4:
bin/in_place inputs/$datasrc/$table/map.csv sed -r "s/,\*/,*$table--/g"
- map the columns in
inputs/$datasrc/$table/map.csv
to terms in the VegCore data dictionary - rename staging table columns to VegCore:
inputs/$datasrc/$table/run
- if any datasource-specific postprocessing is needed:
- add
postprocess.sql
:"cp" -f inputs/VegBank/plot_/postprocess.sql inputs/$datasrc/$table/
- in
postprocess.sql
, modify the existing code to fit the datasource. remove any template code that doesn't apply. - run the postprocessing:
inputs/$datasrc/$table/run
- add
- in
inputs/$datasrc/import_order.txt
, insert$table
in dependency order - commit:
make inputs/$datasrc/add # place files under version control svn di # make sure the test output is correct svn st # make sure all non-data files are under version control svn ci -m "mapped inputs/$datasrc/$table/"
add the table to the left-join¶
- in
inputs/$datasrc/taxon_observation.**/postprocess.sql
, insert the following at the correct position:NATURAL LEFT JOIN $table
- update the left-join view:
inputs/$datasrc/taxon_observation.\*\*/run
- commit:
svn di # make sure the test output is correct svn ci -m "inputs/$datasrc/taxon_observation.**/postprocess.sql: added the $table table"
add new columns to the validation subset¶
- in
inputs/$datasrc/$table/map.csv
, note all output columns that don't start with*
- in
inputs/$datasrc/^taxon_observation.**.sample/create.sql
andmap.csv
, insert these columns at the appropriate position - update the materialized view:
rm=1 inputs/$datasrc/\^taxon_observation.\*\*.sample/run
- commit:
svn di # make sure the test output is correct svn ci -m "inputs/$datasrc/^taxon_observation.**.sample/create.sql, map.csv: added new $table columns"
sync vegbiendev¶
- switch to vegbiendev:
ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
- set vars as above
- apply staging table changes:
svn up inputs/$datasrc/$table/run inputs/$datasrc/taxon_observation.\*\*/run rm=1 inputs/$datasrc/\^taxon_observation.\*\*.sample/run