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 governors_executive_budget_program_measures_sfy table in this repository, by referencing it like:
"pa-gov/governors-executive-budget-program-measures-sfy-c6gj-gj2q:latest"."governors_executive_budget_program_measures_sfy"or in a full query, like:
SELECT
":id", -- Socrata column ID
"number_of_children_served", -- Number of children served (funded slots) in evidence-based home visiting programs
"percentage_of_children_in", -- Percentage of children in subsidized child care enrolled in Keystone STARS Level 3 or 4 facilities
"total_children_served_in", -- Total children served in subsidized child care during the last month of the fiscal year
"persons_receiving_homeless", -- Persons receiving homeless services
"number_of_long_term_living_1", -- Number of long-term living recipients receiving home and community-based waiver services (Waiver and Act 150) (monthly average)
"average_number_of_days_to_3", -- Average number of days to process a child abuse clearance
"emergency_room_utilization", -- Emergency Room utilization per 1,000 member-months
"percentage_of_children_not", -- Percentage of children not returning to care within 12 months of discharge to parents or primary caregivers
"community_based_placements", -- Community Based Placements
"annual_number_of_children", -- Annual number of children receiving child welfare services at home (unduplicated)
"community_healthchoices", -- Community HealthChoices - Recipients receiving institutional care (monthly average)
"youth_served", -- Youth served
"person_family_directed", -- Person/Family Directed Supports Waiver services
"number_of_persons_receiving_3", -- Number of persons receiving Intellectual Disability services with competitive, integrated employment
"persons_receiving_intellectual", -- Persons receiving intellectual disability services during fiscal year: Home and Community Services (Waiver and Base services) (unduplicated)
"percentage_of_adults", -- Percentage of adults readmitted to state hospitals within 180 days of discharge
"total_persons_served_in_state", -- Total persons served in state hospitals
"percentage_of_long_term_living", -- Percentage of long-term living recipients receiving institutional care (monthly average)
"cost_per_trip", -- Cost per trip
"budget_book_budget_year", -- This is the State Fiscal Year when the program measure numbers in this row were submitted for the Governor's Executive Budget Book.
"number_of_persons_receiving", -- Number of persons receiving autism services
"tanf_recipients_obtaining", -- Temporary Assistance for Needy Families - recipients obtaining employment
"number_of_persons_receiving_2", -- Number of persons receiving Intellectual Disability services with an employment goal and receiving employment services
"utilization_rate_of_behavioral", -- Utilization rate of Behavioral Health services
"other_community_residential", -- Other: Community residential programs
"legal_service_clients", -- Legal service clients
"number_of_individuals_on", -- Number of individuals on Medical Assistance with a substance use disorder
"persons_receiving_mental", -- Persons receiving mental health inpatient and outpatient services from non-Medical Assistance funding (unduplicated)
"tanf_recipients_enrolled", -- Temporary Assistance for Needy Families - recipients enrolled in employment and training programs (monthly average)
"persons_receiving_state", -- Persons receiving State Supplemental Grants (monthly average)
"persons_with_developmental", -- Persons with developmental disabilities served in the OBRA Waiver (monthly average)
"chip_enrolled_children_ages_1", -- CHIP(Children's Health Insurance Program) -enrolled children ages <1 to 19 with an acute inpatient discharge (discharges per 1,000 member-months)
"percentage_of_persons_ages", -- Percentage of persons ages 2 to 20 receiving at least one dental visit during the measurement year
"newly_medicaid_eligible_adults", -- Newly Medicaid-eligible adults receiving enhanced federal match under the AFFORDABLE CARE ACT(monthly average)
"persons_participating_in_2", -- Persons participating in Medical Assistance (monthly average) – Children (under age 21)
"percentage_of_chip_enrolled_2", -- Percentage of CHIP(Children's Health Insurance Program) -enrolled children ages 2 to 19 receiving an annual dental visit
"percentage_of_chip_enrolled_1", -- Percentage of CHIP(Children's Health Insurance Program) -enrolled children ages 3 to 19 who received a well-child visit with a primary care physician
"percentage_of_long_term_living_1", -- Percentage of long-term living recipients receiving services in the community (monthly average)
"persons_receiving_supplemental", -- Persons receiving SNAP Supplemental Nutrition Assistance Program benefits (monthly average)
"percentage_of_chip_enrolled", -- Percentage of CHIP(Children's Health Insurance Program)-enrolled two-year-olds with one or more capillary or venous blood tests for lead prior to their second birthday
"percentage_of_patients", -- Percentage of patients discharged from a psychiatric inpatient setting who have follow-up service 7 days from discharge (ages 21 to 64)
"percentage_of_persons_in", -- Percentage of persons in state hospitals with stays longer than two years
"persons_receiving_consolidated", -- Persons receiving Consolidated Waiver services during fiscal year: Home and Community Services (unduplicated)
"persons_receiving_person", -- Persons receiving Person/Family Directed Supports Waiver services during fiscal year: Home and Community Services (unduplicated)
"private_intermediate_care", -- Private intermediate care facilities for persons with intellectual disabilities (ICFs/ID specialize in caring for people with intellectual disabilities with the goal to assist each individual to become as independent as possible. Ranging in age from children to elderly, each resident is taught life skills and receives additional training, encouraging them to reach their highest potential.)
"autism_services", -- Autism services
"group_homes_community", -- Group Homes: Community residential programs
"foster_care_community", -- Foster care: Community residential programs
"annual_recipients_of_in_state", -- Annual recipients of in-state institutional care programs (unduplicated)
"percentage_of_children", -- Percentage of children reunited with parents or primary caregiver within 12 months of placement
"children_reaching_permanency", -- Children reaching permanency outside of adoption
"rape_crisis_sexual_assault", -- Rape crisis/sexual assault victims served
"children_participating_in", -- Children participating in Early Intervention (EI) services
"online_applications_submitted", -- Submitting online applications through COMPASS
"percentage_of_youth_in_work", -- Percentage of youth in work experience
"state_centers", -- State centers
"average_cost_per_person_in", -- Average cost per person in state hospital population
"persons_receiving_cash", -- Persons receiving cash assistance (monthly average)
"households_receiving_energy_1", -- Households receiving energy crisis payments
"recipients_receiving_services", -- Recipients receiving services through the LIFE program (monthly average)
"community_healthchoices_1", -- Community HealthChoices - Recipients receiving home and community-based waiver services (monthly average)
"number_of_long_term_living", -- Number of long-term living recipients receiving institutional care (monthly average)
"children_s_health_insurance", -- Children's Health Insurance Program (CHIP) enrollment (monthly average)
"one_way_trips_in_thousands", -- One-way trips (in thousands)
"average_number_of_days_to_2", -- Average number of days to process an application - Supplemental Nutrition Assistance Program
"percentage_of_all_applications", -- Percentage of submitted applications through COMPASS
"recipients_enrolled_in_program", -- Recipients enrolled in program (monthly average)
"persons_receiving_services", -- Persons receiving services through Medical Assistance expenditures (fee-for-service and HealthChoices) (unduplicated)
"persons_participating_in_1", -- Persons participating in Medical Assistance (monthly average) – Adults (ages 21 and older)
"number_of_regulated_facilities", -- Number of regulated facilities
"number_of_people_moving_from", -- Number of people moving from private ICFs/ID and state centers into the community
"investigations_of_reported", -- Investigations of reported child abuse
"chip_enrolled_children_ages", -- CHIP(Children's Health Insurance Program) -enrolled children ages <1 to 19 utilizing an emergency department (visits per 1,000 member-months)
"breast_cancer_screening", -- Breast cancer screening clients
"number_of_child_abuse", -- Number of child abuse clearances processed
"number_of_individuals_who", -- Number of individuals who reside in a private home (not in a provider-controlled setting)
"consolidated_waiver_services", -- Consolidated Waiver services
"finalized_adoptions", -- Finalized adoptions
"persons_receiving_community", -- Persons receiving Community Living Waiver services during fiscal year: Home and Community Services (Unduplicated)
"number_of_persons_receiving_1", -- Number of persons receiving intellectual disability services
"percentage_of_patients_1", -- Percentage of patients discharged from a substance use disorder hospital setting who have follow-up services 7 days from discharge (ages 21 to 64)
"percentage_of_regulated", -- Percentage of regulated facilities at Keystone STARS Level 3 or 4
"domestic_violence_victims", -- Domestic violence victims served
"percentage_of_child_abuse", -- Percentage of child abuse investigations substantiated
"children_in_out_of_state", -- Children in out-of-state programs
"community_living_waiver", -- Community Living Waiver services
"persons_receiving_base", -- Persons receiving Base services during fiscal year: Home and Community Services (unduplicated)
"persons_receiving_methadone", -- Persons receiving methadone maintenance services
"percentage_of_persons_ages_1", -- Percentage of persons ages 18 to 75 with diabetes (type 1 and type 2) who had Hemoglobin A1c poor control (>9%) (Note: a lower rate equals better performance)
"non_hospital_detoxification", -- Non-hospital detoxification and rehabilitation clients
"households_receiving_energy", -- Households receiving energy cash payments
"percentage_of_children_from", -- Percentage of children from birth to 15 months visiting a physician 6 or more times in the past year
"inpatient_utilization_total", -- Inpatient utilization (total discharges per 1,000 member-months)
"persons_participating_in", -- Persons participating in Medical Assistance (monthly average)
"average_number_of_days_to_1", -- Average number of days to process an application - Temporary Assistance for Needy Families
"average_number_of_days_to", -- Average number of days to process an application - MA/NH/Waiver
"percentage_of_all_compass", -- Percentage of mobile application users on COMPASS
"fiscal_year", -- State Fiscal Year runs from July 1 - June 30
"persons_receiving_services_1" -- Persons receiving services through Medical Assistance expenditures
FROM
"pa-gov/governors-executive-budget-program-measures-sfy-c6gj-gj2q:latest"."governors_executive_budget_program_measures_sfy"
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 pa-gov/governors-executive-budget-program-measures-sfy-c6gj-gj2q 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 pa-gov/governors-executive-budget-program-measures-sfy-c6gj-gj2q: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 pa-gov/governors-executive-budget-program-measures-sfy-c6gj-gj2qCheckout 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 pa-gov/governors-executive-budget-program-measures-sfy-c6gj-gj2q:latestThis will download all the objects for the latest tag of pa-gov/governors-executive-budget-program-measures-sfy-c6gj-gj2q 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 pa-gov/governors-executive-budget-program-measures-sfy-c6gj-gj2q: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 pa-gov/governors-executive-budget-program-measures-sfy-c6gj-gj2q: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, pa-gov/governors-executive-budget-program-measures-sfy-c6gj-gj2q is just another Postgres schema.