Esempio n. 1
0
def public_widget(widget):
    res = {
        'id': widget.id,
        'width': widget.width,
        'options': json_loads(widget.options),
        'text': widget.text,
        'updated_at': widget.updated_at,
        'created_at': widget.created_at
    }

    if widget.visualization and widget.visualization.id:
        query_data = models.QueryResult.query.get(widget.visualization.query_rel.latest_query_data_id).to_dict()
        res['visualization'] = {
            'type': widget.visualization.type,
            'name': widget.visualization.name,
            'description': widget.visualization.description,
            'options': json_loads(widget.visualization.options),
            'updated_at': widget.visualization.updated_at,
            'created_at': widget.visualization.created_at,
            'query': {
                'query': ' ',  # workaround, as otherwise the query data won't be loaded.
                'name': widget.visualization.query_rel.name,
                'description': widget.visualization.query_rel.description,
                'options': {},
                'latest_query_data': query_data
            }
        }

    return res
Esempio n. 2
0
    def get_schema(self, get_stats=False):
        query = """
        select release_version from system.local;
        """
        results, error = self.run_query(query, None)
        results = json_loads(results)
        release_version = results['rows'][0]['release_version']

        query = """
        SELECT table_name, column_name
        FROM system_schema.columns
        WHERE keyspace_name ='{}';
        """.format(self.configuration['keyspace'])

        if release_version.startswith('2'):
                query = """
                SELECT columnfamily_name AS table_name, column_name
                FROM system.schema_columns
                WHERE keyspace_name ='{}';
                """.format(self.configuration['keyspace'])

        results, error = self.run_query(query, None)
        results = json_loads(results)

        schema = {}
        for row in results['rows']:
            table_name = row['table_name']
            column_name = row['column_name']
            if table_name not in schema:
                schema[table_name] = {'name': table_name, 'columns': []}
            schema[table_name]['columns'].append(column_name)

        return schema.values()
Esempio n. 3
0
def get_waiting_in_queue(queue_name):
    jobs = []
    for raw in redis_connection.lrange(queue_name, 0, -1):
        job = json_loads(raw)
        try:
            args = json_loads(job['headers']['argsrepr'])
            if args.get('query_id') == 'adhoc':
                args['query_id'] = None
        except ValueError:
            args = {}

        job_row = {
            'state': 'waiting_in_queue',
            'task_name': job['headers']['task'],
            'worker': None,
            'worker_pid': None,
            'start_time': None,
            'task_id': job['headers']['id'],
            'queue': job['properties']['delivery_info']['routing_key']
        }

        job_row.update(args)
        jobs.append(job_row)

    return jobs
Esempio n. 4
0
 def process_formdata(self, valuelist):
     if valuelist:
         try:
             json_loads(valuelist[0])
         except ValueError:
             raise ValueError(self.gettext(u'Invalid JSON'))
         self.data = valuelist[0]
     else:
         self.data = ''
Esempio n. 5
0
    def get_schema(self, get_stats=False):
        query = """
        SELECT col.table_schema,
               col.table_name,
               col.column_name
        FROM {database}.information_schema.columns col
        WHERE col.table_schema <> 'INFORMATION_SCHEMA'
        """.format(database=self.configuration['database'])

        results, error = self.run_query(query, None)

        if error is not None:
            raise Exception("Failed getting schema.")

        schema = {}
        results = json_loads(results)

        for row in results['rows']:
            table_name = '{}.{}'.format(row['TABLE_SCHEMA'], row['TABLE_NAME'])

            if table_name not in schema:
                schema[table_name] = {'name': table_name, 'columns': []}

            schema[table_name]['columns'].append(row['COLUMN_NAME'])

        return schema.values()
Esempio n. 6
0
    def _get_tables(self, schema):
        query = """
        SELECT table_schema, table_name, column_name
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_schema NOT IN ('guest','INFORMATION_SCHEMA','sys','db_owner','db_accessadmin'
                                  ,'db_securityadmin','db_ddladmin','db_backupoperator','db_datareader'
                                  ,'db_datawriter','db_denydatareader','db_denydatawriter'
                                  );
        """

        results, error = self.run_query(query, None)

        if error is not None:
            raise Exception("Failed getting schema.")

        results = json_loads(results)

        for row in results['rows']:
            if row['table_schema'] != self.configuration['db']:
                table_name = u'{}.{}'.format(row['table_schema'], row['table_name'])
            else:
                table_name = row['table_name']

            if table_name not in schema:
                schema[table_name] = {'name': table_name, 'columns': []}

            schema[table_name]['columns'].append(row['column_name'])

        return schema.values()
