Redis Data Model

Redis is an open-source’s, high-performance, and in-memory data structure store, commonly used for caching.

Supported Redis Data Types:

• Binary-safe Strings.

• Lists: collections of string elements sorted according to the order of insertion. They are basically linked lists.

• Sets: collections of unique, unsorted string elements.

• Sorted Sets (ZSets): similar to sets but where every string element is associated to a floating number value, called score. The elements are always taken sorted by their score, so unlike sets it is possible to retrieve a range of elements (for example you may ask: give me the top 10, or the bottom 10).

• Hashes: maps composed of fields associated with values. Both the field and the value are strings. This is very similar to Ruby or Python hashes.

Tables #

Generally, querying Redis tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns.

Keys #

Returns keys present in the Redis store.

This table allows you to query all Redis keys in one place.

Columns:

System Tables #

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Redis:

sys_catalogs #

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns:

sys_schemas #

• Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas

Columns:

sys_tables #

Lists the available tables and views.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables

Columns:

sys_tablecolumns #

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Customers table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Customers'

Columns:

sys_procedures #

Describes the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures

Columns:

sys_procedureparameters #

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the SelectEntries stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='SelectEntries' AND Direction=1 OR Direction=2 Columns

Columns:

sys_keycolumns #

Describes the primary and foreign keys.

The following query retrieves the primary key for the Customers table:

SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Customers'

Columns:

sys_foreignkeys #

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'

Columns:

sys_primarykeys #

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

SELECT * FROM sys_primarykeys

Columns:

sys_indexes #

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

SELECT * FROM sys_indexes WHERE IsPrimary='false'

Columns:

Data Source Tables

The following tables return information about how to connect to and query the data source:

sys_connection_props #

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:gathr:redis:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns:

sys_sqlinfo #

Describes the SELECT queries that the connector can offload to the data source.

See SQL Compliance for SQL syntax details.

Discovering the Data Source’s SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Note: Individual tables may have different limitations or requirements on the WHERE clause.

Columns:

Query Information Tables

The following table returns query statistics for data modification queries, including batch operations::

sys_identity #

Returns information about batch operations or single updates.

The following query retrieves the Ids of the modified rows in a batch operation:

SELECT * FROM sys_identity

Columns: