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 delaware_checkbook_expenditure_details table in this repository, by referencing it like:
"delaware-gov/delaware-checkbook-expenditure-details-7bip-nb4g:latest"."delaware_checkbook_expenditure_details"or in a full query, like:
SELECT
":id", -- Socrata column ID
"approp", -- A five digit ChartField established to record specific authorizations to spend
"fund_type", -- The type of fund where the expenditure is recorded.
"program_descr", -- The Description of the five digit ChartField used by the organizations to track revenues and or expenditures.
"activity_descr", -- The description of the activities that are sub-components of Projects. Project Budgets are established at this level. Ex: The Project “Build New Elementary School” has two activities associated with it: Design and Construction. Budgets are defined at the Design and Construction activity levels.Grants: Activities are the specific tasks that make up a project. You can add transactions to a project only at the activity level. The Grants module uses Project Activity to represent federal reporting categories.
"op_unit_descr", -- The description of the eight digit ChartField consisting of a budget, location and manager.
"approp_descr", -- The description of the five digit ChartField established to record specific authorizations to spend
"fund", -- A self-balancing Chart Field used in government to segregate different fund types.
"amount", -- The amount of the payment.
"sch_code", -- A six digit code used to identify physical pupil based expenses
"fiscal_year", -- The state fiscal year of the expenditure.
"vendor", -- The name of the business or person that received payment from the state organization.
"budget_ref", -- Four (4) digit Chart Field used to identify the year in which the budget was funded.
"op_unit", -- An eight digit ChartField consisting of a budget, location and manager.
"project", -- A cost center established by an agency; usually either for a Grant or Capital Project or for a DelDOT Operating Project. Projects are associated with an agency’s PCBU. Once the cost center is established, Activities may be associated with the cost center and budgets can be attached at the activity level.Grants: Projects provide the structure to which activities and resources are added. Projects can contain activities and resources. This provides a hierarchical relationship between projects and facilitates cost roll ups. A project in First State Financial Grants is a subset of a proposal (to handle the post-award activities); proposals may contain or entail multiple projects.
"project_descr", -- The description of the cost center established by an agency; usually either for a Grant or Capital Project or for a DelDOT Operating Project. Projects are associated with an agency’s PCBU. Once the cost center is established, Activities may be associated with the cost center and budgets can be attached at the activity level.Grants: Projects provide the structure to which activities and resources are added. Projects can contain activities and resources. This provides a hierarchical relationship between projects and facilitates cost roll ups. A project in First State Financial Grants is a subset of a proposal (to handle the post-award activities); proposals may contain or entail multiple projects.
"check_number", -- The payment reference number. This includes checks and ACH payments.
"fiscal_period", -- The fiscal period of the expenditure. Each fiscal period corresponds to a month within the fiscal year. For example, 1 = July, 2 = August, 3 = September, 7 = January, 12 = June.
"department", -- The state organization where the expense is recognized.
"division", -- The division within the state organization where the expense is recognized.
"fund_descr", -- The description of the self-balancing Chart Field used in government to segregate different fund types.
"deptid", -- Chartfield that tracks administrative units within the State’s organizational hierarchy. A DeptID must have an ongoing business purpose, contains positioned employees, has a permanent budget, and is assigned physical space.
"check_date", -- The date of the payment.
"activity", -- Activities are sub-components of Projects. Project Budgets are established at this level. Ex: The Project “Build New Elementary School” has two activities associated with it: Design and Construction. Budgets are defined at the Design and Construction activity levels.Grants: Activities are the specific tasks that make up a project. You can add transactions to a project only at the activity level. The Grants module uses Project Activity to represent federal reporting categories.
"pc_bu_descr", -- The description of the PCBU’s that are used to functionally organize (and segregate) the use of PC by state organizations. Each Agency and School District within the state which receives a grant or which chooses to use Project Costing to support capital projects is assigned its own PCBU. Grants: Grants module is also using PC Bus Units to group projects under different major Divisions.
"pc_bu", -- PCBU’s are used to functionally organize (and segregate) the use of PC by state organizations. Each Agency and School District within the state which receives a grant or which chooses to use Project Costing to support capital projects is assigned its own PCBU. Grants: Grants module is also using PC Bus Units to group projects under different major Divisions.
"sch_code_descr", -- The description of the six digit code used to identify physical pupil based expenses
"program", -- A five digit ChartField used by the organizations to track revenues and or expenditures.
"account_descr", -- The description of the five digit chartfield used to identify asset, liabilities, fund balance, revenue or expenses. Project Costing: Classifies the nature of a transaction.
"account", -- A five digit chartfield used to identify asset, liabilities, fund balance, revenue or expenses. Project Costing: Classifies the nature of a transaction.
"deptid_descr" -- The description of the chartfield that tracks administrative units within the State’s organizational hierarchy. A DeptID must have an ongoing business purpose, contains positioned employees, has a permanent budget, and is assigned physical space.
FROM
"delaware-gov/delaware-checkbook-expenditure-details-7bip-nb4g:latest"."delaware_checkbook_expenditure_details"
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 delaware-gov/delaware-checkbook-expenditure-details-7bip-nb4g with SQL in under 60 seconds.
This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.delaware.gov. When you querydelaware-gov/delaware-checkbook-expenditure-details-7bip-nb4g: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.delaware.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 \
"delaware-gov/delaware-checkbook-expenditure-details-7bip-nb4g" \
--handler-options '{
"domain": "data.delaware.gov",
"tables": {
"delaware_checkbook_expenditure_details": "7bip-nb4g"
}
}'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, delaware-gov/delaware-checkbook-expenditure-details-7bip-nb4g is just another Postgres schema.