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 lacers_key_facts_and_figures_from_year_end table in this repository, by referencing it like:
"lacity/lacers-key-facts-and-figures-from-year-end-63we-p3wv:latest"."lacers_key_facts_and_figures_from_year_end"or in a full query, like:
SELECT
":id", -- Socrata column ID
"average_beneficiary_age", -- The average age of beneficiaries currently receiving benefits from LACERS. Not included in 1991-1992 CAFRs.
"average_service_retiree_age", -- The average age of service retirees currently receiving benefits from LACERS. Missing 1991-1992
"beneficiaries", -- Survivors/Beneficiaries of deceased retirees who receive benefits from LACERS plan. This is not broken out in CAFRs before early 2000's in a way that's easily reported on.
"disability_retirees", -- Number of retired Members who retired due to a disability and received disability retirement benefits. This is not broken out in CAFRs before early 2000's in a way that's easily reported on.
"service_retirees", -- Number of retired Members who receive service retirement benefits. This is not broken out in CAFRs before early 2000's in a way that's easily reported on.
"actuarially_projected_annual_payroll", -- Actuary's projected covered payroll to calculate Employer's annual contribution toward LACERS Plan. Not reported on in CAFRs from 90s; some uncertainty with reporting because of confusion with Covered Payroll at times.
"covered_payroll", -- Pensionable wages and salaries which are the base to calculate City's contributions to LACRES Plans
"inactive_members_all_tiers", -- City employees who hold status but are not currently working or collecting a pension from the City
"inactive_tier_1", -- City employees who hold Tier 1 status but are not currently working or collecting a pension from the City
"active_members_all_tiers", -- Full-Time and certified Part-Time civilian employees currently working for the City of Los Angeles
"active_tier_3", -- Tier 3 was created in 2016 for all civilian City employees subsequently hired. Did not exist until 2016.
"active_tier_1", -- Tier 1 refers to any and all civilian City employees hired prior to the establishment of Tier 3 in 2016
"maximum_monthly_retiree_healthcare_subsidy_under_65_non_medicar", -- Maximum monthly healthcare subsidy for eligible survivors who are not yet eligible for Medicare. Provided by HBACD, not a metric included in reports.
"monthly_average_health_subsidy_under_65_retiree", -- Average monthly healthcare subsidy amount for retirees who are not yet eligible for Medicare. Provided by HBACD, not a metric included in reports.
"monthly_average_health_subsidy_over_65", -- Average monthly healthcare subsidy amount for retirees who are eligible for Medicare part A & B. Provided by HBACD, not a metric included in reports.
"annual_retirement_benefits", -- Annual Retirement Benefits paid to the retirees and their beneficiaries including Larger Annuity and FDBP, excluding Healthcare Benefits
"monthly_retirement_allowance_disability", -- The average monthly retirement amount for disability retirees. Not consistently reported in CAFRs; dependent on which Demographics charts were brought over from other reports.
"monthly_retirement_allowance_service_retiree", -- The average monthly retirement benefits amount for service retirees. Not consistently reported in CAFRs; dependent on which Demographics charts made it over from other reports
"yr10_return_net", -- Ten year average of investment returns including management fees paid
"yr10_return_gross_", -- Ten year average of investment returns before management fees paid
"yr5_return_net", -- Five year average of investment returns including management fees paid
"yr5_return_gross", -- Five year average of investment returns before management fees paid
"yr3_return_net", -- Three year average of investment returns including management fees paid
"yr_return_net", -- Investment return after management fees paid. Net of Fees 'missing' from reporting 2002 and earlier, time-weighted; applies to all dependent calculations.
"yr_return_gross", -- Investment return before management fees paid. Time-weighted; applies to all dependent calculations.
"net_investment_income_loss_total_lacers_plan", -- Net Investment Income or Loss (including unrealized gain and loss) associated with the combined Retirement and Healthcare Plans. Not specifically reported in CAFR until 1997; can be found in Actuarial Valuation tables that did not make it into early CAFRs.
"healthcare_investment_net", -- Net Investment Income or Loss (including unrealized gain and loss) associated with the Postemployment Healthcare Plan Fund. Not specifically reported in CAFR until 1997; can be found in Actuarial Valuation tables that did not make it into early CAFRs.
"member_contributions_retirement", -- Mandatory and voluntary Retirement Contributions made by active LACERS members
"actual_employer_health_plan_contribution_rate_combined", -- The actual rate of contribution made by the employer toward the health plan.
"actual_employer_retirement_plan_contribution_rate_combined", -- The actual contribution rate of the employer toward the retirement plan.
"city_contributions_total", -- City's annual contributions for LACERS Retirement and Healthcare Plans
"city_contributions_healthcare", -- Contributions made by the City toward the Postemployment Healthcare Plan
"city_contributions_retirement", -- Contributions made by the City toward the Retirement Plan
"funded_ratio_total", -- Ratio of combined Retirement and Postemployment Healthcare Plans based on Market Value of assets to liabilities
"funded_ratio_retirement_plan", -- Ratio of Retirement Plan based on Market Value assets to liabilities
"total_uaal", -- UAAL: Actuarial Value of Assets minus Actuarial Accrued Liability
"healthcare_uaal", -- UAAL: Actuarial Value of Assets minus Actuarial Accrued Liability, NOL: Total OPEB Liability minus Plan Fiduciary Net Position based on Market Value
"retirement_uaal", -- UAAL: Actuarial Value of Assets minus Actuarial Accrued Liability, NPL: Total Pension Liability minus Plan Fiduciary Net Position based on Market Value
"fnp_total", -- The total Fiduciary Net Position of LACERS Plan Funds
"family_death_benefit_plan", -- The Fiduciary Net Position of the FDBP reserves which are collected and managed by LACERS. This information can be found in the Notes to the Basic Financial Statements section of the annual financial report.
"fnp_retirement_plan", -- Fiduciary Net Position of the Retirement Plan Fund
"monthly_retirement_allowance_beneficiaries", -- The average monthly retirement benefit amount for beneficiaries of retired members. Not consistently reported in CAFRs; dependent on which Demographics charts were brought over from other reports.
"monthly_retirement_allowance_all", -- The average monthly retirement benefit amount for all LACERS retirees. Not consistently reported in CAFRs; dependent on which Demographics charts were brought over from other reports.
"funded_ratio_healthcare_plan", -- Ratio of Healthcare Plan based on Market Value of assets to liabilities
"fiscal_year", -- Fiscal Year ends on June 30th of the given year, and begins on July 1st of the previous calendar year.
"monthly_average_health_subsidy_under_65_survivor", -- Average monthly healthcare subsidy for eligible survivors who are not yet eligible for Medicare. Provided by HBACD, not a metric included in reports.
"inactive_tier_2", -- City employees who held Tier 2 status but are not currently working or collecting a pension from the City. As of 2016, these members were transferred into Tier 1. Did not exist before 2014.
"retirement_investment_net", -- Net Investment Income or Loss (including unrealized gain and loss) associated with Retirement Plan Fund. Not specifically reported in CAFR until 1997; can be found in Actuarial Valuation tables that did not make it into early CAFRs.
"fnp_healthcare_plan", -- Fiduciary Net Position of the Postemployment Healthcare Plan Fund
"average_retirement_age", -- The average age of City employees who retired during the fiscal year. Less detailed demographics in CAFR until early 2000s.
"inactive_tier_3", -- City employees who hold Tier 3 status but are not currently working or collecting a pension from the City. Did not exist before 2016.
"yr15_return_gross", -- Fifteen year average of investment returns before management fees paid
"yr3_return_gross", -- Three year average of investment returns before management fees paid
"member_contributions_healthcare", -- LACERS members do not make contributions toward the Postemployment Healthcare Plan
"active_tier_2", -- Tier 2 was created in 2014 and employees were transferred into Tier 1 upon the creation of Tier 3 in 2016. Did not exist until 2014. Transferred to Tier 1 in 2016
"annual_healthcare_benefits", -- Annual Healthcare Benefits paid to the retirees and their eligible dependents
"maximum_monthly_survivor_healthcare_subsidy_under_65_non_medica", -- Maximum monthly healthcare subsidy for retirees who are not yet eligible for Medicare. Provided by HBACD, not a metric included in reports.
"yr15_return_net" -- Fifteen year average of investment returns including management fees paid
FROM
"lacity/lacers-key-facts-and-figures-from-year-end-63we-p3wv:latest"."lacers_key_facts_and_figures_from_year_end"
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 lacity/lacers-key-facts-and-figures-from-year-end-63we-p3wv 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 lacity/lacers-key-facts-and-figures-from-year-end-63we-p3wv: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 lacity/lacers-key-facts-and-figures-from-year-end-63we-p3wvCheckout 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 lacity/lacers-key-facts-and-figures-from-year-end-63we-p3wv:latestThis will download all the objects for the latest tag of lacity/lacers-key-facts-and-figures-from-year-end-63we-p3wv 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 lacity/lacers-key-facts-and-figures-from-year-end-63we-p3wv: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 lacity/lacers-key-facts-and-figures-from-year-end-63we-p3wv: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, lacity/lacers-key-facts-and-figures-from-year-end-63we-p3wv is just another Postgres schema.