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 largescale_renewable_projects_reported_by_nyserda table in this repository, by referencing it like:
"ny-gov/largescale-renewable-projects-reported-by-nyserda-dprp-55ye:latest"."largescale_renewable_projects_reported_by_nyserda"or in a full query, like:
SELECT
":id", -- Socrata column ID
"bid_capacity_mw", -- The NYSERDA-contracted portion of the New Renewable Capacity in Megawatts. Blank cells represent data that were not applicable, required or are not currently available.
"max_annual_contract_quantity", -- The maximum annual contractual obligation of the project in Megawatt hours. Blank cells represent data that were not required or are not currently available.
"p10_annual_orec_exceedance", -- An amount of electrical energy (in MWh), such that the estimated probability in any given year that generation from the Selected Project delivered to the Delivery Point would exceed that amount is 10 percent. Applicable to Offshore Wind projects only. Blank cells represent data that were not required or are not currently available.
"bid_quantity_mwh", -- The annual production of the project in Megawatt hours that NYSERDA and the Counterparty agree constitutes performance in an Agreement. The Bid Quantity may not constitute the entire output of a project. Blank cells represent data that were not required or are not currently available.
"new_renewable_capacity_mw", -- For a new facility, the New Renewable Capacity equals the nameplate capacity of the installed equipment. For an upgraded facility, only the renewable capacity resulting from the upgrade of a facility is provided. For a single facility that was awarded multiple contracts, each for a percentage of the facility’s output under multiple RFPs, the total New Renewable Capacity for the facility is listed once to avoid over-counting. Therefore, the same facility appearing in multiple instances may show a New Renewable Capacity of zero. Blank cells represent projects with expired contracts with NYSERDA.
"contract_duration", -- Number of years of performance under the NYSERDA Agreement
"year_of_delivery_start_date", -- Date NYSERDA’s payments started or are expected to start. For projects Under Development, the dates are reflective of the current status of NYSERDA agreements and are subject to change based on project development progress. Blank cells represent data that were not required or are not currently available
"year_of_commercial_operation", -- Date declared as the Commercial Operation date of facility by NYISO or local utility. Blank cells represent data that were not required or are not currently available.
"project_status", -- The phase/status of which the project is in as of the Data Through Date; either Cancelled, Completed, Under Development, or Operational. A status of Cancelled means that NYSERDA's award or contract with the counterparty was cancelled or terminated. A status of Completed means the project has fulfilled their contractual obligation to NYSERDA. A status of Under Development means that the project has not yet entered operation. A status of Operational means that the project has entered operation.
"redc", -- Regional Economic Development Council of project. In 2011, Governor Andrew M. Cuomo established 10 Regional Councils and charged them with developing long-term strategic plans for economic growth in their regions. Blank cells indicate the project is not located in New York State.
"state_province", -- Name of US state or Canadian Province for project. Blank cells represent data that were not required or are not currently available
"county_province", -- Name of US county or Canadian Province for project. Blank cells represent data that were not required or are not currently available
"zip_code", -- ZIP code of project. Blank cells represent data that were not required or are not currently available
"interconnection_queue_number", -- This is the list for the NYISO Interconnection Queue and indicates the number on the list of transmission and generation projects seeking to join the grid. Additional information is located at: https://www.nyiso.com/interconnections. Tier 4 projects include both Resources and Transmission numbers.
"ptid", -- Indicates the NYISO Generator Point Identifier. A Point Identifier is a resource-specific numerical identifier used by the NYISO’s software systems to identify Generators and other Suppliers and only applies to Operating Projects. Blank cells represent data that were not required or are not currently available.
"nyiso_zone", -- Indicates the New York Control Area Load Zone into which the Bid Facility will interconnect or deliver. Blank cells represent data that were not required or are not currently available.
"energy_storage_energy_capacity_mwh", -- Usable installed energy storage capacity measured in alternating current (AC) power. It is equal to the total capacity measured during a complete discharge from a 100% usable state of charge, performed in accordance with the storage manufacturer’s specifications, on the commercial operation date. Blank cells represent data that were not required or are not currently available. This category does not include pumped storage.
"generation_type", -- Indicates the type of eligible generation; either Existing or New Generation. Blank cells represent data that were not required or are not currently available
"type_of_existing", -- Vintage Generation Facility (VGS). VGFs are projects that are either upgraded, returned to service, relocated or repowered, and must meet the specified requirements
"permit_process", -- The applicable federal, state or local permitting requirements that the Bid Facility is subject to. Blank cells represent data that were not required or are not currently available
"article_10_94c_case_number", -- Name of Article 10 or 94C Case Number. Additional Information is located at http://www3.dps.ny.gov/W/PSCWeb.nsf/All/1392EC6DD904BBC285257F4E005BE810?OpenDocument and https://ores.ny.gov/permit-applications. Blank cells represent data that were not required, applicable or are not currently available
"second_article_10_or_94c_case_number", -- Name of second Article 10 or 94C Case Number, if applicable. Additional Information is located at http://www3.dps.ny.gov/W/PSCWeb.nsf/All/1392EC6DD904BBC285257F4E005BE810?OpenDocument and https://ores.ny.gov/permit-applications. Blank cells represent data that were not required or are not currently available
"article_vii", -- Name of Article VII pertaining to Offshore Wind Project or Tier 4 Project
"federal_permitting_mechanism", -- The dates specific to the schedule of the Environmental Review and Permitting of Offshore Wind and Tier 4 Projects.
"transmission_capacity_hvdc_", -- Capacity of a project's new HVDC transmission line. Applicable to Tier 4 projects only. Blank cells represent data that were not required or are not currently available.
"eligibility", -- Either Maintenance, Non-Tier 1, Tier 1, Tier 2, Tier 4 or OREC. Maintenance represents existing baseline resources, Non-Tier 1 are facilities with a commercial operation date before 1/1/2015, and Tier 1 are facilities with a commercial operation date after 1/1/2015. Tier 2 facilities are from wind and non-state-owned run-of-river hydroelectric generating facilities located within New York State that entered commercial operation prior to January 1, 2015. OREC represents projects in the offshore wind program. Tier 4 represents renewable energy and transmission projects.
"energy_storage_power_capacity_mwac", -- Rating of the electric power to be delivered from an energy storage project in alternating current (AC) power. Blank cells represent data that were not required or are not currently available
"developer_name", -- Name of Developer responsible for developing the project. Blank cells represent data that were not required or are not currently available
"fixed_rec_price", -- The Fixed REC bid price of Tier 1 and Non-Tier 1 awarded projects in US dollars. Blank cells represent data that were not required or are not currently available
"solicitation_name", -- Request for Proposal (RFP) number, or Maintenance Agreement number under Renewable Portfolio Standard (RPS) or Tier 2 Maintenance under the Renewable Energy Standard (RES)
"project_name", -- Name of project. Project names with an asterisk have the same project name as projects awarded under different solicitations
"data_through_date", -- The date the dataset was refreshed
"renewable_technology", -- The type of renewable energy technology for the project currently under contract or installed; either Land Based Wind, Offshore Wind, Hydroelectric, Biomass, Biogas-LFG, Biogas-ADG, Fuel Cell, Solar, Geothermal, Maintenance Biomass, or Maintenance Hydroelectric.
"index_rec_strike_price", -- The Index REC bid price or the Index REC Conversion Offer Price of Tier 1 awarded projects in US dollars. Blank cells represent data that were not required or are not currently available
"counterparty", -- Name of the Seller in NYSERDA’s agreement. A counterparty is the other party to a NYSERDA agreement. Blank cells represent data that were not required or are not currently available
"georeference" -- Open Data/Socrata-generated geocoding information based on supplied address components.
FROM
"ny-gov/largescale-renewable-projects-reported-by-nyserda-dprp-55ye:latest"."largescale_renewable_projects_reported_by_nyserda"
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/largescale-renewable-projects-reported-by-nyserda-dprp-55ye with SQL in under 60 seconds.
This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.ny.gov. When you queryny-gov/largescale-renewable-projects-reported-by-nyserda-dprp-55ye: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.ny.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 \
"ny-gov/largescale-renewable-projects-reported-by-nyserda-dprp-55ye" \
--handler-options '{
"domain": "data.ny.gov",
"tables": {
"largescale_renewable_projects_reported_by_nyserda": "dprp-55ye"
}
}'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, ny-gov/largescale-renewable-projects-reported-by-nyserda-dprp-55ye is just another Postgres schema.