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 dig_ticket_notifications table in this repository, by referencing it like:
"cityofchicago/dig-ticket-notifications-cygx-ui4j:latest"."dig_ticket_notifications"or in a full query, like:
SELECT
":id", -- Socrata column ID
"dig_ticket_", -- Dig ticket number which can be reviewed through the 811 user portal.
"expirationdate", -- The date and time at which the dig ticket expires.
"direction", -- The direction component of the street where the excavation will occur.
"notice_history_site_id", -- The unique site number associated with each utility owner. The 811 system identifies utility owners by their site number.
"location", -- The location of the midpoint of the address range in a format that allows for creation of maps and other geographic operations on this data portal.
"placement", -- The location within the public right of way where the excavation will occur. Excavations on private property do not have placement data associated with them.”
":@computed_region_vrxf_vc4k",
":@computed_region_6mkv_f3dw",
"laststatustime", -- The last time the ticket was updated. This can be either an update from the City of Chicago to a utility or a response from the utility.
"notice_history_status", -- Utility owners respond to the ticket via the 811 system using the following statuses. All utilities should at a minimum respond "acknowledged." The use of the other status codes is a company by company decision. a. “Acknowledged” - a notification from the utility owner that they received the dig ticket through their web service. This is the most common status and often is not updated. b. “Marked” - a notification from the utility owner that they marked the location of their facility at the address on the dig ticket. c. “In Route” - a notification from the utility owner that their locator is on the way to the address on the dig ticket. d. “Positive Response / Clear” - a notification from the utility owner that they do not have facilities near the address on the dig ticket. e. “Unacknowledged” - a notification from the system that the utility owner has not acknowledged receiving the dig ticket.
"permit_", -- The permit associated with the dig ticket if one exists.
":@computed_region_rpca_8um6",
"longitude", -- The longitude of the midpoint of the address range.
"requestdate", -- The date and time on which the dig request was made.
":@computed_region_bdys_3d7i",
"suffix", -- The suffix of the street where the excavation will occur.
":@computed_region_43wa_7qmu",
"last_member_transmission_flag", -- The most recent record for each utility associated with a unique ticket. The most recent record for each utility on a dig ticket will be marked True and all others will be marked False.
"stname", -- The name of the street where the excavation will occur.
"emergency", -- Indicates whether dig ticket was an emergency request.
"notice_history_type", -- The type of ticket issued including: a. “Initial ticket” - the first ticket issued. Utility owners have 48 hours (recorded as the “DIGDATE”) to mark the location of their facilities at the address on the ticket. b. “No-show” - a follow-up request associated with an initial ticket that notifies specific utilities that their facility was not marked by the dig date. c.“Remark request” - a follow-up request associated with an initial ticket that notifies specific utilities that they need to put new location marks at the site. d. “Cancellation Notice” - a notice to the utility owners that the dig ticket has been cancelled. e. “Notification of renewal” - a notice to the utility owners that the dig ticket expiration date has been extended. f. “Update ticket” - a notice to the utility owners that information on the dig ticket has been updated.
"stnofrom", -- The start of the street address where the excavation will occur. The 811 system creates separate dig tickets for address ranges by breaking them into even and odd blocks.
"primarycontactfirst", -- The first name of the individual or company requesting the dig ticket.
"notice_history_created", -- The date and time when the notice was sent to the utility.
"latitude", -- The latitude of the midpoint of the address range.
"notice_history_utility_name", -- The name of the utility company notified. Each dig ticket is sent to one or more utility owners. Thus, the dig ticket number as well as most of the data associated with the dig ticket will be repeated for each utility owner that was notified under that ticket.
"primarycontactlast", -- The last name of the individual or company requesting the dig ticket.
"digdate", -- The date and time before which the excavator is not permitted to break ground.
"stnoto", -- The end of the street address where the excavation will occur. The 811 system creates separate dig tickets for address ranges by breaking them into even and odd blocks.
"uniquekey" -- A unique number that differentiates each record in the data set.
FROM
"cityofchicago/dig-ticket-notifications-cygx-ui4j:latest"."dig_ticket_notifications"
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 cityofchicago/dig-ticket-notifications-cygx-ui4j with SQL in under 60 seconds.
This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.cityofchicago.org. When you querycityofchicago/dig-ticket-notifications-cygx-ui4j: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.cityofchicago.org, 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 \
"cityofchicago/dig-ticket-notifications-cygx-ui4j" \
--handler-options '{
"domain": "data.cityofchicago.org",
"tables": {
"dig_ticket_notifications": "cygx-ui4j"
}
}'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, cityofchicago/dig-ticket-notifications-cygx-ui4j is just another Postgres schema.