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 ethiopia_pastoralist_areas_resilience_improvement table in this repository, by referencing it like:
"usaid-gov/ethiopia-pastoralist-areas-resilience-improvement-qc93-ntm4:latest"."ethiopia_pastoralist_areas_resilience_improvement"or in a full query, like:
SELECT
":id", -- Socrata column ID
"q14016", -- 1401. Receive any info on Current market prices for live animals in area
"q1505g", -- 1505g. Season: Sale of other non-livestock assets rental of land
"q1504b", -- 1504b. Season: Livestock production and sales
"q1701j", -- 1701j. Reduce number of meals eaten in a day?
"q2101", -- 2101. In past 4 weeks, did you worry that your HH would not have enough food?
"q1701d", -- 1701d. Gather wild food, hunt, or harvest immature crops?
"q1610_2", -- 1610. Did the person ever send back money to your family?
"q2105", -- 2105. Past 4 wks, you or HH member have to eat a smaller meal than needed
"q14017", -- 1401. Receive any info on Market prices for animal products...last yr?
"q1304_1", -- 1304. Where is the saving held?
"q1606", -- 1606. Has anyone who was living in your household migrated in past two years?
"q1502", -- 1502. Total number of sources
"q1505h", -- 1505h. Season: Remittances
"q1505l", -- 1505l. Season: Other source 3
"q1812", -- 1812. If your HH had problem and needed help with work...not relatives?
"q1829_2", -- 1829_2. Who provided business development training (NGO)
"q1824", -- 1824. If someone not relative and lives someplace else...give money or food?
"q1013", -- 1013. Other markets where prefer purchase agricultural livestock inputs?
"q2107a", -- 2107a. How often ever no food to eat ... lack of resources to get food?
"q1208a_3", -- 1208a. What is the monthly interest rate on the loan?
"q1803_3", -- 1803_3. What types of support (Housing materials)
"q1815b_6", -- 1815b_6. What types of assistance given (Sadaga)
"q1208b_3", -- 1208b. What is the service fee on the loan?
"q1205_3", -- 1205. Source of the loan
"q1803_1", -- 1803_1. What types of support (Food ration)
"q1101_1", -- 1101_1. Which services (Vaccination)
"com_hh_merge", -- Code to merge community and hh datasets
"q1831_1", -- 1831_1. Who provided early warning training (Government)
"q1208b_2", -- 1208b. What is the service fee on the loan?
"q1829_3", -- 1829_3. Who provided business development training (Private sector)
"q1607_3", -- 1607. Where did the person migrate to?
"q1505e", -- 1505e. Season: Sale of wild bush products
"q1503j", -- 1503j. Rank: Other source 1
"pbs_id", -- Household id
"q1838", -- 1838. Have you or anyone in your household ever received training in how to use..like prices
"q1303_1", -- 1303. ID of household member owning the saving
"q1204_1", -- 1204. ID of household member taking the loan
"q1701e", -- 1701e. Consume seed stock held for next season?
"q2001", -- 2001. Any bread, rice, pasta, injera, biscuits, or other foods made from
"q1701c", -- 1701c. Purchase food on credit?
"q2008", -- 2008. Any foods made from beans (white, brown, horse), peas, lentils
"q1102", -- 1102. Last yr when needed animal services for livestock but unable to get them?
"q1834", -- 1834. Have you anyone in HH received seed starter packets from government NGOs?
"q1839_1", -- 1839. Who did you receive training ... information like prices from? 1
"q1609_1", -- 1609. How long ago did the person migrate?
"q1610_3", -- 1610. Did the person ever send back money to your family?
"q1505a", -- 1505a. Season: Farming crop production and sales
"q1815b_7", -- 1815b_7. What types of assistance given (Other specify)
"q1919", -- 1919. I trust my neighbors to look after my house if I am away.
"q1602", -- 1602. If you have been here for two years or less, where were you before?
"q1305_3", -- 1305. What is the primary purpose of the saving?
"q1101_6", -- 1101_6. Which services (Veterinary store with vaccines)
"q1827_1", -- 1827_1. Who provided skills training (Government)
"q1807", -- 1807. If HH had problem and needed money food... community not relatives?
"ea", -- Unique enumeration area code
"q1204_2", -- 1204. ID of household member taking the loan
"q1504c", -- 1504c. Season: Wage labor (local)
"q1012", -- 1012. Why do you purchase inputs at this location?
"q1505k", -- 1505k. Season: Other source 2
"q1815b_4", -- 1815b_4. What types of assistance given (Loans)
"q1809", -- 1809. Compared to one year ago has your ability to get this type of assistance:
"q1832", -- 1832. Have you anyone in HH received any natural resource management training?
"q1505i", -- 1505i. Season: Gifts inheritance
"q1103_1", -- 1103_1. Why not able to get services (No veterinary center)
"q1810", -- 1810. If someone in your HH fell ill injured...community or from relatives?
"q140110", -- 1401. Receive any info on Business and investment opportunities in the last yr?
"q1803_6", -- 1803_6. What types of support (School for children)
"q1505d", -- 1505d. Season: Salaried work
"q1207_1", -- 1207. Do you have to pay a monthly interest rate or service fee on the loan?
"q1503k", -- 1503k. Rank: Other source 2
"q1302_3", -- 1302. Saving number
"q1101_8", -- 1101_8. Which services (Veterinary store with antibiotics)
"q1103_6", -- 1103_6. Why not able to get services (No transportation)
"q140112", -- 1401. Receive any info on Market prices of food that you buy in the last yr?
"q1701f", -- 1701f. Send household members to eat elsewhere?
"q1504e", -- 1504e. Season: Sale of wild bush products
"q2012", -- 2012. Any other foods, such as condiments, traditional beer, beer, wine
"q1103_5", -- 1103_5. Why not able to get services (Security problems)
"q1827_2", -- 1827_2. Who provided skills training (NGO)
"q2010", -- 2010. Any foods made with oil, margarine, fat, or butter?
"q1302_1", -- 1302. Saving number
"q1802_2", -- 1802_2. Who provided support(NGOs)
"q14025", -- 1402. Main source of information about Livestock disease threats or epidemics?
"q1608_2", -- 1608. Main reason for migration?
"q1205_2", -- 1205. Source of the loan
"q1504h", -- 1504h. Season: Remittances
"q1210_3", -- 1210. Purpose of the loan
"q1604", -- 1604. Where are you moving to?
"q1504f", -- 1504f. Season: Other self-employment own business
"q1204_3", -- 1204. ID of household member taking the loan
"q2005", -- 2005. Any food made from beef, lamb, goat, wild game, chicken, other birds
"q1836", -- 1836. You anyone in HH received adult education (literacy numeracy financial)
"q1504d", -- 1504d. Season: Salaried work
"q1503g", -- 1503g. Rank: Sale of other non-livestock assets rental of land
"q1202", -- 1202. If no, why not?
"q1830", -- 1830. Have you or anyone in your HH ever received any early warning training?
"q1820", -- 1820. Compared to one year ago has your ability to give this type of assistance:
"q1808", -- 1808. If HH had problem and needed money food...living elsewhere not relatives?
"q1822", -- 1822. If relative outside community... able give money or food?
"q2009", -- 2009. Any cheese, yogurt, milk, powder milk, butter milk or other milk products
"q1825", -- 1825. Compared to one year ago has your ability to give this type of assistance:
"q1303_3", -- 1303. ID of household member owning the saving
"q1815b_1", -- 1815b_1. What types of assistance given (Zakat)
"q1503h", -- 1503h. Rank: Remittances
"q1802_3", -- 1802_3. Who provided support(Religious Organization)
"q1802_4", -- 1802_4. Who provided support(Other)
"q2102", -- 2102. Past 4 wks, you or HH member not able to eat the kinds of foods
"q1101_4", -- 1101_4. Which services (Breeding services)
"q1804b_6", -- 1804b_6. What types of assistance received (Sadaga)
"q1806", -- 1806. If HH had problem and needed money food...relatives living elsewhere?
"q1611_1", -- 1611. Did the person ever return?
"q1611_3", -- 1611. Did the person ever return?
"q1804a", -- 1804a. HH received support from relatives, neighbors, friends past 12 months?
"q1835_1", -- 1835_1. Who provided seed packets starter packets(Government)
"q1304_2", -- 1304. Where is the saving held?
"q1910", -- 1910. I feel like what happens ... determined by powerful people
"q140113", -- 1401. Receive any info on Child nutrition and health info in the last yr?
"q1206_3", -- 1206. What was the total value of the loan?
"q1904", -- 1904. Do you agree that one should always follow the advice of the elders?
"q1305_2", -- 1305. What is the primary purpose of the saving?
"q1014", -- 1014. Why do you not purchase at these markets?
"q1206_1", -- 1206. What was the total value of the loan?
"q1203_1", -- 1203. Loan number
"q1605", -- 1605. Why (main reason)?
"q1101_2", -- 1101_2. Which services (Treatment for diseases)
"q2109a", -- 2109a. How often you or HH member go a whole day and night without eating
"q1803_4", -- 1803_4. What types of support (Installed water points)
"q1833_2", -- 1833_2. Who provided natural resource management training (NGO)
"q1811", -- 1811. If your HH had a problem and needed help with work...living elsewhere?
"q1210_1", -- 1210. Purpose of the loan
"q1804b_1", -- 1804b_1. What types of assistance received (Zakat)
"q1828", -- 1828. You anyone in HH ever received any business development training?
"q1503d", -- 1503d. Rank: Salaried work
"q14018", -- 1401. Receive any info on Grazing conditions in nearby areas in the last yr?
"q1829_1", -- 1829_1. Who provided business development training (Government)
"q1815b_5", -- 1815b_5. What types of assistance given (Xoolo goony restocking of poorer relatives)
"q1207_2", -- 1207. Do you have to pay a monthly interest rate or service fee on the loan?
"q1504j", -- 1504j. Season: Other source 1
"q1823", -- 1823. If someone not relative, but lives in this community ...give money food?
"q2006", -- 2006. Any eggs?
"q140211", -- 1402. Main source of information about Opportunities for borrowing money?
"q14026", -- 1402. Main source of information about Current market prices for live animals
"q140213", -- 1402. Main source of information about Child nutrition and health information?
"q14028", -- 1402. Main source of information about Grazing conditions in nearby areas?
"q14027", -- 1402. Main source of info about Market prices for animal products
"q1908", -- 1908. How many times ... attended a church mosque or other religious service?
"q1701a", -- 1701a. Rely on less preferred and less expensive foods?
"q1103_8", -- 1103_8. Why not able to get services (No veterinary store)
"q14024", -- 1402. Main source of information about Methods for animal health husbandry?
"q1837_1", -- 1837_1. Who provided adult education(Government)
"q1839_2", -- 1839. Who did you receive training ... information like prices from? 2
"q1208b_1", -- 1208b. What is the service fee on the loan?
"q14021", -- 1402. Main source of information about Long-term changes in weather patterns?
"q1804b_2", -- 1804b_2. What types of assistance received (Remittances)
"q1907", -- 1907. How many times ... food or drinks, either in home or in public place?
"q1909", -- 1909. In last year, how many times ...more than 2 days outside this Kebele?
"q2102a", -- 2102a. How often you or HH member not able to eat the kinds of foods
"q2101a", -- 2101a. How often did you worry that your household would not have enough food?
"q1203_3", -- 1203. Loan number
"q1831_2", -- 1831_2. Who provided early warning training (NGO)
"q140212", -- 1402. Main source of information about Market prices of the food that you buy?
"q1837_2", -- 1837_2. Who provided adult education (NGO)
"q1903", -- 1903. Are you willing to move somewhere else to improve your life?
"q1103_7", -- 1103_7. Why not able to get services (No road road condition poor)
"q14012", -- 1401. Receive any info on Rainfall weather prospects for coming season
"q1601", -- 1601. How long has your household been living in this location?
"q1833_3", -- 1833_3. Who provided natural resource management training (Private sector)
"q1815b_2", -- 1815b_2. What types of assistance given (Remittances)
"q1103_10", -- 1103_10. Why not able to get services (No money to pay for what I need)
"q1801", -- 1801. Has household received support from the government... last year?
"q1803_7", -- 1803_7. What types of support (Cash transfer)
"q1611_2", -- 1611. Did the person ever return?
"q1701h", -- 1701h. Restrict consumption by adults in order for small children to eat?
"q1101_7", -- 1101_7. Which services (Veterinary store with de-working supplies)
"q1209_3", -- 1209. What amount have you paid back to date
"q2108", -- 2108. Past 4 wks, you or HH member go to sleep at night hungry
"q1701g", -- 1701g. Limit portion size at mealtimes?
"q1912", -- 1912. My life is chiefly controlled by other powerful people.
"q2004", -- 2004. Any food or fruit juices made from fruits such as mango, banana, oranges
"q1819", -- 1819. If not relative and lives someplace else...able to give money or food?
"q2007", -- 2007. Any fresh fish, smoked fish, fish soup sauce or dried fish or shellfish?
"q1302_2", -- 1302. Saving number
"q2106", -- 2106. Past 4 wks, you or other HH member have to eat fewer meals in day
"q14011", -- 1401. Receive any info on Long-term changes in weather patterns in the last yr?
"q1818", -- 1818. If not relative, but lives in community ... able to give money food?
"q2108a", -- 2108a. How often you or HH member go to sleep at night hungry
"q1817", -- 1817. If relative outside of community ... able give money or food?
"q1101_10", -- 1101_10. Which services (Other )
"q1916", -- 1916. My life is determined by my own actions.
"q1503l", -- 1503l. Rank: Other source 3
"q2003", -- 2003. Any food made with vegetables such as onions, cabbage, green leafy
"q1913", -- 1913. Not always wise for me to plan ahead ... matter of good or bad fortune
"q1103_11", -- 1103_11. Why not able to get services (Other)
"q1503i", -- 1503i. Rank: Gifts inheritance
"q2002", -- 2002. Any foods made with potatoes, yams, sweet potatoes, irish potatoes
"q1503a", -- 1503a. Rank: Farming crop production and sales
"q1814", -- 1814. Compared to 1 year ago has number people could ask for help with work
"q2109", -- 2109. Past 4 wks, you or HH member go a whole day and night without eating
"q1303_2", -- 1303. ID of household member owning the saving
"q1815a", -- 1815a. Has HH given assistance to relatives neighbors friends in past 12 months
"q1803_5", -- 1803_5. What types of support (Install latrine)
"q1504l", -- 1504l. Season: Other source 3
"q1206_2", -- 1206. What was the total value of the loan?
"q1504a", -- 1504a. Season: Farming crop production and sales
"q1504i", -- 1504i. Season: Gifts inheritance
"q1821", -- 1821. If a relative in this community had a problem.. able to give money food?
"kebele", -- Kebele
"q1609_2", -- 1609. How long ago did the person migrate?
"q1505f", -- 1505f. Season: Other self-employment own business
"q14022", -- 1402. Main source of info about Rainfall prospects weather prospects
"q1209_1", -- 1209. What amount have you paid back to date
"q1503e", -- 1503e. Rank: Sale of wild bush products
"q1101_5", -- 1101_5. Which services (Commercial feed supply)
"q1915", -- 1915. When I get what I want, It is usually because I worked hard for it.
"q140111", -- 1401. Receive any info on Opportunities for borrowing money in the last yr?
"q1835_2", -- 1835_2. Who provided seed packets starter packets (NGO)
"q1902", -- 1902. Please tell me which one of these two views you most agree with.
"q1504g", -- 1504g. Season: Sale of other non-livestock assets rental of land
"q1010", -- 1010. Do you purchase agricultural and livestock inputs
"q1815b_3", -- 1815b_3. What types of assistance given (Gifts Quaadhan)
"q140210", -- 1402. Main source of information about Business and investment opportunities?
"q1203_2", -- 1203. Loan number
"q2104", -- 2104. Past 4 wks, you or HH member have to eat some foods did not want to eat
"q1701k", -- 1701k. Skip entire days without eating?
"q14029", -- 1402. Main source info about Conflict other restrictions on access to grazing?
"q1205_1", -- 1205. Source of the loan
"q1607_2", -- 1607. Where did the person migrate to?
"q1504k", -- 1504k. Season: Other source 2
"q1305_1", -- 1305. What is the primary purpose of the saving?
"q1503c", -- 1503c. Rank: Wage labor (local)
"q1802_1", -- 1802_1. Who provided support(Government)
"q1914", -- 1914. I can mostly determine what will happen in my life.
"q1609_3", -- 1609. How long ago did the person migrate?
"q2011", -- 2011. Any sugar, sugar cane, or honey?
"q1505j", -- 1505j. Season: Other source 1
"q1803_2", -- 1803_2. What types of support (Food for work Cash for work)
"q1101_3", -- 1101_3. Which services (Animal de-worming)
"q14019", -- 1401. Receive info Conflict other restrictions on access to grazing in last yr?
"q1101_9", -- 1101_9. Which services (Veterinary store with salt licks mineral supplements)
"q1608_1", -- 1608. Main reason for migration?
"q2104a", -- 2104a. How often you or HH member have to eat some foods did not want to eat
"q14013", -- 1401. Receive info on Water available prices in local boreholes, shallow well
"pastor_status", -- DERIVED: Pastoralist Status
"q1208a_1", -- 1208a. What is the monthly interest rate on the loan?
"q2106a", -- 2106a. How often you or other HH member have to eat fewer meals in a day
"q1833_1", -- 1833_1. Who provided natural resource management training (Government)
"q1901", -- 1901. Please tell me which one of these two views you most agree with.
"q1610_1", -- 1610. Did the person ever send back money to your family?
"q1505b", -- 1505b. Season: Livestock production and sales
"q14014", -- 1401. Receive any info on Methods for animal health husbandry in the last yr?
"q1804b_3", -- 1804b_3. What types of assistance received (Gifts Quaadhan)
"q1804b_7", -- 1804b_7. What types of assistance received (Other specify)
"q2103a", -- 2103a. How often you or HH member have limited variety of foods
"q1103_3", -- 1103_3. Why not able to get services (Veterinary center was far away)
"q1804b_4", -- 1804b_4. What types of assistance received (Loans)
"q1503b", -- 1503b. Rank: Livestock production and sales
"q1503f", -- 1503f. Rank: Other self-employment own business
"q1304_3", -- 1304. Where is the saving held?
"q1804b_5", -- 1804b_5. What types of assistance received (Xoolo goony restocking of poorer relatives)
"q1208a_2", -- 1208a. What is the monthly interest rate on the loan?
"q1911", -- 1911. My experience in my life has been that what is going to happen will happen
"q1209_2", -- 1209. What amount have you paid back to date
"q1201", -- 1201. Have any household members taken out a loan in the last year?
"q1103_2", -- 1103_2. Why not able to get services (No staff in center)
"q1103_4", -- 1103_4. Why not able to get services (Veterinary center was destroyed burnt)
"q1607_1", -- 1607. Where did the person migrate to?
"q1608_3", -- 1608. Main reason for migration?
"q1701i", -- 1701i. Feed working members of HH at the expense of non-working members?
"q1918", -- 1918. Most People can be trusted.
"q1831_3", -- 1831_3. Who provided early warning training (Private sector)
"q1803_8", -- 1803_8. What types of support (Other specify)
"q1827_3", -- 1827_3. Who provided skills training (Private sector)
"q1816", -- 1816. If a relative in community had a problem ... able to give money food?
"q14023", -- 1402. Main source of info about Water available and prices in...
"q1701b", -- 1701b. Borrow food, or rely on help from a friend or relative?
"q1906", -- 1906. Past week, have you engaged in econ activities w members of other clans?
"q1905", -- 1905. Do you communicate regularly with at least one person outside the village?
"q1011", -- 1011. Where do you normally purchase your main agricultural and livestock inputs
"q2107", -- 2107. Past 4 wks, ever no food to eat...lack of resources to get food?
"q1917", -- 1917. Most people are basically honest.
"q1505c", -- 1505c. Season: Wage labor (local)
"q1805", -- 1805. If HH had problem and needed money food...relatives living in community?
"q1603", -- 1603. Do you have current plans to move location of your household?
"q14015", -- 1401. Receive any info on Livestock disease threats or epidemics in last yr?
"q1826", -- 1826. Have you or anyone in HH received any vocational (job) or skill training?
"q1813", -- 1813. If your HH had problem and needed help with work..not relatives?
"q2103", -- 2103. Past 4 wks, you or HH member have limited variety of foods
"q1301", -- 1301. Do any of your household members have cash savings?
"q1103_9", -- 1103_9. Why unable to get services (Veterinary store did not have supplies)
"q1207_3", -- 1207. Do you have to pay a monthly interest rate or service fee on the loan?
"q2105a", -- 2105a. How often you or HH member have to eat a smaller meal than needed
"q1210_2" -- 1210. Purpose of the loan
FROM
"usaid-gov/ethiopia-pastoralist-areas-resilience-improvement-qc93-ntm4:latest"."ethiopia_pastoralist_areas_resilience_improvement"
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/ethiopia-pastoralist-areas-resilience-improvement-qc93-ntm4 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/ethiopia-pastoralist-areas-resilience-improvement-qc93-ntm4: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/ethiopia-pastoralist-areas-resilience-improvement-qc93-ntm4" \
--handler-options '{
"domain": "data.usaid.gov",
"tables": {
"ethiopia_pastoralist_areas_resilience_improvement": "qc93-ntm4"
}
}'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/ethiopia-pastoralist-areas-resilience-improvement-qc93-ntm4 is just another Postgres schema.