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 office_for_the_aging_service_expenditures table in this repository, by referencing it like:
"ny-gov/office-for-the-aging-service-expenditures-7sw8-sdsd:latest"."office_for_the_aging_service_expenditures"or in a full query, like:
SELECT
":id", -- Socrata column ID
"date_federal_fiscal_year", -- Federal fiscal year during which the expenditures were incurred.
"caregiver_services", -- Services included are support groups for caregivers, caregiver counseling on issues to help caregivers in making decisions and solving problems related to their care giving roles and training workshops for caregivers.
"personal_emergency_response", -- A service which utilizes an electronic device to alert appropriate people of the need for immediate assistance in the event of an emergency situation in an older person's home.
"long_term_care_ombudsman_program", -- Services provided by duly authorized patient advoca tes on behalf of people residing in long term care facilities and their families.
"senior_center_recreation_and_education", -- Activities organized and scheduled through the Area Agincy on Aging or its subcontractors that involve older persons in courses, workshops, other learning activities and satisfying use of free time.
"in_home_services", -- A service that provides assistance with the tasks that are the same or similar to those included in the definitions of Personal Care Level I and Personal Care Level II, and which is managed by the consumer, or a representative selected by the consumer.
"ourtreach", -- The unit of service definition for Outreach has been expanded to allow the AAA or its subcontractors to count as contacts meetings held at a table at a health event with an individual. For example, providers might conduct face-to-face identification of issues.
"other", -- Other services provided that may include services and activities designed to provide support to older people who are isolated because of physical and/or cognitive limitations. These services are not defined separately elsewhere.
"information_assistance", -- Provides individuals with information on services available through the AAA and within the communities or assists individuals by linking them to services that are available within the communities.
"nutrition_education", -- Each participant of an individual or group session receives one unit of service. In addition, each distribution of handouts to congregate and/or homebound participants (not counted if handouts are part of a presentation or are left on display to be picked (note: this is not the same service as "nutrition counselling").
"legal_assistance", -- Provision of legal advice, counseling and representation by an attorney or other person acting under the supervision of an attorney.
"transportation", -- Transportation is an optional component under SADS and therefore is to be reported separately (units, expenditure s and clients) rather than included in the reporting under adult day services.
"assisted_transportation", -- Assistance and transportation, including escort, to a person who has difficulties (physical or cognitive) using regular vehicular transportation.
"nutrition_counseling", -- A service that provides individualized one-on-one servcie with guidance to individuals who are at nutritional risk because of their health or nutrition history, dietary intake, chronic illnesses, or medications use.
"congregate_meals", -- Provision of a hot or other appropriate meal in a group setting that meets nutritional requirements and is served to an eligible participant in a group setting.
"case_management", -- A service that consists of assessment and reassessment, care planning, arranging for services, follow-up, and monitoring conducted at least every two months and at discharge. These activities must be provided by or under the direction of the designated case manager or case manager supervisor.
"home_delivered_meals", -- Provision of a hot or other appropriate meal delivered at home that meets nutritional requirements and is provided to an eligible person for home consumption.
"pc_level_i", -- A service that includes some or total assistance with thehousehold tasks on behalf of or to assist a person commensurate with the person's limitations
"pc_level_ii", -- A service that includes some or total assistance with the personal tasks on behalf of or to assist a person commensurate with the person's limitations
"county_name", -- Name of the Area Agency on Aging where service providers are located. Generally, the list is by county with the exception of Warren/Hamilton, which are combined; New York City, and the St. Regis Mohawk and Seneca Nation reservations.
"county", -- Two-character code used to identify the Area Agency on Aging (AAA) where service providers are located.
"home_health_aide", -- A service that provides the provision of health care tasks, personal hygiene services, housekeeping tasks, and other specialized related support services essential to the client's health, such as assistance with medical needs.
"health_promotion", -- Services and activities that promote good health and quality of life, increase awareness and understanding of health lifestyles, promote chronic disease prevention and management and promote physical and mental health. Includes physical fitness programs and health screening tests or activities that encourage early detection of health problems
"adult_day_services" -- Services of professionally delivered, integrated, home- and community-based, therapeutic, social, and health-related services provided to individuals to sustain living within the community; there are two types of adult day services not here differentiated: (ADS): (1) Social Adult Day Services (SADS), and (2) Adult Day Health Care (ADH), which includes a medical component and is regulated by the NYS Department of Health.
FROM
"ny-gov/office-for-the-aging-service-expenditures-7sw8-sdsd:latest"."office_for_the_aging_service_expenditures"
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 ny-gov/office-for-the-aging-service-expenditures-7sw8-sdsd 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 ny-gov/office-for-the-aging-service-expenditures-7sw8-sdsd: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 ny-gov/office-for-the-aging-service-expenditures-7sw8-sdsdCheckout 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 ny-gov/office-for-the-aging-service-expenditures-7sw8-sdsd:latestThis will download all the objects for the latest tag of ny-gov/office-for-the-aging-service-expenditures-7sw8-sdsd 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 ny-gov/office-for-the-aging-service-expenditures-7sw8-sdsd: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 ny-gov/office-for-the-aging-service-expenditures-7sw8-sdsd: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, ny-gov/office-for-the-aging-service-expenditures-7sw8-sdsd is just another Postgres schema.