Skip to content
Pivot is in early development and is not production ready. See Project status.

Quickstart

Start Pivot, load some data, and run your first queries.

1. Start Pivot

Section titled “1. Start Pivot”

Pivot runs either as a local SQL shell, or as a server that Postgres clients connect to. Choose the option that fits, then where your data lives: an existing Iceberg catalog, or a Pivot datastore in Amazon S3 or Google Cloud Storage.

Best for: trying Pivot on your own machine, with no server to run and no client to install.

Install the Pivot CLI on Linux:

Terminal window
curl https://pivotlake.io | sh

Then open your data:

Give Pivot the catalog’s credential, then open the Iceberg REST catalog:

Terminal window
export PIVOT_ICEBERG_CREDENTIAL=client-id:client-secret
# Or, for a catalog that issues bearer tokens:
# export PIVOT_ICEBERG_TOKEN=your-token


pivot open --kind iceberg https://catalog.example.com/api --warehouse mywarehouse

Each catalog namespace appears as a schema. Table files are read with the credentials the catalog vends; if it vends none, also export your object-store credentials.

Pivot opens an interactive SQL prompt, and your data stays where it is after you exit with \q. See the CLI reference for options, credentials, and shell commands.

2. Load data

Section titled “2. Load data”

As an example, create a table of NYC taxi trips, then fill it three ways: with SQL, from Parquet in object storage, and from raw Arrow IPC data with \copy.

CREATE TABLE trips (
  vendor_id INTEGER,
  pickup_at TIMESTAMP,
  dropoff_at TIMESTAMP,
  passenger_count BIGINT,
  trip_distance DOUBLE,
  pickup_location_id INTEGER,
  dropoff_location_id INTEGER,
  fare_amount DOUBLE,
  tip_amount DOUBLE,
  total_amount DOUBLE
) WITH (sort_by = 'pickup_at');
-- Insert rows with SQL
INSERT INTO trips VALUES
  (2, '2024-01-01 00:57:55', '2024-01-01 01:17:43', 1, 1.72, 186, 79, 17.7, 0.0, 22.7),
  (1, '2024-01-01 00:03:00', '2024-01-01 00:09:36', 1, 1.8, 140, 236, 10.0, 3.75, 18.75);


-- Load January 2024 (about 3 million trips) from Parquet in a public bucket
INSERT INTO trips
SELECT VendorID, tpep_pickup_datetime, tpep_dropoff_datetime, passenger_count,
       trip_distance, PULocationID, DOLocationID, fare_amount, tip_amount,
       total_amount
FROM read_parquet('s3://pivotlake-examples/nyc-taxi/yellow_tripdata_2024-01.parquet');


-- Ingest raw Arrow IPC data from a local file (works in psql too). Download it first:
-- curl -O https://pivotlake-examples.s3.amazonaws.com/nyc-taxi/yellow_tripdata_2024-02_sample.arrow
\copy trips FROM 'yellow_tripdata_2024-02_sample.arrow' WITH (FORMAT arrow)

3. Query

Section titled “3. Query”

Find January’s busiest days and their average tip:

SELECT date_trunc('day', pickup_at) AS day,
       count(*) AS trips,
       CAST(avg(tip_amount) AS DECIMAL(10, 2)) AS avg_tip
FROM trips
WHERE pickup_at >= '2024-01-01' AND pickup_at < '2024-02-01'
GROUP BY day
ORDER BY trips DESC
LIMIT 5;
         day         | trips  | avg_tip
---------------------+--------+---------
 2024-01-27 00:00:00 | 110515 |    3.10
 2024-01-17 00:00:00 | 110365 |    3.35
 2024-01-18 00:00:00 | 110358 |    3.36
 2024-01-25 00:00:00 | 110318 |    3.48
 2024-01-20 00:00:00 | 108768 |    2.92
(5 rows)

Or see how tipping changes with the number of passengers:

SELECT passenger_count,
       count(*) AS trips,
       CAST(avg(tip_amount / NULLIF(fare_amount, 0)) * 100 AS DECIMAL(10, 1)) AS tip_percent
FROM trips
WHERE passenger_count BETWEEN 1 AND 6
GROUP BY passenger_count
ORDER BY passenger_count;
 passenger_count |  trips  | tip_percent
-----------------+---------+-------------
               1 | 2271171 |        23.7
               2 |  416662 |        20.7
               3 |   93344 |        21.0
               4 |   53029 |        18.0
               5 |   34516 |        21.4
               6 |   23040 |        21.5
(6 rows)

Next steps

Section titled “Next steps”

Build from source

Section titled “Build from source”

To build the same executable from the repository:

Terminal window
cargo build --release -p bin --bin pivot

Run the local shell directly from the build output:

Terminal window
./target/release/pivot open ./pivot-data

To run a server from the build output, copy bin/config.example.yaml, adjust its datastore locations, and start Pivot with:

Terminal window
./target/release/pivot server --config pivot.yaml