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