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-hw36-g6pk:latest"."feed_the_future_malawi_interim_survey_in_the_zone"or in a full query, like:
SELECT
":id", -- Socrata column ID
"d09", -- Does this household have electricity?
"roofcat", -- This measure is a recoded category of variable D01, roof type. The variable is classified into three categories, natural, rudimentary, and finished, according to the classification used in the questionnaire.
"a19_3", -- Native language of respondent SELECT ALL THAT APPLY 3 - TUMBUKA
"nyouth15_29", -- This measure is a count of youth aged 15 to 29 years (i.e., greater than 14 years and less than 30 years) in the household roster. The AGE and SEX variables in the HHMEMBERS file are used to generate these variables. The age and sex information collected
"f03", -- In the past 30 days did you or any household member go to sleep at night hungry because there was not enough food?
"solidfuel", -- This variable is a recode of variable D10 and identifies the type of primary fuel used for cooking. Solid fuel is coded as a 1 and includes any biomass, such as coal, lignite, charcoal, wood, straw shrubs grass, agricultural crop residue, and animal dung.
"severehunger", -- This dichotomous variable indicates that a household experiences severe hunger (4 or more on the household hunger scale).
"a11", -- Total number of women 15-49
"a18", -- Language of interview
"d07", -- How many households use this toilet?
"nages65_69", -- This measure is a count of household members in the household who are between the ages of 65 to 69 years old.
"nages70_74", -- This measure is a count of household members in the household who are between the ages of 70 to 74 years old.
"nages95_", -- This measure is a count of household members in the household who are 95 years old or older.
"nmaleadults", -- This measure is a count of the men aged 18 and older in the household roster. The AGE and SEX variables in the HHMEMBERS file are used to generate these variables. The age and sex information collected in individual modules were not used for these measure
"a19_7", -- Native language of respondent SELECT ALL THAT APPLY 7 - OTHER
"a17", -- Language of questionnaire
"nages40_44", -- This measure is a count of household members in the household who are between the ages of 40 to 44 years old.
"hhsizecat", -- This measure if a recode of the HHSIZE variable. Households are classified by the number of individuals living in the household, where those with 1-5 household members are termed small, those with 6-10 members are termed medium, and those with 11 or m
"hhhunger", -- This variable is an indicator that a household experiences moderate or severe HH hunger. The weighted mean of this variable is the Feed the Future Indicator of household hunger.
"nages10_14", -- This measure is a count of household members in the household who are between the ages of 10 to 14 years old.
"nages85_89", -- This measure is a count of household members in the household who are between the ages of 85 to 89 years old.
"newf02", -- This is a recode of F01 and F02. It shows how often the household was unable to eat because there was no food and the household lacked the resources to acquire food.
"nages20_24", -- This measure is a count of household members in the household who are between the ages of 20 to 24 years old.
"a04", -- TA Town - Anonymous
"d03", -- Observed exterior walls
"cluster", -- Cluster number
"f01", -- In the past 30 days was there ever no food to eat of any kind in your house because of lack of resources to get food?
"a20", -- Was a translator used?
"a06", -- Region
"nages5_9", -- This measure is a count of household members in the household who are between the ages of 5 to 9 years old.
"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.
"d08", -- What is the main source of drinking water for your household?
"zoi", -- Was the data collected by Westat or ICF?
"maxeducat", -- This variable is the maximum level of household education. It contains four categories: None, Less than primary, Primary, and Secondary or more. It represents the category of the highest level of education in the household.
"nages0_4", -- This measure is a count of household members in the household who are between the ages of 0 to 4 years old.
"country", -- These data were collected in Malawi
"a05", -- District
"d02", -- Observed floor material
"a19_5", -- Native language of respondent SELECT ALL THAT APPLY 5 - NGONI
"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.
"d04", -- How many rooms in this dwelling are used for sleeping?
"nages15_19", -- This measure is a count of household members in the household who are between the ages of 15 to 19 years old.
"d06", -- Do you share this toilet with other households?
"strata3", -- Stratification for subgroup 3
"nages90_94", -- This measure is a count of household members in the household who are between the ages of 90 to 94 years old.
"d10", -- What is the main source of cooking fuel for your household?
"nfemaleadults", -- This measure is a count of the women aged 18 and older in the household roster. The AGE and SEX variables in the HHMEMBERS file are used to generate these variables. The age and sex information collected in individual modules were not used for these measu
"electric", -- This variable is a recode of D09 and indicates, with a 1, that a household has access to electricity.
"nages35_39", -- This measure is a count of household members in the household who are between the ages of 35 to 39 years old.
"a19_2", -- Native language of respondent SELECT ALL THAT APPLY 2 - YAO
"nages80_84", -- This measure is a count of household members in the household who are between the ages of 80 to 84 years old.
"f06", -- How often did this happen in the past 30 days?
"d05", -- What is the main type of toilet your household uses?
"hungerscale", -- Derived household hunger scale, ranging from 0 to 6 where higher values indicate greater hunger and food insecurity.
"impwater", -- This is a binary indicator of improved drinking water. It is recoded from variable D08 using WHO and UNICEF classifications. Improved water includes piped water, tube well borehole, protected dug well, protected spring, and rainwater collection. Unimpro
"f04", -- How often did this happen in the past 30 days?
"nages25_29", -- This measure is a count of household members in the household who are between the ages of 25 to 29 years old.
"impsanitation", -- This is a binary indicator of access to improved sanitation facilities. The variable is based on questions D05 and D06 following WHO and UNICEF classifications. Improved sanitation is classified as flush or pour toilets that are flushed to piped sewer sys
"a10", -- Total persons in household
"nages75_79", -- This measure is a count of household members in the household who are between the ages of 75 to 79 years old.
"modhunger", -- This dichotomous variable indicates that a household experiences moderate hunger (2 or 3 on the household hunger scale).
"f05", -- In the past 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?
"nages30_34", -- This measure is a count of household members in the household who are between the ages of 30 to 34 years old.
"pbs_id", -- Administrative variable for identifying households
"wallcat", -- This measure is a recoded category of variable D03, roof type. The variable is classified into three categories, Natural, Rudimentary, and Finished, according to the classification used in the questionnaire.
"nages60_64", -- This measure is a count of household members in the household who are between the ages of 60 to 64 years old.
"nages50_54", -- This measure is a count of household members in the household who are between the ages of 50 to 54 years old.
"nkids0_1", -- This measure is a count of the children aged 0 to 1 years (i.e., less than 2 years) in the household roster. The AGE and SEX variables in the HHMEMBERS file are used to generate these variables. The age and sex information collected in individual modules
"nkids0_4", -- This measure is a count of the children aged 0 to 4 years (i.e., less than 5 years) in the household roster. The AGE and SEX variables in the HHMEMBERS file are used to generate these variables. The age and sex information collected in individual modules
"floorcat", -- This measure is a recoded category of variable D02, roof type. The variable is classified into three categories, Natural, Rudimentary, and Finished, according to the classification used in the questionnaire.
"nages45_49", -- This measure is a count of household members in the household who are between the ages of 45 to 49 years old.
"d01", -- Observed roof top material (outer covering)
"hhsize", -- Derived number of individuals in the household based on the household roster
"nages55_59", -- This measure is a count of household members in the household who are between the ages of 55 to 59 years old.
"ghht", -- Derived gendered household type
"urbrur", -- Location type (urban rural)
"newf04", -- This is a recode of F03 and F04. It shows how often household members went to sleep hungry because there was not enough food.
"outcome", -- A09. overall result code
"f02", -- How often did this happen in the past 30 days?
"a19_1", -- Native language of respondent SELECT ALL THAT APPLY 1 - CHICHEWA
"a12", -- Total number of children 0-5
"a19_4", -- Native language of respondent SELECT ALL THAT APPLY 4 - LOMWE
"a19_6", -- Native language of respondent SELECT ALL THAT APPLY 6 - SENA
"nkids5_17", -- This measure is a count of the children aged 5 to 17 years (i.e., greater than 4 years and less than 18 years) in the household roster. The AGE and SEX variables in the HHMEMBERS file are used to generate these variables. The age and sex information colle
"newf06", -- This is a recode of F05 and F06. It shows how often household members went all day without eating.
"littlehunger", -- This dichotomous variable indicates that a household experiences little or no hunger (0 or 1 on the household hunger scale).
"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
"s_hh_wt3", -- Household weights, adjusted for non-response, subgroup 3
"today", -- The day of interview as a string variable, MM-DD-YYYY
"personsperroom" -- This measure is the number of household members per sleeping room in the household. It is created by dividing HHSIZE by the number of sleeping rooms (D04).
FROM
"usaid-gov/feed-the-future-malawi-interim-survey-in-the-zone-hw36-g6pk: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-hw36-g6pk 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-hw36-g6pk: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
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-hw36-g6pk" \
--handler-options '{
"domain": "data.usaid.gov",
"tables": {
"feed_the_future_malawi_interim_survey_in_the_zone": "hw36-g6pk"
}
}'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-hw36-g6pk is just another Postgres schema.