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 wdfw_salmonid_population_indicators_database_spi table in this repository, by referencing it like:
"wa-gov/wdfw-salmonid-population-indicators-database-spi-x25s-cxg8:latest"."wdfw_salmonid_population_indicators_database_spi"or in a full query, like:
SELECT
":id", -- Socrata column ID
"compilerrecordid", -- Agency record ID maintained by the data submitter.
"contactemail", -- Email address of person who is the best contact for questions that may arise about this data record.
"otherdatasources", -- Identifies additional organizations that provided data or expertise to calculate the indicator(s), metric(s), or age distribution for this record.
"methodadjustments", -- Minor adjustments to a method in a given year that are not described in the method citations above but are important.
"age5prop", -- The proportion of natural origin fish that were age 5 (brood year +5).
"age4proplowerlimit", -- The lower limit of the confidence interval for the Age4Prop field.
"age3prop", -- The proportion of natural origin fish that were age 3 (brood year +3).
"phosejlowerlimit", -- The lower limit of the confidence interval for the pHOSej field.
"nosaejupperlimit", -- The upper limit of the confidence interval for the NOSAEJ field.
"waterbody", -- For NOSA estimates (when EstimateType = "NOSA"), the name of the body of water associated with the time series.
"popfitnotes", -- Text description of how well the NOSA/escapement value corresponds to the defined population, and why the data are not at the scale of a single population.
"age8prop", -- The proportion of natural origin fish that were age 8 (brood year +8).
"age7prop", -- The proportion of natural origin fish that were age 7 (brood year +7).
"phosej", -- Point estimate for the proportion of fish spawning naturally, excluding jacks, that are hatchery origin fish.
"nosaij", -- The point estimate for NOSA or natural origin escapement, including jacks.
"age7propupperlimit", -- The upper limit of the confidence interval for the Age7Prop field.
"age7proplowerlimit", -- The lower limit of the confidence interval for the Age7Prop field.
"age6prop", -- The proportion of natural origin fish that were age 6 (brood year +6).
"age5propupperlimit", -- The upper limit of the confidence interval for the Age5Prop field.
"nosaijupperlimit", -- The upper limit of the confidence interval for the NOSAIJ field.
"escapementlong", -- For escapement estimates, longitude of the location specified in the WaterBody field in decimal degrees (not degrees-minutes-seconds). Calculated using NAD83/WGS84 datum.
"age6propupperlimit", -- The upper limit of the confidence interval for the Age6Prop field.
"age3proplowerlimit", -- The lower limit of the confidence interval for the Age3Prop field.
"methodnumber", -- This field represents the method(s) used to calculate the values in the "Indicators" and "Metrics" sections.
"esu_dps", -- For populations listed under the federal ESA, this is the name of a defined Evolutionarily Significant Unit (ESU) or Distinct Population Segment (DPS) as defined by NMFS Northwest Region or by USFWS. For non-listed populations this is the DPS or other name.
"nosaejlowerlimit", -- The lower limit of the confidence interval for the NOSAEJ field.
"majorpopgroup", -- Name of "major population group" (MPG) or “stratum†as defined by the NMFS Northwest Region, in which the population falls.
"age4prop", -- The proportion of natural origin fish that were age 4 (brood year +4).
"phosejupperlimit", -- The upper limit of the confidence interval for the pHOSej field.
"nosaej", -- The point estimate for NOSA or natural origin escapement, excluding jacks.
"id", -- Value used by computer to identify a record.
"popid", -- Code for the population(s) of fish represented by this record.
"tsaejlowerlimit", -- The lower limit of the confidence interval for the TSAEJ field.
"tsaij", -- The point estimate for total spawner abundance, including jacks
"tsaej", -- The point estimate for total spawner abundance, excluding jacks
"tsaijupperlimit", -- The upper limit of the confidence interval for the TSAIJ field.
"age8propupperlimit", -- The upper limit of the confidence interval for the Age8Prop field.
"age2prop", -- The proportion of natural origin fish that were age 2 (brood year +2).
"age4propupperlimit", -- The upper limit of the confidence interval for the Age4Prop field.
"agepropalpha", -- The significance level for the Age_x_Prop confidence intervals, expressed as alpha.
"age2propupperlimit", -- The upper limit of the confidence interval for the Age2Prop field.
"run", -- Run of fish.
"escapementtiming", -- Specific time period for an escapement estimate, in terms of months of the year. May be the start and end months of sampling, or the first and last months of fish observations.
"bestvalue", -- A declaration of whether the ContactAgency considers this record to be their approved best estimate for this combination of PopID and SpawningYear.
"tsaijalpha", -- The significance level for the TSAIJ confidence interval, expressed as alpha.
"tsaijlowerlimit", -- The lower limit of the confidence interval for the TSAIJ field.
"nobroodstockremoved", -- When EstimateType = "NOSA", this field is the number of natural origin fish (adults plus jacks) that were prevented from participating in natural spawning because they were taken for use as hatchery broodstock.
"recoverydomain", -- Name of the "recovery domain," as defined by the NMFS Northwest Region, in which the population falls geographically.
"upddate", -- The date and time that the record was created or updated.
"popfit", -- Categorization of how well the geographic extent of the NOSA/escapement estimate corresponds to the geographic definition of the population.
"phosejalpha", -- The significance level for the pHOSej confidence interval, expressed as alpha.
"nosjfupperlimit", -- The upper limit of the confidence interval for the NOSJF field.
"comments", -- Any issues, problems, questions about this indicator that were not already captured in other places.
"age3propupperlimit", -- The upper limit of the confidence interval for the Age3Prop field.
"phosij", -- Point estimate for the proportion of fish spawning naturally, including jacks, that are hatchery origin fish.
"spawningyear", -- The four-digit year in which spawning of this species (and run where appropriate) began.
"contactpersonfirst", -- First name of person who is the best contact for questions that may arise about this data record.
"tsaejupperlimit", -- The upper limit of the confidence interval for the TSAEJ field.
"phosijupperlimit", -- The upper limit of the confidence interval for the pHOSij field.
"publish", -- Yes/no value indicating whether this record should be shared freely with all public users via the StreamNet CAX.
"dataentry", -- Compiler name.
"metacomments", -- Comments regarding the supporting information.
"metriclocation", -- Where the supporting metrics are maintained at the source.
"indicatorlocation", -- Where this indicator is maintained at the source.
"nullrecord", -- This field is used to indicate that indicator values do not exist because the data do not exist to calculate them.
"protmethdocumentation", -- Citation or documentation that describes the protocol and/or methods used.
"age11pluspropupperlimit", -- The upper limit of the confidence interval for the Age11PlusProp field.
"age11plusproplowerlimit", -- The lower limit of the confidence interval for the Age11PlusProp field.
"age11plusprop", -- The proportion of natural origin fish that were age 11 (brood year +11) or older
"age10propupperlimit", -- The upper limit of the confidence interval for the Age10Prop field.
"age10prop", -- The proportion of natural origin fish that were age 10 (brood year +10).
"age9proplowerlimit", -- The lower limit of the confidence interval for the Age9Prop field.
"age2proplowerlimit", -- The lower limit of the confidence interval for the Age2Prop field.
"hosjf", -- The point estimate for the hatchery origin spawners jack fraction.
"nosjf", -- The point estimate for the natural origin spawners jack fraction.
"nosaijalpha", -- The significance level for the NOSAIJ confidence interval, expressed as alpha.
"commonpopname", -- Population name used by local biologists.
"refid", -- The unique StreamNet reference ID number that identifies the source document or database from which the record was obtained.
"contactpersonlast", -- Last name of person who is the best contact for questions that may arise about this data record.
"phosijlowerlimit", -- The lower limit of the confidence interval for the pHOSij field.
"nosaijlowerlimit", -- The lower limit of the confidence interval for the NOSAIJ field.
"contactagency", -- Agency, tribe, or other entity, or person responsible for these data that is the best contact for questions that may arise about this data record.
"escapementlat", -- For escapement estimates, latitude of the location specified in the WaterBody field in decimal degrees (not degrees-minutes-seconds). Calculated using NAD83/WGS84 datum.
"contactphone", -- Phone number of person who is the best contact for questions that may arise about this data record.
"age6proplowerlimit", -- The lower limit of the confidence interval for the Age6Prop field.
"age5proplowerlimit", -- The lower limit of the confidence interval for the Age5Prop field.
"submitagency", -- Initials or acronym for the agency, tribe, or other entity, or name of person, that sent this record of data to the exchange network node at StreamNet.
"phosijalpha", -- The significance level for the pHOSij confidence interval, expressed as alpha.
"nosaejalpha", -- The significance level for the NOSAEJ confidence interval, expressed as alpha.
"age9prop", -- The proportion of natural origin fish that were age 9 (brood year +9).
"nosjfalpha", -- The significance level for the NOSJF confidence interval, expressed as alpha.
"age9propupperlimit", -- The upper limit of the confidence interval for the Age9Prop field.
"estimatetype", -- Whether the values in the NOSAIJ / NOSAEJ fields are classified as spawner abundance or escapement.
"dataentrynotes", -- Notes about this record by the compiler identified in the DataEntry field.
"measurelocation", -- Where the measurements are maintained that were used for these calculations.
"datastatus", -- Status of the data in the current record
"age10proplowerlimit", -- The lower limit of the confidence interval for the Age10Prop field.
"tsaejalpha", -- The significance level for the TSAEJ confidence interval, expressed as alpha.
"nosjflowerlimit", -- The lower limit of the confidence interval for the NOSJF field.
"protmethurl", -- URL(s) for published protocols and methods describing the methodology and documenting the derivation of the indicator.
"age8proplowerlimit", -- The lower limit of the confidence interval for the Age8Prop field.
"commonname" -- Common name of the taxon of fish.
FROM
"wa-gov/wdfw-salmonid-population-indicators-database-spi-x25s-cxg8:latest"."wdfw_salmonid_population_indicators_database_spi"
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/wdfw-salmonid-population-indicators-database-spi-x25s-cxg8 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/wdfw-salmonid-population-indicators-database-spi-x25s-cxg8: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/wdfw-salmonid-population-indicators-database-spi-x25s-cxg8Checkout 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/wdfw-salmonid-population-indicators-database-spi-x25s-cxg8:latestThis will download all the objects for the latest tag of wa-gov/wdfw-salmonid-population-indicators-database-spi-x25s-cxg8 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/wdfw-salmonid-population-indicators-database-spi-x25s-cxg8: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/wdfw-salmonid-population-indicators-database-spi-x25s-cxg8: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/wdfw-salmonid-population-indicators-database-spi-x25s-cxg8 is just another Postgres schema.