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-judh-8udi:latest"."feed_the_future_malawi_interim_survey_in_the_zone"or in a full query, like:
SELECT
":id", -- Socrata column ID
"mwicpi15", -- Malawi 2015 CPI. Average monthly CPI for July to October, months of data collection. 2005 100
"e608", -- How much do you pay each time you make a payment on your mortgage?
"e602", -- If you sold this dwelling today, how much would you receive for it?
"e605a", -- How much do you pay to rent this dwelling? Local amount
"country", -- These data were collected in Malawi
"povthres_125", -- The poverty threshold of 1.25 2005 PPP per person per day in constant 2015 Kwacha
"pcc", -- Derived daily per capita consumption in 2015 Kwacha
"e604a", -- If you rented this dwelling out today, how much rent would you receive? Local amount
"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?
"e607", -- How often do you make mortgage payments?
"pov_nat", -- Poverty status at the national poverty threshold of 350.51 2015 Kwacha.
"hhexp_cat03", -- Clothing Footwear, real daily consumption
"strata3", -- Stratification for subgroup 3
"e112", -- Over the past 7 days, did your household purchase pet food for family pets like a cat or a dog?
"mwicpi05", -- Malawi 2005 CPI. 2005 100
"pov_ext", -- Poverty status at the national extreme poverty threshold of 217.45 2015 Kwacha.
"hhexp_cat06", -- Health, real daily consumption
"hhexp_cat07", -- Transport, real daily consumption
"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.
"e604b", -- If you rented this dwelling out today, how much rent would you receive? Unit
"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 h
"hhexp_total", -- Derived daily total HH expenditures in 2015 Kwacha
"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
"e609", -- In the past month, how much did you spent on repairs maintenance to this house?
"cluster", -- Cluster number
"pov", -- Poverty status at the poverty threshold of less than 1.25 per person per day in 2005 PPP
"hhexp_cat12", -- Misc Goods Services, real daily consumption
"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?
"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.
"shortfall_ext", -- Consumption shortfall of the poor at the national extreme 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
"ghht", -- Derived gendered household type
"e605b", -- How much do you pay to rent this dwelling? Unit
"hhexp_cat08", -- Communication, real daily consumption
"ppp", -- 2005 PPP
"zoi", -- Was the data collected by Westat or ICF?
"povthres_ext", -- The extreme poverty threshold of 1.25 2005 PPP per person per day in constant 2015 Kwacha
"e114", -- Over the past 7 days, were there any other expenditures on pets?
"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 m
"today", -- The day of interview as a string variable, MM-DD-YYYY
"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?
"gap_ext", -- Poverty gap at the national extreme 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.
"hhexp_cat09", -- Recreation, real daily consumption
"hhexp_cat11", -- Hotels and restaurants, real daily consumption
"usdconversion", -- Conversion factor transforming 2015 Kwacha into 2010 USD, adjusting for 2005 PPP.
"mwicpi11", -- Malawi 2011 CPI. 2005 100
"hhexp_cat05", -- Furnishings, real daily consumption
"pcc_usd", -- Derived daily per capita consumption in 2010 US dollars, adjusted for 2005 PPP
"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.
"e603", -- How old is this house, in years?
"s_hh_wt3", -- Household weights, adjusted for non-response, subgroup 3
"e606", -- Do you pay a mortgage on this house, that is, a regular payment towards purchasing the house?
"shortfall_190", -- Consumption shortfall of the poor at the poverty threshold of less than 1.90 per person per day in 2011 PPP. Value is normalized to the poverty threshold and households, such that values are expressed as a proportion of the poverty line, with consumptio
"ppp2011", -- 2011 PPP
"pov_190", -- Poverty status at the poverty threshold of less than 1.90 per person per day in 2011 PPP
"hhexp_cat02", -- Alc Tobacco, real daily consumption
"e109", -- Over the past 7 days, how many people who are not members of your household ate meals in your household?
"hhexp_cat10", -- Education, real daily consumption
"povthres_nat", -- The national poverty threshold of 1.25 2005 PPP per person per day in constant 2015 Kwacha
"gap_190", -- Poverty gap at the poverty threshold of less than 1.90 per person per day in 2011 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 h
"price_index", -- Spatial price index
"e108", -- Over the past 7 days, did any people who are not members of your household eat any meals in your household?
"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 consumptio
"e113", -- How much did you spend on pet food last week?
"hhexp_cat01", -- Food Bev, real daily consumption
"pbs_id", -- Administrative variable for identifying households
"uscpi10", -- US 2010 CPI. 2005 100
"hhexp_cat04", -- Housing Utilities, real daily consumption
"povthres_190", -- The poverty threshold of 1.90 2011 PPP per person per day in constant 2015 Kwacha
"pcc_wt3", -- Per capita consumption and poverty weight, subgroup 3
"uscpi05", -- US 2005 CPI. 2005 100
"mwicpi13", -- Malawi 2013 CPI. 2005 100
"urbrur", -- Location type (urban rural)
"e115" -- How much did you spend on their purchases for pets last week?
FROM
"usaid-gov/feed-the-future-malawi-interim-survey-in-the-zone-judh-8udi: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-judh-8udi 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-malawi-interim-survey-in-the-zone-judh-8udi: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-malawi-interim-survey-in-the-zone-judh-8udiCheckout 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-malawi-interim-survey-in-the-zone-judh-8udi:latestThis will download all the objects for the latest tag of usaid-gov/feed-the-future-malawi-interim-survey-in-the-zone-judh-8udi 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-malawi-interim-survey-in-the-zone-judh-8udi: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-malawi-interim-survey-in-the-zone-judh-8udi: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-malawi-interim-survey-in-the-zone-judh-8udi is just another Postgres schema.