SQL scripts for ingesting the COVIDcast data

SQL scripts for ingesting the COVIDcast data

This page documents the preview version (v2.23). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.

Here are the .sql scripts that jointly implement the whole ingestion flow: creating staging tables; copying in the data from the .csv files; checking that the data in the staging tables conforms to the expected rules; and projecting the relevant columns and joining them into a single "covidcast_fb_survey_results" table.

  • ingest-the-data.sql is the master script for this purpose. It contains some explicit SQL statements and it invokes other files that, for example, create procedures that the master script calls. Save it to the "covid-data-case-study" directory that you created for this case study. You can't run it yet because the files that it needs aren't yet in place. You will step through each manually and then save it. When you've done this once, and saved all the files, you will then be able to run, and re-run, the whole ingestion process with a single "button press". This will be useful if you decide, later, to download more recent COVIDcast data.

    Here are the scripts that the master script relies on:

  • cr-cr-staging-tables.sql

  • cr-cr-copy-from-csv-scripts.sql

  • cr-assert-assumptions-ok.sql

  • cr-xform-to-covidcast-fb-survey-results.sql