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 dob_safety_violations table in this repository, by referencing it like:
"cityofnewyork-us/dob-safety-violations-855j-jady:latest"."dob_safety_violations"or in a full query, like:
SELECT
":id", -- Socrata column ID
"block", -- The block where the violating condition was observed
"street", -- The address name where the violating condition was observed
"zip", -- The postal code where the violating condition was observed
"community_board", -- Community Boards are volunteer-run neighborhood organizations that work on all kinds of issues pertinent to their communities, often working with elected city officials in doing so. Community Board members, who can be as young as 16, are appointed by Borough Presidents and City Council representatives. They serve two-year terms. There are 59 Community Boards in NYC, each of which represents a unique geographical area, known as a Community District. Note: as a column header, “Community Board” (which refers to a group of Board members) is often used to mean “Community District” (the geographical area) instead.
"council_district", -- The City Council is the lawmaking body of NYC, on equal footing with the mayor in terms of governing power. Besides legislating, the Council has sole approval power over the city budget, and is the final decision-maker in land use matters. There are 51 City Council members in total, each representing a unique geographical area, called a Council District.
"bbl", -- The BBL (Borough, Block, and Lot) is a unique combination of three numeric codes - a 1-digit borough number, a block number (up to 5 digits) and a lot number (up to 4 digits) -- designated and modified by the Department of Finance (DOF). BBLs are used by various city agencies to identify real estate for taxes, zoning, construction, and other purposes.
"census_tract_2020_", -- The Census Tract (Census 2020) field indicates the U.S. Census Tract where the site is located. Please note that as part of the geocoding process, leading and trailing zeros are dropped
"neighborhood_tabulation_area_nta_2020_", -- NTAs are small area boundaries, created by the Department of City Planning (DCP) to aggregate population projections in a small area.. Each NTA approximates a minimum population of 15,000. While NTAs were initially created to support PlaNYC, the thirty-year (2000-2030) sustainability plan for NYC, NTAs are now also being used to present data from the Decennial Census and American Community Survey. NTA boundaries and their associated names do not definitively represent neighborhood boundaries.
"device_type", -- The specific type of device
"lot", -- The lot where the violating condition was observed
"violation_type", -- The type of violation issued
"latitude", -- Latitude of the site's location
"violation_number", -- The violation identifier
"house_number", -- The address number where the violating condition was observed
"violation_remarks", -- Note recorded about the violation
"longitude", -- Longitude of the site's location
"violation_status", -- The status of the violation
"borough", -- The borough where the violating condition was observed
"state", -- The state where the violating condition was observed
"bin", -- Building Identification Number assigned by Department of City Planning
"city", -- The city where the violating condition was observed
"violation_issue_date", -- The date of violation issuance
"device_number", -- The devices identification number
"cycle_end_date" -- The end date of the compliance cycle
FROM
"cityofnewyork-us/dob-safety-violations-855j-jady:latest"."dob_safety_violations"
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 cityofnewyork-us/dob-safety-violations-855j-jady with SQL in under 60 seconds.
This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.cityofnewyork.us. When you querycityofnewyork-us/dob-safety-violations-855j-jady: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.cityofnewyork.us, 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 \
"cityofnewyork-us/dob-safety-violations-855j-jady" \
--handler-options '{
"domain": "data.cityofnewyork.us",
"tables": {
"dob_safety_violations": "855j-jady"
}
}'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, cityofnewyork-us/dob-safety-violations-855j-jady is just another Postgres schema.