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-hw36-g6pk:latest"."feed_the_future_malawi_interim_survey_in_the_zone"or in a full query, like:
SELECT
":id", -- Socrata column ID
"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.
"d04", -- How many rooms in this dwelling are used for sleeping?
"d10", -- What is the main source of cooking fuel for your household?
"d07", -- How many households use this toilet?
"ghht", -- Derived gendered household type
"a19_5", -- Native language of respondent SELECT ALL THAT APPLY 5 - NGONI
"d09", -- Does this household have electricity?
"littlehunger", -- This dichotomous variable indicates that a household experiences little or no hunger (0 or 1 on the household hunger scale).
"d02", -- Observed floor material
"urbrur", -- Location type (urban rural)
"a19_2", -- Native language of respondent SELECT ALL THAT APPLY 2 - YAO
"nfemaleadults", -- This measure is a count of the women aged 18 and older in the household roster. The AGE and SEX variables in the HHMEMBERS file are used to generate these variables. The age and sex information collected in individual modules were not used for these measu
"newf04", -- This is a recode of F03 and F04. It shows how often household members went to sleep hungry because there was not enough food.
"wallcat", -- This measure is a recoded category of variable D03, roof type. The variable is classified into three categories, Natural, Rudimentary, and Finished, according to the classification used in the questionnaire.
"nages80_84", -- This measure is a count of household members in the household who are between the ages of 80 to 84 years old.
"hhsize", -- Derived number of individuals in the household based on the household roster
"impwater", -- This is a binary indicator of improved drinking water. It is recoded from variable D08 using WHO and UNICEF classifications. Improved water includes piped water, tube well borehole, protected dug well, protected spring, and rainwater collection. Unimpro
"nages60_64", -- This measure is a count of household members in the household who are between the ages of 60 to 64 years old.
"a20", -- Was a translator used?
"nages55_59", -- This measure is a count of household members in the household who are between the ages of 55 to 59 years old.
"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
"hungerscale", -- Derived household hunger scale, ranging from 0 to 6 where higher values indicate greater hunger and food insecurity.
"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.
"strata3", -- Stratification for subgroup 3
"a11", -- Total number of women 15-49
"nages65_69", -- This measure is a count of household members in the household who are between the ages of 65 to 69 years old.
"nages35_39", -- This measure is a count of household members in the household who are between the ages of 35 to 39 years old.
"a05", -- District
"nages10_14", -- This measure is a count of household members in the household who are between the ages of 10 to 14 years old.
"nyouth15_29", -- This measure is a count of youth aged 15 to 29 years (i.e., greater than 14 years and less than 30 years) in the household roster. The AGE and SEX variables in the HHMEMBERS file are used to generate these variables. The age and sex information collected
"severehunger", -- This dichotomous variable indicates that a household experiences severe hunger (4 or more on the household hunger scale).
"a17", -- Language of questionnaire
"newf06", -- This is a recode of F05 and F06. It shows how often household members went all day without eating.
"nages70_74", -- This measure is a count of household members in the household who are between the ages of 70 to 74 years old.
"nages25_29", -- This measure is a count of household members in the household who are between the ages of 25 to 29 years old.
"a06", -- Region
"solidfuel", -- This variable is a recode of variable D10 and identifies the type of primary fuel used for cooking. Solid fuel is coded as a 1 and includes any biomass, such as coal, lignite, charcoal, wood, straw shrubs grass, agricultural crop residue, and animal dung.
"impsanitation", -- This is a binary indicator of access to improved sanitation facilities. The variable is based on questions D05 and D06 following WHO and UNICEF classifications. Improved sanitation is classified as flush or pour toilets that are flushed to piped sewer sys
"country", -- These data were collected in Malawi
"nkids5_17", -- This measure is a count of the children aged 5 to 17 years (i.e., greater than 4 years and less than 18 years) in the household roster. The AGE and SEX variables in the HHMEMBERS file are used to generate these variables. The age and sex information colle
"pbs_id", -- Administrative variable for identifying households
"a19_4", -- Native language of respondent SELECT ALL THAT APPLY 4 - LOMWE
"a19_6", -- Native language of respondent SELECT ALL THAT APPLY 6 - SENA
"nages50_54", -- This measure is a count of household members in the household who are between the ages of 50 to 54 years old.
"a12", -- Total number of children 0-5
"nages30_34", -- This measure is a count of household members in the household who are between the ages of 30 to 34 years old.
"nages90_94", -- This measure is a count of household members in the household who are between the ages of 90 to 94 years old.
"nages85_89", -- This measure is a count of household members in the household who are between the ages of 85 to 89 years old.
"nages95_", -- This measure is a count of household members in the household who are 95 years old or older.
"nages5_9", -- This measure is a count of household members in the household who are between the ages of 5 to 9 years old.
"hhhunger", -- This variable is an indicator that a household experiences moderate or severe HH hunger. The weighted mean of this variable is the Feed the Future Indicator of household hunger.
"a19_1", -- Native language of respondent SELECT ALL THAT APPLY 1 - CHICHEWA
"d03", -- Observed exterior walls
"newf02", -- This is a recode of F01 and F02. It shows how often the household was unable to eat because there was no food and the household lacked the resources to acquire food.
"f02", -- How often did this happen in the past 30 days?
"nmaleadults", -- This measure is a count of the men aged 18 and older in the household roster. The AGE and SEX variables in the HHMEMBERS file are used to generate these variables. The age and sex information collected in individual modules were not used for these measure
"nages20_24", -- This measure is a count of household members in the household who are between the ages of 20 to 24 years old.
"cluster", -- Cluster number
"nkids0_1", -- This measure is a count of the children aged 0 to 1 years (i.e., less than 2 years) in the household roster. The AGE and SEX variables in the HHMEMBERS file are used to generate these variables. The age and sex information collected in individual modules
"d06", -- Do you share this toilet with other households?
"f01", -- In the past 30 days was there ever no food to eat of any kind in your house because of lack of resources to get food?
"nkids0_4", -- This measure is a count of the children aged 0 to 4 years (i.e., less than 5 years) in the household roster. The AGE and SEX variables in the HHMEMBERS file are used to generate these variables. The age and sex information collected in individual modules
"floorcat", -- This measure is a recoded category of variable D02, roof type. The variable is classified into three categories, Natural, Rudimentary, and Finished, according to the classification used in the questionnaire.
"zoi", -- Was the data collected by Westat or ICF?
"nages45_49", -- This measure is a count of household members in the household who are between the ages of 45 to 49 years old.
"nages75_79", -- This measure is a count of household members in the household who are between the ages of 75 to 79 years old.
"hhsizecat", -- This measure if a recode of the HHSIZE variable. Households are classified by the number of individuals living in the household, where those with 1-5 household members are termed small, those with 6-10 members are termed medium, and those with 11 or m
"nages0_4", -- This measure is a count of household members in the household who are between the ages of 0 to 4 years old.
"outcome", -- A09. overall result code
"maxeducat", -- This variable is the maximum level of household education. It contains four categories: None, Less than primary, Primary, and Secondary or more. It represents the category of the highest level of education in the household.
"electric", -- This variable is a recode of D09 and indicates, with a 1, that a household has access to electricity.
"nages40_44", -- This measure is a count of household members in the household who are between the ages of 40 to 44 years old.
"nages15_19", -- This measure is a count of household members in the household who are between the ages of 15 to 19 years old.
"a19_3", -- Native language of respondent SELECT ALL THAT APPLY 3 - TUMBUKA
"d01", -- Observed roof top material (outer covering)
"f05", -- In the past 30 days did you or any household member go a whole day and night without eating anything at all because there was not enough food?
"roofcat", -- This measure is a recoded category of variable D01, roof type. The variable is classified into three categories, natural, rudimentary, and finished, according to the classification used in the questionnaire.
"personsperroom", -- This measure is the number of household members per sleeping room in the household. It is created by dividing HHSIZE by the number of sleeping rooms (D04).
"a19_7", -- Native language of respondent SELECT ALL THAT APPLY 7 - OTHER
"a10", -- Total persons in household
"modhunger", -- This dichotomous variable indicates that a household experiences moderate hunger (2 or 3 on the household hunger scale).
"f03", -- In the past 30 days did you or any household member go to sleep at night hungry because there was not enough food?
"d05", -- What is the main type of toilet your household uses?
"today", -- The day of interview as a string variable, MM-DD-YYYY
"d08", -- What is the main source of drinking water for your household?
"a04", -- TA Town - Anonymous
"f04", -- How often did this happen in the past 30 days?
"a18", -- Language of interview
"s_hh_wt3", -- Household weights, adjusted for non-response, subgroup 3
"f06" -- How often did this happen in the past 30 days?
FROM
"datahub-usaid-gov/feed-the-future-malawi-interim-survey-in-the-zone-hw36-g6pk: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-hw36-g6pk 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-hw36-g6pk: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-hw36-g6pkCheckout 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-hw36-g6pk:latestThis will download all the objects for the latest tag of datahub-usaid-gov/feed-the-future-malawi-interim-survey-in-the-zone-hw36-g6pk 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-hw36-g6pk: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-hw36-g6pk: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-hw36-g6pk is just another Postgres schema.