Skip to content

Latest commit

 

History

History
226 lines (180 loc) · 9.43 KB

README.md

File metadata and controls

226 lines (180 loc) · 9.43 KB

MySQL Data Connector

This recipe will use a demo instance of MySQL with a dataset generated using SQL stored procedure. Follow the recipe to create MySQL instance and get started with MySQL as a Data Connector.

Prerequisites

  • Install MySQL.
    • Alternatively, you can use Docker to run a MySQL instance.
  • Spice is installed (see the Getting Started documentation).

Steps

Step 0. Optional. Start a MySQL instance using Docker, if MySQL is not installed.

docker run -d --name mysql-spice-demo -e MYSQL_ROOT_PASSWORD=123 -e MYSQL_DATABASE=spice_demo -e MYSQL_PASSWORD=123 -p 3306:3306 mysql:8.0

Step 1. Create a sample MySQL database and generate a testing table using stored procedure.

Invoke MySQL in the prompt.

mysql -u USERNAME -pPASSWORD
# Or if using Docker
docker exec -it mysql-spice-demo mysql -u root -p123 --port 3306

Using the MySQL console, create a sample database named spice_demo.

CREATE DATABASE spice_demo;
USE spice_demo;

Create a sample table named sample_data.

CREATE TABLE `sample_data`
(
  `id`             bigint(20) NOT NULL AUTO_INCREMENT,
  `datetime`       timestamp  NULL DEFAULT CURRENT_TIMESTAMP,
  `name`           varchar(255) DEFAULT NULL,
  `phone`          varchar(20) DEFAULT NULL,
  `email`          varchar(255) DEFAULT NULL,
  `street_address` varchar(255) DEFAULT NULL,
  `zip_code`       varchar(10) DEFAULT NULL,
  `region`         varchar(100) DEFAULT NULL,
  `latitude`       decimal(9,6) DEFAULT NULL,
  `longitude`      decimal(9,6) DEFAULT NULL,

  PRIMARY KEY (`id`)
);

Create a stored procedure for generating data in sample_data.

DELIMITER $$
CREATE PROCEDURE sample_data_gen()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 20000 DO
    INSERT INTO `sample_data` (
      `datetime`, `name`, `phone`, `email`, `street_address`, `zip_code`, `region`, `latitude`, `longitude`
    ) VALUES (
      FROM_UNIXTIME(UNIX_TIMESTAMP('2024-01-01 01:00:00') + FLOOR(RAND() * 31536000)),
      CONCAT('Name', FLOOR(RAND() * 100)),
      CONCAT('555-', LPAD(FLOOR(RAND() * 10000), 4, '0')),
      CONCAT('user', FLOOR(RAND() * 100), '@example.com'),
      CONCAT('Street', FLOOR(RAND() * 100), ' Avenue'),
      LPAD(FLOOR(RAND() * 100000), 5, '0'),
      CONCAT('Region', FLOOR(RAND() * 10)),
      ROUND(-90 + (180 * RAND()), 6),
      ROUND(-180 + (360 * RAND()), 6)
    );
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

Call the stored procedure to generate data in sample_data.

CALL sample_data_gen();
Query OK, 1 row affected (1.92 sec)

Drop the stored procedure.

DROP PROCEDURE sample_data_gen;

Check the sample data generated in the sample_data table.

SELECT * FROM sample_data LIMIT 10;
+----+---------------------+--------+----------+--------------------+-----------------+----------+---------+------------+-------------+
| id | datetime            | name   | phone    | email              | street_address  | zip_code | region  | latitude   | longitude   |
+----+---------------------+--------+----------+--------------------+-----------------+----------+---------+------------+-------------+
|  1 | 2024-04-21 17:26:52 | Name49 | 555-5584 | [email protected] | Street85 Avenue | 36728    | Region2 | -50.484340 |  -70.260682 |
|  2 | 2024-11-11 23:19:51 | Name41 | 555-4699 | [email protected] | Street13 Avenue | 36252    | Region3 |  72.269701 |  -66.940222 |
|  3 | 2024-11-12 01:13:48 | Name38 | 555-3363 | [email protected] | Street59 Avenue | 42688    | Region3 | -14.786284 | -154.742840 |
|  4 | 2024-02-05 12:45:07 | Name27 | 555-0867 | [email protected] | Street77 Avenue | 04347    | Region8 | -23.942890 | -130.868949 |
|  5 | 2024-07-31 07:16:24 | Name49 | 555-7441 | [email protected] | Street90 Avenue | 85108    | Region5 | -68.518797 |  177.533816 |
|  6 | 2024-08-09 21:41:31 | Name5  | 555-4727 | [email protected] | Street51 Avenue | 00585    | Region4 | -11.677234 |   73.445000 |
|  7 | 2024-03-19 10:41:05 | Name96 | 555-1629 | [email protected] | Street16 Avenue | 02943    | Region6 | -56.949324 |  163.769050 |
|  8 | 2024-03-22 17:50:09 | Name25 | 555-5982 | [email protected] | Street35 Avenue | 07752    | Region3 | -16.818093 | -162.236694 |
|  9 | 2024-01-10 21:38:38 | Name98 | 555-8541 | [email protected] | Street98 Avenue | 00167    | Region0 | -46.053884 | -154.358929 |
| 10 | 2024-08-15 17:31:51 | Name90 | 555-6528 | [email protected] | Street79 Avenue | 30242    | Region1 |  53.699951 |   24.584954 |
+----+---------------------+--------+----------+--------------------+-----------------+----------+---------+------------+-------------+
10 rows in set (0.00 sec)

