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 water_resources_work_orders table in this repository, by referencing it like:
"citydata-mesaaz-gov/water-resources-work-orders-u9pq-7g95:latest"."water_resources_work_orders"or in a full query, like:
SELECT
":id", -- Socrata column ID
"created_by_cycle", -- Yes or No field and identifies if the work order was created by the preventative cyclical work orders.
"actual_finish_year", -- The year work was actually finished
"work_order_permit_cost", -- Total permit cost
"work_order_material_cost", -- Total material cost
"work_order_map_scale", -- GIS Attribute that defines the map scale
"work_order_close_date", -- Date work order was closed
":@computed_region_fcpr_wj2n",
"cancelled", -- Check box to mark work order cancelled.
"shop", -- Enter a shop or warehouse. This field is not used by the Water domain
"contract_billable", -- Check box to mark if the work order is legal billable
"submit_to_month", -- Month submit to field was populated
"expense_type", -- Maintenance and Capital Improvement
"cycle_interval_unit", -- Identifies cycle interval, Days, Weeks, Months, or Years.
"scheduled_month_date",
"cancelled_month", -- The month work was cancelled
"status", -- Condition, or situation of the work order Open, Complete, Closed etc
"submit_to_id", -- Unique ID of person the work order is submitted to
"work_order_category", -- Select the category for this work order. This field has default setting, but can be changed. This field uses a list to populate and is Enterprise wide.
"printed_month_date",
"project_finish_month", -- Month the project was finished
"initiated_month", -- The month the work order was created
"units_description", -- List of unit description, i.e. Each, Gallons, Hours, Feet, Inches, etc
"scheduled_year", -- Year the work order scheduled to begin
"actual_finish_month", -- The month work was actually finished
"project_start_month", -- Month the project was started
"submit_to_opened_by", -- Name of person the submit to was opened by
"service_interruption_ratio", -- Used by the Water Service Interruption performance measure, this number represents the Total Number of Customer Accounts divided by the Total Number of Water Service Interruptions that month.
"asset_unattached", -- Yes or No field, identifies if an asset is attached or not
"cycle_from", -- Identifies which field the cycle will start from. Either Actual Finish Date or Projected Start Date.
"domain_id", -- Unique field to identify domain 1=Transportation, 2=Water, 3=Energy, 4=Facilities, 5=Parks, 10=Mesa Citywide
"line_item_cost", -- Lists cost of a line item identified on a contract
"resolution", -- Select final outcome. This is a list field and is not populated
"scheduled_month", -- Month the work order scheduled to begin
"update_map", -- Check this box if work order has custom fields with new data to update GIS
"work_order_map_extent", -- GIS Attribute that defines map extent
"actual_start_month", -- The month work actually started
"from_month", -- The month breakout of the From Date value
"from_year", -- The year breakout of the From Date value
"is_it_reportable", -- For sanitary sewer overflow, if the event is large enough to be reportable.
"is_reactive", -- Check box selected if work is reactive.
"work_order_template_id", -- Unique ID of the work order template
"work_order_labor_cost", -- Total cost of labor
"work_order_description", -- This field is automatically populated with the work order description selected when the work order was created.
"work_order_closed_month_date",
"work_order_closed_by", -- Automatically populated by the person that closed the work order.
"work_completed_by_id", -- Unique ID of person who completed the work
"submit_to_date", -- Date submit to field was populated
"project_id", -- Automatically populated with unique ID for project selected
"project_finish_month_date",
"printed_date", -- This field is automatically populated with the date work order was printed
"initiated_month_date",
"entity_type", -- This field is automatically populated with the entity type (asset type) selected when the work order was created
"cycle_interval", -- Identifies the number for the cycle interval, 1, 30, etc.
"cancelled_by_id", -- Unique ID of person who cancelled work order
"actual_start_month_date",
"actual_start_date", -- The date and time work actually started
"actual_finish_month_date",
"supervisor_id", -- The unique ID of supervisor
"submit_to_open_month", -- Month submit to open was populated
"map_page", -- Used to automatically populate with the GIS city section and city quarter on certain point features.
"initiated_date", -- The date when the work order was created
"initiated_by_id", -- Unique Id of person who initiated work order
"initiated_by", -- The user who created the work order.
"work_order_equipment_cost", -- Unique ID custom field category
"supervisor", -- The supervisor of person doing the work.
"project_name", -- Select from the drop-down list to tie the work order to a project.
"cancelled_by", -- Field automatically populated with the login of the user who cancelled the work order
"actual_finish_date", -- The date and time work was actually finished
"units_accomplished", -- Total number of units accomplished. Waste Water group uses this field to track miles of sewer line cleaned.
"submit_to_month_date",
"work_completed_by", -- Name of person who completed the work
"submit_to_open_year", -- Year submit to open was populated
"submit_to_open_month_date",
"district", -- Enterprise wide field. Water uses this field to designate which work group the Process Controls group did work for.
"account_number", -- This list is made of old RC #'s. This is an Enterprise Wide field and the list is the same for all Domains
"project_start_month_date",
"contractor_id", -- Unique Contractor ID, for joining Contractor table/dataset
"work_order_closed_month", -- Month work order was closed
"cancelled_month_date",
"cancelled_date", -- The date work order is canceled. This field is automatically populated with the date.
"close_by_id", -- Login ID of user who closed the work order
"cancel_reason", -- If the work order has been canceled, this field appears and a reason is entered.
"submit_to_opened_date", -- Date submit to field was opened
"submit_to", -- The person who the work order is submitted to.
"legally_billable", -- Check box selected if work legal and billable
"requested_by", -- The user who requested the work order
"stage", -- Identifies whether work order is actual or proposed
"project_finish_date", -- Project date for the work to be completed
"primary_contract_id", -- Unique ID of contractor primary contract.
"work_order_cost", -- Total cost of work order
"requested_by_id", -- The unique ID of who requested the work order
"from_date", -- The next cycle work order will be created by either Actual Finish Date, Projected Start Date, or user can Pick a Date.
"project_finish_year", -- Year the project was finished
"asset_group", -- Enterprise wide field. Selects a specific group within a domain. i.e. Water domain has two groups Water and Waste Water.
"submit_to_year", -- Year submit to was populated
"units_locked", -- Yes or No field to lock the units of measure so it can not be changed
"printed_month", -- Month the work order was printed
"priority", -- Priority rating for the work order 1-5 with 1 being the highest priority.
"map_template_name", -- Map template name used by the work order
"cancelled_year", -- The year work was cancelled
"work_order_id", -- This field is automatically populated with the unique work order ID and cannot be changed.
"work_order_closed_year", -- Year work order was closed
"submit_to_opened_by_id", -- The unique ID of person submitted to.
"source_work_order_id", -- Parent work order ID
"project_start_date", -- Project start date for the work to begin
"project_start_year", -- Year the project was started
"initiated_year", -- The year the work order was created
"work_order_output", -- Not sure what this field is for. It is populated with A or C.
"work_order_custom_field", -- Unique ID custom field category
"service_interruption_duration", -- The length of time in hours of water service interruption if any.
"scheduled_date", -- Projected date for the work to begin
"printed_year", -- Year the work order was printed
"number_of_days_before", -- Number of days before the work order is printed
"initiated_by_app", -- Automatically populated by the app that created the work order. All records = Internal
"from_month_date",
"actual_start_year" -- The year work actually started
FROM
"citydata-mesaaz-gov/water-resources-work-orders-u9pq-7g95:latest"."water_resources_work_orders"
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 citydata-mesaaz-gov/water-resources-work-orders-u9pq-7g95 with SQL in under 60 seconds.
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, 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 (like this repository), where the author has pushed Splitgraph Images to the repository, you can "clone" and/or "checkout" the data using sgr cloneand sgr checkout.
Cloning Data
Because citydata-mesaaz-gov/water-resources-work-orders-u9pq-7g95:latest is a Splitgraph Image, you can clone the data from Spltgraph Cloud to your local engine, where you can query it like any other Postgres database, using any of your existing tools.
First, install Splitgraph if you haven't already.
Clone the metadata with sgr clone
This will be quick, and does not download the actual data.
sgr clone citydata-mesaaz-gov/water-resources-work-orders-u9pq-7g95Checkout the data
Once you've cloned the data, you need to "checkout" the tag that you want. For example, to checkout the latest tag:
sgr checkout citydata-mesaaz-gov/water-resources-work-orders-u9pq-7g95:latestThis will download all the objects for the latest tag of citydata-mesaaz-gov/water-resources-work-orders-u9pq-7g95 and load them into the Splitgraph Engine. Depending on your connection speed and the size of the data, you will need to wait for the checkout to complete. Once it's complete, you will be able to query the data like you would any other Postgres database.
Alternatively, use "layered checkout" to avoid downloading all the data
The data in citydata-mesaaz-gov/water-resources-work-orders-u9pq-7g95:latest is 0 bytes. If this is too big to download all at once, or perhaps you only need to query a subset of it, you can use a layered checkout.:
sgr checkout --layered citydata-mesaaz-gov/water-resources-work-orders-u9pq-7g95:latestThis will not download all the data, but it will create a schema comprised of foreign tables, that you can query as you would any other data. Splitgraph will lazily download the required objects as you query the data. In some cases, this might be faster or more efficient than a regular checkout.
Read the layered querying documentation to learn about when and why you might want to use layered queries.
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, citydata-mesaaz-gov/water-resources-work-orders-u9pq-7g95 is just another Postgres schema.