Esempio n. 7
0
    def _get_tables(self, schema):
        query = """
        SELECT col.table_schema,
               col.table_name,
               col.column_name
        FROM `information_schema`.`columns` col
        WHERE col.table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys');
        """

        results, error = self.run_query(query, None)

        if error is not None:
            raise Exception("Failed getting schema.")

        results = json_loads(results)

        for row in results['rows']:
            if row['table_schema'] != self.configuration['db']:
                table_name = u'{}.{}'.format(row['table_schema'], row['table_name'])
            else:
                table_name = row['table_name']

            if table_name not in schema:
                schema[table_name] = {'name': table_name, 'columns': []}

            schema[table_name]['columns'].append(row['column_name'])

        return schema.values()
Esempio n. 8
0
    def run_query(self, query, user):
        logger.debug("BigQuery got query: %s", query)

        bigquery_service = self._get_bigquery_service()
        jobs = bigquery_service.jobs()

        try:
            if "totalMBytesProcessedLimit" in self.configuration:
                limitMB = self.configuration["totalMBytesProcessedLimit"]
                processedMB = self._get_total_bytes_processed(jobs, query) / 1000.0 / 1000.0
                if limitMB < processedMB:
                    return None, "Larger than %d MBytes will be processed (%f MBytes)" % (limitMB, processedMB)

            data = self._get_query_result(jobs, query)
            error = None

            json_data = json_dumps(data, ignore_nan=True)
        except apiclient.errors.HttpError as e:
            json_data = None
            if e.resp.status == 400:
                error = json_loads(e.content)['error']['message']
            else:
                error = e.content
        except KeyboardInterrupt:
            error = "Query cancelled by user."
            json_data = None

        return json_data, error
Esempio n. 9
0
    def run_query(self, query, user):
        jql_url = '{}/rest/api/2/search'.format(self.configuration["url"])

        try:
            query = json_loads(query)
            query_type = query.pop('queryType', 'select')
            field_mapping = FieldMapping(query.pop('fieldMapping', {}))

            if query_type == 'count':
                query['maxResults'] = 1
                query['fields'] = ''
            else:
                query['maxResults'] = query.get('maxResults', 1000)

            response, error = self.get_response(jql_url, params=query)
            if error is not None:
                return None, error

            data = response.json()

            if query_type == 'count':
                results = parse_count(data)
            else:
                results = parse_issues(data, field_mapping)

            return results.to_json(), None
        except KeyboardInterrupt:
            return None, "Query cancelled by user."
Esempio n. 10
0
def parse_tasks(task_lists, state):
    rows = []

    for task in itertools.chain(*task_lists.values()):
        task_row = {
            'state': state,
            'task_name': task['name'],
            'worker': task['hostname'],
            'queue': task['delivery_info']['routing_key'],
            'task_id': task['id'],
            'worker_pid': task['worker_pid'],
            'start_time': task['time_start'],
        }

        if task['name'] == 'redash.tasks.execute_query':
            try:
                args = json_loads(task['args'])
            except ValueError:
                args = {}

            if args.get('query_id') == 'adhoc':
                args['query_id'] = None

            task_row.update(args)

        rows.append(task_row)

    return rows
Esempio n. 11
0
    def get_schema(self, get_stats=False):
        query = """
        SELECT TABLE_SCHEMA,
               TABLE_NAME,
               COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA <> 'INFORMATION_SCHEMA'
        """

        results, error = self.run_query(query, None)

        if error is not None:
            raise Exception("Failed getting schema.")

        schema = {}
        results = json_loads(results)

        for row in results['rows']:
            table_name = '{}.{}'.format(row['TABLE_SCHEMA'], row['TABLE_NAME'])

            if table_name not in schema:
                schema[table_name] = {'name': table_name, 'columns': []}

            schema[table_name]['columns'].append(row['COLUMN_NAME'])

        return schema.values()
Esempio n. 12
0
    def _fetch_rows(self, query_id):
        query = models.Query.get_by_id_and_org(query_id, self.current_org)
        require_access(query.data_source.groups, self.current_user, view_only)

        query_result = models.QueryResult.get_by_id_and_org(
            query.latest_query_data_id, self.current_org)

        return json_loads(query_result.data)["rows"]
Esempio n. 13
0
    def data(self):
        if self._data is None:
            return None

        if not hasattr(self, DESERIALIZED_DATA_ATTR):
            setattr(self, DESERIALIZED_DATA_ATTR, json_loads(self._data))

        return self._deserialized_data
Esempio n. 14
0
 def _get_analytics_service(self):
     scope = ["https://www.googleapis.com/auth/analytics.readonly"]
     key = json_loads(b64decode(self.configuration["jsonKeyFile"]))
     creds = ServiceAccountCredentials.from_json_keyfile_dict(key, scope)
     return build("analytics",
                  "v3",
                  http=creds.authorize(httplib2.Http()),
                  cache_discovery=False)
