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
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
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.
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
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
Step 6. Query a dataset
SELECT * FROM scp.tpch.lineitem LIMIT 10;
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.
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
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.
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
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.
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.
![dependabot[bot]](https://avatars.githubusercontent.com/in/29110?v=4)
