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 iowa_unemployment_compensation_fund_status_fund table in this repository, by referencing it like:
"mydata-iowa-gov/iowa-unemployment-compensation-fund-status-fund-k9rt-4t6d:latest"."iowa_unemployment_compensation_fund_status_fund"or in a full query, like:
SELECT
":id", -- Socrata column ID
"iowa_reserve_fund", -- Iowa Reserve Fund (millions) - This refers to the principal in the Unemployment Compensation Reserve Fund created in the state treasury under S. F. 458 in 2003. Monies in the reserve fund shall be used to pay benefits to the extent moneys in the unemployment compensation fund are insufficient to pay benefits during a calendar quarter.
"high_year_1", -- High Year 1 - The year with the highest benefit cost rate used to compute the Average High Benefit Cost Rate. The time period is last twenty years or three recessions (if longer).
"average_high_cost_multiple", -- Average High Cost Multiple - A trust fund solvency measure equal to the ratio of the current UI Trust Fund balance (as a percent of total contributory wages) to the Average High Cost Rate.
"average_high_benefit_cost", -- Average High Benefit Cost Rate - A measure of past high benefit costs. It is equal to the average of the three highest benefit cost rates in the last twenty years or a period including three national recessions, if longer.
"months_average_high_cost", -- Months of Benefits in Trust Fund at Three Year Average High Benefit Cost Level - The number of months of benefits in the UI Trust Fund at the historic highest three year average benefit level adjusted for covered wage growth during the last twenty years or three recessions. The U. S. Department of Labor recommends a solvency standard of 12 months.
"months_high_cost", -- Months of Benefits in Trust Fund at Highest Benefit Cost Level - The number of months of benefits in the UI Trust Fund at the historic highest benefit level adjusted for covered wage growth.
"months_current", -- Months of Benefits in Trust Fund at the Current Benefit Level - UI Trust Fund Balance divided by average monthly UI Benefits paid for each year.
"benefit_at_average_high_cost", -- Benefit at Average High Cost (millions) - The amount of benefits that would be paid out at the average of the three highest benefit cost rates in the last twenty years or a period including three national recessions, if longer (adjusted for covered wage growth).
"reserve_ratio", -- Reserve Ratio - UI Trust Fund balance as a percentage of UI covered wages for contributory employers. The fund is expressed as a percentage of covered wages in order to control for employment and wage growth. The contribution rate table formulas use 2.0 percent of covered wages as a minimum safety factor.
"ui_trust_fund", -- UI Trust Fund Balance (millions): The Unemployment Trust Fund is a fund established in the Treasury of the United States which contains all monies deposited by state agencies to the credit of their unemployment fund accounts and federal unemployment taxes (FUTA) collected by the Internal Revenue Service. In this dataset this term refers to the balance in Iowa’s individual account in the Unemployment Trust Fund which is available to pay UI benefits.
"highest_benefit_cost_rate", -- Highest Benefit Cost Rate - The highest historic reserve ratio. The current high cost rate is based in the twelve months ending April 1983 where benefits totaled $317 million and covered wages totaled $11.6 billion. Benefits paid equaled 2.75 percent of wages.
"benefit_at_highest_cost", -- Benefit at Highest Cost (millions) - The amount of benefits that would be paid out at the historic highest benefit level adjusted for covered wage growth for contributory employers. Iowa's highest benefit cost level is based on the 12-month period ending April 1983.
"high_year_3", -- High Year 3 - The year with the third highest benefit cost rate used to compute the Average High Benefit Cost Rate. The time period is last twenty years or three recessions (if longer)
"high_year_2", -- High Year 2 - The year with the second highest benefit cost rate used to compute the Average High Benefit Cost Rate. The time period is last twenty years or three recessions (if longer)
"high_cost_multiple", -- High Cost Multiple - A trust fund solvency measure equal to the ratio of the current UI Trust Fund balance (as a percent of total contributory wages) to the Highest Benefit Cost Rate.
"cpi_adjusted_tf", -- CPI Adjusted Trust Fund Balance (millions) - UI Trust Fund balance adjusted by the Consumer Price Index [CPI-U 1982-1984=100]
"year" -- Calendar Year
FROM
"mydata-iowa-gov/iowa-unemployment-compensation-fund-status-fund-k9rt-4t6d:latest"."iowa_unemployment_compensation_fund_status_fund"
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 mydata-iowa-gov/iowa-unemployment-compensation-fund-status-fund-k9rt-4t6d 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 mydata-iowa-gov/iowa-unemployment-compensation-fund-status-fund-k9rt-4t6d: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 mydata-iowa-gov/iowa-unemployment-compensation-fund-status-fund-k9rt-4t6dCheckout 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 mydata-iowa-gov/iowa-unemployment-compensation-fund-status-fund-k9rt-4t6d:latestThis will download all the objects for the latest tag of mydata-iowa-gov/iowa-unemployment-compensation-fund-status-fund-k9rt-4t6d 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 mydata-iowa-gov/iowa-unemployment-compensation-fund-status-fund-k9rt-4t6d: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 mydata-iowa-gov/iowa-unemployment-compensation-fund-status-fund-k9rt-4t6d: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, mydata-iowa-gov/iowa-unemployment-compensation-fund-status-fund-k9rt-4t6d is just another Postgres schema.