Quick tip: SingleStoreDB's Persistent Computed Columns

Abstract

SingleStoreDB supports a feature called Persistent Computed Columns that enables the creation of a table column defined by an expression that uses other table columns. In this short article, we'll look at an example of how to use this feature with Geospatial data.

Introduction

When working with some types of data, it can be useful to automatically compute values, saving time and effort for the developer. In SingleStoreDB, this capability is available through Persistent Computed Columns. Let's see how to use this with a simple Geospatial example.

Create a SingleStoreDB Cloud account

A previous article showed the steps required to create a free SingleStoreDB Cloud account. We'll use PCC Demo Group as our Workspace Group Name and pcc-demo as our Workspace Name. We'll make a note of our password and host name.

Create the database and table

First, we'll download a CSV file containing some synthetic sensor data.

Next, we'll use a MySQL client to connect to SingleStoreDB Cloud, as follows:

mysql --local-infile \
      -u admin \
      -h <host> \
      -P 3306 \
      --default-auth=mysql_native_password \
      -p

The <host> being replaced with the value obtained from SingleStoreDB Cloud.

We'll now create a database and database table, as follows:

CREATE DATABASE pcc_demo;

USE pcc_demo;

CREATE ROWSTORE TABLE sensors (
     id INT PRIMARY KEY,
     name VARCHAR (50),
     latitude DOUBLE,
     longitude DOUBLE,
     location AS GEOGRAPHY_POINT(longitude, latitude) PERSISTED GEOGRAPHYPOINT,
     INDEX(location)
);

Load the data

We'll now load the sensor data into the sensors table, as follows:

LOAD DATA LOCAL INFILE '/path/to/sensor.csv'
INTO TABLE sensors
IGNORE 1 LINES
COLUMNS TERMINATED BY ',';

We would replace /path/to/ with the actual path to where the CSV file was located.

Our CSV file contains four columns (id, name, latitude, longitude). As the data are loaded into the table, the location column is automatically populated for us with a Geospatial Point by using the longitude and latitude values.

Query the data

Once the data are loaded we can check the table, as follows:

SELECT id, name, location FROM sensors LIMIT 5;

The result should be similar to the following:

+----+-----------+-----------------------------------+
| id | name      | location                          |
+----+-----------+-----------------------------------+
|  1 | sensor_1  | POINT(88.44146008 -50.58444199)   |
|  3 | sensor_3  | POINT(93.61987432 -54.16528058)   |
|  6 | sensor_6  | POINT(-117.33446641 -55.57414366) |
|  9 | sensor_9  | POINT(-58.40311992 -5.46648307)   |
| 11 | sensor_11 | POINT(-141.93731583 10.42001621)  |
+----+-----------+-----------------------------------+

We can now use the location column as part of Geospatial queries. This column is also indexed, similar to any standard column.

Summary

Through a quick example, we have seen how to use Persistent Computed Columns. As we loaded the CSV data, SingleStoreDB automatically created the data values in the extra column, saving us time and effort.