Esempio n. 15
0
def serialize_dashboard(obj,
                        with_widgets=False,
                        user=None,
                        with_favorite_state=True):
    layout = json_loads(obj.layout)

    widgets = []

    if with_widgets:
        for w in obj.widgets:
            if w.visualization_id is None:
                widgets.append(serialize_widget(w))
            elif user and has_access(w.visualization.query_rel, user,
                                     view_only):
                widgets.append(serialize_widget(w))
            else:
                widget = project(serialize_widget(w),
                                 ('id', 'width', 'dashboard_id', 'options',
                                  'created_at', 'updated_at'))
                widget['restricted'] = True
                widgets.append(widget)
    else:
        widgets = None

    if obj.user is not None:
        _user = obj.user.to_dict()
    else:
        assets = app.extensions['webpack']['assets'] or {}
        path = 'images/avatar.svg'
        profile_image_url = url_for('static', filename=assets.get(path, path))
        _user = {
            'name': 'user',
            'profile_image_url': profile_image_url,
        }

    d = {
        'id': obj.id,
        'slug': obj.slug,
        'name': obj.name,
        'user_id': obj.user_id,
        # TODO: we should properly load the users
        'user': _user,
        'layout': layout,
        'dashboard_filters_enabled': obj.dashboard_filters_enabled,
        'widgets': widgets,
        'is_archived': obj.is_archived,
        'is_draft': obj.is_draft,
        'tags': obj.tags or [],
        # TODO: bulk load favorites
        'updated_at': obj.updated_at,
        'created_at': obj.created_at,
        'version': obj.version
    }

    if with_favorite_state:
        d['is_favorite'] = models.Favorite.is_favorite(current_user.id, obj)

    return d
Esempio n. 16
0
    def test_get_dashboard(self):
        d1 = self.factory.create_dashboard()
        rv = self.make_request('get', '/api/dashboards/{0}'.format(d1.slug))
        self.assertEquals(rv.status_code, 200)

        expected = serialize_dashboard(d1, with_widgets=True, with_favorite_state=False)
        actual = json_loads(rv.data)

        self.assertResponseEqual(expected, actual)
Esempio n. 17
0
    def _get_definitions(self, schema, query):
        results, error = self.run_query(query, None)

        if error is not None:
            raise Exception("Failed getting schema.")

        results = json_loads(results)

        build_schema(results, schema)
Esempio n. 18
0
def _load_result(query_id):
    from redash.authentication.org_resolving import current_org
    from redash import models

    query = models.Query.get_by_id_and_org(query_id, current_org)
    require_access(query.data_source.groups, current_user, view_only)
    query_result = models.QueryResult.get_by_id_and_org(query.latest_query_data_id, current_org)

    return json_loads(query_result.data)
Esempio n. 19
0
    def test_get_dashboard_with_slug(self):
        d1 = self.factory.create_dashboard()
        rv = self.make_request("get", "/api/dashboards/{0}?legacy".format(d1.slug))
        self.assertEqual(rv.status_code, 200)

        expected = serialize_dashboard(d1, with_widgets=True, with_favorite_state=False)
        actual = json_loads(rv.data)

        self.assertResponseEqual(expected, actual)
Esempio n. 20
0
def serialize_dashboard(obj,
                        with_widgets=False,
                        user=None,
                        with_favorite_state=True):
    layout = json_loads(obj.layout)

    widgets = []

    if with_widgets:
        for w in obj.widgets:
            if w.visualization_id is None:
                widgets.append(serialize_widget(w))
            elif user and has_access(w.visualization.query_rel, user,
                                     view_only):
                widgets.append(serialize_widget(w))
            else:
                widget = project(
                    serialize_widget(w),
                    (
                        "id",
                        "width",
                        "dashboard_id",
                        "options",
                        "created_at",
                        "updated_at",
                    ),
                )
                widget["restricted"] = True
                widgets.append(widget)
    else:
        widgets = None

    d = {
        "id": obj.id,
        "slug": obj.name_as_slug,
        "name": obj.name,
        "user_id": obj.user_id,
        "user": {
            "id": obj.user.id,
            "name": obj.user.name,
            "email": obj.user.email,
            "profile_image_url": obj.user.profile_image_url,
        },
        "layout": layout,
        "dashboard_filters_enabled": obj.dashboard_filters_enabled,
        "widgets": widgets,
        "options": obj.options,
        "is_archived": obj.is_archived,
        "is_draft": obj.is_draft,
        "tags": obj.tags or [],
        "updated_at": obj.updated_at,
        "created_at": obj.created_at,
        "version": obj.version,
    }

    return d
Esempio n. 21
0
 def render_template(self):
     if not self.template:
         return ''
     data = json_loads(self.query_rel.latest_query_data.data)
     context = {
         'rows': data['rows'],
         'cols': data['columns'],
         'state': self.state
     }
     return mustache_render(self.template, context)
