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-6a5g-yvqy:latest"."feed_the_future_northern_kenya_interim_survey_in"or in a full query, like:
SELECT
":id", -- Socrata column ID
"g301a_h", -- Does anyone in your household currently have any ITEM ? ITEM Nonfarm business equipment (solar panels used for recharging, sewing machine, brewing equipment, fryers)
"g301b_m", -- How many of ITEM does your household currently have? ITEM Other land not used for agricultural purposes (pieces plots, residential or commercial land)
"g301a_d", -- Does anyone in your household currently have any ITEM ? ITEM Chickens, ducks, turkeys, pigeons, or other birds
"g110", -- Have you ever been married or lived together with a man as if married?
"g404_f", -- Is there a GROUP in your community? GROUP Trade and business association
"g404_g", -- Is there a GROUP in your community? GROUP Civic groups (improving community) or charitable group (helping others)
"g602", -- Next, I am going to ask you a question about how satisfied you are with the time you have to yourself to do things you enjoy. Please give your opinion on a scale of 1 to 10. 1 means you are not satisfied and 10 means you are very satisfied. If you are neither satisfied nor dissatisfied, this would be in the middle, or 5, on the scale. How satisfied are you with your available time for leisure activities like visiting neighbors, watching TV, listening to the ratio, seeing movies or doing sports?
"g404_k", -- Is there a GROUP in your community? GROUP Education group
"g306_b", -- Who contributes most to decisions regarding a new purchase of ITEM ? ITEM Large livestock (cattle, camels, donkeys)
"g301b_c", -- How many of ITEM does your household currently have? ITEM Small livestock (goats, pigs, sheep)
"g303_c", -- Who would you say can decide whether to sell ITEM most of the time? ITEM Small livestock (goats, pigs, sheep)
"g305_c", -- Who would you say can decide to mortgage or rent out ITEM most of the time? ITEM Small livestock (goats, pigs, sheep)
"g302_d", -- Who would you say owns most of the ITEM ? ITEM Chickens, ducks, turkeys, pigeons, or other birds
"g303_d", -- Who would you say can decide whether to sell ITEM most of the time? ITEM Chickens, ducks, turkeys, pigeons, or other birds
"g305_d", -- Who would you say can decide to mortgage or rent out ITEM most of the time? ITEM Chickens, ducks, turkeys, pigeons, or other birds
"g306_d", -- Who contributes most to decisions regarding a new purchase of ITEM ? ITEM Chickens, ducks, turkeys, pigeons, or other birds
"g303_f", -- Who would you say can decide whether to sell ITEM most of the time? ITEM Farm equipment (non-mechanized: hand tools, ox plough)
"g305_f", -- Who would you say can decide to mortgage or rent out ITEM most of the time? ITEM Farm equipment (non-mechanized: hand tools, ox plough)
"g302_h", -- Who would you say owns most of the ITEM ? ITEM Nonfarm business equipment (solar panels used for recharging, sewing machine, brewing equipment, fryers)
"g501_c", -- When decisions are made regarding ACTIVITY , who is it that normally takes the decision? ACTIVITY Taking crops to the market (or not)
"g501_e", -- When decisions are made regarding ACTIVITY , who is it that normally takes the decision? ACTIVITY Your own (singular) wage or salary employment
"g502_g", -- To what extent do you feel you can make your own personal decisions regarding these aspects of household life if you want(ed) to? ACTIVITY Minor household expenditures (such as food for daily consumption or other household needs)
"today", -- Survey Date
"g304_b", -- Who would you say can decide whether to give away ITEM most of the time? ITEM Large livestock (cattle, camels, donkeys)
"country", -- These data were collected in the Feed the Future Zone of Influence in Kenya.
"g308_e", -- Who made the decision to borrow from SOURCE ? SOURCE Group based micro-finance or lending including Village Community Banking (VICOBA) or merry-go-rounds
"g301b_k", -- How many of ITEM does your household currently have? ITEM Small consumer durables (radio, cookware)
"g502_a", -- To what extent do you feel you can make your own personal decisions regarding these aspects of household life if you want(ed) to? ACTIVITY Getting inputs for agricultural production
"g405_f", -- Are you an active member of this GROUP? GROUP Trade and business association
"g502_b", -- To what extent do you feel you can make your own personal decisions regarding these aspects of household life if you want(ed) to? ACTIVITY The types of crops to grow
"g301a_n", -- Does anyone in your household currently have any ITEM ? ITEM Means of transportation (bicycle, motorcycle, car)
"g108", -- interviewer determined: Is the respondent 18 years old or older?
"g302_l", -- Who would you say owns most of the ITEM ? ITEM Cell phone
"g304_g", -- Who would you say can decide whether to give away ITEM most of the time? ITEM Farm equipment (mechanized: tractor-drawn plough, power tiller, treadle pump)
"g405_m", -- Are you an active member of this GROUP? GROUP Any other group or organization, specified
"g307_e", -- Has anyone in your household taken any loans or borrowed cash in-kind from SOURCE in the past 12 months? SOURCE Group based micro-finance or lending including Village Community Banking (VICOBA) or merry-go-rounds
"g404_b", -- Is there a GROUP in your community? GROUP Water users group
"g306_e", -- Who contributes most to decisions regarding a new purchase of ITEM ? ITEM Fish pond or fishing equipment
"g501_g", -- When decisions are made regarding ACTIVITY , who is it that normally takes the decision? ACTIVITY Minor household expenditures (such as food for daily consumption or other household needs)
"g201_c", -- Did you yourself participate in ACTIVITY in the past 12 months (that is, during the past one or two cropping seasons)? ACTIVITY Livestock raising
"g301a_l", -- Does anyone in your household currently have any ITEM ? ITEM Cell phone
"idcode", -- Woman s ID code
"g306_f", -- Who contributes most to decisions regarding a new purchase of ITEM ? ITEM Farm equipment (non-mechanized: hand tools, ox plough)
"g402", -- Do you feel comfortable speaking up in public to ensure proper payment of wages for public works or other similar programs?
"g404_c", -- Is there a GROUP in your community? GROUP Forest users group
"g301b_i", -- How many of ITEM does your household currently have? ITEM House or other structures
"g203_b", -- How much input did you have in decisions on the use of income generated from ACTIVITY ? ACTIVITY Cash crop farming: These are crops that are grown primarily for sale in market
"g309_c", -- Who makes the decision about what to do with the money item borrowed from SOURCE ? SOURCE Formal lender (bank financial institution, government)
"g201_d", -- Did you yourself participate in ACTIVITY in the past 12 months (that is, during the past one or two cropping seasons)? ACTIVITY Non-farm economic activities: This would include things like running a small business, self-employment, buy-and-sell
"g401", -- Do you feel comfortable speaking up in public to help decide on infrastructure (like small wells, roads, water supplies) to be built in your community?
"g301b_l", -- How many of ITEM does your household currently have? ITEM Cell phone
"g404_m", -- Is there a GROUP in your community? GROUP Any other group or organization, specified
"g405_g", -- Are you an active member of this GROUP? GROUP Civic groups (improving community) or charitable group (helping others)
"g306_g", -- Who contributes most to decisions regarding a new purchase of ITEM ? ITEM Farm equipment (mechanized: tractor-drawn plough, power tiller, treadle pump)
"g109", -- Are you currently married or living together with a man as if married?
"g303_e", -- Who would you say can decide whether to sell ITEM most of the time? ITEM Fish pond or fishing equipment
"g301a_g", -- Does anyone in your household currently have any ITEM ? ITEM Farm equipment (mechanized: tractor-drawn plough, power tiller, treadle pump)
"g306_c", -- Who contributes most to decisions regarding a new purchase of ITEM ? ITEM Small livestock (goats, pigs, sheep)
"g301b_a", -- How many of ITEM does your household currently have? ITEM Agricultural land (pieces plots)
"g203_d", -- How much input did you have in decisions on the use of income generated from ACTIVITY ? ACTIVITY Non-farm economic activities: This would include things like running a small business, self-employment, buy-and-sell
"g301a_b", -- Does anyone in your household currently have any ITEM ? ITEM Large livestock (cattle, camels, donkeys)
"g301a_k", -- Does anyone in your household currently have any ITEM ? ITEM Small consumer durables (radio, cookware)
"g301a_m", -- Does anyone in your household currently have any ITEM ? ITEM Other land not used for agricultural purposes (pieces plots, residential or commercial land)
"g405_e", -- Are you an active member of this GROUP? GROUP Mutual help or insurance group (including burial associations)
"stratum", -- Administrative variable for identifying the strata of sampling designs. Unstratified samples have a constant value of 1.
"weai_wt", -- Individual sampling weight for primary female decisionmakers
"g301b_f", -- How many of ITEM does your household currently have? ITEM Farm equipment (non-mechanized: hand tools, ox plough)
"urbrur", -- Location type (urban rural)
"survey", -- These data were collected in the 2015 Interim Zone of Influence Survey.
"g301a_i", -- Does anyone in your household currently have any ITEM ? ITEM House or other structures
"g405_l", -- Are you an active member of this GROUP? GROUP Other women s group (only if it does not fit into one of the other categories)
"g404_e", -- Is there a GROUP in your community? GROUP Mutual help or insurance group (including burial associations)
"g405_i", -- Are you an active member of this GROUP? GROUP Religious group
"g301b_d", -- How many of ITEM does your household currently have? ITEM Chickens, ducks, turkeys, pigeons, or other birds
"g307_c", -- Has anyone in your household taken any loans or borrowed cash in-kind from SOURCE in the past 12 months? SOURCE Formal lender (bank financial institution, government)
"g104", -- Ability to be interviewed alone
"g308_a", -- Who made the decision to borrow from SOURCE ? SOURCE Non-governmental organization (NGO)
"g501_b", -- When decisions are made regarding ACTIVITY , who is it that normally takes the decision? ACTIVITY The types of crops to grow
"g405_h", -- Are you an active member of this GROUP? GROUP Local government
"g405_d", -- Are you an active member of this GROUP? GROUP Credit or microfinance group (including Village Community Banking (VICOBA) or merry-go-rounds)
"g302_e", -- Who would you say owns most of the ITEM ? ITEM Fish pond or fishing equipment
"g405_j", -- Are you an active member of this GROUP? GROUP Youth group
"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.
"g301b_j", -- How many of ITEM does your household currently have? ITEM Large consumer durables (refrigerator, TV, sofa)
"g404_d", -- Is there a GROUP in your community? GROUP Credit or microfinance group (including Village Community Banking (VICOBA) or merry-go-rounds)
"g404_i", -- Is there a GROUP in your community? GROUP Religious group
"g405_c", -- Are you an active member of this GROUP? GROUP Forest users group
"g111", -- What is your marital status now: are you widowed, divorced or separated?
"g309_e", -- Who makes the decision about what to do with the money item borrowed from SOURCE ? SOURCE Group based micro-finance or lending including Village Community Banking (VICOBA) or merry-go-rounds
"g201_a", -- Did you yourself participate in ACTIVITY in the past 12 months (that is, during the past one or two cropping seasons)? ACTIVITY Food crop farming: These are crops that are grown primarily for household food consumption
"g601b", -- In the past 24 hours, did you work, either at home or outside the home, more than usual, about the same amount as usual, or less than usual?
"g404_l", -- Is there a GROUP in your community? GROUP Other women s group (only if it does not fit into one of the other categories)
"g301a_e", -- Does anyone in your household currently have any ITEM ? ITEM Fish pond or fishing equipment
"g302_f", -- Who would you say owns most of the ITEM ? ITEM Farm equipment (non-mechanized: hand tools, ox plough)
"g502_c", -- To what extent do you feel you can make your own personal decisions regarding these aspects of household life if you want(ed) to? ACTIVITY Taking crops to the market (or not)
"g305_a", -- Who would you say can decide to mortgage or rent out ITEM most of the time? ITEM Agricultural land (pieces plots)
"g201_f", -- Did you yourself participate in ACTIVITY in the past 12 months (that is, during the past one or two cropping seasons)? ACTIVITY Fishing or fishpond culture
"g202_b", -- How much input did you have in making decisions about ACTIVITY ? ACTIVITY Cash crop farming: These are crops that are grown primarily for sale in market
"g304_f", -- Who would you say can decide whether to give away ITEM most of the time? ITEM Farm equipment (non-mechanized: hand tools, ox plough)
"g106", -- Please tell me how old you are. What was your age at your last birthday?
"g107", -- Are you 18 years old or older?
"g301b_h", -- How many of ITEM does your household currently have? ITEM Nonfarm business equipment (solar panels used for recharging, sewing machine, brewing equipment, fryers)
"g304_e", -- Who would you say can decide whether to give away ITEM most of the time? ITEM Fish pond or fishing equipment
"g307_b", -- Has anyone in your household taken any loans or borrowed cash in-kind from SOURCE in the past 12 months? SOURCE Informal lender, loan shark
"g404_h", -- Is there a GROUP in your community? GROUP Local government
"g202_a", -- How much input did you have in making decisions about ACTIVITY ? ACTIVITY Food crop farming: These are crops that are grown primarily for household food consumption
"g202_d", -- How much input did you have in making decisions about ACTIVITY ? ACTIVITY Non-farm economic activities: This would include things like running a small business, self-employment, buy-and-sell
"g305_e", -- Who would you say can decide to mortgage or rent out ITEM most of the time? ITEM Fish pond or fishing equipment
"g304_d", -- Who would you say can decide whether to give away ITEM most of the time? ITEM Chickens, ducks, turkeys, pigeons, or other birds
"g105_y", -- In what month and year were you born? Year
"g203_a", -- How much input did you have in decisions on the use of income generated from ACTIVITY ? ACTIVITY Food crop farming: These are crops that are grown primarily for household food consumption
"g202_f", -- How much input did you have in making decisions about ACTIVITY ? ACTIVITY Fishing or fishpond culture
"g202_e", -- How much input did you have in making decisions about ACTIVITY ? ACTIVITY Wage and salary employment: This could be work that is paid for in cash or in-kind, including both agriculture and other wage work. In-kind means that a payment or exchange is made without the use of money. For example, a person can work to earn food instead of working to earn money
"g304_a", -- Who would you say can decide whether to give away ITEM most of the time? ITEM Agricultural land (pieces plots)
"g306_a", -- Who contributes most to decisions regarding a new purchase of ITEM ? ITEM Agricultural land (pieces plots)
"g305_b", -- Who would you say can decide to mortgage or rent out ITEM most of the time? ITEM Large livestock (cattle, camels, donkeys)
"g303_a", -- Who would you say can decide whether to sell ITEM most of the time? ITEM Agricultural land (pieces plots)
"g203_e", -- How much input did you have in decisions on the use of income generated from ACTIVITY ? ACTIVITY Wage and salary employment: This could be work that is paid for in cash or in-kind, including both agriculture and other wage work. In-kind means that a payment or exchange is made without the use of money. For example, a person can work to earn food instead of working to earn money
"g303_g", -- Who would you say can decide whether to sell ITEM most of the time? ITEM Farm equipment (mechanized: tractor-drawn plough, power tiller, treadle pump)
"g301a_f", -- Does anyone in your household currently have any ITEM ? ITEM Farm equipment (non-mechanized: hand tools, ox plough)
"g501_a", -- When decisions are made regarding ACTIVITY , who is it that normally takes the decision? ACTIVITY Getting inputs for agricultural production
"g201_e", -- Did you yourself participate in ACTIVITY in the past 12 months (that is, during the past one or two cropping seasons)? ACTIVITY Wage and salary employment: This could be work that is paid for in cash or in-kind, including both agriculture and other wage work. In-kind means that a payment or exchange is made without the use of money. For example, a person can work to earn food instead of working to earn money
"g202_c", -- How much input did you have in making decisions about ACTIVITY ? ACTIVITY Livestock raising
"g302_k", -- Who would you say owns most of the ITEM ? ITEM Small consumer durables (radio, cookware)
"g501_d", -- When decisions are made regarding ACTIVITY , who is it that normally takes the decision? ACTIVITY Livestock raising
"g301b_b", -- How many of ITEM does your household currently have? ITEM Large livestock (cattle, camels, donkeys)
"g304_c", -- Who would you say can decide whether to give away ITEM most of the time? ITEM Small livestock (goats, pigs, sheep)
"g302_j", -- Who would you say owns most of the ITEM ? ITEM Large consumer durables (refrigerator, TV, sofa)
"g301a_j", -- Does anyone in your household currently have any ITEM ? ITEM Large consumer durables (refrigerator, TV, sofa)
"g502_f", -- To what extent do you feel you can make your own personal decisions regarding these aspects of household life if you want(ed) to? ACTIVITY Major household expenditures (such as a large appliance for the house like a refrigerator)
"g309_b", -- Who makes the decision about what to do with the money item borrowed from SOURCE ? SOURCE Informal lender, loan shark
"g405_k", -- Are you an active member of this GROUP? GROUP Education group
"g302_a", -- Who would you say owns most of the ITEM ? ITEM Agricultural land (pieces plots)
"g302_m", -- Who would you say owns most of the ITEM ? ITEM Other land not used for agricultural purposes (pieces plots, residential or commercial land)
"g405_a", -- Are you an active member of this GROUP? GROUP Agricultural livestock fisheries producer s group (including marketing groups)
"g301b_n", -- How many of ITEM does your household currently have? ITEM Means of transportation (bicycle, motorcycle, car)
"g308_b", -- Who made the decision to borrow from SOURCE ? SOURCE Informal lender, loan shark
"g307_d", -- Has anyone in your household taken any loans or borrowed cash in-kind from SOURCE in the past 12 months? SOURCE Friends or relatives
"g302_g", -- Who would you say owns most of the ITEM ? ITEM Farm equipment (mechanized: tractor-drawn plough, power tiller, treadle pump)
"g307_a", -- Has anyone in your household taken any loans or borrowed cash in-kind from SOURCE in the past 12 months? SOURCE Non-governmental organization (NGO)
"g302_b", -- Who would you say owns most of the ITEM ? ITEM Large livestock (cattle, camels, donkeys)
"g405_b", -- Are you an active member of this GROUP? GROUP Water users group
"g301a_a", -- Does anyone in your household currently have any ITEM ? ITEM Agricultural land (pieces plots)
"pbs_id", -- Administrative variable for identifying households
"g404_j", -- Is there a GROUP in your community? GROUP Youth group
"g201_b", -- Did you yourself participate in ACTIVITY in the past 12 months (that is, during the past one or two cropping seasons)? ACTIVITY Cash crop farming: These are crops that are grown primarily for sale in market
"g404_a", -- Is there a GROUP in your community? GROUP Agricultural livestock fisheries producer s group (including marketing groups)
"g308_d", -- Who made the decision to borrow from SOURCE ? SOURCE Friends or relatives
"g403", -- Do you feel comfortable speaking up in public to protest the misbehavior of authorities or elected officials?
"g305_g", -- Who would you say can decide to mortgage or rent out ITEM most of the time? ITEM Farm equipment (mechanized: tractor-drawn plough, power tiller, treadle pump)
"g303_b", -- Who would you say can decide whether to sell ITEM most of the time? ITEM Large livestock (cattle, camels, donkeys)
"g302_i", -- Who would you say owns most of the ITEM ? ITEM House or other structures
"g302_n", -- Who would you say owns most of the ITEM ? ITEM Means of transportation (bicycle, motorcycle, car)
"g309_d", -- Who makes the decision about what to do with the money item borrowed from SOURCE ? SOURCE Friends or relatives
"g302_c", -- Who would you say owns most of the ITEM ? ITEM Small livestock (goats, pigs, sheep)
"g301b_g", -- How many of ITEM does your household currently have? ITEM Farm equipment (mechanized: tractor-drawn plough, power tiller, treadle pump)
"g501_f", -- When decisions are made regarding ACTIVITY , who is it that normally takes the decision? ACTIVITY Major household expenditures (such as a large appliance for the house like a refrigerator)
"g301a_c", -- Does anyone in your household currently have any ITEM ? ITEM Small livestock (goats, pigs, sheep)
"g203_c", -- How much input did you have in decisions on the use of income generated from ACTIVITY ? ACTIVITY Livestock raising
"g105_m", -- In what month and year were you born? Month
"g203_f", -- How much input did you have in decisions on the use of income generated from ACTIVITY ? ACTIVITY Fishing or fishpond culture
"g309_a", -- Who makes the decision about what to do with the money item borrowed from SOURCE ? SOURCE Non-governmental organization (NGO)
"g502_d", -- To what extent do you feel you can make your own personal decisions regarding these aspects of household life if you want(ed) to? ACTIVITY Livestock raising
"cluster", -- Cluster number
"g502_e", -- To what extent do you feel you can make your own personal decisions regarding these aspects of household life if you want(ed) to? ACTIVITY Your own (singular) wage or salary employment
"g301b_e", -- How many of ITEM does your household currently have? ITEM Fish pond or fishing equipment
"g308_c" -- Who made the decision to borrow from SOURCE ? SOURCE Formal lender (bank financial institution, government)
FROM
"usaid-gov/feed-the-future-northern-kenya-interim-survey-in-6a5g-yvqy: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-6a5g-yvqy 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-6a5g-yvqy: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-6a5g-yvqy" \
--handler-options '{
"domain": "data.usaid.gov",
"tables": {
"feed_the_future_northern_kenya_interim_survey_in": "6a5g-yvqy"
}
}'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-6a5g-yvqy is just another Postgres schema.