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 baseline_study_of_food_for_peace_title_ii table in this repository, by referencing it like:
"datahub-usaid-gov/baseline-study-of-food-for-peace-title-ii-cvza-mwy2:latest"."baseline_study_of_food_for_peace_title_ii"or in a full query, like:
SELECT
":id", -- Socrata column ID
"g00m", -- MODULE G START MINUTE
"g00h", -- MODULE G START HOUR
"f18m", -- MODULE F FINISH MINUTE
"i110m", -- MODULE I1 FINISH MINUTE
"hdds", -- Average Household Dietary Diversity Score HDDS
"i110h", -- MODULE I1 FINISH HOUR
"cj", -- Oil fats
"ci", -- Milk and milk products
"f17_9", -- F17 List five critical moments for handwashing
"f17_7", -- F17 List five critical moments for handwashing
"f17_6", -- F17 List five critical moments for handwashing
"cd", -- Fruits
"i100m", -- MODULE I1 START MINUTE
"f16", -- F16 Observe presence of soap detergent or other cleansing agent at the place for handwashing
"i100h", -- MODULE I1 START HOUR
"cc", -- Vegetables
"f11", -- F11 What kind of toilet facility do members of your household usually use
"f10_7", -- F10 7 Do you do anything to the water to make it safer to drink
"e38", -- E38 WOMAN S WEIGHT KILOGRAMS
"e37", -- E37 WOMAN S HEIGHT CENTIMETERS
"e36", -- E36 WOMAN S AGE IN YEARS
"cb", -- Root and tubers
"e33m", -- MODULE E FINISH MINUTE
"e33h", -- MODULE E FINISH HOUR
"f10_1", -- F10 1 Do you do anything to the water to make it safer to drink
"e31d", -- E31D When was the last time you gave birth DAY
"f09", -- F09 Do you do anything to the water to make it safer to drink
"e30", -- E30 Have you ever given birth
"e28", -- E28 Are you currently pregnant
"f01b", -- F01B VILLAGE NUMBER
"e15", -- E15 Any ripe mangos papayas melon or passionfruit
"b21h", -- MODULE B FINISH HOUR
"wmwt", -- Sampling weight for women 15 49 adjusted for module E non response
"e13", -- E13 Potatoes or any other foods made from roots
"b00m", -- MODULE B START MINUTE
"e12", -- E12 Any foods that are yellow or orange inside
"e04", -- E04 How old are you
"a20m", -- A20 MONTH OF DATA ENTRY
"e03b", -- E03B In what year were you born
"hhwt", -- Sampling weight for households and household members
"a20", -- A20 DATA ENTRY OPERATOR CODE
"e02", -- E02 LINE NUMBER OF WOMAN FROM HOUSEHOLD ROSTER
"e01b", -- E01B VILLAGE NUMBER
"f17_3", -- F17 List five critical moments for handwashing
"ck", -- Sugar honey
"e01a", -- E01A HOUSEHOLD NUMBER
"soap_water", -- Cleansing agent and water at handwashing station
"hhs", -- Household Hunger Scale
"c1819", -- Went to bed hungry
"c01c", -- C01C LINE NUMBER FROM HOUSEHOLD ROSTER COLUMN 6
"c00h", -- MODULE C START HOUR
"e21", -- E21 Any foods made from beans or seeds
"at_hh", -- ANTHRO HOUSEHOLD NUMBER
"at_id", -- ANTHRO QUESTIONNAIRE NUMBER
"d66m", -- MODULE D FINISH MINUTE
"d66h", -- MODULE D FINISH HOUR
"e20", -- E20 Any fish or seafood
"delivery_lapse", -- Time since last delivery in days
"c11", -- C11 Any foods made from beans nuts or seeds
"d00m", -- MODULE D START MINUTE
"d00h", -- MODULE D START HOUR
"c22m", -- MODULE C FINISH MINUTE
"reg", -- A05 REGION
"c22h", -- MODULE C FINISH HOUR
"c20", -- C20 In the past 4 weeks did anyone go a whole day and night without eating because there was no food
"f15", -- F15 Observe presence of water at the place of handwashing
"at_anm", -- Anthropometry date Month
"e23", -- E23 Any foods made with oils fats or butter
"c16", -- C16 In the past 4 weeks was there ever no food to eat due to lack of resources
"a14", -- A14 FINAL OUTCOME OF INTERVIEW
"e18", -- E18 Any meat
"del_date", -- Date of last delivery
"wom_age", -- Women s age in completed years
"e25", -- E25 Any condiments such as chilies spices herbs or fish powder
"f18h", -- MODULE F FINISH HOUR
"c15", -- C15 Any other foods such as condiments coffee or tea
"a18", -- A18 FIELD COORDINATOR CODE
"c10", -- C10 Any fresh or dried fish or shellfish
"c08", -- C08 Any meats organ meats or blood
"c07", -- C07 Any fruits
"birth_date", -- Woman s date of birth
"c04", -- C04 Any foods made from wheat maize rice sorghum or millet
"c02", -- C02 Was yesterday an unusual or special day
"int_date", -- Date of Interview
"f17_10", -- F17 List five critical moments for handwashing
"c01b", -- C01B VILLAGE NUMBER
"cluster", -- Cluster
"c21", -- C21 How often did this happen in the last 4 weeks
"strata", -- Strata
"c01a", -- C01A HOUSEHOLD NUMBER
"c00m", -- MODULE C START MINUTE
"g70m", -- MODULE G FINISH MINUTE
"h01m", -- H01M MODULE H FINISH MINUTE
"g70h", -- MODULE G FINISH HOUR
"f17_8", -- F17 List five critical moments for handwashing
"f17_5", -- F17 List five critical moments for handwashing
"f17_4", -- F17 List five critical moments for handwashing
"f17_1", -- F17 List five critical moments for handwashing
"f14", -- F14 Show me where members of your household most often wash their hands
"h01h", -- H01H MODULE H FINISH HOUR
"f13", -- F13 How many households share the toilet facility
"eligible_women", -- Women eligible for Module E between 15 and 49 years of age
"h00m", -- H00M MODULE H START MINUTE
"f12", -- F12 Does your household share the toilet facility with other households
"f10_5", -- F10 5 Do you do anything to the water to make it safer to drink
"f10_3", -- F10 3 Do you do anything to the water to make it safer to drink
"f07", -- F07 Is water available from this source all year round
"f06", -- F06 How long does it take to go there get water and come back
"f05", -- F05 Where is that water source located
"f04", -- F04 What is main source of drinking water
"f02", -- F02 LINE NUMBER OF RESPONDENT
"f10_6", -- F10 6 Do you do anything to the water to make it safer to drink
"c18", -- C18 In the past 4 weeks did anyone go to sleep hungry because there was not enough food
"f01a", -- F01A HOUSEHOLD NUMBER
"f00m", -- MODULE F START MINUTE
"f00h", -- MODULE F START HOUR
"h00h", -- H00H MODULE H START HOUR
"b21m", -- MODULE B FINISH MINUTE
"b00h", -- MODULE B START HOUR
"i208m", -- MODULE I2 FINISH MINUTE
"a20d", -- A20 DAY OF DATA ENTRY
"a19", -- A19 OFFICE EDITOR CODE
"a14_5", -- A14 5 Line Number of Respondant to Household Questionnaire
"i208h", -- MODULE I2 FINISH HOUR
"a14_4", -- A14 4 Total Children Under 5
"f08", -- F08 In the last two weeks was water available from this source for an entire day or longer
"a14_3", -- A14 3 Total Eligible Farmers
"a14_2", -- A14 2 Total Women 15 49 yrs
"ca", -- Cereals
"a14_1", -- A14 1 Total Number of Visits
"i200m", -- MODULE I2 START MINUTE
"a13in", -- INTERVIEWER NUMBER FOR FINAL VISIT
"improved_source", -- Households using an improved drinking water source
"a13m", -- MONTH OF FINAL VISIT
"f10_8", -- F10 8 Do you do anything to the water to make it safer to drink
"a13d", -- DAY OF FINAL VISIT
"a04a", -- A04a COMMUNE NAME
"vn", -- A02 VILLAGE NUMBER
"hh", -- A01 HOUSEHOLD NUMBER
"id",
"c1617", -- No food at all in the house
"ce", -- Meats organs blood
"gendered_hh", -- Gendered Household type
"i200h", -- MODULE I2 START HOUR
"at_any", -- Anthropometry date Year
"at_sm", -- ANTHRO START MINUTE
"at_vn", -- ANTHRO VILLAGE NUMBER
"e39", -- E39 RESULT
"e34", -- E34 ANTHROPOMETRY LINE NUMBER OF WOMAN FROM ROSTER
"e31m", -- E31M When was the last time you gave birth MONTH
"e29", -- E29 Have you ever been pregnant
"e26", -- E26 Any grubs snails or insects
"e16", -- E16 Any other fruit or vegetables
"f10_2", -- F10 2 Do you do anything to the water to make it safer to drink
"e14", -- E14 Any dark green leafy vegetables
"hhs_n2", -- Household Hunger Scale moderate and severe hunger
"e27", -- E27 Any foods made with red palm oil red palm nut or red palm nut pulp sauce
"c2021", -- Went all day and night without eating
"a20y", -- A20 YEAR OF DATA ENTRY
"e11", -- E11 Any foods made from grains
"e05", -- E05 Are you between the ages of 15 49 yrs
"at_fm", -- ANTHRO FINISH MINUTE
"at_fh", -- ANTHRO FINISH HOUR
"cg", -- Fish and seafood
"e22", -- E22 Any cheese yogurt or other milk products
"e24", -- E24 Any sugary foods such as chocolate and cake
"e31y", -- E31Y When was the last time you gave birth YEAR
"c13", -- C13 Any foods made with oil fat or butter
"critical_handwashing_all", -- Knows all 5 critical moments for handwashing
"e03a", -- E03A In what month were you born
"women_id", -- member ID for module E
"a04b", -- A04b DEPARTMENT NAME
"critical_handwashing_cat", -- Knows 3 of 5 critical moments for handwashing
"cf", -- Eggs
"e00m", -- MODULE E START MINUTE
"pvo", -- PVO
"f10_4", -- F10 4 Do you do anything to the water to make it safer to drink
"c06", -- C06 Any vegetables
"c19", -- C19 How often did this happen in the last 4 weeks
"a13y", -- YEAR OF FINAL VISIT
"e17", -- E17 Any liver kidney heart or other organ meats blood
"c14", -- C14 Any sugar or honey
"e19", -- E19 Any eggs
"c17", -- C17 How often did this happen in the past 4 weeks
"ch", -- Pulses legumes nuts
"at_and", -- Anthropometry date Day
"c05", -- C05 Any foods made from roots or tubers
"c12", -- C12 Any cheese yogurt milk or other milk products
"improved_sanitation", -- Households using improved sanitation facilities
"f10_9", -- F10 9 Do you do anything to the water to make it safer to drink
"cl", -- Miscellaneous
"wom_count", -- Number of women interviewed in household per MOD E E02
"c09", -- C09 Any eggs
"critical_handwashing", -- Number of critical moments for handwashing known out of 5
"e06", -- E06 CHECK AGE IS BETWEEN 15 49 YRS
"e00h", -- MODULE E START HOUR
"f17_2", -- F17 List five critical moments for handwashing
"at_sh", -- ANTHRO START HOUR
"hhs_n", -- Household Hunger Scale moderate or severe hunger
"a17" -- A17 SUPERVISOR CODE
FROM
"datahub-usaid-gov/baseline-study-of-food-for-peace-title-ii-cvza-mwy2:latest"."baseline_study_of_food_for_peace_title_ii"
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-usaid-gov/baseline-study-of-food-for-peace-title-ii-cvza-mwy2 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 datahub-usaid-gov/baseline-study-of-food-for-peace-title-ii-cvza-mwy2: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-usaid-gov/baseline-study-of-food-for-peace-title-ii-cvza-mwy2Checkout 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-usaid-gov/baseline-study-of-food-for-peace-title-ii-cvza-mwy2:latestThis will download all the objects for the latest tag of datahub-usaid-gov/baseline-study-of-food-for-peace-title-ii-cvza-mwy2 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-usaid-gov/baseline-study-of-food-for-peace-title-ii-cvza-mwy2: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-usaid-gov/baseline-study-of-food-for-peace-title-ii-cvza-mwy2: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-usaid-gov/baseline-study-of-food-for-peace-title-ii-cvza-mwy2 is just another Postgres schema.