Dynamically retrieve your dataset structure & map your data types to Luzmo types

Dynamically retrieve your dataset structure

Depending on your database, API, webservice that you want to connect to Luzmo through the plugin, you will retrieve your data structure in a different manner.

For example, for a Plugin exposing data from a Postgres database, you could use following query to retrieve all tables and their columns.

SELECT table_schema, table_name, column_name, data_type 
FROM information_schema.COLUMNS 
WHERE table_schema NOT IN ('pg_catalog','information_schema')

This query returns the tables, their columns and the type of each column:

Of course, the query does not return the results in the format that Luzmo expects. Therefore, in the second step, we need to map the database types and slightly restructure the format.

Mapping types

For simplicity, Luzmo only has three types: hierarchy, numeric and datetime. Before we can send the results back to Luzmo, we need to map the database types to Luzmo types. Below is an example (on the left) of an implementation with regular expressions. The regular expressions make it easier to cope with types that have a variating element (e.g. varchar(4)). If you don’t have those, feel free to use regular string comparison (second example below).

Remember that it's easy to forget to map a type so it is definitely a good idea to print types that could not be mapped.

Implementation with regular expressions

class TypeMapping {
  constructor() {
    this.cumulioToClickhouse = {
      numeric: ['money', '^smallint$', '^numeric\\((.+)(.+)\\)$'],
      datetime: ['^date$', '^time$', '^timestamp$'],
      hierarchy: ['^uuid', '^character$', '^varchar(\\d)$']
    }
    this.clickhouseToCumulio = util.reverseMap(this.cumulioToClickhouse )
  }
// Mapping to Cumul.io types
  toCumulio( clickhouseType ) {
    let foundType = false
    Object.keys( this.clickhouseToCumulio ).forEach(( key ) => {
      const regex = new RegExp( key )
      if ( regex.test( clickhouseType.toLowerCase())) {
        foundType = this.clickhouseToCumulio[ key ]
      }
    })
    return foundType
  }
}

Implementation with regular string comparison

const COLUMN_TYPES = {
  'money': {type: 'numeric'},
  'smallint': {type: 'numeric'},
  'numeric': {type: 'numeric'},
  'date': {type: 'datetime'},
  'time': {type: 'datetime'},
  'timestamp': {type: 'datetime'},
  'uuid': {type: 'hierarchy'},
  'character varying': {type: 'hierarchy'},
  'varchar': {type: 'hierarchy'}
}
// transform types to Cumulio types
function toCumulioType(postgresqlType) {
  if (postgresqlType && COLUMN_TYPES[postgresqlType.toLowerCase()])
    return COLUMN_TYPES[postgresqlType.toLowerCase()].type;
  return 'hierarchy';
}

The ‘^’ in a regular expression identifies the beginning of the string and ‘$’ matches the end of the string. By using those, we avoid matching an array type of varchar instead of varchar by accident.

Cache the schema

Optionally, you can choose to cache the schema in case you might need the type information in the query generation. For more information on caching the schema, please refer to the following article

Transform the metadata to the Luzmo format

This result still needs to be formatted to the Luzmo dataset format. A format that consists of a list of dataset objects with a columns property that refers to a list of columns. In addition, names and descriptions are localized allowing you to provide translations for them.

  generateDatasets( datasetHashmap ) {
    const tableNames = Object.keys( datasetHashmap )
    const result = tableNames.map(( tableName ) => {
      const columnsHash = datasetHashmap[ tableName ]
      const columNames = Object.keys( columnsHash )
      const columns = columNames.map( columnName => {
        const column = columnsHash[ columnName ]
        return {
          id: column.name.toLowerCase(),
          name: { en: column.name },
          type: column.type
        }
      })
      return {
        id: tableName,
        name: {
          en: tableName.replace( '.', ' - ' )
        },
        description: {
          en: ''
        },
        columns
      }
    })
    return result
  }

Additionally, you can find a complete description of the dataset response format in our developer docs, and you’ll find the example implementation in the ‘results-generator.js’ file of our full-scale plugin example on Github.