ON THIS PAGE
- Dataset schema
- Loading NYC traffic data into Firebolt
- Option 1: COPY FROM
- Option 2: External table
- Sample SQL queries
- Total traffic volume by segment and borough
- Traffic volume by direction
- Count of records by month and year
- Busiest streets by total traffic volume
- Peak traffic hour
- Comprehensive traffic analysis
New York City maintains comprehensive traffic records through its Department of Transportation. This tutorial walks through analyzing NYC traffic patterns using SQL queries in Firebolt.
## Dataset schema
| Column Name | Data Type | Description |
|---|---|---|
requestid | Bigint | Unique identifier for each record |
boro | Text | Borough the traffic data is from |
yr | Int | Year of the data |
month | Int | Month of the data |
dd | Int | Day of the data |
hh | Int | Hour of the data |
mm | Int | Minute of the data |
vol | Int | Traffic volume |
segmentid | Bigint | Unique identifier for each segment |
wktgeom | Text | Well-Known Text representation of geometry |
street | Text | Street name |
fromstreet | Text | Starting street |
tostreet | Text | Ending street |
direction | Text | Traffic direction |
## Loading NYC traffic data into Firebolt
### Option 1: COPY FROM
Let Firebolt infer the schema and create the table automatically:
COPY INTO nyc_traffic FROM 's3://firebolt-sample-datasets-public-us-east-1/nyc_sample_datasets/nyctraffic/parquet/'WITH PATTERN="*.parquet" AUTO_CREATE=TRUE TYPE=PARQUET;This generates the following table structure:
CREATE TABLE "nyc_traffic" ("requestid" bigint NULL, "boro" text NULL, "yr" integer NULL, "month" integer NULL, "dd" integer NULL, "hh" integer NULL, "mm" integer NULL, "vol" integer NULL, "segmentid" bigint NULL, "wktgeom" text NULL, "street" text NULL, "fromstreet" text NULL, "tostreet" text NULL, "direction" text NULL)### Option 2: External table
Create an external table over the Parquet files in S3:
CREATE EXTERNAL TABLE ex_nyc_traffic ( requestid bigint, boro Text, yr int, month int, dd int, hh int, mm int, vol int, segmentid bigint, wktgeom Text, street Text, fromstreet Text, tostreet Text, direction Text) URL = 's3://firebolt-sample-datasets-public-us-east-1/nyc_sample_datasets/nyctraffic/parquet/' OBJECT_PATTERN = '*.parquet'TYPE = (PARQUET);Create the internal table with a primary index tuned for the queries below:
CREATE TABLE nyc_traffic ( requestid bigint, boro Text, yr int, month int, dd int, hh int, mm int, vol int, segmentid bigint, wktgeom Text, street Text, fromstreet Text, tostreet Text, direction Text) PRIMARY INDEX yr,month,dd, hh, mm, boro, street;Load the data:
INSERT INTO nyc_traffic SELECT * FROM ex_nyc_traffic;## Sample SQL queries
### Total traffic volume by segment and borough
Identify the top 10 segments with the highest traffic volume.
SELECT boro, street, segmentid, SUM(vol) AS TotalTrafficVolumeFROM nyc_trafficGROUP BY boro, street, segmentidORDER BY TotalTrafficVolume DESCLIMIT 10;### Traffic volume by direction
Break down traffic volume by direction.
SELECT direction, SUM(vol) AS TotalTrafficVolumeFROM nyc_trafficGROUP BY direction;### Count of records by month and year
Identify trends across month-year combinations.
SELECT yr, month, COUNT(*) AS RecordCountFROM nyc_trafficGROUP BY yr, monthORDER BY yr, month;### Busiest streets by total traffic volume
Find the top 10 busiest streets for infrastructure planning.
SELECT street, SUM(vol) AS TotalTrafficVolumeFROM nyc_trafficGROUP BY streetORDER BY TotalTrafficVolume DESCLIMIT 10;### Peak traffic hour
Find the hour with the highest traffic volume.
SELECT hh, SUM(vol) AS TotalTrafficVolumeFROM nyc_trafficGROUP BY hhORDER BY TotalTrafficVolume DESCLIMIT 1;### Comprehensive traffic analysis
Combine year, borough, streets, and direction into one view.
SELECT yr, boro, street, fromstreet, tostreet, direction, SUM(vol) AS TotalTrafficVolumeFROM nyc_trafficGROUP BY ALLORDER BY yr, TotalTrafficVolume DESC;