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