Need to extract SQL from mapd crossfilter

sachinpkaushik · 2018-06-17 04:21:25 UTC

Hi I have a requirement wherein I need to embed the mapd crossfilter js generated SQL in my Mapd vega specs.
I need to extract the SQL formed by the mapd crossfilter library and embed it in my vega spec and further prohibit the mapd crossfilter library from making mapd connections.

veda · 2018-06-18 20:20:57 UTC

The new Tweetmap application code shows (https://github.com/mapd/mapd-tweetmap-2) how to use the crossfilter API from your Javascript code. You can refer to /src/services/crossfilter.js for creating the crossfilter object. The file actions.js under src/components/Legend, src/components/MapBody and src/components/TweetSidebar, shows how to use the crossfilter object to then pass the table dimensions. You can refer to the function groupAll() in mapd/mapd-crossfilter/src/mapd-crossfilter.js to see the construction of the SQL query.

Reference: https://www.mapd.com/blog/data-viz-tweetmap-mapd/

sachinpkaushik · 2018-06-19 05:50:23 UTC

Thanks for your reply, let me be more specific I need to access Crossfilter.writeQuery method from outside the mapd crossfilter library, how may I do that? Is there any workaround.

github.com

mapd/mapd-crossfilter/blob/master/src/mapd-crossfilter.js#L1198


  if (softFilterClear == undefined || softFilterClear == false) {
    rangeFilters = []
  }
  filterVal = null
  scopedFilters[dimensionIndex] = ""
  return dimension
}


// Returns the top K selected records based on this dimension"s order.
// Note: observes this dimension"s filter, unlike group and groupAll.
function writeQuery(hasRenderSpec) {
  var projList = ""
  if (projectOnAllDimensionsFlag) {
    var dimensions = crossfilter.getDimensions()
    var nonNullDimensions = []
    for (var d = 0; d < dimensions.length; d++) {
      // other conditions:
      // && dimensions[d] in columnTypeMap && !columnTypeMap[dimensions[d]].is_array
      if (dimensions[d] !== null && dimensions[d] !== "") {
        nonNullDimensions.push(dimensions[d])
      }

marc · 2018-06-19 15:20:26 UTC

Hi Sachinpkaushik,

Unfortunately it might be difficult to return the raw query with the current implementation. You will likely need to fork CrossFilter and expose the function manually by adding:

writeQuery: writeQuery around here.

github.com

mapd/mapd-crossfilter/blob/master/src/mapd-crossfilter.js#L1444


getCrossfilterId: crossfilter.getId,
getTable: crossfilter.getTable,
setTargetSlot: function(s) {
  targetSlot = s
}, // TODO should it return group?
getTargetSlot: function() {
  return targetSlot
},
size: size,
sizeAsync: sizeAsync,
writeFilter: writeFilter,
writeTopQuery: writeTopQuery,
writeBottomQuery: writeBottomQuery,
getReduceExpression: function() {
  return reduceExpression
}, // TODO for testing only
dimension: function() {
  return dimension
},


getProjectOn: getProjectOn,

One caveat to be aware of is there are a few functions of writeQuery within crossFilter, so you might have to expose writeQuery in several areas such as the dimension and group functions.