ON THIS PAGE
New York City's Department of Health publishes the results of every restaurant inspection. This tutorial covers the schema, loading the data into Firebolt, and sample SQL queries for extracting insights from the records.
## Dataset schema
The dataset contains 27 columns:
| Column Name | Data Type | Description |
|---|---|---|
camis | INTEGER | Unique restaurant ID |
dba | TEXT | Business name ("doing business as") |
boro | TEXT | Borough |
building | TEXT | Building number |
street | TEXT | Street |
zipcode | TEXT | ZIP code |
phone | TEXT | Phone number |
cuisine_description | TEXT | Cuisine type |
inspection_date | TEXT | Date of inspection |
action | TEXT | Action taken as a result of inspection |
violation_code | TEXT | Violation code |
violation_description | TEXT | Violation description |
critical_flag | TEXT | Whether the violation was critical |
score | NUMERIC | Inspection score |
grade | TEXT | Letter grade |
grade_date | DATE | Date the grade was issued |
record_date | DATE | Date the record was created |
inspection_type | TEXT | Type of inspection |
latitude | NUMERIC | Latitude |
longitude | NUMERIC | Longitude |
community_board | TEXT | Community board |
council_district | TEXT | Council district |
census_tract | TEXT | Census tract |
bin | TEXT | Building identification number |
bbl | TEXT | Borough-block-lot identifier |
nta | TEXT | Neighborhood tabulation area |
location_point1 | TEXT | Location point |
## Loading data into Firebolt
There are two ways to load the data.
### Option 1: COPY FROM
Let Firebolt infer the schema and create the table automatically:
COPY INTO nyc_restaurant_inspections FROM 's3://firebolt-sample-datasets-public-us-east-1/nyc_sample_datasets/nyc_restaurant_inspections/parquet/'WITH PATTERN="*.parquet" AUTO_CREATE=TRUE TYPE=PARQUET;### Option 2: External table
Create an external table over the Parquet files in S3:
CREATE EXTERNAL TABLE ex_nyc_restaurant_inspections ( camis INTEGER, dba TEXT, boro TEXT, building TEXT, street TEXT, zipcode TEXT, phone TEXT, cuisine_description TEXT, inspection_date TEXT, action TEXT, violation_code TEXT, violation_description TEXT, critical_flag TEXT, score NUMERIC, grade TEXT, grade_date DATE, record_date DATE, inspection_type TEXT, latitude NUMERIC, longitude NUMERIC, community_board TEXT, council_district TEXT, census_tract TEXT, bin TEXT, bbl TEXT, nta TEXT, location_point1 TEXT) URL = 's3://firebolt-sample-datasets-public-us-east-1/nyc_sample_datasets/nyc_restaurant_inspections/parquet/'OBJECT_PATTERN = '*.parquet'TYPE = (PARQUET);Create the internal table:
CREATE TABLE nyc_restaurant_inspections( camis INTEGER, dba TEXT, boro TEXT, building TEXT, street TEXT, zipcode TEXT, phone TEXT, cuisine_description TEXT, inspection_date TEXT, action TEXT, violation_code TEXT, violation_description TEXT, critical_flag TEXT, score NUMERIC, grade TEXT, grade_date DATE, record_date DATE, inspection_type TEXT, latitude NUMERIC, longitude NUMERIC, community_board TEXT, council_district TEXT, census_tract TEXT, bin TEXT, bbl TEXT, nta TEXT, location_point1 TEXT);Load the data from the external table:
INSERT INTO nyc_restaurant_inspectionsSELECT camis, dba, boro, building, street, zipcode, phone, cuisine_description, inspection_date, action, violation_code, violation_description, critical_flag, score, grade, grade_date, record_date, inspection_type, latitude, longitude, community_board, council_district, census_tract, bin, bbl, nta, location_point1FROM ex_nyc_restaurant_inspections;## Sample SQL queries for analysis
### Total inspections by borough
SELECT boro, COUNT(*) as total_inspectionsFROM nyc_restaurant_inspectionsGROUP BY boroORDER BY total_inspections DESC;### Top 10 most common cuisine types
SELECT cuisine_description, COUNT(*) as countFROM nyc_restaurant_inspectionsGROUP BY cuisine_descriptionORDER BY count DESCLIMIT 10;### Top 10 highest-scoring restaurants
SELECT dba, boro, max(score) as scoreFROM nyc_restaurant_inspectionsGROUP BY ALLORDER BY score DESCLIMIT 10;### Violations by critical flag
SELECT critical_flag, COUNT(*) as countFROM nyc_restaurant_inspectionsGROUP BY critical_flag;### Average inspection score by cuisine type (top 10)
SELECT cuisine_description, AVG(score) as avg_scoreFROM nyc_restaurant_inspectionsGROUP BY cuisine_descriptionORDER BY avg_score DESCLIMIT 10;Customize these queries based on your specific analytical objectives.