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 texas_commission_on_environmental_quality table in this repository, by referencing it like:
"texas-gov/texas-commission-on-environmental-quality-xcc6-2a52:latest"."texas_commission_on_environmental_quality"or in a full query, like:
SELECT
":id", -- Socrata column ID
"expiration_date", -- Expiration date for the regulated entity's current or most recent dry cleaner registration. Note, if the expiration date has passed, the dry cleaner registration has expired.
"phone_number", -- Phone number of the customer.
"billing_zip_code_extension", -- Zip code extension tied to the billing address.
"billing_zip_code", -- Zip code tied to the billing address.
"customer_name", -- Name of the customer for the Dry Cleaner Registration. This name usually indicates the owner of the dry cleaning business.
"tceq_region_for_location", -- TCEQ Region number and area for the location.
"location_county", -- County tied to the location address.
"regulated_entity_name", -- Name associated with the regulated entity that has a dry cleaning registration. This name may differ from the location name if the purpose of the location has changed due to the dry cleaning operation going out of business or if the owner of the dry cleaning operation has not updated the regulated entity name.
"location_name", -- Name of the location for the dry cleaner facility or drop station.
"solvent_fee_status", -- Status indicating if a distributor should collect a solvent fee from this dry cleaning facility. Drop stations do not require solvent fees.
"phone_area_code", -- Phone area code of the customer.
"account_number", -- Number for the customer's account.
"status_year", -- The fiscal year (FY) when the dry cleaning registration last paid their registration fees. TCEQ's FYs run from Sept 1 of the year prior through August 31 of the year listed.
"customer_number_cn", -- Customer reference number for the dry cleaning registration.
"location_city", -- City tied to the location address.
"location_address", -- Location address for the dry cleaning registration.
"outlet_number", -- Unique numeric identifier for each location tied to the dry cleaner's taxpayer identification number. For example, a dry cleaner that has two dry cleaning facilities and two drop stations would have outlet numbers 1, 2, 3, and 4.
"gross_receipt_amount", -- The gross receipts for the dry cleaning facility or drop station. Gross receipts are the total revenues received for the location from all sources during the entity's annual accounting period. Gross receipt amount determines the annual fee for the location's dry cleaning registration. For more information on fees, visit https://www.tceq.texas.gov/permitting/registration/dry_cleaners/registration_fees.html.
"solvent_type", -- Type of solvent used and/or stored at the dry cleaner facility or location.
"billing_address", -- Billing address for the customer.
"location_zip_code_extension", -- Zip Code Extension tied to the location address.
"regulated_entity_number_rn", -- Regulated Entity Number associated with the location that has a Dry Cleaning Registration.
"registration_id", -- Dry Cleaning Registration (DCR) identification number.
"fund_elibility", -- Whether the dry cleaning facility or drop station has opted in and is eligible for the Dry Cleaning Environmental Remediation Fund. For more info on the fund, visit https://www.tceq.texas.gov/remediation/dry_cleaners/dceligibility.html.
"location_type", -- The type of dry cleaning location: Facility or Drop Station.
"billing_state", -- State tied to the billing address.
"location_zip_code", -- Zip Code tied to the location address.
"capacity_gallons", -- Maximum capacity of solvent stored/used at the dry cleaning location. This information is not available for all dry cleaner registrations.
"billing_address_line_2", -- Second line of the billing address for the customer.
"location_state", -- State tied to the location address.
"location_address_line_2", -- Second line of location address for the dry cleaning registration.
"certificate_status" -- The status of the Dry Cleaner Registration (current or expired).
FROM
"texas-gov/texas-commission-on-environmental-quality-xcc6-2a52:latest"."texas_commission_on_environmental_quality"
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 texas-gov/texas-commission-on-environmental-quality-xcc6-2a52 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 texas-gov/texas-commission-on-environmental-quality-xcc6-2a52: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 texas-gov/texas-commission-on-environmental-quality-xcc6-2a52Checkout 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 texas-gov/texas-commission-on-environmental-quality-xcc6-2a52:latestThis will download all the objects for the latest tag of texas-gov/texas-commission-on-environmental-quality-xcc6-2a52 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 texas-gov/texas-commission-on-environmental-quality-xcc6-2a52: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 texas-gov/texas-commission-on-environmental-quality-xcc6-2a52: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, texas-gov/texas-commission-on-environmental-quality-xcc6-2a52 is just another Postgres schema.