![hero](/static-v2/images/blog/select-from-the-browser/hero.jpg) ## Serverless SQL Queries Frontend developers who write SQL have traditionally been required to sequester their SQL queries to a backend server. The popular [adage](https://stackoverflow.com/a/60332563/2044952) has more or less been 'don't run SQL directly from the frontend.' For Splitgraph users, it turns out the old rules no longer apply. Thanks to the [DDN HTTP API](https://www.splitgraph.com/docs/query/ddn-http), the browser can work directly with raw SQL queries. In this post we demonstrate by building a client-side only dashboard with taxi ride data. ## In a nutshell Today we build a 100% client-side JavaScript app. We will: - download official NYC taxi data and publish it to a Splitgraph repo - write some SQL to roll up taxi zone activity across a 24h period - clone and run a SPA to present taxi activity on an interactive map of NYC ## Dashboard goal: NYC Covid recovery followup Whereas we previously focused on [subway rides](./data-driven-app-powered-by-splitgraph), cab rides are also a familiar sight to New Yorkers. Rendering taxi activity on a map of NYC may give us additional indicators into how the Covid recovery is going. I assume you have a Splitgraph account ([need one?](https://www.splitgraph.com/auth/sign_up)), Node.js, and the [sgr](https://www.splitgraph.com/docs/sgr-advanced/getting-started/installation#installation-1) CLI available. ## Download the data Taxi ride datasets are published at [nyc.gov](https://www1.nyc.gov/site/tlc/about/tlc-trip-record-data.page) on a monthly basis. April is the most recent month available at the time of writing, so let's download yellow cab records for April, across 2020, 2021, and 2022. It looks something like this: ![April 2022](/static-v2/images/blog/select-from-the-browser/download.jpg) Note: as of May 2022 nyc.gov started publishing this data in Parquet format, but we need CSV. Thus please convert however you prefer.

parquet2csv.py helper (optional)

A [script](https://github.com/splitgraph/nyctaxis/master/) is available. It depends on `pandas` and `fastparquet`. ```shell pip install pandas fastparquet ```

However you make your CSVs once they're ready, please upload them via the `sgr` CLI. ```shell $ sgr cloud upload $yourSplitgraphUser/nyctaxi yellow_tripdata_2020-04.csv yellow_tripdata_2021-04.csv yellow_tripdata_2022-04.csv ``` ![Upload](/static-v2/images/blog/select-from-the-browser/upload.jpg) Once the CSVs have been ingested your repo should look like this: ![Table](/static-v2/images/blog/select-from-the-browser/table.jpg) Download and publish taxi data to Splitgraph: ✅ ## SQL rollup To populate the taxi zones with a 24 hour sample, let's [roll up](https://github.com/splitgraph/nyctaxis/master/ddn-fetcher.ts#L6) all taxi pickups and drop-offs that occurred during the given day.

Want to see the query?

= '2022-04-01' AND tpep_pickup_datetime <= '2022-04-01'::date + INTERVAL '24 HOURS' GROUP BY "PULocationID" ), dropoffs AS ( SELECT "DOLocationID" as location_id, COUNT("DOLocationID") as dropoff_count FROM "paws/nyctaxi"."yellow_tripdata_2022-04" WHERE tpep_dropoff_datetime >= '2022-04-01' AND tpep_dropoff_datetime <= '2022-04-01'::date + INTERVAL '24 HOURS' GROUP BY "DOLocationID" ), joined AS ( SELECT pickups.location_id, pickup_count, dropoff_count FROM pickups LEFT JOIN dropoffs ON pickups.location_id = dropoffs.location_id ) SELECT * from joined`} layout="hsplit" />

Roll up taxi activity: ✅ ## Clone the React app The [repo](https://www.github.com/splitgraph/nyctaxis) is available on GitHub.

Want to step through the code tag by tag?

- [Initial CRA](https://github.com/splitgraph/nyctaxis/tree/setup-cra) Tag: `setup-cra` - [Add taxi zone data](https://github.com/splitgraph/nyctaxis/tree/taxizone-data) Tag: `taxizone-data` - [Handle fetching + mapping](https://github.com/splitgraph/nyctaxis/tree/fetch-and-map) Tag: `fetch-and-map`

Since we use `mapbox-gl` for the interactive map, get yourself a free API key at [mapbox.com](https://www.mapbox.com/) if you need one. Changing the date and clicking 'load' will cause new data to be fetched and rendered on the app. Clone and run the SPA: ✅ ## Conclusion The DDN HTTP API offers a convenient way to move beyond the old rules of the road, and let frontend developers run queries from their end user's browser. Thanks for reading and keep following the Splitgraph blog for more posts. ##### Image credit [Eduards Ceravs](https://flic.kr/p/b67zX6)

Splitgraph has been acquired by EDB! Read the blog post.

react

SELECT directly from the browser

Product

Support

Company

Community

Splitgraph