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 chicago_public_schools_progress_report_cards table in this repository, by referencing it like:
"cityofchicago/chicago-public-schools-progress-report-cards-9xs2-f89t:latest"."chicago_public_schools_progress_report_cards"or in a full query, like:
SELECT
":id", -- Socrata column ID
"cps_performance_policy_status", -- This refl ects whether or not your school is on probation this year. More information on the CPS Performance Policy may be found online at www.cps.edu. N/A: Not applicable NDA: No data available
"gr_8_explore_math", -- % of students at college readiness benchmark, math
"teachers_score", -- Collaborative Teachers score from 5 Essentials survey. These scores range from 1 to 99. For more information on these scores, please see the My Voice, My School survey available at www.ccsrsurvey.uchicago.edu/2011.
"gr3_5_keep_pace_math", -- % of students meeting growth targets, math, grades 3-5
"pk_2_literacy", -- % of students at benchmark on DIBELS or IDEL, tests that help teachers measure literacy skills development in younger students (grades K-2); given in most, but not all, CPS schools; IDEL is given to students who are learning to read in Spanish.
"gr3_5_grade_level_math", -- % of students at grade level, math, grades 3-5
"isat_exceeding_math_", -- % of students exceeding on ISAT, math
"isat_value_add_math", -- ISAT value-add value, math
"gr6_8_grade_level_read", -- % of students at grade level, reading, grades 6-8
"gr6_8_keep_pace_math", -- % of students meeting growth targets, math, grades 6-8
"instruction_icon_", -- Ambitious Instruction category from 5 Essentials survey
":@computed_region_43wa_7qmu",
":@computed_region_bdys_3d7i",
":@computed_region_6mkv_f3dw",
":@computed_region_vrxf_vc4k",
":@computed_region_rpca_8um6",
"location_state",
"location_zip",
"location_city",
"location_address",
":@computed_region_awaf_s7ux",
"street_address",
"network_manager",
"environment_score", -- Supportive Environment score from 5 Essentials survey. These scores range from 1 to 99. For more information on these scores, please see the My Voice, My School survey available at www.ccsrsurvey.uchicago.edu/2011.
"leaders_score_", -- Effective Leaders score from 5 Essentials survey. These scores range from 1 to 99. For more information on these scores, please see the My Voice, My School survey available at www.ccsrsurvey.uchicago.edu/2011.
"parent_engagement_score", -- Parent Perception/Engagement score from parent survey. These scores range from 30 to 70. For more information on these scores, please see the parent survey information available at www.cps.edu.
"average_student_attendance", -- Average daily student attendance %
"average_teacher_attendance", -- Average daily teacher attendance %
"individualized_education_program_compliance_rate_", -- % of Individualized Education Programs (IEPs) and 504 plans completed by due date
"gr3_5_grade_level_read_", -- % of students at grade level, reading, grades 3-5
"gr6_8_grade_level_math", -- % of students at grade level, math, grades 6-8
"isat_value_add_read", -- ISAT value-add value, reading
"_th_grade_explore_2009_", -- Average EXPLORE score, 9th graders tested in fall 2009. Test taken in 8th and 9th grades and provides baseline information about students’ college readiness; helps prepare students for their high school coursework and their post-high school choices.
"net_change_explore_and_plan", -- Difference between Grade 9 Explore (2009) and Grade 10 Plan (2010)
"graduation_rate_", -- % of students who have graduated within five years
"college_enrollment_rate", -- % of students enrolled in college
"longitude",
"community_area_number",
"community_area_name",
"ward",
"isat_exceeding_reading_", -- % of students exceeding on ISAT, reading
"gr6_8_keep_pace_read", -- % of students meeting growth targets, reading, grades 6-8
"environment_icon_", -- upportive Environment category from 5 Essentials survey.
"gr_8_explore_read", -- % of students at college readiness benchmark, reading
"school_id",
"name_of_school",
"link_",
"pk_2_math_", -- % of students at benchmark on mClass. mClass is a test that helps teachers measure math skills in younger students (grades K-2); given in some, but not all, CPS schools.
"rate_of_misconducts_per_100_students_", -- # of misconducts per 100 students
"students_passing_algebra", -- % of students passing algebra
"_th_grade_plan_2009_", -- Average PLAN score, 10th graders tested fall 2009; serves as the midpoint check of academic progress in high school; similar to ACT
"isat_value_add_color_math", -- ISAT value-add color, math
"isat_value_add_color_read", -- ISAT value-add color, reading
"students_taking_algebra", -- % of students taking algebra
"_th_grade_explore_2010_", -- Average EXPLORE score, 9th graders tested in fall 2010. Test taken in 8th and 9th grades and provides baseline information about students’ college readiness; helps prepare students for their high school coursework and their post-high school choices.
"college_eligibility_", -- % of graduates eligible for a selective four-year college
"college_enrollment_number_of_students_", -- Number of students enrolled in college
"_th_grade_plan_2010_", -- Average PLAN score, 10th graders tested fall 2010; serves as the midpoint check of academic progress in high school; similar to ACT
"_th_grade_average_act_2011_", -- Average ACT score, 11th graders who tested in fall 2011; measures high school students’ general educational development and their ability to complete college level work; administered as part of the Prairie State Achievement Exam (PSAE).
"y_coordinate",
"general_services_route_",
"rcdts_code", -- Illinois State Board of Education Region County District Type Schools Codes (ISBE RCDTS codes)
"net_change_plan_and_act", -- Difference between Grade 10 Plan (2009) and Grade 11 ACT (2011)
"latitude",
"police_district",
"freshman_on_track_rate",
"elementary_or_high_school", -- ES indicates elementary school; MS indicates middle school; and HS indicates high school
"location",
"instruction_score", -- Ambitious Instruction score from 5 Essentials survey. These scores range from 1 to 99. For more information on these scores, please see the My Voice, My School survey available at www.ccsrsurvey.uchicago.edu/2011.
"teachers_icon_", -- Collaborative Teachers category from 5 Essentials survey
"leaders_icon_", -- Effective Leaders category from 5 Essentials survey
"family_involvement_score", -- Involved Families score from 5 Essentials survey. These scores range from 1 to 99. For more information on these scores, please see the My Voice, My School survey available at www.ccsrsurvey.uchicago.edu/2011.
"x_coordinate",
"parent_engagement_icon_", -- Parent Perception/Engagement category from parent survey
"parent_environment_icon", -- Parent Perception/Environment category from parent survey
"gr3_5_keep_pace_read_", -- % of students meeting growth targets, reading, grades 3-5
"phone_number",
"city",
"healthy_schools_certified_",
"family_involvement_icon", -- Involved Families category from 5 Essentials survey
"state",
"adequate_yearly_progress_made_",
"collaborative_name",
"zip_code",
"cps_performance_policy_level", -- Level 1 indicates the highest performing schools. Level 2 indicates a middle-performing school that needs improvement. Level 3 indicates the lowest performing schools. NDA indicates no data available.
"safety_icon_", -- Student Perception/Safety category from 5 Essentials survey
"safety_score", -- Student Perception/Safety score from 5 Essentials survey. These scores range from 1 to 99. For more information on these scores, please see the My Voice, My School survey available at www.ccsrsurvey.uchicago.edu/2011.
"track_schedule",
"parent_environment_score" -- Parent Perception/Environment score from parent survey. These scores range from 30 to 70. For more information on these scores, please see the parent survey information available at www.cps.edu.
FROM
"cityofchicago/chicago-public-schools-progress-report-cards-9xs2-f89t:latest"."chicago_public_schools_progress_report_cards"
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 cityofchicago/chicago-public-schools-progress-report-cards-9xs2-f89t 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 cityofchicago/chicago-public-schools-progress-report-cards-9xs2-f89t: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 cityofchicago/chicago-public-schools-progress-report-cards-9xs2-f89tCheckout 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 cityofchicago/chicago-public-schools-progress-report-cards-9xs2-f89t:latestThis will download all the objects for the latest tag of cityofchicago/chicago-public-schools-progress-report-cards-9xs2-f89t 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 cityofchicago/chicago-public-schools-progress-report-cards-9xs2-f89t: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 cityofchicago/chicago-public-schools-progress-report-cards-9xs2-f89t: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, cityofchicago/chicago-public-schools-progress-report-cards-9xs2-f89t is just another Postgres schema.