Memurai SQL commands

Create

MSQL.CREATE

Description

The MSQL.CREATE command allows you to create an index table for a particular group of related entries that share a common prefix. It is possible to index any number of the "hash" sub-fields by specifying their name (column name) and their type.

Note: If the type specified in an index table does not match the data type stored in Memurai (or Valkey), Memurai SQL will automatically do the conversion.

For example, the following entry was created in Memurai (or Valkey):

HSET employee:002 name "John Smith" dob "1/1/2000"

If you create an index where the column "dob" is specified as a DATE and not a string as it is in Memurai (or Valkey), Memurai SQL will convert it to a DATE every time it loads it from Memurai (or Valkey).

Syntax

MSQL.CREATE {INDEXNAME} ON hash {PREFIX} {COL1NAME} {COL1TYPE} [ {COL2NAME} {COL2TYPE} ,…. ] ]

{INDEXNAME}: string

{PREFIX}: string

{COL?NAME}: string field present in hash object with given prefix.

{COL?TYPE}: one of: TEXT, BIGINT, FLOAT, DATE, TIMESTAMP

Examples

MSQL.CREATE myproducts ON hash "product:" id TEXT quantity BIGINT rating FLOAT type TEXT

MSQL.CREATE mymovies ON hash "movie:" title TEXT release_year BIGINT rating FLOAT genre TEXT

MSQ.CREATE transactions_idx ON hash "transaction:" ip_addr TEXT geography TEXT product_no BIGINT event_date DATE

MSQL.CREATE myguids on hash "guid:" guid TEXT ip_addr TEXT pages_visited BIGINT timestamp_start TIMESTAMP device TEXT OS TEXT browser TEXT

You can query the index with MSQL.QUERY commands, and NULLs are supported.

The following data types are supported as index table column types:

TEXT

As is the case with Valkey, Memurai SQL supports strings as blobs of data, and can contain binary zero. UTF8 strings are supported with the following limitation: UTF8 strings with binary zeros inside the strings cannot be used as query parameters.

BIGINT

64 bit signed integer numbers.

FLOAT

64 bit floating point numbers.

DATE

ISO 8601 format is supported: YYYY-mm-dd where month can be number or a short string like "Jan".

Examples: 2021-01-30 and 2021-Jan-30

TIMESTAMP

YYYY-mm-ddTHH:MM:ss.millis

“millis” is optional.

The Timestamp format is essentially the ISO 8601 date format, plus ‘T’, plus time.

Query data

MSQL.QUERY

Description

Executes the specified query against your current indexes, enabling you to you to run SQL-like queries and return the information that you need. The MSQL.QUERY command is very useful overall for searching and running queries on the hash entries across your Memurai data stores.

Syntax

MSQL.QUERY [QUERY TEXT]

Examples

Query data, order by

MSQL.QUERY "SELECT ip_addr, event_date from transactions_idx"

MSQL.QUERY "SELECT ip_addr, event_date from transactions_idx ORDER BY ip_addr"

MSQL.QUERY "SELECT ip_addr, event_date from transactions_idx ORDER BY event_date"

Query data, compare texts

MSQL.QUERY "SELECT ip_addr, geography, event_date from transactions_idx where ip_addr = '9.12.345.987'"

MSQL.QUERY "SELECT ip_addr, geography from transactions_idx WHERE event_date = '10-10-2020' ORDER BY geography"

MSQL.QUERY "SELECT guid, ip_addr from myguids where device = 'iphone'"

Query data, some timestamp comparisons

MSQL.QUERY "SELECT ip_addr from myguids where timestamp_start > '2021-08-15T12:02:03'"

MSQL.QUERY "SELECT ip_addr from transactions_idx where timestamp_start >= '2021-08-14T12:10:15'"

MSQL.QUERY "SELECT ip_addr from transactions_idx where timestamp_start <= '2021-08-14T13:15:02'"

Query data, offset and limit

MSQL.QUERY "SELECT ip_addr from transactions_idx ORDER BY ip_addr limit 2"

