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_2016 table in this repository, by referencing it like:
"edmonton-ca/311-call-centre-satisfaction-survey-2016-p5jn-jbs7:latest"."311_call_centre_satisfaction_survey_2016"or in a full query, like:
SELECT
":id", -- Socrata column ID
"q15m10", -- Even though the information might be available through the self-service options, why did you choose to call 311 instead of using a self-serving option? Is it because... Response 10
"q15m7", -- Even though the information might be available through the self-service options, why did you choose to call 311 instead of using a self-serving option? Is it because... Response 7
"q15m4", -- Even though the information might be available through the self-service options, why did you choose to call 311 instead of using a self-serving option? Is it because... Response 4
"q14m9", -- Why did you choose to call 311? Is it because... Response 19
"q14m7", -- Why did you choose to call 311? Is it because... Response 17
"q14m1", -- Why did you choose to call 311? Is it because... Response 11
"q12m7", -- 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? Response 7
"q12m6", -- 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? Response 6
"q11m9", -- Why did you choose to call 311? Is it because... Response 9
"q11m7", -- Why did you choose to call 311? Is it because... Response 7
"qs3m7", -- What was the purpose of your call / calls to 311 in the past six months? Was it...
"qs3m6", -- What was the purpose of your call / calls to 311 in the past six months? Was it...
"qs3m3", -- What was the purpose of your call / calls to 311 in the past six months? Was it...
"qs2", -- How many times have you called 311 services in the past six months?
"qn1", -- Have you called / contacted 311 services over the telephone in the past six months to enquire about ETS or Edmonton Transit System?
"d3", -- In which of the following age categories do you belong?
"q14m2", -- Why did you choose to call 311? Is it because... Response 12
"q13a2", -- Did you use ETS to Go to check transit information?
"q12m10", -- 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? Response 10
"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? Response 3
"q10", -- Did you check for information about <REC3> on the City website?
"q14m6", -- Why did you choose to call 311? Is it because... Response 16
"q12a4", -- Are you aware of the following self-service options through which you can access information about ETS services? ETS Text and Ride - where you can text a bus or bus stop number and get schedule information on your mobile
"q12a2", -- Are you aware of the following self-service options through which you can access information about ETS services? ETS TO GO - a modified version of the Google trip planner which can be used on your mobile devices
"q12a1", -- Are you aware of the following self-service options through which you can access information about ETS services? GOOGLE TRIP PLANNER available on the ETS website through which you can plan your route from start to finish
"q9m2", -- Do you know about the City of Edmonton website and 311 online? Response 2
"q4om1", -- Why do you say so? Response 1
"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
"qs5m3", -- Was your last call to 311 for transit information to... Please choose all that apply. Response 3
"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
"q6m4", -- Tell us why you had to call back on the same issue.. Response 4
"q13a1", -- Did you use Google Trip Planner to check transit information?
"q12m8", -- 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? Response 8
"q12m9", -- 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? Response 9
"q13a5", -- Did you use Edmonton.ca to check transit information?
"q4om5", -- Why do you say so? Response 5
"q11m8", -- Why did you choose to call 311? Is it because... Response 8
"q14m5", -- Why did you choose to call 311? Is it because... Response 15
"q14m10", -- Why did you choose to call 311? Is it because... Response 20
"q6m2", -- Tell us why you had to call back on the same issue.. Response 2
"q5", -- You said that you had called 311 <REC0> times. Thinking about the most recent call to 311, were you calling to follow up on an earlier issue or enquiry... [READ OPTIONS. SINGLE RESPONSE]
"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
"q12m5", -- 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? Response 5
"q11m5", -- Why did you choose to call 311? Is it because... Response 5
"q11m2", -- Why did you choose to call 311? Is it because... Response 2
"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? Response 4
"q11m1", -- Why did you choose to call 311? Is it because... Response 1
"q14m3", -- Why did you choose to call 311? Is it because... Response 13
"q15m5", -- Even though the information might be available through the self-service options, why did you choose to call 311 instead of using a self-serving option? Is it because... Response 5
"q1", -- The next few questions are about the most recent call you made to 311 <QS4>. 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?
"q15m6", -- Even though the information might be available through the self-service options, why did you choose to call 311 instead of using a self-serving option? Is it because... Response 6
"q17bm3", -- You are aware of the 311 app, but have not used it. What has prevented you from using the 311 app? Response 3
"q16m4", -- Thinking overall about the 311 service, do you have any suggestions about how to improve it? Response 4
"q17bm2", -- You are aware of the 311 app, but have not used it. What has prevented you from using the 311 app? Response 2
"q17a", -- Have you used the 311 app in the past 12 months?
"q16m5", -- Thinking overall about the 311 service, do you have any suggestions about how to improve it? Response 5
"q15m9", -- Even though the information might be available through the self-service options, why did you choose to call 311 instead of using a self-serving option? Is it because... Response 9
"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
"q2m5", -- Why were you <Q1> with your most recent call experience? Response 5
"q13a3", -- Did you use Buslink to check transit information?
"q12a5", -- Are you aware of the following self-service options through which you can access information about ETS services? Edmonton.ca - The City of Edmonton website where you can access information about ETS
"q12a3", -- Are you aware of the following self-service options through which you can access information about ETS services? BUSLINK - where you can call 780 496 1600 anytime to get automated information about ETS services
"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? Response 1
"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? Response 2
"q11m10", -- Why did you choose to call 311? Is it because... Response 10
"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
"qs1", -- Have you called / contacted 311 services over the telephone in the past six months?
"q13a4", -- Did you use ETS Text and Ride to check transit information?
"qs3m4", -- What was the purpose of your call / calls to 311 in the past six months? Was it...
"qs3m1", -- What was the purpose of your call / calls to 311 in the past six months? Was it...
"qs3m2", -- What was the purpose of your call / calls to 311 in the past six months? Was it...
"q14m4", -- Why did you choose to call 311? Is it because... Response 14
"qs3m5", -- What was the purpose of your call / calls to 311 in the past six months? Was it...
"q2m1", -- Why were you <Q1> with your most recent call experience? Response 1
"q15m8", -- Even though the information might be available through the self-service options, why did you choose to call 311 instead of using a self-serving option? Is it because... Response 8
"qs5m1", -- Was your last call to 311 for transit information to... Please choose all that apply. Response 1
"q15m3", -- Even though the information might be available through the self-service options, why did you choose to call 311 instead of using a self-serving option? Is it because... Response 3
"qs5m2", -- Was your last call to 311 for transit information to... Please choose all that apply. Response 2
"q15m1", -- Even though the information might be available through the self-service options, why did you choose to call 311 instead of using a self-serving option? Is it because... Response 1
"q14m8", -- Why did you choose to call 311? Is it because... Response 18
"qs4", -- And what was the purpose of your most recent call to 311? Was it...
"q15m2", -- Even though the information might be available through the self-service options, why did you choose to call 311 instead of using a self-serving option? Is it because... Response 2
"q17c", -- How satisfied are you with the 311 app? Are you…
"q17bm4", -- You are aware of the 311 app, but have not used it. What has prevented you from using the 311 app? Response 4
"q17bm5", -- You are aware of the 311 app, but have not used it. What has prevented you from using the 311 app? Response 5
"q17bm1", -- You are aware of the 311 app, but have not used it. What has prevented you from using the 311 app? Response 1
"q17", -- Are you aware that the City of Edmonton has a 311 app which you can access on your mobile device and report your concerns while you are on the go?
"q16m3", -- Thinking overall about the 311 service, do you have any suggestions about how to improve it? Response 3
"q16m1", -- Thinking overall about the 311 service, do you have any suggestions about how to improve it? Response 1
"q9m1", -- Do you know about the City of Edmonton website and 311 online? Response 1
"q16m2", -- Thinking overall about the 311 service, do you have any suggestions about how to improve it? Response 2
"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
"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
"q2m4", -- Why were you <Q1> with your most recent call experience? Response 4
"q2m2", -- Why were you <Q1> with your most recent call experience? Response 2
"q6m3", -- Tell us why you had to call back on the same issue.. Response 3
"q4om4", -- Why do you say so? Response 4
"q4", -- To what extent do you agree or disagree to the following statement... Do you Completely Agree, Somewhat Agree, Somewhat Disagree or Completely Disagree that...{311 makes it convenient for Edmontonians to obtain information or seek the service that they want
"q6m1", -- Tell us why you had to call back on the same issue.. Response 1
"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
"q4om2", -- Why do you say so? Response 2
"q4om3", -- Why do you say so? Response 3
"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
"q11m6" -- Why did you choose to call 311? Is it because... Response 6
FROM
"edmonton-ca/311-call-centre-satisfaction-survey-2016-p5jn-jbs7:latest"."311_call_centre_satisfaction_survey_2016"
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-2016-p5jn-jbs7 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 edmonton-ca/311-call-centre-satisfaction-survey-2016-p5jn-jbs7: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 edmonton-ca/311-call-centre-satisfaction-survey-2016-p5jn-jbs7Checkout 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 edmonton-ca/311-call-centre-satisfaction-survey-2016-p5jn-jbs7:latestThis will download all the objects for the latest tag of edmonton-ca/311-call-centre-satisfaction-survey-2016-p5jn-jbs7 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 edmonton-ca/311-call-centre-satisfaction-survey-2016-p5jn-jbs7: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 edmonton-ca/311-call-centre-satisfaction-survey-2016-p5jn-jbs7: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, edmonton-ca/311-call-centre-satisfaction-survey-2016-p5jn-jbs7 is just another Postgres schema.