Esempio n. 22
0
    def get_databases(self):
        query = "SHOW DATABASES"
        results, error = self.run_query(query, None)

        if error is not None:
            raise Exception("Failed getting schema.")

        results = json_loads(results)

        return [row["namespace"] for row in results["rows"]]
Esempio n. 23
0
 def to_dict(self):
     return {
         'id': self.id,
         'query_hash': self.query_hash,
         'query': self.query_text,
         'data': json_loads(self.data),
         'data_source_id': self.data_source_id,
         'runtime': self.runtime,
         'retrieved_at': self.retrieved_at
     }
Esempio n. 24
0
 def to_dict(self):
     return {
         'id': self.id,
         'query_hash': self.query_hash,
         'query': self.query_text,
         'data': json_loads(self.data),
         'data_source_id': self.data_source_id,
         'runtime': self.runtime,
         'retrieved_at': self.retrieved_at
     }
Esempio n. 25
0
def serialize_dashboard(obj,
                        with_widgets=False,
                        user=None,
                        with_favorite_state=True):
    layout = json_loads(obj.layout)

    widgets = []

    if with_widgets:
        for w in obj.widgets:
            if w.visualization_id is None:
                widgets.append(serialize_widget(w))
            elif user and has_access(w.visualization.query_rel, user,
                                     view_only):
                widgets.append(serialize_widget(w))
            else:
                widget = project(
                    serialize_widget(w),
                    (
                        "id",
                        "width",
                        "dashboard_id",
                        "options",
                        "created_at",
                        "updated_at",
                    ),
                )
                widget["restricted"] = True
                widgets.append(widget)
    else:
        widgets = None

    d = {
        "id": obj.id,
        "slug": obj.slug,
        "name": obj.name,
        "user_id": obj.user_id,
        # TODO: we should properly load the users
        "user": obj.user.to_dict(),
        "layout": layout,
        "dashboard_filters_enabled": obj.dashboard_filters_enabled,
        "widgets": widgets,
        "is_archived": obj.is_archived,
        "is_draft": obj.is_draft,
        "tags": obj.tags or [],
        # TODO: bulk load favorites
        "updated_at": obj.updated_at,
        "created_at": obj.created_at,
        "version": obj.version,
    }

    if with_favorite_state:
        d["is_favorite"] = models.Favorite.is_favorite(current_user.id, obj)

    return d
    def get_databases(self):
        query = "SHOW DATABASES"
        results, error = self.run_query(query, None)

        if error is not None:
            raise Exception("Failed getting schema.")

        results = json_loads(results)

        first_column_name = results["columns"][0]["name"]
        return [row[first_column_name] for row in results["rows"]]
Esempio n. 27
0
def _load_result(query_id):
    from redash.authentication.org_resolving import current_org
    from redash import models

    query = models.Query.get_by_id_and_org(query_id, current_org)

    if query.data_source:
        query_result = models.QueryResult.get_by_id_and_org(query.latest_query_data_id, current_org)
        return json_loads(query_result.data)
    else:
        abort(400, message="This query is detached from any data source. Please select a different query.")
Esempio n. 28
0
def _load_result(query_id, org):
    from redash import models

    query = models.Query.get_by_id_and_org(query_id, org)

    if query.data_source:
        query_result = models.QueryResult.get_by_id_and_org(
            query.latest_query_data_id, org)
        return json_loads(query_result.data)
    else:
        raise QueryDetachedFromDataSourceError(query_id)
Esempio n. 29
0
    def _get_spreadsheet_service(self):
        scope = ["https://spreadsheets.google.com/feeds"]

        key = json_loads(b64decode(self.configuration["jsonKeyFile"]))
        creds = ServiceAccountCredentials.from_json_keyfile_dict(key, scope)

        timeout_session = Session()
        timeout_session.requests_session = TimeoutSession()
        spreadsheetservice = gspread.Client(auth=creds, session=timeout_session)
        spreadsheetservice.login()
        return spreadsheetservice
Esempio n. 30
0
    def make_csv_content(self):
        s = cStringIO.StringIO()

        query_data = json_loads(self.data)
        writer = csv.DictWriter(s, extrasaction="ignore", fieldnames=[col['name'] for col in query_data['columns']])
        writer.writer = utils.UnicodeWriter(s)
        writer.writeheader()
        for row in query_data['rows']:
            writer.writerow(row)

        return s.getvalue()
Esempio n. 31
0
def _load_result(query_id):
    from redash.authentication.org_resolving import current_org
    from redash import models

    query = models.Query.get_by_id_and_org(query_id, current_org)

    if query.data_source:
        query_result = models.QueryResult.get_by_id_and_org(query.latest_query_data_id, current_org)
        return json_loads(query_result.data)
    else:
        abort(400, message="This query is detached from any data source. Please select a different query.")
