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 residential_existing_homes_one_to_four_units table in this repository, by referencing it like:
"ny-gov/residential-existing-homes-one-to-four-units-5vqm-4rpf:latest"."residential_existing_homes_one_to_four_units"or in a full query, like:
SELECT
":id", -- Socrata column ID
"baseline_gas_mmbtu", -- The modeled, weather normalized natural gas usage during the baseline period (9 continuous months prior to intervention). Blank cells represent data that were not required or are not currently available
"consolidated_edison", -- Utility for the project, where “True” indicates the project is in the Consolidated Edison territory. Some homes are served by more than one utility company
"project_county", -- Name of county for project location
"project_city", -- Name of city for project location
"project_zip", -- Five-digit ZIP code for project location
"weather_station", -- Weather Station United States Air Force (USAF) Identifier closest to the project
"weather_station_normalization", -- Weather Station USAF Identifier for weather station used for normalization (may be different from the weather station closest to the project if insufficient weather data)
"size_of_home", -- Square footage of the home. Blank cells represent data that were not required or are not currently available
"volume_of_home", -- Cubic feet of conditioned space in the home. Blank cells represent data that were not required or are not currently available
"year_home_built", -- Home construction date. Blank cells represent data that were not required or are not currently available
"total_project_cost", -- Cost of project in US dollars
"contractor_incentive", -- Amount of incentive in US dollars paid directly to the Contractor
"total_incentives", -- Amount of homeowner incentive in US dollars. Zero dollars represent either projects receiving only financing, or Market Rate projects that did not receive an incentive, but still participated in the program to take advantage of working with participating contractors and Quality Assurance benefits
"amount_financed_through_program", -- Project loan amount in US dollars. If zero, then project was not financed
"estimated_annual_electric_savings_kwh", -- Modeled annual electric savings estimate in kWh per model developed by participating contractor at time of audit. Negative numbers represent projects with post-retrofit increase in electric consumption, typically from fuel conversions or ancillary savings. Blank cells represent data that were not required or are not currently available
"estimated_first_year_energy_savings", -- Estimated post-retrofit first year dollar savings (USD) from model developed by the contractor at time of audit. Negative numbers represent projects with estimated post-retrofit first year dollar expenses, typically occurring when non-energy work was completed such as health and safety improvements, or when work was done in conjunction with another, net positive energy savings project. Blank cells represent data that were not required or are not currently available
"baseline_electric_kwh", -- The modeled, weather normalized electric usage during the baseline period (9 continuous months prior to intervention). Blank cells represent data that were not required or are not currently available
"reporting_electric_kwh", -- The modeled, weather-normalized electric usage during the reporting period (9 continuous months after the intervention). Blank cells represent data that were not required or are not currently available
"reporting_gas_mmbtu", -- The modeled, weather-normalized natural gas usage during the reporting period (9 continuous months after the intervention). Blank cells represent data that were not required or are not currently available
"evaluated_annual_electric_savings_kwh", -- The modeled, weather normalized annual site-level electric savings (kWh), calculated as pre-retrofit normalized baseline less post-retrofit normalized usage. Negative numbers represent projects with post-retrofit increase in electric consumption, typically from fuel conversions or ancillary savings
"geocoded_column_zip",
"geocoded_column_city",
"geocoded_column_state",
"geocoded_column_address",
":@computed_region_yamh_8v7k",
":@computed_region_wbg7_3whc",
":@computed_region_kjdx_g34t",
"customer_type", -- Indicates if the homeowner received market rate incentives or assisted subsidy
"climate_zone", -- Project climate zone. See https://www.energy.gov/eere/buildings/climate-zones for more information.
"contractor_id", -- Unique identifier for participating Contractor responsible for audit, modeled savings projections, and project implementation
"evaluated_annual_gas_savings_mmbtu", -- The modeled, weather normalized annual site-level gas savings (MMBtu), calculated as pre-retrofit normalized baseline less post-retrofit normalized usage. Negative numbers represent projects with post-retrofit increase in gas consumption, typically from fuel conversions or ancillary savings
"estimated_annual_gas_savings_mmbtu", -- Modeled annual gas savings estimate in MMBtu per model developed by participating contractor at time of audit. Negative numbers represent projects with post-retrofit increase in gas consumption, typically from fuel conversions or ancillary savings. Blank cells represent data that were not required or are not currently available
"number_of_units", -- Number of units served by the Program. Blank cells represent data that were not required or are not currently available
"geocoded_column", -- Open Data/Socrata-generated geocoding information.
"orange_and_rockland", -- Utility for the project, where “True” indicates the project is in the Orange and Rockland territory. Some homes are served by more than one utility company
"nyseg", -- Utility for the project, where “True” indicates the project is in the New York State Electric and Gas territory. Some homes are served by more than one utility company
"lipa", -- Utility for the project, where “True” indicates the project is in the Long Island Power Authority (LIPA) territory. Some homes are served by more than one utility company
"central_hudson", -- Utility for the project, where “True” indicates the project is in the Central Hudson territory. Some homes are served by more than one utility company
"project_completion_date", -- Date final project completion paperwork was reviewed and approved by Program.
"project_id", -- Unique identifier for project, same as the Project ID field in the Residential Existing Homes (One to Four Units) Predicted First Year Savings for Energy Efficiency Measures: 2007 – 2012 (https://data.ny.gov/d/jtrr-tvq4) and Residential Existing Homes (One to Four Units) Energy Efficiency Projects with Income-based Incentives by Customer Type: Beginning 2010 (https://data.ny.gov/d/assk-vu73) datasets
"rochester_gas_and_electric", -- Utility for the project, where “True” indicates the project is in the Rochester Gas and Electric territory. Some homes are served by more than one utility company
"national_fuel_gas", -- Utility for the project, where “True” indicates the project is in the National Fuel Gas territory. Some homes are served by more than one utility company
"national_grid" -- Utility for the project, where “True” indicates the project is in the National Grid territory. Some homes are served by more than one utility company
FROM
"ny-gov/residential-existing-homes-one-to-four-units-5vqm-4rpf:latest"."residential_existing_homes_one_to_four_units"
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 ny-gov/residential-existing-homes-one-to-four-units-5vqm-4rpf 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 ny-gov/residential-existing-homes-one-to-four-units-5vqm-4rpf: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 ny-gov/residential-existing-homes-one-to-four-units-5vqm-4rpfCheckout 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 ny-gov/residential-existing-homes-one-to-four-units-5vqm-4rpf:latestThis will download all the objects for the latest tag of ny-gov/residential-existing-homes-one-to-four-units-5vqm-4rpf 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 ny-gov/residential-existing-homes-one-to-four-units-5vqm-4rpf: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 ny-gov/residential-existing-homes-one-to-four-units-5vqm-4rpf: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, ny-gov/residential-existing-homes-one-to-four-units-5vqm-4rpf is just another Postgres schema.