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 conservation_easements_for_charities_operating_in table in this repository, by referencing it like:
"colorado-gov/conservation-easements-for-charities-operating-in-u4xw-xiec:latest"."conservation_easements_for_charities_operating_in"or in a full query, like:
SELECT
":id", -- Socrata column ID
"expensesincurredenforcementamt", -- Amount of expenses incurred in monitoring, inspecting, handling of violations, and enforcing conservation easements during the year by the charitable organization
"staffhoursspentenforcementcnt", -- Staff and volunteer hours devoted to monitoring, inspecting, handling of violations, and enforcing conservation easements during the year by the charitable organization
"totaleasementscnt", -- Total number of conservation easements held by the charitable organization
"conservationeasementsind", -- Indicator for if the organization received or held a conservation easement, including easements to preserve open space, the environment, historic land areas, or historic structures. t is True, f is False
"businessname2", -- Additional name of the charitable organization if applicable
"businessname1", -- Name of the charitable organization
"id", -- Automatically generated unique ID
"totalacreagecnt", -- Total acreage restricted by conservation easements by the charitable organization
"ein", -- Employer identification number for charitable organization
"section170hrqrstsfdind", -- Indicator for if each conservation easement reported satisfies the requirements of section 170(h)(4)(B)(i) and section 170(h)(4)(B)(ii) on Schedule D of the IRS 990 e-filing. t is True, f is False
"stateseasementsheldcnt", -- Number of states where property subject to conservation easement is located by the charitable organization
"historicstructureeasementscnt", -- Number of conservation easements on a certified historic structure by the charitable organization
"historicstructureind", -- Indicator for if the organization preserved a certified historical structure. t is True, f is False
"protectionnaturalhabitatind", -- Indicator for if the organization protected natural habitat. t is True, f is False
"writtenpolicymonitoringind", -- Indicator for if the organization has a written policy regarding the periodic monitoring, inspection, handling of violations, and enforcement of the conservation easements it holds. t is True, f is False
"modifiedeasementscnt", -- Number of conservation easements modified, transferred, released, extinguished, or terminated by the organization during the tax year by the charitable organization
"historicstrctreasementsaftrcnt", -- Number of conservation easements acquired after 8/17/06, and not on a historic structure listed in the National Register by the charitable organization
"historiclandareaind", -- Indicator for if the organization preserved a historically important land area. t is True, f is False
"preservationopenspaceind", -- Indicator for if the organization protected open space. t is True, f is False
"preservationpublicuseind", -- Indicator for if the organization preserved land for public use. t is True, f is False
"taxyr" -- Tax year corresponding to the charitable organization's filing
FROM
"colorado-gov/conservation-easements-for-charities-operating-in-u4xw-xiec:latest"."conservation_easements_for_charities_operating_in"
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 colorado-gov/conservation-easements-for-charities-operating-in-u4xw-xiec with SQL in under 60 seconds.
This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.colorado.gov. When you querycolorado-gov/conservation-easements-for-charities-operating-in-u4xw-xiec: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.colorado.gov, 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 \
"colorado-gov/conservation-easements-for-charities-operating-in-u4xw-xiec" \
--handler-options '{
"domain": "data.colorado.gov",
"tables": {
"conservation_easements_for_charities_operating_in": "u4xw-xiec"
}
}'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, colorado-gov/conservation-easements-for-charities-operating-in-u4xw-xiec is just another Postgres schema.