Esempio n. 32
0
    def make_csv_content(self):
        s = cStringIO.StringIO()

        query_data = json_loads(self.data)
        writer = csv.DictWriter(s, extrasaction="ignore", fieldnames=[col['name'] for col in query_data['columns']])
        writer.writer = utils.UnicodeWriter(s)
        writer.writeheader()
        for row in query_data['rows']:
            writer.writerow(row)

        return s.getvalue()
Esempio n. 33
0
    def run_query(self, query, user):
        try:
            error = None

            logger.debug(query)
            query_dict = json_loads(query)

            index_name = query_dict.pop("index", "")
            result_fields = query_dict.pop("result_fields", None)

            if not self.server_url:
                error = "Missing configuration key 'server'"
                return None, error

            url = "{0}/{1}/_search".format(self.server_url, index_name)
            mapping_url = "{0}/{1}/_mapping".format(self.server_url,
                                                    index_name)

            mappings, error = self._get_query_mappings(mapping_url)
            if error:
                return None, error

            logger.debug("Using URL: %s", url)
            logger.debug("Using query: %s", query_dict)
            r = requests.get(url, json=query_dict, auth=self.auth)
            r.raise_for_status()
            logger.debug("Result: %s", r.json())

            result_columns = []
            result_rows = []
            self._parse_results(mappings, result_fields, r.json(),
                                result_columns, result_rows)

            json_data = json_dumps({
                "columns": result_columns,
                "rows": result_rows
            })
        except KeyboardInterrupt:
            logger.exception(e)
            error = "Query cancelled by user."
            json_data = None
        except requests.HTTPError as e:
            logger.exception(e)
            error = "Failed to execute query. Return Code: {0}   Reason: {1}".format(
                r.status_code, r.text)
            json_data = None
        except requests.exceptions.RequestException as e:
            logger.exception(e)
            error = "Connection refused"
            json_data = None

        return json_data, error
Esempio n. 34
0
def serialize_dashboard(obj,
                        with_widgets=False,
                        user=None,
                        with_favorite_state=True):
    layout = json_loads(obj.layout)

    widgets = []

    if with_widgets:
        for w in obj.widgets:
            if w.visualization_id is None:
                widgets.append(serialize_widget(w))
            elif user and has_access(w.visualization.query_rel, user,
                                     view_only):
                widgets.append(serialize_widget(w))
            else:
                widget = project(serialize_widget(w),
                                 ('id', 'width', 'dashboard_id', 'options',
                                  'created_at', 'updated_at'))
                widget['restricted'] = True
                widgets.append(widget)
    else:
        widgets = None

    d = {
        'id': obj.id,
        'slug': obj.slug,
        'name': obj.name,
        'user_id': current_user.id,
        'created_by': obj.user.to_dict(),
        'user': current_user.to_dict(),
        'layout': layout,
        'dashboard_filters_enabled': obj.dashboard_filters_enabled,
        'widgets': widgets,
        'is_archived': obj.is_archived,
        'is_draft': obj.is_draft,
        'tags': obj.tags or [],
        'updated_at': obj.updated_at,
        'created_at': obj.created_at,
        'version': obj.version,
        'background_image': obj.background_image,
        'description': obj.description,
        'type': obj.type or 'dashboard',
        'folder_id': obj.folder_id
    }

    d['groups'] = [
        g.to_dict(with_permissions_for=True)
        for g in models.DashboardGroup.get_by_dashboard(obj)
    ]

    return d
Esempio n. 35
0
    def _get_spreadsheet_service(self):
        scope = [
            'https://spreadsheets.google.com/feeds',
        ]

        key = json_loads(b64decode(self.configuration['jsonKeyFile']))
        creds = ServiceAccountCredentials.from_json_keyfile_dict(key, scope)

        timeout_session = HTTPSession()
        timeout_session.requests_session = TimeoutSession()
        spreadsheetservice = gspread.Client(auth=creds, http_session=timeout_session)
        spreadsheetservice.login()
        return spreadsheetservice
Esempio n. 36
0
    def get_definition(self, base_url):
        def_url = base_url + "adapter.json"

        response, error = self.__get_json_response(def_url)
        if error is not None:
            return None, error

        json_data = response.content.strip()
        if json_data:
            json_data_obj = json_loads(json_data)
            return json_data_obj, None
        else:
            return None, "Got empty response from '{}'.".format(base_url)
Esempio n. 37
0
    def test_success(self):
        d1 = self.factory.create_dashboard()
        access_token = self.factory.create_access_token()
        rv = self.make_request(
            "get",
            "/api/dashboards/embed/{}?access_token={}".format(d1.id, access_token),
        )
        self.assertEqual(rv.status_code, 200)

        expected = public_dashboard(d1)
        actual = json_loads(rv.data)

        self.assertResponseEqual(expected, actual)
