ingest-the-data.sql
Save this script as "ingest-the-data.sql"
-- Set up the infrastructure and create the tables.
drop table if exists covidcast_names cascade;
create table covidcast_names(csv_file text primary key, staging_table text not null, signal text not null);
-- Each of these files contains 50 days of observations.
insert into covidcast_names(csv_file, staging_table, signal) values
('csv-files/covidcast-fb-survey-smoothed_'|| 'wearing_mask' ||'-2020-09-13-to-2020-11-01.csv', 'mask_wearers', 'smoothed_wearing_mask'),
('csv-files/covidcast-fb-survey-smoothed_'|| 'cli' ||'-2020-09-13-to-2020-11-01.csv', 'symptoms', 'smoothed_cli'),
('csv-files/covidcast-fb-survey-smoothed_'|| 'hh_cmnty_cli' ||'-2020-09-13-to-2020-11-01.csv', 'cmnty_symptoms', 'smoothed_hh_cmnty_cli');
create unique index covidcast_names_staging_table_unq on covidcast_names(staging_table);
create unique index covidcast_names_signal_unq on covidcast_names(signal);
\i cr-cr-staging-tables.sql
\i cr-cr-copy-from-csv-scripts.sql
call cr_staging_tables();
--------------------------------------------------------------------------------
-- Import the CSV files into the staging tables;
\t on
\o copy_from_csv.sql
select cr_copy_from_scripts(1);
\o
\i copy_from_csv.sql
\o copy_from_csv.sql
select cr_copy_from_scripts(2);
\o
\i copy_from_csv.sql
\o copy_from_csv.sql
select cr_copy_from_scripts(3);
\o
\i copy_from_csv.sql
\t off
--------------------------------------------------------------------------------
-- Check that the imported data are consistent with what was assumed about its
-- format and content. If the checks pass, then merge it into the single
-- "covidcast_fb_survey_results" table.
\i cr-assert-assumptions-ok.sql
\i cr-xform-to-covidcast-fb-survey-results.sql
do $body$
begin
-- If "assert_assumptions_ok()" aborts with an assert failure,
-- then "cr_covidcast_fb_survey_results()" will not be called.
call assert_assumptions_ok(
start_survey_date => to_date('2020-09-13', 'yyyy-mm-dd'),
end_survey_date => to_date('2020-11-01', 'yyyy-mm-dd'));
call xform_to_covidcast_fb_survey_results();
end;
$body$;