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_baseline_household_survey table in this repository, by referencing it like:
"datahub-usaid-gov/feed-the-future-malawi-baseline-household-survey-5thr-j4sz:latest"."feed_the_future_malawi_baseline_household_survey"or in a full query, like:
SELECT
":id", -- Socrata column ID
"c04_l", -- g3 03l Decide to sell Cell Phone
"credjanydec_any", -- Inadequacy in access to and decisions on credit
"rp", -- Average empowerment gap for women without parity
"c06_h", -- g3 05h Decide to rent out Nonfarm Equip
"d08", -- ZID Fuel src
"c01a_k", -- g3 1a k Anyone Owns Small Durable
"g04_b", -- g5 04b Input to buy avoid think poorly
"a04", -- Primary Secondary
"c01a_i", -- g3 1a i Anyone Owns House
"c01a_h", -- g3 1a h Anyone Owns Nonfarm Equip
"e07_g", -- g4 05g Active member of any civic or charitable group
"he", -- of women empowered
"modf_missing", -- Missing all elements from F
"c04_g", -- g3 03g Decide to sell Farm Equip mech
"d05", -- ZID Toilet tp
"urbrur", -- Urban Rural 1 urban 2 rural
"c02_c", -- g3 02c Owns most of Small Livestock
"f02", -- ZIF No food freq
"g02_b", -- g5 02b Input to buy personal Decision
"b03_5", -- g2 03e Input for spend money from Wage and salary employment
"b03_2", -- g2 03b Input for spend money from Cash crop farming
"f03", -- ZIF Sleep hungry yn
"c05_j", -- g3 04j Decide to give away Large Durable
"hp", -- of women with gender parity
"c02_i", -- g3 02i Owns most of House
"ch_20p", -- Individual indicator that woman has not achieved empowerment
"b02_1", -- g2 02a Input for Food crop farming
"f01", -- ZIF No food yn
"c02_h", -- g3 02h Owns most of Nonfarm Equip
"ci", -- Individual disempowerment score
"b01_6", -- g2 01f Participate in Fishing or fishpond culture
"e07_a", -- g4 05a Active member of any agricultural livestock fisheries producer group
"d03", -- ZID Ext wall tp
"c01a_g", -- g3 1a g Anyone Owns Farm Equip mech
"e07_h", -- g4 05h Active member of any local government
"c09_k", -- g3 06k Decide to buy new Small Durable
"c01b_i", -- g3 1b i Household owns House
"groupmember_any", -- Inadequacy in group member
"c12_a", -- g3 09a Decide how to use money from NGO
"jown_count", -- Inadequacy in ownership of assets
"c06_j", -- g3 05j Decide to rent out Large Durable
"b02_4", -- g2 02d Input for Non farm
"c04_n", -- g3 03n Decide to sell Transportation
"c04_m", -- g3 03m Decide to sell Other Land
"c02_n", -- g3 02n Owns most of Transportation
"c11_b", -- g3 08b Decide to borrow from Informal lender
"zia_region_cd", -- ZIA Region cd
"c01a_m", -- g3 1a m Anyone Owns Other Land
"g404_j", -- g4 04j Is there a other womens group
"f06", -- ZIF Whl day no eat freq
"g05_e", -- g5 05e Livestock raising reflect own value
"leisuretime", -- Inadequacy in leisure
"c04_j", -- g3 03j Decide to sell Large Durable
"c11_c", -- g3 08c Decide to borrow from Formal lender
"f05", -- ZIF Whl day no eat yn
"g404_g", -- g4 04g Is there a civic or charitable group
"g05_c", -- g5 05c Crops to grow reflect own value
"modd_missing", -- Missing all elements from D
"d02", -- ZID Floor tp
"b01_3", -- g2 01c Participate in Livestock raising
"b01_4", -- g2 01d Participate in Non farm
"b02_6", -- g2 02f Input for Fishing or fishpond culture
"b03_6", -- g2 03f Input for spend money from Fishing or fishpond culture
"b03_1", -- g2 03a Input for spend money from Food crop farming
"c01a_a", -- g3 1a a Anyone Owns Agriculture
"c01a_j", -- g3 1a j Anyone Owns Large Durable
"c01a_l", -- g3 1a l Anyone Owns Cell Phone
"c01a_n", -- g3 1a n Anyone Owns Transportation
"c01b_b", -- g3 1b b Household owns Large Livestock
"c01b_n", -- g3 1b n Household owns Transportation
"c02_a", -- g3 02a Owns most of Agriculture
"c02_e", -- g3 02e Owns most of Fish Pond
"c02_j", -- g3 02j Owns most of Large Durable
"c04_a", -- g3 03a Decide to sell Agriculture
"c04_c", -- g3 03c Decide to sell Small Livestock
"c04_d", -- g3 03d Decide to sell Chicken
"c04_f", -- g3 03f Decide to sell Farm Equip non mech
"c04_i", -- g3 03i Decide to sell House
"c04_k", -- g3 03k Decide to sell Small Durable
"c05_b", -- g3 04b Decide to give away Large Livestock
"c05_f", -- g3 04f Decide to give away Farm Equip non mech
"c05_g", -- g3 04g Decide to give away Farm Equip mech
"c05_h", -- g3 04h Decide to give away Nonfarm Equip
"c05_l", -- g3 04l Decide to give away Cell Phone
"c05_m", -- g3 04m Decide to give away Other Land
"c05_n", -- g3 04n Decide to give away Transportation
"c06_b", -- g3 05b Decide to rent out Large Livestock
"c06_c", -- g3 05c Decide to rent out Small Livestock
"c06_i", -- g3 05i Decide to rent out House
"c09_a", -- g3 06a Decide to buy new Agriculture
"c09_d", -- g3 06d Decide to buy new Chicken
"c09_e", -- g3 06e Decide to buy new Fish Pond
"c09_f", -- g3 06f Decide to buy new Farm Equip non mech
"c09_h", -- g3 06h Decide to buy new Nonfarm Equip
"c09_i", -- g3 06i Decide to buy new House
"c09_j", -- g3 06j Decide to buy new Large Durable
"c09_l", -- g3 06l Decide to buy new Cell Phone
"c09_m", -- g3 06m Decide to buy new Other Land
"c09_n", -- g3 06n Decide to buy new Transportation
"c10_b", -- g3 07b Taken any loans from Informal lender
"c10_c", -- g3 07c Taken any loans from Formal lender
"c10_e", -- g3 07e Taken any loans from Group
"c11_a", -- g3 08a Decide to borrow from NGO
"c11_d", -- g3 08d Decide to borrow from Friends
"c11_e", -- g3 08e Decide to borrow from Group
"c12_c", -- g3 09c Decide how to use money from Formal lender
"c12_e", -- g3 09e Decide how to use money from Group
"a05", -- ZIG1 Sex g1
"e02_a", -- g4 01 Speak in public to help decide
"e02_c", -- g4 03 Speak in public to protest
"g404_a", -- g4 04a Is there a agricultural livestock fisheries producer group
"g404_d", -- g4 04d Is there a credit or microfinance group
"g404_c", -- g4 04c Is there a forest users group
"g404_h", -- g4 04h Is there a local government
"g404_e", -- g4 04e Is there a mutual felp or insurance group
"g404_k", -- g4 04k Is there a other
"g404_f", -- g4 04f Is there a trade and business association
"e07_d", -- g4 05d Active member of any credit or microfinance group
"e07_c", -- g4 05c Active member of any forest users group
"e07_e", -- g4 05e Active member of any mutual felp or insurance group
"e07_k", -- g4 05k Active member of any other
"g01_e", -- g5 01e Livestock raising whose Decision
"g01_h", -- g5 01h Major HH Exp whose Decision NOT in STATA
"g01_i", -- g5 01i Minor HH Exp whose Decision is g01 h in STATA
"g02_e", -- g5 02e Livestock raising personal Decision
"g02_d", -- g5 02d Market personal Decision
"g02_h", -- g5 02h Major HH Exp personal Decision NOT in STATA
"g02_i", -- g5 02i Minor HH Exp personal Decision is g02 h in STATA
"g04_c", -- g5 04c Crops to grow avoid think poorly
"zia_district_cd", -- ZIA District cd
"country", -- ZIA Country
"f04", -- ZIF Sleep hungry freq
"incdec_count", -- Inadequacy in control over use of income
"aa", -- Average empowerment score of women not yet empowered
"b01_2", -- g2 01b Participate in Cash crop farming
"b01_5", -- g2 01e Participate in Wage and salary employment
"b02_3", -- g2 02c Input for Livestock raising
"b02_5", -- g2 02e Input for Wage and salary employment
"b03_4", -- g2 03d Input for spend money from Non farm
"c01a_d", -- g3 1a d Anyone Owns Chicken
"c01a_e", -- g3 1a e Anyone Owns Fish Pond
"c01b_a", -- g3 1b a Household owns Agriculture
"c01b_d", -- g3 1b d Household owns Chicken
"c01b_e", -- g3 1b e Household owns Fish Pond
"c01b_f", -- g3 1b f Household owns Farm Equip non mech
"c01b_g", -- g3 1b g Household owns Farm Equip mech
"c01b_j", -- g3 1b j Household owns Large Durable
"c01b_k", -- g3 1b k Household owns Small Durable
"c01b_m", -- g3 1b m Household owns Other Land
"c02_b", -- g3 02b Owns most of Large Livestock
"c02_d", -- g3 02d Owns most of Chicken
"c02_l", -- g3 02l Owns most of Cell Phone
"c04_b", -- g3 03b Decide to sell Large Livestock
"c04_e", -- g3 03e Decide to sell Fish Pond
"c05_a", -- g3 04a Decide to give away Agriculture
"c05_c", -- g3 04c Decide to give away Small Livestock
"c05_d", -- g3 04d Decide to give away Chicken
"c05_e", -- g3 04e Decide to give away Fish Pond
"c06_a", -- g3 05a Decide to rent out Agriculture
"c06_d", -- g3 05d Decide to rent out Chicken
"c06_g", -- g3 05g Decide to rent out Farm Equip mech
"c06_l", -- g3 05l Decide to rent out Cell Phone
"c06_m", -- g3 05m Decide to rent out Other Land
"c09_b", -- g3 06b Decide to buy new Large Livestock
"c09_g", -- g3 06g Decide to buy new Farm Equip mech
"c10_a", -- g3 07a Taken any loans from NGO
"c10_d", -- g3 07d Taken any loans from Friends
"c12_d", -- g3 09d Decide how to use money from Friends
"g404_b", -- g4 04b Is there a water users group
"e07_i", -- g4 05i Active member of any religious group
"e07_b", -- g4 05b Active member of any water users group
"e07_j", -- g4 05j Active member of any other womens group
"g01_b", -- g5 01b Input to buy whose Decision
"g01_d", -- g5 01d Market whose Decision
"g02_c", -- g5 02c Crops to grow personal Decision
"g02_g", -- g5 02g Wage personal Decision
"g03_c", -- g5 03c Crops to grow avoid trouble
"g03_e", -- g5 03e Livestock raising avoid trouble
"g04_e", -- g5 04e Livestock raising avoid think poorly
"g04_d", -- g5 04d Market avoid think poorly
"g05_d", -- g5 05d Market reflect own value
"zia_cluster_cd", -- ZIA Cluster cd
"zia_int_outcome", -- ZIA Int outcome
"wt_weai_2", -- WEAI weight for Females
"d01", -- ZID Roof tp
"d04", -- ZID Rooms num
"d06", -- ZID Drink water src
"d07", -- ZID Electr yn
"a06", -- Household type derived by module C data
"pbs_id", -- PBS ID
"e07_f", -- g4 05f Active member of any trade and business association
"feelinputdecagr", -- Inadequacy in input in productive decisions
"g03_b", -- g5 03b Input to buy avoid trouble
"speakpublic_any", -- Inadequacy in speaking in public
"ea_20p", -- Population value of 5DE
"c09_c", -- g3 06c Decide to buy new Small Livestock
"c05_i", -- g3 04i Decide to give away House
"ci_gap", -- Individual empowerment gap for women without parity
"c06_n", -- g3 05n Decide to rent out Transportation
"g03_d", -- g5 03d Market avoid trouble
"a12", -- ZIG1 Int alone
"ci_above", -- Individual indicator that woman lacks gender parity
"c06_k", -- g3 05k Decide to rent out Small Durable
"c01b_c", -- g3 1b c Household owns Small Livestock
"b03_3", -- g2 03c Input for spend money from Livestock raising
"g01_g", -- g5 01g Wage whose Decision
"c02_m", -- g3 02m Owns most of Other Land
"c01a_c", -- g3 1a c Anyone Owns Small Livestock
"b02_2", -- g2 02b Input for Cash crop farming
"c06_f", -- g3 05f Decide to rent out Farm Equip non mech
"weaiwght", -- Gender specific WEAI weight
"raiprod_any", -- Inadequacy in autonomy in production
"c02_k", -- g3 02k Owns most of Small Durable
"c02_f", -- g3 02f Owns most of Farm Equip non mech
"f04b", -- g6 02 How satisfied scale of 1 10
"c06_e", -- g3 05e Decide to rent out Fish Pond
"wt_weai_1", -- WEAI weight for Males
"c02_g", -- g3 02g Owns most of Farm Equip mech
"c05_k", -- g3 04k Decide to give away Small Durable
"c01b_l", -- g3 1b l Household owns Cell Phone
"g404_i", -- g4 04i Is there a religious group
"c01b_h", -- g3 1b h Household owns Nonfarm Equip
"c01a_b", -- g3 1a b Anyone Owns Large Livestock
"g05_b", -- g5 05b Input to buy reflect own value
"g01_c", -- g5 01c Crops to grow whose Decision
"e02_b", -- g4 02 Speak in public to ensure proper wages
"b01_1", -- g2 01a Participate in Food crop farming
"gpi", -- Population value of GPI
"c01a_f", -- g3 1a f Anyone Owns Farm Equip non mech
"c12_b", -- g3 09b Decide how to use money from Informal lender
"jrightanyagr", -- Inadequacy in purchase sale or transfer of assets
"c04_h", -- g3 03h Decide to sell Nonfarm Equip
"npoor_z105" -- Inadequacy in workload
FROM
"datahub-usaid-gov/feed-the-future-malawi-baseline-household-survey-5thr-j4sz:latest"."feed_the_future_malawi_baseline_household_survey"
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-baseline-household-survey-5thr-j4sz 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-baseline-household-survey-5thr-j4sz: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-baseline-household-survey-5thr-j4szCheckout 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-baseline-household-survey-5thr-j4sz:latestThis will download all the objects for the latest tag of datahub-usaid-gov/feed-the-future-malawi-baseline-household-survey-5thr-j4sz 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-baseline-household-survey-5thr-j4sz: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-baseline-household-survey-5thr-j4sz: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-baseline-household-survey-5thr-j4sz is just another Postgres schema.