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 act_baseline_characteristics_summary_5_year_window table in this repository, by referencing it like:
"ny-gov/act-baseline-characteristics-summary-5-year-window-ppqs-ie7i:latest"."act_baseline_characteristics_summary_5_year_window"or in a full query, like:
SELECT
":id", -- Socrata column ID
"length_of_stay_1_to_3_years", -- Number of ACT participants who have enrolled in ACT for 1-3 years. (See Note under Length of Stay 0 to 6 Months).
"census", -- Number of individuals admitted to ACT in the aggregation categories (denominator for all columns below)
"property_damage", -- Number of ACT participants who damaged or destroyed property or created a public disturbance in the past six months
"any_substance_use", -- Number of ACT participants who have any alcohol or drug use at admission Note: Drugs include Cocaine, Cannabis, Amphetamines, Cracks, Inhalants, Opiates etc.
"psychiatric_hospitalization", -- Number of ACT participants who have any psychiatric hospitalization in the past six months at admission
"harm_to_others", -- Number of ACT participants who expressed homicidal ideation, were suspected of sexual abuse, physically abused and/or assaulted others, threatened physical violence, verbally assaulted others or engaged in arson in the past six months
"harm_to_self", -- Number of ACT participants who attempted suicide, engaged in self-injurious behavior, or expressed suicidal ideation in the past six months
"forensic_involvement", -- Number of ACT participants arrested or incarcerated in the past six months or currently involved with the criminal justice system (e.g. on probation or parole)
"psychiatric_medication", -- Number of ACT participants who have any psychiatric medication at admission
"diagnosis_depression", -- Number of ACT participants with a diagnosis of major depression at admission. (See Note under Diagnosis Schizophrenia.)
"diagnosis_bipolar_disorder", -- Number of ACT participants with a diagnosis of bipolar disorder at admission. (See Note under Diagnosis Schizophrenia.)
"diagnosis_schizophrenia", -- Number of ACT participants with a diagnosis of schizophrenia at admission. Note: Diagnostic information is grouped into hierarchical mutually exclusive groups. If a person has a diagnosis of schizophrenia they are placed in that group; if not, but they have a diagnosis of bipolar disorder they are placed in that group; then depression; then other.
"education_unknown", -- Number of ACT participants who have an Unknown level of education
"education_other", -- Number of ACT participants who have another level of education
"education_some_college", -- Number of ACT participants who have at least some college education (some college, associate degree, bachelor’s degree, or graduate degree)
"education_high_school", -- Number of ACT participants who have a high school degree or equivalent
"admission_year", -- Aggregation level: Year of admission to ACT (past 5 years)
"education_less_than_high", -- Number of ACT participants who have less than a high school degree (No formal education, middle school, or some high school)
"living_situation_unknown", -- Number of ACT participants whose living situation is Unknown
"living_situation_other", -- Number of ACT participants having another living situation
"living_situation_institution", -- Number of ACT participants who are living in an institution such as a hospital or prison
"date_created", -- Date and Time the dataset was created
"region_name", -- Aggregation level: New York State or Name of the OMH region (i.e. Central New York, Hudson River, Long Island, New York City, and Western New York)
"living_situation_unstably", -- Number of ACT participants who are unstably housed (moving around with no stable permanent home)
"living_situation_supported", -- Number of ACT participants who live in supportive housing (any permanent or temporary housing program with supports)
"living_situation_private", -- Number of ACT participants who live in a private residence (apartment or house)
"ethnicity_unknown", -- Number of ACT participants whose Hispanic ethnicity is Unknown
"aot_status_at_admission", -- Aggregation level: AOT court order status at admission (On AOT, Not On AOT, All)
"ethnicity_hispanic", -- Number of ACT participants who are Hispanic
"race_unknown", -- Number of ACT participants whose race is Unknown
"race_other", -- Number of ACT participants whose race is other than Black or White
"race_white", -- Number of ACT participants whose race is White
"gender_male", -- Number of ACT participants who are male
"living_situation_homeless", -- Number of ACT participants who are homeless (e.g., in a homeless shelter or on the street)
"age_more_than_65_years", -- Number of ACT participants who are more than 65 years old at admission
"psychiatric_er", -- Number of ACT participants who have any psychiatric ER visits in the past six months at admission
"age_45_to_64_years", -- Number of ACT participants who are 45 - 64 years old at admission
"length_of_stay_3_to_5_years", -- Number of ACT participants who have enrolled in ACT for 3-5 years. (See Note under Length of Stay 0 to 6 Months).
"employed", -- Number of ACT participants who are currently employed (employed full or part-time with or without supports)
"ethnicity_non_hispanic", -- Number of ACT participants who are not Hispanic
"race_black", -- Number of ACT participants whose race is Black
"diagnosis_other", -- Number of ACT participants with a diagnosis of Other at admission (i.e. not schizophrenia, bipolar or depression). (See Note under Diagnosis Schizophrenia.)
"length_of_stay_0_to_6_months", -- Number of ACT participants who have enrolled in ACT for 0-6 months Note: Length of stay is determined based on either discharge date or, if the participant is still enrolled, on the Date Created date.
"age_25_to_44_years", -- Number of ACT participants who are 25 - 44 years old at admission
"gender_female", -- Number of ACT participants who are female
"age_24_years_or_less", -- Number of ACT participants who are under 25 years old at admission
"length_of_stay_more_than", -- Number of ACT participants who have enrolled in ACT for 5 or more years. (See Note under Length of Stay 0 to 6 Months).
"length_of_stay_6_to_12_months" -- Number of ACT participants who have enrolled in ACT for 6-12 months. (See Note under Length of Stay 0 to 6 Months).
FROM
"ny-gov/act-baseline-characteristics-summary-5-year-window-ppqs-ie7i:latest"."act_baseline_characteristics_summary_5_year_window"
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/act-baseline-characteristics-summary-5-year-window-ppqs-ie7i 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/act-baseline-characteristics-summary-5-year-window-ppqs-ie7i: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/act-baseline-characteristics-summary-5-year-window-ppqs-ie7iCheckout 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/act-baseline-characteristics-summary-5-year-window-ppqs-ie7i:latestThis will download all the objects for the latest tag of ny-gov/act-baseline-characteristics-summary-5-year-window-ppqs-ie7i 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/act-baseline-characteristics-summary-5-year-window-ppqs-ie7i: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/act-baseline-characteristics-summary-5-year-window-ppqs-ie7i: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/act-baseline-characteristics-summary-5-year-window-ppqs-ie7i is just another Postgres schema.