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 grandmothers_and_the_gender_gap_in_the_mexican table in this repository, by referencing it like:
"mydata-iadb/grandmothers-and-the-gender-gap-in-the-mexican-h3ek-xh3t:latest"."grandmothers_and_the_gender_gap_in_the_mexican"or in a full query, like:
SELECT
":id", -- Socrata column ID
"z_pago_pu", -- Public daycare cost (std)
"z_pago_pr", -- Private daycare cost (std)
"z_pago_hora_pu", -- Public daycare cost per hour (std)
"z_pago_hora_pr", -- Private daycare cost per hour (std)
"z_pago_hora_es", -- Public School cost per hour (std)
"z_pago_hora_con_es", -- Private School cost per hour (std)
"z_pago_es", -- Public School cost (std)
"z_pago_con_es", -- Private School cost per hour (std)
"yq_c", -- ID Year + Quarter
"year", -- Year
"upm", -- Primary sampling unit
"time_cuidar_gm_p1", -- Hours grandmother provided care
"time_cuidar_gm", -- Hours grandmother provided care
"time_cuidar", -- Hours provided care
"t_loc", -- Locality size
"t_ind_max", -- Household size
"t_gk_u6_p1", -- Total number of grandchildren < 6 years old (in N_ENT = 1)
"t_gk_p1", -- Total number of grandchildren (in N_ENT = 1)
"t_gk_max", -- Total number of grandchildren by HH_ID
"t_gk", -- Total number of grandchildren
"t_gd_max", -- Total number of grandfathers by HH_ID
"pobtot_20_mun", -- Total population municipality level
"pobtot_20_loc", -- Total population locality level
"per", -- Survey period
"p0a5_20_loc", -- Population <6 years old (locality level)
"n_ren", -- Row number
"n_pro_viv", -- Progressive number of housing units
"n_hij_p1", -- Number of kids (in N_ENT = 1)
"n_624412_p0a5_20", -- Public daycares per child (at most 5 yrs old)
"n_624411_p0a5_20", -- Private daycares per child (at most 5 yrs old)
"mun_full", -- Ent + mun
"mun", -- Municipality
"miss_sex", -- Check for changes in sex
"middle_time", -- Middle time worker (<= 30 hrs)
"merge_est", -- Matching result from merge
"max_eda_gm", -- Max age grandmother
"max_eda_gk", -- Max age grandchildren
"max_eda_gd", -- Max age grandfather
"max_eda", -- Max age by ID
"loc_group", -- ent + mun + loc
"loc", -- Locality
"ingocup_p1", -- Monthly income (in N_ENT = 1)
"ingocup", -- Monthly income
"ing_x_hrs", -- Average income per hour worked
"inc_f_cat10", -- Deciles of income
"id", -- Individual ID
"hrsocup_p1", -- Hours worked per week (in N_ENT = 1)
"hrsocup", -- Hours worked per week
"hh_id", -- Household ID
"gm_died_a3_mom", -- After grandmother dies - period 3 X mother
"gm_died_a2", -- After grandmother dies - period 2
"gm_died", -- Grandmother died
"gd_died_b3_o_le_5", -- Before grandfather dies - period 3 X oldest gk at most 5yrs
"fac", -- Expansion factor
"ent", -- State
"ei_n_p0a5_20", -- Daycares affiliated with the Estancias Infantiles
"eda_cat5", -- Age - 5 years interval categories
"eda", -- Age
"d_gk_m_p1", -- 1 + male grandchildren (in N_ENT = 1)
"as_time_cuidar", -- Hours provided care (asymptotic)
"anios_esc", -- Years of schooling
"cuida", -- Hours provided care >0
"gm_died_ob1_mom", -- Before grandmother dies - period 1 X mom
"gm_died_a3_o_le_5", -- After grandmother dies - period 3 X oldest gk at most 5yrs
"gm_died_b4_o_le_5", -- Before grandmother dies - period 4 X oldest gk at most 5yrs
"gd_died_a4", -- After grandfather dies - period 4
"gd_died_b2", -- Before grandfather dies - period 2
"gm_died_a3", -- After grandmother dies - period 3
"gm_died_a1", -- After grandmother dies - period 1
"post_gm_died", -- Post X Grandmother died
"generations", -- Number of generations in the household (ENOE)
"obs_hh", -- Check for number of observations in the 5 surveys
"f_emp_p1", -- Formal employment (in N_ENT = 1)
"any_work", -- Individual working paid or unpaid
"t_gk_m_p1", -- Total number of male grandchildren (in N_ENT = 1)
"t_dad_max", -- Total number of fathers by HH_ID
"t_mom_max", -- Total number of mothers by HH_ID
"gm_le70", -- Grandmother at most 70 years old
"man", -- Man
"woman", -- Women
"gk", -- Grandchild
"mom", -- Mother
"n_hij", -- Number of children
"n_ent", -- Number of interview
"post_gm_died_o_le_5_mom", -- Post X gm death X Oldest grandchild at most 5yrs X mom
"post_gm_died_y_le_5", -- Post X gm death X Youngest grandchild at most 5yrs
"post_gm_died_o_le_5", -- Post X gm death X Oldest grandchild at most 5yrs
"gm_died_a4_mom", -- After grandmother dies - period 4 X mother
"gm_died_a1_mom", -- After grandmother dies - period 1 X mother
"gm_died_b4_mom", -- Before grandmother dies - period 4 X mother
"gm_died_a4_o_le_5", -- After grandmother dies - period 4 X oldest gk at most 5yrs
"gm_died_a2_o_le_5", -- After grandmother dies - period 2 X oldest gk at most 5yrs
"gm_died_a1_o_le_5", -- After grandmother dies - period 1 X oldest gk at most 5yrs
"gm_died_b3_o_le_5", -- Before grandmother dies - period 3 X oldest gk at most 5yrs
"gm_died_a3_o_le_5_mom", -- After grandmother dies - period 3 X oldest gk at most 5yrs X mom
"gm_died_ob1_o_le_5_mom", -- Before grandmother dies - period 1 X oldest gk at most 5yrs X mom
"gm_died_b2_o_le_5_mom", -- Before grandmother dies - period 2 X oldest gk at most 5yrs X mom
"gm_died_b3_o_le_5_mom", -- Before grandmother dies - period 3 X oldest gk at most 5yrs X mom
"gd_died_ob1", -- Before grandfather dies - period 1
"gm_died_ob1", -- Before grandmother dies - period 1
"gd_died", -- Grandfather died
"merge_eness_format", -- Matching result from merge
"merge_mun", -- Matching result from merge
"edu_max", -- Max educational level by ID
"full_time", -- Full time worker (> 30 hrs)
"part_time_t2_p1", -- Part time worker (<= 30 hrs) (in N_ENT = 1)
"employed", -- Employed
"t_sec_gen_max", -- Total number of second generations
"t_gd_died", -- Number of deaths of grandfathers
"y_le_5", -- Youngest grandchild at most 5 years old
"max_eda_gk_3", -- Age of grandchildren (4 brackets)
"sex", -- Sex
"year_period", -- Year + Quarter
"gm_died_b2", -- Before grandmother dies - period 2
"gm_m_side", -- Grandmother on the mother's side
"part_time", -- Part time worker (<= 20 hrs)
"gd_died_b3", -- Before grandfather dies - period 3
"sec_gen", -- Second generation
"gd_died_b2_o_le_5", -- Before grandfather dies - period 2 X oldest gk at most 5yrs
"t_gm_max", -- Total number of grandmothers by HH_ID
"emp_ppal", -- Job classification (formal/informal)
"n_hog", -- Household number
"gm_died_ob1_o_le_5", -- Before grandmother dies - period 1 X oldest gk at most 5yrs
"merge_loc", -- Matching result from merge
"any_paidwork", -- Individidual working
"merge_denue", -- Expansion factor eness
"gm_died_b2_o_le_5", -- Before grandmother dies - period 2 X oldest gk at most 5yrs
"gd_died_b4_o_le_5", -- Before grandfather dies - period 4 X oldest gk at most 5yrs
"gm_le60", -- Grandmother at most 60 years old
"gd_died_a1_o_le_5", -- After grandfather dies - period 1 X oldest gk at most 5yrs
"employed_p1", -- Employed (in N_ENT = 1)
"gd_died_a3_o_le_5", -- After grandfather dies - period 3 X oldest gk at most 5yrs
"part_time_t2", -- Part time worker (<= 30 hrs)
"coll_more_p1", -- College or more (in N_ENT = 1)
"gm_died_a4_o_le_5_mom", -- After grandmother dies - period 4 X oldest gk at most 5yrs X mom
"gk_m_u6", -- Grandchild <= 5 years old and male
"post_gd_died", -- Post X Grandfather died
"gm_died_b3", -- Before grandmother dies - period 3
"gd_died_a4_o_le_5", -- After grandfather dies - period 4 X oldest gk at most 5yrs
"o_le_5", -- Oldest grandchild at most 5 years old
"gm_died_a2_mom", -- After grandmother dies - period 2 X mother
"gm_died_b2_mom", -- Before grandmother dies - period 2 X mother
"gm_died_a1_o_le_5_mom", -- After grandmother dies - period 1 X oldest gk at most 5yrs X mom
"high_more_p1", -- Highschool or more (in N_ENT = 1)
"post_gm_died_mom", -- Post X grandmother death
"gd_died_a1", -- After grandfather dies - period 1
"full_time_p1", -- Full time worker (> 30 hrs) (in N_ENT = 1)
"gm_died_b4_o_le_5_mom", -- Before grandmother dies - period 4 X oldest gk at most 5yrs X mom
"post_gm_died_y_le_5_mom", -- Post X gm death X Youngest grandchild at most 5yrs X mom
"gd_died_a3", -- After grandfather dies - period 3
"dad", -- Father
"gm_died_a2_o_le_5_mom", -- After grandmother dies - period 2 X oldest gk at most 5yrs X mom
"gm_died_b4", -- Before grandmother dies - period 4
"gd", -- Grandfather
"t_gm_died", -- Number of deaths of grandmothers
"gd_died_a2", -- After grandfather dies - period 2
"gd_died_a2_o_le_5", -- After grandfather dies - period 2 X oldest gk at most 5yrs
"gd_died_b4", -- Before grandfather dies - period 4
"gm", -- Grandmother
"gm_died_a4", -- After grandmother dies - period 4
"gm_died_b3_mom" -- Before grandmother dies - period 3 X mother
FROM
"mydata-iadb/grandmothers-and-the-gender-gap-in-the-mexican-h3ek-xh3t:latest"."grandmothers_and_the_gender_gap_in_the_mexican"
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 mydata-iadb/grandmothers-and-the-gender-gap-in-the-mexican-h3ek-xh3t 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 mydata-iadb/grandmothers-and-the-gender-gap-in-the-mexican-h3ek-xh3t: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 mydata-iadb/grandmothers-and-the-gender-gap-in-the-mexican-h3ek-xh3tCheckout 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 mydata-iadb/grandmothers-and-the-gender-gap-in-the-mexican-h3ek-xh3t:latestThis will download all the objects for the latest tag of mydata-iadb/grandmothers-and-the-gender-gap-in-the-mexican-h3ek-xh3t 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 mydata-iadb/grandmothers-and-the-gender-gap-in-the-mexican-h3ek-xh3t: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 mydata-iadb/grandmothers-and-the-gender-gap-in-the-mexican-h3ek-xh3t: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, mydata-iadb/grandmothers-and-the-gender-gap-in-the-mexican-h3ek-xh3t is just another Postgres schema.