Quick tip: Ibis, Pandas and SingleStoreDB
Tags: singlestoredb, ibis, pandas, python
Published: 2023-03-10 22:00:18

## Abstract
[The Ibis Project](https://ibis-project.org/) provides the power of Python Analytics with SQL. In this short article, we'll see how to use Ibis to connect to SingleStoreDB Cloud and run a few commands to demonstrate the integration.

## Introduction
In previous articles, we've discussed various options to connect to SingleStoreDB. These have included using, for example:

- [SingleStore Spark Connector](https://github.com/memsql/singlestore-spark-connector)
- [JDBC](https://github.com/memsql/S2-JDBC-Connector)
- [SQLAlchemy](https://github.com/singlestore-labs/sqlalchemy-singlestoredb)
- [Python Client](https://github.com/singlestore-labs/singlestoredb-python)

Another connection option is the [Ibis backend](https://github.com/singlestore-labs/ibis-singlestoredb) being developed by SingleStore Labs. In this short article, we'll test this implementation with some commands.

## Create a SingleStoreDB Cloud account
A [previous article](https://dev.to/singlestore/quick-tip-using-dbt-with-singlestoredb-161g) showed the steps required to create a free SingleStoreDB Cloud account. We'll use **Ibis Demo Group** as our Workspace Group Name and **ibis-demo** as our Workspace Name. We'll make a note of our **password** and **host** name.

We'll use the **SQL Editor** to create a new database, as follows:

```sql
CREATE DATABASE IF NOT EXISTS iris_db;
```

## Create a Deepnote account
We'll create a free [account](https://deepnote.com/sign-up) on the Deepnote website. Once logged in, we'll create a new Deepnote project to give us a new notebook. We'll also create a new directory called `data`. We'll use the [Iris flower data set](https://en.wikipedia.org/wiki/Iris_flower_data_set) and upload the [CSV](https://gist.github.com/VeryFatBoy/9af771d443f5ec4dd6eec8d69a062638) file into the `data` directory.

## Deepnote notebook
First, we'll install the package:

```shell
!pip install ibis-singlestoredb
```

Deepnote will prompt us to add this to the `requirements.txt` file.

Next, we'll create some imports:

```python
import ibis
import pandas as pd

ibis.options.interactive = True
```

We'll now read the `iris.csv` file and look at the data:

```python
iris_df = pd.read_csv("data/iris.csv")

iris_df.head(5)
```

The result should be similar to the following:

```
   sepal_length  sepal_width  petal_length  petal_width      species
0           5.1          3.5           1.4          0.2  Iris-setosa
1           4.9          3.0           1.4          0.2  Iris-setosa
2           4.7          3.2           1.3          0.2  Iris-setosa
3           4.6          3.1           1.5          0.2  Iris-setosa
4           5.0          3.6           1.4          0.2  Iris-setosa
```

Next, let's check the Pandas Dataframe:

```python
iris_df.info()
```

The result should be as follows:

```
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 150 entries, 0 to 149
Data columns (total 5 columns):
 #   Column        Non-Null Count  Dtype  
---  ------        --------------  -----  
 0   sepal_length  150 non-null    float64
 1   sepal_width   150 non-null    float64
 2   petal_length  150 non-null    float64
 3   petal_width   150 non-null    float64
 4   species       150 non-null    object 
dtypes: float64(4), object(1)
```

We'll now create a connection to SingleStoreDB:

```python
conn = ibis.singlestoredb.connect(
    "admin:<password>@<host>:3306/iris_db"
)
```

We'll replace the `<password>` and `<host>` with the values from our SingleStoreDB Cloud account.

Now we'll create a new table in SingleStoreDB using the Pandas Dataframe and then look at the data:

```python
iris_tbl = conn.create_table("iris", iris_df, force = True)

iris_tbl.head(5)
```

The result should be similar to the following:

```
   sepal_length  sepal_width  petal_length  petal_width      species
0           4.6          3.1           1.5          0.2  Iris-setosa
1           5.4          3.9           1.3          0.4  Iris-setosa
2           5.1          3.5           1.4          0.3  Iris-setosa
3           4.9          3.1           1.5          0.1  Iris-setosa
4           4.5          2.3           1.3          0.3  Iris-setosa
```

We can get some further details:

```python
iris_tbl.info()
```

The result should be as follows:

```
                      Summary of iris
                          150 rows
+--------------+------------------------+---------+---------+
| Name         | Type                   | # Nulls | % Nulls |
+--------------+------------------------+---------+---------+
| sepal_length | Float64(nullable=True) |       0 |    0.00 |
| sepal_width  | Float64(nullable=True) |       0 |    0.00 |
| petal_length | Float64(nullable=True) |       0 |    0.00 |
| petal_width  | Float64(nullable=True) |       0 |    0.00 |
| species      | String(nullable=True)  |       0 |    0.00 |
+--------------+------------------------+---------+---------+
```

Basic schema information can also be viewed:

```python
iris_tbl.schema()
```

The result should be as follows:

```
ibis.Schema {
  sepal_length  float64
  sepal_width   float64
  petal_length  float64
  petal_width   float64
  species       string
}
```

We can also find the details of the `CREATE TABLE` statement:

```python
conn.show.create_table("iris")
```

The result should be as follows:

```sql
|  Name | CreateTable                                                                                                                 |
---------------------------------------------------------------------------------------------------------------------------------------
|  iris | CREATE TABLE `iris` (
          `sepal_length` float DEFAULT NULL,
          `sepal_width` float DEFAULT NULL,
          `petal_length` float DEFAULT NULL,
          `petal_width` float DEFAULT NULL,
          `species` text CHARACTER SET utf8 COLLATE utf8_general_ci,
          KEY `__UNORDERED` () USING CLUSTERED COLUMNSTORE,
          SHARD KEY ()
          ) AUTOSTATS_CARDINALITY_MODE=INCREMENTAL AUTOSTATS_HISTOGRAM_MODE=CREATE AUTOSTATS_SAMPLING=ON SQL_MODE='STRICT_ALL_TABLES' |
```

We can count the number of different species:

```python
iris_tbl.species.value_counts()
```

The result should be as follows:

```
           species  count
0   Iris-virginica     50
1      Iris-setosa     50
2  Iris-versicolor     50
```

Filtering is also possible. Here, for example, we want to limit the results by `Iris-versicolor` where the `petal_length` is greater than 4.5:

```python
res = iris_tbl[iris_tbl.species.like("Iris-versicolor")][iris_tbl.petal_length > 4.5]
```

Using the filter we can get a count:

```python
res.count()
```

The result should be as follows:

```
14
```

Sorting is also possible. This is in ascending order for `petal_length`:

```python
res.sort_by("petal_length")
```

The result should be as follows:

```
    sepal_length  sepal_width  petal_length  petal_width          species
0            6.6          2.9           4.6          1.3  Iris-versicolor
1            6.1          3.0           4.6          1.4  Iris-versicolor
2            6.5          2.8           4.6          1.5  Iris-versicolor
3            6.1          2.8           4.7          1.2  Iris-versicolor
4            6.7          3.1           4.7          1.5  Iris-versicolor
5            7.0          3.2           4.7          1.4  Iris-versicolor
6            6.3          3.3           4.7          1.6  Iris-versicolor
7            6.1          2.9           4.7          1.4  Iris-versicolor
8            5.9          3.2           4.8          1.8  Iris-versicolor
9            6.8          2.8           4.8          1.4  Iris-versicolor
10           6.9          3.1           4.9          1.5  Iris-versicolor
11           6.3          2.5           4.9          1.5  Iris-versicolor
12           6.7          3.0           5.0          1.7  Iris-versicolor
13           6.0          2.7           5.1          1.6  Iris-versicolor
```

Finally, we can convert the results back into a Pandas Dataframe, for additional processing, as follows:

```python
another_iris_df = res.execute()
```

## Summary
The Ibis Project provides another way to work with SingleStoreDB using Python and Pandas. The [SingleStore Labs GitHub repo](https://github.com/singlestore-labs/ibis-singlestoredb) contains example notebooks that use other popular datasets. Check it out.