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 feed_the_future_malawi_interim_survey_in_the_zone table in this repository, by referencing it like:
"usaid-gov/feed-the-future-malawi-interim-survey-in-the-zone-k3ij-9jgw:latest"."feed_the_future_malawi_interim_survey_in_the_zone"or in a full query, like:
SELECT
":id", -- Socrata column ID
"i04_m", -- What is the child s birthday? In what month and year was CHILD S NAME born? Month
"i49", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Foods made with red palm oil, red palm nut, or red palm nut pulp sauce?
"project", -- Identifies whether the record is used to report on Feed the Future (FTF), Food for Peace (FFP), Catholic Relief Services (CRS), or Project Concern International (PCI) programming. Due to coordinated survey collection, the same record may be used to evalu
"i30", -- Yesterday, during the day or night, did CHILD S NAME have any thin porridge?
"c_vita", -- This variable indicates that the child consumed a food from the Vitamin A-rich fruits and vegetables group.
"c_nrvcc_any", -- This measure identifies whether any of the targeted nutrient-rich value chain commodities were consumed in the prior day. The weighted mean of this measure will produce the Feed the Future prevalence indicator.
"pbs_id", -- Administrative variable for identifying households
"i19", -- Now I would like to ask you about some medicines and vitamins that are sometimes given to infants. Was CHILD S NAME given any vitamin drops or other medicines as drops during the day or at night?
"i07b", -- Are year and month of birth recorded in I04 or I04D consistent with age in months recorded in I06?
"c_nrvcc1", -- This measure identifies whether targeted NRVCC was consumed in the prior day. The weighted mean of this measure will produce the Feed the Future prevalence indicator.
"i42a", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Any food made from groundnut or groundnut products such as groundnut flour, peanut butter, roasted groundnuts, bo
"i31", -- Yesterday, during the day or night, did CHILD S NAME have Any other liquids such as gripe water, glucose water, or sugar water?
"i37a", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Ripe mangoes, ripe papayas, apricots, cantaloupe melons, pulp from African locust bean, or other fruits that are
"i38a", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Any liver, kidney, heart, or other organ meats from domesticated animals such as beef, pork, lamb, goat, chicken,
"i04_y", -- What is the child s birthday? In what month and year was CHILD S NAME born? Year
"waz3", -- This is an indicator of severe underweight status, based on the children falling below -3 standard deviations of the WHO reference population.
"i38b", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Any meat from domesticated animals, such as beef, pork, lamb, goat, chicken, duck, or pigeon?
"i48", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Edible insects, mopane worms, grasshoppers or flying ants?
"xbfsample", -- This measure indicates that a child is aged 0-5 months.
"c_eggs", -- This variable indicates that the child consumed a food from the eggs group.
"iagecons", -- Flag checks for consistency between DOB (I04) and age (I05 or I06). Calculate age in months based on the reported date of birth or date of birth observed on a vaccination card. If the day of birth is missing, but month and year of birth are provided, the
"c_meatfish", -- This variable indicates that the child consumed a food from the flesh foods (meat, fish, poultry, and liver organ meats) group.
"caregiver", -- Caregiver s ID Code from the household roster
"i32", -- Yesterday, during the day or night, did CHILD S NAME have any other liquids?
"minfrequency", -- The is a binary measure indicating that a child meets the minimum meal frequency for their age and breastfeeding status.
"haz", -- Derived Z score of children s height for age (stunting), based on reference populations established by WHO UNICEF. This variable corresponds to _zlen that is generated by the igrowup program.
"i11", -- Measured height in centimeters
"i33", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Bread, savory biscuits, porridge, crackers, pasta, noodles, rice, or other foods made ??from grains such as corn,
"rcvd_milk", -- This measure is coded as 1 if the child received infant formula, milk, or yogurt, and the variable is coded as a 0 otherwise.
"i03", -- What is CHILD S NAME sex?
"c_foodmiss", -- This is a flag variable that indicates that the liquid food consumption questions,I21, I22, I24, I26, I27, I28, I30, I31, and I32, are all missing, and or the solid food consumptions questions, I33 - I49, are all missing. This variable is created to excl
"i27", -- Yesterday, during the day or night, did CHILD S NAME have any clear broth?
"idcode", -- Woman s ID code
"whz_flag", -- A flag indicating that the weight for height Z score is biologically implausible, determined by a Z score of less than -5 or greater than 5. This variable corresponds to _fwfl that is generated by the WHO igrowup program.
"age_cmpmth",
"mad", -- This measure is the Feed the Future indicator of Minimum Acceptable Diet. Children who receive a minimum acceptable diet are coded as 1, and those who do not achieve a minimum acceptable diet are coded as a 0.
"i08", -- Is CHILD S NAME under 60 months
"isex", -- Child s sex, based on question I03.
"i37b", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Any other fruits like bananas, apples, avocados, pineapples, berries, baobab fruit, etc.?
"i50", -- Did CHILD S NAME eat any solid, semi-solid, or soft foods yesterday during the day or at night? If YES PROBE: What kind of solid, semi-solid, or soft foods did CHILD S NAME eat?
"c_grains", -- This variable indicates that the child consumed a food from the grains, roots, and tubers group.
"c_nutsbeans", -- This variable indicates that the child consumed a food from the legumes and nuts group.
"madsample", -- This measure indicates that a child is aged 6-23 months.
"c_nrvcc2", -- This measure identifies whether targeted NRVCC was consumed in the prior day. The weighted mean of this measure will produce the Feed the Future prevalence indicator.
"i44", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Soured milk, cheese, yogurt, or other milk products?
"i15", -- Is the child under 2 years of age?
"today", -- The day of interview as a string variable, MM-DD-YYYY
"baz", -- Derived Z score of BMI for age, based on reference populations established by WHO UNICEF. This variable corresponds to _zbmi that is generated by the WHO igrowup program.
"whz", -- Derived Z score of children s weight for height (wasting), based on reference populations established by WHO UNICEF. This variable corresponds to _zwfl that is generated by the WHO igrowup program.
"i18", -- Sometimes babies are fed breast milk in different ways, for example by spoon, cup, or bottle. This can happen when the mother cannot always be with her baby. Sometimes babies are breastfed by another woman or given breast milk from another woman by spoon,
"whz3", -- This is an indicator of severe wasting status, based on the children falling below -3 standard deviations of the WHO reference population.
"i43a", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Any foods made from sesame or sesame flour?
"mindiversity", -- This variable indicates that the child meets the minimum dietary diversity for his or her breastfeeding status. Children who consume four or more food groups during the prior day meet the minimum dietary diversity, whereas those who consume fewer than fou
"i10", -- Measured weight in kilograms
"i17", -- Was CHILD S NAME breastfed yesterday during the day or at night?
"i39b", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Any flesh from wild animals, such as warthogs, buck, kudu, impala, antelopes, crocodile, cats, monkeys, alligator
"i35", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD White potatoes, white yams, cassava, plantains or any other foods made from roots?
"i28", -- Yesterday, during the day or night, did the CHILD S NAME have any yogurt?
"i42b", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Any foods made from soy or soy products such as soya bean flour, soy milk, soy mash relish, soy flitters, or soy
"xbf", -- This measure is the indicator for exclusive breastfeeding. Children are coded as a 1 if they consumed breastmilk during the prior day and did not consume any other types of food or liquid, including water. Note that children may receive oral rehydration s
"subgroup3", -- The 7-district FTF FEEDBACK ZOI which will include district level data from rural areas only of Michinji, Lilongwe, Dedza, Mangochi, Ntcheu, Balaka, Machinga.
"ghht", -- Derived gendered household type
"minmilk", -- This measure indicates that child who did not receive breast milk during the prior day received two or more feedings of milk.
"i51", -- How many times did CHILD S NAME eat solid, semi-solid, or soft foods other than liquids yesterday during the day or night?
"survey", -- Two surveys were administered. This variable identifies whether the survey was the Interim Feed the Future survey or the Baseline Food for Peace survey.
"i34b", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Any other dark yellow or orange fleshed roots, tubers, or vegetables such as yellow-fleshed sweet potatoes, pumpk
"i36a", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Any dark green leafy vegetables such as spinach, kale, okra, pumpkin leaves, amaranth leaves, or moringa leaves?
"anthrosample", -- This measure indicates that a child is aged 0-59 months.
"strata3", -- Stratification for subgroup 3
"child_wt3", -- Individual sampling weight for children aged 0 to 59 months. This should be used for subgroup 3 analyses.
"i21", -- Yesterday, during the day or night, did CHILD S NAME have any plain water?
"haz3", -- This is an indicator of severe stunting status, based on the children falling below -3 standard deviations of the WHO reference population.
"c_bmi", -- BMI calculated as weight (kg) divided by length height (cm) squared. This variable corresponds to _cbmi that is generated by the WHO igrowup program.
"c_dairy", -- This variable indicates that the child consumed a food from the dairy products (milk, yogurt, cheese) group.
"c_nrvcc3", -- This measure identifies whether targeted NRVCC was consumed in the prior day. The weighted mean of this measure will produce the Feed the Future prevalence indicator.
"i16", -- Has CHILD S NAME ever been breastfed?
"i11a", -- Was the child measured lying down or standing up?
"waz_flag", -- A flag indicating that the weight for age Z score is biologically implausible, determined by a Z score of less than -6 or greater than 5. This variable corresponds to _fwei that is generated by the WHO igrowup program.
"rcvd_food", -- This measure is coded as 1 if the child received any solid or semi-solid food, and the variable is coded as 0 otherwise. This measure is based on the foods reported in questions I33 through I49.
"i47", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Condiments for flavor, such as chilies, spices, herbs, fish powder, curry, or bicarbonate soda ash used for cooki
"i22", -- Yesterday, during the day or night, did the CHILD S NAME drink any Infant formula such as Nani, SMA, Nestle, Enfamil, Isomil, Lactogen, or S26?
"i07a", -- Check: Is the year recorded in I04 or I04D consistent with the age in years recorded in I05?
"rcvd_liquid", -- This measure is coded as 1 if the child received infant formula, milk, or yogurt, and the variable is coded as a 0 otherwise.
"rcvd_water", -- This measure is coded as 1 if the child received water, and the variable is coded as a 0 otherwise.
"i05", -- How old was CHILD S NAME at his or her last birthday? Record age in completed years.
"ianthro_flag", -- This flag measure indicates the reason for excluding a measure from the analysis of children anthropometry.
"urbrur", -- Location type (urban rural)
"i39a", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Any liver, kidney, heart, or other organ meats from wild animals such as warthogs, buck, kudu, impala, antelopes
"baz_flag", -- A flag indicating that the BMI for age Z score is biologically implausible, determined by a Z score of less than -5 or greater than 5. This variable corresponds to _fbmi that is generated by the WHO igrowup program.
"i06", -- How many months old is CHILD S NAME ? Record age in completed months.
"solidfoodfeeds", -- This variable is the total number of solid or semi-solid feedings the child received during the prior day, as reported in question I51.
"i34a", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Orange-fleshed sweet potatoes or foods made from orange-fleshed sweet potatoes such as porridge, flitters, or ste
"haz2", -- Indicator of child being stunted defined as falling below a -2 standard deviation of the WHO reference population
"c_foodsum6", -- This is the number of food groups consumed by the child in the prior day. It includes six of the seven food groups, excluding dairy. This measure represents the dietary diversity of non-breastfed children.
"i43b", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Any foods made from other nuts or seeds? EXCLUDE FOODS MADE FROM GROUNDNUTS OR SESAME SEEDS WHICH BELONG IN ABO
"i23", -- How many times yesterday, during the day or at night, did the child consume any formula?
"country", -- These data were collected in Malawi
"i40", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Eggs? (chicken, turkey, fowl, duck)
"i20", -- Was CHILD S NAME given oral rehydration solution yesterday during the day or at night?
"i24", -- Yesterday, during the day or night, did CHILD S NAME have any milk such as tinned, powdered, or fresh animal milk?
"i41", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Fresh or dried fish, shellfish, crabs, or seafood?
"i25", -- How many times yesterday, during the day or at night, did CHILD S NAME consume any milk?
"c_othfrtveg", -- This variable indicates that the child consumed a food from the other fruits and vegetables group.
"i29", -- How many times yesterday during the day or at night did the CHILD S NAME consume any yogurt?
"i36b", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Any other vegetables such as green beans, tomatoes, mushrooms, cabbage, cauliflower, broccoli etc.?
"waz", -- Derived Z score of children s weight for age (underweight), based on reference populations established by WHO UNICEF. This variable corresponds to _zwei that is generated by the WHO igrowup program.
"i45", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Any oil, fats, or butter, or foods made with any of these? INCLUDE GROUNDNUT OIL AND SESAME OIL.
"i42d", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Any other foods made from beans, peas, lentils or other legumes?
"c_foodsum", -- This is the number of food groups consumed by the child in the prior day. This variables takes the value of c_foodsum7 if the child consumed breast milk in the prior day and c_foodsum6 if the child did not consume breast milk in the prior day.
"i09", -- Does the child have edema?
"bf", -- This variable indicates whether a child consumed breastmilk during the prior day. Children who were breastfed or consumed breastmilk through other means are coded as a one. Children who were not breastfed and who did not consume breastmilk are coded as ze
"milkfeeds", -- The variable represents the number of milk feedings the child received during the prior day. Consumption of infant formula, milk, and yogurt count as milk feedings. This measure is required to assess minimum meal frequency for children who are not breastf
"zoi", -- Was the data collected by Westat or ICF?
"i46", -- This variable combines the spontaneously reported and the prompted recall of the child s consumption of FOOD during the prior day. FOOD Any sugary foods such as chocolates, sweets, candies, pastries, doughnuts, cakes, sweet biscuits, or sugar cane?
"whz2", -- Indicator of child being wasted defined as falling below a -2 standard deviation of the WHO reference population
"waz2", -- Indicator of child being underweight, defined as falling below a -2 standard deviation of the WHO reference population.
"i26", -- Yesterday, during the day or night, did the CHILD S NAME have any juice or juice drinks?
"cluster", -- Cluster number
"haz_flag", -- A flag indicating that the height for age Z score is biologically implausible, determined by a Z score of less than -6 or greater than 6. This variable corresponds to _flen that is generated by the igrowup program.
"c_foodsum7" -- This is the number of food groups consumed by the child in the prior day. It includes all seven food groups, including dairy. This measure represents the dietary diversity of breastfed children.
FROM
"usaid-gov/feed-the-future-malawi-interim-survey-in-the-zone-k3ij-9jgw:latest"."feed_the_future_malawi_interim_survey_in_the_zone"
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/feed-the-future-malawi-interim-survey-in-the-zone-k3ij-9jgw 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/feed-the-future-malawi-interim-survey-in-the-zone-k3ij-9jgw: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
Install Splitgraph Locally
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/feed-the-future-malawi-interim-survey-in-the-zone-k3ij-9jgw" \
--handler-options '{
"domain": "data.usaid.gov",
"tables": {
"feed_the_future_malawi_interim_survey_in_the_zone": "k3ij-9jgw"
}
}'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/feed-the-future-malawi-interim-survey-in-the-zone-k3ij-9jgw is just another Postgres schema.