In [1]:
Copied!
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("Jupyter").getOrCreate()
spark
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("Jupyter").getOrCreate()
spark
25/06/22 04:47:52 WARN SparkSession: Using an existing Spark session; only runtime SQL configurations will take effect.
Out[1]:
SparkSession - in-memory
Load Two Months of NYC Taxi/Limousine Trip Data¶
This notebook uses the New York City Taxi and Limousine Commission Trip Record Data available on the AWS Open Data Registry. This contains data of trips taken by taxis and for-hire vehicles in New York City. This data is stored in an iceberg table called taxis.
To be able to rerun the notebook several times, let's drop the table and the views if they exist to start fresh.
In [2]:
Copied!
%%sql
CREATE DATABASE IF NOT EXISTS nyc.taxis;
%%sql
CREATE DATABASE IF NOT EXISTS nyc.taxis;
Out[2]:
In [3]:
Copied!
%%sql
DROP TABLE IF EXISTS nyc.taxis
%%sql
DROP TABLE IF EXISTS nyc.taxis
Out[3]:
In [4]:
Copied!
%%sql
DROP VIEW IF EXISTS nyc.long_distances
%%sql
DROP VIEW IF EXISTS nyc.long_distances
Out[4]:
In [5]:
Copied!
%%sql
DROP VIEW IF EXISTS nyc.negative_amounts
%%sql
DROP VIEW IF EXISTS nyc.negative_amounts
Out[5]:
Create the table¶
In [7]:
Copied!
%%sql
CREATE TABLE nyc.taxis (
VendorID bigint,
tpep_pickup_datetime timestamp,
tpep_dropoff_datetime timestamp,
passenger_count double,
trip_distance double,
RatecodeID double,
store_and_fwd_flag string,
PULocationID bigint,
DOLocationID bigint,
payment_type bigint,
fare_amount double,
extra double,
mta_tax double,
tip_amount double,
tolls_amount double,
improvement_surcharge double,
total_amount double,
congestion_surcharge double,
airport_fee double
)
USING iceberg
PARTITIONED BY (days(tpep_pickup_datetime))
%%sql
CREATE TABLE nyc.taxis (
VendorID bigint,
tpep_pickup_datetime timestamp,
tpep_dropoff_datetime timestamp,
passenger_count double,
trip_distance double,
RatecodeID double,
store_and_fwd_flag string,
PULocationID bigint,
DOLocationID bigint,
payment_type bigint,
fare_amount double,
extra double,
mta_tax double,
tip_amount double,
tolls_amount double,
improvement_surcharge double,
total_amount double,
congestion_surcharge double,
airport_fee double
)
USING iceberg
PARTITIONED BY (days(tpep_pickup_datetime))
Out[7]:
Write a month of data¶
In [8]:
Copied!
df = spark.read.parquet("/home/iceberg/data/yellow_tripdata_2022-01.parquet")
df.writeTo("nyc.taxis").append()
df = spark.read.parquet("/home/iceberg/data/yellow_tripdata_2022-01.parquet")
df.writeTo("nyc.taxis").append()
In [9]:
Copied!
%%sql
SELECT * FROM nyc.taxis
%%sql
SELECT * FROM nyc.taxis
Out[9]:
| VendorID | tpep_pickup_datetime | tpep_dropoff_datetime | passenger_count | trip_distance | RatecodeID | store_and_fwd_flag | PULocationID | DOLocationID | payment_type | fare_amount | extra | mta_tax | tip_amount | tolls_amount | improvement_surcharge | total_amount | congestion_surcharge | airport_fee |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2 | 2022-01-09 09:24:06 | 2022-01-09 09:45:35 | 1.0 | 9.81 | 1.0 | N | 138 | 244 | 2 | 29.0 | 0.0 | 0.5 | 0.0 | 6.55 | 0.3 | 37.6 | 0.0 | 1.25 |
| 2 | 2022-01-09 11:27:14 | 2022-01-09 11:47:43 | 1.0 | 9.01 | 1.0 | N | 138 | 230 | 2 | 27.5 | 0.0 | 0.5 | 0.0 | 6.55 | 0.3 | 38.6 | 2.5 | 1.25 |
| 2 | 2022-01-09 13:14:53 | 2022-01-09 13:23:52 | 1.0 | 3.52 | 1.0 | N | 138 | 7 | 1 | 12.0 | 0.0 | 0.5 | 2.81 | 0.0 | 0.3 | 16.86 | 0.0 | 1.25 |
| 2 | 2022-01-09 13:51:42 | 2022-01-09 14:16:59 | 1.0 | 10.1 | 1.0 | N | 138 | 162 | 2 | 29.0 | 0.5 | 0.5 | 0.0 | 6.55 | 0.3 | 40.6 | 2.5 | 1.25 |
| 2 | 2022-01-09 15:09:41 | 2022-01-09 15:38:33 | 1.0 | 11.76 | 1.0 | N | 138 | 181 | 2 | 35.5 | 0.5 | 0.5 | 0.0 | 0.0 | 0.3 | 38.05 | 0.0 | 1.25 |
| 2 | 2022-01-09 17:05:34 | 2022-01-09 17:36:28 | 1.0 | 12.86 | 1.0 | N | 138 | 37 | 2 | 36.0 | 0.5 | 0.5 | 0.0 | 0.0 | 0.3 | 38.55 | 0.0 | 1.25 |
| 2 | 2022-01-09 00:02:12 | 2022-01-09 00:11:44 | 1.0 | 2.43 | 1.0 | N | 48 | 90 | 2 | 9.5 | 0.5 | 0.5 | 0.0 | 0.0 | 0.3 | 13.3 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:00:18 | 2022-01-09 00:12:53 | 1.0 | 4.92 | 1.0 | N | 114 | 75 | 1 | 15.0 | 0.5 | 0.5 | 3.76 | 0.0 | 0.3 | 22.56 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:00:46 | 2022-01-09 00:13:20 | 1.0 | 3.04 | 1.0 | N | 140 | 246 | 1 | 11.5 | 0.5 | 0.5 | 2.5 | 0.0 | 0.3 | 17.8 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:00:44 | 2022-01-09 00:05:42 | 1.0 | 1.37 | 1.0 | N | 141 | 162 | 1 | 6.0 | 0.5 | 0.5 | 1.2 | 0.0 | 0.3 | 11.0 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:03:08 | 2022-01-09 00:15:43 | 1.0 | 4.99 | 1.0 | N | 246 | 41 | 1 | 15.5 | 0.5 | 0.5 | 3.86 | 0.0 | 0.3 | 23.16 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:00:50 | 2022-01-09 00:15:38 | 2.0 | 7.17 | 1.0 | N | 230 | 244 | 1 | 21.5 | 0.5 | 0.5 | 3.0 | 0.0 | 0.3 | 28.3 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:01:07 | 2022-01-09 00:05:53 | 1.0 | 0.33 | 1.0 | N | 249 | 158 | 1 | 4.0 | 0.5 | 0.5 | 2.34 | 0.0 | 0.3 | 10.14 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:00:08 | 2022-01-09 00:10:08 | 1.0 | 2.28 | 1.0 | N | 237 | 48 | 1 | 9.5 | 0.5 | 0.5 | 1.33 | 0.0 | 0.3 | 14.63 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:00:20 | 2022-01-09 00:04:28 | 2.0 | 0.63 | 1.0 | N | 164 | 164 | 2 | 4.5 | 0.5 | 0.5 | 0.0 | 0.0 | 0.3 | 8.3 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:02:57 | 2022-01-09 00:06:59 | 5.0 | 0.85 | 1.0 | N | 234 | 90 | 1 | 5.0 | 0.5 | 0.5 | 1.76 | 0.0 | 0.3 | 10.56 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:00:04 | 2022-01-09 00:03:20 | 3.0 | 0.65 | 1.0 | N | 90 | 249 | 1 | 4.5 | 0.5 | 0.5 | 5.0 | 0.0 | 0.3 | 13.3 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:00:06 | 2022-01-09 00:12:41 | 1.0 | 4.77 | 1.0 | N | 42 | 136 | 2 | 16.0 | 0.5 | 0.5 | 0.0 | 0.0 | 0.3 | 17.3 | 0.0 | 0.0 |
| 2 | 2022-01-09 00:01:03 | 2022-01-09 00:07:43 | 1.0 | 1.08 | 1.0 | N | 114 | 249 | 1 | 6.5 | 0.5 | 0.5 | 2.06 | 0.0 | 0.3 | 12.36 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:03:30 | 2022-01-09 00:12:39 | 2.0 | 1.5 | 1.0 | N | 140 | 229 | 1 | 8.0 | 3.0 | 0.5 | 2.35 | 0.0 | 0.3 | 14.15 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:52:48 | 2022-01-09 01:19:35 | 1.0 | 4.9 | 1.0 | N | 239 | 79 | 1 | 18.5 | 3.0 | 0.5 | 4.46 | 0.0 | 0.3 | 26.76 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:15:47 | 2022-01-09 00:28:35 | 1.0 | 2.57 | 1.0 | N | 238 | 262 | 1 | 11.5 | 0.5 | 0.5 | 4.59 | 0.0 | 0.3 | 19.89 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:02:18 | 2022-01-09 00:10:54 | 3.0 | 2.5 | 1.0 | N | 141 | 137 | 1 | 9.0 | 3.0 | 0.5 | 2.55 | 0.0 | 0.3 | 15.35 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:21:20 | 2022-01-09 00:25:34 | 4.0 | 0.5 | 1.0 | N | 237 | 140 | 1 | 4.5 | 3.0 | 0.5 | 1.25 | 0.0 | 0.3 | 9.55 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:52:49 | 2022-01-09 01:14:52 | 1.0 | 2.7 | 1.0 | N | 151 | 244 | 2 | 16.0 | 0.5 | 0.5 | 0.0 | 0.0 | 0.3 | 17.3 | 0.0 | 0.0 |
| 2 | 2022-01-09 00:00:04 | 2022-01-09 00:02:51 | 3.0 | 0.72 | 1.0 | N | 151 | 238 | 1 | 4.5 | 0.5 | 0.5 | 1.0 | 0.0 | 0.3 | 9.3 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:18:32 | 2022-01-09 00:36:48 | 1.0 | 10.54 | 1.0 | N | 70 | 244 | 1 | 29.5 | 0.5 | 0.5 | 7.65 | 6.55 | 0.3 | 45.0 | 0.0 | 0.0 |
| 2 | 2022-01-09 00:19:38 | 2022-01-09 00:29:40 | 3.0 | 2.08 | 1.0 | N | 249 | 137 | 1 | 9.0 | 0.5 | 0.5 | 2.56 | 0.0 | 0.3 | 15.36 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:31:39 | 2022-01-09 00:40:45 | 2.0 | 2.94 | 1.0 | N | 137 | 262 | 2 | 10.5 | 0.5 | 0.5 | 0.0 | 0.0 | 0.3 | 14.3 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:49:58 | 2022-01-09 01:00:11 | 2.0 | 2.83 | 1.0 | N | 237 | 234 | 1 | 10.5 | 0.5 | 0.5 | 3.58 | 0.0 | 0.3 | 17.88 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:04:54 | 2022-01-09 00:22:53 | 1.0 | 6.64 | 1.0 | N | 209 | 262 | 1 | 22.0 | 0.5 | 0.5 | 4.64 | 0.0 | 0.3 | 30.44 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:56:55 | 2022-01-09 01:17:04 | 2.0 | 5.39 | 1.0 | N | 263 | 129 | 2 | 18.0 | 0.5 | 0.5 | 0.0 | 0.0 | 0.3 | 21.8 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:07:25 | 2022-01-09 00:15:12 | 1.0 | 1.52 | 1.0 | N | 249 | 231 | 1 | 7.5 | 0.5 | 0.5 | 2.26 | 0.0 | 0.3 | 13.56 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:30:15 | 2022-01-09 00:39:04 | 2.0 | 1.92 | 1.0 | N | 114 | 87 | 1 | 8.0 | 0.5 | 0.5 | 2.36 | 0.0 | 0.3 | 14.16 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:55:45 | 2022-01-09 01:05:34 | 3.0 | 2.67 | 1.0 | N | 148 | 162 | 1 | 10.0 | 0.5 | 0.5 | 1.0 | 0.0 | 0.3 | 14.8 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:11:53 | 2022-01-09 00:18:44 | 2.0 | 1.5 | 1.0 | N | 68 | 186 | 1 | 7.0 | 3.0 | 0.5 | 2.0 | 0.0 | 0.3 | 12.8 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:09:04 | 2022-01-09 00:24:08 | 1.0 | 2.4 | 1.0 | N | 148 | 158 | 1 | 12.0 | 2.5 | 0.5 | 2.0 | 0.0 | 0.3 | 17.3 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:28:16 | 2022-01-09 00:37:34 | 2.0 | 1.2 | 1.0 | N | 158 | 114 | 1 | 7.5 | 2.5 | 0.5 | 2.15 | 0.0 | 0.3 | 12.95 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:39:36 | 2022-01-09 00:49:36 | 1.0 | 2.6 | 1.0 | N | 114 | 170 | 2 | 10.0 | 2.5 | 0.5 | 0.0 | 0.0 | 0.3 | 13.3 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:53:28 | 2022-01-09 00:59:43 | 2.0 | 1.2 | 1.0 | N | 137 | 161 | 1 | 6.5 | 2.5 | 0.5 | 3.0 | 0.0 | 0.3 | 12.8 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:19:35 | 2022-01-09 00:26:20 | 1.0 | 0.98 | 1.0 | N | 231 | 87 | 2 | 6.0 | 0.5 | 0.5 | 0.0 | 0.0 | 0.3 | 9.8 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:50:03 | 2022-01-09 00:55:59 | 1.0 | 0.97 | 1.0 | N | 249 | 211 | 1 | 6.0 | 0.5 | 0.5 | 1.96 | 0.0 | 0.3 | 11.76 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:18:57 | 2022-01-09 00:24:35 | 1.0 | 1.49 | 1.0 | N | 143 | 50 | 1 | 6.5 | 0.5 | 0.5 | 2.06 | 0.0 | 0.3 | 12.36 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:29:13 | 2022-01-09 00:36:00 | 1.0 | 1.92 | 1.0 | N | 142 | 151 | 1 | 8.0 | 0.5 | 0.5 | 2.95 | 0.0 | 0.3 | 14.75 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:05:47 | 2022-01-09 00:23:31 | 1.0 | 7.8 | 1.0 | N | 232 | 151 | 1 | 24.0 | 0.5 | 0.5 | 5.56 | 0.0 | 0.3 | 33.36 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:39:21 | 2022-01-09 00:40:45 | 1.0 | 0.1 | 1.0 | N | 50 | 50 | 3 | -3.0 | -0.5 | -0.5 | 0.0 | 0.0 | -0.3 | -6.8 | -2.5 | 0.0 |
| 2 | 2022-01-09 00:39:21 | 2022-01-09 00:40:45 | 1.0 | 0.1 | 1.0 | N | 50 | 50 | 2 | 3.0 | 0.5 | 0.5 | 0.0 | 0.0 | 0.3 | 6.8 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:50:55 | 2022-01-09 00:53:15 | 1.0 | 0.69 | 1.0 | N | 48 | 143 | 2 | 4.5 | 0.5 | 0.5 | 0.0 | 0.0 | 0.3 | 8.3 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:24:45 | 2022-01-09 00:28:48 | 1.0 | 0.88 | 1.0 | N | 166 | 151 | 1 | 5.5 | 0.5 | 0.5 | 1.36 | 0.0 | 0.3 | 8.16 | 0.0 | 0.0 |
| 1 | 2022-01-09 00:50:30 | 2022-01-09 01:00:04 | 1.0 | 1.7 | 1.0 | N | 233 | 113 | 1 | 9.0 | 3.0 | 0.5 | 3.2 | 0.0 | 0.3 | 16.0 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:04:37 | 2022-01-09 00:08:37 | 1.0 | 0.49 | 1.0 | N | 114 | 113 | 1 | 4.5 | 0.5 | 0.5 | 1.66 | 0.0 | 0.3 | 9.96 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:17:15 | 2022-01-09 00:26:27 | 1.0 | 2.06 | 1.0 | N | 107 | 48 | 1 | 9.0 | 0.5 | 0.5 | 2.56 | 0.0 | 0.3 | 15.36 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:31:03 | 2022-01-09 00:40:10 | 1.0 | 1.39 | 1.0 | N | 68 | 186 | 1 | 8.0 | 0.5 | 0.5 | 2.36 | 0.0 | 0.3 | 14.16 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:50:16 | 2022-01-09 00:54:23 | 1.0 | 0.95 | 1.0 | N | 186 | 48 | 2 | 5.0 | 0.5 | 0.5 | 0.0 | 0.0 | 0.3 | 8.8 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:19:51 | 2022-01-09 00:31:30 | 1.0 | 3.8 | 1.0 | N | 148 | 140 | 2 | 13.0 | 3.0 | 0.5 | 0.0 | 0.0 | 0.3 | 16.8 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:57:05 | 2022-01-09 01:20:48 | 1.0 | 6.4 | 1.0 | Y | 249 | 41 | 1 | 22.0 | 3.0 | 0.5 | 5.15 | 0.0 | 0.3 | 30.95 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:10:05 | 2022-01-09 00:15:50 | 1.0 | 1.13 | 1.0 | N | 263 | 236 | 1 | 6.5 | 0.5 | 0.5 | 2.06 | 0.0 | 0.3 | 12.36 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:21:19 | 2022-01-09 00:27:09 | 1.0 | 1.2 | 1.0 | N | 236 | 141 | 1 | 6.5 | 0.5 | 0.5 | 2.06 | 0.0 | 0.3 | 12.36 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:32:03 | 2022-01-09 00:40:38 | 1.0 | 2.51 | 1.0 | N | 237 | 164 | 1 | 9.5 | 0.5 | 0.5 | 2.66 | 0.0 | 0.3 | 15.96 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:47:26 | 2022-01-09 01:05:00 | 1.0 | 5.48 | 1.0 | N | 164 | 87 | 1 | 19.5 | 0.5 | 0.5 | 4.66 | 0.0 | 0.3 | 27.96 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:10:34 | 2022-01-09 00:38:59 | 2.0 | 9.7 | 1.0 | N | 236 | 231 | 2 | 31.5 | 0.5 | 0.5 | 0.0 | 0.0 | 0.3 | 35.3 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:48:08 | 2022-01-09 01:02:33 | 2.0 | 3.33 | 1.0 | N | 144 | 48 | 1 | 13.0 | 0.5 | 0.5 | 3.36 | 0.0 | 0.3 | 20.16 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:23:04 | 2022-01-09 00:53:14 | 1.0 | 18.2 | 2.0 | N | 132 | 90 | 1 | 52.0 | 3.75 | 0.5 | 10.0 | 6.55 | 0.3 | 73.1 | 2.5 | 1.25 |
| 2 | 2022-01-09 00:52:29 | 2022-01-09 01:02:11 | 1.0 | 4.37 | 1.0 | N | 132 | 139 | 2 | 14.0 | 0.5 | 0.5 | 0.0 | 0.0 | 0.3 | 16.55 | 0.0 | 1.25 |
| 2 | 2022-01-09 00:31:15 | 2022-01-09 00:38:14 | 1.0 | 0.96 | 1.0 | N | 162 | 48 | 1 | 6.5 | 0.5 | 0.5 | 2.06 | 0.0 | 0.3 | 12.36 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:40:19 | 2022-01-09 00:43:24 | 3.0 | 0.54 | 1.0 | N | 50 | 48 | 1 | 4.0 | 0.5 | 0.5 | 1.56 | 0.0 | 0.3 | 9.36 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:45:08 | 2022-01-09 01:00:04 | 1.0 | 3.31 | 1.0 | N | 48 | 79 | 1 | 13.0 | 0.5 | 0.5 | 4.2 | 0.0 | 0.3 | 21.0 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:16:16 | 2022-01-09 00:20:44 | 1.0 | 0.9 | 1.0 | N | 142 | 48 | 1 | 5.5 | 0.5 | 0.5 | 1.86 | 0.0 | 0.3 | 11.16 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:33:27 | 2022-01-09 00:37:11 | 1.0 | 0.87 | 1.0 | N | 141 | 229 | 1 | 5.0 | 0.5 | 0.5 | 1.76 | 0.0 | 0.3 | 10.56 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:40:05 | 2022-01-09 00:44:25 | 1.0 | 0.76 | 1.0 | N | 229 | 162 | 1 | 5.0 | 0.5 | 0.5 | 1.76 | 0.0 | 0.3 | 10.56 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:40:11 | 2022-01-10 00:31:17 | 5.0 | 7.1 | 1.0 | N | 232 | 238 | 1 | 26.5 | 0.5 | 0.5 | 0.0 | 0.0 | 0.3 | 30.3 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:29:04 | 2022-01-09 00:35:14 | 2.0 | 2.13 | 1.0 | N | 114 | 170 | 2 | 8.0 | 0.5 | 0.5 | 0.0 | 0.0 | 0.3 | 11.8 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:57:41 | 2022-01-09 01:25:06 | 1.0 | 19.14 | 2.0 | N | 132 | 79 | 2 | 52.0 | 0.0 | 0.5 | 0.0 | 0.0 | 0.3 | 56.55 | 2.5 | 1.25 |
| 1 | 2022-01-09 00:07:39 | 2022-01-09 00:12:13 | 2.0 | 1.0 | 1.0 | N | 79 | 144 | 1 | 5.5 | 3.0 | 0.5 | 2.3 | 0.0 | 0.3 | 11.6 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:18:52 | 2022-01-09 00:23:16 | 3.0 | 0.9 | 1.0 | N | 113 | 79 | 1 | 5.0 | 3.0 | 0.5 | 1.75 | 0.0 | 0.3 | 10.55 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:25:43 | 2022-01-09 00:38:45 | 2.0 | 3.5 | 1.0 | N | 107 | 66 | 1 | 13.5 | 3.0 | 0.5 | 1.5 | 0.0 | 0.3 | 18.8 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:46:46 | 2022-01-09 00:55:51 | 1.0 | 2.6 | 1.0 | N | 125 | 246 | 1 | 10.0 | 3.0 | 0.5 | 2.75 | 0.0 | 0.3 | 16.55 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:57:19 | 2022-01-09 01:28:29 | 1.0 | 12.4 | 1.0 | N | 132 | 61 | 1 | 36.5 | 1.75 | 0.5 | 9.75 | 0.0 | 0.3 | 48.8 | 0.0 | 1.25 |
| 2 | 2022-01-09 00:29:25 | 2022-01-09 00:30:26 | 1.0 | 0.36 | 1.0 | N | 48 | 48 | 1 | 3.0 | 0.5 | 0.5 | 1.36 | 0.0 | 0.3 | 8.16 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:31:32 | 2022-01-09 00:36:47 | 1.0 | 1.42 | 1.0 | N | 48 | 246 | 2 | 6.0 | 0.5 | 0.5 | 0.0 | 0.0 | 0.3 | 9.8 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:39:11 | 2022-01-09 00:45:43 | 1.0 | 1.63 | 1.0 | N | 263 | 74 | 2 | 7.0 | 0.5 | 0.5 | 0.0 | 0.0 | 0.3 | 10.8 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:02:59 | 2022-01-09 00:15:56 | 2.0 | 3.0 | 1.0 | N | 234 | 209 | 1 | 11.5 | 3.0 | 0.5 | 2.0 | 0.0 | 0.3 | 17.3 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:20:25 | 2022-01-09 00:25:16 | 1.0 | 1.1 | 1.0 | N | 261 | 45 | 1 | 6.0 | 3.0 | 0.5 | 1.95 | 0.0 | 0.3 | 11.75 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:32:10 | 2022-01-09 00:33:37 | 2.0 | 0.5 | 1.0 | N | 114 | 113 | 1 | 3.5 | 3.0 | 0.5 | 1.45 | 0.0 | 0.3 | 8.75 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:37:04 | 2022-01-09 00:38:07 | 1.0 | 0.0 | 1.0 | N | 107 | 107 | 4 | 3.0 | 3.0 | 0.5 | 0.0 | 0.0 | 0.3 | 6.8 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:41:19 | 2022-01-09 00:44:59 | 1.0 | 1.2 | 1.0 | N | 137 | 233 | 1 | 5.5 | 3.0 | 0.5 | 1.0 | 0.0 | 0.3 | 10.3 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:13:56 | 2022-01-09 00:27:02 | 1.0 | 3.62 | 1.0 | N | 234 | 263 | 1 | 13.0 | 0.5 | 0.5 | 3.36 | 0.0 | 0.3 | 20.16 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:27:50 | 2022-01-09 00:42:28 | 1.0 | 6.37 | 1.0 | N | 142 | 244 | 1 | 20.0 | 0.5 | 0.5 | 4.76 | 0.0 | 0.3 | 28.56 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:16:47 | 2022-01-09 00:26:42 | 1.0 | 2.3 | 1.0 | N | 249 | 230 | 1 | 9.5 | 3.0 | 0.5 | 2.65 | 0.0 | 0.3 | 15.95 | 2.5 | 0.0 |
| 1 | 2022-01-09 00:32:03 | 2022-01-09 00:39:40 | 2.0 | 1.5 | 1.0 | N | 163 | 237 | 1 | 8.0 | 3.0 | 0.5 | 2.95 | 0.0 | 0.3 | 14.75 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:34:58 | 2022-01-09 00:43:31 | 1.0 | 1.67 | 1.0 | N | 166 | 41 | 2 | 8.0 | 0.5 | 0.5 | 0.0 | 0.0 | 0.3 | 9.3 | 0.0 | 0.0 |
| 2 | 2022-01-09 00:36:53 | 2022-01-09 00:37:05 | 2.0 | 0.0 | 5.0 | N | 148 | 148 | 1 | 40.0 | 0.0 | 0.0 | 8.56 | 0.0 | 0.3 | 51.36 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:37:46 | 2022-01-09 00:48:28 | 1.0 | 2.28 | 1.0 | N | 148 | 186 | 1 | 10.0 | 0.5 | 0.5 | 2.76 | 0.0 | 0.3 | 16.56 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:59:42 | 2022-01-09 01:05:21 | 1.0 | 1.02 | 1.0 | N | 249 | 90 | 1 | 6.0 | 0.5 | 0.5 | 4.0 | 0.0 | 0.3 | 13.8 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:41:37 | 2022-01-09 01:02:36 | 1.0 | 6.4 | 1.0 | N | 137 | 166 | 1 | 20.5 | 0.5 | 0.5 | 4.86 | 0.0 | 0.3 | 29.16 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:27:48 | 2022-01-09 00:48:18 | 1.0 | 8.49 | 1.0 | N | 132 | 86 | 2 | 25.5 | 0.5 | 0.5 | 0.0 | 0.0 | 0.3 | 28.05 | 0.0 | 1.25 |
| 2 | 2022-01-09 00:02:36 | 2022-01-09 00:07:45 | 1.0 | 2.01 | 1.0 | N | 263 | 229 | 1 | 7.5 | 0.5 | 0.5 | 2.26 | 0.0 | 0.3 | 13.56 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:06:43 | 2022-01-09 00:41:00 | 1.0 | 18.61 | 2.0 | N | 132 | 90 | 1 | 52.0 | 0.0 | 0.5 | 18.55 | 6.55 | 0.3 | 81.65 | 2.5 | 1.25 |
| 2 | 2022-01-09 00:47:17 | 2022-01-09 00:57:33 | 1.0 | 1.43 | 1.0 | N | 249 | 144 | 1 | 8.0 | 0.5 | 0.5 | 1.08 | 0.0 | 0.3 | 12.88 | 2.5 | 0.0 |
| 2 | 2022-01-09 00:24:57 | 2022-01-09 00:26:01 | 1.0 | 0.37 | 1.0 | N | 249 | 90 | 1 | 3.0 | 0.5 | 0.5 | 1.36 | 0.0 | 0.3 | 8.16 | 2.5 | 0.0 |
Create a view¶
Let's create an Iceberg view to look at the longest distances travelled and the total amount of the trips.
In [11]:
Copied!
%%sql
CREATE VIEW nyc.long_distances (
vendor_id COMMENT 'Vendor ID',
pickup_date,
dropoff_date,
distance COMMENT 'Trip Distance',
total COMMENT 'Total amount'
)
AS SELECT
VendorID,
tpep_pickup_datetime,
tpep_dropoff_datetime,
trip_distance,
total_amount
FROM nyc.taxis
ORDER BY trip_distance
%%sql
CREATE VIEW nyc.long_distances (
vendor_id COMMENT 'Vendor ID',
pickup_date,
dropoff_date,
distance COMMENT 'Trip Distance',
total COMMENT 'Total amount'
)
AS SELECT
VendorID,
tpep_pickup_datetime,
tpep_dropoff_datetime,
trip_distance,
total_amount
FROM nyc.taxis
ORDER BY trip_distance
Out[11]:
In [12]:
Copied!
%%sql
SELECT * FROM nyc.long_distances
%%sql
SELECT * FROM nyc.long_distances
Out[12]:
| vendor_id | pickup_date | dropoff_date | distance | total |
|---|---|---|---|---|
| 1 | 2022-01-16 01:00:55 | 2022-01-16 01:21:30 | 0.0 | 38.98 |
| 2 | 2022-01-21 02:21:34 | 2022-01-21 02:21:42 | 0.0 | 62.8 |
| 2 | 2022-01-19 06:08:05 | 2022-01-19 06:08:08 | 0.0 | 3.3 |
| 2 | 2022-01-21 06:43:11 | 2022-01-21 06:44:37 | 0.0 | 18.6 |
| 1 | 2022-01-20 03:03:52 | 2022-01-20 03:04:22 | 0.0 | 40.35 |
| 1 | 2022-01-21 00:27:47 | 2022-01-21 01:00:56 | 0.0 | 42.0 |
| 2 | 2022-01-19 08:39:19 | 2022-01-19 08:39:21 | 0.0 | 66.96 |
| 2 | 2022-01-21 02:35:03 | 2022-01-21 02:35:20 | 0.0 | 27.36 |
| 1 | 2022-01-10 06:18:18 | 2022-01-10 06:25:42 | 0.0 | 11.6 |
| 2 | 2022-01-21 00:18:35 | 2022-01-21 00:19:10 | 0.0 | 65.3 |
| 1 | 2022-01-19 06:02:03 | 2022-01-19 06:02:33 | 0.0 | 5.8 |
| 1 | 2022-01-21 02:05:08 | 2022-01-21 02:45:46 | 0.0 | 47.55 |
| 1 | 2022-01-20 07:19:26 | 2022-01-20 07:36:43 | 0.0 | 26.0 |
| 1 | 2022-01-21 00:45:57 | 2022-01-21 00:46:33 | 0.0 | 10.3 |
| 2 | 2022-01-19 06:38:36 | 2022-01-19 06:38:59 | 0.0 | 3.3 |
| 2 | 2022-01-21 02:01:24 | 2022-01-21 02:01:27 | 0.0 | 74.12 |
| 2 | 2022-01-03 04:17:04 | 2022-01-03 04:17:08 | 0.0 | 50.3 |
| 2 | 2022-01-21 00:09:33 | 2022-01-21 00:17:00 | 0.0 | 11.76 |
| 1 | 2022-01-19 06:34:39 | 2022-01-19 07:16:24 | 0.0 | 62.55 |
| 1 | 2022-01-21 02:04:33 | 2022-01-21 02:04:33 | 0.0 | 6.3 |
| 2 | 2022-01-20 03:25:39 | 2022-01-20 03:25:42 | 0.0 | 12.36 |
| 1 | 2022-01-21 00:52:08 | 2022-01-21 00:52:14 | 0.0 | 3.8 |
| 2 | 2022-01-19 00:53:38 | 2022-01-19 17:07:30 | 0.0 | 6.8 |
| 1 | 2022-01-21 06:56:41 | 2022-01-21 07:11:09 | 0.0 | 30.0 |
| 1 | 2022-01-10 06:47:47 | 2022-01-10 06:58:27 | 0.0 | 11.8 |
| 2 | 2022-01-21 00:19:21 | 2022-01-21 00:46:42 | 0.0 | 23.76 |
| 2 | 2022-01-19 06:10:42 | 2022-01-19 06:10:59 | 0.0 | -60.6 |
| 2 | 2022-01-21 00:02:52 | 2022-01-21 00:03:33 | 0.0 | 99.36 |
| 1 | 2022-01-20 03:41:36 | 2022-01-20 04:04:59 | 0.0 | 29.0 |
| 2 | 2022-01-21 00:49:19 | 2022-01-21 00:49:29 | 0.0 | 8.3 |
| 1 | 2022-01-19 00:10:23 | 2022-01-19 00:45:40 | 0.0 | 48.0 |
| 1 | 2022-01-21 00:57:47 | 2022-01-21 01:11:16 | 0.0 | 18.0 |
| 2 | 2022-01-22 01:32:29 | 2022-01-22 01:44:19 | 0.0 | 12.98 |
| 1 | 2022-01-21 00:00:31 | 2022-01-21 00:06:37 | 0.0 | 18.28 |
| 2 | 2022-01-19 06:10:42 | 2022-01-19 06:10:59 | 0.0 | 60.6 |
| 1 | 2022-01-21 02:24:36 | 2022-01-21 02:24:36 | 0.0 | 6.3 |
| 2 | 2022-01-20 01:12:01 | 2022-01-20 01:12:06 | 0.0 | 52.3 |
| 2 | 2022-01-21 00:17:41 | 2022-01-21 00:17:46 | 0.0 | 6.3 |
| 1 | 2022-01-19 00:20:54 | 2022-01-19 00:39:37 | 0.0 | 55.3 |
| 2 | 2022-01-21 02:35:20 | 2022-01-21 02:35:28 | 0.0 | 147.96 |
| 2 | 2022-01-10 02:49:06 | 2022-01-10 02:49:09 | 0.0 | 45.0 |
| 2 | 2022-01-21 00:08:44 | 2022-01-21 00:08:53 | 0.0 | 78.36 |
| 2 | 2022-01-19 06:12:08 | 2022-01-19 06:13:21 | 0.0 | -60.6 |
| 2 | 2022-01-21 02:53:56 | 2022-01-21 02:54:00 | 0.0 | 11.3 |
| 2 | 2022-01-20 03:55:40 | 2022-01-20 03:55:43 | 0.0 | 45.3 |
| 2 | 2022-01-21 00:55:53 | 2022-01-21 01:07:28 | 0.0 | 13.8 |
| 1 | 2022-01-19 00:35:43 | 2022-01-19 01:11:46 | 0.0 | 81.63 |
| 1 | 2022-01-21 02:21:01 | 2022-01-21 02:21:10 | 0.0 | 5.05 |
| 1 | 2022-01-03 07:00:14 | 2022-01-03 07:29:31 | 0.0 | 34.38 |
| 2 | 2022-01-21 00:41:09 | 2022-01-21 00:41:17 | 0.0 | 3.8 |
| 2 | 2022-01-19 06:12:08 | 2022-01-19 06:13:21 | 0.0 | 60.6 |
| 1 | 2022-01-21 02:50:31 | 2022-01-21 02:51:57 | 0.0 | 6.3 |
| 2 | 2022-01-20 00:24:11 | 2022-01-20 00:28:17 | 0.0 | 9.96 |
| 1 | 2022-01-21 00:22:03 | 2022-01-21 00:47:38 | 0.0 | 27.45 |
| 2 | 2022-01-19 00:05:43 | 2022-01-19 00:05:48 | 0.0 | 6.3 |
| 2 | 2022-01-21 02:24:13 | 2022-01-21 02:24:21 | 0.0 | -5.05 |
| 1 | 2022-01-10 06:01:43 | 2022-01-10 06:34:35 | 0.0 | 50.55 |
| 2 | 2022-01-21 00:44:58 | 2022-01-21 00:45:09 | 0.0 | 4.45 |
| 2 | 2022-01-19 00:16:41 | 2022-01-19 00:16:47 | 0.0 | 8.16 |
| 2 | 2022-01-21 02:24:13 | 2022-01-21 02:24:21 | 0.0 | 5.05 |
| 2 | 2022-01-20 03:08:05 | 2022-01-20 03:08:14 | 0.0 | 18.12 |
| 2 | 2022-01-21 00:19:44 | 2022-01-21 00:20:08 | 0.0 | 100.0 |
| 1 | 2022-01-19 08:15:54 | 2022-01-19 08:17:19 | 0.0 | 6.8 |
| 2 | 2022-01-21 03:41:05 | 2022-01-21 03:41:16 | 0.0 | 17.16 |
| 2 | 2022-01-31 04:44:24 | 2022-01-31 04:45:42 | 0.0 | 90.3 |
| 1 | 2022-01-21 00:45:43 | 2022-01-21 00:46:02 | 0.0 | 0.31 |
| 2 | 2022-01-19 06:09:19 | 2022-01-19 06:09:29 | 0.0 | 76.55 |
| 2 | 2022-01-21 03:49:38 | 2022-01-21 03:49:48 | 0.0 | 3.8 |
| 2 | 2022-01-20 00:47:03 | 2022-01-20 00:48:27 | 0.0 | 13.3 |
| 1 | 2022-01-21 00:23:41 | 2022-01-21 00:24:39 | 0.0 | 39.35 |
| 2 | 2022-01-19 06:34:25 | 2022-01-19 06:34:42 | 0.0 | 35.38 |
| 2 | 2022-01-21 03:09:22 | 2022-01-21 03:09:34 | 0.0 | 75.3 |
| 2 | 2022-01-10 00:29:42 | 2022-01-10 00:30:25 | 0.0 | 77.05 |
| 2 | 2022-01-21 01:16:27 | 2022-01-21 01:16:39 | 0.0 | 39.39 |
| 1 | 2022-01-19 02:51:27 | 2022-01-19 03:04:43 | 0.0 | 21.0 |
| 1 | 2022-01-21 03:02:55 | 2022-01-21 03:03:23 | 0.0 | 18.35 |
| 2 | 2022-01-20 03:26:02 | 2022-01-20 03:26:51 | 0.0 | -27.8 |
| 1 | 2022-01-21 01:30:40 | 2022-01-21 01:35:09 | 0.0 | 150.4 |
| 2 | 2022-01-19 00:52:46 | 2022-01-19 00:52:48 | 0.0 | 81.0 |
| 2 | 2022-01-21 03:25:39 | 2022-01-21 03:25:49 | 0.0 | -19.8 |
| 1 | 2022-01-03 04:37:38 | 2022-01-03 05:06:25 | 0.0 | 34.0 |
| 1 | 2022-01-21 01:07:06 | 2022-01-21 01:07:20 | 0.0 | 3.8 |
| 2 | 2022-01-19 02:57:36 | 2022-01-19 02:57:39 | 0.0 | 13.5 |
| 2 | 2022-01-21 03:25:39 | 2022-01-21 03:25:49 | 0.0 | 19.8 |
| 2 | 2022-01-20 00:54:10 | 2022-01-20 00:59:36 | 0.0 | 30.3 |
| 1 | 2022-01-21 01:14:38 | 2022-01-21 01:38:27 | 0.0 | 33.0 |
| 2 | 2022-01-19 00:49:37 | 2022-01-19 00:49:46 | 0.0 | -52.8 |
| 2 | 2022-01-21 03:27:01 | 2022-01-21 03:27:11 | 0.0 | 22.3 |
| 2 | 2022-01-10 06:22:55 | 2022-01-10 06:23:04 | 0.0 | 5.8 |
| 1 | 2022-01-21 01:54:04 | 2022-01-21 01:59:26 | 0.0 | 18.28 |
| 1 | 2022-01-19 00:12:13 | 2022-01-19 00:24:55 | 0.0 | 21.0 |
| 1 | 2022-01-21 03:04:04 | 2022-01-21 03:24:34 | 0.0 | 38.98 |
| 2 | 2022-01-20 03:26:02 | 2022-01-20 03:26:51 | 0.0 | 32.8 |
| 1 | 2022-01-21 01:44:44 | 2022-01-21 01:45:45 | 0.0 | 16.58 |
| 2 | 2022-01-19 00:50:43 | 2022-01-19 00:50:50 | 0.0 | -52.8 |
| 2 | 2022-01-21 01:09:20 | 2022-01-21 01:13:54 | 0.0 | 9.96 |
| 2 | 2022-01-22 03:14:11 | 2022-01-22 03:14:17 | 0.0 | 19.13 |
| 2 | 2022-01-21 01:57:55 | 2022-01-21 02:06:05 | 0.0 | 12.36 |
| 2 | 2022-01-19 02:57:35 | 2022-01-19 02:57:37 | 0.0 | 1.69 |
| 2 | 2022-01-21 03:01:54 | 2022-01-21 03:12:25 | 0.0 | 12.8 |
Update View to order results differently¶
The output isn't as helpful as imagined, so let's update the view and change the order of columns and the ordering of the results.
In [13]:
Copied!
%%sql
CREATE OR REPLACE VIEW nyc.long_distances (
distance COMMENT 'Trip Distance',
total COMMENT 'Total amount',
vendor_id COMMENT 'Vendor ID',
pickup_date,
dropoff_date
)
AS SELECT
trip_distance,
total_amount,
VendorID,
tpep_pickup_datetime,
tpep_dropoff_datetime
FROM nyc.taxis
WHERE trip_distance > 35
ORDER BY total_amount, trip_distance
%%sql
CREATE OR REPLACE VIEW nyc.long_distances (
distance COMMENT 'Trip Distance',
total COMMENT 'Total amount',
vendor_id COMMENT 'Vendor ID',
pickup_date,
dropoff_date
)
AS SELECT
trip_distance,
total_amount,
VendorID,
tpep_pickup_datetime,
tpep_dropoff_datetime
FROM nyc.taxis
WHERE trip_distance > 35
ORDER BY total_amount, trip_distance
Out[13]:
In [14]:
Copied!
%%sql
SELECT * FROM nyc.long_distances
%%sql
SELECT * FROM nyc.long_distances
Out[14]:
| distance | total | vendor_id | pickup_date | dropoff_date |
|---|---|---|---|---|
| 116.91 | -408.85 | 2 | 2022-01-31 22:22:53 | 2022-02-01 00:34:41 |
| 51.16 | -207.48 | 2 | 2022-01-14 19:25:02 | 2022-01-14 20:32:19 |
| 44.83 | -144.6 | 2 | 2022-01-26 17:42:57 | 2022-01-26 19:27:50 |
| 53.05 | -134.85 | 2 | 2022-01-07 15:37:09 | 2022-01-07 16:23:34 |
| 36.69 | -131.6 | 2 | 2022-01-05 16:38:10 | 2022-01-05 18:08:35 |
| 46.67 | -106.65 | 2 | 2022-01-12 21:27:20 | 2022-01-12 22:02:16 |
| 47.01 | -101.6 | 2 | 2022-01-11 06:19:19 | 2022-01-11 07:03:04 |
| 45.06 | -94.1 | 2 | 2022-01-28 18:08:24 | 2022-01-28 18:58:27 |
| 37.15 | -53.55 | 2 | 2022-01-31 19:26:39 | 2022-01-31 20:02:41 |
| 35.9 | 0.3 | 1 | 2022-01-28 19:02:55 | 2022-01-28 19:50:20 |
| 55.0 | 3.3 | 1 | 2022-01-10 10:20:56 | 2022-01-10 10:21:31 |
| 96.0 | 5.8 | 1 | 2022-01-03 07:43:00 | 2022-01-03 07:43:09 |
| 38.3 | 6.85 | 1 | 2022-01-17 07:06:19 | 2022-01-17 07:52:55 |
| 118618.94 | 8.5 | 2 | 2022-01-18 15:51:00 | 2022-01-18 15:55:00 |
| 42.0 | 9.3 | 1 | 2022-01-02 13:42:44 | 2022-01-02 13:48:13 |
| 42.6 | 9.3 | 1 | 2022-01-04 07:24:26 | 2022-01-04 07:27:53 |
| 52.0 | 9.68 | 1 | 2022-01-09 12:54:40 | 2022-01-09 13:03:10 |
| 46.3 | 9.85 | 1 | 2022-01-03 16:00:40 | 2022-01-03 17:08:59 |
| 112219.77 | 10.21 | 2 | 2022-01-15 01:57:00 | 2022-01-15 02:00:00 |
| 35.2 | 10.3 | 1 | 2022-01-01 11:13:24 | 2022-01-01 11:21:31 |
| 35.8 | 10.3 | 1 | 2022-01-12 14:41:33 | 2022-01-12 14:48:55 |
| 38.2 | 10.3 | 1 | 2022-01-08 10:59:50 | 2022-01-08 11:05:00 |
| 44.2 | 10.3 | 1 | 2022-01-03 10:56:29 | 2022-01-03 11:03:48 |
| 37.4 | 10.55 | 1 | 2022-01-06 13:21:54 | 2022-01-06 13:26:15 |
| 49.7 | 10.7 | 1 | 2022-01-08 13:02:27 | 2022-01-08 13:06:16 |
| 35.9 | 10.8 | 1 | 2022-01-11 17:19:16 | 2022-01-11 17:26:54 |
| 39.4 | 10.8 | 1 | 2022-01-12 08:44:26 | 2022-01-12 08:53:56 |
| 45.1 | 10.8 | 1 | 2022-01-06 09:33:54 | 2022-01-06 09:42:06 |
| 45.4 | 10.8 | 1 | 2022-01-07 08:52:31 | 2022-01-07 08:58:30 |
| 46.0 | 10.8 | 1 | 2022-01-11 14:15:35 | 2022-01-11 14:23:15 |
| 37.4 | 11.15 | 1 | 2022-01-02 12:52:36 | 2022-01-02 12:57:12 |
| 47.8 | 11.3 | 1 | 2022-01-08 13:49:49 | 2022-01-08 13:59:43 |
| 49.8 | 11.3 | 1 | 2022-01-01 15:12:31 | 2022-01-01 15:21:20 |
| 51.2 | 11.3 | 1 | 2022-01-14 07:38:48 | 2022-01-14 07:45:00 |
| 601.5 | 11.3 | 1 | 2022-01-07 08:10:45 | 2022-01-07 08:20:30 |
| 39.6 | 11.31 | 1 | 2022-01-16 14:34:00 | 2022-01-16 14:43:03 |
| 38.0 | 11.33 | 1 | 2022-01-04 07:35:51 | 2022-01-04 07:43:25 |
| 41.4 | 11.6 | 1 | 2022-01-02 18:25:30 | 2022-01-02 18:31:42 |
| 47.0 | 11.6 | 1 | 2022-01-08 11:18:45 | 2022-01-08 11:22:10 |
| 107007.93 | 11.61 | 2 | 2022-01-15 18:11:00 | 2022-01-15 18:19:00 |
| 42.8 | 11.75 | 1 | 2022-01-12 15:36:54 | 2022-01-12 15:44:08 |
| 47.6 | 11.75 | 1 | 2022-01-08 09:56:22 | 2022-01-08 10:02:15 |
| 53.7 | 11.8 | 1 | 2022-01-02 11:36:54 | 2022-01-02 11:42:40 |
| 69.8 | 11.8 | 1 | 2022-01-01 16:32:57 | 2022-01-01 16:40:28 |
| 74.9 | 11.85 | 1 | 2022-01-16 10:50:22 | 2022-01-16 10:59:36 |
| 59.1 | 12.0 | 1 | 2022-01-08 10:28:15 | 2022-01-08 10:35:32 |
| 8665.17 | 12.0 | 2 | 2022-01-28 08:41:00 | 2022-01-28 08:48:00 |
| 30650.3 | 12.0 | 2 | 2022-01-15 03:13:00 | 2022-01-15 03:20:00 |
| 46.8 | 12.25 | 1 | 2022-01-04 14:17:13 | 2022-01-04 14:23:24 |
| 54.4 | 12.25 | 1 | 2022-01-11 10:57:34 | 2022-01-11 11:03:05 |
| 38.2 | 12.3 | 1 | 2022-01-04 17:12:47 | 2022-01-04 17:23:23 |
| 40.5 | 12.3 | 1 | 2022-01-10 18:06:52 | 2022-01-10 18:16:47 |
| 46.4 | 12.3 | 1 | 2022-01-05 07:53:49 | 2022-01-05 08:02:02 |
| 51.1 | 12.3 | 1 | 2022-01-03 18:03:05 | 2022-01-03 18:09:43 |
| 51.2 | 12.3 | 1 | 2022-01-12 13:56:53 | 2022-01-12 14:08:31 |
| 52.2 | 12.3 | 1 | 2022-01-16 13:24:12 | 2022-01-16 13:30:51 |
| 53.2 | 12.3 | 1 | 2022-01-03 15:14:54 | 2022-01-03 15:26:10 |
| 39.6 | 12.35 | 1 | 2022-01-08 11:36:31 | 2022-01-08 11:44:36 |
| 41.0 | 12.35 | 1 | 2022-01-14 08:03:02 | 2022-01-14 08:09:34 |
| 50.2 | 12.35 | 1 | 2022-01-05 09:32:42 | 2022-01-05 09:39:06 |
| 53.5 | 12.35 | 1 | 2022-01-02 11:44:30 | 2022-01-02 11:50:48 |
| 40.2 | 12.42 | 1 | 2022-01-14 14:22:26 | 2022-01-14 14:31:38 |
| 48.4 | 12.43 | 1 | 2022-01-03 10:31:49 | 2022-01-03 10:41:34 |
| 43.2 | 12.8 | 1 | 2022-01-08 15:47:14 | 2022-01-08 15:56:15 |
| 43.4 | 12.8 | 1 | 2022-01-05 11:13:57 | 2022-01-05 11:22:43 |
| 52.3 | 12.8 | 1 | 2022-01-15 11:02:47 | 2022-01-15 11:11:58 |
| 72.7 | 12.8 | 1 | 2022-01-10 11:14:53 | 2022-01-10 11:25:27 |
| 53.0 | 12.85 | 1 | 2022-01-16 10:32:39 | 2022-01-16 10:38:44 |
| 40.4 | 12.95 | 1 | 2022-01-07 18:47:56 | 2022-01-07 18:54:49 |
| 59.5 | 12.95 | 1 | 2022-01-11 07:15:54 | 2022-01-11 07:23:13 |
| 62.6 | 12.95 | 1 | 2022-01-01 14:57:30 | 2022-01-01 15:04:19 |
| 38.5 | 12.96 | 1 | 2022-01-09 16:32:54 | 2022-01-09 16:41:49 |
| 52.4 | 12.98 | 1 | 2022-01-06 18:17:05 | 2022-01-06 18:25:23 |
| 50.2 | 13.0 | 1 | 2022-01-16 16:23:20 | 2022-01-16 16:32:49 |
| 62.1 | 13.05 | 1 | 2022-01-08 16:13:13 | 2022-01-08 16:21:51 |
| 100224.11 | 13.28 | 2 | 2022-01-21 05:37:00 | 2022-01-21 05:40:00 |
| 40.5 | 13.3 | 1 | 2022-01-01 15:52:54 | 2022-01-01 16:05:13 |
| 45.9 | 13.3 | 1 | 2022-01-12 11:08:44 | 2022-01-12 11:18:25 |
| 48.5 | 13.3 | 1 | 2022-01-10 17:19:12 | 2022-01-10 17:30:49 |
| 62.3 | 13.3 | 1 | 2022-01-14 15:13:02 | 2022-01-14 15:26:34 |
| 96113.91 | 13.33 | 2 | 2022-01-28 07:55:00 | 2022-01-28 08:05:00 |
| 52.1 | 13.55 | 1 | 2022-01-13 07:52:47 | 2022-01-13 08:02:04 |
| 68.1 | 13.55 | 1 | 2022-01-01 13:15:57 | 2022-01-01 13:23:05 |
| 40.7 | 13.56 | 1 | 2022-01-07 08:12:17 | 2022-01-07 08:22:07 |
| 60.0 | 13.56 | 1 | 2022-01-04 14:04:12 | 2022-01-04 14:13:04 |
| 48.2 | 13.57 | 1 | 2022-01-08 16:24:19 | 2022-01-08 16:33:18 |
| 35595.76 | 13.74 | 2 | 2022-01-29 18:48:00 | 2022-01-29 19:00:00 |
| 67058.18 | 13.77 | 2 | 2022-01-16 08:47:00 | 2022-01-16 08:57:00 |
| 8139.12 | 13.79 | 2 | 2022-01-24 07:39:00 | 2022-01-24 07:43:00 |
| 57.1 | 13.8 | 1 | 2022-01-12 13:33:15 | 2022-01-12 13:47:33 |
| 57.2 | 13.8 | 1 | 2022-01-10 15:43:31 | 2022-01-10 15:55:43 |
| 71.4 | 13.8 | 1 | 2022-01-08 13:29:26 | 2022-01-08 13:42:09 |
| 76869.06 | 13.87 | 2 | 2022-01-27 06:54:00 | 2022-01-27 07:06:00 |
| 82340.33 | 13.96 | 2 | 2022-01-14 15:40:00 | 2022-01-14 15:46:00 |
| 49.5 | 14.15 | 1 | 2022-01-10 15:18:14 | 2022-01-10 15:28:32 |
| 51.2 | 14.15 | 1 | 2022-01-01 16:49:40 | 2022-01-01 16:59:27 |
| 53.5 | 14.15 | 1 | 2022-01-07 15:00:23 | 2022-01-07 15:10:42 |
| 55.8 | 14.15 | 1 | 2022-01-09 14:31:03 | 2022-01-09 14:40:52 |
| 57.9 | 14.15 | 1 | 2022-01-02 18:41:46 | 2022-01-02 18:51:18 |
| 59.5 | 14.15 | 1 | 2022-01-09 13:58:38 | 2022-01-09 14:08:34 |
In [15]:
Copied!
%%sql
SELECT count(*) FROM nyc.long_distances
%%sql
SELECT count(*) FROM nyc.long_distances
Out[15]:
| count(1) |
|---|
| 1340 |
Write a month of data¶
Let's write another month of data and see how the results of the view change
In [16]:
Copied!
df = spark.read.parquet("/home/iceberg/data/yellow_tripdata_2022-02.parquet")
df.writeTo("nyc.taxis").append()
df = spark.read.parquet("/home/iceberg/data/yellow_tripdata_2022-02.parquet")
df.writeTo("nyc.taxis").append()
In [17]:
Copied!
%%sql
SELECT * FROM nyc.long_distances
%%sql
SELECT * FROM nyc.long_distances
Out[17]:
| distance | total | vendor_id | pickup_date | dropoff_date |
|---|---|---|---|---|
| 116.91 | -408.85 | 2 | 2022-01-31 22:22:53 | 2022-02-01 00:34:41 |
| 38.75 | -286.24 | 2 | 2022-02-05 03:25:27 | 2022-02-05 04:26:27 |
| 75.79 | -220.1 | 2 | 2022-02-17 16:05:40 | 2022-02-17 17:49:26 |
| 72.06 | -218.85 | 2 | 2022-02-03 01:16:51 | 2022-02-03 02:22:09 |
| 51.16 | -207.48 | 2 | 2022-01-14 19:25:02 | 2022-01-14 20:32:19 |
| 43.35 | -202.8 | 2 | 2022-02-01 23:31:22 | 2022-02-02 00:24:03 |
| 47.59 | -200.6 | 2 | 2022-02-25 13:07:48 | 2022-02-25 14:19:56 |
| 35.16 | -195.6 | 2 | 2022-02-25 17:27:22 | 2022-02-25 18:25:38 |
| 37.14 | -170.6 | 2 | 2022-02-21 16:44:09 | 2022-02-21 17:51:24 |
| 46.58 | -158.05 | 2 | 2022-02-05 20:53:58 | 2022-02-05 21:42:45 |
| 44.83 | -144.6 | 2 | 2022-01-26 17:42:57 | 2022-01-26 19:27:50 |
| 53.05 | -134.85 | 2 | 2022-01-07 15:37:09 | 2022-01-07 16:23:34 |
| 36.69 | -131.6 | 2 | 2022-01-05 16:38:10 | 2022-01-05 18:08:35 |
| 46.67 | -106.65 | 2 | 2022-01-12 21:27:20 | 2022-01-12 22:02:16 |
| 46.46 | -103.65 | 2 | 2022-02-21 17:24:31 | 2022-02-21 18:12:13 |
| 47.01 | -101.6 | 2 | 2022-01-11 06:19:19 | 2022-01-11 07:03:04 |
| 45.06 | -94.1 | 2 | 2022-01-28 18:08:24 | 2022-01-28 18:58:27 |
| 40.53 | -77.35 | 2 | 2022-02-28 19:11:20 | 2022-02-28 19:53:52 |
| 43.62 | -77.1 | 2 | 2022-02-26 01:26:13 | 2022-02-26 01:56:26 |
| 47.62 | -67.6 | 2 | 2022-02-18 19:29:54 | 2022-02-18 20:16:56 |
| 37.15 | -53.55 | 2 | 2022-01-31 19:26:39 | 2022-01-31 20:02:41 |
| 37.35 | -2.05 | 2 | 2022-02-24 18:46:35 | 2022-02-24 19:47:01 |
| 50.92 | -2.05 | 2 | 2022-02-21 08:53:22 | 2022-02-21 09:40:22 |
| 35.9 | 0.3 | 1 | 2022-01-28 19:02:55 | 2022-01-28 19:50:20 |
| 36.4 | 0.3 | 1 | 2022-02-06 19:56:32 | 2022-02-06 20:46:50 |
| 38.7 | 0.31 | 1 | 2022-02-18 08:55:41 | 2022-02-18 10:16:21 |
| 37.35 | 2.05 | 2 | 2022-02-24 18:46:35 | 2022-02-24 19:47:01 |
| 50.92 | 2.05 | 2 | 2022-02-21 08:53:22 | 2022-02-21 09:40:22 |
| 55.0 | 3.3 | 1 | 2022-01-10 10:20:56 | 2022-01-10 10:21:31 |
| 35.2 | 3.8 | 1 | 2022-02-27 02:38:48 | 2022-02-27 02:38:59 |
| 46.9 | 4.55 | 1 | 2022-02-06 18:46:33 | 2022-02-06 18:46:35 |
| 96.0 | 5.8 | 1 | 2022-01-03 07:43:00 | 2022-01-03 07:43:09 |
| 40.8 | 6.8 | 1 | 2022-02-15 21:01:56 | 2022-02-15 21:04:00 |
| 38.3 | 6.85 | 1 | 2022-01-17 07:06:19 | 2022-01-17 07:52:55 |
| 39.5 | 6.85 | 1 | 2022-02-16 14:14:48 | 2022-02-16 15:05:49 |
| 43.7 | 6.86 | 1 | 2022-02-02 20:14:26 | 2022-02-02 22:04:57 |
| 118618.94 | 8.5 | 2 | 2022-01-18 15:51:00 | 2022-01-18 15:55:00 |
| 42.0 | 9.3 | 1 | 2022-01-02 13:42:44 | 2022-01-02 13:48:13 |
| 42.6 | 9.3 | 1 | 2022-01-04 07:24:26 | 2022-01-04 07:27:53 |
| 52.0 | 9.68 | 1 | 2022-01-09 12:54:40 | 2022-01-09 13:03:10 |
| 46.3 | 9.85 | 1 | 2022-01-03 16:00:40 | 2022-01-03 17:08:59 |
| 112219.77 | 10.21 | 2 | 2022-01-15 01:57:00 | 2022-01-15 02:00:00 |
| 81856.67 | 10.28 | 2 | 2022-02-02 16:20:00 | 2022-02-02 16:25:00 |
| 35.2 | 10.3 | 1 | 2022-01-01 11:13:24 | 2022-01-01 11:21:31 |
| 35.8 | 10.3 | 1 | 2022-01-12 14:41:33 | 2022-01-12 14:48:55 |
| 38.2 | 10.3 | 1 | 2022-01-08 10:59:50 | 2022-01-08 11:05:00 |
| 42.35 | 10.3 | 2 | 2022-02-21 21:59:58 | 2022-02-21 23:35:36 |
| 44.2 | 10.3 | 1 | 2022-01-03 10:56:29 | 2022-01-03 11:03:48 |
| 37.4 | 10.55 | 1 | 2022-01-06 13:21:54 | 2022-01-06 13:26:15 |
| 49.7 | 10.7 | 1 | 2022-01-08 13:02:27 | 2022-01-08 13:06:16 |
| 35.9 | 10.8 | 1 | 2022-01-11 17:19:16 | 2022-01-11 17:26:54 |
| 39.4 | 10.8 | 1 | 2022-01-12 08:44:26 | 2022-01-12 08:53:56 |
| 45.1 | 10.8 | 1 | 2022-01-06 09:33:54 | 2022-01-06 09:42:06 |
| 45.4 | 10.8 | 1 | 2022-01-07 08:52:31 | 2022-01-07 08:58:30 |
| 46.0 | 10.8 | 1 | 2022-01-11 14:15:35 | 2022-01-11 14:23:15 |
| 66047.66 | 11.12 | 2 | 2022-02-14 20:01:00 | 2022-02-14 20:11:00 |
| 37.4 | 11.15 | 1 | 2022-01-02 12:52:36 | 2022-01-02 12:57:12 |
| 47.8 | 11.3 | 1 | 2022-01-08 13:49:49 | 2022-01-08 13:59:43 |
| 49.8 | 11.3 | 1 | 2022-01-01 15:12:31 | 2022-01-01 15:21:20 |
| 51.2 | 11.3 | 1 | 2022-01-14 07:38:48 | 2022-01-14 07:45:00 |
| 601.5 | 11.3 | 1 | 2022-01-07 08:10:45 | 2022-01-07 08:20:30 |
| 39.6 | 11.31 | 1 | 2022-01-16 14:34:00 | 2022-01-16 14:43:03 |
| 38.0 | 11.33 | 1 | 2022-01-04 07:35:51 | 2022-01-04 07:43:25 |
| 41.4 | 11.6 | 1 | 2022-01-02 18:25:30 | 2022-01-02 18:31:42 |
| 47.0 | 11.6 | 1 | 2022-01-08 11:18:45 | 2022-01-08 11:22:10 |
| 107007.93 | 11.61 | 2 | 2022-01-15 18:11:00 | 2022-01-15 18:19:00 |
| 42.8 | 11.75 | 1 | 2022-01-12 15:36:54 | 2022-01-12 15:44:08 |
| 47.6 | 11.75 | 1 | 2022-01-08 09:56:22 | 2022-01-08 10:02:15 |
| 53.7 | 11.8 | 1 | 2022-01-02 11:36:54 | 2022-01-02 11:42:40 |
| 69.8 | 11.8 | 1 | 2022-01-01 16:32:57 | 2022-01-01 16:40:28 |
| 74.9 | 11.85 | 1 | 2022-01-16 10:50:22 | 2022-01-16 10:59:36 |
| 59.1 | 12.0 | 1 | 2022-01-08 10:28:15 | 2022-01-08 10:35:32 |
| 8665.17 | 12.0 | 2 | 2022-01-28 08:41:00 | 2022-01-28 08:48:00 |
| 30650.3 | 12.0 | 2 | 2022-01-15 03:13:00 | 2022-01-15 03:20:00 |
| 40.2 | 12.06 | 1 | 2022-02-18 23:03:12 | 2022-02-19 02:53:39 |
| 46.8 | 12.25 | 1 | 2022-01-04 14:17:13 | 2022-01-04 14:23:24 |
| 54.4 | 12.25 | 1 | 2022-01-11 10:57:34 | 2022-01-11 11:03:05 |
| 38.2 | 12.3 | 1 | 2022-01-04 17:12:47 | 2022-01-04 17:23:23 |
| 40.5 | 12.3 | 1 | 2022-01-10 18:06:52 | 2022-01-10 18:16:47 |
| 46.4 | 12.3 | 1 | 2022-01-05 07:53:49 | 2022-01-05 08:02:02 |
| 51.1 | 12.3 | 1 | 2022-01-03 18:03:05 | 2022-01-03 18:09:43 |
| 51.2 | 12.3 | 1 | 2022-01-12 13:56:53 | 2022-01-12 14:08:31 |
| 52.2 | 12.3 | 1 | 2022-01-16 13:24:12 | 2022-01-16 13:30:51 |
| 53.2 | 12.3 | 1 | 2022-01-03 15:14:54 | 2022-01-03 15:26:10 |
| 39.6 | 12.35 | 1 | 2022-01-08 11:36:31 | 2022-01-08 11:44:36 |
| 41.0 | 12.35 | 1 | 2022-01-14 08:03:02 | 2022-01-14 08:09:34 |
| 50.2 | 12.35 | 1 | 2022-01-05 09:32:42 | 2022-01-05 09:39:06 |
| 53.5 | 12.35 | 1 | 2022-01-02 11:44:30 | 2022-01-02 11:50:48 |
| 40.2 | 12.42 | 1 | 2022-01-14 14:22:26 | 2022-01-14 14:31:38 |
| 48.4 | 12.43 | 1 | 2022-01-03 10:31:49 | 2022-01-03 10:41:34 |
| 43.2 | 12.8 | 1 | 2022-01-08 15:47:14 | 2022-01-08 15:56:15 |
| 43.4 | 12.8 | 1 | 2022-01-05 11:13:57 | 2022-01-05 11:22:43 |
| 52.3 | 12.8 | 1 | 2022-01-15 11:02:47 | 2022-01-15 11:11:58 |
| 72.7 | 12.8 | 1 | 2022-01-10 11:14:53 | 2022-01-10 11:25:27 |
| 7814.79 | 12.84 | 2 | 2022-02-11 07:17:00 | 2022-02-11 07:23:00 |
| 53.0 | 12.85 | 1 | 2022-01-16 10:32:39 | 2022-01-16 10:38:44 |
| 40.4 | 12.95 | 1 | 2022-01-07 18:47:56 | 2022-01-07 18:54:49 |
| 59.5 | 12.95 | 1 | 2022-01-11 07:15:54 | 2022-01-11 07:23:13 |
| 62.6 | 12.95 | 1 | 2022-01-01 14:57:30 | 2022-01-01 15:04:19 |
| 38.5 | 12.96 | 1 | 2022-01-09 16:32:54 | 2022-01-09 16:41:49 |
In [18]:
Copied!
%%sql
SELECT count(*) FROM nyc.long_distances
%%sql
SELECT count(*) FROM nyc.long_distances
Out[18]:
| count(1) |
|---|
| 2477 |
Create another view¶
It appears that there are trips with negative total amounts. Let's display these results in a separate view
In [19]:
Copied!
%%sql
CREATE OR REPLACE VIEW nyc.negative_amounts (
total COMMENT 'Total amount',
distance COMMENT 'Trip Distance',
vendor_id COMMENT 'Vendor ID',
pickup_date,
dropoff_date)
AS SELECT total_amount, trip_distance, VendorID, tpep_pickup_datetime, tpep_dropoff_datetime
FROM nyc.taxis
WHERE total_amount < 0 ORDER BY total_amount
%%sql
CREATE OR REPLACE VIEW nyc.negative_amounts (
total COMMENT 'Total amount',
distance COMMENT 'Trip Distance',
vendor_id COMMENT 'Vendor ID',
pickup_date,
dropoff_date)
AS SELECT total_amount, trip_distance, VendorID, tpep_pickup_datetime, tpep_dropoff_datetime
FROM nyc.taxis
WHERE total_amount < 0 ORDER BY total_amount
Out[19]:
In [20]:
Copied!
%%sql
SELECT * FROM nyc.negative_amounts
%%sql
SELECT * FROM nyc.negative_amounts
Out[20]:
| total | distance | vendor_id | pickup_date | dropoff_date |
|---|---|---|---|---|
| -600.3 | 1.29 | 2 | 2022-02-03 18:35:56 | 2022-02-03 18:38:57 |
| -480.3 | 0.0 | 2 | 2022-01-02 17:18:54 | 2022-01-02 17:19:06 |
| -408.85 | 116.91 | 2 | 2022-01-31 22:22:53 | 2022-02-01 00:34:41 |
| -358.55 | 6.96 | 2 | 2022-02-01 19:32:19 | 2022-02-01 19:49:03 |
| -300.3 | 0.0 | 2 | 2022-02-25 00:52:30 | 2022-02-25 00:52:35 |
| -286.24 | 38.75 | 2 | 2022-02-05 03:25:27 | 2022-02-05 04:26:27 |
| -273.3 | 0.0 | 2 | 2022-02-25 16:53:35 | 2022-02-25 16:53:39 |
| -262.8 | 0.0 | 2 | 2022-02-26 21:14:13 | 2022-02-26 21:17:29 |
| -253.3 | 0.1 | 2 | 2022-01-19 01:39:15 | 2022-01-19 01:43:05 |
| -252.8 | 0.0 | 2 | 2022-02-25 00:22:02 | 2022-02-25 00:27:40 |
| -252.8 | 0.0 | 2 | 2022-02-12 20:14:29 | 2022-02-12 20:14:37 |
| -251.47 | 6.92 | 2 | 2022-01-09 03:43:22 | 2022-01-09 03:55:48 |
| -250.3 | 0.0 | 2 | 2022-02-28 02:58:51 | 2022-02-28 02:59:00 |
| -242.8 | 0.23 | 2 | 2022-01-09 03:01:45 | 2022-01-09 03:07:34 |
| -232.8 | 0.0 | 2 | 2022-01-09 01:04:41 | 2022-01-09 01:04:49 |
| -222.5 | 0.0 | 2 | 2022-02-21 22:04:51 | 2022-02-21 22:04:58 |
| -220.1 | 75.79 | 2 | 2022-02-17 16:05:40 | 2022-02-17 17:49:26 |
| -218.85 | 0.0 | 2 | 2022-02-06 06:58:13 | 2022-02-06 07:05:14 |
| -218.85 | 72.06 | 2 | 2022-02-03 01:16:51 | 2022-02-03 02:22:09 |
| -213.7 | 0.0 | 2 | 2022-01-17 10:41:45 | 2022-01-17 10:43:46 |
| -207.48 | 51.16 | 2 | 2022-01-14 19:25:02 | 2022-01-14 20:32:19 |
| -203.3 | 0.02 | 2 | 2022-02-16 22:28:37 | 2022-02-16 22:29:07 |
| -202.8 | 43.35 | 2 | 2022-02-01 23:31:22 | 2022-02-02 00:24:03 |
| -202.24 | 0.0 | 2 | 2022-01-30 23:16:31 | 2022-01-30 23:16:40 |
| -200.6 | 47.59 | 2 | 2022-02-25 13:07:48 | 2022-02-25 14:19:56 |
| -200.3 | 0.0 | 2 | 2022-02-05 04:43:18 | 2022-02-05 04:43:36 |
| -200.3 | 0.0 | 2 | 2022-02-05 04:45:10 | 2022-02-05 04:45:18 |
| -199.8 | 0.96 | 2 | 2022-02-21 15:53:02 | 2022-02-21 16:04:15 |
| -195.6 | 35.16 | 2 | 2022-02-25 17:27:22 | 2022-02-25 18:25:38 |
| -190.3 | 17.94 | 2 | 2022-01-20 01:13:17 | 2022-01-20 01:29:08 |
| -177.8 | 0.35 | 2 | 2022-01-27 23:06:05 | 2022-01-27 23:09:48 |
| -175.8 | 22.67 | 2 | 2022-01-22 20:43:32 | 2022-01-22 21:35:34 |
| -170.6 | 25.87 | 2 | 2022-01-14 16:51:34 | 2022-01-14 17:57:44 |
| -170.6 | 37.14 | 2 | 2022-02-21 16:44:09 | 2022-02-21 17:51:24 |
| -164.55 | 0.13 | 2 | 2022-02-26 04:10:26 | 2022-02-26 04:10:51 |
| -161.55 | 0.0 | 2 | 2022-01-19 18:57:19 | 2022-01-19 18:58:04 |
| -161.55 | 26.88 | 2 | 2022-01-21 20:25:13 | 2022-01-21 21:01:03 |
| -160.3 | 0.0 | 2 | 2022-02-11 19:27:39 | 2022-02-11 19:27:54 |
| -158.05 | 46.58 | 2 | 2022-02-05 20:53:58 | 2022-02-05 21:42:45 |
| -155.3 | 1.2 | 2 | 2022-01-31 17:49:07 | 2022-01-31 17:51:49 |
| -155.0 | 0.06 | 2 | 2022-01-28 05:13:22 | 2022-01-28 05:15:48 |
| -153.3 | 0.73 | 2 | 2022-02-18 12:07:54 | 2022-02-18 12:16:42 |
| -152.8 | 0.01 | 2 | 2022-02-20 01:39:31 | 2022-02-20 01:48:16 |
| -150.3 | 0.0 | 2 | 2022-02-12 02:50:11 | 2022-02-12 02:52:43 |
| -148.1 | 0.0 | 2 | 2022-01-20 08:08:31 | 2022-01-20 08:08:41 |
| -147.36 | 0.02 | 2 | 2022-01-14 23:40:09 | 2022-01-14 23:40:43 |
| -145.6 | 0.0 | 2 | 2022-01-20 08:22:09 | 2022-01-20 08:22:20 |
| -145.3 | 0.47 | 2 | 2022-02-23 16:44:48 | 2022-02-23 16:45:42 |
| -144.6 | 44.83 | 2 | 2022-01-26 17:42:57 | 2022-01-26 19:27:50 |
| -143.18 | 18.19 | 2 | 2022-01-22 00:28:31 | 2022-01-22 00:57:56 |
| -142.8 | 0.0 | 2 | 2022-01-27 11:01:22 | 2022-01-27 11:01:30 |
| -135.8 | 0.0 | 2 | 2022-01-05 18:26:34 | 2022-01-05 18:26:39 |
| -135.05 | 0.54 | 2 | 2022-01-09 01:56:58 | 2022-01-09 01:57:16 |
| -134.85 | 53.05 | 2 | 2022-01-07 15:37:09 | 2022-01-07 16:23:34 |
| -133.52 | 0.79 | 2 | 2022-01-25 14:29:05 | 2022-01-25 14:33:39 |
| -132.33 | 30.18 | 2 | 2022-01-30 12:27:00 | 2022-01-30 13:12:00 |
| -131.6 | 36.69 | 2 | 2022-01-05 16:38:10 | 2022-01-05 18:08:35 |
| -130.3 | 30.34 | 2 | 2022-01-14 09:18:04 | 2022-01-14 09:52:14 |
| -130.0 | 0.0 | 2 | 2022-02-03 14:19:59 | 2022-02-03 14:20:05 |
| -127.05 | 0.34 | 2 | 2022-01-25 21:43:34 | 2022-01-25 21:44:52 |
| -127.05 | 27.85 | 2 | 2022-02-19 18:40:59 | 2022-02-19 19:21:36 |
| -122.8 | 0.0 | 2 | 2022-02-27 05:10:28 | 2022-02-27 05:10:42 |
| -121.05 | 32.03 | 2 | 2022-02-15 22:03:23 | 2022-02-15 22:39:40 |
| -120.24 | 0.0 | 2 | 2022-02-28 03:04:39 | 2022-02-28 03:04:45 |
| -118.76 | 31.68 | 2 | 2022-02-19 16:31:19 | 2022-02-19 17:19:45 |
| -118.3 | 0.0 | 2 | 2022-01-09 03:03:35 | 2022-01-09 03:05:25 |
| -117.8 | 19.94 | 2 | 2022-01-03 18:28:33 | 2022-01-03 19:12:16 |
| -114.55 | 12.75 | 2 | 2022-01-19 14:34:01 | 2022-01-19 14:58:55 |
| -111.61 | 0.0 | 2 | 2022-02-21 06:05:37 | 2022-02-21 06:06:04 |
| -111.55 | 20.28 | 2 | 2022-02-17 01:31:55 | 2022-02-17 04:04:54 |
| -110.05 | 19.83 | 2 | 2022-01-21 16:37:05 | 2022-01-21 17:38:25 |
| -108.81 | 1.18 | 2 | 2022-01-20 17:16:29 | 2022-01-20 17:23:30 |
| -108.8 | 0.0 | 2 | 2022-02-27 14:57:18 | 2022-02-27 14:57:23 |
| -108.35 | 26.1 | 2 | 2022-01-22 10:03:44 | 2022-01-22 10:48:46 |
| -107.3 | 0.0 | 2 | 2022-01-01 15:45:04 | 2022-01-01 15:45:08 |
| -107.3 | 0.0 | 2 | 2022-02-13 19:49:09 | 2022-02-13 19:49:20 |
| -106.65 | 46.67 | 2 | 2022-01-12 21:27:20 | 2022-01-12 22:02:16 |
| -105.85 | 15.88 | 2 | 2022-01-24 14:44:14 | 2022-01-24 15:15:13 |
| -104.8 | 14.42 | 2 | 2022-01-03 17:26:15 | 2022-01-03 17:48:00 |
| -103.65 | 46.46 | 2 | 2022-02-21 17:24:31 | 2022-02-21 18:12:13 |
| -103.3 | 0.0 | 2 | 2022-01-16 03:21:02 | 2022-01-16 03:21:58 |
| -103.3 | 9.52 | 2 | 2022-02-22 12:24:25 | 2022-02-22 15:26:29 |
| -103.1 | 27.13 | 2 | 2022-01-05 06:49:22 | 2022-01-05 08:37:14 |
| -102.8 | 14.33 | 2 | 2022-01-01 16:18:51 | 2022-01-01 16:41:48 |
| -101.6 | 47.01 | 2 | 2022-01-11 06:19:19 | 2022-01-11 07:03:04 |
| -101.55 | 12.42 | 2 | 2022-01-20 14:02:48 | 2022-01-20 14:21:12 |
| -100.3 | 0.0 | 2 | 2022-02-21 12:22:31 | 2022-02-21 12:22:43 |
| -100.29 | 0.0 | 2 | 2022-02-11 03:31:44 | 2022-02-11 03:32:02 |
| -99.55 | 16.17 | 2 | 2022-01-01 05:03:43 | 2022-01-01 05:23:59 |
| -99.55 | 23.29 | 2 | 2022-02-19 03:59:55 | 2022-02-19 04:32:02 |
| -99.3 | 0.07 | 2 | 2022-01-02 18:30:48 | 2022-01-02 18:30:54 |
| -98.55 | 21.23 | 2 | 2022-02-20 23:35:18 | 2022-02-21 00:07:03 |
| -97.6 | 34.07 | 2 | 2022-02-14 09:48:18 | 2022-02-14 10:38:11 |
| -95.8 | 18.75 | 2 | 2022-02-17 17:59:17 | 2022-02-17 18:50:44 |
| -95.8 | 31.89 | 2 | 2022-02-14 01:19:56 | 2022-02-14 02:38:25 |
| -94.95 | 19.17 | 2 | 2022-02-27 16:38:28 | 2022-02-27 17:30:26 |
| -94.9 | 2.23 | 2 | 2022-02-27 14:53:46 | 2022-02-27 15:17:28 |
| -94.55 | 0.0 | 2 | 2022-02-04 00:11:35 | 2022-02-04 00:11:55 |
| -94.3 | 5.6 | 2 | 2022-01-10 23:59:39 | 2022-01-11 02:46:42 |
| -94.1 | 45.06 | 2 | 2022-01-28 18:08:24 | 2022-01-28 18:58:27 |
Listing and describing views¶
In [21]:
Copied!
%%sql
SHOW VIEWS in nyc
%%sql
SHOW VIEWS in nyc
Out[21]:
| namespace | viewName | isTemporary |
|---|---|---|
| nyc | long_distances | False |
| nyc | negative_amounts | False |
In [22]:
Copied!
%%sql
SHOW VIEWS in nyc LIKE '*neg*'
%%sql
SHOW VIEWS in nyc LIKE '*neg*'
Out[22]:
| namespace | viewName | isTemporary |
|---|---|---|
| nyc | negative_amounts | False |
In [23]:
Copied!
%%sql
DESCRIBE nyc.long_distances
%%sql
DESCRIBE nyc.long_distances
Out[23]:
| col_name | data_type | comment |
|---|---|---|
| distance | double | Trip Distance |
| total | double | Total amount |
| vendor_id | bigint | Vendor ID |
| pickup_date | timestamp | |
| dropoff_date | timestamp |
In [24]:
Copied!
%%sql
DESCRIBE EXTENDED nyc.long_distances
%%sql
DESCRIBE EXTENDED nyc.long_distances
Out[24]:
| col_name | data_type | comment |
|---|---|---|
| distance | double | Trip Distance |
| total | double | Total amount |
| vendor_id | bigint | Vendor ID |
| pickup_date | timestamp | |
| dropoff_date | timestamp | |
| # Detailed View Information | ||
| Comment | ||
| View Catalog and Namespace | demo.nyc | |
| View Query Output Columns | [trip_distance, total_amount, VendorID, tpep_pickup_datetime, tpep_dropoff_datetime] | |
| View Properties | ['format-version' = '1', 'location' = 's3://warehouse/nyc/long_distances', 'provider' = 'iceberg'] | |
| Created By | Spark 3.5.5 |
Displaying the CREATE statement of a view¶
In [25]:
Copied!
%%sql
SHOW CREATE TABLE nyc.long_distances
%%sql
SHOW CREATE TABLE nyc.long_distances
Out[25]:
| createtab_stmt |
|---|
| CREATE VIEW demo.nyc.long_distances ( distance COMMENT 'Trip Distance', total COMMENT 'Total amount', vendor_id COMMENT 'Vendor ID', pickup_date, dropoff_date) TBLPROPERTIES ( 'format-version' = '1', 'location' = 's3://warehouse/nyc/long_distances', 'provider' = 'iceberg') AS SELECT trip_distance, total_amount, VendorID, tpep_pickup_datetime, tpep_dropoff_datetime FROM nyc.taxis WHERE trip_distance > 35 ORDER BY total_amount, trip_distance |
Altering and displaying properties of a view¶
This will add a new property and also update the comment of the view. The comment will be shown when describing the view. The end of this section will also remove a property from the view.
In [27]:
Copied!
%%sql
SHOW TBLPROPERTIES nyc.long_distances
%%sql
SHOW TBLPROPERTIES nyc.long_distances
Out[27]:
| key | value |
|---|---|
| location | s3://warehouse/nyc/long_distances |
| provider | iceberg |
| format-version | 1 |
In [28]:
Copied!
%%sql
ALTER VIEW nyc.long_distances SET TBLPROPERTIES ('key1' = 'val1', 'key2' = 'val2', 'comment' = 'This is a view comment')
%%sql
ALTER VIEW nyc.long_distances SET TBLPROPERTIES ('key1' = 'val1', 'key2' = 'val2', 'comment' = 'This is a view comment')
Out[28]:
In [29]:
Copied!
%%sql
SHOW TBLPROPERTIES nyc.long_distances
%%sql
SHOW TBLPROPERTIES nyc.long_distances
Out[29]:
| key | value |
|---|---|
| location | s3://warehouse/nyc/long_distances |
| provider | iceberg |
| key1 | val1 |
| key2 | val2 |
| format-version | 1 |
In [30]:
Copied!
%%sql
DESCRIBE EXTENDED nyc.long_distances
%%sql
DESCRIBE EXTENDED nyc.long_distances
Out[30]:
| col_name | data_type | comment |
|---|---|---|
| distance | double | Trip Distance |
| total | double | Total amount |
| vendor_id | bigint | Vendor ID |
| pickup_date | timestamp | |
| dropoff_date | timestamp | |
| # Detailed View Information | ||
| Comment | This is a view comment | |
| View Catalog and Namespace | demo.nyc | |
| View Query Output Columns | [trip_distance, total_amount, VendorID, tpep_pickup_datetime, tpep_dropoff_datetime] | |
| View Properties | ['format-version' = '1', 'key1' = 'val1', 'key2' = 'val2', 'location' = 's3://warehouse/nyc/long_distances', 'provider' = 'iceberg'] | |
| Created By | Spark 3.5.5 |
In [31]:
Copied!
%%sql
ALTER VIEW nyc.long_distances UNSET TBLPROPERTIES ('key1')
%%sql
ALTER VIEW nyc.long_distances UNSET TBLPROPERTIES ('key1')
Out[31]:
In [32]:
Copied!
%%sql
SHOW TBLPROPERTIES nyc.long_distances
%%sql
SHOW TBLPROPERTIES nyc.long_distances
Out[32]:
| key | value |
|---|---|
| location | s3://warehouse/nyc/long_distances |
| provider | iceberg |
| key2 | val2 |
| format-version | 1 |
In [ ]:
Copied!