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_zambia_baseline_population_based table in this repository, by referencing it like:
"usaid-gov/feed-the-future-zambia-baseline-population-based-tkib-xq77:latest"."feed_the_future_zambia_baseline_population_based"or in a full query, like:
SELECT
":id", -- Socrata column ID
"i14", -- NOT ASKED IN THIS SURVEY
"d05", -- What is the main type of toilets your household uses?
"i24", -- Did child s name have any milk such as tinned, powdered, or fresh animal milk?
"i10", -- WEIGHT IN KILOGRAMS
"f04", -- How often did this happen in the past 4 weeks 30 days ?
"i44", -- Cheese, yogurt, or other milk products
"i11", -- HEIGHT IN CENTIMETERS:
"i03", -- What is child s name s sex?
"waz2", -- Weight for Age ( -2sd)
"minmore", -- Min. Meal Freq. for BF Children
"i25", -- How many times yesterday during the day or at night did child s name consume any milk?
"i46", -- Any sugary foods such as chocolates, sweets, candies, pastries, cakes, or biscuits
"d06", -- What is the main source of drinking water for your household?
"i35", -- White potatoes, white yams, manioc, cassava, other local root crops or any other foods made from roots
"i04_dd", -- On what day was child s name born?
"i17", -- Was child s name breastfed yesterday during the day or at night?
"i06", -- How many months old is child s name ?
"i04_mm", -- In what month was child s name born?
"beansgrp", -- Legumes and nuts
"eggsgrp", -- Eggs
"i42", -- Fresh or dried fish, shellfish, or seafood
"pbs_id", -- Unique household ID
"d02", -- Floor material
"i39", -- Liver, kidney, heart, or other organ meats
"f01", -- In the past 4 weeks 30 days was there ever no food to eat of any kind in your house because of lack of resources to get food?
"a09",
"i07_mo", -- A) IS THE YEAR RECORDED IN I04 CONSISTENT WITH THE AGE IN YEARS RECORDED IN I05?
"i43", -- Any foods made from beans, peas, lentils, nuts, or seeds such as local food names
"f05", -- In the past 4 weeks 30 days did you or any household member go a whole day and night without eating anything at all because there was not enough food?
"haz", -- Stunting Z-score
"i32", -- Any other liquids?
"i50", -- Did child s name eat any solid, semi-solid, or soft foods yesterday during the day or at night?
"vitagrp", -- Vit. A rich fruits and vegetables
"nomilk", -- Total Number of 6 Food Groups (Not Including Dairy, NBF children)
"i19", -- Was child s name given any vitamin drops or other medicines as drops yesterday during the day or at night?
"i41", -- Eggs
"haz2", -- Height for Age ( -2sd)
"i13_a", -- NOT ASKED IN THIS SURVEY
"othfrtgrp", -- Other fruits and vegetables
"i12", -- Derived in ODK
"i47", -- Condiments for flavor, such as chilies, spices, herbs, or fish powder
"i49", -- Foods made with red palm oil, red palm nut, or red palm nut pulp sauce
"a21", -- Final outcome of interview
"i34", -- Pumpkin, carrots, squash, or sweet potatoes that are yellow or orange inside or other local yellow orange foods
"milkfeeds", -- Breast milk or milk products
"i29", -- How many times yesterday during the day or at night did child s name consume any yogurt?
"whz", -- Wasting Z-score
"i33", -- Food made from grains, such as bread, rice, noodles, porridge, or other local grain food
"a06", -- Type of household
"country", -- Country recorded in module G
"milkgrp", -- Dairy products (milk, yogurt, cheese)
"i15", -- CHECK QUESTION I05. IS THE CHILD UNDER 2 YEARS OF AGE?
"i13_consent", -- NOT ASKED IN THIS SURVEY
"d04", -- How many rooms are there in this dwelling? (Do not count bathrooms, hallways, garage, toilet, cellar, kitchen)
"i18", -- Did child s name consume breast milk in any of these ways yesterday during the day or at night?
"urbrur", -- Urban, rural
"i45", -- Any oil, fats, or butter, or foods made with any of these
"anymilk",
"i48", -- Grubs, snails or insects
"i40", -- Any meat, such as beef, pork, lamb, goat, chicken, or duck
"i16", -- Has child s name ever been breastfed?
"f02", -- How often did this happen in the past 4 weeks 30 days ?
"a02", -- Household listing number
"i07_yr", -- B) ARE YEAR AND MONTH OF BIRTH RECORDED IN I04 CONSISTENT WITH AGE IN MONTHS RECORDED IN I06?
"id_code", -- Respondent ID in the household
"whz2", -- Weight for Height ( -2sd)
"i23", -- How many times yesterday during the day or at night did child s name consume any formula?
"i05", -- How old was child s name at his her last birthday?
"i28", -- Yogurt?
"i31", -- Any other liquids such as list other water-based liquids available in the local setting ?
"chwght", -- WEIGHT IN KILOGRAMS
"mad", -- FTF Minimum Acceptable Diet
"waz", -- Weight for age z-score based on WHO reference
"f03", -- In the past 4 weeks 30 days did you or any household member go to sleep at night hungry because there was not enough food?
"totalfeeds", -- Total Number of Feedings
"bmiz", -- BMI for age z-score based on WHO reference
"a05", -- Sex of respondent
"i51", -- How many times did child s name eat solid, semi-solid, or soft foods other than liquids yesterday during the day or at night?
"i38", -- Any other fruits or vegetables
"i26", -- Did child s name have any juice or juice drinks?
"i21", -- Plain water?
"i22", -- Infant formula such as insert local examples ?
"i30", -- Did child s name have any thin porridge?
"d01", -- Roof top material (outer covering)
"d03", -- Exterior Walls
"f06", -- How often did this happen in the past 4 weeks 30 days ?
"i04_yy", -- In what year was child s name born?
"i09", -- DOES CHILD HAVE EDEMA?
"d08", -- What is the main source of cooking fuel for your household?
"d07", -- Does this household have electricity?
"i08", -- CHECK I06. IS THE CHILD UNDER 60 MONTHS?
"i37", -- Ripe mangoes, ripe papayas or other local vitamin A-rich fruits
"i20", -- Was child s name given local name for oral rehydration solution yesterday during the day or at night?
"i01", -- CAREGIVER S ID CODE FROM THE HOUSEHOLD
"modd_missing", -- Missing all elements from module D
"i27", -- Clear broth?
"allgrp", -- Total Number of 7 Food Groups (BF children)
"grainsgrp",
"bf", -- Child Breastfed or Received Breastmilk Yesterday
"fleshgrp", -- Flesh foods (meat, fish, poultry and liver organ meats)
"i36", -- Any dark green leafy vegetables such as local dark green leafy vegetables
"modf_missing", -- Missing all elements from module F
"h07" -- WEIGHT IN KILOGRAMS
FROM
"usaid-gov/feed-the-future-zambia-baseline-population-based-tkib-xq77:latest"."feed_the_future_zambia_baseline_population_based"
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-zambia-baseline-population-based-tkib-xq77 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/feed-the-future-zambia-baseline-population-based-tkib-xq77: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/feed-the-future-zambia-baseline-population-based-tkib-xq77Checkout 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/feed-the-future-zambia-baseline-population-based-tkib-xq77:latestThis will download all the objects for the latest tag of usaid-gov/feed-the-future-zambia-baseline-population-based-tkib-xq77 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/feed-the-future-zambia-baseline-population-based-tkib-xq77: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/feed-the-future-zambia-baseline-population-based-tkib-xq77: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/feed-the-future-zambia-baseline-population-based-tkib-xq77 is just another Postgres schema.