def query(self, collection, query): solr_query = {} solr_query['collection'] = collection['name'] if query.get('download'): solr_query['rows'] = 1000 solr_query['start'] = 0 else: solr_query['rows'] = int(collection['template']['rows'] or 10) solr_query['start'] = int(query['start']) solr_query['rows'] = min(solr_query['rows'], 1000) solr_query['start'] = min(solr_query['start'], 10000) params = self._get_params() + ( ('q', self._get_q(query)), ('wt', 'json'), ('rows', solr_query['rows']), ('start', solr_query['start']), ) if any(collection['facets']): params += ( ('facet', 'true'), ('facet.mincount', 0), ('facet.limit', 10), ) json_facets = {} timeFilter = self._get_range_borders(collection, query) for facet in collection['facets']: if facet['type'] == 'query': params += (('facet.query', '%s' % facet['field']),) elif facet['type'] == 'range' or facet['type'] == 'range-up': keys = { 'id': '%(id)s' % facet, 'field': facet['field'], 'key': '%(field)s-%(id)s' % facet, 'start': facet['properties']['start'], 'end': facet['properties']['end'], 'gap': facet['properties']['gap'], 'mincount': int(facet['properties']['mincount']) } if timeFilter and timeFilter['time_field'] == facet['field'] and (facet['id'] not in timeFilter['time_filter_overrides'] or facet['widgetType'] != 'histogram-widget'): keys.update(self._get_time_filter_query(timeFilter, facet)) params += ( ('facet.range', '{!key=%(key)s ex=%(id)s f.%(field)s.facet.range.start=%(start)s f.%(field)s.facet.range.end=%(end)s f.%(field)s.facet.range.gap=%(gap)s f.%(field)s.facet.mincount=%(mincount)s}%(field)s' % keys), ) elif facet['type'] == 'field': keys = { 'id': '%(id)s' % facet, 'field': facet['field'], 'key': '%(field)s-%(id)s' % facet, 'limit': int(facet['properties'].get('limit', 10)) + (1 if facet['widgetType'] == 'facet-widget' else 0), 'mincount': int(facet['properties']['mincount']) } params += ( ('facet.field', '{!key=%(key)s ex=%(id)s f.%(field)s.facet.limit=%(limit)s f.%(field)s.facet.mincount=%(mincount)s}%(field)s' % keys), ) elif facet['type'] == 'nested': _f = {} if facet['properties']['facets']: self._n_facet_dimension(facet, _f, facet['properties']['facets'], 1, timeFilter) if facet['properties'].get('domain'): if facet['properties']['domain'].get('blockParent') or facet['properties']['domain'].get('blockChildren'): _f['domain'] = {} if facet['properties']['domain'].get('blockParent'): _f['domain']['blockParent'] = ' OR '.join(facet['properties']['domain']['blockParent']) if facet['properties']['domain'].get('blockChildren'): _f['domain']['blockChildren'] = ' OR '.join(facet['properties']['domain']['blockChildren']) if _f: sort = {'count': facet['properties']['facets'][0]['sort']} for i, agg in enumerate(self._get_dimension_aggregates(facet['properties']['facets'][1:])): if agg['sort'] != 'default': agg_function = self._get_aggregate_function(agg) sort = {'agg_%02d_%02d:%s' % (1, i, agg_function): agg['sort']} if sort.get('count') == 'default': sort['count'] = 'desc' dim_key = [key for key in _f['facet'].keys() if 'dim' in key][0] _f['facet'][dim_key].update({ 'excludeTags': facet['id'], 'offset': 0, 'numBuckets': True, 'allBuckets': True, 'sort': sort #'prefix': '' # Forbidden on numeric fields }) json_facets[facet['id']] = _f['facet'][dim_key] elif facet['type'] == 'function': if facet['properties']['facets']: json_facets[facet['id']] = self._get_aggregate_function(facet['properties']['facets'][0]) if facet['properties']['compare']['is_enabled']: # TODO: global compare override unit = re.split('\d+', facet['properties']['compare']['gap'])[1] json_facets[facet['id']] = { 'type': 'range', 'field': collection['timeFilter'].get('field'), 'start': 'NOW/%s-%s-%s' % (unit, facet['properties']['compare']['gap'], facet['properties']['compare']['gap']), 'end': 'NOW/%s' % unit, 'gap': '+%(gap)s' % facet['properties']['compare'], 'facet': {facet['id']: json_facets[facet['id']]} } if facet['properties']['filter']['is_enabled']: json_facets[facet['id']] = { 'type': 'query', 'q': facet['properties']['filter']['query'] or EMPTY_QUERY.get(), 'facet': {facet['id']: json_facets[facet['id']]} } json_facets['processEmpty'] = True elif facet['type'] == 'pivot': if facet['properties']['facets'] or facet['widgetType'] == 'map-widget': fields = facet['field'] fields_limits = [] for f in facet['properties']['facets']: fields_limits.append('f.%s.facet.limit=%s' % (f['field'], f['limit'])) fields_limits.append('f.%s.facet.mincount=%s' % (f['field'], f['mincount'])) fields += ',' + f['field'] keys = { 'id': '%(id)s' % facet, 'key': '%(field)s-%(id)s' % facet, 'field': facet['field'], 'fields': fields, 'limit': int(facet['properties'].get('limit', 10)), 'mincount': int(facet['properties']['mincount']), 'fields_limits': ' '.join(fields_limits) } params += ( ('facet.pivot', '{!key=%(key)s ex=%(id)s f.%(field)s.facet.limit=%(limit)s f.%(field)s.facet.mincount=%(mincount)s %(fields_limits)s}%(fields)s' % keys), ) if json_facets: params += ( ('json.facet', json.dumps(json_facets)), ) params += self._get_fq(collection, query) fl = urllib.unquote(utf_quoter(','.join(Collection2.get_field_list(collection)))) nested_fields = self._get_nested_fields(collection) if nested_fields: fl += urllib.unquote(utf_quoter(',[child parentFilter="%s"]' % ' OR '.join(nested_fields))) if collection['template']['moreLikeThis'] and fl != ['*']: # Potential conflict with nested documents id_field = collection.get('idField', 'id') params += ( ('mlt', 'true'), ('mlt.fl', fl.replace(',%s' % id_field, '')), ('mlt.mintf', 1), ('mlt.mindf', 1), ('mlt.maxdf', 50), ('mlt.maxntp', 1000), ('mlt.count', 10), #('mlt.minwl', 1), #('mlt.maxwl', 1), ) fl = '*' params += (('fl', fl),) params += ( ('hl', 'true'), ('hl.fl', '*'), ('hl.snippets', 5), ('hl.fragsize', 1000), ) if collection['template']['fieldsSelected']: fields = [] for field in collection['template']['fieldsSelected']: attribute_field = filter(lambda attribute: field == attribute['name'], collection['template']['fieldsAttributes']) if attribute_field: if attribute_field[0]['sort']['direction']: fields.append('%s %s' % (field, attribute_field[0]['sort']['direction'])) if fields: params += ( ('sort', ','.join(fields)), ) response = self._root.get('%(collection)s/select' % solr_query, params) return self._get_json(response)
def query(self, dashboard, query, facet=None): if query['qs'] == [{'q': '_root_:*'}]: return {'response': {'numFound': 0}} filters = [q['q'] for q in query['qs'] if q['q']] filters.extend(self._get_fq(dashboard, query, facet)) result_properties = {} timeFilter = self._get_time_filter_query(dashboard, query) if timeFilter: filters.append(timeFilter) if self.source == 'query': sql_from = '(%(query)s) t' % { 'query': self._get_query(dashboard['name']) } database, table = '', '' else: database, table = self._get_database_table_names(dashboard['name']) sql_from = '%(backticks)s%(database)s%(backticks)s.%(backticks)s%(table)s%(backticks)s' % { 'database': database, 'table': table, 'backticks': self.backticks } if facet and facet['properties']['facets']: for i, _facet in enumerate(facet['properties']['facets']): _facet['position'] = i if facet['type'] == 'nested': fields_dimensions = [ self._get_dimension_field(f)['name'] for f in self._get_dimension_fields(facet) ] last_dimension_seen = False fields = [] for f in reversed(facet['properties']['facets']): if f['aggregate']['function'] == 'count': if not last_dimension_seen: fields.insert( 0, 'COUNT(*) AS %(field)s_%(position)s' % f) last_dimension_seen = True fields.insert( 0, self._get_dimension_field(f) ['name' if last_dimension_seen else 'select']) else: if not last_dimension_seen: fields.insert( 0, self._get_aggregate_function(f) + 'AS %(field)s_%(position)s' % f) has_facet_mincount_greater_than_one = [ f for f in facet['properties']['facets'] if f['mincount'] > 1 ] if has_facet_mincount_greater_than_one and self._supports_count_over( ): mincount_fields_name = [] mincount_fields_operation = [] mincount_where = [] for f in facet['properties']['facets']: mincount_fields_name.append(f['field']) mincount_field_name = 'count__' + '_'.join( mincount_fields_name) mincount_fields_operation.append( 'COUNT(*) OVER (PARTITION BY %s) AS %s' % (', '.join(mincount_fields_name), mincount_field_name)) mincount_where.append( '%s >= %s' % (mincount_field_name, str(f['mincount']))) sql_from = '''(SELECT * FROM (SELECT *, %(fields)s FROM %(sql_from)s) default WHERE %(where)s) default''' % { 'fields': ', '.join(mincount_fields_operation), 'sql_from': sql_from, 'where': ' AND '.join(mincount_where) } order_by = ', '.join([ self._get_dimension_field(f)['order_by'] for f in reversed(facet['properties']['facets']) if f['sort'] != 'default' ]) sql = '''SELECT %(fields)s FROM %(sql_from)s %(filters)s GROUP BY %(fields_dimensions)s %(order_by)s LIMIT %(limit)s''' % { 'sql_from': sql_from, 'fields': ', '.join(fields), 'fields_dimensions': ', '.join(fields_dimensions), 'order_by': 'ORDER BY %s' % order_by if order_by else '', 'filters': self._convert_filters_to_where(filters), 'limit': LIMIT } elif facet['type'] == 'function': # 1 dim only now aggregate_function = facet['properties']['facets'][0][ 'aggregate']['function'] if (aggregate_function == 'percentile' or aggregate_function == 'median') and not self._supports_percentile( ) and self._supports_cume_dist(): sql_from = ''' (SELECT * FROM ( SELECT %(field)s, cume_dist() OVER (ORDER BY %(field)s) * 100 AS cume_dist__%(field)s FROM %(sql_from)s ) DEFAULT WHERE cume_dist__%(field)s >= %(value)s) DEFAULT ''' % { 'field': facet['properties']['facets'][0]['field'], 'value': facet['properties']['facets'][0]['aggregate'] ['percentile'] if aggregate_function == 'percentile' else 50, 'sql_from': sql_from, } sql = '''SELECT %(fields)s FROM %(sql_from)s %(filters)s''' % { 'sql_from': sql_from, 'fields': self._get_aggregate_function( facet['properties']['facets'][0]), 'filters': self._convert_filters_to_where(filters), } elif facet['type'] == 'statement': doc = Document2.objects.get_by_uuid( user=self.user, uuid=facet['properties']['statementUuid'], perm_type='read') snippets = doc.data_dict.get('snippets', []) sql = snippets[0].get('statement', '') result_properties = facet['properties']['result'] else: fields = Collection2.get_field_list(dashboard) order_by = ', '.join([ '%(backticks)s%(name)s%(backticks)s %(direction)s' % { 'backticks': self.backticks, 'name': f['name'], 'direction': f['sort']['direction'] } for f in dashboard['template']['fieldsAttributes'] if f['sort']['direction'] and f['name'] in fields ]) sql = ''' SELECT %(fields)s FROM %(sql_from)s %(filters)s %(order_by)s %(limit)s''' % { 'sql_from': sql_from, 'fields': ', '.join([ '%(backticks)s%(column)s%(backticks)s as %(backticks)s%(column)s%(backticks)s' % { 'backticks': self.backticks, 'column': f } if f != '*' else '*' for f in fields ]), 'filters': self._convert_filters_to_where(filters) if filters else '', 'order_by': 'ORDER BY %s' % order_by if order_by else '', 'limit': 'LIMIT %s' % dashboard['template']['rows'] or LIMIT } editor = make_notebook(name='Execute and watch', editor_type=dashboard['engine'], statement=sql, database=database, status='ready-execute', skip_historify=True, result_properties=result_properties) response = editor.execute(MockRequest(self.user, self.cluster)) if 'handle' in response and response['handle'].get('sync'): response['result'] = self._convert_result(response['result'], dashboard, facet, query) return response
def query(self, collection, query): solr_query = {} solr_query['collection'] = collection['name'] if query.get('download'): solr_query['rows'] = 1000 solr_query['start'] = 0 else: solr_query['rows'] = int(collection['template']['rows'] or 10) solr_query['start'] = int(query['start']) solr_query['rows'] = min(solr_query['rows'], 1000) solr_query['start'] = min(solr_query['start'], 10000) params = self._get_params() + ( ('q', self._get_q(query)), ('wt', 'json'), ('rows', solr_query['rows']), ('start', solr_query['start']), ) if any(collection['facets']): params += ( ('facet', 'true'), ('facet.mincount', 0), ('facet.limit', 10), ) json_facets = {} timeFilter = self._get_range_borders(collection, query) for facet in collection['facets']: if facet['type'] == 'query': params += (('facet.query', '%s' % facet['field']), ) elif facet['type'] == 'range' or facet['type'] == 'range-up': keys = { 'id': '%(id)s' % facet, 'field': facet['field'], 'key': '%(field)s-%(id)s' % facet, 'start': facet['properties']['start'], 'end': facet['properties']['end'], 'gap': facet['properties']['gap'], 'mincount': int(facet['properties']['mincount']) } if timeFilter and timeFilter['time_field'] == facet[ 'field'] and ( facet['id'] not in timeFilter['time_filter_overrides'] or facet['widgetType'] != 'histogram-widget'): keys.update( self._get_time_filter_query(timeFilter, facet)) params += (( 'facet.range', '{!key=%(key)s ex=%(id)s f.%(field)s.facet.range.start=%(start)s f.%(field)s.facet.range.end=%(end)s f.%(field)s.facet.range.gap=%(gap)s f.%(field)s.facet.mincount=%(mincount)s}%(field)s' % keys), ) elif facet['type'] == 'field': keys = { 'id': '%(id)s' % facet, 'field': facet['field'], 'key': '%(field)s-%(id)s' % facet, 'limit': int(facet['properties'].get('limit', 10)) + (1 if facet['widgetType'] == 'facet-widget' else 0), 'mincount': int(facet['properties']['mincount']) } params += (( 'facet.field', '{!key=%(key)s ex=%(id)s f.%(field)s.facet.limit=%(limit)s f.%(field)s.facet.mincount=%(mincount)s}%(field)s' % keys), ) elif facet['type'] == 'nested': _f = { 'field': facet['field'], 'limit': int(facet['properties'].get('limit', 10)) + (1 if facet['widgetType'] == 'text-facet-widget' else 0), 'mincount': int(facet['properties']['mincount']), 'sort': { 'count': facet['properties']['sort'] }, } if facet['properties']['domain'].get( 'blockParent' ) or facet['properties']['domain'].get('blockChildren'): _f['domain'] = {} if facet['properties']['domain'].get('blockParent'): _f['domain']['blockParent'] = ' OR '.join( facet['properties']['domain']['blockParent']) if facet['properties']['domain'].get('blockChildren'): _f['domain']['blockChildren'] = ' OR '.join( facet['properties']['domain']['blockChildren']) if 'start' in facet['properties'] and not facet[ 'properties'].get('type') == 'field': _f.update({ 'type': 'range', 'start': facet['properties']['start'], 'end': facet['properties']['end'], 'gap': facet['properties']['gap'], }) if timeFilter and timeFilter['time_field'] == facet[ 'field'] and ( facet['id'] not in timeFilter['time_filter_overrides'] or facet['widgetType'] != 'bucket-widget'): _f.update( self._get_time_filter_query(timeFilter, facet)) else: _f.update({ 'type': 'terms', 'field': facet['field'], 'excludeTags': facet['id'], 'offset': 0, 'numBuckets': True, 'allBuckets': True, #'prefix': '' # Forbidden on numeric fields }) if facet['properties']['canRange'] and not facet[ 'properties']['isDate']: del _f['mincount'] # Numeric fields do not support if facet['properties']['facets']: self._n_facet_dimension(facet, _f, facet['properties']['facets'], 1) if facet['widgetType'] == 'text-facet-widget': _fname = _f['facet'].keys()[0] _f['sort'] = {_fname: facet['properties']['sort']} # domain = '-d2:NaN' # Solr 6.4 json_facets[facet['id']] = _f elif facet['type'] == 'function': json_facets[facet['id']] = self._get_aggregate_function( facet) json_facets['processEmpty'] = True elif facet['type'] == 'pivot': if facet['properties']['facets'] or facet[ 'widgetType'] == 'map-widget': fields = facet['field'] fields_limits = [] for f in facet['properties']['facets']: fields_limits.append('f.%s.facet.limit=%s' % (f['field'], f['limit'])) fields_limits.append('f.%s.facet.mincount=%s' % (f['field'], f['mincount'])) fields += ',' + f['field'] keys = { 'id': '%(id)s' % facet, 'key': '%(field)s-%(id)s' % facet, 'field': facet['field'], 'fields': fields, 'limit': int(facet['properties'].get('limit', 10)), 'mincount': int(facet['properties']['mincount']), 'fields_limits': ' '.join(fields_limits) } params += (( 'facet.pivot', '{!key=%(key)s ex=%(id)s f.%(field)s.facet.limit=%(limit)s f.%(field)s.facet.mincount=%(mincount)s %(fields_limits)s}%(fields)s' % keys), ) if json_facets: params += (('json.facet', json.dumps(json_facets)), ) params += self._get_fq(collection, query) from dashboard.models import Collection2 fl = urllib.unquote( utf_quoter(','.join(Collection2.get_field_list(collection)))) nested_fields = self._get_nested_fields(collection) if nested_fields: fl += urllib.unquote( utf_quoter(',[child parentFilter="%s"]' % ' OR '.join(nested_fields))) params += (('fl', fl), ) params += ( ('hl', 'true'), ('hl.fl', '*'), ('hl.snippets', 5), ('hl.fragsize', 1000), ) if collection['template']['fieldsSelected']: fields = [] for field in collection['template']['fieldsSelected']: attribute_field = filter( lambda attribute: field == attribute['name'], collection['template']['fieldsAttributes']) if attribute_field: if attribute_field[0]['sort']['direction']: fields.append( '%s %s' % (field, attribute_field[0]['sort']['direction'])) if fields: params += (('sort', ','.join(fields)), ) response = self._root.get('%(collection)s/select' % solr_query, params) return self._get_json(response)
def query(self, dashboard, query, facet=None): database, table = self._get_database_table_names(dashboard['name']) if query['qs'] == [{'q': '_root_:*'}]: return {'response': {'numFound': 0}} filters = [q['q'] for q in query['qs'] if q['q']] filters.extend(self._get_fq(dashboard, query, facet)) timeFilter = self._get_time_filter_query(dashboard, query) if timeFilter: filters.append(timeFilter) if facet: if facet['type'] == 'nested': fields_dimensions = [ self._get_dimension_field(f)['name'] for f in self._get_dimension_fields(facet) ] last_dimension_seen = False fields = [] for f in reversed(facet['properties']['facets']): if f['aggregate']['function'] == 'count': if not last_dimension_seen: fields.insert(0, 'COUNT(*) AS Count') last_dimension_seen = True fields.insert(0, self._get_dimension_field(f)['select']) else: if not last_dimension_seen: fields.insert(0, self._get_aggregate_function(f)) if not last_dimension_seen: fields.insert(0, 'COUNT(*) as Count') fields.insert(0, self._get_dimension_field(facet)['select']) sql = '''SELECT %(fields)s FROM %(database)s.%(table)s %(filters)s GROUP BY %(fields_dimensions)s ORDER BY %(order_by)s LIMIT %(limit)s''' % { 'database': database, 'table': table, 'fields': ', '.join(fields), 'fields_dimensions': ', '.join(fields_dimensions), 'order_by': ', '.join([ self._get_dimension_field(f)['order_by'] for f in self._get_dimension_fields(facet) ]), 'filters': self._convert_filters_to_where(filters), 'limit': LIMIT } elif facet['type'] == 'function': # 1 dim only now sql = '''SELECT %(fields)s FROM %(database)s.%(table)s %(filters)s''' % { 'database': database, 'table': table, 'fields': self._get_aggregate_function(facet), 'filters': self._convert_filters_to_where(filters), } else: fields = Collection2.get_field_list(dashboard) sql = "SELECT %(fields)s FROM `%(database)s`.`%(table)s`" % { 'database': database, 'table': table, 'fields': ', '.join(['`%s`' % f if f != '*' else '*' for f in fields]) } if filters: sql += ' ' + self._convert_filters_to_where(filters) sql += ' LIMIT %s' % LIMIT editor = make_notebook(name='Execute and watch', editor_type=dashboard['engine'], statement=sql, database=database, status='ready-execute', skip_historify=True) response = editor.execute(MockRequest(self.user)) if 'handle' in response and response['handle'].get('sync'): response['result'] = self._convert_result(response['result'], dashboard, facet, query) return response
def query(self, dashboard, query, facet=None): database, table = self._get_database_table_names(dashboard['name']) if query['qs'] == [{'q': '_root_:*'}]: return {'response': {'numFound': 0}} filters = [q['q'] for q in query['qs'] if q['q']] filters.extend(self._get_fq(dashboard, query, facet)) timeFilter = self._get_time_filter_query(dashboard, query) if timeFilter: filters.append(timeFilter) if facet: if facet['type'] == 'nested': fields_dimensions = [self._get_dimension_field(f)['name'] for f in self._get_dimension_fields(facet)] last_dimension_seen = False fields = [] for f in reversed(facet['properties']['facets']): if f['aggregate']['function'] == 'count': if not last_dimension_seen: fields.insert(0, 'COUNT(*) AS Count') last_dimension_seen = True fields.insert(0, self._get_dimension_field(f)['select']) else: if not last_dimension_seen: fields.insert(0, self._get_aggregate_function(f)) if not last_dimension_seen: fields.insert(0, 'COUNT(*) as Count') fields.insert(0, self._get_dimension_field(facet)['select']) sql = '''SELECT %(fields)s FROM %(database)s.%(table)s %(filters)s GROUP BY %(fields_dimensions)s ORDER BY %(order_by)s LIMIT %(limit)s''' % { 'database': database, 'table': table, 'fields': ', '.join(fields), 'fields_dimensions': ', '.join(fields_dimensions), 'order_by': ', '.join([self._get_dimension_field(f)['order_by'] for f in self._get_dimension_fields(facet)]), 'filters': self._convert_filters_to_where(filters), 'limit': LIMIT } elif facet['type'] == 'function': # 1 dim only now sql = '''SELECT %(fields)s FROM %(database)s.%(table)s %(filters)s''' % { 'database': database, 'table': table, 'fields': self._get_aggregate_function(facet), 'filters': self._convert_filters_to_where(filters), } else: fields = Collection2.get_field_list(dashboard) sql = "SELECT %(fields)s FROM `%(database)s`.`%(table)s`" % { 'database': database, 'table': table, 'fields': ', '.join(['`%s`' % f if f != '*' else '*' for f in fields]) } if filters: sql += ' ' + self._convert_filters_to_where(filters) sql += ' LIMIT %s' % LIMIT editor = make_notebook( name='Execute and watch', editor_type=dashboard['engine'], statement=sql, database=database, status='ready-execute', skip_historify=True ) response = editor.execute(MockRequest(self.user)) if 'handle' in response and response['handle'].get('sync'): response['result'] = self._convert_result(response['result'], dashboard, facet, query) return response
def query(self, dashboard, query, facet=None): database, table = self._get_database_table_names(dashboard['name']) if query['qs'] == [{'q': '_root_:*'}]: return {'response': {'numFound': 0}} filters = [q['q'] for q in query['qs'] if q['q']] filters.extend(self._get_fq(dashboard, query, facet)) timeFilter = self._get_time_filter_query(dashboard, query) if timeFilter: filters.append(timeFilter) if facet and facet['properties']['facets']: for i, _facet in enumerate(facet['properties']['facets']): _facet['position'] = i if facet['type'] == 'nested': fields_dimensions = [ self._get_dimension_field(f)['name'] for f in self._get_dimension_fields(facet) ] last_dimension_seen = False fields = [] for f in reversed(facet['properties']['facets']): if f['aggregate']['function'] == 'count': if not last_dimension_seen: fields.insert( 0, 'COUNT(*) AS %(field)s_%(position)s' % f) last_dimension_seen = True fields.insert( 0, self._get_dimension_field(f) ['name' if last_dimension_seen else 'select']) else: if not last_dimension_seen: fields.insert( 0, self._get_aggregate_function(f) + 'AS %(field)s_%(position)s' % f) if self._supports_count_over(): mincount_fields_name = [] mincount_fields_operation = [] mincount_where = [] for f in facet['properties']['facets']: mincount_fields_name.append(f['field']) mincount_field_name = '_'.join( mincount_fields_name) + "_count" mincount_fields_operation.append( 'count(*) over (partition by %s) as %s' % (','.join(mincount_fields_name), mincount_field_name)) mincount_where.append( '%s >= %s' % (mincount_field_name, str(f['mincount']))) sql_mincount = '''(SELECT * FROM (SELECT *,%(fields)s FROM %(database)s.%(table)s) default WHERE %(where)s) default''' % { 'fields': ', '.join(mincount_fields_operation), 'database': database, 'table': table, 'where': ' and '.join(mincount_where) } else: sql_mincount = '(%(database)s.%(table)s)' % { 'database': database, 'table': table } order_by = ', '.join([ self._get_dimension_field(f)['order_by'] for f in reversed(facet['properties']['facets']) if f['sort'] != 'default' ]) sql = '''SELECT %(fields)s FROM %(sql_mincount)s %(filters)s GROUP BY %(fields_dimensions)s %(order_by)s LIMIT %(limit)s''' % { 'sql_mincount': sql_mincount, 'database': database, 'table': table, 'fields': ', '.join(fields), 'fields_dimensions': ', '.join(fields_dimensions), 'order_by': 'ORDER BY %s' % order_by if order_by else '', 'filters': self._convert_filters_to_where(filters), 'limit': LIMIT } elif facet['type'] == 'function': # 1 dim only now sql = '''SELECT %(fields)s FROM %(database)s.%(table)s %(filters)s''' % { 'database': database, 'table': table, 'fields': self._get_aggregate_function( facet['properties']['facets'][0]), 'filters': self._convert_filters_to_where(filters), } else: fields = Collection2.get_field_list(dashboard) sql = "SELECT %(fields)s FROM `%(database)s`.`%(table)s`" % { 'database': database, 'table': table, 'fields': ', '.join(['`%s`' % f if f != '*' else '*' for f in fields]) } if filters: sql += ' ' + self._convert_filters_to_where(filters) sql += ' LIMIT %s' % dashboard['template']['rows'] or LIMIT editor = make_notebook(name='Execute and watch', editor_type=dashboard['engine'], statement=sql, database=database, status='ready-execute', skip_historify=True) response = editor.execute(MockRequest(self.user)) if 'handle' in response and response['handle'].get('sync'): response['result'] = self._convert_result(response['result'], dashboard, facet, query) return response