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_uganda_interim_survey_in_the_zone table in this repository, by referencing it like:
"usaid-gov/feed-the-future-uganda-interim-survey-in-the-zone-rthi-gc3q:latest"."feed_the_future_uganda_interim_survey_in_the_zone"or in a full query, like:
SELECT
":id", -- Socrata column ID
"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 was not used for these measures in order to ensure that the information came from the same source.)
"a02", -- ENUMERATION AREA (EA) - Anonymized
"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 was not used for these measures in order to ensure that the information came from the same source.)
"a19_5", -- Native language of respondent SELECT ALL THAT APPLY 5 - Lugisu
"floorcat", -- This measure is a recoded category of variable D02, floor type. The variable is classified into three categories, Natural, Rudimentary, and Finished, according to the classification used in the questionnaire.
"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 systems, septic tanks, pit latrines, or unknown places. It also includes ventilated improved pit latrines, pit latrines with slabs, and composting toilets. Unimproved sanitation is flush toilets that flush to elsewhere, pit latrines without a slab open pit, bucket, hanging toilets, or no facilities, bush, field. All shared or public facilities are classified as unimproved.
"a04", -- Sub-county - Anonymized
"today", -- Survey Date
"nages15_19", -- This measure is a count of household members in the household who are between the ages of 15 to 19 years old.
"d07", -- How many households use this toilet?
"nages10_14", -- This measure is a count of household members in the household who are between the ages of 10 to 14 years old.
"pbs_id", -- Administrative variable for identifying households
"survey", -- These data were collected in the 2015 Interim Zone of Influence Survey.
"nages40_44", -- This measure is a count of household members in the household who are between the ages of 40 to 44 years old.
"nkids0_1", -- This measure is a count of the children aged 0 to 1 year (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 was not used for these measures in order to ensure that the information came from the same source.)
"hhsize", -- Derived number of individuals in the household based on the household roster
"a19_9", -- Native language of respondent SELECT ALL THAT APPLY 9 - Other (specify)
"nages60_64", -- This measure is a count of household members in the household who are between the ages of 60 to 64 years old.
"a13", -- Line number (i.e., IDCODE) of respondent to Module C
"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 collected in individual modules was not used for these measures in order to ensure that the information came from the same source.)
"f06", -- How often did this happen in the past 4 weeks 30 days ?
"a19_4", -- Native language of respondent SELECT ALL THAT APPLY 4 - Lusoga
"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.
"wallcat", -- This measure is a recoded category of variable D03, wall type. The variable is classified into three categories, Natural, Rudimentary, and Finished, according to the classification used in the questionnaire.
"nages80_84", -- This measure is a count of household members in the household who are between the ages of 80 to 84 years old.
"nages20_24", -- This measure is a count of household members in the household who are between the ages of 20 to 24 years old.
"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.
"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?
"nages70_74", -- This measure is a count of household members in the household who are between the ages of 70 to 74 years old.
"country", -- These data were collected in the Feed the Future Zone of Influence in Uganda.
"f02", -- How often did this happen in the past 4 weeks 30 days ?
"ghht", -- Derived gendered household type
"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 in individual modules was not used for these measures in order to ensure that the information came from the same source.)
"a19_6", -- Native language of respondent SELECT ALL THAT APPLY 6 - Runyoro-Rutoro
"stratum", -- Administrative variable for identifying the sampling strata.
"a19_7", -- Native language of respondent SELECT ALL THAT APPLY 7 - English
"dmiss", -- Is the Module D record missing for the household?
"hhsizecat", -- This measure is 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 more are termed large.
"nages90_94", -- This measure is a count of household members in the household who are between the ages of 90 to 94 years old.
"d02", -- Observed floor material
"a05", -- County - Anonymized
"d10", -- What is the main source of cooking fuel for your household?
"a19_1", -- Native language of respondent SELECT ALL THAT APPLY 1 - Luganda
"urbrur", -- Location type (urban rural)
"outcome", -- Overall result code
"nages45_49", -- This measure is a count of household members in the household who are between the ages of 45 to 49 years old.
"nages65_69", -- This measure is a count of household members in the household who are between the ages of 65 to 69 years old.
"a03", -- Parish LC2 Name - Anonymized
"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. Those households who use liquid or gas fuels or electricity for cooking are coded as 0.
"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.
"a18", -- Language of interview
"nages95_", -- This measure is a count of household members in the household who are 95 years old or older.
"d04", -- How many rooms in this dwelling are used for sleeping?
"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 was not used for these measures in order to ensure that the information came from the same source.)
"nages55_59", -- This measure is a count of household members in the household who are between the ages of 55 to 59 years old.
"a12", -- Total number of children 0-5
"nages75_79", -- This measure is a count of household members in the household who are between the ages of 75 to 79 years old.
"d01", -- Observed roof top material (outer covering)
"cmiss", -- Is the Module C record missing for the household?
"electric", -- This variable is a recode of D09 and indicates, with a 1, that a household has access to electricity.
"f04", -- How often did this happen in the past 4 weeks 30 days ?
"nages85_89", -- This measure is a count of household members in the household who are between the ages of 85 to 89 years old.
"d09", -- Does this household have 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.
"nages5_9", -- This measure is a count of household members in the household who are between the ages of 5 to 9 years old.
"nages0_4", -- This measure is a count of household members in the household who are between the ages of 0 to 4 years old.
"a10", -- Total persons in household
"impwater", -- This variable indicates that the farmer used an improved technology for non-irrigation based water management.
"d08", -- What is the main source of drinking water for your household?
"fmiss", -- Is the Module F record missing for the household?
"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).
"a06", -- District - Anonymized
"a11", -- Total number of women 15-49
"d03", -- Observed exterior walls
"hungerscale", -- Derived household hunger scale, ranging from 0 to 6 where higher values indicate greater hunger and food insecurity.
"region", -- Region
"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.
"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?
"a20", -- Was a translator used?
"hh_wt", -- This is the household weight, merged from the HOUSEHOLD file.
"cluster", -- Cluster number
"newf06", -- This is a recode of F05 and F06. It shows how often household members went all day and night without eating because there was not enough food.
"nages25_29", -- This measure is a count of household members in the household who are between the ages of 25 to 29 years old.
"maxeducat", -- This variable is a recode of the maximum level of household education, maxeducat. It contains four categories: None, Less than primary, Primary, and Secondary or more. It represents the category of the highest level of education of the members in the household.
"nages50_54", -- This measure is a count of household members in the household who are between the ages of 50 to 54 years old.
"a19_2", -- Native language of respondent SELECT ALL THAT APPLY 2 - Luo
"nages30_34", -- This measure is a count of household members in the household who are between the ages of 30 to 34 years old.
"d05", -- What is the main type of toilet your household uses?
"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?
"a17", -- Language of questionnaire
"d06", -- Do you share this toilet with other households?
"a19_3" -- Native language of respondent SELECT ALL THAT APPLY 3 - Runyankole-Rukiga
FROM
"usaid-gov/feed-the-future-uganda-interim-survey-in-the-zone-rthi-gc3q:latest"."feed_the_future_uganda_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-uganda-interim-survey-in-the-zone-rthi-gc3q 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-uganda-interim-survey-in-the-zone-rthi-gc3q: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-uganda-interim-survey-in-the-zone-rthi-gc3qCheckout 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-uganda-interim-survey-in-the-zone-rthi-gc3q:latestThis will download all the objects for the latest tag of usaid-gov/feed-the-future-uganda-interim-survey-in-the-zone-rthi-gc3q 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-uganda-interim-survey-in-the-zone-rthi-gc3q: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-uganda-interim-survey-in-the-zone-rthi-gc3q: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-uganda-interim-survey-in-the-zone-rthi-gc3q is just another Postgres schema.