Esempio n. 38
0
    def _get_bigquery_service(self):
        scope = [
            "https://www.googleapis.com/auth/bigquery",
            "https://www.googleapis.com/auth/drive"
        ]

        key = json_loads(b64decode(self.configuration['jsonKeyFile']))

        creds = ServiceAccountCredentials.from_json_keyfile_dict(key, scope)
        http = httplib2.Http(timeout=settings.BIGQUERY_HTTP_TIMEOUT)
        http = creds.authorize(http)

        return build("bigquery", "v2", http=http)
Esempio n. 39
0
    def _get_bigquery_service(self):
        scope = [
            "https://www.googleapis.com/auth/bigquery",
            "https://www.googleapis.com/auth/drive"
        ]

        key = json_loads(b64decode(self.configuration['jsonKeyFile']))

        creds = ServiceAccountCredentials.from_json_keyfile_dict(key, scope)
        http = httplib2.Http(timeout=settings.BIGQUERY_HTTP_TIMEOUT)
        http = creds.authorize(http)

        return build("bigquery", "v2", http=http)
Esempio n. 40
0
def get_query_results(user, query_id, bring_from_cache):
    query = _load_query(user, query_id)
    if bring_from_cache:
        if query.latest_query_data_id is not None:
            results = query.latest_query_data.data
        else:
            raise Exception("No cached result available for query {}.".format(query.id))
    else:
        results, error = query.data_source.query_runner.run_query(query.query_text, user)
        if error:
            raise Exception("Failed loading results for query id {}.".format(query.id))

    return json_loads(results)
def get_query_results(user, query_id, bring_from_cache):
    query = _load_query(user, query_id)
    if bring_from_cache:
        if query.latest_query_data_id is not None:
            results = query.latest_query_data.data
        else:
            raise Exception("No cached result available for query {}.".format(query.id))
    else:
        results, error = query.data_source.query_runner.run_query(query.query_text, user)
        if error:
            raise Exception("Failed loading results for query id {}.".format(query.id))

    return json_loads(results)
Esempio n. 42
0
def public_widget(widget):
    res = {
        'id': widget.id,
        'width': widget.width,
        'options': json_loads(widget.options),
        'text': widget.text,
        'updated_at': widget.updated_at,
        'created_at': widget.created_at
    }

    if widget.visualization and widget.visualization.id:
        res['visualization'] = public_visualization(widget.visualization)

    return res
Esempio n. 43
0
    def _get_tables(self, schema):
        query_table = "select tbl_name from sqlite_master where type='table'"
        query_columns = "PRAGMA table_info(%s)"

        results, error = self.run_query(query_table, None)

        if error is not None:
            raise Exception("Failed getting schema.")

        results = json_loads(results)

        for row in results['rows']:
            table_name = row['tbl_name']
            schema[table_name] = {'name': table_name, 'columns': []}
            results_table, error = self.run_query(query_columns % (table_name,), None)
            if error is not None:
                raise Exception("Failed getting schema.")

            results_table = json_loads(results_table)
            for row_column in results_table['rows']:
                schema[table_name]['columns'].append(row_column['name'])

        return schema.values()
Esempio n. 44
0
    def get_schema(self, refresh=False):
        cache = None
        if not refresh:
            cache = redis_connection.get(self._schema_key)

        if cache is None:
            query_runner = self.query_runner
            schema = sorted(query_runner.get_schema(get_stats=refresh), key=lambda t: t['name'])

            redis_connection.set(self._schema_key, json_dumps(schema))
        else:
            schema = json_loads(cache)

        return schema
Esempio n. 45
0
    def _get_tables(self, schema):
        query_table = "select tbl_name from sqlite_master where type='table'"
        query_columns = "PRAGMA table_info(%s)"

        results, error = self.run_query(query_table, None)

        if error is not None:
            raise Exception("Failed getting schema.")

        results = json_loads(results)

        for row in results['rows']:
            table_name = row['tbl_name']
            schema[table_name] = {'name': table_name, 'columns': []}
            results_table, error = self.run_query(query_columns % (table_name,), None)
            if error is not None:
                raise Exception("Failed getting schema.")

            results_table = json_loads(results_table)
            for row_column in results_table['rows']:
                schema[table_name]['columns'].append(row_column['name'])

        return list(schema.values())
Esempio n. 46
0
    def get_schema(self, get_stats=False):
        query = """
        select release_version from system.local;
        """
        results, error = self.run_query(query, None)
        results = json_loads(results)
        release_version = results["rows"][0]["release_version"]

        query = """
        SELECT table_name, column_name
        FROM system_schema.columns
        WHERE keyspace_name ='{}';
        """.format(
            self.configuration["keyspace"]
        )

        if release_version.startswith("2"):
            query = """
                SELECT columnfamily_name AS table_name, column_name
                FROM system.schema_columns
                WHERE keyspace_name ='{}';
                """.format(
                self.configuration["keyspace"]
            )

        results, error = self.run_query(query, None)
        results = json_loads(results)

        schema = {}
        for row in results["rows"]:
            table_name = row["table_name"]
            column_name = row["column_name"]
            if table_name not in schema:
                schema[table_name] = {"name": table_name, "columns": []}
            schema[table_name]["columns"].append(column_name)

        return list(schema.values())