Step 2. Inside MySQL, check the users in the local MySQL instance. Use any of the users from your query result in the later configuration of mysql_user in spicepod.yaml.

SELECT user FROM mysql.user;
+------------------+
| user             |
+------------------+
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
4 rows in set (0.00 sec)

Step 3. Initialize a Spice app.

spice init mysql-demo
cd mysql-demo

Step 4. Configure the dataset to connect to MySQL. Copy and paste the configuration below to spicepod.yaml in the Spice app.

version: v1
kind: Spicepod
name: mysql-demo
datasets:
  - from: mysql:spice_demo.sample_data
    name: sample_data
    params:
      mysql_host: localhost
      mysql_tcp_port: 3306
      mysql_db: spice_demo
      mysql_sslmode: disabled
      mysql_user: root
      mysql_pass: ${env:MYSQL_PASS}

Ensure the MYSQL_PASS environment variable is set to the password for your MySQL instance. Environment variables can be specified on the command line when running the Spice runtime or in a .env file in the same directory as spicepod.yaml.

echo "MYSQL_PASS=<password>" > .env
# i.e. echo "MYSQL_PASS=123" > .env

Step 5. Start the Spice runtime

spice run

Confirm in the terminal output the sample_data dataset has been loaded:

2025/01/13 11:52:51 INFO Spice.ai runtime starting...
2025-01-13T19:52:51.473621Z  INFO runtime::init::dataset: Initializing dataset sample_data
2025-01-13T19:52:51.474059Z  INFO runtime::metrics_server: Spice Runtime Metrics listening on 127.0.0.1:9090
2025-01-13T19:52:51.474795Z  INFO runtime::http: Spice Runtime HTTP listening on 127.0.0.1:8090
2025-01-13T19:52:51.474869Z  INFO runtime::flight: Spice Runtime Flight listening on 127.0.0.1:50051
2025-01-13T19:52:51.481201Z  INFO runtime::opentelemetry: Spice Runtime OpenTelemetry listening on 127.0.0.1:50052
2025-01-13T19:52:51.491591Z  INFO runtime::init::dataset: Dataset sample_data registered (mysql:spice_demo.sample_data).
2025-01-13T19:52:51.673260Z  INFO runtime::init::results_cache: Initialized results cache; max size: 128.00 MiB, item ttl: 1s

Follow the getting started guide to get started with the Spice.ai runtime.

See the datasets reference for more dataset configuration options.

Step 6. Run queries against the dataset using the Spice SQL REPL.

In a new terminal, start the Spice SQL REPL

spice sql

You can now now query sample_data in the runtime.

select * from sample_data limit 10;
+----+---------------------+--------+----------+--------------------+-----------------+----------+---------+------------+-------------+
| id | datetime            | name   | phone    | email              | street_address  | zip_code | region  | latitude   | longitude   |
+----+---------------------+--------+----------+--------------------+-----------------+----------+---------+------------+-------------+
| 1  | 2024-12-22T13:15:47 | Name54 | 555-7824 | [email protected] | Street7 Avenue  | 52178    | Region3 | -26.288629 | 42.540805   |
| 2  | 2024-01-11T14:56:16 | Name29 | 555-3652 | [email protected] | Street67 Avenue | 52628    | Region5 | -16.801736 | -92.886831  |
| 3  | 2024-12-27T08:49:21 | Name22 | 555-1488 | [email protected]  | Street91 Avenue | 37329    | Region1 | -11.593564 | 124.084618  |
| 4  | 2024-11-30T14:37:20 | Name4  | 555-4955 | [email protected] | Street16 Avenue | 83573    | Region6 | 72.989369  | -7.251897   |
| 5  | 2024-09-06T07:14:17 | Name97 | 555-8235 | [email protected] | Street50 Avenue | 92686    | Region1 | 64.729577  | 149.010004  |
| 6  | 2024-12-27T16:01:57 | Name21 | 555-0879 | [email protected] | Street75 Avenue | 35681    | Region5 | 8.764192   | -118.093257 |
| 7  | 2024-03-19T02:24:54 | Name55 | 555-1229 | [email protected] | Street41 Avenue | 19421    | Region7 | -74.996777 | -101.823089 |
| 8  | 2024-11-01T03:03:14 | Name52 | 555-1288 | [email protected]  | Street92 Avenue | 44372    | Region4 | 66.192141  | -173.042407 |
| 9  | 2024-06-29T04:27:17 | Name40 | 555-5655 | [email protected] | Street30 Avenue | 72462    | Region7 | -24.397028 | 71.749364   |
| 10 | 2024-05-27T05:28:20 | Name91 | 555-3794 | [email protected] | Street58 Avenue | 47381    | Region6 | 37.356779  | 57.708228   |
+----+---------------------+--------+----------+--------------------+-----------------+----------+---------+------------+-------------+

Time: 0.011687958 seconds. 10 rows.

For more information on using spice sql, see the CLI reference.