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-x637-9qn3:latest"."feed_the_future_northern_kenya_interim_survey_in"or in a full query, like:
SELECT
":id", -- Socrata column ID
"povthres_nat", -- The extreme poverty threshold is the poverty line established by the Kenya Integrated Household Budget Survey -2005 06. This value is 1,562 2,913 (rural urban) Kenya shillings per month per adult equivalent. This threshold has been adjusted to reflect a daily per capita threshold. This threshold, when adjusted to daily per capita terms can be different across households.
"pcc", -- Daily per capita consumption in Kenya shillings, 2005 prices
"gap", -- Poverty gap at the poverty threshold of less than 1.25 per person per day in 2005 PPP. Value is normalized to the poverty threshold, such that values are expressed as a proportion of the poverty line, and households with consumption at the threshold or higher are set to missing.
"gap_nat", -- Poverty gap at the national poverty threshold. Value is normalized to the poverty threshold, such that values are expressed as a proportion of the poverty line, and households with consumption at the threshold or higher are set to missing.
"shortfall_nat", -- Consumption shortfall of the poor at the national poverty threshold. Value is normalized to the poverty threshold and households, such that values are expressed as a proportion of the poverty line, with consumption at the threshold or higher are set to missing.
"shortfall_ext", -- Consumption shortfall of the poor at the national extreme (food) poverty threshold. Value is normalized to the poverty threshold and households, such that values are expressed as a proportion of the poverty line, with consumption at the threshold or higher are set to missing.
"e6_dailyrent", -- Derived daily rent for aggregation
"cluster", -- Cluster number
"hh_clothing", -- Total daily household expenditure on clothing, Kenyan shillings in 2005 prices.
"e604b", -- If you rented this dwelling out today, how much rent would you receive? Unit
"hh_transport", -- Total daily household expenditure on transportation, Kenyan shillings in 2005 prices. This includes fees for transportation or costs associated with owning and using one s own transportation vehicle.
"imp_rent", -- Imputation flag for rent
"adultequiv", -- This is a measure of adult equivalents in the household, and the measure is used in the calculation of the national poverty rates. As documented in the Basic Report on Well-Being in Kenya, released by the Kenya National Bureau of Statistics in 2007, the approach used in Kenya was developed by Anzagi and Bernard. This approach weights children 0-4 as at 0.24 and children 5-14 at 0.65. All people aged 15 and above are treated as a full adult and weighted as a 1. This measure is the sum of adult equivalents within the household.
"e111", -- Over the past 7 days, what was the total number of meals that were shared with people who are not members of your household? NUMBER OF MEALS
"e605a", -- How much do you pay to rent this dwelling? Local amount
"hh_rent", -- Total daily rental value for housing, Kenyan shillings in 2005 prices. This variable is the estimated rental value of the dwelling occupied by the household. This measure is calculated from a hedonic regression of rental values reported by renters. Housing rental values were then predicted for non-renters by using the model coefficients and dwelling characteristics of non-renters.
"e605b", -- How much do you pay to rent this dwelling? Unit
"expsample", -- This variable indicates that the observation was used to calculate indicator estimates for the 2015 interim assessment.
"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
"eresp2", -- Module E2-7 Respondent
"e113", -- How much did you spend on pet food last week? ENTER AMOUNT IN KSH
"e602", -- If you sold this dwelling today, how much would you receive for it?
"e603", -- How old is this house, in years?
"e604a", -- If you rented this dwelling out today, how much rent would you receive? Local amount
"pcc_wt", -- This variable is the person weight used to the weight the data to produce population-level poverty estimates.
"hh_food", -- Total daily household consumption of food items (excluding alcohol), Kenyan shillings in 2005 prices.
"hh_food_r", -- Total daily household consumption of food items (excluding alcohol), Kenyan shillings in 2005 prices with a spatial price adjustment using a Paashe index. The adjusted value in this variable is included in interim assessment analyses.
"hh_house", -- Total daily household consumption on household maintenance and upkeep, Kenyan shillings in 2005 prices. This includes water and electric utilities, as well as he costs associated with purchasing or obtaining access to energy (such as electricity, gas, or firewood) and water. This also includes the reported household s reported costs associated with household reports as reported in Module E6.
"hh_furnish", -- Total daily household consumption on household furnishings, Kenyan shillings in 2005 prices. These are durable or semipermanent objects that are used to supply a home.
"hh_recreate", -- Total daily household expenditure on recreational and leisure, Kenyan shillings in 2005 prices. This includes durable goods used for entertainment, discretionary items used for enjoyment, and leisure travel.
"hh_comm", -- Total daily household expenditure on recreational and leisure, Kenyan shillings in 2005 prices. This includes cell phones and costs related to the use of a cell phone, land line phone services, mail or courier fees.
"hh_health", -- Total daily household expenditure on health care, Kenyan shillings in 2005 prices. This include expenses incurred to obtain medical treatment for illnesses or preventative health care. Health care may be provided for formal medical providers or traditional practitioners. The major health care expenses and hospitalizations that are reported in Module E5 are not included in this measure because these introduce considerable bias into poverty estimates.
"hh_edu", -- Total daily household expenditure on health care, Kenyan shillings in 2005 prices. Educational expenses include costs such as school uniforms, school supplies, tuition, or other fees.
"e609", -- In the past month, how much did you spend on repairs maintenance to this house?
"hh_alcohol", -- Total daily household expenditure on alcohol and cigarettes, Kenyan shillings in 2005 prices.
"e608", -- How much do you pay each time you make a payment on your mortgage?
"e109", -- Over the past 7 days, how many people who are not members of your household ate meals in your household? NUMBER OF PEOPLE
"e601", -- Do you own or are purchasing this household, is it provided to you by an employer, do you use it for free, or do you rent this house?
"emiss", -- Is the Module E record missing for the household?
"povthres_125", -- The poverty threshold of 1.25 2005 PPP per person per day in Kenya shillings, 2005 prices
"urbrur", -- Location type (urban rural)
"pov_ext", -- Poverty status at the national extreme (food) poverty threshold.
"e112", -- Over the past 7 days, did your household purchase pet food for family pets like a cat or a dog?
"today", -- Survey Date
"eresp1", -- Module E1 Respondent
"povthres_ext", -- The extreme poverty threshold is the food poverty line established by the Kenya Integrated Household Budget Survey -2005 06. This value is 988 1,474 (rural urban) Kenya shillings per month per adult equivalent. This threshold has been adjusted to reflect a daily per capita threshold. This threshold, when adjusted to daily per capita terms can be different across households.
"hh_total", -- Derived daily total HH expenditures in Kenya shillings, 2005 prices. This total does not include the household s financial transfers (e.g., donations, taxes, loans) or large ceremonial expenses. These types of expenses are conventionally excluded because they insert bias into poverty estimates.
"e607", -- How often do you make mortgage payments?
"e108", -- Over the past 7 days, did any people who are not members of your household eat any meals in your household?
"usdconversion", -- Conversion factor transforming Kenyan shillings in 2005 prices to 2010 USD, adjusting for 2005 PPP.
"survey", -- These data were collected in the 2015 Interim Zone of Influence Survey.
"pov", -- Poverty status at the poverty threshold of less than 1.25 per person per day in 2005 PPP
"e606", -- Do you pay a mortgage on this house, that is, a regular payment towards purchasing the house?
"e114", -- Over the past 7 days, were there any other expenditures on pets?
"pov_nat", -- Poverty status at the national poverty threshold.
"e110", -- Over the past 7 days, what was the total number of days in which any meal was shared with people who are not members of your household? NUMBER OF DAYS
"pcc_usd", -- Daily per capita consumption in 2010 US dollars, adjusted for 2005 PPP
"country", -- These data were collected in the Feed the Future Zone of Influence in Kenya.
"e6_dailyrepair", -- Derived daily house repair for aggregation
"hh_wt", -- Administrative variable of the design weight, adjusted for household non-response.
"shortfall", -- Consumption shortfall of the poor at the poverty threshold of less than 1.25 per person per day in 2005 PPP. Value is normalized to the poverty threshold and households, such that values are expressed as a proportion of the poverty line, with consumption at the threshold or higher are set to missing.
"e115", -- How much did you spend on other purchases for pets last week? ENTER AMOUNT IN KSH
"hh_misc", -- Total daily household expenditure on miscellaneous household goods, Kenyan shillings in 2005 prices. These goods include items such as household cleaners, personal care and bath items, or wages paid to servants.
"gap_ext", -- Poverty gap at the national extreme (food) poverty threshold. Value is normalized to the poverty threshold, such that values are expressed as a proportion of the poverty line, and households with consumption at the threshold or higher are set to missing.
"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.
FROM
"usaid-gov/feed-the-future-northern-kenya-interim-survey-in-x637-9qn3: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-x637-9qn3 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-northern-kenya-interim-survey-in-x637-9qn3: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-northern-kenya-interim-survey-in-x637-9qn3Checkout 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-northern-kenya-interim-survey-in-x637-9qn3:latestThis will download all the objects for the latest tag of usaid-gov/feed-the-future-northern-kenya-interim-survey-in-x637-9qn3 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-northern-kenya-interim-survey-in-x637-9qn3: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-northern-kenya-interim-survey-in-x637-9qn3: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-northern-kenya-interim-survey-in-x637-9qn3 is just another Postgres schema.