Quick tip: Using R, OpenAI and SingleStore Notebooks
Tags: singlestoredb, r, openai, jupyter
Published: 2024-05-01 10:16:15

## Abstract
In this short article, we'll see how to create vector embeddings using R and OpenAI. We'll also store the embeddings in SingleStore. R is not officially supported by OpenAI, but a community library provides access to the API.

The notebook file used in this article is available on [GitHub](https://github.com/VeryFatBoy/r-examples/blob/main/r_openai_demo.ipynb).

## Create a SingleStore Cloud account
A [previous article](https://dev.to/singlestore/quick-tip-using-dbt-with-singlestoredb-161g) showed the steps to create a free SingleStore Cloud account. We'll use the following settings:

- **Workspace Group Name:** R Demo Group
- **Cloud Provider:** AWS
- **Region:** US East 1 (N. Virginia)
- **Workspace Name:** r-demo
- **Size:** S-00

## Create a new notebook
From the left navigation pane in the cloud portal, we'll select **Develop > Notebooks**.

In the top right of the web page, we'll select **New Notebook > New Notebook**, as shown in Figure 1.

![Figure 1. New Notebook.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cw8q0k33wmch4zv5h5a4.png)
<center>Figure 1. New Notebook.</center>

We'll call the notebook **r_openai_demo**, select a **Blank** notebook template from the available options, and save it in the **Personal** location.

## Create a database and table
In our SingleStore Cloud account, let's use the **SQL Editor** to create a new database and table. We'll call these `r_demo` and `articles`, respectively, as follows:

```sql
DROP DATABASE IF EXISTS r_demo;
CREATE DATABASE IF NOT EXISTS r_demo;

USE r_demo;

CREATE TABLE IF NOT EXISTS articles (
     id SMALLINT,
     text TEXT,
     vector VECTOR(1536) NOT NULL
);
```

## Fill out the notebook
First, let's install the R kernel and some other packages we need for this article:

```shell
!conda install -y --quiet -c conda-forge r-irkernel r-rjava r-rjdbc r-remotes
```

Next, we need to change the kernel. Refreshing the page will help the notebook detect any changes, including the installation of a new kernel.

In the top right, we can see that Python is currently selected, as shown in Figure 2.

![Figure 2. Python 3 (ipykernel).](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wx4nuda2r26cyqi0zw8h.png)
<center>Figure 2. Python 3 (ipykernel).</center>

Selecting Python 3 will present a box with a pull-down as shown in Figure 3.

![Figure 3. Select Kernel.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ky0617ovdklupvlu00ng.png)
<center>Figure 3. Select Kernel.</center>

Clicking the pull-down will show some options and R should be one of the options. We'll choose R, as shown in Figure 4.

![Figure 4. Select Kernel.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ooozrtar3rfgd3drcidu.png)
<center>Figure 4. Select Kernel.</center>

Next, we'll click the **Select** button.

To connect to SingleStore, we'll use JDBC, as follows:

```r
library(RJDBC)
```

This will also load `DBI` and `rJava`:

```
Loading required package: DBI

Loading required package: rJava
```

Next, we'll download the SingleStore JDBC Client and save it in a `jars` directory:

```r
# URL of the JDBC driver file
driver_url <- "https://repo1.maven.org/maven2/com/singlestore/singlestore-jdbc-client/1.2.1/singlestore-jdbc-client-1.2.1.jar"

# Set the JDBC driver class name
driver <- "com.singlestore.jdbc.Driver"

# Local directory to save the driver file
local_dir <- "jars"
dir.create(local_dir, showWarnings = FALSE, recursive = TRUE)

# Check if the driver file already exists
driver_file <- file.path(
    local_dir,
    "singlestore-jdbc-client-1.2.1.jar"
)

if (!file.exists(driver_file)) {
    # Download the JDBC driver file if it doesn't exist
    download.file(
        driver_url,
        destfile = driver_file,
        mode = "wb",
        quiet = TRUE
    )
}

# Check if the driver file has been downloaded successfully
if (file.exists(driver_file)) {
    print("Driver file downloaded successfully")
} else {
    print("Failed to download the driver file")
}
```

Now we'll create the connection details to SingleStore:

```r
host <- "<HOST>"
port <- 3306
database <- "r_demo"
user <- "admin"
password <- "<PASSWORD>"

url <- paste0("jdbc:singlestore://", host, ":", port, "/", database)
```

Replace `<HOST>` and `<PASSWORD>` with the values for your environment. These values can be obtained from the workspace using **Connect > SQL IDE**.

We'll now prepare the connection to SingleStore.

```r
# Establish the JDBC connection
conn <- dbConnect(
    drv = JDBC(driver, driver_file),
    url = url,
    user = user,
    password = password
)
```

Next, we'll install [rgpt3](https://github.com/ben-aaron188/rgpt3):

```r
remotes::install_github("ben-aaron188/rgpt3", force = TRUE)
```

and import some libraries:

```r
library(jsonlite)
library(rgpt3)
```

We'll now enter and save our `OpenAI Key`:

```r
# Prompt for the OpenAI Key
openai_key <- readline(prompt = "OpenAI Key:")

# Specify the file path
file_path <- "access_key.txt"

# Write the string to the file with a newline
writeLines(openai_key, file_path)
```

and check that it was saved correctly:

```r
rgpt_authenticate("access_key.txt")
```

Next, we'll load a built-in dataset:

```r
data("travel_blog_data")
```

We'll select some text from the dataset and create OpenAI embeddings:

```r
my_text = travel_blog_data$gpt_content[1]

my_embeddings = rgpt_single_embedding(
    input = my_text,
    model = "text-embedding-3-small"
)
```

Next, we'll create a new DataFrame:

```r
# Create a DataFrame with one row
article <- data.frame(
    id = travel_blog_data$n[1],
    text = my_text,
    vector = as.character(toJSON(my_embeddings))
)
```

and then write the DataFrame to SingleStore:

```r
# Write the DataFrame to the database
dbWriteTable(conn, "articles", article, append = TRUE)
```

We'll read the data back and check it, as follows:

```r
# Read the data from the database
article_from_db <- dbReadTable(conn, "articles")

# Print the summary of the DataFrame
str(article_from_db)
```

Example output:

```
'data.frame': 1 obs. of  3 variables:
 $ id    : num 1
 $ text  : chr "Title: Paws Across the UK: A Tail-Wagging Journey Through Britain\n\nAs someone who firmly believes that advent"| __truncated__
 $ vector: chr "[0.0215000007,0.0126999998,0.0524000004,0.0340999998,-0.0328000002,-0.0188999996,-0.0214000009,0.0250000004,-0."| __truncated__
```

We can also create embeddings for multiple rows, as follows:

```r
multiple_embeddings = rgpt_embeddings(
    input_var = travel_blog_data$gpt_content,
    id_var = travel_blog_data$n,
    param_model = "text-embedding-3-small"
)
```

Next, we'll create a new DataFrame:

```r
# Convert each row of embeddings to a JSON array
embeddings_json <- apply(multiple_embeddings[, 1:1536], 1, function(row) {
    toJSON((row))
})

# Create the DataFrame with id and vector columns
articles <- data.frame(
    id = multiple_embeddings$id,
    vector = embeddings_json
)

# Merge articles with travel_blog_data based on id and n columns
merged_data <- merge(articles, travel_blog_data[, c("n", "gpt_content")], by.x = "id", by.y = "n", all.x = TRUE)

# Update the articles DataFrame with the merged gpt_content
articles$text <- merged_data$gpt_content

# Reorder the columns to match the database schema
articles <- articles[, c("id", "text", "vector")]
```
and then write the DataFrame to SingleStore:

```r
# Write the DataFrame to the database
dbWriteTable(conn, "articles", articles, append = TRUE)
```

We'll read the data back and check it, as follows:

```r
# Read the data from the database
articles_from_db <- dbReadTable(conn, "articles")

# Print the summary of the DataFrame
str(articles_from_db)
```

Example output:

```
'data.frame': 11 obs. of  3 variables:
 $ id    : num  10 7 4 6 2 1 9 8 3 5 ...
 $ text  : chr  "# A Tail-Wagging Tour: Roaming the UK on Four Paws\n\nAs a devoted dog parent, planning a trip that's both exci"| __truncated__ "### Paws Across the UK: A Tail-Wagging Adventure\n\n#### Introduction\nHello fellow adventurers and animal love"| __truncated__ "**Paws Across the Pond: A Dog's Journey Through the UK**\n\nEmbarking on an adventure across the United Kingdom"| __truncated__ "# A Tail-Wagging Tour: A Dog's Journey Through the UK\n\n### Day 1: London Calling\n\nMy journey began in the b"| __truncated__ ...
 $ vector: chr  "[0.0399999991,0.0186000001,0.0647,0.0353000015,-0.0469999984,-0.0313999988,-0.0147000002,0.0162000004,-0.012299"| __truncated__ "[0.0282000005,0.0137,0.0423999988,0.0421000011,-0.0549000017,-0.00639999984,-0.0324000008,0.0272000004,-0.00680"| __truncated__ "[0.0339000002,0.00939999986,0.0269000009,0.0445999987,-0.0397000015,-0.00779999979,-0.0337000005,0.0299999993,-"| __truncated__ "[0.0245999992,0.00980000012,0.0460000001,0.0223999992,-0.0593000017,-0.0110999998,-0.0113000004,-0.00400000019,"| __truncated__ ...
```

There are 11 rows. One row was added earlier and 10 rows were added later.

Finally, we'll close the JDBC connection:

```r
# Close the JDBC connection
dbDisconnect(conn)
```

## Summary
In this short article, we've seen how to create vector embeddings using R and OpenAI. We've also seen how we can store and retrieve the vector embeddings using SingleStore.