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 washington_health_workforce_survey_data table in this repository, by referencing it like:
"wa-gov/washington-health-workforce-survey-data-cvrw-ujje:latest"."washington_health_workforce_survey_data"or in a full query, like:
SELECT
":id", -- Socrata column ID
"professionalcertifications_2", -- Mental Health Counselor: Certifications or specialty certifications held by respondent
"volunteerhourspatientoutstate", -- Number of hours in a typical week respondent spent in volunteer client/patient care outside of Washington state
"volunteerhourspatientcar", -- Number of hours in a typical week respondent spent in volunteer client/patient care in Washington state
"volunteerhoursother", -- Number of hours in a typical week respondent spent in other volunteer activities
"volunteerhoursadministration", -- Number of hours in a typical week respondent spent in volunteer administration activities
"thirdpracticezip", -- Osteopathic: Primary zip where respondents third practice for this profession is located
"secondarypracticestate", -- Secondary state where respondent practices this profession
"secondarypracticearea", -- Category that best describes respondents secondary area of practice
"residencyprogramarea", -- Osteopathic: Areas where respondent has completed an accredited residency program or fellowship
"professionalcertifications_6", -- Social Work: Specialty certifications and/or other licenses held by respondent
"professionalcertifications_5", -- Respiratory Care Practitioner: National certifications held by respondent
"professionalcertifications_3", -- Occupational Therapy: Certifications or specialty credentials respondent possesses
"primarypracticearea", -- Description of primary area of practice
"physiciandegreelocation", -- Osteopathic: US state where respondent obtained physician degree
"paidhourspatientcareoutstate", -- Number of hours in a typical week respondent spent in paid client/patient care outside of Washington state
"numberyearsprimarypracti", -- Number of years respondent has been practicing at primary location
"numberyearspracticewashington", -- Number of years respondent has been practicing this profession in Washington State
"numberhygienstsprimarylocation", -- Dental Hygiene: Number of dental hygienists at respondents primary practice location
"nontraditionalmedicinetype", -- Osteopathic: Type of nontraditional medicine respondent practices
"highesteducationlocation", -- Location where respondent completed highest level of education for this profession
"ethnicity", -- Respondent’s ethnicity
"educationalprograms", -- Educational programs completed by respondent in this profession
"datecreated", -- Date and time survey was submitted
"credentialtype", -- Profession of respondent
"communicationinperson", -- Osteopathic PA: Primary means of communication respondent uses to communicate with primary supervising physician
"boardcertificationorganization", -- Osteopathic: Board respondent is certified by
"birthyear", -- Respondent’s year of birth
"americanindianalaskanativerace", -- Respondent’s American Indian or Alaska Native race
"primarypracticezip", -- Primary zip code where respondent practices this profession
"acgmeaccreditation", -- Osteopathic: Indication if respondent's residency was accredited by Accreditation Council for Graduate medical Education (ACGME)
"secondarypracticestreeta_1", -- Street address line 2 of secondary location where respondent practices this profession
"secondarypracticesetting", -- Veterinary: Description of respondents secondary practice location
"primarypracticestreetaddress1", -- Street address line 1 of primary location where respondent practices this profession
"primarypracticestate", -- Primary state where respondent practices this profession
"volunteerhoursresearch", -- Number of hours in a typical week respondent spent in volunteer research activities
"volunteerhourseducation", -- Number of hours in a typical week respondent spent in volunteer education/teaching activities
"unspervisedpracticelocat", -- Dental Hygiene: Type of unsupervised practice location
"thirdpracticecity", -- Osteopathic: Primary city where respondents third practice for this profession is located
"residencezipcode", -- ZIP code of respondent’s residence
"professionalcertifications_1", -- Medical Assistant Hemodialysis Technician: National credential held by resondent
"primarypracticestreetaddress2", -- Street address line 2 of primary location where respondent practices this profession
"paidhoursresearch", -- Number of hours in a typical week respondent spent in paid research activities
"paidhourspatientcareinstate", -- Number of hours in a typical week respondent spent in paid client/patient care in Washington state
"paidhoursother", -- Number of hours in a typical week respondent spent in other paid activities
"paidhoursadministration", -- Number of hours in a typical week respondent spent in paid administration activities
"numberdentistsprimarylocation", -- Dental Hygiene: Number of dentists at respondents primary practice location
"nccpacertificationyear", -- Osteopathic: Year NCCPA certification was first obtained
"highesteducationyear", -- Year that respondent complete highest level of education
"fourthpracticecity", -- Osteopathic: Primary city where respondents fourth practice for this profession is located
"asianrace", -- Respondent’s Asian race
"annualweeksworked", -- Number of weeks respondent worked or volunteered in this profession in the past 12 months
"primarypracticesetting", -- Description of primary practice location
"primarypracticecity", -- Primary city where respondent practices this profession
"thirdpracticestreetaddress1", -- Osteopathic: Street address line 1 of third location where respondent practices this profession
"primaryclinicalsupervisor", -- Medical Assistant Certified: Respondents primary clinical supervisor's type of practice
"nativehawaiianpacificisl", -- Respondent’s Native Hawaiian or Pacific Islander race
"numberhygienstssecondary", -- Dental Hygiene: Number of dental hygienists at respondents secondary practice location
"initialcredentialyear", -- Year first United States license in this profession was issued
"educationalprogramsnonpr", -- Pharmacy Technician: Educational programs completed by respondent outside of this profession
"practicesettingmedicalfocus", -- Medical Assistant Certified: Primary medial focus of respondents work setting
"thirdpracticestate", -- Osteopathic: Primary state where respondents third practice for this profession is located
"residencyprogram", -- Osteopathic: Indication if respondent has completed an accredited residency program or fellowship
"remotesitepractice", -- Osteopathic: Indication if respondent practices at a remote site
"race", -- Respondent’s race
"fourthpracticestreetaddress1", -- Osteopathic: Street address line 1 of fourth location where respondent practices this profession
"dentalhygienesupervisiontype", -- Close supervision / dentist present
"communicationtelephone", -- Osteopathic PA: Primary means of communication respondent uses to communicate with primary supervising physician
"communicateotherlanguage", -- Indicator if respondent is able to personally communicate with clients/patients in a language other than English
"secondarypracticecity", -- Secondary city where respondent practices this profession
"physiciandegreeyear", -- Osteopathic: Year respondent compelted physician degree
"paosteopathicprinciples", -- Osteopathic: Indication if respondents physician assistant applies osteopathic principles
"fourthpracticezip", -- Osteopathic: Primary zip where respondents fourth practice for this profession is located
"averageclientcaseload", -- Social Work: Respondents average client caseload
"supervisepa", -- Osteopathic: Indication if respondent supervises a physician assistant
"secondarypracticezip", -- Secondary zip code where respondent practices this profession
"secondarypracticestreeta", -- Street address line 1 of secondary location where respondent practices this profession
"fourthpracticestreetaddress2", -- Osteopathic: Street address line 2 of fourth location where respondent practices this profession
"workstatus", -- Indicator if respondent is currently practicing (paid or volunteer) in this profession
"thirdpracticestreetaddress2", -- Osteopathic: Street address line 2 of third location where respondent practices this profession
"telemedicineproviderwashington", -- Indicator if respondent provides tele-medicine in Washington State
"telemedicineprovider", -- Indicator if respondent provides tele-medicine
"speechlanguagepathologya_1", -- Speech Language Pathologist: Indication if respondent utilized speech-language pathology assistants in practice
"speechlanguagepathologya", -- Speech Language Pathologist: Respondents speech-language pathologist to assistant ratio
"socialmediauses", -- How respondent uses social media in a professional practice
"sex", -- Respondent’s sex
"restorativeservices", -- Dental Hygiene: Indication if respondent provides restorative services
"pursuingadditionaleducation", -- Dental Hygiene: Indication if respondent is pursuing additional dental hygiene education at this time
"professionalsocialmediause", -- Indicator if respondent uses social media as part of a professional practice
"professionalcertifications_7", -- Veterinary: AVMA-recognized veterinary specialty organizations respondent has been awarded diplomat status with
"professionalcertifications_4", -- Podiatric: Organizations respondent is certified by
"professionalcertifications", -- Chemical Dependency Professional: Certifications completed for respondents profession
"pointofcaretestingcount", -- Pharmacist: Number of types of point of care testing performed by respondent
"pamanipulativetherapyhours", -- Osteopathic: Amount of time per week respondents psycian assistant practices manipulate therapy
"pamanipulativetherapy", -- Osteopathic: Indication if respondents physician assistant practices osteopathic manipulative therapy
"paidhourseducation", -- Number of hours in a typical week respondent spent in paid education/teaching activities
"ownerlocumtenens", -- Osteopathic: Indication if respondent is owner or part owner of pracice location or serves as a locum tenens
"osteopathicprinciples", -- Osteopathic: Indication if respondent uses osteopathic principles
"osteopathicmanipulativetherapy", -- Osteopathic: Indication if respondent uses osteopathic manipulative therapy
"officebasedsurgery", -- Osteopathic: Indication if respondent performs office-based surgery requiring more than minimal local anesthesia
"numberdentistssecondaryl", -- Dental Hygiene: Number of dentists at respondents sectondary practice location
"notpracticereason", -- Explanation of why respondent is not practicing this profession
"nontraditionalmedicine", -- Osteopathic: Indication if respondent practices nontraditional medicine
"noncanceropiods_dopa", -- Osteopathic: Respondents practice status for prescribing chronic non-cancer opioids
"noncanceropiods_do", -- Osteopathic: Respondents practice status for prescribing chronic non-cancer opioids
"nccpacertification", -- Osteopathic: Indication of certification with the National Commission on the Certification of Physician
"manipulativetherapyhours", -- Amount of time per week respondent practices manipulative therapy
"fourthpracticestate", -- Osteopathic: Primary state where respondents fourth practice for this profession is located
"fellowshipresidencytraining", -- Physical Therapy: Indicator if respondent completed fellowship or residency training
"directpatientcarebilling", -- Pharmacy: Indication if respondent is currently billing for direct patient care services
"departmentsocialmediainf", -- Desired categories of social media information communications
"delegationagreements", -- Osteopathic PA: Number of approved delegation agreements respondent currently practices under
"communicationother", -- Osteopathic PA: Primary means of communication respondent uses to communicate with primary supervising physician
"communicationelectronic", -- Osteopathic PA: Primary means of communication respondent uses to communicate with primary supervising physician
"boardspecialtycertifications", -- Physical Therapy: Board or specialty certifications held by respondent
"audiologyassistantuse", -- Audiologist: Indicator if respondent uses audiology assistants
"audiologyassistantratio", -- Audiologist: Ratio of audiologist to audiology assistants
"audiologyassistanteducation", -- Audiologist: Level of education held by respondents audiology assistants
"aoacertified", -- Osteopathic: American Osteopathic Association (AOA) recognized boards respondent is certified by
"aoaaccreditation", -- Osteopathic: Indication if respondent's residency was accredited by American Osteopathic Association (AOA)
"abmscertified", -- Osteopathic: American Board of Medical Specialties (ABMS) recognized boards respondent is certified by
"pharmacistinterntype", -- Pharmacist Intern: Type of intern
"highesteducationonline", -- Indicator if respondent obtained highest level of education solely using distance or online learning
"communicationvideoconference", -- Osteopathic PA: Primary means of communication respondent uses to communicate with primary supervising physician
"activecredentialotherstate", -- Indicator if respondent currently holds active license in this profession in another state
"morethantwopracticelocations", -- Indicator if respondent practices at more than two locations
"acceptedpaymentmethods", -- Methods of payment accepted by respondent
"collaborativedrugtherapy", -- Pharmacist: Number of collaborative drug therapy agreements respondent practices under
"otherboardcertification" -- Osteopathic: Indication if respondent is certified by a certification board other than American Osteopathic Association (AOA) or American Board of Medical Specialties (ABMS)
FROM
"wa-gov/washington-health-workforce-survey-data-cvrw-ujje:latest"."washington_health_workforce_survey_data"
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 wa-gov/washington-health-workforce-survey-data-cvrw-ujje with SQL in under 60 seconds.
Query Your Local Engine
Install Splitgraph Locally
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 wa-gov/washington-health-workforce-survey-data-cvrw-ujje: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 wa-gov/washington-health-workforce-survey-data-cvrw-ujjeCheckout 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 wa-gov/washington-health-workforce-survey-data-cvrw-ujje:latestThis will download all the objects for the latest tag of wa-gov/washington-health-workforce-survey-data-cvrw-ujje 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 wa-gov/washington-health-workforce-survey-data-cvrw-ujje: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 wa-gov/washington-health-workforce-survey-data-cvrw-ujje: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, wa-gov/washington-health-workforce-survey-data-cvrw-ujje is just another Postgres schema.