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 recapitulation table in this repository, by referencing it like:
"texas-gov/recapitulation-jxqk-9dmq:latest"."recapitulation"or in a full query, like:
SELECT
":id", -- Socrata column ID
"work_begin_date", -- Date at which contract work begins
"vendor_id", -- Vendor identification number
"total_work_to_date", -- Total amount billed to the contract to date.
"to", -- Date at which contract work begins
"time_begin_date", -- Date at which contract working / calendar days begins.
"substantial_work_completed", -- Date substantial work was completed by contractor
"subcontractor_percent", -- The percentage of subcontractor.
"subcontractor_name", -- The name of subcontractor.
"state_senate_district_list", -- The State Senate District List in Texas is a compilation of all the districts and their respective senators within the upper chamber of the Texas state legislature.
"revised_bid_price", -- Revised Bid Price is the cost of the total contract currently (after approved Change orders / adjustments).
"report_date", -- Report Date
"projected", -- Projected is the amount the contract is projected to cost at the time of completion (after any cost adjustment or incentives/disincentives).
"project_manager", -- This is an auto populated field based on who initiated the project. This should be updated with the Project Manager who is InCharge of the project throughout the lifecycle. This can be modified.
"project_number", -- Number assigned to the project for identification purposes to track funds.
"project_engineer", -- This is the Area Engineer assigned to the project. This relates to the P5 screen in DCIS.
"percent_complete_time", -- Percentage of completion based on current number of days charged to the project. [Day Charged]/[Days Allowed]
"percent_complete", -- Percentage of completion based on the total amount paid to the contractor: [Total Amt Paid to Contractor]/[Revised Bid Price]
"other_adjustments", -- Other contract adjustments chaged
"original_contract_bid_price", -- Original Contract Bid Price is the contract price at execution.
"net_change_order_amount", -- Amount of all approved change orders for a contract
"liquidated_damages", -- Liquidated Damages charged
"let_date", -- Date project let
"highway", -- Represents the highway type and number.
"from", -- Beginning location of the project.
"execution_date", -- Date that the contract is executed.
"estimate_status", -- Status of the current estimate
"estimate_generation_date", -- Date current estimate was processed
"estimate_approval_date", -- Date current estimate was approved
"estimate_amount", -- Amount of the current estimate
"district_number", -- The district where the project is to be conducted in district code.
"district", -- The district where the project is to be conducted. The project must be within the boundary of the district. Division has a statewide responsibility in a specialized function or asset.
"disincentives", -- Disincentives charged
"difference", -- Difference between the contract activation price less the bid amount
"days_granted", -- Number of days granted by all change orders and other processes.
"days_allowed", -- Total number of days to complete the project. [Bid Days]+[Days Granted]
"date_work_begin", -- Date at which contract work begins
"county", -- The county(s) where the project location is identified. The project can be across the counties, while it is within the same control section.
"contract_number", -- Contract Number
"change_order_adjustment_days", -- Number of days granted by all change orders.
"bid_days", -- The number of days to complete the contract as bid on by the winning contractor.
"area_office_number", -- This specifies the office of a particular district. It is divided into several area offices which are made of several counties. This field is only visible for Maintenance projects.
"area_engineer", -- This is the Area Engineer assigned to the project. This relates to the P5 screen in DCIS.
"type_of_work", -- This is the contract’s type of work. Where multiple projects are combined into one contract, this will include a summary of the type of work for all projects.
"total_amount_paid_to", -- Total amount paid to the contractor
"state_house_district_list", -- The State House District List in Texas is a roster of all the districts and their respective representatives within the lower chamber of the Texas state legislature.
"retain",
"date_work_accepted", -- Date completed work was accepted by oversight.
"contractor", -- Current Contractor's Name
"contract_type", -- Type of project the contract is for: C = Construction, M = Maintenance, B = Building, D = Design Build, R = Rail
"contract_status", -- Current Contract status
"maintenance_contract_indicator", -- Indicate contract is a maintenance contract.
"third_party_funding", -- Amount added to the contract by a thrid party
"subcontracot", -- Subcontractor and percentage in single field
"incentives", -- Incentives paid
"district_engineer", -- District Engineer Name
"days_charged", -- Total number of days currently charged to the project.
"date_time_begin", -- Date at which contract working / calendar days begins.
"contract_oversight", -- Agency providing oversight for the contract
"ccsj", -- This is the identifier of the combined project for all associated control section jobs/project ID’s.
"percent_time_used",
"estimate_number", -- Current Estimate Number Processed
"contract_funding", -- Indicates State or Federal Funding Sources
"spec_book_year", -- Spec Book year used for bid items on the project
"estimate_type" -- Current Estimate type.
FROM
"texas-gov/recapitulation-jxqk-9dmq:latest"."recapitulation"
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 texas-gov/recapitulation-jxqk-9dmq with SQL in under 60 seconds.
This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.texas.gov. When you querytexas-gov/recapitulation-jxqk-9dmq: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.texas.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 \
"texas-gov/recapitulation-jxqk-9dmq" \
--handler-options '{
"domain": "data.texas.gov",
"tables": {
"recapitulation": "jxqk-9dmq"
}
}'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, texas-gov/recapitulation-jxqk-9dmq is just another Postgres schema.