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 311_call_centre_satisfaction_survey_2014 table in this repository, by referencing it like:
"edmonton-ca/311-call-centre-satisfaction-survey-2014-6asu-htha:latest"."311_call_centre_satisfaction_survey_2014"or in a full query, like:
SELECT
":id", -- Socrata column ID
"q16m1", -- Thinking overall about the 311 service, do you have any suggestions about how to improve it?
"d3", -- In which of the following age categories do you belong? Please stop me when I read the correct one.
"q12m4", -- Even though the information might be available on the City website, why did you choose to call 311 instead of looking through the City website? Is it because… Response 4
"qs1", -- Have you called / contacted 311 services over the telephone in the past six months?
"qs2", -- How many times have you called 311 services in the past six months?
"q11m7", -- Why did you choose to call 311? Is it because… Response 7
"q3h", -- Again, thinking about your most recent call to 311, how satisfied were you on the following aspects of your last call. Your reason for calling was resolved in a timely manner
"q3f", -- Again, thinking about your most recent call to 311, how satisfied were you on the following aspects of your last call. The agent was knowledgeable
"q3a", -- Again, thinking about your most recent call to 311, how satisfied were you on the following aspects of your last call. The ease of contacting 311
"d1", -- GENDER
"qs4", -- And what was the purpose of your most recent call to 311? Was it…?
"q12m2", -- Even though the information might be available on the City website, why did you choose to call 311 instead of looking through the City website? Is it because… Response 2
"q12m3", -- Even though the information might be available on the City website, why did you choose to call 311 instead of looking through the City website? Is it because… Response 3
"q16m2", -- Thinking overall about the 311 service, do you have any suggestions about how to improve it? Response 2
"q1", -- Please only consider your last call experience with 311 while providing your opinions. Overall, how satisfied were you with your experience with the most recent call to 311?
"postal",
"q16m3", -- Thinking overall about the 311 service, do you have any suggestions about how to improve it? Response 3
"qs3m3", -- What was the purpose of your call / calls to 311 in the past six months? Response 3
"qs3m4", -- What was the purpose of your call / calls to 311 in the past six months? Response 4
"qs3m1", -- What was the purpose of your call / calls to 311 in the past six months?
"qs3m5", -- What was the purpose of your call / calls to 311 in the past six months? Response 5
"q3c", -- Again, thinking about your most recent call to 311, how satisfied were you on the following aspects of your last call. The agent was helpful
"q3e", -- Again, thinking about your most recent call to 311, how satisfied were you on the following aspects of your last call. The agent was courteous and professional
"q4om2", -- IF SOMEWHAT OR COMPLETELY DISAGREE IN Q4 – Why do you say so? Response 2
"q3d", -- Again, thinking about your most recent call to 311, how satisfied were you on the following aspects of your last call. The information provided was accurate
"q4", -- To what extent do you agree or disagree to the following statement... 311 makes it convenient for Edmontonians to obtain information or seek the service that they want.
"q4om1", -- IF SOMEWHAT OR COMPLETELY DISAGREE IN Q4 – Why do you say so?
"q11m3", -- Why did you choose to call 311? Is it because… Response 3
"q11m4", -- Why did you choose to call 311? Is it because… Response 4
"q11m2", -- Why did you choose to call 311? Is it because… Response 2
"q11m5", -- Why did you choose to call 311? Is it because… Response 5
"q11m6", -- Why did you choose to call 311? Is it because… Response 6
"q10", -- Did you check for information about <QS4> on the City website?
"qs3m2", -- What was the purpose of your call / calls to 311 in the past six months? Response 2
"q5", -- You said that you had called 311 <QS2> times. Thinking about the most recent call to 311, were you calling to follow up on an earlier issue or enquiry...
"q9m2", -- Do you know about the City of Edmonton website and 311 online? Response 2
"q2m1", -- Why were you <Q1> with your most recent call experience?
"q3b", -- Again, thinking about your most recent call to 311, how satisfied were you on the following aspects of your last call. The length of time it took to reach a 311 agent
"q2m2", -- Why were you <Q1> with your most recent call experience? Response 2
"q2m3", -- Why were you <Q1> with your most recent call experience? Response 3
"q3g", -- Again, thinking about your most recent call to 311, how satisfied were you on the following aspects of your last call. The agent processed your call promptly, without a lengthy wait or hold period
"q6m1", -- Tell us why you had to call back on the same issue..
"q6m2", -- Tell us why you had to call back on the same issue. Response 2
"q6m3", -- Tell us why you had to call back on the same issue. Response 3
"q9m1", -- Do you know about the City of Edmonton website and 311 online?
"q11m1", -- Why did you choose to call 311? Is it because...
"q12m1" -- Even though the information might be available on the City website, why did you choose to call 311 instead of looking through the City website? Is it because...
FROM
"edmonton-ca/311-call-centre-satisfaction-survey-2014-6asu-htha:latest"."311_call_centre_satisfaction_survey_2014"
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 edmonton-ca/311-call-centre-satisfaction-survey-2014-6asu-htha with SQL in under 60 seconds.
This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.edmonton.ca. When you queryedmonton-ca/311-call-centre-satisfaction-survey-2014-6asu-htha: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
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 data.edmonton.ca, 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 \
"edmonton-ca/311-call-centre-satisfaction-survey-2014-6asu-htha" \
--handler-options '{
"domain": "data.edmonton.ca",
"tables": {
"311_call_centre_satisfaction_survey_2014": "6asu-htha"
}
}'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, edmonton-ca/311-call-centre-satisfaction-survey-2014-6asu-htha is just another Postgres schema.