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:
"datahub-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
"minmilk", -- This measure indicates that child who did not receive breast milk during the prior day received two or more feedings of milk.
"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
"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
"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.
"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.
"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?
"i29", -- How many times yesterday during the day or at night did the CHILD S NAME consume any yogurt?
"pbs_id", -- Administrative variable for identifying households
"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.
"today", -- The day of interview as a string variable, MM-DD-YYYY
"i04_m", -- What is the child s birthday? In what month and year was CHILD S NAME born? Month
"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.?
"i03", -- What is CHILD S NAME sex?
"i11", -- Measured height in centimeters
"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
"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.
"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
"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.
"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?
"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.
"minfrequency", -- The is a binary measure indicating that a child meets the minimum meal frequency for their age and breastfeeding status.
"i11a", -- Was the child measured lying down or standing up?
"i32", -- Yesterday, during the day or night, did CHILD S NAME have any other liquids?
"caregiver", -- Caregiver s ID Code from the household roster
"c_meatfish", -- This variable indicates that the child consumed a food from the flesh foods (meat, fish, poultry, and liver organ meats) group.
"i16", -- Has CHILD S NAME ever been breastfed?
"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.
"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.
"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
"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?
"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?
"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
"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.
"i51", -- How many times did CHILD S NAME eat solid, semi-solid, or soft foods other than liquids yesterday during the day or night?
"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?
"waz3", -- This is an indicator of severe underweight status, based on the children falling below -3 standard deviations of the WHO reference population.
"i04_y", -- What is the child s birthday? In what month and year was CHILD S NAME born? Year
"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)
"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?
"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.
"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,
"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.
"i06", -- How many months old is CHILD S NAME ? Record age in completed months.
"c_eggs", -- This variable indicates that the child consumed a food from the eggs group.
"xbfsample", -- This measure indicates that a child is aged 0-5 months.
"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,
"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
"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
"i23", -- How many times yesterday, during the day or at night, did the child consume any formula?
"country", -- These data were collected in Malawi
"i20", -- Was CHILD S NAME given oral rehydration solution yesterday during the day or at night?
"i31", -- Yesterday, during the day or night, did CHILD S NAME have Any other liquids such as gripe water, glucose water, or sugar water?
"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.
"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
"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.
"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.
"i07a", -- Check: Is the year recorded in I04 or I04D consistent with the age in years recorded in I05?
"whz3", -- This is an indicator of severe wasting status, based on the children falling below -3 standard deviations of the WHO reference population.
"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,
"i07b", -- Are year and month of birth recorded in I04 or I04D consistent with age in months recorded in I06?
"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
"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?
"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.
"ghht", -- Derived gendered household type
"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.
"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.
"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
"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?
"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
"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
"i28", -- Yesterday, during the day or night, did the CHILD S NAME have any yogurt?
"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.
"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?
"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.?
"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
"c_vita", -- This variable indicates that the child consumed a food from the Vitamin A-rich fruits and vegetables group.
"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.
"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.
"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",
"i30", -- Yesterday, during the day or night, did CHILD S NAME have any thin porridge?
"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?
"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.
"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)
"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.
FROM
"datahub-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 datahub-usaid-gov/feed-the-future-malawi-interim-survey-in-the-zone-k3ij-9jgw with SQL in under 60 seconds.
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, 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/feed-the-future-malawi-interim-survey-in-the-zone-k3ij-9jgw: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/feed-the-future-malawi-interim-survey-in-the-zone-k3ij-9jgwCheckout 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/feed-the-future-malawi-interim-survey-in-the-zone-k3ij-9jgw:latestThis will download all the objects for the latest tag of datahub-usaid-gov/feed-the-future-malawi-interim-survey-in-the-zone-k3ij-9jgw 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/feed-the-future-malawi-interim-survey-in-the-zone-k3ij-9jgw: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/feed-the-future-malawi-interim-survey-in-the-zone-k3ij-9jgw: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/feed-the-future-malawi-interim-survey-in-the-zone-k3ij-9jgw is just another Postgres schema.