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 guatemala_child_health_data table in this repository, by referencing it like:
"usaid-gov/guatemala-child-health-data-gazt-8tm5:latest"."guatemala_child_health_data"or in a full query, like:
SELECT
":id", -- Socrata column ID
"int_date", -- Date interview was done
"wom_age", -- Women s age in completed years
"total_members", -- Total household members
"wmwt", -- Sampling weight for women 15 49 adjusted for module E non response
"mtwt", -- Sampling weight for mothers of children under 5 years of age adjusted for module I non response
"anthro_date", -- Date anthropometry was done
"agemos", -- Child s age in months at time of anthropometry
"region", -- PVO
"lorh", -- Height measured laying down or standing up
"length", -- Child s length height in centimeters
"weight2", -- Child s weight in kilograms
"whplus2", -- wt ht 2SD
"oedema", -- Child has oedema
"meal_frequency_rec1", -- Minimum meal frequency 2 or more Breastfed 6 8 months
"d64_7", -- D64 What else did you give to treat diarrhea
"d31", -- D31 Any other liquid such as coffee tea water corn rice water barley water pelo de ma z chamomile
"d64_5", -- D64 What else did you give to treat diarrhea
"urbanr", -- Urban Rural
"total_milk_feeds", -- Total milk feeds yesterday formula milk yogurt
"child_max", -- Household has at least one child under 18
"diarrhea", -- Children 0 59 who had diarrhea in the last two weeks
"liquids", -- Liquids child drank yesterday
"dd4", -- Flesh foods meat fish poultry and liver organ meats
"meal_frequency", -- Frequency of feeding of solid semi solid and soft foods yesterday
"total_feeds", -- Total milk and non milk feeds yesterday
"zhaz", -- Length height for age z score
"zbmi", -- BMI for age z score
"whmin2", -- wt ht 2SD
"wamin2", -- wt age 2SD
"a05", -- A05 Department
"breastfeeding", -- Child is breastfeeding
"dd1", -- Grains roots and tubers
"mad", -- Minimum Acceptable Diet MAD
"ort", -- Children 0 59 with diarrhea treated with ORT
"wamin3", -- wt age 3SD
"hamin2", -- ht age 2SD
"age_0_23", -- Age categories for children 0 to 23 months
"d59_5", -- D59 Where did you seek advice or treatment for CHILD diarrhea
"d46", -- D46 Sugary foods such as chocolates sweets candies pastries cakes or biscuits
"d29", -- D29 How many times yesterday during the day or night did CHILD consume yogurt
"a09_2", -- A09 2 Month of interview
"d64_8", -- D64 What else did you give to treat diarrhea
"d59_7", -- D59 Where did you seek advice or treatment for CHILD diarrhea
"d62a", -- D62A Since CHILD started having diarrhea let him drink WATER
"wom_count", -- Number of women interviewed in household per MOD E E02
"d64_2", -- D64 What else did you give to treat diarrhea
"meal_frequency_rec2", -- Minimum meal frequency 3 or more Breastfed 9 23 months
"male_adult_max", -- Household has at least one male 18 or older
"d71a", -- D71 childs birth date YEAR
"d59_4", -- D59 Where did you seek advice or treatment for CHILD diarrhea
"d24", -- D24 Did he CHILD canned milk powdered milk or fresh cow or goat milk
"dd6", -- Vitamin A rich fruits and vegetables
"sex", -- Child s sex
"d27", -- D27 Clear broth
"d04", -- D04 Sex of child
"d18", -- D18 Did child have breast milk in any of these ways yesterday during the day or at night
"d59_2", -- D59 Where did you seek advice or treatment for CHILD diarrhea
"d21", -- D21 Pure water
"exclusive_breast", -- Exclusive breastfeeding status
"d55", -- D55 Was there any blood in the stools of CHILD
"d59_9", -- D59 Where did you seek advice or treatment for CHILD diarrhea
"at_id", -- ANTHROPOMETRY ID
"foods_min", -- Flag for case deletion Did not consume ANY foods in the last 7 days or MISSING
"d62c", -- D62C Since CHILD started having diarrhea let him drink ORAL REHYDRATION SOLUTION
"d05b", -- D05B Month of birth
"whplus1", -- wt ht 1SD
"d30", -- D30 Did CHILD drink any atole
"at_ano", -- Conducted AT A O ANTHROPOMETRY
"whplus3", -- wt ht 3SD
"d41", -- D41 Eggs
"d72", -- D72 Source of birth date
"d47", -- D47 Flavor seasonings such as chili spices herbs fish powder
"pvo", -- PVO
"d71d", -- D71 childs birth date DIA
"d42", -- D42 Fresh or dried fish shellfish or seafood
"d20", -- D20 Yesterday was CHILD given oral saline solution during the day or night
"ddseven_rec", -- Minimum dietary diversity 4 or more 7 food groups
"bmiplus1", -- bmi age 1SD
"d64_9", -- D64 What else did you give to treat diarrhea
"dd7", -- Other fruits and vegetables
"dd2", -- Legumes and nuts
"d40", -- D40 Meat such as poultry beef goat pork rabbit
"eligible_women", -- Women eligible for Module E between 15 and 49 years of age
"d75", -- D75 Height as
"d17", -- D17 Was CHILD breastfed yesterday during the day or night
"female_adult_max", -- Household has at least one female 18 or older
"d59_6", -- D59 Where did you seek advice or treatment for CHILD diarrhea
"d19", -- D19 Yesterday during the day or at night was CHILD given any vitamins or other medicines in drops
"d73", -- D73 Child Height in Centimeters
"d37", -- D37 Ripe mangoes ripe papayas cantaloupe
"d64_4", -- D64 What else did you give to treat diarrhea
"d59_10", -- D59 Where did you seek advice or treatment for CHILD diarrhea
"bmimin3", -- bmi age 3SD
"breast_status", -- Breastfeeding status
"id",
"d57", -- D57 During the time that CHILD had diarrhea was he she fed less than usual the same amount of food or more food
"bmimin2", -- bmi age 2SD
"d64_3", -- D64 What else did you give to treat diarrhea
"bmiplus3", -- bmi age 3SD
"d56", -- D56 Did CHILD drink less than usual the same or more liquids than usual
"ddseven", -- Dietary Diversity 7 food groups for breastfed children
"dd3", -- Dairy products milk yogurt cheese
"d38", -- D38 Other fruits or vegetables Cabbage broccoli tomatoes onions apples bananas and other fruits or vegetables
"ddsix_rec", -- Minimum dietary diversity 4 or more 6 food groups
"zwaz", -- Weight for age z score
"zwhz", -- Weight for length height z score
"whmin3", -- wt ht 3SD
"bmiplus2", -- bmi age 2SD
"hamin3", -- ht age 3SD
"agedays3", -- Child s age in days at time of anthropometry
"at_dia", -- Conducted AT Dia ANTHROPOMETRY
"a02", -- A02 Enumeration area number
"d02", -- D02 Order number of mother
"d03", -- D03 Serial Child
"d05a", -- D05a Birth Day
"d05c", -- D05c Year of Birth
"d06", -- D06 How old was CHILD on last birthday
"d07", -- D07 CHILD age in months
"d08b", -- D08B Check D08b
"agedays", -- Child s age in days at date of interview
"d16", -- D16 Was CHILD ever breastfed
"d23", -- D23 How many times did CHILD drink formula yesterday
"d32", -- D32 Any other liquid
"d34", -- D34 Zucchini carrots yellow sweet potatoes
"d35", -- D35 Potatoes yucca white sweet potatoes other roots or foods made from roots
"d36", -- D36 Green leafy vegetables such as spinach lettuce swiss chard turnip leaves amaranth zucchini leaves chickpea leaves watercress hierbamora macuy
"d50", -- D50 Did CHILD eat any solid semisolid or soft foods yesterday during the day or night
"d54", -- D54 Did CHILD have diarrhea at any point during the last two weeks
"d58", -- D58 Did you seek advice or treatment for CHILD diarrhea
"d59_1", -- D59 Where did you seek advice or treatment for CHILD diarrhea
"d64_1", -- D64 What else did you give to treat diarrhea
"hhwt", -- Sampling weight for households and household members
"gendered_hh", -- Gendered Household type
"mi_count1", -- Number of unique caretakers interviewed per hh according to MOD I I01C
"birth_date", -- Child birthday
"d59_8", -- D59 Where did you seek advice or treatment for CHILD diarrhea
"at_ropa", -- AT CLOTH
"at_mf", -- Module ends AT MF ANTHROPOMETRY Minutes
"at_hf", -- Module Time ends ANTHROPOMETRY AT HF
"d64_10", -- D64 What else did you give to treat diarrhea
"d67", -- D67 Order Number ANTHROPOMETRY
"a09_1", -- A09 1 Day of interview
"at_enu", -- Enumeration number ANTHROPOMETRY
"at_mes", -- Conducted AT MES ANTHROPOMETRY
"at_spid", -- Supervisor Id SP ID ANTHROPOMETRY
"at_spdia", -- SP Dia in which oversaw ANTHROPOMETRY
"at_spmes", -- SP MES in which oversaw ANTHROPOMETRY
"at_mi", -- Minutes module starts ANTHROPOMETRY
"at_hi", -- Anthropometry module starts Time
"at_hog", -- Home number ANTHROPOMETRY
"a09_3", -- A09 3 Year of interview
"member_idunique", -- Household member ID for merging with other modules
"strata", -- Strata
"cluster", -- Cluster
"d77", -- D77 Edema
"d76", -- D76 Result
"d74", -- D74 Childs weight in POUNDS
"d71m", -- D71 childs birth date MES
"d70", -- D70 Age in months
"d69", -- D69 Child sex
"d63", -- D63 Did you give CHILD any other treatment to help stop the diarrhea
"d62e", -- D62E Since CHILD started having diarrhea let him drink MEDICINES
"d62b", -- D62B Since CHILD started having diarrhea let him drink HOMEMADE WHEY
"d61", -- D61 Where did you first seek advice or treatment
"d59_3", -- D59 Where did you seek advice or treatment for CHILD diarrhea
"d51", -- D51 How many times did CHILD eat solid semi solid or soft foods yesterday during the day or night
"d45", -- D45 Oil butter margarine lard or foods made with any of these products
"d44", -- D44 Cheese cream milk liquid or powder cow milk goat milk yogurt or other dairy products
"d43", -- D43 Foods made of beans nuts lentils broad beens peas or seeds
"d39", -- D39 Entrails liver kidney heart stomach or other organs
"chwt", -- Child sampling weight
"wgting", -- Child sampling weight
"foods", -- Foods the child ate yesterday
"d33", -- D33 Foods made from grains such as tortillas tamales bread rice pasta cereals
"d26", -- D26 Did child consume any type of juice
"d25", -- D25 How many times yesterday during the day or night did CHILD consume milk
"d22", -- D22 Formula such as Similac Enfamil NAN
"d14", -- D14 Child is less than 60 months
"d08a", -- D08A Check D08a
"hog", -- NUMBER OF HOUSEHOLD
"nonbreast_feeds", -- Minimum meal frequency 4 or more 2 milk Non breastfed 6 23 months
"ddsix", -- Dietary Diversity 6 food groups for non breastfed children
"d28", -- D28 Yogurt
"dd5", -- Eggs
"d62f", -- D62F Since CHILD started having diarrhea let him drink OTHERS
"d64_6", -- D64 What else did you give to treat diarrhea
"d15", -- D15 Child is under 24 months
"d62d", -- D62D Since CHILD started having diarrhea let him drink HOME REMEDIES
"at_spano" -- SP A O in which oversaw ANTHROPOMETRY
FROM
"usaid-gov/guatemala-child-health-data-gazt-8tm5:latest"."guatemala_child_health_data"
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/guatemala-child-health-data-gazt-8tm5 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/guatemala-child-health-data-gazt-8tm5: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/guatemala-child-health-data-gazt-8tm5Checkout 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/guatemala-child-health-data-gazt-8tm5:latestThis will download all the objects for the latest tag of usaid-gov/guatemala-child-health-data-gazt-8tm5 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/guatemala-child-health-data-gazt-8tm5: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/guatemala-child-health-data-gazt-8tm5: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/guatemala-child-health-data-gazt-8tm5 is just another Postgres schema.