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-fqgg-p3nf:latest"."baseline_study_of_food_for_peace_title_ii"or in a full query, like:
SELECT
":id", -- Socrata column ID
"d71d", -- D71D CHILDs DATE OF BIRTH DAY
"diarrhea", -- Children 0 59 who had diarrhea in the last two weeks
"foods", -- Foods the child ate yesterday
"agedays3", -- Child s age in days at time of anthropometry
"length", -- Child s length height in centimeters
"region", -- PVO
"agemos", -- Child s age in months at time of anthropometry
"agedays", -- Child s age in days at date of interview
"d76", -- D76 RESULT
"d71m", -- D71M CHILDs DATE OF BIRTH MONTH
"pvo", -- PVO
"d59_3", -- D59 Where did you seek advice or treatment
"d59_2", -- D59 Where did you seek advice or treatment
"d51", -- D51 How many times did the child eat any solid semi solid or soft foods other than liquids yesterday during the day or at night
"d45", -- D45 Any foods made with oils fats or butter
"d38", -- D38 Any other fruit or vegetables
"d37", -- D37 Any ripe mangos papayas melon or passionfruit
"d36", -- D36 Any dark green leafy vegetables
"d32", -- D32 Did the child have any other liquids
"d26", -- D26 Did the child have any juice or juice drinks
"d25", -- D25 How many times yesterday did the child consume any milk
"d24", -- D24 Did the child cosume any milk such as tinned powdered or animal milk
"d19", -- D19 Was the child given any vitamin drops or other medicines as drops yesterday during the day or at night
"d18", -- D18 Did child consume breast milk in any of these ways yesterday during the day or night
"d06", -- D06 How old was the child on his her last birthday
"d03", -- D03 CHILDS LINE NUMBER FROM ROSTER
"wamin2", -- wt age 2SD
"hamin3", -- ht age 3SD
"bmiplus3", -- bmi age 3SD
"bmimin2", -- bmi age 2SD
"whmin2", -- wt ht 2SD
"whplus2", -- wt ht 2SD
"whplus1", -- wt ht 1SD
"zbmi", -- BMI for age z score
"d48", -- D48 Any grubs snails or insects
"d28", -- D28 Did the child have any yogurt
"ddseven_rec", -- Minimum dietary diversity 4 or more 7 food groups
"ort", -- Children 0 59 with diarrhea treated with ORT
"at_any", -- Anthropometry date Year
"breastfeeding", -- Child is breastfeeding
"breast_status", -- Breastfeeding status
"dd4", -- Flesh foods meat fish poultry and liver organ meats and other misc small animal protein
"dd7", -- Other fruits and vegetables
"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
"ddsix", -- Dietary Diversity 6 food groups for non breastfed children
"d62a", -- D62A Was the child given any fluid made from a special packet Sachet SRO
"total_milk_feeds", -- Total milk feeds yesterday formula milk yogurt
"d64_4", -- D64 What else was given to treat the diarrhea
"vn", -- A02 VILLAGE NUMBER
"ddsix_rec", -- Minimum dietary diversity 4 or more 6 food groups
"hh", -- A01 HOUSEHOLD NUMBER
"d50", -- D50 Did the child eat any solid semi solid or soft foods yesterday during the day or at night
"total_feeds", -- Total milk and non milk feeds yesterday
"casenum", -- Child case number
"zhaz", -- Length height for age z score
"zwaz", -- Weight for age z score
"strata", -- Strata
"zwhz", -- Weight for length height z score
"cluster", -- Cluster
"whmin3", -- wt ht 3SD
"chwt", -- Child sampling weight
"bmiplus1", -- bmi age 1SD
"bmiplus2", -- bmi age 2SD
"id",
"d02", -- D02 CAREGIVERS LINE NUMBER FROM ROSTER
"d05d", -- D05 Childs day of birth
"d05m", -- D05 Childs month of birth
"d07", -- D07 How many months old is the child
"d14", -- D14 CHECK D07 CHILD UNDER 60 MONTHS
"d30", -- D30 Did the child consume any thin porridge
"member_id", -- Household member ID for merging with other modules
"d67", -- D67 ANTHROPOMETRY CHILDs LINE NUMBER FROM HH ROSTER
"dd3", -- Dairy products milk yogurt cheese
"d61", -- D61 Where did you first seek advice or treatment
"at_and", -- Anthropometry date Day
"a13y", -- YEAR OF FINAL VISIT
"dd2", -- Legumes and nuts
"d49", -- D49 Any foods made with red palm oil red palm nut or red palm nut pulp sauce
"d64_2", -- D64 What else was given to treat the diarrhea
"ddseven", -- Dietary Diversity 7 food groups for breastfed children
"urbanr", -- urbanr
"d59_4", -- D59 Where did you seek advice or treatment
"d46", -- D46 Any sugary foods such as chocolate and cake
"d22", -- D22 Did the child have infant formula
"d23", -- D23 How many times yestersay did the child consume any formula
"d05a", -- D05 Childs year of birth
"meal_frequency_rec1", -- Minimum meal frequency 2 or more Breastfed 6 8 months
"d16", -- D16 Has the child ever been breastfed
"mad", -- Minimum Acceptable Diet MAD
"reg", -- A05 REGION
"a13m", -- MONTH OF FINAL VISIT
"bmimin3", -- bmi age 3SD
"d31", -- D31 Did the child have any other water based liquids available in the local setting
"dd1", -- Grains roots and tubers
"d72", -- D72 SOURCE OF BIRTH DATE
"d44", -- D44 Any cheese yogurt or other milk products
"d77", -- D77 EDEMA
"d64_5", -- D64 What else was given to treat the diarrhea
"d58", -- D58 Did you seek advice or treatment for the diarrhea from any source
"d20", -- D20 Was the child given oral rehydration solution yesterday during the day or at night
"sex", -- Child s sex
"d21", -- D21 Did the child have plain water
"lorh", -- Height measured laying down or standing up
"d55", -- D55 Was there any blood in the stools
"whplus3", -- wt ht 3SD
"d47", -- D47 Any condiments such as chilies spices herbs or fish powder
"dd6", -- Vitamin A rich fruits and vegetables
"d69", -- D69 CHILSs SEX
"d70", -- D70 CHILDs AGE IN MONTHS
"d71a", -- D71A CHILDs DATE OF BIRTH YEAR
"d73", -- D73 CHILDs WEIGHT KILOGRAMS
"d74", -- D74 CHILDs HEIGHT CENTIMETERS
"a13d", -- DAY OF FINAL VISIT
"d15", -- D15 CHECK D07 CHILD UNDER 24 MONTHS
"anthro_date", -- Date anthropometry was done
"d29", -- D29 How many times yesterday during the day or at night did the child consume any yogurt
"d04", -- D04 Sex of child
"d40", -- D40 Any meat
"d59_5", -- D59 Where did you seek advice or treatment
"d35", -- D35 Potatoes or any other foods made from roots
"dd5", -- Eggs
"d39", -- D39 Any liver kidney heart or other organ meats blood
"at_anm", -- Anthropometry date Month
"meal_frequency_rec2", -- Minimum meal frequency 3 or more Breastfed 9 23 months
"d33", -- D33 Any foods made from grains
"d41", -- D41 Any eggs
"d43", -- D43 Any foods made from beans or seeds
"d54", -- D54 Has the child had diarrhea in the last two weeks
"d56", -- D56 Was the child given less than usual to drink about the same or more than usual to drink
"liquids", -- Liquids child drank yesterday
"d27", -- D27 Did the child have any clear broth
"d42", -- D42 Any fish or seafood
"d63", -- D63 Was anything else given to treat the diarrhea
"hamin2", -- ht age 2SD
"d34", -- D34 Any foods that are yellow or orange inside
"wamin3", -- wt age 3SD
"d64_3", -- D64 What else was given to treat the diarrhea
"exclusive_breast", -- Exclusive breastfeeding status
"d75", -- D75 HEIGHT MEASURED LAYING DOWN OR STANDING UP
"d64_1", -- D64 What else was given to treat the diarrhea
"birth_date", -- Child birthday
"int_date", -- Date interview was done
"weight2", -- Child s weight in kilograms
"oedema", -- Child has oedema
"d62b", -- D62B Was the child given any govt recommended homemade fluids Eau salee sucre ESS
"age_0_23", -- Age categories for children 0 to 23 months
"d17", -- D17 Was the child breastfed yesterday during the day or night
"wgting", -- Child sampling weight
"meal_frequency", -- Frequency of feeding of solid semi solid and soft foods yesterday
"nonbreast_feeds" -- Minimum meal frequency 4 or more 2 milk Non breastfed 6 23 months
FROM
"usaid-gov/baseline-study-of-food-for-peace-title-ii-fqgg-p3nf: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-fqgg-p3nf 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 usaid-gov/baseline-study-of-food-for-peace-title-ii-fqgg-p3nf: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 usaid-gov/baseline-study-of-food-for-peace-title-ii-fqgg-p3nfCheckout 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 usaid-gov/baseline-study-of-food-for-peace-title-ii-fqgg-p3nf:latestThis will download all the objects for the latest tag of usaid-gov/baseline-study-of-food-for-peace-title-ii-fqgg-p3nf 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 usaid-gov/baseline-study-of-food-for-peace-title-ii-fqgg-p3nf: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 usaid-gov/baseline-study-of-food-for-peace-title-ii-fqgg-p3nf: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, usaid-gov/baseline-study-of-food-for-peace-title-ii-fqgg-p3nf is just another Postgres schema.