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 2010_us_census_mail_return_rates_and_demographics table in this repository, by referencing it like:
"datacatalog-cookcountyil-gov/2010-us-census-mail-return-rates-and-demographics-mpyu-4jqk:latest"."2010_us_census_mail_return_rates_and_demographics"or in a full query, like:
SELECT
":id", -- Socrata column ID
"pct_spanprim", -- Calculated percent population of individuals in which Spanish is the primary language ( Population of individuals in which Spanish is the primary language divided by Total Population of individuals over age 5 years, multiplied by 100).
"unemployment_rate", -- Calculated Unemployment Rate ( Population of individuals over age 16 who are unemployed divided by the population of the labor force)
"pop16andover_labor", -- Population over age 16 - All Labor - Includes civilian and military
"totalpop_16over", -- Total Population over age 16
"pertpop_blk", -- Calculated Percent population Black ( Total Population Black divided by Total Population, multiplied by 100).
"pertpop_65andover", -- Calculated Percent population age 65 and over ( Total Population age 65 and over divided by Total Population, multiplied by 100).
"mailreturnrate", -- Percentage of valid census forms returned to U.S. Census Bureau
"totalformsmailed", -- Calculated Total Forms Mailed (Mail Return Count divided by the Mail Return Rate)
"totalpop_65andover", -- Total Population age 65 and over. This field based on combined totals from SocialExplorer.com
"pct_othlanprim", -- Calculated percent population of individuals in which another language is the primary language ( Population of individuals in which another language is the primary language divided by Total Population of individuals over age 5 years, multiplied by 100).
"pop_otherlan", -- Population of individuals in which another language is the primary language ( Other and Unspecified languages ). Combined individuals that speak English "very well" and individuals that speak English less than "very well"
"tractce", -- Numeric 6 Character unique code identifier.
"pop_vietnamese", -- Population of individuals in which Vietnamese is the primary language. Combined individuals that speak English "very well" and individuals that speak English less than "very well"
"affgeoid", -- Combined geographic identifier that specifies a single geography (Summary level code + variant code + component code + US + State FIPS + County FIPS + TractCE).
"pop16andover_unemployed", -- Population of individuals over age 16 who are unemployed
"total_2ormore", -- Total Population 2 or More Races. This field based on combined totals (Hispanic and non Hispanic) from SocialExplorer.com
"pertpop_asian", -- Calculated Percent population Asian ( Total Population Asian divided by Total Population, multiplied by 100).
"pertpop_hisp", -- Calculated Percent population Hispanic ( Total Population Hispanic divided by Total Population, multiplied by 100).
"total_asian", -- Total Population Asian. This field based on combined totals (Hispanic and non Hispanic) from SocialExplorer.com
"popout_oflaborforce", -- Population of Individuals that are out of the labor force
"pct_chineseprim", -- Calculated percent population of individuals in which Chinese is the primary language ( Population of individuals in which Chinese is the primary language divided by Total Population of individuals over age 5 years, multiplied by 100).
"total_pacific", -- Total Population Hawaiian and Pacific Islander Decent. This field based on combined totals (Hispanic and non Hispanic) from SocialExplorer.com
"perpopov25_hseqorabove", -- Calculated Percent population over age 25 - High School or High School Equivilency ( Total Population over over age 25 - High School or High School Equivilency divided by Total Population over 25, multiplied by 100).
"total_nativeam", -- Total Population Native American. This field based on combined totals (Hispanic and non Hispanic) from SocialExplorer.com
"pop_tagalog", -- Population of individuals in which Tagalog is the primary language (Including Filipino). Combined individuals that speak English "very well" and individuals that speak English less than "very well"
"totalpop_nonhisp", -- Total Population non Hispanic
"pct_asiaothprim", -- Calculated percent population of individuals in which another Asian language is the primary language ( Population of individuals in which another Asian language is the primary language divided by Total Population of individuals over age 5 years, multiplied by 100).
"total_otherrace", -- Total Population Other Race. This field based on combined totals (Hispanic and non Hispanic) from SocialExplorer.com
"pctengonly", -- Calculated percent population of individuals that speak primarily English ( Population of individuals that speak primarily English divided by Total Population of individuals over age 5 years, multiplied by 100).
"pertpop_othrace", -- Calculated Percent population Other Race ( Total Population Other Race divided by Total Population, multiplied by 100).
"totalpop_under18", -- Total Individual Population under age 18. This field based on combined totals from SocialExplorer.com
"total_white", -- Total Population White. This field based on combined totals (Hispanic and non Hispanic) from SocialExplorer.com
"pop_engonly", -- Population of individuals that speak primarily English
"pct_arabicprim", -- Calculated percent population of individuals in which an Arabic language is the primary language ( Population of individuals in which an Arabic language is the primary language divided by Total Population of individuals over age 5 years, multiplied by 100).
"pertpop_2ormore", -- Calculated Percent population 2 or More Races ( Total Population 2 or More Races divided by Total Population, multiplied by 100).
"totalpopover25", -- Total Population over 25
"popov_hsoreqorabove", -- Population over age 25 - High School or High School Equivilency. This field based on combined totals (High School or Equivilent and some college) from SocialExplorer.com
"totalpop_hisp", -- Total Population Hispanic
"popov25_bachdegree", -- Population over age 25 - with Bachelor Degree
"pertpop_18to64", -- Calculated Percent population age 18 through 64 ( Total Population age 18 through 64 divided by Total Population, multiplied by 100).
"pop_korean", -- Population of individuals in which Korean is the primary language. Combined individuals that speak English "very well" and individuals that speak English less than "very well"
"perpopov25_graddeg", -- Calculated Percent population over age 25 - with Graduate Degree ( Total Population over age 25 - with Graduate Degree divided by Total Population over 25, multiplied by 100).
"pop_spanish", -- Population of individuals in which Spanish is the primary language. Combined individuals that speak English "very well" and individuals that speak English less than "very well"
"perpopov25_lesshs", -- Calculated Percent population over age 25 - Education level less than High School ( Total Population over age 25 - Education level less than High School divided by Total Population over 25, multiplied by 100).
"avghouseholdinc", -- The average income per household
"perpop16andoverunemployed", -- Calculated Percent population over age 16 - Unemployed ( Total Population over age 16 - Unemployed divided by Total Population over age 16, multiplied by 100).
"pertpop_nonhisp", -- Calculated Percent population non Hispanic ( Total Population non Hispanic divided by Total Population, multiplied by 100).
"popinlaborforce", -- Population in Labor Force
"popov25_graddegree", -- Population over age 25 - with Graduate Degree. This field based on combined totals (Master's, Professional, Doctorate Degrees) from SocialExplorer.com
"pop_chinese", -- Population of individuals in which Chinese is the primary language (Including Mandarin, Cantonese). Combined individuals that speak English "very well" and individuals that speak English less than "very well"
"popov25_lesshsch", -- Population over age 25 - Education level less than High School
"total_blk", -- Total Population Black. This field based on combined totals (Hispanic and non Hispanic) from SocialExplorer.com
"pct_slavicprim", -- Calculated percent population of individuals in which a Slavic language is the primary language ( Population of individuals in which a Slavic language is the primary language divided by Total Population of individuals over age 5 years, multiplied by 100).
"pop_arabic", -- Population of individuals in which an Arabic language is the primary language. Combined individuals that speak English "very well" and individuals that speak English less than "very well"
"geoid", -- Combined geographic identifier that specifies a single geography (State FIPS + County FIPS + TractCE).
"totalpop_18to64", -- Total Population Age 18 through 64. This field based on combined totals from SocialExplorer.com
"pct_vietprim", -- Calculated percent population of individuals in which Vietnamese is the primary language ( Population of individuals in which Vietnamese is the primary language divided by Total Population of individuals over age 5 years, multiplied by 100).
"pct_germanprim", -- Calculated percent population of individuals in which a Germanic language is the primary language ( Population of individuals in which a Germanic language is the primary language divided by Total Population of individuals over age 5 years, multiplied by 100).
"perpopov25_bachdeg", -- Calculated Percent population over age 25 - with Bachelor Degree ( Total Population over age 25 - with Bachelor Degree divided by Total Population over 25, multiplied by 100).
"pct_tagalogprim", -- Calculated percent population of individuals in which Tagalog is the primary language ( Population of individuals in which Tagalog is the primary language divided by Total Population of individuals over age 5 years, multiplied by 100).
"pertpop_white", -- Calculated Percent population White ( Total Population White divided by Total Population, multiplied by 100).
"countyfp", -- Numeric 3 digit code defined in US Federal Information Processing Publication 5-2 to identify US Counties
"statefp", -- Numeric 2 digit code defined in US Federal Information Processing Publication 5-2 to identify US States
"pertpop_pacific", -- Calculated Percent population Hawaiian and Pacific Islander Decent ( Total Population Hawaiian and Pacific Islander Decent divided by Total Population, multiplied by 100).
"pop_germanic", -- Population of individuals in which a Germanic language is the primary language ( German or Other West Germanic Languages ). Combined individuals that speak English "very well" and individuals that speak English less than "very well"
"pertpop_nativeam", -- Calculated Percent population Native American ( Total Population Native American divided by Total Population, multiplied by 100).
"mailreturncount", -- Number of valid census forms returned to the US Census Bureau.
"pop_french", -- Population of individuals in which French is the primary language (French Haitian or Cajun). Combined individuals that speak English "very well" and individuals that speak English less than "very well"
"pct_korprim", -- Calculated percent population of individuals in which Korean is the primary language ( Population of individuals in which Korean is the primary language divided by Total Population of individuals over age 5 years, multiplied by 100).
"pertpop_und18", -- Calculated Percent population under age 18 ( Total Population under age 18 divided by Total Population, multiplied by 100).
"totalpop", -- Total Individual Population
"pct_indeuprim", -- Calculated percent population of individuals in which a Indo-European language is the primary language ( Population of individuals in which a Indo-European language is the primary language divided by Total Population of individuals over age 5 years, multiplied by 100).
"pop_asiaoth", -- Population of individuals in which another Asian language is the primary language ( Including other Pacific Island languages). Combined individuals that speak English "very well" and individuals that speak English less than "very well"
"poplanguage", -- Population of individuals over age 5 years
"pop_indoeu", -- Population of individuals in which another Indo-European language is the primary language. Combined individuals that speak English "very well" and individuals that speak English less than "very well"
"pct_frenchprim", -- Calculated percent population of individuals in which French is the primary language ( Population of individuals in which French is the primary language divided by Total Population of individuals over age 5 years, multiplied by 100).
"pop_slavic", -- Population of individuals in which a Slavic language is the primary language ( Russian, Polish, or Other Slavic Languages ). Combined individuals that speak English "very well" and individuals that speak English less than "very well"
"pct_pop16over_labor" -- Calculated Percent population over age 16 - All Labor ( Total Population over age 16 - All Labor divided by Total Population over age 16, multiplied by 100).
FROM
"datacatalog-cookcountyil-gov/2010-us-census-mail-return-rates-and-demographics-mpyu-4jqk:latest"."2010_us_census_mail_return_rates_and_demographics"
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 datacatalog-cookcountyil-gov/2010-us-census-mail-return-rates-and-demographics-mpyu-4jqk with SQL in under 60 seconds.
This repository is an "external" repository. That means it's hosted elsewhere, in this case at datacatalog.cookcountyil.gov. When you querydatacatalog-cookcountyil-gov/2010-us-census-mail-return-rates-and-demographics-mpyu-4jqk: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 datacatalog.cookcountyil.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 \
"datacatalog-cookcountyil-gov/2010-us-census-mail-return-rates-and-demographics-mpyu-4jqk" \
--handler-options '{
"domain": "datacatalog.cookcountyil.gov",
"tables": {
"2010_us_census_mail_return_rates_and_demographics": "mpyu-4jqk"
}
}'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, datacatalog-cookcountyil-gov/2010-us-census-mail-return-rates-and-demographics-mpyu-4jqk is just another Postgres schema.