Esempio n. 47
0
    def run_query(self, query, user):
        try:
            error = None

            logger.debug(query)
            query_dict = json_loads(query)

            index_name = query_dict.pop("index", "")
            result_fields = query_dict.pop("result_fields", None)

            if not self.server_url:
                error = "Missing configuration key 'server'"
                return None, error

            url = "{0}/{1}/_search".format(self.server_url, index_name)
            mapping_url = "{0}/{1}/_mapping".format(self.server_url, index_name)

            mappings, error = self._get_query_mappings(mapping_url)
            if error:
                return None, error

            logger.debug("Using URL: %s", url)
            logger.debug("Using query: %s", query_dict)
            r = requests.get(url, json=query_dict, auth=self.auth)
            r.raise_for_status()
            logger.debug("Result: %s", r.json())

            result_columns = []
            result_rows = []
            self._parse_results(mappings, result_fields, r.json(), result_columns, result_rows)

            json_data = json_dumps({
                "columns": result_columns,
                "rows": result_rows
            })
        except KeyboardInterrupt:
            logger.exception(e)
            error = "Query cancelled by user."
            json_data = None
        except requests.HTTPError as e:
            logger.exception(e)
            error = "Failed to execute query. Return Code: {0}   Reason: {1}".format(r.status_code, r.text)
            json_data = None
        except requests.exceptions.RequestException as e:
            logger.exception(e)
            error = "Connection refused"
            json_data = None

        return json_data, error
Esempio n. 48
0
 def get_schema(self, get_stats=False):
     query = "MATCH (n) RETURN distinct keys(n) as x, labels(n) as y"
     data, error = self.run_query(query, None)
     if error is not None:
         raise Exception("Failed getting schema.")
     results = json_loads(data)
     arr = []
     schema = {}
     for x in results["rows"]:
         table_name = x["y"][0]
         if table_name not in schema:
             schema[table_name] = {'name': table_name, 'columns': []}
         schema[table_name]['columns'] = list(
             set(schema[table_name]['columns'] + x["x"]))
     return list(schema.values())
Esempio n. 49
0
def serialize_visualization(object, with_query=True):
    d = {
        'id': object.id,
        'type': object.type,
        'name': object.name,
        'description': object.description,
        'options': json_loads(object.options),
        'updated_at': object.updated_at,
        'created_at': object.created_at
    }

    if with_query:
        d['query'] = serialize_query(object.query_rel)

    return d
Esempio n. 50
0
def serialize_widget(object):
    d = {
        'id': object.id,
        'width': object.width,
        'options': json_loads(object.options),
        'dashboard_id': object.dashboard_id,
        'text': object.text,
        'updated_at': object.updated_at,
        'created_at': object.created_at
    }

    if object.visualization and object.visualization.id:
        d['visualization'] = serialize_visualization(object.visualization)

    return d
Esempio n. 51
0
def serialize_visualization(object, with_query=True):
    d = {
        'id': object.id,
        'type': object.type,
        'name': object.name,
        'description': object.description,
        'options': json_loads(object.options),
        'updated_at': object.updated_at,
        'created_at': object.created_at
    }

    if with_query:
        d['query'] = serialize_query(object.query_rel)

    return d
Esempio n. 52
0
def serialize_widget(object):
    d = {
        'id': object.id,
        'width': object.width,
        'options': json_loads(object.options),
        'dashboard_id': object.dashboard_id,
        'text': object.text,
        'updated_at': object.updated_at,
        'created_at': object.created_at
    }

    if object.visualization and object.visualization.id:
        d['visualization'] = serialize_visualization(object.visualization)

    return d
Esempio n. 53
0
    def get_schema(self, refresh=False):
        key = "data_source:schema:{}".format(self.id)

        cache = None
        if not refresh:
            cache = redis_connection.get(key)

        if cache is None:
            query_runner = self.query_runner
            schema = sorted(query_runner.get_schema(get_stats=refresh), key=lambda t: t['name'])

            redis_connection.set(key, json_dumps(schema))
        else:
            schema = json_loads(cache)

        return schema
Esempio n. 54
0
    def _get_definitions(self, schema, query):
        results, error = self.run_query(query, None)

        if error is not None:
            raise Exception("Failed getting schema.")

        results = json_loads(results)

        for row in results['rows']:
            if row['table_schema'] != 'public':
                table_name = u'{}.{}'.format(row['table_schema'], row['table_name'])
            else:
                table_name = row['table_name']

            if table_name not in schema:
                schema[table_name] = {'name': table_name, 'columns': []}

            schema[table_name]['columns'].append(row['column_name'])