MSQL.QUERY "SELECT ip_addr from transactions_idx ORDER BY ip_addr offset 2"

MSQL.QUERY "SELECT ip_addr from transactions_idx ORDER BY ip_addr limit 2 offset 2"

Query data, like patterns

MSQL.QUERY "SELECT ip_addr, product_no from transactions_idx where ip_addr like '%345'"

MSQL.QUERY "SELECT ip_addr, product_no from transactions_idx where ip_addr like '9.12%'"

MSQL.QUERY "SELECT ip_addr, product_no from transactions_idx where ip_addr like '%345%'"

Query data, And/Or comparisons

MSQL.QUERY "SELECT ip_addr from transactions_idx where product_no > 1 and product_no < 5"

MSQL.QUERY "SELECT ip_addr from transactions_idx where product_no > 1 or product_no < 5"

MSQL.QUERY "SELECT ip_addr from transactions_idx where product_no >= 3 and weight < 7.0"

MSQL.QUERY "SELECT ip_addr from transactions_idx where product_no > 1 or ip_addr = '9.12.345.987'"

Query data, query parameters

MSQL.QUERY "SELECT ip_addr from transactions_idx where product_no > ?" "2"

MSQL.QUERY "SELECT ip_addr from transactions_idx where product_no > ? and weight < ?" "2" "7.8"

MSQL.QUERY "SELECT ip_addr from transactions_idx where product_no > ? or weight < ?" "3" "5.0"

MSQL.QUERY commonalities with SQL

MSQL.QUERY has commonalities with the SQL standard, with a subset of the SQL commands available in MSQL.QUERY. The following section lists the supported and unsupported commands and operators in MSQL.QUERY:

Supported

ORDER BY is supported.

OFFSET and LIMIT are supported.

Only simple projections are supported. For example, SELECT COL1, COL2 …

Only simple WHERE clauses are supported. For example, WHERE COL1 >= VAL OR COL2 = 3

The LIKE pattern in WHERE clauses is supported. For example, WHERE LASTNAME LIKE “EINST%”

The following three LIKE patterns are supported: “%END”, “START%”, “%MIDDLE%”

Query parameters are supported:

MSQL.QUERY “SELECT name FROM students WHERE grade = ? AND country = ?;" “actual” “expected”

Not supported at this time

The asterisk wildcard character * is not supported anywhere.

Only SELECT statements are supported; Insert, Update, and Alter are not supported.

Nested SELECT statements are NOT supported.

JOIN, GROUP BY MAX, MIN, AVG or other functions on projections are not supported.

Describe and Show commands

MSQL.DESCRIBE

Description

MSQL.DESCRIBE returns the columns and types for the given index. You must specify the name of the index that you want to get information about.

Syntax

MSQL.DESCRIBE   {INDEXNAME}

Returns

Array Response. A nested array of keys and values.

Complexity

0(1)

Example

MSQL.DESCRIBE   mymovies

MSQL.SHOW

Description

MSQL.SHOW returns a list of indexes that are currently active, and optionally, with their associated columns.

Syntax

MSQL.SHOW [WITH COLUMNS]

Returns

An array with index names, and optionally with associated columns.

Complexity

0(n) where n is the number of indexes in the system.

Examples

MSQL.SHOW

MSQL.SHOW WITH COLUMNS

Get index size

MSQL.SIZE

Description

The MSQL.SIZE command returns the number of rows in a given index. The number will increase/decrease as more hash datasets are added or removed in Memurai (or Valkey).

Syntax

MSQL.SIZE   {INDEXNAME}

Complexity

0(n) where n is the number of rows in a given index.

Examples

MSQL.SIZE mymovies

Remove index

MSQL.DESTROY

Description

MSQL.DESTROY deletes the specified index from memory. The hashes on which the index was created are not affected by the removal of the index and remain intact.

Syntax

MSQL.DESTROY  {INDEXNAME}

Returns

Status message: msql.destroy: Table {INDEXNAME} dropped

Examples

MSQL.DESTROY mymovies