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_school_improvement_framework_wsif_2019 table in this repository, by referencing it like:
"wa-gov/washington-school-improvement-framework-wsif-2019-qtus-455b:latest"."washington_school_improvement_framework_wsif_2019"or in a full query, like:
SELECT
":id", -- Socrata column ID
"school_organization_id", -- OSPI Unique School ID
"el_progress_decile", -- EL_Progress_Rate mapped to a 1-10 score as compared to other schools in the state
"grad_fouryear_denominator", -- Number of students that were in the 4 year cohort, for the last three years
"growth_math_median_sgp", -- Median of SGPs for the student group in mathematics over three years
"grad_fouryear_decile", -- Four year graduation rate (rated from 1-10) as compared to other schools in the state
"grad_extended_decileincrease", -- Grad_Extended_TotalIncrease mapped to a 0-2 score
"grad_combined_decile", -- Sum of Grad_FourYear_Decile and Grad_Extended_DecileIncrease
"el_progress_denominator", -- Number of students who have taken the ELPA21 for at least 2 years plus the number of students who enter the program and transition in the same year
"regularattendance_numerator", -- Number of students whom have less than an average of 2 absences per month enrolled, in the last three years
"regularattendance_denominator", -- Number of students enrolled for 90 calendar days between September 1st and June 1st, in the last three years
"regularattendance_decile", -- RegularAttendance_Rate mapped to a 1-10 score as compared to other schools in the state
"ninthgradeontrack_numerator", -- Number of first time 9th grade students (in the last three years) who have earned all credits attempted
"dualcredit_denominator", -- Number of 9-12 grade students that attempted any high school credit, summed over the last three years
"esd_name", -- OSPI Education Service District
"wsif_year", -- The last year of data the WSIF used for this run - example: 2019 = 2016-17, 2017-18, and 2018-19 data are included
"district_name", -- OSPI District Name
"school_code", -- OSPI Unique School Code
"esd_organization_id", -- OSPI Education Service District ID
"dualcredit_decile", -- DualCredit_Rate mapped to a 1-10 score as compared to other schools in the state
"ninthgradeontrack_rate", -- Percent of students that passed all credits attempted in their 9th grade year
"dualcredit_numerator", -- Number of high school students (Grades 9, 10, 11, and 12) that have completed a Dual Credit course each year (including AP, IB, College in the High School, Cambridge, Running Start, Advanced Certificated Courses, or Tech Prep) , summed over the last three years
"final_school_score", -- All applicable indicators combined (see Overall Score Weighting Table)
"student_group", -- Race/Ethnicity and Program groups
"ninthgradeontrack_denominator", -- Number of first time 9th grade students (in the last three years) that attempted a credit
"regularattendance_rate", -- Percent of students that missed less than 10% of school days
"el_progress_rate", -- Percent of students who made progress over three test administrations
"el_progress_numerator", -- Number of students who made adequate progress to transition out of services within 6 years (based on business rules) plus the number of students who enter the program and transition in the same year
"grad_extended_totalincrease", -- Percent of additional students that graduated in 5, 6, and 7 years
"grad_fouryear_rate", -- Percent of 4 year cohort that graduated in 4 year timespan, for the last three years
"school_name", -- OSPI School Name
"proficiency_ela_numerator", -- Number of students in three years who took the ELA SmarterBalance or ELA WA-AIM and scored a 3 or 4
"proficiency_ela_denominator", -- The greater of: Number of students that were tested over 3 years OR 95% of those expected to test over 3 years in ELA on the Smarter Balanced Assessment or WA-AIM
"grad_fouryear_numerator", -- Number of students who graduated in 4 years based on their first time ninth grade cohort, for the last three years
"proficiency_ela_decile", -- School proficiency in ELA (rated from 1-10) as compared to other schools in the state
"growth_combined_decile", -- Average of Growth_Math_Decile and Growth_ELA_Decile mapped toa 1-10 score as compared to other schools in the state
"growth_math_decile", -- Growth_math_rate mapped to a 1-10 score as compared to other schools in the state
"growth_ela_median_sgp", -- Median of SGPs for the student group in English Language Arts over three years
"ninthgradeontrack_decile", -- NinthGradeOnTrack_Rate mapped to a 1-10 score as compared to other schools in the state
"district_code", -- OSPI Unique District Code
"growth_ela_numberofstudents", -- Number of students that received a Student Growth Percentile (SGP) in English Language Arts
"proficiency_combined_decile", -- The average of the math and ELA proficiency (rated from 1-10) as compared to other schools in the state
"proficiency_math_rate", -- Percent of students that met standard in math on the Smarter Balanced Assessment or WA-AIM
"proficiency_math_denominator", -- The greater of: Number of students that were tested over 3 years OR 95% of those expected to test over 3 years in math on the Smarter Balanced Assessment and WA-AIM
"school_type", -- OSPI School Type: P=Public, A=Alternative, T=Tribal, C=College/University, I=Institutional, S=Special, 5=Virtual and Out of District
"proficiency_ela_rate", -- Percent of students that met standard in English Language Arts on the Smarter Balanced Assessment or WA-AIM
"proficiency_math_numerator", -- Number of students in three years who took the math SmarterBalance or math WA-AIM and scored a 3 or 4
"growth_ela_decile", -- Growth_ELA_Rate mapped to a 1-10 score as compared to other schools in the state
"sqss_combined_decile", -- Average of RegularAttendance_Decile, NinthGradeOnTrack_Decile, and DualCredit_Decile
"dualcredit_rate", -- Percent of students that completed a dual credit course in the last three years
"growth_math_numberofstudents", -- Number of students that received a Student Growth Percentile (SGP) in math
"support_tier", -- Tier of Support based on Final_School_Score
"district_organization_id", -- OSPI Unique District ID
"ela_metessa_participation", -- Met ESSA participation requirement in ELA
"math_metessa_participation", -- Met ESSA participation requirement in Math
"proficiency_math_decile", -- School proficiency in math (rated from 1-10) as compared to other schools in the state
"organizationid" -- Unique ID for each organization (ESD, District, and School) in the state
FROM
"wa-gov/washington-school-improvement-framework-wsif-2019-qtus-455b:latest"."washington_school_improvement_framework_wsif_2019"
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-school-improvement-framework-wsif-2019-qtus-455b 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 wa-gov/washington-school-improvement-framework-wsif-2019-qtus-455b: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-school-improvement-framework-wsif-2019-qtus-455bCheckout 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-school-improvement-framework-wsif-2019-qtus-455b:latestThis will download all the objects for the latest tag of wa-gov/washington-school-improvement-framework-wsif-2019-qtus-455b 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-school-improvement-framework-wsif-2019-qtus-455b: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-school-improvement-framework-wsif-2019-qtus-455b: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-school-improvement-framework-wsif-2019-qtus-455b is just another Postgres schema.