Spice.ai Cloud Platform Catalog Connector

Works with v2.0+

The Spice.ai Cloud Platform Catalog Connector makes querying datasets in the Spice.ai Cloud Platform simple.

This example will show how to connect to public datasets available in the Spice.ai Cloud Platform. Additional public datasets are available in Spicerack (opens in a new tab).

Prerequisites

• A Spice.ai Cloud Platform account (sign up at https://spice.ai (opens in a new tab))
• Spice is installed (see the Getting Started (opens in a new tab) documentation).

Step 1. Create a Spice.ai Cloud Platform account

Sign up for a Spice.ai Cloud Platform account at https://spice.ai (opens in a new tab).

Step 2. Create a new directory and initialize a Spicepod

spice init spice-catalog-demo
cd spice-catalog-demo

Step 3. Login to the Spice.ai Cloud Platform with spice login

Working in the spice-catalog-demo directory, use the Spice CLI to login to the Spice.ai Cloud Platform. A browser window will open to authenticate when executing the spice login command.

spice login

After successfully authenticating, the Spice.ai Cloud Platform API Key and Token will be stored in the spice-catalog-demo working directory .env file. The Spice runtime reads environment variables set in the local working .env file.

Step 4. Add the Spice.ai Cloud Platform Catalog Connector to spicepod.yaml

Add the following configuration to your spicepod.yaml:

catalogs:
  - from: spice.ai/spiceai/tpch
    name: scp
    params:
      spiceai_region: us-east-1

This will register the scp catalog to connect to the spiceai/tpch (opens in a new tab) app and load all available tables.

Step 5. Start the Spice runtime

spice run

Step 6. Query a dataset

spice sql

SELECT * FROM scp.tpch.lineitem LIMIT 10;

Step 7. Explore the available datasets

Use show tables; in the Spice SQL REPL to see the available datasets.

sql> show tables;
+---------------+--------------+--------------+------------+
| table_catalog | table_schema |  table_name  | table_type |
|    varchar    |    varchar   |    varchar   |   varchar  |
+---------------+--------------+--------------+------------+
| scp           | tpch         | orders       | BASE TABLE |
| scp           | tpch         | region       | BASE TABLE |
| scp           | tpch         | part         | BASE TABLE |
| scp           | tpch         | supplier     | BASE TABLE |
| scp           | tpch         | lineitem     | BASE TABLE |
| scp           | tpch         | nation       | BASE TABLE |
| scp           | tpch         | customer     | BASE TABLE |
| scp           | tpch         | partsupp     | BASE TABLE |
| spice         | runtime      | task_history | BASE TABLE |
+---------------+--------------+--------------+------------+

Time: 0.005605209 seconds. 9 rows.

Step 8. Filter the included tables with include

Specify an include filter to limit the tables registered in the catalog.

catalogs:
  - from: spice.ai/spiceai/tpch
    name: scp
    params:
      spiceai_region: us-east-1
    include:
      - tpch.part*
      - tpch.supplier

sql> show tables;
+---------------+--------------+---------------+------------+
| table_catalog | table_schema |   table_name  | table_type |
|    varchar    |    varchar   |    varchar    |   varchar  |
+---------------+--------------+---------------+------------+
| scp           | tpch         | partsupp      | BASE TABLE |
| scp           | tpch         | part          | BASE TABLE |
| scp           | tpch         | supplier      | BASE TABLE |
| spice         | runtime      | task_history  | BASE TABLE |
+---------------+--------------+---------------+------------+

Time: 0.001866958 seconds. 4 rows.

Step 9. Add the Quickstart Catalog

Add the Quickstart Catalog to the spicepod.yaml file. This demonstrates how to include tables from multiple Spice.ai Cloud Platform apps.

catalogs:
  # ... existing catalog ...

  - from: spice.ai/spiceai/quickstart
    name: quickstart
    params:
      spiceai_region: us-east-1

spice sql
sql> show tables;
+---------------+--------------+--------------+------------+
| table_catalog | table_schema |  table_name  | table_type |
|    varchar    |    varchar   |    varchar   |   varchar  |
+---------------+--------------+--------------+------------+
| scp           | tpch         | partsupp     | BASE TABLE |
| scp           | tpch         | part         | BASE TABLE |
| scp           | tpch         | supplier     | BASE TABLE |
| quickstart    | public       | taxi_trips   | BASE TABLE |
| spice         | runtime      | task_history | BASE TABLE |
+---------------+--------------+--------------+------------+

Time: 0.011640125 seconds. 5 rows.

sql> SELECT trip_distance, fare_amount FROM quickstart.public.taxi_trips LIMIT 10;
+---------------+-------------+
| trip_distance | fare_amount |
+---------------+-------------+
| 2.4           | 12.1        |
| 0.9           | 7.2         |
| 2.02          | 11.4        |
| 2.08          | 14.2        |
| 1.03          | 8.6         |
| 0.71          | 5.8         |
| 1.0           | 6.5         |
| 2.8           | 17.7        |
| 0.5           | 5.8         |
| 5.7           | 24.7        |
+---------------+-------------+

Time: 0.267290292 seconds. 10 rows.

Next Steps

Discover the apps available in Spicerack (opens in a new tab) and use them as catalogs in the Spice.ai Cloud Platform catalog connector.