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 wdfwsalmonid_population_indicators_spi_populations table in this repository, by referencing it like:
"wa-gov/wdfwsalmonid-population-indicators-spi-populations-ncqh-ypvf:latest"."wdfwsalmonid_population_indicators_spi_populations"or in a full query, like:
SELECT
":id", -- Socrata column ID
"stock_spawning_distribution", -- Detailed free form description of the geographic spawning distribution for a stock. Typically includes both names of rivers or tributaries and river mile details, including the location of prominent landmarks. This information, which originates from individual stock reports, is used for stock summary display purposes in Salmonscape.
"local_biologist_name", -- Contact name for the local biologist for this stock.
"hatchery_standards_met",
"stock_genetic_analysis_desc", -- Detailed free form description of any and all genetic testing that has been performed in an effort to confirm the ancestral lineage of a stock. When possible, all stocks against which the focal stock was compared should be noted and a citation given for the formal report describing the outcomes. This information, which originates from individual stock reports, is used for stock summary display purposes in Salmonscape.
"recovery_plan", -- Link to the recovery plan managed by each salmon recovery region.
"species", -- Common name for the species.
"esa_listing_date", -- ESA listing date
"esa_federal_status", -- ESA listing status
"stock_origin", -- Detailed free form description of the origin (e.g., native, mixed, unknown) and production type (e.g., composite, wild, unknown) associated with a stock. If foreign (i.e., not locally adapted) broodstock has been used for harvest augmentation or supplementation the source of this broodstock and the duration of its influence should be noted. This information, which originates from individual stock reports, is used for stock summary display purposes in Salmonscape.
"stock_definition", -- Detailed free form description of the basis used to determine that a stock is, in fact, a ‘predominately reproductively isolated’ population i.e., stock. This description may include information on spawning distribution, run timing, genetic testing, morphological variation, etc. This information, which originates from individual stock reports, is used for stock summary display purposes in Salmonscape.
"last_update",
"stock_number", -- 4 digit numeric designation for each individual stock.
"population_goal_notes",
"recovery_plan_year", -- Year of the recovery plan.
"population_goals",
"population_goal_refs",
"salmon_recovery_region", -- Salmon recovery region as defined by the Governor's Salmon Recovery Office.
"stock_data_desc", -- Detailed free form description of the data collection and analysis methods used to generate all stock monitoring estimates for the stock since the historic beginning of data collection. For example, if unexpanded index redd counts were made from 1979-1999 along river miles 0.5-2.6, after which the marked redd census method was used for the entire spawning area (river miles 0.2-3.9) and expanded by 2.5 to account for males (excluding jacks), a description of both methods would be included. This information, which originates from individual stock reports, is used for stock summary display purposes in Salmonscape.
"major_population_group", -- A group of salmon populations that are geographically and genetically cohesive. The MPG is a level of organization between demographically independent populations and the ESU or DPS.
"esa_unit_name", -- Unit of organization that is used for listing and delisting species under the Endangered Species Act (ESA)
"local_biologist_email", -- Contact email for the local biologist for this stock.
"population_name" -- A group of fish that return to spawn in a given area at the same time and that are, for the most part, reproductively isolated from other such groups. A stock may include several local spawning populations that do not demonstrate sufficient reproductive isolation from one another.
FROM
"wa-gov/wdfwsalmonid-population-indicators-spi-populations-ncqh-ypvf:latest"."wdfwsalmonid_population_indicators_spi_populations"
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 wa-gov/wdfwsalmonid-population-indicators-spi-populations-ncqh-ypvf 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 wa-gov/wdfwsalmonid-population-indicators-spi-populations-ncqh-ypvf: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 wa-gov/wdfwsalmonid-population-indicators-spi-populations-ncqh-ypvfCheckout 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 wa-gov/wdfwsalmonid-population-indicators-spi-populations-ncqh-ypvf:latestThis will download all the objects for the latest tag of wa-gov/wdfwsalmonid-population-indicators-spi-populations-ncqh-ypvf 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 wa-gov/wdfwsalmonid-population-indicators-spi-populations-ncqh-ypvf: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 wa-gov/wdfwsalmonid-population-indicators-spi-populations-ncqh-ypvf: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, wa-gov/wdfwsalmonid-population-indicators-spi-populations-ncqh-ypvf is just another Postgres schema.