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:
"usaid-gov/baseline-study-of-food-for-peace-title-ii-mkec-gk6e:latest"."baseline_study_of_food_for_peace_title_ii"or in a full query, like:
SELECT
":id", -- Socrata column ID
"dd3", -- Dairy products milk yogurt cheese
"dd2", -- Legumes and nuts
"d17", -- D17 Was the child breastfed yesterday during the day or night
"d59_3", -- D59 Where did you seek advice or treatment
"d63", -- D63 Was anything else given to treat the diarrhea
"d64_14", -- D64 What else was given to treat the diarrhea
"foods_min", -- none
"d64_12", -- D64 What else was given to treat the diarrhea
"d59_17", -- D59 Where did you seek advice or treatment
"mad", -- Minimum Acceptable Diet MAD
"d59_13", -- D59 Where did you seek advice or treatment
"d41", -- D41 Any eggs
"d59_18", -- D59 Where did you seek advice or treatment
"wamin3", -- wt age 3SD
"d59_4", -- D59 Where did you seek advice or treatment
"d64_20", -- D64 What else was given to treat the diarrhea
"at_fh", -- ANTHRO FINISH HOUR
"liquids_water", -- none
"d40", -- D40 Any meat
"at_any", -- ANTHROPOMETRIST YEAR
"bmimin3", -- bmi age 3SD
"whmin3", -- wt ht 3SD
"d51check", -- none
"hamin3", -- ht age 3SD
"d64_4", -- D64 What else was given to treat the diarrhea
"hamin2", -- ht age 2SD
"d64_17", -- D64 What else was given to treat the diarrhea
"liquids_nonmilk_min", -- none
"whplus2", -- wt ht 2SD
"meal_frequency_rec2", -- Minimum meal frequency 3 or more Breastfed 9 23 months
"d62c", -- D62C Was the child given any govt recommended homemade fluids
"age_0_23", -- Age categories for children 0 to 23 months
"d04", -- D04 Sex of child
"d05d", -- D05 Child s day of birth
"d05a", -- D05 Child s year of birth
"d06", -- D06 How old was the child on his her last birthday
"at_anm", -- ANTHROPOMETRIST MONTH
"d72", -- D72 SOURCE OF BIRTH DATE
"d07", -- D07 How many months old is the child
"d18", -- D18 Did child consume breast milk in any of these ways yesterday during the day or night
"d20", -- D20 Was the child given oral rehydration solution yesterday during the day or at night
"d22", -- D22 Did the child have infant formula
"d24", -- D24 Did the child cosume any milk such as tinned powdered or animal milk
"d27", -- D27 Did the child have any clear broth
"d29", -- D29 How many times yesterday during the day or at night did the child consume any yogurt
"d31", -- D31 Did the child have any other water based liquids available in the local setting
"d35", -- D35 Potatoes or any other foods made from roots
"d37", -- D37 Any ripe mangos papayas melon or passionfruit
"d38", -- D38 Any other fruit or vegetables
"d39", -- D39 Any liver kidney heart or other organ meats blood
"d43", -- D43 Any foods made from beans or seeds
"d46", -- D46 Any sugary foods such as chocolate and cake
"d47", -- D47 Any condiments such as chilies spices herbs or fish powder
"d49", -- D49 Any foods made with red palm oil red palm nut or red palm nut pulp sauce
"d51", -- D51 How many times did the child eat any solid semi solid or soft foods other than liquids yesterday during the da
"d54", -- D54 Has the child had diarrhea in the last two weeks
"d55", -- D55 Was there any blood in the stools
"d57", -- D57 Was the child given less than usual to eat about the same or more than usual to eat
"d59_1", -- D59 Where did you seek advice or treatment
"d59_2", -- D59 Where did you seek advice or treatment
"d59_6", -- D59 Where did you seek advice or treatment
"d59_9", -- D59 Where did you seek advice or treatment
"d61", -- D61 Where did you first seek advice or treatment
"d64_11", -- D64 What else was given to treat the diarrhea
"cluster", -- Cluster
"pvo", -- PVO
"at_id", -- ANTHRO QUESTIONNAIRE NUMBER
"at_hh", -- ANTHRO HOUSEHOLD NUMBER
"at_vn", -- ANTHRO VILLAGE NUMBER
"at_sm", -- ANTHRO START MINUTE
"at_anid", -- ANTHROPOMETRIST ID NUMBER
"at_and", -- ANTHROPOMETRIST DAY
"at_spid", -- ANTHRO SUPERVISOR ID NUMBER
"d59_7", -- D59 Where did you seek advice or treatment
"at_spm", -- ANTHRO SUPERVISOR MONTH
"at_spy", -- ANTHRO SUPERVISOR YEAR
"at_fm", -- ANTHRO FINISH MONTH
"birth_date", -- Child birthday
"int_date", -- Date interview was done
"d67", -- D67 ANTHROPOMETRY CHILD S LINE NUMBER FROM HH ROSTER
"d70", -- D70 CHILD S AGE IN MONTHS
"d73", -- D73 CHILD S WEIGHT KILOGRAMS
"d74", -- D74 CHILD S HEIGHT CENTIMETERS
"d03", -- D03 CHILDS LINE NUMBER FROM ROSTER
"d64_8", -- D64 What else was given to treat the diarrhea
"d64_19", -- D64 What else was given to treat the diarrhea
"d69", -- D69 CHILS S SEX
"d02", -- D02 CAREGIVERS LINE NUMBER FROM ROSTER
"d77", -- D77 EDEMA
"anthro_date", -- Date anthropometry was done
"agedays", -- Child s age in days at date of interview
"sex", -- Child s sex
"lorh", -- Height measured laying down or standing up
"length", -- Child s length height in centimeters
"weight2", -- Child s weight in kilograms
"d36", -- D36 Any dark green leafy vegetables
"agedays3", -- Child s age in days at time of anthropometry
"ddsix", -- Dietary Diversity 6 food groups for non breastfed children
"d64_15", -- D64 What else was given to treat the diarrhea
"total_milk_feeds", -- Total milk feeds yesterday formula milk yogurt
"agemos", -- Child s age in months at time of anthropometry
"wgting", -- Child sampling weight
"filter", -- none
"liquids_min", -- none
"liquids_milk", -- none
"dd4", -- Flesh foods meat fish poultry and liver organ meats or misc small animal protein
"meal_frequency", -- Frequency of feeding of solid semi solid and soft foods yesterday
"d58", -- D58 Did you seek advice or treatment for the diarrhea from any source
"liquids_milk_min", -- none
"yogurt_feeds", -- none
"hh", -- HOUSEHOLD NUMBER
"vn", -- VILLAGE NUMBER
"dis", -- DISTRICT NUMBER
"whplus1", -- wt ht 1SD
"ddsix_rec", -- Minimum dietary diversity 4 or more 6 food groups
"bmiplus3", -- bmi age 3SD
"bmiplus1", -- bmi age 1SD
"whmin2", -- wt ht 2SD
"total_feeds", -- Total milk and non milk feeds yesterday
"meal_frequency_rec1", -- Minimum meal frequency 2 or more Breastfed 6 8 months
"nonbreast_feeds", -- Minimum meal frequency 4 or more 2 milk Non breastfed 6 23 months
"casenum", -- Case ID
"zhaz", -- Length height for age z score
"zbmi", -- BMI for age z score
"zwaz", -- Weight for age z score
"zwhz", -- Weight for length height z score
"whplus3", -- wt ht 3SD
"bmimin2", -- bmi age 2SD
"formula_feeds", -- none
"dd5", -- Eggs
"dd1", -- Grains roots and tubers
"liquids_other", -- none
"exclusive_breast", -- Exclusive breastfeeding status
"chwt", -- Child sampling weight
"oedema", -- Child has oedema
"d76", -- D76 RESULT
"at_sh", -- ANTHRO START HOUR
"d56", -- D56 Was the child given less than usual to drink about the same or more than usual to drink
"d48", -- D48 Any grubs snails or insects
"d16", -- D16 Has the child ever been breastfed
"d15", -- D15 CHECK D07 CHILD UNDER 24 MONTHS
"d34", -- D34 Any foods that are yellow or orange inside
"liquids_other_min", -- none
"d59_10", -- D59 Where did you seek advice or treatment
"d26", -- D26 Did the child have any juice or juice drinks
"d64_13", -- D64 What else was given to treat the diarrhea
"ddseven_rec", -- Minimum dietary diversity 4 or more 7 food groups
"d64_3", -- D64 What else was given to treat the diarrhea
"d44", -- D44 Any cheese yogurt or other milk products
"dd7", -- Other fruits and vegetables
"d59_16", -- D59 Where did you seek advice or treatment
"milk_feeds", -- none
"at_spd", -- ANTHRO SUPERVISOR DAY
"d32", -- D32 Did the child have any other liquids
"d19", -- D19 Was the child given any vitamin drops or other medicines as drops yesterday during the day or at night
"d62b", -- D62B Was the child given any reconstituted ORS liquid provided through govt health facilities
"liquids_nonmilk", -- none
"d14", -- D14 CHECK D07 CHILD UNDER 60 MONTHS
"d59_14", -- D59 Where did you seek advice or treatment
"d64_2", -- D64 What else was given to treat the diarrhea
"liquids", -- Liquids child drank yesterday
"d59_19", -- D59 Where did you seek advice or treatment
"d75", -- D75 HEIGHT MEASURED LAYING DOWN OR STANDING UP
"d64_9", -- D64 What else was given to treat the diarrhea
"d05m", -- D05 Child s month of birth
"bmiplus2", -- bmi age 2SD
"d30", -- D30 Did the child consume any thin porridge
"breast_status", -- Breastfeeding status
"d23", -- D23 How many times yestersay did the child consume any formula
"dd6", -- Vitamin A rich fruits and vegetables
"d64_1", -- D64 What else was given to treat the diarrhea
"d59_12", -- D59 Where did you seek advice or treatment
"d59_20", -- D59 Where did you seek advice or treatment
"wamin2", -- wt age 2SD
"d59_11", -- D59 Where did you seek advice or treatment
"strata", -- Strata
"ddseven", -- Dietary Diversity 7 food groups for breastfed children
"d64_10", -- D64 What else was given to treat the diarrhea
"id",
"d64_5", -- D64 What else was given to treat the diarrhea
"region", -- PVO
"breastfeeding", -- Child is breastfeeding
"d62a", -- D62A Was the child given any fluid made from a special packet called ORS Sachet such as Zinkin or RESTORE
"d50", -- D50 Did the child eat any solid semi solid or soft foods yesterday during the day or at night
"d64_18", -- D64 What else was given to treat the diarrhea
"d64_16", -- D64 What else was given to treat the diarrhea
"d33", -- D33 Any foods made from grains
"d59_5", -- D59 Where did you seek advice or treatment
"d21", -- D21 Did the child have plain water
"d59_15", -- D59 Where did you seek advice or treatment
"d45", -- D45 Any foods made with oils fats or butter
"d59_8", -- D59 Where did you seek advice or treatment
"ort", -- Children 0 59 with diarrhea treated with ORT
"urbanr", -- Urban Rural
"diarrhea", -- Children 0 59 who had diarrhea in the last two weeks
"foods", -- Foods the child ate yesterday
"d28", -- D28 Did the child have any yogurt
"d42", -- D42 Any fish or seafood
"d64_6", -- D64 What else was given to treat the diarrhea
"d64_7", -- D64 What else was given to treat the diarrhea
"d25" -- D25 How many times yesterday did the child consume any milk
FROM
"usaid-gov/baseline-study-of-food-for-peace-title-ii-mkec-gk6e: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 usaid-gov/baseline-study-of-food-for-peace-title-ii-mkec-gk6e with SQL in under 60 seconds.
This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.usaid.gov. When you queryusaid-gov/baseline-study-of-food-for-peace-title-ii-mkec-gk6e:latest on the DDN, we "mount" the repository using the socrata mount handler. The mount handler proxies your SQL query to the upstream data source, translating it from SQL to the relevant language (in this case SoQL).
We also cache query responses on the DDN, but we run the DDN on multiple nodes so a CACHE_HIT is only guaranteed for subsequent queries that land on the same node.
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 (like this repository), 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, where the author has pushed Splitgraph Images to the repository, you can "clone" and/or "checkout" the data using sgr cloneand sgr checkout.
Mounting Data
This repository is an external repository. It's not hosted by Splitgraph. It is hosted by data.usaid.gov, and Splitgraph indexes it. This means it is not an actual Splitgraph image, so you cannot use sgr clone to get the data. Instead, you can use the socrata adapter with the sgr mount command. Then, if you want, you can import the data and turn it into a Splitgraph image that others can clone.
First, install Splitgraph if you haven't already.
Mount the table with sgr mount
sgr mount socrata \
"usaid-gov/baseline-study-of-food-for-peace-title-ii-mkec-gk6e" \
--handler-options '{
"domain": "data.usaid.gov",
"tables": {
"baseline_study_of_food_for_peace_title_ii": "mkec-gk6e"
}
}'That's it! Now you can query the data in the mounted table like any other Postgres table.
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, usaid-gov/baseline-study-of-food-for-peace-title-ii-mkec-gk6e is just another Postgres schema.