version: v1
kind: Spicepod
name: federation_example

datasets:
  - from: postgres:public.customers
    name: customers
    params:
      pg_host: localhost
      pg_db: mydb
      pg_user: reader
      pg_pass: ${secrets:PG_PASS}

  - from: s3://analytics-bucket/orders/
    name: orders
    params:
      file_format: parquet
      s3_auth: iam_role

version: v1
kind: Spicepod
name: federation_example

datasets:
  - from: postgres:public.customers
    name: customers
    params:
      pg_host: localhost
      pg_db: mydb
      pg_user: reader
      pg_pass: ${secrets:PG_PASS}

  - from: s3://analytics-bucket/orders/
    name: orders
    params:
      file_format: parquet
      s3_auth: iam_role

-- Join across PostgreSQL and S3 in a single query
SELECT c.name, COUNT(o.order_id) as order_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name
ORDER BY order_count DESC;

-- Join across PostgreSQL and S3 in a single query
SELECT c.name, COUNT(o.order_id) as order_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name
ORDER BY order_count DESC;

curl -X POST http://localhost:8090/v1/sql \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT * FROM my_table LIMIT 10"}'

curl -X POST http://localhost:8090/v1/sql \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT * FROM my_table LIMIT 10"}'

import adbc_driver_flightsql.dbapi

conn = adbc_driver_flightsql.dbapi.connect('grpc://localhost:50051')
cursor = conn.cursor()
cursor.execute("SELECT * FROM my_table LIMIT 10")
result = cursor.fetch_arrow_table()

import adbc_driver_flightsql.dbapi

conn = adbc_driver_flightsql.dbapi.connect('grpc://localhost:50051')
cursor = conn.cursor()
cursor.execute("SELECT * FROM my_table LIMIT 10")
result = cursor.fetch_arrow_table()

spice sql

spice sql

SELECT * FROM my_table LIMIT 10;

SELECT * FROM my_table LIMIT 10;

git clone https://github.com/spiceai/cookbook.git
cd cookbook/federation

git clone https://github.com/spiceai/cookbook.git
cd cookbook/federation

make

make

taxi_zones
------------
        265
(1 row)

taxi_zones
------------
        265
(1 row)

spice login postgres -p postgres

spice login postgres -p postgres

spice run

spice run

spice sql

spice sql

SELECT z.zone,
       z.borough,
       COUNT(*) AS trips,
       ROUND(AVG(t.fare_amount), 2) AS avg_fare,
       ROUND(AVG(t.tip_amount), 2) AS avg_tip
FROM taxi_trips t
JOIN taxi_zones z ON t."PULocationID" = z.location_id
GROUP BY z.zone, z.borough
ORDER BY trips DESC
LIMIT 10;

SELECT z.zone,
       z.borough,
       COUNT(*) AS trips,
       ROUND(AVG(t.fare_amount), 2) AS avg_fare,
       ROUND(AVG(t.tip_amount), 2) AS avg_tip
FROM taxi_trips t
JOIN taxi_zones z ON t."PULocationID" = z.location_id
GROUP BY z.zone, z.borough
ORDER BY trips DESC
LIMIT 10;

+------------------------------+-----------+--------+----------+---------+
|             zone             |  borough  |  trips | avg_fare | avg_tip |
|            varchar           |  varchar  |  int64 |  float64 | float64 |
+------------------------------+-----------+--------+----------+---------+
| JFK Airport                  | Queens    | 145240 | 59.4     | 8.86    |
| Midtown Center               | Manhattan | 143471 | 15.21    | 3.08    |
| Upper East Side South        | Manhattan | 142708 | 12.18    | 2.59    |
| Upper East Side North        | Manhattan | 136465 | 12.71    | 2.64    |
| Midtown East                 | Manhattan | 106717 | 14.79    | 3.02    |
| Times Sq/Theatre District    | Manhattan | 106324 | 17.54    | 3.3     |
| Penn Station/Madison Sq West | Manhattan | 104523 | 15.79    | 3.09    |
| Lincoln Square East          | Manhattan | 104080 | 13.43    | 2.79    |
| LaGuardia Airport            | Queens    | 89533  | 41.46    | 8.67    |
| Upper West Side South        | Manhattan | 88474  | 13.45    | 2.79    |
+------------------------------+-----------+--------+----------+---------+

Time: 4.922428459 seconds. 10 rows.

+------------------------------+-----------+--------+----------+---------+
|             zone             |  borough  |  trips | avg_fare | avg_tip |
|            varchar           |  varchar  |  int64 |  float64 | float64 |
+------------------------------+-----------+--------+----------+---------+
| JFK Airport                  | Queens    | 145240 | 59.4     | 8.86    |
| Midtown Center               | Manhattan | 143471 | 15.21    | 3.08    |
| Upper East Side South        | Manhattan | 142708 | 12.18    | 2.59    |
| Upper East Side North        | Manhattan | 136465 | 12.71    | 2.64    |
| Midtown East                 | Manhattan | 106717 | 14.79    | 3.02    |
| Times Sq/Theatre District    | Manhattan | 106324 | 17.54    | 3.3     |
| Penn Station/Madison Sq West | Manhattan | 104523 | 15.79    | 3.09    |
| Lincoln Square East          | Manhattan | 104080 | 13.43    | 2.79    |
| LaGuardia Airport            | Queens    | 89533  | 41.46    | 8.67    |
| Upper West Side South        | Manhattan | 88474  | 13.45    | 2.79    |
+------------------------------+-----------+--------+----------+---------+

Time: 4.922428459 seconds. 10 rows.

SELECT z.zone,
       z.borough,
       COUNT(*) AS trips,
       ROUND(AVG(t.fare_amount), 2) AS avg_fare,
       ROUND(AVG(t.tip_amount), 2) AS avg_tip
FROM taxi_trips_accelerated t
JOIN taxi_zones_accelerated z ON t."PULocationID" = z.location_id
GROUP BY z.zone, z.borough
ORDER BY trips DESC
LIMIT 10;

SELECT z.zone,
       z.borough,
       COUNT(*) AS trips,
       ROUND(AVG(t.fare_amount), 2) AS avg_fare,
       ROUND(AVG(t.tip_amount), 2) AS avg_tip
FROM taxi_trips_accelerated t
JOIN taxi_zones_accelerated z ON t."PULocationID" = z.location_id
GROUP BY z.zone, z.borough
ORDER BY trips DESC
LIMIT 10;

Time: 0.022001709 seconds. 10 rows.

Time: 0.022001709 seconds. 10 rows.

make clean

make clean