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 restaurant_inspections_in_tricounty_colorado_2018 table in this repository, by referencing it like:
"colorado-gov/restaurant-inspections-in-tricounty-colorado-2018-cx7q-izrb:latest"."restaurant_inspections_in_tricounty_colorado_2018"or in a full query, like:
SELECT
":id", -- Socrata column ID
"_14e", -- Physical Facilities: Ventilation. Non-Critical
"_08b", -- Toxics Unlabeled. The contents of containers of toxic materials must be identified. Category: Poison/Toxic Items
"_01e", -- Food Source: Date marking
"siteaddress", -- Street address of facility selling consumable goods
"_13c", -- Utensils, Single-Service Articles: No reuse of single-service articles. Non-Critical
"_13a", -- Utensils, Single-Service Articles: Utensils provided, used, stored. Non-Critical
"i0b", -- Equipment Design, Construction: Nonfood-contact surfaces/. Non-Critical
"_05a", -- Safe Water Source. Water from a safe, drinkable source shall be supplied to the establishment. Category: Water/Sewer/Plumbing
"_01a", -- Unapproved Source; No Shellfish Tags. Food must be purchased from suppliers who are properly inspected and/or licensed. All shellfish are sold with source tracking tags that must be retained for at least 90 days. All milk and egg products must be pasteurized. Category: Food Source
"longitude", -- Longitude of facility selling consumable goods
"servicedesc", -- Type of inspection
"recordid", -- ID number of the licensed inspector's program
"activitydate", -- Date of facility inspection
"latitude", -- Latitude of facility selling consumable goods
"zip", -- Zip of facility selling consumable goods
"city", -- City of facility selling consumable goods
"programidentifier", -- Name of facility selling consumable goods
"serialnumber", -- ID number of the licensed inspector
"_14i", -- Physical Facilities: Restroom facilities. Non-Critical
"_14g", -- Physical Facilities: Premises maintained. Non-Critical
"_05c", -- Cross-Connections/Backflow Protection. The establishment’s drinking water supply shall be protected from contamination resulting from plumbing connections to undrinkable water or other substances. Category: Water/Sewer/Plumbing
"county", -- County in which inspected business operates
"_15b", -- Other Operations: Linen properly stored. Non-Critical
"_14b", -- Physical Facilities: Garbage and refuse. Non-Critical
"_10a", -- Equipment Design, Construction: Food-contact surfaces. Non-Critical
"_15a", -- Other Operations: Personnel: clean clothes, hair restraints, authorized. Non-Critical
"_14c", -- Physical Facilities: Floors, walls, and ceilings. Non-Critical
"_10c", -- Equipment Design, Construction: Dishwashing facilities. Non-Critical
"_02f", -- Demonstration of Knowledge. Employees must be able to demonstrate knowledge of disease prevention for each task they perform. Employees are required to report to the person in charge when infected with a disease transmitted by the consumption of contaminated food. Category: Personnel
"_03a", -- Cooling. Foods requiring refrigeration after cooking shall be cooled to 41° F. within 6 hours. Category: Food Temp Control Critical
"_02g", -- Personnel: Preventing food contamination from bare hands
"_14a", -- Physical Facilities: Plumbing: installed, maintained. Non-Critical
"numberoffbicritical", -- Total food borne illness risk critical violations for inspection codes 01a-08c
"_07c", -- Animals Excluded. Live animals are not permitted within retail food establishments, except for edible fish, crustacea, shellfish, or fish in aquariums. Guide or service animals assisting handicapped persons are permitted in customer and office areas. Category: Pest Control
"_03d", -- "Required Cooking Temperatures. All parts of poultry, poultry stuffing, stuffed meats or stuffings containing meat shall be heated/cooked to at least 165°F with no interruption; all parts of pork or food containing pork to at least 155°F; rare beef steak or rare roast beef to an internal temperature of at least 130°F; eggs and fish to an internal temperature of 145°F; ground beef/game meat to an internal temperature of 155°F; microwaved foods to an internal temperature of 165°F, unless otherwise ordered by the consumer. Category: Food Temp Control Critical
"pe", -- Building Capacity
"_14f", -- Physical Facilities: Locker rooms. Non-Critical
"_12c", -- Cleaning Of Equipment & Utensils: Dishwashing operations. Non-Critical
"_06c", -- Soap/Drying Devices. Soap and approved drying device shall be provided at all handsinks at all times. Category: Handwash Facilities
"_03c", -- "Hot PHF Not 135°F or More. Foods containing meats, dairy products, poultry, seafood, cooked vegetables, beans, rice, or pasta must be held hot at a temperature of at least 135° F, except for rare roast beef, which may be held at 130° F. Category: Food Temp Control Critical
"totalnoncritical", -- Total non-critical violations for inspection codes 09a-15b
"_02e", -- Eating, Drinking, Smoking. Smoking, eating or drinking in food preparation or utensil washing areas is prohibited. Employee beverages shall be contained in clean, covered containers, and stored to prevent contamination of food, clean equipment, and utensils. Category: Personnel
"_03b", -- Reheating. Refrigerated foods must be reheated to at least 165° F. within 2 hours. Category: Food Temp Control Critical
"_14h", -- Physical Facilities: Separation of living, laundry. Non-Critical
"_13b", -- Utensils, Single-Service Articles: Single service articles stored, used. Non-Critical
"_12d", -- Cleaning Of Equipment & Utensils: Wiping cloths. Non-Critical
"_05b", -- Hot/Cold Water Under Pressure. An adequate supply of hot and cold water shall be supplied to those fixtures that require them. Category: Water/Sewer/Plumbing
"_03g", -- PHF Holding/Reheating Units. Sufficient equipment to maintain potentially hazardous foods at required temperatures must be provided. Category: Food Temp Control Critical
"_01b", -- Unsound Condition; Damaged Cans; Raw Fish Not Frozen. Food must be in good condition, cannot be dirty, spoiled, or contaminated. Dented or otherwise damaged cans may not be stored with undented/undamaged cans. Raw fish must be frozen to kill parasites before it can be served raw (sushi). Category: Food Source
"_02d", -- Common Towels; Inappropriate Sink; Fingernails; Minimal Hand Contact With Food. The handling of ready-to-eat foods with bare hands shall be minimized. Employees shall keep their fingernails clean and trimmed. The wiping of hands on cloth towels or aprons is prohibited. The washing of hands shall be performed in designated handwashing sinks only. Use singleuse gloves properly. Category: Personnel
"_11c", -- Testing Devices: Chemical test kits provided and accessible. Non-Critical
"totalcritical", -- Total critical violations for inspection codes 01a-08c
"_08c", -- Sanitizer Too High/Unapproved Toxics . Necessary toxic chemicals must be approved and used properly. Category: Poison/Toxic Items
"_07b", -- "Pesticide Application. Only approved pesticides may be used for pest control in retail food establishments. Category: Pest Control
"pedesc", -- Type of business with building capacity range
"facilityid", -- ID number of the facility selling consumable goods
"_01f", -- Food Source: Consumer advisory (7/1/13)
"_11a", -- Testing Devices: Refrigeration units provided with accurate, conspicuous thermometer. Non-Critical
"_05d", -- Sewage Disposal. Sewage and wastewater shall be disposed of in a sanitary sewer. Category: Water/Sewer/Plumbing
"_01d", -- HACCP Plan. The packaging of foods by methods that remove or reduce oxygen inside the package, or otherwise modify the atmosphere inside the package, must be conducted following an approved procedure. Category: Food Source
"_12a", -- Cleaning Of Equipment & Utensils: Food-contact surfaces. Non-Critical
"_03f", -- No Product Thermometer. A long metal stem thermometer must be provided to determine proper food temperatures. Category: Food Temp Control Critical
"st", -- State of facility selling consumable goods
"_14d", -- Physical Facilities: Lighting. Non-Critical
"_11b", -- Testing Devices: Dish machine provided with accurate thermometer and gauge cock. Non-Critical
"_09b", -- Food Labeling, Food Protection: Food protected from contamination. Non-Critical
"_09a", -- Food Labeling, Food Protection: Original container, properly labeled. Non-Critical
"_06b", -- Lavatory Accessibility/Use. Handsinks shall be used for no other purpose than to wash hands, and must be available for use at all times. Category: Handwash Facilities
"_04c", -- In-place Sanitization. Large, non-movable food-contact equipment must be sanitized by exposure to high temperature water or an approved sanitizer/water solution at the proper concentration. Category: Sanitizing Rinse
"_03e", -- Cold PHF Not 41°F or Less. Foods requiring refrigeration must be held cold at a temperature of not more than 41° F. Category: Food Temp Control Critical
"_02c", -- Handwashing. Employees must thoroughly wash their hands before starting work, after smoking, eating, drinking, clearing tables, using the toilet, or any other activity that may contaminate their hands. The use of hand sanitizers may not be substituted for thorough hand washing. Category: Personnel
"description", -- Type of business
"category", -- Risk category
"_04a", -- Manual Sanitization. Utensils washed by hand in a 3-compartment sink must be sanitized by immersion in high temperature water or an approved sanitizer/water solution at the proper concentration.Category: Sanitizing Rinse
"agency", -- Agency under which operating business is inspected
"_08a", -- Toxics Storage. Poisonous or toxic materials must be stored below or away from food and utensils, and reactive materials must be stored away from each other. Category: Poison/Toxic Items
"_07a", -- Presence of Insects/Rodents/Other Pests. Control measures shall be employed to minimize the presence of insects, rodents, and other pests within and about the establishment. Category: Pest Control
"_06a", -- Adequate Toilet/Handwashing Facilities/ Metered Faucets. Handwashing sinks shall be provided in sufficient number and location to be readily accessible for handwashing, and shall be properly designed and installed. Category: Handwash Facilities
"_04b", -- Mechanical Sanitization. Utensils washed in a mechanical dishmachine must be sanitized by exposure to high temperature water or an approved sanitizer/water solution at the proper concentration.Category: Sanitizing Rinse
"_02b", -- Infected Wounds. Employees with a wound or boil must wear a dry, waterproof, durable, tight fitting bandage and a single-use glove that shields the wound from contact with food and/or equipment. Category: Personnel
"_02a", -- Infected Worker. Personnel with infections and/or communicable diseases may not handle food or the foodcontact surfaces of equipment or utensils, and may not work in areas that present the risk of disease transmission to other persons. Category: Personnel
"_01c", -- Cross-contamination; Food Re-served. Foods that require no further cooking before serving must be protected from contamination from raw meats, poultry, or seafood. Shellfish from different lots cannot be mixed. Unwrapped foods cannot be reserved, and no meat, poultry, seafood, dairy products, cooked vegetables or cooked pasta may be re-served, whether packaged or not. Category: Food Source
"_12b" -- Cleaning Of Equipment & Utensils: Nonfood-contact surfaces. Non-Critical
FROM
"colorado-gov/restaurant-inspections-in-tricounty-colorado-2018-cx7q-izrb:latest"."restaurant_inspections_in_tricounty_colorado_2018"
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 colorado-gov/restaurant-inspections-in-tricounty-colorado-2018-cx7q-izrb 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 colorado-gov/restaurant-inspections-in-tricounty-colorado-2018-cx7q-izrb: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 colorado-gov/restaurant-inspections-in-tricounty-colorado-2018-cx7q-izrbCheckout 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 colorado-gov/restaurant-inspections-in-tricounty-colorado-2018-cx7q-izrb:latestThis will download all the objects for the latest tag of colorado-gov/restaurant-inspections-in-tricounty-colorado-2018-cx7q-izrb 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 colorado-gov/restaurant-inspections-in-tricounty-colorado-2018-cx7q-izrb: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 colorado-gov/restaurant-inspections-in-tricounty-colorado-2018-cx7q-izrb: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, colorado-gov/restaurant-inspections-in-tricounty-colorado-2018-cx7q-izrb is just another Postgres schema.