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
"ddsix_rec", -- Minimum dietary diversity 4 or more 6 food groups
"whplus1", -- wt ht 1SD
"liquids_milk_min", -- none
"d58", -- D58 Did you seek advice or treatment for the diarrhea from any source
"total_milk_feeds", -- Total milk feeds yesterday formula milk yogurt
"d64_15", -- D64 What else was given to treat the diarrhea
"ddsix", -- Dietary Diversity 6 food groups for non breastfed children
"d36", -- D36 Any dark green leafy vegetables
"d69", -- D69 CHILS S SEX
"d05d", -- D05 Child s day of birth
"zwaz", -- Weight for age z score
"d05a", -- D05 Child s year of birth
"zbmi", -- BMI for age z score
"d06", -- D06 How old was the child on his her last birthday
"zhaz", -- Length height for age z score
"casenum", -- Case ID
"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
"nonbreast_feeds", -- Minimum meal frequency 4 or more 2 milk Non breastfed 6 23 months
"meal_frequency_rec1", -- Minimum meal frequency 2 or more Breastfed 6 8 months
"yogurt_feeds", -- none
"d07", -- D07 How many months old is the child
"meal_frequency", -- Frequency of feeding of solid semi solid and soft foods yesterday
"dd4", -- Flesh foods meat fish poultry and liver organ meats or misc small animal protein
"liquids_milk", -- none
"d64_6", -- D64 What else was given to treat the diarrhea
"foods", -- Foods the child ate yesterday
"d64_7", -- D64 What else was given to treat the diarrhea
"d64_19", -- D64 What else was given to treat the diarrhea
"d64_8", -- D64 What else was given to treat the diarrhea
"d59_7", -- D59 Where did you seek advice or treatment
"d72", -- D72 SOURCE OF BIRTH DATE
"at_anm", -- ANTHROPOMETRIST MONTH
"liquids_min", -- none
"urbanr", -- Urban Rural
"filter", -- none
"wgting", -- Child sampling weight
"agemos", -- Child s age in months at time of anthropometry
"agedays3", -- Child s age in days at time of anthropometry
"d64_9", -- D64 What else was given to treat the diarrhea
"age_0_23", -- Age categories for children 0 to 23 months
"meal_frequency_rec2", -- Minimum meal frequency 3 or more Breastfed 9 23 months
"whplus2", -- wt ht 2SD
"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
"d75", -- D75 HEIGHT MEASURED LAYING DOWN OR STANDING UP
"at_vn", -- ANTHRO VILLAGE NUMBER
"d59_19", -- D59 Where did you seek advice or treatment
"at_sm", -- ANTHRO START MINUTE
"d59_3", -- D59 Where did you seek advice or treatment
"d63", -- D63 Was anything else given to treat the diarrhea
"liquids_nonmilk_min", -- none
"d64_17", -- D64 What else was given to treat the diarrhea
"hamin2", -- ht age 2SD
"d64_4", -- D64 What else was 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
"at_anid", -- ANTHROPOMETRIST ID NUMBER
"liquids", -- Liquids child drank yesterday
"d64_2", -- D64 What else was given to treat the diarrhea
"at_and", -- ANTHROPOMETRIST DAY
"at_spid", -- ANTHRO SUPERVISOR ID NUMBER
"at_spm", -- ANTHRO SUPERVISOR MONTH
"at_spy", -- ANTHRO SUPERVISOR YEAR
"hamin3", -- ht age 3SD
"d51check", -- none
"whmin3", -- wt ht 3SD
"bmimin3", -- bmi age 3SD
"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
"at_any", -- ANTHROPOMETRIST YEAR
"d74", -- D74 CHILD S HEIGHT CENTIMETERS
"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
"d40", -- D40 Any meat
"diarrhea", -- Children 0 59 who had diarrhea in the last two weeks
"liquids_water", -- none
"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
"dd3", -- Dairy products milk yogurt cheese
"milk_feeds", -- none
"at_spd", -- ANTHRO SUPERVISOR DAY
"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
"d32", -- D32 Did the child have any other liquids
"d64_16", -- D64 What else was given to treat the diarrhea
"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
"d33", -- D33 Any foods made from grains
"at_fh", -- ANTHRO FINISH HOUR
"d59_5", -- 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
"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
"d14", -- D14 CHECK D07 CHILD UNDER 60 MONTHS
"d59_14", -- D59 Where did you seek advice or treatment
"d59_8", -- D59 Where did you seek advice or treatment
"d25", -- D25 How many times yesterday did the child consume any milk
"ort", -- Children 0 59 with diarrhea treated with ORT
"d59_11", -- D59 Where did you seek advice or treatment
"wamin2", -- wt age 2SD
"d59_20", -- D59 Where did you seek advice or treatment
"d59_12", -- D59 Where did you seek advice or treatment
"d64_1", -- D64 What else was given to treat the diarrhea
"dd6", -- Vitamin A rich fruits and vegetables
"d23", -- D23 How many times yestersay did the child consume any formula
"breast_status", -- Breastfeeding status
"d30", -- D30 Did the child consume any thin porridge
"bmiplus2", -- bmi age 2SD
"d05m", -- D05 Child s month of birth
"d48", -- D48 Any grubs snails or insects
"d16", -- D16 Has the child ever been breastfed
"dd1", -- Grains roots and tubers
"liquids_other", -- none
"exclusive_breast", -- Exclusive breastfeeding status
"chwt", -- Child sampling weight
"bmimin2", -- bmi age 2SD
"whplus3", -- wt ht 3SD
"oedema", -- Child has oedema
"dd5", -- Eggs
"d76", -- D76 RESULT
"formula_feeds", -- none
"region", -- PVO
"breastfeeding", -- Child is breastfeeding
"d62c", -- D62C Was the child given any govt recommended homemade fluids
"dd2", -- Legumes and nuts
"d17", -- D17 Was the child breastfed yesterday during the day or night
"total_feeds", -- Total milk and non milk feeds yesterday
"whmin2", -- wt ht 2SD
"bmiplus1", -- bmi age 1SD
"bmiplus3", -- bmi age 3SD
"dis", -- DISTRICT NUMBER
"vn", -- VILLAGE NUMBER
"hh", -- HOUSEHOLD NUMBER
"d02", -- D02 CAREGIVERS LINE NUMBER FROM ROSTER
"d03", -- D03 CHILDS LINE NUMBER FROM ROSTER
"d04", -- D04 Sex of child
"d64_5", -- D64 What else was given to treat the diarrhea
"zwhz", -- Weight for length height z score
"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
"id",
"d64_10", -- D64 What else was given to treat the diarrhea
"d28", -- D28 Did the child have any yogurt
"d42", -- D42 Any fish or seafood
"ddseven", -- Dietary Diversity 7 food groups for breastfed children
"strata" -- Strata
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.