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 citizen_satisfaction_survey_20182021 table in this repository, by referencing it like:
"calgary-ca/citizen-satisfaction-survey-20182021-btc8-9kef:latest"."citizen_satisfaction_survey_20182021"or in a full query, like:
SELECT
":id", -- Socrata column ID
"q9_2_34", -- Service Investment_ City of Calgary website.
"q9_2_33", -- Service Investment_ Property tax assessment.
"q9_2_32", -- Service Investment_ Downtown revitalization.
"q9_2_30", -- Service Investment_ Business licenses and inspections.
"q9_2_25", -- Service Investment_ Support for arts and culture including festivals.
"q9_2_22", -- Service Investment_ Affordable housing for low.
"q9_2_21", -- Service Investment_ Social services for individuals such as seniors or youth.
"q9_2_20", -- Service Investment_ Community services such as support for community associations and not for profit groups.
"q9_2_17", -- Service Investment_ Bylaw services for things such as noise complaints, fire pits and weeds.
"q9_2_13", -- Service Investment_ Road maintenance including pothole repairs.
"q9_2_9", -- Service Investment_ The quality of drinking water.
"q9_2_6", -- Service Investment_ Residential garbage collection service.
"q9_2_5", -- Service Investment_ Disaster planning and response.
"q9_1_26", -- Service Satisfaction_ On-street bikeways.
"q9_1_13", -- Service Satisfaction_ Road maintenance including pothole repairs.
"q9_1_6", -- Service Satisfaction_ Residential garbage collection service.
"q9_1_5", -- Service Satisfaction_ Disaster planning and response.
"q8_35", -- Service Importance_ 311 service.
"q8_34", -- Service Importance_ City of Calgary website.
"q8_31", -- Service Importance_ City growth management.
"q8_30", -- Service Importance_ Business licenses and inspections.
"q8_17", -- Service Importance_ Bylaw services for things such as noise complaints, fire pits and weeds.
"q8_15", -- Service Importance_ Snow removal.
"q8_12", -- Service Importance_ City operated roads and infrastructure.
"q8_11", -- Service Importance_ Calgary Transit including bus and CTrain service.
"q8_7", -- Service Importance_ Residential Blue Cart recycling .
"q8_6", -- Service Importance_ Residential garbage collection service.
"q8_2", -- Service Importance_ Calgary Fire Department.
"q19_8", -- The City used input from Calgarians in decision-making about City projects and services.
"q19_3", -- City staff are courteous, helpful, and knowledgeable.
"q10", -- How safe do you or would you feel walking alone in your neighborhood after dark.
"q24bx_7", -- Calgary is a great place to make a life. (Likert scale)
"q40", -- A member of visible minority.
"q37", -- Years in Calgary.
"q39", -- Household income category.
"mweight0", -- Survey weight.
"q9_2_35", -- Service Investment_ 311 service.
"q9_2_29", -- Service Investment_ Development and building inspections and permits.
"q9_2_23", -- Service Investment_ City operated recreation PROGRAMS such as swimming lessons.
"q9_2_19", -- Service Investment_ Calgary's parks, playgrounds and other open spaces.
"q9_2_12", -- Service Investment_ City operated roads and infrastructure.
"q9_2_8", -- Service Investment_ Residential Green Cart service.
"q9_2_3", -- Service Investment_ 911.
"q9_1_30", -- Service Satisfaction_ Business licenses and inspections.
"q9_1_28", -- Service Satisfaction_ City land use planning.
"q9_1_21", -- Service Satisfaction_ Social services for individuals such as seniors or youth.
"q9_1_16", -- Service Satisfaction_ Traffic flow management.
"q8_33", -- Service Importance_ Property tax assessment.
"q8_29", -- Service Importance_ Development and building inspections and permits.
"q8_21", -- Service Importance_ Social services for individuals such as seniors or youth.
"q8_19", -- Service Importance_ Calgary's parks, playgrounds and other open spaces.
"q8_14", -- Service Importance_ Spring road cleaning.
"q8_13", -- Service Importance_ Road maintenance including pothole repairs.
"q8_9", -- Service Importance_ The quality of drinking water.
"q8_8", -- Service Importance_ Residential Green Cart service.
"q8_4", -- Service Importance_ Protection from river flooding.
"q12", -- Increase taxes vs cut services.
"q11a", -- Please rate the value you feel you receive from your municipal property tax dollars.
"q19_5", -- The City of Calgary practices open and accessible government.
"q24bx_6", -- Calgary is a great place to make a living. (Likert scale)
"q24bx_5", -- I am regularly involved in neighbourhood and local community events. (Likert scale)
"q24bx_3", -- I am proud to be a Calgarian. (Likert scale)
"q24bx_2", -- The City of Calgary, municipal government, fosters a city that is inclusive and accepting of all. (Likert scale)
"q24bx_1", -- Calgary is moving in the right direction to ensure a high quality of life for future generations. (Likert scale)
"q3", -- Change in quality of life. (Likert scale)
"q34", -- Household tenancy.
"q9_1_24", -- Service Satisfaction_ City operated recreation FACILITIES such as pools, leisure centres, and golf courses.
"q9_1_20", -- Service Satisfaction_ Community services such as support for community associations and not for profit groups.
"q9_1_19", -- Service Satisfaction_ Calgary's parks, playgrounds and other open spaces.
"q9_1_12", -- Service Satisfaction_ City operated roads and infrastructure.
"q9_1_32", -- Service Satisfaction_ Downtown revitalization.
"q9_1_17", -- Service Satisfaction_ Bylaw services for things such as noise complaints, fire pits and weeds.
"q8_24", -- Service Importance_ City operated recreation FACILITIES such as pools, leisure centres, and golf courses.
"q9_2_11", -- Service Investment_ Calgary Transit including bus and CTrain service.
"q9_1_4", -- Service Satisfaction_ Protection from river flooding.
"q8_25", -- Service Importance_ Support for arts and culture including festivals.
"sexfix", -- Gender (assumed).
"q38", -- Highest level of education.
"q32x", -- Children in household.
"q2a", -- Overall quality of life. (Likert scale)
"s4qt", -- Quadrant of Calgary.
"q9_2_7", -- Service Investment_ Residential Blue Cart recycling.
"q9_2_4", -- Service Investment_ Protection from river flooding.
"q9_2_28", -- Service Investment_ City land use planning.
"q9_2_26", -- Service Investment_ On-street bikeways.
"q9_2_2", -- Service Investment_ Calgary Fire Department.
"q9_2_18", -- Service Investment_ Animal control services for stray animals and pet licensing.
"q9_2_1", -- Service Investment_ Calgary Police Service.
"q9_1_8", -- Service Satisfaction_ Residential Green Cart service.
"q9_1_35", -- Service Satisfaction_ 311 service.
"q9_1_34", -- Service Satisfaction_ City of Calgary website.
"q9_1_31", -- Service Satisfaction_ City growth management.
"q9_1_14", -- Service Satisfaction_ Spring road cleaning.
"q9_1_11", -- Service Satisfaction_ Calgary Transit including bus and CTrain service.
"q9_1_10", -- Service Satisfaction_ Transportation planning.
"q8_5", -- Service Importance_ Disaster planning and response.
"q8_32", -- Service Importance_ Downtown revitalization.
"q8_3", -- Service Importance_ 911.
"q8_27", -- Service Importance_ Calgary's pathway system.
"q8_23", -- Service Importance_ City operated recreation PROGRAMS such as swimming lessons.
"q8_20", -- Service Importance_ Community services such as support for community associations and not for profit groups.
"q8_16", -- Service Importance_ Traffic flow management.
"q8_1", -- Service Importance_ Calgary Police Service.
"q19_7", -- The City allows citizen to have meaningful input into decision-making.
"q19_1", -- The quality of customer service from the city is consistently high.
"market2", -- City of Calgary Ward.
"q9_1_2", -- Service Satisfaction_ Calgary Fire Department.
"q8_26", -- Service Importance_ On-street bikeways.
"q24bx_4", -- I am proud to live in my neighbourhood. (Likert scale)
"q9_2_31", -- Service Investment_ City growth management.
"q9_2_15", -- Service Investment_ Snow removal.
"q9_2_14", -- Service Investment_ Spring road cleaning.
"q9_2_10", -- Service Investment_ Transportation planning.
"q9_1_29", -- Service Satisfaction_ Development and building inspections and permits.
"q9_1_23", -- Service Satisfaction_ City operated recreation PROGRAMS such as swimming lessons.
"q9_1_18", -- Service Satisfaction_ Animal control services for stray animals and pet licensing.
"q9_1_15", -- Service Satisfaction_ Snow removal.
"q19_4", -- City staff are easy to get a hold of when I need them.
"q19_2", -- The city responds quickly to requests and concerns.
"q24cx", -- Calgary is on the right track to being a better city 10 years from now.
"q29x", -- Gender.
"q30", -- Age group.
"q9_2_16", -- Service Investment_ Traffic flow management.
"q9_1_25", -- Service Satisfaction_ Support for arts and culture including festivals.
"q9_2_27", -- Service Investment_ Calgary's pathway system.
"q9_1_22", -- Service Satisfaction_ Affordable housing for low.
"q9_1_27", -- Service Satisfaction_ Calgary's pathway system.
"q8_18", -- Service Importance_ Animal control services for stray animals and pet licensing.
"q8_28", -- Service Importance_ City land use planning.
"q8_10", -- Service Importance_ Transportation planning.
"q9_1_9", -- Service Satisfaction_ The quality of drinking water.
"q9_1_7", -- Service Satisfaction_ Residential Blue Cart recycling.
"q9_1_1", -- Service Satisfaction_ Calgary Police Service.
"q9_1_33", -- Service Satisfaction_ Property tax assessment.
"q19_6", -- The City of Calgary makes customer service a priority.
"qwave", -- Year of survey.
"q9_2_24", -- Service Investment_ City operated recreation FACILITIES such as pools, leisure centres, and golf courses.
"q8_22", -- Service Importance_ Affordable housing for low.
"q9_1_3" -- Service Satisfaction_ 911.
FROM
"calgary-ca/citizen-satisfaction-survey-20182021-btc8-9kef:latest"."citizen_satisfaction_survey_20182021"
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 calgary-ca/citizen-satisfaction-survey-20182021-btc8-9kef 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 calgary-ca/citizen-satisfaction-survey-20182021-btc8-9kef: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 calgary-ca/citizen-satisfaction-survey-20182021-btc8-9kefCheckout 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 calgary-ca/citizen-satisfaction-survey-20182021-btc8-9kef:latestThis will download all the objects for the latest tag of calgary-ca/citizen-satisfaction-survey-20182021-btc8-9kef 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 calgary-ca/citizen-satisfaction-survey-20182021-btc8-9kef: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 calgary-ca/citizen-satisfaction-survey-20182021-btc8-9kef: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, calgary-ca/citizen-satisfaction-survey-20182021-btc8-9kef is just another Postgres schema.