Query the Data Delivery Network
Query the DDNThe easiest way to query any data on Splitgraph is via the "Data Delivery Network" (DDN). The DDN is a single endpoint that speaks the PostgreSQL wire protocol. Any Splitgraph user can connect to it at data.splitgraph.com:5432 and query any version of over 40,000 datasets that are hosted or proxied by Splitgraph.
For example, you can query the covid19_hospital_data_coverage_for_hospital_in table in this repository, by referencing it like:
"datahub-hhs-gov/covid19-hospital-data-coverage-for-hospital-in-a6za-z3xi:latest"."covid19_hospital_data_coverage_for_hospital_in"or in a full query, like:
SELECT
":id", -- Socrata column ID
"able_to_obtain_surgical_masks", -- Able to order and obtain supply: surgical masks
"staffed_adult_icu_bed", -- Number of staffed adult ICU beds that are occupied
":@computed_region_pqdx_y6mm", -- This column was automatically created in order to record in what polygon from the dataset 'Demo | State Geographic Boundaries' (pqdx-y6mm) the point in column 'georeference' is located. This enables the creation of region maps (choropleths) in the visualization canvas and data lens.
"total_pediatric_patients_hospitalized_confirmed_rsv_", -- Number of hospitalized pediatric patients with confirmed RSV (inpatient, including NICU)
"ventilators_used", -- Number of mechanical ventilators in use
"total_adult_patients", -- Number of hospitalized adults with suspected or confirmed COVID-19 (inpatient, including observation)
"total_adult_patients_1", -- Number of hospitalized adults with confirmed COVID-19 (inpatient, including observation)
"total_pediatric_patients", -- Number of hospitalized pediatric confirmed COVID-19 patients (inpatient, including NICU)
"total_pediatric_patients_1", -- Number of hospitalized pediatric confirmed COVID-19 patients (inpatient, including NICU)
"inpatient_ventilators_used", -- Ventilators: in use by suspected or confirmed COVID
"staffed_icu_adult_patients_1", -- Number of patients in an adult ICU bed with confirmed COVID-19
"hospital_onset_covid", -- Number of current inpatients with onset of suspected or confirmed COVID-19 fourteen or more days after admission for a condition other than COVID-19.
"previous_day_admission_adult", -- Number of patients admitted to adult inpatient bed on previous calendar day with confirmed COVID-19
"previous_day_admission", -- Number of pediatric patients admitted to an inpatient bed on previous calendar day with confirmed COVID-19
"previous_day_total_ed_visits", -- Number of ED visits on the previous calendar day
"previous_day_covid_ed_visits", -- Number of ED visits related to COVID-19 (meets suspected or confirmed definition or presents for COVID-19 diagnostic testing) on the previous calendar day
"total_patients_hospitalized", -- Total hospitalized patients with both laboratory-confirmed COVID-19 and laboratory-confirmed influenza
"icu_beds_used", -- Total staffed inpatient ICU beds occupied
"previous_day_deaths_influenza", -- Number of patients with laboratory-confirmed influenza who died on previous calendar day in the hospital, ED, or any overflow location
"previous_day_deaths_covid", -- Number of patients with laboratory-confirmed influenza and laboratory-confirmed COVID-19 who died on previous calendar day in the hospital, ED, or any overflow location
"on_hand_supply_remdesivir", -- Number of units available: remdesivir vials
"on_hand_ventilator_supplies", -- Days of supply in stock: ventilator supplies
"n95_respirators_days_available", -- Days of supply in stock: N95 respirators
"able_to_maintain_paprs", -- Able to maintain at least a 3- day supply: other respirators such as PAPRs or elastomerics
"on_hand_supply_therapeutic_1", -- Number of therapeutic C courses currently in inventory. This field has been designated as Bamlanivimab/ Etesevimab and is mandatory as of 4/07/2021.
"previous_day_admission_adult_influenza_confirmed_", -- Number of adult patients admitted to an inpatient bed on previous calendar day with confirmed influenza
"total_staffed_adult_icu_beds", -- Number of staffed adult ICU beds
"total_icu_beds", -- Total staffed inpatient ICU beds
"all_adult_hospital_inpatient_1", -- Number of staffed inpatient adult beds that are occupied
"all_adult_hospital_inpatient", -- Number of staffed inpatient adult beds including all overflow, observation, and surge/expansion beds used for inpatients (includes all ICU)
"inpatient_beds", -- Number of staffed inpatient beds including all overflow, observation, and surge/expansion beds used for inpatients (includes all ICU)
"suspend_reason", -- Reason for why hospital is in suspense (Emergency Event, New CCN, Workplan, or None)
"percentage_of_required_fields", -- The percentage of required fields reported over the prior 7 day period.
"days_at_100", -- The number of the days in the prior 7 day period that the hospital reported 100% of required fields.
"certified_bed_count", -- The certified bed count associated with the hospital in the CMS POS file.
"reporting_source", -- Reflects the source of hospital-reported data used for the completeness report. State indicates data submitted directly to HHS Protect by a state on behalf of hospitals, TT indicates data submitted through Teletracking, State/TT indicates data is submitted through both sources, N/A indicates no data was submitted. For hospitals with data from both State and Teletracking (TT), a single data source is used for each day of the week, but data source may be mixed over the week.
"fips_county", -- Reporting Facility fips county code tied with reporting CCN
"zip_code", -- Reporting Facility zip code tied with reporting CCN
"street_address", -- Reporting facility street address tied with reporting CCN
"facility_name", -- Reporting facility name tied with reporting CCN
"georeference",
"on_hand_supply_of_single", -- Days of supply in stock: single-use gowns
"previous_day_remdesivir_used", -- Number of remdesivir vials used on the previous calendar day in an inpatient, ED, and/or overflow location
"staffed_icu_pediatric_patients_confirmed_rsv_", -- Number of patients in a pediatric ICU bed with confirmed RSV
"staffed_icu_pediatric_patients_confirmed_influenza_", -- Number of patients in a pediatric ICU bed with confirmed influenza
"previous_day_admission_pediatric_influenza_confirmed_", -- Number of pediatric patients admitted to an inpatient bed on previous calendar day with confirmed influenza
"total_pediatric_patients_hospitalized_confirmed_influenza_", -- Number of hospitalized pediatric patients with confirmed influenza (inpatient, including NICU)
"able_to_maintain_ventilator", -- Able to maintain at least a 3- day supply: ventilator supplies (e.g. flow sensors, tubing, filters etc.)
"critical_staffing_shortage_1", -- Anticipated staff shortages within a week
"total_patients_hospitalized_1", -- Total hospitalized patients with both laboratory-confirmed COVID-19 and laboratory-confirmed influenza
"previous_day_admission_adult_3", -- Number of patients admitted to adult inpatient bed on previous calendar day with confirmed COVID-19
"overflow_ventilators_used", -- Patients with suspected or confirmed COVID-19 currently in the ED or any overflow location awaiting an inpatient bed and on a mechanical ventilator.
"staffed_icu_adult_patients_confirmed_rsv_", -- Number of patients in an adult ICU bed with confirmed RSV
"previous_day_admission_pediatric_rsv_confirmed_", -- Number of pediatric patients admitted to an inpatient bed on previous calendar day with confirmed RSV
"previous_day_admission_adult_rsv_confirmed_", -- Number of adult patients admitted to an inpatient bed on previous calendar day with confirmed RSV
"total_adult_patients_hospitalized_confirmed_rsv_", -- Number of hospitalized adult patients with confirmed RSV (inpatient, including observation)
"total_adult_patients_hospitalized_confirmed_influenza_", -- Number of hospitalized adult patients with confirmed influenza (inpatient, including observation)
"able_to_maintain_n95_masks", -- Able to maintain at least a 3- day supply: N95 masks
"able_to_obtain_gloves", -- Able to order and obtain supply: gloves (o.g. face shields, goggles)
"able_to_obtain_single_use", -- Able to order and obtain supply: single-use gowns
"able_to_obtain_paprs", -- Able to order and obtain supply: PAPRs or elastomerics
"able_to_obtain_ventilator_1", -- Able to order and obtain supply: ventilator medications
"on_hand_supply_of_gloves", -- Days of supply in stock: gloves
"ppe_supply_management_source", -- Management of PPE for facility
"previous_day_admission_2", -- Number of patients with laboratory-confirmed influenza admitted to inpatient bed on previous calendar day
"previous_day_admission_adult_1", -- Number of patients admitted to adult inpatient bed on previous calendar day with suspected COVID-19
"total_ventilators", -- Number of all mechanical ventilators in facility
"provider_subtype", -- Reporting Provider Subtype (Short Term, Long Term, Critical Access Hospitals, Psychiatric, or Rehabilitation)
"staffed_icu_adult_patients_confirmed_influenza_", -- Number of patients in an adult ICU bed with confirmed influenza
"previous_week_patients_covid_1", -- Number of patients who received the final dose in a COVID-19 vaccination series in the previous week.
"previous_week_patients_covid", -- Number of patients who received the first dose of a COVID-19 vaccine that is administered in a multi-dose series in the previous week. If this is the first week of reporting, field contains all doses given up to that date
"total_personnel", -- Current total number of healthcare personnel for the facility. Includes all paid and unpaid persons serving in healthcare settings who have the potential for direct or indirect exposure to patients or infectious materials
"total_personnel_covid_2", -- Current total number of healthcare personnel who have received a complete series of a COVID-19 vaccination.
"total_personnel_covid_1", -- Current total number of healthcare personnel who have received at least one dose of COVID-19 vaccination that is administered in a multi-dose series.
"total_personnel_covid", -- Number of healthcare personnel who have not yet received a single vaccine dose
"previous_week_personnel_covid", -- Number of COVID-19 vaccination doses administered in the previous week. If this is the first week of reporting, field contains all doses given up to that date.
"previous_week_therapeutic_2", -- Number of therapeutic B courses used in the previous calendar week in an inpatient, ED, overflow, or outpatient location, such as an urgent care, infusion center, or outpatient clinic. This field has been designated as Bamlanivimab and will be mandatory on 1/8/21.
"on_hand_supply_therapeutic_2", -- Number of therapeutic B courses currently in inventory. This field has been designated as Bamlanivimab and will be mandatory on 1/8/21.
"previous_week_therapeutic_1", -- Number of therapeutic C courses used in the previous calendar week in an inpatient, ED, overflow, or outpatient clinic. This field has been designated as Bamlanivimab/ Etesevimab and is mandatory as of 4/07/2021.
"previous_week_therapeutic", -- Number of therapeutic A courses used in the previous calendar week in an inpatient, ED, overflow, or outpatient location, such as an urgent care, infusion center, or outpatient clinic. This field has been designated as Casirivimab/Imdevimab and will be mandatory on 1/8/21.
"on_hand_supply_therapeutic", -- Number of therapeutic A courses currently in inventory. This field has been designated as Casirivimab/Imdevimab and will be mandatory on 1/8/21.
"able_to_maintain_lab_viral", -- Able to maintain at least a 3- day supply (laboratory): viral transport media
"able_to_maintain_lab_nasal_1", -- Able to maintain at least a 3- day supply (laboratory): nasal swabs
"able_to_maintain_lab_nasal", -- Able to maintain at least a 3- day supply (laboratory): nasal pharyngeal swabs
"able_to_maintain_gloves", -- Able to maintain at least a 3- day supply: gloves
"able_to_maintain_single_use", -- Able to maintain at least a 3- day supply: single use gowns
"able_to_maintain_eye", -- Able to maintain at least a 3- day supply: eye protections (e.g. face shield, goggles)
"able_to_maintain_surgical", -- Able to maintain at least a 3- day supply: surgical masks
"able_to_maintain_ventilator_1", -- Able to maintain at least a 3- day supply: ventilator medications
"able_to_obtain_eye_protection", -- Able to order and obtain supply: eye protection (e.g. face shields, goggles)
"able_to_obtain_n95_masks", -- Able to order and obtain supply: N95 masks
"able_to_obtain_ventilator", -- Able to order and obtain supply: ventilator supplies (e.g. flow sensors, tubing, filters etc.)
"on_hand_supply_of_eye", -- Days of supply in stock: eye protection including face shields and goggles
"on_hand_supply_of_surgical", -- Days of supply in stock: surgical and procedure masks
"critical_staffing_shortage", -- Have staff shortages today
"icu_patients_confirmed", -- Total ICU patients with laboratory-confirmed influenza
"previous_day_admission_1", -- Number of pediatric patients admitted to inpatient bed on previous calendar day with suspected COVID-19
"previous_day_admission_adult_2", -- Number of patients admitted to adult inpatient bed on previous calendar day with suspected COVID-19
"deaths_covid", -- Number of patients with suspected or confirmed COVID-19 who died on the previous calendar day in the hospital, ED, or overflow location
"overflow_covid", -- Number of patients with suspected or confirmed COVID-19 currently in the ED or any overflow location awaiting an inpatient bed
"staffed_icu_adult_patients", -- Number of patients in an adult ICU bed with suspected or confirmed COVID-19
"inpatient_beds_used", -- Number of staffed inpatient beds that are occupied
"all_adult_hospital_beds", -- Number of staffed inpatient and outpatient adult beds, including all overflow and surge/expansion beds used for inpatients and for outpatients (includes all ICU, ED, and observation)
"total_beds", -- Number of staffed inpatient and outpatient beds, including all overflow and surge/expansion beds used for inpatients and for outpatients (includes all ICU, ED, and observation)
"hospital_required_to_report", -- Indicates whether a hospital is only required to report once a week, on Wednesdays.
"week_ending", -- The last day of the 7 day period used to asses reporting completeness. The 7 day period is the prior Friday to Thursday e.g., 2020-10-22 for 10/16-10/22. (yyyy-mm-dd)
"component_hospitals", -- The number of individual hospital campuses covered by the CCN level reported displayed in this report.
"dates_with_missing_data", -- If any, the dates when a facility did not fully report all required fields during the enforcement period
"city", -- Reporting facility city tied with reporting CCN
"ccn", -- Reporting CCN
"state" -- State where reporting CCN based
FROM
"datahub-hhs-gov/covid19-hospital-data-coverage-for-hospital-in-a6za-z3xi:latest"."covid19_hospital_data_coverage_for_hospital_in"
LIMIT 100;Connecting to the DDN is easy. All you need is an existing SQL client that can connect to Postgres. As long as you have a SQL client ready, you'll be able to query datahub-hhs-gov/covid19-hospital-data-coverage-for-hospital-in-a6za-z3xi with SQL in under 60 seconds.
Query Your Local Engine
Install Splitgraph Locally
bash -c "$(curl -sL https://github.com/splitgraph/splitgraph/releases/latest/download/install.sh)"Read the installation docs.
Splitgraph Cloud is built around Splitgraph Core (GitHub), which includes a local Splitgraph Engine packaged as a Docker image. Splitgraph Cloud is basically a scaled-up version of that local Engine. When you query the Data Delivery Network or the REST API, we mount the relevant datasets in an Engine on our servers and execute your query on it.
It's possible to run this engine locally. You'll need a Mac, Windows or Linux system to install sgr, and a Docker installation to run the engine. You don't need to know how to actually use Docker; sgrcan manage the image, container and volume for you.
There are a few ways to ingest data into the local engine.
For external repositories, the Splitgraph Engine can "mount" upstream data sources by using sgr mount. This feature is built around Postgres Foreign Data Wrappers (FDW). You can write custom "mount handlers" for any upstream data source. For an example, we blogged about making a custom mount handler for HackerNews stories.
For hosted datasets (like this repository), where the author has pushed Splitgraph Images to the repository, you can "clone" and/or "checkout" the data using sgr cloneand sgr checkout.
Cloning Data
Because datahub-hhs-gov/covid19-hospital-data-coverage-for-hospital-in-a6za-z3xi:latest is a Splitgraph Image, you can clone the data from Spltgraph Cloud to your local engine, where you can query it like any other Postgres database, using any of your existing tools.
First, install Splitgraph if you haven't already.
Clone the metadata with sgr clone
This will be quick, and does not download the actual data.
sgr clone datahub-hhs-gov/covid19-hospital-data-coverage-for-hospital-in-a6za-z3xiCheckout the data
Once you've cloned the data, you need to "checkout" the tag that you want. For example, to checkout the latest tag:
sgr checkout datahub-hhs-gov/covid19-hospital-data-coverage-for-hospital-in-a6za-z3xi:latestThis will download all the objects for the latest tag of datahub-hhs-gov/covid19-hospital-data-coverage-for-hospital-in-a6za-z3xi and load them into the Splitgraph Engine. Depending on your connection speed and the size of the data, you will need to wait for the checkout to complete. Once it's complete, you will be able to query the data like you would any other Postgres database.
Alternatively, use "layered checkout" to avoid downloading all the data
The data in datahub-hhs-gov/covid19-hospital-data-coverage-for-hospital-in-a6za-z3xi:latest is 0 bytes. If this is too big to download all at once, or perhaps you only need to query a subset of it, you can use a layered checkout.:
sgr checkout --layered datahub-hhs-gov/covid19-hospital-data-coverage-for-hospital-in-a6za-z3xi:latestThis will not download all the data, but it will create a schema comprised of foreign tables, that you can query as you would any other data. Splitgraph will lazily download the required objects as you query the data. In some cases, this might be faster or more efficient than a regular checkout.
Read the layered querying documentation to learn about when and why you might want to use layered queries.
Query the data with your existing tools
Once you've loaded the data into your local Splitgraph Engine, you can query it with any of your existing tools. As far as they're concerned, datahub-hhs-gov/covid19-hospital-data-coverage-for-hospital-in-a6za-z3xi is just another Postgres schema.