KuzuDB with yellow_tripdata

ddl.cypher

INSTALL httpfs;
LOAD EXTENSION httpfs;

CREATE NODE TABLE taxi_zone (
    LocationID INT32,
    Borough STRING,
    Zone STRING,
    service_zone STRING,
    PRIMARY KEY (LocationID)
);
COPY taxi_zone FROM "https://d37ci6vzurychx.cloudfront.net/misc/taxi_zone_lookup.csv" (header=true);

CREATE REL TABLE yellow_tripdata (
    FROM taxi_zone TO taxi_zone,
    VendorID INT32,
    tpep_pickup_datetime TIMESTAMP,
    tpep_dropoff_datetime TIMESTAMP,
    passenger_count INT64,
    trip_distance DOUBLE,
    RatecodeID INT64,
    store_and_fwd_flag STRING,
    payment_type INT64,
    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
);

COPY yellow_tripdata FROM (
  LOAD FROM "https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2024-07.parquet"
  RETURN
    PULocationID,
    DOLocationID,
    VendorID,
    tpep_pickup_datetime,
    tpep_dropoff_datetime,
    passenger_count,
    trip_distance,
    RatecodeID,
    store_and_fwd_flag,
    payment_type,
    fare_amount,
    extra,
    mta_tax,
    tip_amount,
    tolls_amount,
    improvement_surcharge,
    total_amount,
    congestion_surcharge,
    Airport_fee
);

match.cypher

MATCH (PU:taxi_zone)-[y:yellow_tripdata]->(DO:taxi_zone)
  WHERE PU.Zone = 'Alphabet City' AND DO.Zone = 'Central Park'
  RETURN y.*;