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_northern_kenya_interim_survey_in table in this repository, by referencing it like:
"usaid-gov/feed-the-future-northern-kenya-interim-survey-in-dmyf-we6q:latest"."feed_the_future_northern_kenya_interim_survey_in"or in a full query, like:
SELECT
":id", -- Socrata column ID
"d10", -- What is the main source of cooking fuel for your household?
"a19_10", -- A19. NATIVE LANGUAGE OF RESPONDENT LANGUAGE 10 - Maasai
"a19_11", -- A19. NATIVE LANGUAGE OF RESPONDENT LANGUAGE 11 - Meru
"a19_4", -- A19. NATIVE LANGUAGE OF RESPONDENT LANGUAGE 4 - Kamba
"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.
"nages65_69", -- This measure is a count of household members in the household who are between the ages of 65 to 69 years old.
"emiss", -- Is the Module E record missing for the household?
"a19_18", -- A19. NATIVE LANGUAGE OF RESPONDENT LANGUAGE 18 - Other
"nages15_19", -- This measure is a count of household members in the household who are between the ages of 15 to 19 years old.
"f04", -- How often did this happen in the past 4 weeks 30 days?
"a17", -- Language of questionnaire
"d03", -- Observed exterior walls
"a04", -- A04. SUB-LOCATION
"d02", -- Observed floor material
"a20", -- Was a translator used?
"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.
"cmiss", -- Is the Module C record missing for the household?
"nages70_74", -- This measure is a count of household members in the household who are between the ages of 70 to 74 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.
"nages45_49", -- This measure is a count of household members in the household who are between the ages of 45 to 49 years old.
"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 were not used for these measures in order to ensure that the information came from the same source.
"a19_13", -- A19. NATIVE LANGUAGE OF RESPONDENT LANGUAGE 13 - Pokot
"a19_8", -- A19. NATIVE LANGUAGE OF RESPONDENT LANGUAGE 8 - Luhya2
"a13", -- Line number (i.e., IDCODE) of respondent to Module C
"a19_3", -- A19. NATIVE LANGUAGE OF RESPONDENT LANGUAGE 3 - Kalenjin
"a19_16", -- A19. NATIVE LANGUAGE OF RESPONDENT LANGUAGE 16 - Turkana
"a11", -- Total number of women 15-49
"a19_17", -- A19. NATIVE LANGUAGE OF RESPONDENT LANGUAGE 17 - English
"a19_14", -- A19. NATIVE LANGUAGE OF RESPONDENT LANGUAGE 14 - Somali
"a19_1", -- A19. NATIVE LANGUAGE OF RESPONDENT LANGUAGE 1 - Borana
"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.
"intensity", -- This variable identifies the intensity of USAID Kenya programming activities within the county. High intensity counties are Marsabit and Isiolo. Medium intensity counties are Garissa, Wajir, and Turkana. Low intensity counties are Tana River, Samburu, and Baringo.
"nages90_94", -- This measure is a count of household members in the household who are between the ages of 90 to 94 years old.
"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?
"a05", -- A05. LOCATION
"nages55_59", -- This measure is a count of household members in the household who are between the ages of 55 to 59 years old.
"zoi", -- This variable indicates that a record is included in the Feed the Future Zone of Influence. The ZOI includes the counties of Marsabit, Garissa, Isiolo, Wajir, and Turkana.
"a19_7", -- A19. NATIVE LANGUAGE OF RESPONDENT LANGUAGE 7 - Luhya1
"nages20_24", -- This measure is a count of household members in the household who are between the ages of 20 to 24 years old.
"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.
"hungerscale", -- Derived household hunger scale, ranging from 0 to 6 where higher values indicate greater hunger and food insecurity.
"a19_2", -- A19. NATIVE LANGUAGE OF RESPONDENT LANGUAGE 2 - Embu
"nages30_34", -- This measure is a count of household members in the household who are between the ages of 30 to 34 years old.
"country", -- These data were collected in the Feed the Future Zone of Influence in Kenya.
"nages5_9", -- This measure is a count of household members in the household who are between the ages of 5 to 9 years old.
"a18", -- Language of interview
"hhsize", -- Derived number of individuals in the household based on the household roster
"nages95_", -- This measure is a count of household members in the household who are 95 years old or older.
"dmiss", -- Is the Module D record missing for the household?
"d04", -- How many rooms in this dwelling are used for sleeping?
"electric", -- This variable is a recode of D09 and indicates, with a 1, that a household has access to electricity.
"nages10_14", -- This measure is a count of household members in the household who are between the ages of 10 to 14 years old.
"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?
"pbs_id", -- Administrative variable for identifying households
"nages75_79", -- This measure is a count of household members in the household who are between the ages of 75 to 79 years old.
"a19_9", -- A19. NATIVE LANGUAGE OF RESPONDENT LANGUAGE 9 - Luo
"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.
"f02", -- How often did this happen in the past 4weeks 30 days
"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.
"a19_5", -- A19. NATIVE LANGUAGE OF RESPONDENT LANGUAGE 5 - Kikuyu
"nages60_64", -- This measure is a count of household members in the household who are between the ages of 60 to 64 years old.
"today", -- Survey Date
"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.
"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).
"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.
"nages25_29", -- This measure is a count of household members in the household who are between the ages of 25 to 29 years old.
"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.
"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 4 weeks 30 days?
"a12", -- Total number of children 0-5
"nages35_39", -- This measure is a count of household members in the household who are between the ages of 35 to 39 years old.
"d07", -- How many households use this toilet?
"a10", -- Total persons in household
"stratum", -- Administrative variable for identifying the strata of sampling designs. Unstratified samples have a constant value of 1.
"a19_15", -- A19. NATIVE LANGUAGE OF RESPONDENT LANGUAGE 15 - Swahili
"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.
"nages0_4", -- This measure is a count of household members in the household who are between the ages of 0 to 4 years old.
"a19_6", -- A19. NATIVE LANGUAGE OF RESPONDENT LANGUAGE 6 - Kisii
"d06", -- Do you share this toilet with other households?
"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.
"survey", -- These data were collected in the 2015 Interim Zone of Influence Survey.
"hhhunger", -- This variable is an indicator that a household experiences moderate or severe HH hunger or not.
"d09", -- Does this household have electricity?
"d08", -- What is the main source of drinking water for your household?
"d01", -- Observed roof top material (outer covering)
"cluster", -- Cluster number
"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.
"nages40_44", -- This measure is a count of household members in the household who are between the ages of 40 to 44 years old.
"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.
"newf06", -- This is a recode of F05 and F06. It shows how often household members went all day without eating.
"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?
"d05", -- What is the main type of toilet your household uses?
"nages50_54", -- This measure is a count of household members in the household who are between the ages of 50 to 54 years old.
"a06", -- A06. COUNTY
"fmiss", -- Is the Module F record missing for the household?
"a19_12", -- A19. NATIVE LANGUAGE OF RESPONDENT LANGUAGE 12 - Mijikenda
"outcome", -- A09. overall result code
"urbrur", -- Location type (urban rural)
"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.
"hh_wt", -- Administrative variable of the design weight, adjusted for household non-response.
"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.
FROM
"usaid-gov/feed-the-future-northern-kenya-interim-survey-in-dmyf-we6q:latest"."feed_the_future_northern_kenya_interim_survey_in"
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-northern-kenya-interim-survey-in-dmyf-we6q 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-northern-kenya-interim-survey-in-dmyf-we6q: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
Install Splitgraph Locally
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-northern-kenya-interim-survey-in-dmyf-we6q" \
--handler-options '{
"domain": "data.usaid.gov",
"tables": {
"feed_the_future_northern_kenya_interim_survey_in": "dmyf-we6q"
}
}'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-northern-kenya-interim-survey-in-dmyf-we6q is just another Postgres schema.