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_2022 table in this repository, by referencing it like:
"wa-gov/washington-school-improvement-framework-wsif-2022-v8by-xqk3:latest"."washington_school_improvement_framework_wsif_2022"or in a full query, like:
SELECT
":id", -- Socrata column ID
"_2022_titlei", -- 2022 Title I School Status
"growth_math_decile", -- Growth median mapped to a 1-10 score as compared to other schools in the state
"_2022_score", -- All applicable indicators combined (see Overall Score Weighting Table)
"student_group", -- Race/Ethnicity and Program groups
"grad_extended_decileincrease", -- Grad_Extended_TotalIncrease mapped to a 0-2 score
"esd_name", -- OSPI Education Service District Name
"regularattendance_rate", -- Percent of students who have less than an average of 2 absences per month enrolled, over three years
"wsif_year", -- The last year of data the WSIF used for this run
"district_organization_id", -- OSPI Unique District ID
"district_code", -- OSPI Unique District Code
"district_name", -- OSPI District Name
"school_name", -- OSPI School Name
"school_organization_id", -- OSPI Unique School ID
"school_type", -- OSPI School Type: P=Public, A=Alternative, T=Tribal, C=College/University, I=Institutional, S=Special, 5=Virtual and Out of District
"_2022_support_tier", -- Initial Tier of Support for 2022 based on Final_School_Score
"grad_fouryear_rate", -- Percent of 4 year cohort that graduated in 4 year timespan, for the last three years
"grad_extended_totalincrease", -- Percent of additional students that graduated in 5, 6, and 7 years
"al_math_avg_level", -- The average achievement level for the student group in Math
"ela_metessa_participation", -- Met ESSA participation requirement in ELA. If no, 1 point has been deducted from the decile score.
"el_progress_decile", -- EL_Progress_Rate mapped to a 1-10 score as compared to other schools in the state
"esd_organization_id", -- OSPI Education Service District ID
"sqss_combined_decile", -- Average of RegularAttendance_Decile, NinthGradeOnTrack_Decile, and DualCredit_Decile
"dualcredit_decile", -- DualCredit_Rate mapped to a 1-10 score as compared to other schools in the state
"proficiency_ela_numerator", -- Number of students in three years who took the ELA SmarterBalance or ELA WA-AIM and scored a 3 or 4
"grad_fouryear_denominator", -- Number of students that were in the 4 year cohort, for the last three years
"grad_fouryear_numerator", -- Number of students who graduated in 4 years based on their first time ninth grade cohort, for the last three years
"growth_ela_decile", -- Growth median mapped to a 1-10 score as compared to other schools in the state
"dualcredit_rate", -- Percent of students that completed a dual credit course in the three years included
"al_math_numberofstudents", -- Achievement Level (AL) - Number of students in grades 3-8 with assessment scores in Math in the SmarterBalance or WA-AIM assessments
"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
"dualcredit_denominator", -- Number of 9-12 grade students that attempted any high school credit, summed over three years
"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 three years
"school_code", -- OSPI Unique School Code
"proficiency_ela_rate", -- Percent of students that met standard in English Language Arts on the Smarter Balanced Assessment or WA-AIM
"regularattendance_denominator", -- Number of students enrolled for 90 calendar days each of the three included school years
"proficiency_ela_decile", -- School proficiency in ELA (rated from 1-10) as compared to other schools in the state
"ninthgradeontrack_decile", -- NinthGradeOnTrack_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
"organizationid", -- Unique ID for each organization (ESD, District, and School) in the state
"ninthgradeontrack_denominator", -- Number of first time 9th grade students (over three years) that attempted a credit
"ninthgradeontrack_numerator", -- Number of first time 9th grade students (over three years) who have earned all credits attempted
"other_academic_combined_decile", -- Average of Growth_Math_Decile, Growth_ELA_Decile, AL_ELA_Decile, and AL_Math_Decile
"proficiency_math_numerator", -- Number of students in three years who took the math SmarterBalance or math WA-AIM and scored a 3 or 4
"al_ela_decile", -- Average achievement level in English Language Arts mapped to a 1-10 score as compared to other schools in the state
"al_math_decile", -- Average achievement level in math mapped to a 1-10 score as compared to other schools in the state
"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
"proficiency_math_rate", -- Percent of students that met standard in math on the Smarter Balanced Assessment or WA-AIM
"grad_fouryear_decile", -- Four year graduation rate (rated from 1-10) as compared to other schools in the state
"al_ela_avg_level", -- The average achievement level for the student group in English Language Arts
"proficiency_math_decile", -- School proficiency in math (rated from 1-10) as compared to other schools in the state
"al_ela_numberofstudents", -- Achievement Level (AL) - Number of students in grades 3-8 with assessment scores in English Language Arts the SmarterBalance or WA-AIM assessments
"growth_math_median_sgp", -- Median of SGPs for the student group in mathematics
"proficiency_combined_decile", -- The average of the math and ELA proficiency (rated from 1-10) as compared to other schools in the state
"growth_ela_numberofstudents", -- Number of students that received a Student Growth Percentile (SGP) in English Language Arts
"growth_math_numberofstudents", -- Number of students that received a Student Growth Percentile (SGP) in math
"growth_ela_median_sgp", -- Median of SGPs for the student group in English Language Arts
"regularattendance_decile", -- RegularAttendance_Rate mapped to a 1-10 score as compared to other schools in the state
"regularattendance_numerator", -- Number of students who have less than an average of 2 absences per month enrolled, over three years
"el_progress_denominator", -- Number of students who have taken the ELPA21 in the 2020-21 school year and the WIDA in the 2021-22 school year
"el_progress_rate", -- Percent of students who made progress
"_2022_cycle_support", -- Final Tier of Support for 2022 based on several factors including the 2017_Support_Tier, 2022_Support Tier, and Title I status
"_2017_support_tier", -- Tier of Support from the 2017 WSIF Run
"grad_combined_decile", -- Sum of Grad_FourYear_Decile and Grad_Extended_DecileIncrease
"math_metessa_participation", -- Met ESSA participation requirement in Math. If no, 1 point has been deducted from the decile score.
"el_progress_numerator" -- Number of students who made adequate progress to transition out of services within 6 years (based on business rules)
FROM
"wa-gov/washington-school-improvement-framework-wsif-2022-v8by-xqk3:latest"."washington_school_improvement_framework_wsif_2022"
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-2022-v8by-xqk3 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-2022-v8by-xqk3: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-2022-v8by-xqk3Checkout 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-2022-v8by-xqk3:latestThis will download all the objects for the latest tag of wa-gov/washington-school-improvement-framework-wsif-2022-v8by-xqk3 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-2022-v8by-xqk3: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-2022-v8by-xqk3: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-2022-v8by-xqk3 is just another Postgres schema.