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_rwanda_interim_survey_in_the_zone table in this repository, by referencing it like:
"usaid-gov/feed-the-future-rwanda-interim-survey-in-the-zone-f7f2-6g7z:latest"."feed_the_future_rwanda_interim_survey_in_the_zone"or in a full query, like:
SELECT
":id", -- Socrata column ID
"d04", -- How many rooms in this dwelling are used for sleeping?
"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.
"a13", -- Line number (i.e., IDCODE) of respondent to Module C
"a20", -- Was a translator used?
"nages85_89", -- This measure is a count of household members in the household who are between the ages of 85 to 89 years old.
"d06", -- Do you share this toilet with other households?
"a19_2", -- Native language of respondent SELECT ALL THAT APPLY 2 - French
"ghht", -- Derived gendered household type
"a10", -- Total persons in household
"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 were 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.
"newf06", -- This is a recode of F05 and F06. It shows how often household members went all day without eating.
"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.
"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 measures in order to ensure that the information came from the same source.
"littlehunger", -- This dichotomous variable indicates that a household experiences little or no hunger (0 or 1 on the household hunger scale).
"nages40_44", -- This measure is a count of household members in the household who are between the ages of 40 to 44 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 systems, septic tanks, pit latrines, or unknown places. It also included 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 not counted as improved.
"a19_1", -- Native language of respondent SELECT ALL THAT APPLY 1 - Kinyarwanda
"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 were not used for these measures in order to ensure that the information came from the same source.
"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.
"today", -- Survey Date
"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 were not used for these measures in order to ensure that the information came from the same source.
"nages80_84", -- This measure is a count of household members in the household who are between the ages of 80 to 84 years old.
"urbrur", -- Location type (urban rural)
"modhunger", -- This dichotomous variable indicates that a household experiences moderate hunger (2 or 3 on the household hunger scale).
"country", -- These data were collected in the Feed the Future Zone of Influence in Rwanda.
"f06", -- How often did this happen in the past 4 weeks 30 days?
"hhsize", -- Derived number of individuals in the household based on the household roster
"a05", -- District
"dmiss", -- Is the Module D record missing for the household?
"nages20_24", -- This measure is a count of household members in the household who are between the ages of 20 to 24 years old.
"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).
"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.
"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?
"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.
"a19_4", -- Native language of respondent SELECT ALL THAT APPLY 4 - Other (specify)
"d07", -- How many households use this toilet?
"survey", -- These data were collected in the 2015 Interim Zone of Influence Survey.
"nages90_94", -- This measure is a count of household members in the household who are between the ages of 90 to 94 years old.
"nages60_64", -- This measure is a count of household members in the household who are between the ages of 60 to 64 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.
"severehunger", -- This dichotomous variable indicates that a household experiences severe hunger (4 or more on the household hunger scale).
"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?
"d09", -- Does this household have electricity?
"nages75_79", -- This measure is a count of household members in the household who are between the ages of 75 to 79 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.
"nages65_69", -- This measure is a count of household members in the household who are between the ages of 65 to 69 years old.
"outcome", -- A09. overall result code
"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 more are termed large.
"hh_wt", -- Administrative variable of the design weight, adjusted for household non-response.
"cluster", -- Cluster number
"a03", -- Village
"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?
"a18", -- Language of interview
"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. Unimproved sources include unprotected dug well, unprotected spring, cart with small tank, tanker trucks, bottled water, and surface water.
"hungerscale", -- Derived household hunger scale, ranging from 0 to 6 where higher values indicate greater hunger and food insecurity.
"a11", -- Total number of women 15-49
"f04", -- How often did this happen in the past 4 weeks 30 days?
"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 a 0.
"nages45_49", -- This measure is a count of household members in the household who are between the ages of 45 to 49 years old.
"d05", -- What is the main type of toilet your household uses?
"nages5_9", -- This measure is a count of household members in the household who are between the ages of 5 to 9 years old.
"maxeducat", -- This variable is a recode of the maximum level of household education, maxedu. 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.
"a04", -- Sector
"nages10_14", -- This measure is a count of household members in the household who are between the ages of 10 to 14 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.
"a12", -- Total number of children 0-3
"stratum", -- Administrative variable for identifying the strata of sampling designs. Unstratified samples have a constant value of 1.
"pbs_id", -- Administrative variable for identifying households
"nages15_19", -- This measure is a count of household members in the household who are between the ages of 15 to 19 years old.
"f02", -- How often did this happen in the past 4weeks 30 days
"electric", -- This variable is a recode of D09 and indicates, with a 1, that a household has access to electricity.
"a17", -- Language of questionnaire
"cmiss", -- Is the Module C record missing for the household?
"a19_3", -- Native language of respondent SELECT ALL THAT APPLY 3 - English
"d03", -- Observed exterior walls
"a06", -- Province
"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 measures in order to ensure that the information came from the same source.
"fmiss", -- Is the Module F record missing for the household?
"nages25_29", -- This measure is a count of household members in the household who are between the ages of 25 to 29 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.
"d02", -- Observed floor material
"d10", -- What is the main source of cooking fuel for your household?
"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 were not used for these measures in order to ensure that the information came from the same source.
"d01", -- Observed roof top material (outer covering)
"nages30_34", -- This measure is a count of household members in the household who are between the ages of 30 to 34 years old.
"nages35_39", -- This measure is a count of household members in the household who are between the ages of 35 to 39 years old.
"d08" -- What is the main source of drinking water for your household?
FROM
"usaid-gov/feed-the-future-rwanda-interim-survey-in-the-zone-f7f2-6g7z:latest"."feed_the_future_rwanda_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-rwanda-interim-survey-in-the-zone-f7f2-6g7z 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-rwanda-interim-survey-in-the-zone-f7f2-6g7z: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-rwanda-interim-survey-in-the-zone-f7f2-6g7z" \
--handler-options '{
"domain": "data.usaid.gov",
"tables": {
"feed_the_future_rwanda_interim_survey_in_the_zone": "f7f2-6g7z"
}
}'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-rwanda-interim-survey-in-the-zone-f7f2-6g7z is just another Postgres schema.