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 overdose_information_network_data_cy_january_2018 table in this repository, by referencing it like:
"pa-gov/overdose-information-network-data-cy-january-2018-hbkk-dwy3:latest"."overdose_information_network_data_cy_january_2018"or in a full query, like:
SELECT
":id", -- Socrata column ID
"geocoded_column_1_address",
"geocoded_column", -- Georeferenced column for use in creating mapping visualizations with both latitude and longitude of the Incident county
"ethnicity_desc", -- Victim ethnicity. Possible values: Hispanic, Mongolian, Not Hispanic, Unknown
"geocoded_column_city",
"victim_county_lat", -- Victim County Latitude
"victim_county_fips_code", -- The FIPS code for the county of the victim. The FIPS county code is a five-digit Federal Information Processing Standard (FIPS) code (FIPS 6-4) which uniquely identifies counties and county equivalents in the United States, certain U.S. possessions, and certain freely associated states. This is the 3-digit part of the 5-digit county FIPS code specifically standing for the county.
"incident_county_long", -- Incident County Longitude
"revive_action_desc", -- Describes the action taken if the victim received naloxone and survived. Possible values: Arrest, Hospital Conscious, Hospital Unconscious, Other, Released, Transported to treatment, Verbally referred to treatment
"administration_id", -- Unique key identifying a single agency's administration of naloxone to an overdose victim. The ID will only repeat itself within an incident. Null will be present in the field if there was an overdose incident but naloxone was not administered.
"susp_od_drug_desc", -- Suspected drug causing the overdose. Possible values: OTHER, BATH SALTS, BENZODIAZEPINES (I.E.VALIUM, XANAX, ATIVAN, ETC), COCAINE/CRACK, FENTANYL, FENTANYL ANALOG/OTHER SYNTHETIC OPIOID, HEROIN, UNKNOWN, MARIJUANA, METHADONE, METHAMPHETAMINE, PHARMACEUTICAL OPIOID, PHARMACEUTICAL OTHER, SUBOXONE, SYNTHETIC MARIJUANA, ALCOHOL, PHARMACEUTICAL STIMULANT, CARFENTANIL, BARBITURATES (I.E. AMYTAL, NEMBUTAL, ETC)
"victim_od_drug_id", -- Unique identifier for each suspected drug causing the overdose. Value will not appear on different incidents. Value is assigned by the system.
"race", -- Victim Race. Possible values: American Indian or Alaskan Native, Asian or Pacific Islander, Black, Unknown, White
"incident_county_name", -- The name of the county in Pennsylvania where the incident occurred. If a County doesn't exist in the data set then nothing was reported for an Incident in that county.
"day", -- The day of the week the incident occurred
"incident_date", -- The date that the incident occurred
"geocoded_column_state",
"geocoded_column_1", -- Georeferenced column for use in creating mapping visualizations with both latitude and longitude of the victim county
"victim_county_long", -- Victim County Longitude
"incident_county_fips_code", -- The FIPS code of the county where the incident occurred. The FIPS county code is a five-digit Federal Information Processing Standard (FIPS) code (FIPS 6-4) which uniquely identifies counties and county equivalents in the United States, certain U.S. possessions, and certain freely associated states. This is the 3-digit part of the 5-digit county FIPS code specifically standing for the county.
"third_party_admin_desc", -- Description of who administered naloxone if the administration occurred by someone other than the responding agency entering the incident. Null value means Non Third Party Administration or no administration of naloxone. If 'Other' is listed this could mean another agency, hospital staff, family member, roommate, landlord, friend, etc. For records with "Third Party Admin Desc" = Null and "Naloxone administered" = Yes, naloxone was administered by the responding police agency.
"naloxone_administered", -- Field identifying whether or not naloxone was administered. Possible values: Y, N
"victim_county", -- The county of the victim's home address if provided. If a County doesn't not exist in the data set then nothing was reported for a Victim or lives in that County.
"victim_state", -- The state of the victim's home address if provided
"incident_time", -- The time the incident occurred utulizing a 24-hour clock
"incident_id", -- Key that identifies a unique record containing the details of a single incident (identified by date, time and location)
"geocoded_column_1_zip",
"incident_county_lat", -- Incident County Latitude
"response_desc", -- Describes the victim's response to naloxone. Possible values: Combative, No Response to naloxone, Responsive and Alert, Responsive and Angry, Responsive but sedated
"dose_desc", -- Measurement unit (MG) or Unknown.
"accidental_exposure", -- Identifies an incident where a first responder was accidentally exposed to an opioid. Possible values: Y, N
"geocoded_column_1_state",
"geocoded_column_address",
"survive", -- Describes if the victim survived the incident. Possible values: Y, N, U. (U=Unknown is a value assigned by the person entering the data. In some cases the responder may not know if the victim survived.)
"age_range", -- Age range of the victim
"geocoded_column_1_city",
"dose_count", -- Number of doses of naloxone administered. Multiple doses can be administered to a single victim
"dose_unit", -- Numeric field indicating the number of milligrams in the dose of naloxone administered. 0 Means Unknown. Null value means either no administration of naloxone or none reported.
"victim_id", -- Key that identifies a unique record for a victim.
"response_time_desc", -- Describes the amount of time it took for the victim to respond after receiving naloxone. Null value means either no administration of naloxone or none reported. Possible values: <1, 1-3, 3-5, >5, Don’t Know, Did not work
"gender_desc", -- Victim Gender. Possible values include: Male, Female, Unknown
"incident_state", -- The state where the incident occurred
"geocoded_column_zip"
FROM
"pa-gov/overdose-information-network-data-cy-january-2018-hbkk-dwy3:latest"."overdose_information_network_data_cy_january_2018"
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/overdose-information-network-data-cy-january-2018-hbkk-dwy3 with SQL in under 60 seconds.
This repository is an "external" repository. That means it's hosted elsewhere, in this case at data.pa.gov. When you querypa-gov/overdose-information-network-data-cy-january-2018-hbkk-dwy3:latest on the DDN, we "mount" the repository using the socrata mount handler. The mount handler proxies your SQL query to the upstream data source, translating it from SQL to the relevant language (in this case SoQL).
We also cache query responses on the DDN, but we run the DDN on multiple nodes so a CACHE_HIT is only guaranteed for subsequent queries that land on the same node.
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 (like this repository), 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, where the author has pushed Splitgraph Images to the repository, you can "clone" and/or "checkout" the data using sgr cloneand sgr checkout.
Mounting Data
This repository is an external repository. It's not hosted by Splitgraph. It is hosted by data.pa.gov, and Splitgraph indexes it. This means it is not an actual Splitgraph image, so you cannot use sgr clone to get the data. Instead, you can use the socrata adapter with the sgr mount command. Then, if you want, you can import the data and turn it into a Splitgraph image that others can clone.
First, install Splitgraph if you haven't already.
Mount the table with sgr mount
sgr mount socrata \
"pa-gov/overdose-information-network-data-cy-january-2018-hbkk-dwy3" \
--handler-options '{
"domain": "data.pa.gov",
"tables": {
"overdose_information_network_data_cy_january_2018": "hbkk-dwy3"
}
}'That's it! Now you can query the data in the mounted table like any other Postgres table.
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/overdose-information-network-data-cy-january-2018-hbkk-dwy3 is just another Postgres schema.