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 next_generation_accountability_system table in this repository, by referencing it like:
"ct-gov/next-generation-accountability-system-h28j-iix5:latest"."next_generation_accountability_system"or in a full query, like:
SELECT
":id", -- Socrata column ID
"ind2lep_oral_points", -- Progress Toward English Language Proficiency (Average percentage of growth target achieved based on LAS Links for all English learners) - Oral Rate Points Received
"ind2lep_oral_rate", -- Progress Toward English Language Proficiency (Average percentage of growth target achieved based on LAS Links for all English learners) - Oral Rate
"ind2lep_ltcy_possiblepoints", -- Progress Toward English Language Proficiency (Average percentage of growth target achieved based on LAS Links for all English learners) - Literacy Rate Points Possible
"ind2lep_ltcy_points", -- Progress Toward English Language Proficiency (Average percentage of growth target achieved based on LAS Links for all English learners) - Literacy Rate Points Received
"ind2lep_ltcy_rate", -- Progress Toward English Language Proficiency (Average percentage of growth target achieved based on LAS Links for all English learners) - Literacy Rate
"distinctioncategory", -- Area of distinction (See Using Accountability Results to Guide Improvement for details.)
"distinction", -- Annually, the CSDE identifies a group of schools as Schools of Distinction for HIgh Performance, High Growth, or High Improvement (See Using Accountability Results to Guide Improvement for details.)
"supportcategory", -- Area(s) indicating need for support (e.g., High Needs ELA Performance, Graduation Rate)
"supporttype", -- Turnaround or Focus (For complete details, see Using Accountability Results to Guide Improvement (fourth edition) pages 79-82)
"hurdle_means_grad", -- The average difference between High Needs and NON High Needs Student 6-year Graduation Rates (across all districts or all schools) plus one standard deviation.
"hurdle_mean_sci", -- The average difference between High Needs and NON High Needs Student Performance Index for Science (across all districts or all schools) plus one standard deviation.
"hurdle_mean_math", -- The average difference between High Needs and NON High Needs Student Performance Index for Math (across all districts or all schools) plus one standard deviation.
"hurdle_mean_ela", -- The average difference between High Needs and NON High Needs Performance Index for ELA (across all districts or all schools) plus one standard deviation.
"grad6yrhnnhndiff", -- Difference between NON High Needs Student Rate and High Needs Student Rate (Grad6YrRateNHN-Grad6YrRateHN)
"grad6yrratenhn", -- 6-year Graduation: NON High Needs Students Rate
"grad6yrratehn", -- 6-year Graduation: High Needs Students Rate
"ind12possiblepoints", -- Arts Access Points Possible
"ind12points", -- Arts Access Points Received
"ind12rate", -- Arts Access - Percentage of students in grade 9 through 12 participating in at least one dance, theater, music, or visual arts course in the school year.
"ind11possiblepoints", -- Physical Fitness Points Possible
"ind11points", -- Physical Fitness Points Received (points are reduced if participation rate is below 90%)
"ind11participationrate", -- Physical Fitness Participation Rate - estimated percentage of students who participated in the physical fitness assessment (state expectation is 90%)
"ind11fitnessrate", -- Physical Fitness - Percent of students meeting/exceeding the “Health Fitness Zone Standard” in all four areas of the CT Physical Fitness Assessment
"ind10points", -- Postsecondary Entrance Points Received
"ind10rate", -- Postsecondary Entrance Rate (enrolled in a 2- or 4- year postsecondary institution any time during the first year after high school graduation)
"gradgapflag", -- A district/school is identified as having a graduation rate gap if the size of the six-year graduation rate gap between the High Needs group and the Non-High Needs group (or 94% if that’s lower) is at least one standard deviation greater than the statewide gap.
"ind9pointspossible", -- 6-year Graduation: High Needs Students Points Possible
"ind9points", -- 6-year Graduation: High Needs Students Points Received
"ind9rate", -- 6-year Graduation: Percentage of first time 9th graders who graduate with a regular high school diploma in six years or less – High Needs Students
"ind8pointspossible", -- 4-year Graduation: All Students Points Possible
"ind8points", -- 4-year Graduation: All Students Points Received
"ind8rate", -- 4-year Graduation: Percentage of first time 9th graders who graduate with a regular high school diploma in four years or less – All Students
"ind7pointspossible", -- Students On-track to High School Graduation Points Possible
"ind7rate", -- Students On-track to High School Graduation Rate (9th graders earning at least five full-year credits in the year)
"ind6possiblepoints", -- Preparation for CCR - Percent Passing Exams Points Possible
"ind6points", -- Preparation for CCR - Percent Passing Exams Points Received
"ind6rate", -- Preparation for CCR - Percent Passing Exams - Percentage of students in grades 11 & 12 achieving CCR benchmark on at least one of the following: SAT or ACT or AP or IB
"ind5possiblepoints", -- Preparation for CCR - Coursework - Points Possible
"ind5points", -- Preparation for CCR - Coursework - Points Received
"ind5rate", -- Preparation for CCR - Percentage of students in grades 11 & 12 participating in at least one of the following during high school: Two courses in AP/IB/dual enrollment; or Two courses in one of seventeen CTE categories; or Two workplace experience “courses” in any area.
"ind4hnpossiblepoints", -- Chronic Absenteeism (missing ten percent or greater of the total number of days enrolled in the school year) - High Needs Students Points Possible
"ind4hnpoints", -- Chronic Absenteeism (missing ten percent or greater of the total number of days enrolled in the school year) - High Needs Students Points Received
"ind4possiblepoints", -- Chronic Absenteeism (missing ten percent or greater of the total number of days enrolled in the school year) - All Students Points Possible
"ind4points", -- Chronic Absenteeism (missing ten percent or greater of the total number of days enrolled in the school year) - All Students Points Received
"ind4rate", -- Chronic Absenteeism (missing ten percent or greater of the total number of days enrolled in the school year) - All Students Rate
"ind3sci_nhn_rate", -- Assessment Participation Rate Science - NON High Needs Students
"ind3sci_hn_rate", -- Assessment Participation Rate Science - High Needs Students
"ind3sci_all_rate", -- Assessment Participation Rate Science - All Students
"ind3math_hn_rate", -- Assessment Participation Rate Math - High Needs Students
"ind3math_all_rate", -- Assessment Participation Rate Math - All Students
"ind3ela_nhn_rate", -- Assessment Participation Rate ELA - NON High Needs Students
"ind3ela_hn_rate", -- Assessment Participation Rate ELA - High Needs Students
"ind3ela_all_rate", -- Assessment Participation Rate ELA - All Students
"ind3partrateflag", -- Indicates if assessment participation rate is less than 95% for either the All Students group or the High Needs group in ELA, Math, or Science. (1=Yes, 0=No, .=N/A)
"ind2math_hn_possiblepoints", -- Math Academic Growth (Average percentage of growth target achieved) - High Needs Students Points Possible
"ind2math_hn_points", -- Math Academic Growth (Average percentage of growth target achieved) - High Needs Students Points Received
"ind2math_nhn_rate", -- Math Academic Growth (Average percentage of growth target achieved) - NON High Needs Students Rate
"ind2math_hn_rate", -- Math Academic Growth (Average percentage of growth target achieved) - High Needs Students Rate
"ind2ela_hn_possiblepoints", -- ELA Academic Growth (Average percentage of growth target achieved) - High Needs Students Points Possible
"ind2ela_hn_points", -- ELA Academic Growth (Average percentage of growth target achieved) - High Needs Students Points Received
"ind2ela_nhn_rate", -- ELA Academic Growth (Average percentage of growth target achieved) - NON High Needs Students Rate
"ind2ela_hn_rate", -- ELA Academic Growth (Average percentage of growth target achieved) - High Needs Students Rate
"ind2math_all_possiblepoints", -- Math Academic Growth (Average percentage of growth target achieved) - All Students Points Possible
"ind2math_all_points", -- Math Academic Growth (Average percentage of growth target achieved) - All Students Points Received
"ind2math_all_rate", -- Math Academic Growth (Average percentage of growth target achieved) - All Students Rate
"ind2ela_all_possiblepoints", -- ELA Academic Growth (Average percentage of growth target achieved) - All Students Points Possible
"ind2ela_all_points", -- ELA Academic Growth (Average percentage of growth target achieved) - All Students Points Received
"ind2ela_all_rate", -- ELA Academic Growth (Average percentage of growth target achieved) - All Students Rate
"ind1scigap", -- Difference between High Needs and NON High Needs Student Rates For Science
"ind1mathgap", -- Difference between High Needs and NON High Needs Student Rates for Math
"ind1elagap", -- Difference between High Needs and NON High Needs Student Rates for ELA
"ind1sci_hn_possiblepoints", -- Science Performance Index - High Needs Students Points Possible
"ind1sci_hn_points", -- Science Performance Index - High Needs Students Points Received
"ind1sci_nhn_rate", -- Science Performance Index - NON High Needs Students
"ind1sci_hn_rate", -- Science Performance Index - High Needs Students Rate
"ind1sci_all_possiblepoints", -- Science Performance Index - All Students Point Possible
"ind1sci_all_points", -- Science Performance Index - All Students Point Received
"ind1sci_all_rate", -- Science Performance Index - All Students Rate
"ind1math_hn_possiblepoints", -- Math Performance Index - High Needs Students Points Possible
"ind1math_hn_points", -- Math Performance Index - High Needs Students Points Received
"ind1math_hn_rate", -- Math Performance Index - High Needs Students Rate
"ind1math_nhn_rate", -- Math Performance Index - NON High Needs Students Rate
"ind1math_all_possiblepoints", -- Math Performance Index - All Students Points Possible
"ind1math_all_points", -- Math Performance Index - All Students Points Received
"ind1math_all_rate", -- Math Performance Index - All Students Rate
"ind1ela_hn_possiblepoints", -- ELA Performance Index - High Needs Students: Points Possible
"ind1ela_hn_points", -- ELA Performance Index - High Needs Students: Points Received
"ind1ela_nhn_rate", -- ELA Performance Index - NON High Needs Students Rate
"ind1ela_hn_rate", -- ELA Performance Index - High Needs Students Rate
"ind1ela_all_possiblepoints", -- ELA Performance Index - All Students: Points Possible
"ind1ela_all_points", -- ELA Performance Index - All Students: Points Received
"ind1ela_all_rate", -- ELA Performance Index - All Students Rate
"achievementgapflag", -- A district/school is identified as having an achievement gap if the size of its index score gap between the High Needs subgroup and the Non-High Needs group (or the ultimate achievement target of 75 if that’s lower) is a significant outlier i.e., at least one standard deviation greater than the statewide gap in any subject area.
"finalcategory", -- All schools are placed into one of five categories. Categories 4 and 5 represent those identified for comprehensive support (TURNAROUND) or targeted support (FOCUS). The remaining schools are categorized into either 1, 2 or 3 based on the Accountability Index, achievement/graduation gaps and assessment participation rates.
"outcomeratepct", -- Overall Accountability Index. This is the percentage of possible points earned. It is TotalPoints divided by TotalPossiblePoints.
"totalpossiblepoints", -- Total Accountability Points Possible. This varies based on grades offered at the school (e.g., points for graduation indicators are not possible for elementary schools).
"totalpoints", -- Total Accountability Points Received.
"category", -- Indicator of what the row represents (District, School or State). Values include DistrictTot, SchoolTot, StateTot.
"schooltitleitype", -- Title 1 type for the school (not available for district or state). Values include Targeted Assistance, Schoolwide.
"schoolhighgrade", -- The highest grade offered in the school (not available for district or state).
"schoollowgrade", -- The lowest grade offered in the school (not available for district or state).
"schoolorgtype", -- Organization Type of the school (not available for district or state). Values include CT Technical Education and Career Schools, Endowed and Incorporated Academies Schools, Public Charter Schools, Public Schools, Regional Education Service Center Schools, Regional Schools, State Agency Facilities.
"schoolcode", -- Seven-digit School Code
"schoolname", -- School Name
"reportingdistrictcode", -- Seven-digit District Code
"rptngdistrictname", -- District Name
"fallofyear", -- Fall of the school year (e.g., 2017 represents school year 2017-18)
"ind2lep_oral_possiblepoints", -- Progress Toward English Language Proficiency (Average percentage of growth target achieved based on LAS Links for all English learners) - Oral Rate Points Possible
"schoolyear", -- School Year, formatted yyyy-yy
"ind10possiblepoints", -- Postsecondary Entrance Points Possible
"ind7points", -- Students On-track to High School Graduation Points Received
"ind4hnrate", -- Chronic Absenteeism (missing ten percent or greater of the total number of days enrolled in the school year) - High Needs Students Rate
"ind3math_nhn_rate" -- Assessment Participation Rate Math - NON High Needs Students
FROM
"ct-gov/next-generation-accountability-system-h28j-iix5:latest"."next_generation_accountability_system"
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 ct-gov/next-generation-accountability-system-h28j-iix5 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 ct-gov/next-generation-accountability-system-h28j-iix5: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 ct-gov/next-generation-accountability-system-h28j-iix5Checkout 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 ct-gov/next-generation-accountability-system-h28j-iix5:latestThis will download all the objects for the latest tag of ct-gov/next-generation-accountability-system-h28j-iix5 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 ct-gov/next-generation-accountability-system-h28j-iix5: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 ct-gov/next-generation-accountability-system-h28j-iix5: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, ct-gov/next-generation-accountability-system-h28j-iix5 is just another Postgres schema.