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 influenza_icu_cases_by_week_and_demographicmedical table in this repository, by referencing it like:
"cityofchicago/influenza-icu-cases-by-week-and-demographicmedical-4c4i-t7dw:latest"."influenza_icu_cases_by_week_and_demographicmedical"or in a full query, like:
SELECT
":id", -- Socrata column ID
"white_non_latinx_count", -- Cases among non-Latinx White people
"other_race_non_latinx_pct", -- Percent of all cases among non-Latinx people of other race not listed here
"male_pct", -- Percent of all cases among males
"age_unknown_pct", -- Percent of all cases with unknown age
"nhpi_count", -- Cases among non-Latinx Native Hawaiian Pacific Islander people
"asian_non_latinx_count", -- Cases among non-Latinx Asian people
"flushot_reported", -- Cases with known influenza vaccination status for the current season as reported by hospital
"flushot_count", -- Cases that receved an influenza vaccination for the current season as reported by hospital
"latinx_count", -- Cases among Latinx people
"age_25_49_count", -- Cases in age 25-49
"age_unknown_count", -- Cases with unknown age
"aian_count", -- Cases among non-Latinx American Indian Alaskan Native people
"age_0_4_count", -- Cases in age 0-4
"aian_pct", -- Percent of all cases among non-Latinx American Indian Alaskan Native people
"age_18_24_count", -- Cases in age 18-24
"week_start", -- Beginning date of the the MMWR week
"cases", -- Number of diagnosed flu cases
"age_0_4_pct", -- Percent of all cases occurring in age 0-4
"ah1n1_count", -- Cases positive for influenza A and subtyped as H1N1pdm09 virus strain
"flushot_pct", -- Percent of cases with known influenza vaccination status for the current season that receved an influenza vaccination
"deaths_count", -- Deaths among reported cases
"female_pct", -- Percent of all cases among females
"ah1n1_pct", -- Percent of all cases positive for influenza A and subtyped as H1N1pdm09 virus strain
"age_5_17_pct", -- Percent of all cases occurring in age 5-17
"age_18_24_pct", -- Percent of all cases occurring in age 18-24
"black_non_latinx_count", -- Cases among non-Latinx Black people
"white_non_latinx_pct", -- Percent of all cases among non-Latinx White people
"male_count", -- Cases among males
"age_50_64_pct", -- Percent of all cases occurring in age 50-64
"age_25_49_pct", -- Percent of all cases occurring in age 25-49
"age_65_over_count", -- Cases in age 65 and over
"age_50_64_count", -- Cases in age 50-64
"latinx_pct", -- Percent of all cases among Latinx people
"record_id", -- A unique identifier for the record.
"unknown_race_ethnicity_pct", -- Percent of all cases among people with no race/ethnicity reported
"anotsub_count", -- Cases positive for influenza A and not subtyped
"female_count", -- Cases among females
"deaths_pct", -- Percent of deaths among reported cases
"ah3n2_count", -- Cases positive for influenza A and subtyped as H3N2 virus strain
"b_count", -- Cases positive for influenza B
"anotsub_pct", -- Percent of all cases positive for influenza A and not subtyped
"b_pct", -- Percent of all cases positive for influenza B
"other_race_non_latinx_count", -- Cases among non-Latinx people of other race not listed here
"unknown_race_ethnicity_count", -- Cases among people with no race/ethnicity reported
"nhpi_pct", -- Percent of all cases among non-Latinx Native Hawaiian Pacific Islander people
"week_end", -- Ending date of the MMWR week
"age_5_17_count", -- Cases in age 5-17
"asian_non_latinx_pct", -- Percent of all cases among non-Latinx Asian people
"influenza_season", -- Annually recurring time period of increased influenza activity standardized by the U.S. Centers for Disease Control and Prevention
"ah3n2_pct", -- Percent of all cases positive for influenza A and subtyped as H3N2 virus strain
"mmwr_week", -- A weekly counting system within a calendar year standardized by the U.S. Centers for Disease Control and Prevention
"age_65_over_pct", -- Percent of all cases occurring in age 65 and over
"black_non_latinx_pct" -- Percent of all cases among non-Latinx Black people
FROM
"cityofchicago/influenza-icu-cases-by-week-and-demographicmedical-4c4i-t7dw:latest"."influenza_icu_cases_by_week_and_demographicmedical"
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 cityofchicago/influenza-icu-cases-by-week-and-demographicmedical-4c4i-t7dw with SQL in under 60 seconds.
Query Your Local Engine
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 cityofchicago/influenza-icu-cases-by-week-and-demographicmedical-4c4i-t7dw: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 cityofchicago/influenza-icu-cases-by-week-and-demographicmedical-4c4i-t7dwCheckout 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 cityofchicago/influenza-icu-cases-by-week-and-demographicmedical-4c4i-t7dw:latestThis will download all the objects for the latest tag of cityofchicago/influenza-icu-cases-by-week-and-demographicmedical-4c4i-t7dw 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 cityofchicago/influenza-icu-cases-by-week-and-demographicmedical-4c4i-t7dw: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 cityofchicago/influenza-icu-cases-by-week-and-demographicmedical-4c4i-t7dw: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, cityofchicago/influenza-icu-cases-by-week-and-demographicmedical-4c4i-t7dw is just another Postgres schema.