Esempio n. 55
0
    def _get_tables(self, schema):
        query = "SELECT database, table, name FROM system.columns WHERE database NOT IN ('system')"

        results, error = self.run_query(query, None)

        if error is not None:
            raise Exception("Failed getting schema.")

        results = json_loads(results)

        for row in results['rows']:
            table_name = '{}.{}'.format(row['database'], row['table'])

            if table_name not in schema:
                schema[table_name] = {'name': table_name, 'columns': []}

            schema[table_name]['columns'].append(row['name'])

        return schema.values()
Esempio n. 56
0
    def evaluate(self):
        data = json_loads(self.query_rel.latest_query_data.data)

        if data['rows'] and self.options['column'] in data['rows'][0]:
            value = data['rows'][0][self.options['column']]
            op = self.options['op']

            if op == 'greater than' and value > self.options['value']:
                new_state = self.TRIGGERED_STATE
            elif op == 'less than' and value < self.options['value']:
                new_state = self.TRIGGERED_STATE
            elif op == 'equals' and value == self.options['value']:
                new_state = self.TRIGGERED_STATE
            else:
                new_state = self.OK_STATE
        else:
            new_state = self.UNKNOWN_STATE

        return new_state
Esempio n. 57
0
def serialize_query_result_to_csv(query_result):
    s = cStringIO.StringIO()

    query_data = json_loads(query_result.data)

    fieldnames, special_columns = _get_column_lists(query_data['columns'])

    writer = csv.DictWriter(s, extrasaction="ignore", fieldnames=fieldnames)
    writer.writer = UnicodeWriter(s)
    writer.writeheader()

    for row in query_data['rows']:
        for col_name, converter in special_columns.iteritems():
            if col_name in row:
                row[col_name] = converter(row[col_name])

        writer.writerow(row)

    return s.getvalue()
Esempio n. 58
0
def serialize_dashboard(obj, with_widgets=False, user=None, with_favorite_state=True):
    layout = json_loads(obj.layout)

    widgets = []

    if with_widgets:
        for w in obj.widgets:
            if w.visualization_id is None:
                widgets.append(serialize_widget(w))
            elif user and has_access(w.visualization.query_rel, user, view_only):
                widgets.append(serialize_widget(w))
            else:
                widget = project(serialize_widget(w),
                                ('id', 'width', 'dashboard_id', 'options', 'created_at', 'updated_at'))
                widget['restricted'] = True
                widgets.append(widget)
    else:
        widgets = None

    d = {
        'id': obj.id,
        'slug': obj.slug,
        'name': obj.name,
        'user_id': obj.user_id,
        # TODO: we should properly load the users
        'user': obj.user.to_dict(),
        'layout': layout,
        'dashboard_filters_enabled': obj.dashboard_filters_enabled,
        'widgets': widgets,
        'is_archived': obj.is_archived,
        'is_draft': obj.is_draft,
        'tags': obj.tags or [],
        # TODO: bulk load favorites
        'updated_at': obj.updated_at,
        'created_at': obj.created_at,
        'version': obj.version
    }

    if with_favorite_state:
        d['is_favorite'] = models.Favorite.is_favorite(current_user.id, obj)

    return d
Esempio n. 59
0
def outdated_queries():
    manager_status = redis_connection.hgetall('redash:status')
    query_ids = json_loads(manager_status.get('query_ids', '[]'))
    if query_ids:
        outdated_queries = (
            models.Query.query.outerjoin(models.QueryResult)
                              .filter(models.Query.id.in_(query_ids))
                              .order_by(models.Query.created_at.desc())
        )
    else:
        outdated_queries = []

    record_event(current_org, current_user._get_current_object(), {
        'action': 'list',
        'object_type': 'outdated_queries',
    })

    response = {
        'queries': QuerySerializer(outdated_queries, with_stats=True, with_last_modified_by=False).serialize(),
        'updated_at': manager_status['last_refresh_at'],
    }
    return json_response(response)
Esempio n. 60
0
def serialize_query_result_to_xlsx(query_result):
    s = cStringIO.StringIO()

    query_data = json_loads(query_result.data)
    book = xlsxwriter.Workbook(s, {'constant_memory': True})
    sheet = book.add_worksheet("result")

    column_names = []
    for (c, col) in enumerate(query_data['columns']):
        sheet.write(0, c, col['name'])
        column_names.append(col['name'])

    for (r, row) in enumerate(query_data['rows']):
        for (c, name) in enumerate(column_names):
            v = row.get(name)
            if isinstance(v, list) or isinstance(v, dict):
                v = str(v).encode('utf-8')
            sheet.write(r + 1, c, v)

    book.close()

    return s.getvalue()