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 accountability_data_social_and_environmental table in this repository, by referencing it like:
"mydata-iadb/accountability-data-social-and-environmental-gwz9-e5pe:latest"."accountability_data_social_and_environmental"or in a full query, like:
SELECT
":id", -- Socrata column ID
"complaintid", -- Original Request Code [as per internal documents reference]
"iic_perf_std4", -- Request refers to IIC Performance Standard 4
"iic_perf_std2", -- Request refers to IIC Performance Standard 2
"haswebpage", -- False for cases without webpage [non-registered 2010-policy cases]
"crp_reportduedate", -- Expected completion date of the investigation
"crp_tors_extension", -- True if an extension was required for the investigation TORs
"cp_extension", -- True if an extension was required for the Consultation Process
"eligextension", -- True when MICI requests an eligibility suspension
"reg_notification", -- Date of the Registration notification
"receptiondate", -- Date of request's reception
"op765", -- Request refers to OP-765 Indigenous People
"op761", -- Request refers to OP-761 Gender Equality In Development
"op710", -- Request refers to OP-710 Involuntary Resettlement
"iic_perf_std5", -- Request refers to IIC Performance Standard 5
"impact_patrim", -- Alleged involuntary resetlement
"impact_life", -- Alleged livelihood impact
"crp2010_noteligible_date", -- For 2010-policy cases, Date when case was declared not eligible for CRP
"idbg_financing", -- IDBG financing (main operation only)
"relatedoperations", -- Other operations linked to the request
"crp_recomm_apprvd", -- Approval of the investigation's recommendations
"crp_report_consideration", -- Date of consideration of the investigation report
"crp_tors_reject_investigate", -- True if MICI recommended not to conduct a Compliance Review
"crp_torcomments_received", -- Date of comments to the investigation TORs reception
"crp_tors_sent", -- Date of the investigation TORs issuance for comments
"cp_reportdate", -- Date of the Consultation Report
"cp_agreementdate", -- Date of CP agreement
"cp_assessmentdate", -- Date of the CP Assessment Report
"eligibility_outcome", -- Eligibility Determination
"eligmemodate", -- Date of Eligibility Memorandum
"eligduedate", -- Due date for the Eligibility Memorandum
"year", -- Year of request's reception
"casetitlept", -- Case title in Portuguese
"casetitlees", -- Case title in Spanish
"sector", -- Sector of the main related operation
"closurereason", -- Reason of completion of MICI process
"closuredate", -- Date of completion of MICI process
"currentphase", -- Currently active Phase or stage (or closed)
"case_number", -- Case File Identifier, unique
"op703", -- Request refers to OP-703 Environment And Safeguards Compliance
"iic_perf_std7", -- Request refers to IIC Performance Standard 7
"iic_inform_policy", -- Request refers to IIC Information Policy
"alleg_risk_analysis", -- Alleged deficient risk analysis
"cp2010_noteligible_date", -- For 2010-policy cases, Date when case was declared not eligible for CP
"micipolicy", -- MICI Policy applicable to the request
"operationnumber", -- Main operation linked to the request
"crp_extension", -- True if an extension was required for the investigation
"crp_originating_step", -- Phase or Stage that triggered CRP
"cp_monitoring", -- True if MICI monitored the implementation of the agreement
"eligmgmtresponse", -- Date when Management response was received
"legacy_request", -- True for Legacy MII requests [Transition Plan MI-48-1]
"casetitlefr", -- Case title in French
"casetitleen", -- Case title in English
"phase_activated", -- Phase activated upon eligibility
"eligsuspension", -- True when Management requests an eligibility suspension
"country", -- Country of the related operation
"cp_status", -- Current stage of the Consultation Phase
"iic_perf_std1", -- Request refers to IIC Performance Standard 1
"crp_actionplan", -- True if the Board requested an Action Plan after investigation
"reprisalsalleged", -- True when requesters allege reprisals risk, any time in the process. DATA FROM Jan 1 2020
"op708", -- Request refers to OP-708 Public Utilities
"impact_safety", -- Alleged safety impact
"cp_closed", -- True if CP existed and is concluded
"registration_outcome", -- Registration decision
"requestedconfidentiality", -- True when requesters request their confidentiality, any time during the process
"status", -- Current Status of the case ("Active";"Completed")
"idb_espf", -- Request refers to ESP Framework
"alleg_no_consultation", -- Alleged deficient consultation or public participation
"impact_social_fabric", -- Alleged impact on community relations
"crp_tors_approved", -- True if the investigation TORs were approved
"repres_non_cso", -- True if request had a representative (who was not a CSO)
"repres_cso", -- True if a CSO supported the request
"op102", -- Request refers to OP-102 Access To Information
"impact_health", -- Alleged health impact
"crp_status", -- Current stage of the Compliance Review Phase
"cp_monitor_start", -- Start date of the CP monitoring stage
"category", -- Category of the main operation
"iic_es_policy", -- Request refers to IIC Social and Environmental Policy
"institution", -- Institution linked to the operation ("IDB";"IDBinvest";"IDBlab";"None")
"project_indicator_elig", -- True if linked operation had a previous eligible request
"cp_agreement", -- True if an agreement was reached
"cp_exists", -- True if Consultation Phase exists(ed)
"cp_assess_extension", -- True if an extension was required for the CP assessment
"iic_perf_std3", -- Request refers to IIC Performance Standard 3
"alleg_no_information", -- Alleged deficient information process
"impact_environ", -- Alleged environmental impact
"crp_findings_apprvd", -- Approval of the investigation's findings
"cp_feasible", -- Feasibility of a Consultation Process
"op704", -- Request refers to OP-704 Disaster Risk Management
"crp_closed", -- True if CRP existed and is concluded
"iic_perf_std6", -- Request refers to IIC Performance Standard 6
"type_of_requester", -- Type of requesters
"crp_exists", -- True if Compliance Review Phase exists(ed)
"webpage", -- URL of this case on MICI's public registry
"iic_perf_std8", -- Request refers to IIC Performance Standard 8
"impact_cultural", -- Alleged cultural impact
"project_indicator_recep" -- True if linked operation had a previous request
FROM
"mydata-iadb/accountability-data-social-and-environmental-gwz9-e5pe:latest"."accountability_data_social_and_environmental"
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 mydata-iadb/accountability-data-social-and-environmental-gwz9-e5pe 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 mydata-iadb/accountability-data-social-and-environmental-gwz9-e5pe: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 mydata-iadb/accountability-data-social-and-environmental-gwz9-e5peCheckout 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 mydata-iadb/accountability-data-social-and-environmental-gwz9-e5pe:latestThis will download all the objects for the latest tag of mydata-iadb/accountability-data-social-and-environmental-gwz9-e5pe 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 mydata-iadb/accountability-data-social-and-environmental-gwz9-e5pe: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 mydata-iadb/accountability-data-social-and-environmental-gwz9-e5pe: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, mydata-iadb/accountability-data-social-and-environmental-gwz9-e5pe is just another Postgres schema.