def extension(app): logger.info( "Loading Redash Extension for the custom BigQuery query runner") # Register our own BigQuery query runner class # which automatically overwrites the default presto query runner register(BigQuery) logger.info("Loaded Redash Extension for the custom BigQuery query runner") return
def extension(app): logger.info('Loading Redash Extension for the custom Presto query runner') # Monkeypatch the pyhive.presto.connect function presto.connect = stmo_connect # and register our own STMOPresto query runner class # which automatically overwrites the default presto query runner register(STMOPresto) logger.info('Loaded Redash Extension for the custom Presto query runner') return stmo_connect
TABLE_SCHEMA not in ('INFORMATION_SCHEMA', 'information_schema', 'sys') and TABLE_SCHEMA not like '%.information_schema' and TABLE_SCHEMA not like '%.INFORMATION_SCHEMA' """ allowed_schemas = self.configuration.get('allowed_schemas') if allowed_schemas: query += "and TABLE_SCHEMA in ({})".format(', '.join(map(lambda x: "'{}'".format(re.sub('[^a-zA-Z0-9_.`]', '', x)), allowed_schemas.split(',')))) results, error = self.run_query(query, None) if error is not None: raise Exception("Failed getting schema.") results = json_loads(results) schema = {} for row in results['rows']: table_name = u'{}.{}'.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() register(Drill)
cursor = self._get_cursor() # load tables first, otherwise tables without columns are not showed cursor.tables(schema=database_name) for table in cursor: table_name = "{}.{}".format(table[1], table[2]) if table_name not in schema: schema[table_name] = {"name": table_name, "columns": []} cursor.columns(schema=database_name) for column in cursor: table_name = "{}.{}".format(column[1], column[2]) if table_name not in schema: schema[table_name] = {"name": table_name, "columns": []} schema[table_name]["columns"].append({"name": column[3], "type": column[5]}) return list(schema.values()) def get_table_columns(self, database_name, table_name): cursor = self._get_cursor() cursor.columns(schema=database_name, table=table_name) return [{"name": column[3], "type": column[5]} for column in cursor] register(Databricks)
try: cursor.execute(query) if cursor.description is not None: columns = self.fetch_columns([(i[0], None) for i in cursor.description]) rows = [ dict(zip((c['name'] for c in columns), row)) for row in cursor ] data = {'columns': columns, 'rows': rows} error = None json_data = json.dumps(data, cls=JSONEncoder) else: error = 'Query completed but it returned no data.' json_data = None except KeyboardInterrupt: connection.cancel() error = "Query cancelled by user." json_data = None except Exception as e: raise sys.exc_info()[1], None, sys.exc_info()[2] finally: connection.close() return json_data, error register(Sqlite)
col.table_name as table_name, col.column_name as 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"]: logger.info(row) if row["table_schema"] != self.configuration["db"]: table_name = "{}.{}".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 list(schema.values()) register(DataAPIRDSMySQL)
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() register(Druid)
def get_schema(self, get_stats=False): query = ".show database schema as json" results, error = self.run_query(query, None) if error is not None: raise Exception("Failed getting schema.") results = json_loads(results) schema_as_json = json_loads(results['rows'][0]['DatabaseSchema']) tables_list = schema_as_json['Databases'][ self.configuration['database']]['Tables'].values() schema = {} for table in tables_list: table_name = table['Name'] if table_name not in schema: schema[table_name] = {'name': table_name, 'columns': []} for column in table['OrderedColumns']: schema[table_name]['columns'].append(column['Name']) return schema.values() register(AzureKusto)
def type(cls): return 'csv_parser' @classmethod def annotate_query(cls): return False def __init__(self, configuration): super(CsvParser, self).__init__(configuration) def test_connection(self): pass def run_query(self, query, user): query = query.strip() delimiter = str(self.configuration.get('delimiter')) columns = [] rows = [] for row in csv.DictReader(query.splitlines(), delimiter=delimiter): if len(columns) == 0: for key in row.keys(): columns.append({'name': key, 'friendly_name': key}) rows.append(row) return json.dumps({'columns': columns, 'rows': rows}), None register(CsvParser)
for i, row in enumerate(cursor): for j, col in enumerate(row): guess = _guess_type(col) if columns[j]['type'] is None: columns[j]['type'] = guess elif columns[j]['type'] != guess: columns[j]['type'] = TYPE_STRING rows.append(dict(zip(column_names, row))) data = {'columns': columns, 'rows': rows} error = None json_data = json.dumps(data, cls=JSONEncoder) else: error = 'Query completed but it returned no data.' json_data = None except KeyboardInterrupt: conn.cancel() error = "Query cancelled by user." json_data = None finally: conn.close() return json_data, error register(ReqlQueryRunner)
http_uri = "https://{}{}".format(host, path) transport = THttpClient.THttpClient(http_uri) password = self.configuration.get('http_password', '') auth = base64.b64encode('token:' + password) transport.setCustomHeaders({'Authorization': 'Basic ' + auth}) connection = hive.connect(thrift_transport=transport) return connection def _get_tables(self, schema): schemas_query = "show schemas" tables_query = "show tables in %s" columns_query = "show columns in %s.%s" schemas = self._run_query_internal(schemas_query) for schema_name in filter(lambda a: len(a) > 0, map(lambda a: str(a['databaseName']), schemas)): for table_name in filter(lambda a: len(a) > 0, map(lambda a: str(a['tableName']), self._run_query_internal(tables_query % schema_name))): columns = filter(lambda a: len(a) > 0, map(lambda a: str(a['col_name']), self._run_query_internal(columns_query % (schema_name, table_name)))) if schema_name != 'default': table_name = '{}.{}'.format(schema_name, table_name) schema[table_name] = {'name': table_name, 'columns': columns} return schema.values() register(Databricks)
def run_query(self, query, user): try: data = { 'athenaUrl': 'jdbc:awsathena://athena.{}.amazonaws.com:443/'.format(self.configuration['region'].lower()), 'awsAccessKey': self.configuration['aws_access_key'], 'awsSecretKey': self.configuration['aws_secret_key'], 's3StagingDir': self.configuration['s3_staging_dir'], 'query': query } response = requests.post(PROXY_URL, json=data) response.raise_for_status() json_data = response.content.strip() error = None return json_data, error except requests.RequestException as e: if e.response.status_code == 400: return None, response.content return None, str(e) except KeyboardInterrupt: error = "Query cancelled by user." json_data = None return json_data, error register(Athena)
rows = [] column_names = [c['name'] for c in columns] for i, row in enumerate(cursor): for j, col in enumerate(row): guess = guess_type(col) if columns[j]['type'] is None: columns[j]['type'] = guess elif columns[j]['type'] != guess: columns[j]['type'] = TYPE_STRING rows.append(dict(zip(column_names, row))) data = {'columns': columns, 'rows': rows} error = None json_data = json_dumps(data) else: error = 'Query completed but it returned no data.' json_data = None except KeyboardInterrupt: connection.cancel() error = "Query cancelled by user." json_data = None finally: connection.close() return json_data, error register(Results)
@classmethod def configuration_schema(cls): return { "type": "object", "properties": { "host": {"type": "string"}, "user": {"type": "string"}, "passwd": {"type": "string", "title": "Password"}, "db": {"type": "string", "title": "Database name"}, "port": {"type": "number", "default": 3306}, "use_ssl": {"type": "boolean", "title": "Use SSL"}, }, "order": ["host", "port", "user", "passwd", "db"], "required": ["db", "user", "passwd", "host"], "secret": ["passwd"], } def _get_ssl_parameters(self): if self.configuration.get("use_ssl"): ca_path = os.path.join( os.path.dirname(__file__), "./files/rds-combined-ca-bundle.pem" ) return {"ca": ca_path} return None register(Mysql) register(RDSMySQL)
# finally, assemble both the columns and data data = {'columns': columns, 'rows': processed_data} json_data = json_dumps(data) except Exception as e: error = e return json_data, error def get_schema(self, get_stats=False): """Queries Dgraph for all the predicates, their types, their tokenizers, etc. Dgraph only has one schema, and there's no such things as columns""" query = "schema {}" results = self.run_dgraph_query_raw(query) schema = {} for row in results['schema']: table_name = row['predicate'] if table_name not in schema: schema[table_name] = {'name': table_name, 'columns': []} return schema.values() register(Dgraph)
columns = self.fetch_columns([('Count', TYPE_INTEGER)]) rows = [{'Count': response['totalSize']}] elif len(records) > 0: cols = self._build_columns(sf, records[0]) rows = self._build_rows(cols, records) columns = self.fetch_columns(cols) error = None data = {'columns': columns, 'rows': rows} json_data = json_dumps(data) except SalesforceError as err: error = err.content json_data = None return json_data, error def get_schema(self, get_stats=False): sf = self._get_sf() response = sf.describe() if response is None: raise Exception("Failed describing objects.") schema = {} for sobject in response['sobjects']: table_name = sobject['name'] if sobject['queryable'] is True and table_name not in schema: desc = sf.__getattr__(sobject['name']).describe() fields = desc['fields'] schema[table_name] = {'name': table_name, 'columns': [f['name'] for f in fields]} return schema.values() register(Salesforce)
for d in cursor.description]) rows = [] column_names = [c['name'] for c in columns] for i, row in enumerate(cursor): if i == 0: for j, col in enumerate(row): guess = guess_type(col) if columns[j]['type'] is None: columns[j]['type'] = guess elif columns[j]['type'] != guess: columns[j]['type'] = TYPE_STRING rows.append(dict(zip(column_names, row))) return json_dumps({'columns': columns, 'rows': rows}), None except KeyboardInterrupt: if connection: connection.interrupt() return None, 'Query cancelled by user.' finally: if cursor: cursor.close() if connection: connection.close() register(ResultsWithParams)
if len(metrics) == 0: return None, 'query result is empty.' metric_labels = metrics[0]['metric'].keys() for label_name in metric_labels: columns.append({ 'friendly_name': label_name, 'type': TYPE_STRING, 'name': label_name }) if query_type == 'query_range': rows = get_range_rows(metrics) else: rows = get_instant_rows(metrics) json_data = json_dumps({'rows': rows, 'columns': columns}) except requests.RequestException as e: return None, str(e) except KeyboardInterrupt: error = "Query cancelled by user." json_data = None return json_data, error register(Prometheus)
def run_query(self, query): connection = sqlite3.connect(self._dbpath) cursor = connection.cursor() try: cursor.execute(query) if cursor.description is not None: columns = self.fetch_columns([(i[0], None) for i in cursor.description]) rows = [dict(zip((c['name'] for c in columns), row)) for row in cursor] data = {'columns': columns, 'rows': rows} error = None json_data = json.dumps(data, cls=JSONEncoder) else: error = 'Query completed but it returned no data.' json_data = None except KeyboardInterrupt: connection.cancel() error = "Query cancelled by user." json_data = None except Exception as e: raise sys.exc_info()[1], None, sys.exc_info()[2] finally: connection.close() return json_data, error register(Sqlite)
elif self.configuration.get('username') or self.configuration.get( 'password'): request_options['auth'] = (self.configuration.get('username'), self.configuration.get('password')) if method not in ('get', 'post'): raise QueryParseError("Only GET or POST methods are allowed.") if fields and not isinstance(fields, list): raise QueryParseError("'fields' needs to be a list.") response, error = self.get_response(query['url'], http_method=method, **request_options) if error is not None: return None, error data = json_dumps(parse_json(response.json(), path, fields)) if data: return data, None else: return None, "Got empty response from '{}'.".format( query['url']) except KeyboardInterrupt: return None, "Query cancelled by user." register(JSON)
def get_schema(self, get_stats=False): schemas = {} try: headers = self._get_header() content = requests.get("%s/api/latest/hive?describe=true&per_page=10000" % self.configuration['endpoint'], headers=headers) data = content.json() for schema in data['schemas']: tables = data['schemas'][schema] for table in tables: table_name = table.keys()[0] columns = [f['name'] for f in table[table_name]['columns']] if schema != 'default': table_name = '{}.{}'.format(schema, table_name) schemas[table_name] = {'name': table_name, 'columns': columns} except Exception as e: logging.error("Failed to get schema information from Qubole. Error {}".format(str(e))) return schemas.values() def _get_header(self): return {"Content-type": "application/json", "Accept": "application/json", "X-AUTH-TOKEN": self.configuration['token']} register(Qubole)
error = None json_data = json_dumps(data) finally: cursor.close() connection.close() return json_data, error def _get_tables(self, schema): connection = self.connect_database() try: for table_name in connection.get_tables(): schema[table_name] = {'name': table_name, 'columns': []} for row_column in connection.get_table_details(table_name): schema[table_name]['columns'].append(row_column[0]) finally: connection.close return list(schema.values()) def test_connection(self): connection = self.connect_database() try: tables = connection.get_tables() num_tables = tables.count(tables) finally: connection.close register(Mapd)
return json_data, error 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() register(Snowflake)
escape_table = { escape(japanese): japanese for japanese in find_include_japanese_column(query) } logging.info('query_reply : bq_jp : %s', escape_table) for escaped, raw in six.iteritems(escape_table): query = query.replace(raw, escaped) logging.info('query_encode : bq_jp : %s', query) json_data, error = super(BigQueryJP, self).run_query(query, user) if error is not None: return None, error data = json.loads(json_data) data["rows"] = [{ escape_table.get(col, col): val for col, val in six.iteritems(row) } for row in data["rows"]] for column in data["columns"]: name = column["name"] if name in escape_table: original_name = escape_table.get(name, name) column["name"] = original_name column["friendly_name"] = original_name return json.dumps(data), None register(BigQueryJP)
for label_name in metric_labels: columns.append({ 'friendly_name': label_name, 'type': TYPE_STRING, 'name': label_name }) if query_type == 'query_range': rows = get_range_rows(metrics) else: rows = get_instant_rows(metrics) json_data = json_dumps( { 'rows': rows, 'columns': columns } ) except requests.RequestException as e: return None, str(e) except KeyboardInterrupt: error = "Query cancelled by user." json_data = None return json_data, error register(Prometheus)
'secret': ['api_key'], } def run_query(self, query, user): base_url = self.configuration.get('base_url', None) api_key = self.configuration.get('api_key', None) data_source_id = self.configuration.get('data_source_id', None) query = query.strip() parsed_query = urlparse.urlparse(query) query_id = parsed_query.path params = { k: v[0] for k, v in urlparse.parse_qs(parsed_query.query).items() } try: client = RedashDynamicQuery(endpoint=base_url, apikey=api_key, data_source_id=data_source_id) results = client.query(query_id, params) return json_dumps(results['query_result']['data']), None except requests.RequestException as e: return None, str(e) except KeyboardInterrupt: return None, 'Query cancelled by user.' register(RedashQuery)
response.raise_for_status() raw_data = StringIO(response.text) reader = csv.DictReader(raw_data) columns = [] rows = [] for row in reader: if not columns: columns = [{ "name": k, "friendly_name": k, "type": TYPE_STRING } for k in row.keys()] rows.append({k: v for k, v in row.items()}) json_data = json_dumps({"columns": columns, "rows": rows}) return json_data, error except requests.RequestException as e: return None, str(e) except KeyboardInterrupt: error = "Query cancelled by user." json_data = None return json_data, error register(CsvUrl)
result = session.execute(query) column_names = result.column_names columns = self.fetch_columns(map(lambda c: (c, 'string'), column_names)) rows = [dict(zip(column_names, row)) for row in result] data = {'columns': columns, 'rows': rows} json_data = json.dumps(data, cls=CassandraJSONEncoder) error = None except KeyboardInterrupt: error = "Query cancelled by user." json_data = None return json_data, error class ScyllaDB(Cassandra): def __init__(self, configuration): super(ScyllaDB, self).__init__(configuration) @classmethod def type(cls): return "scylla" register(Cassandra) register(ScyllaDB)
for schema in data['schemas']: tables = data['schemas'][schema] for table in tables: table_name = table.keys()[0] columns = [f['name'] for f in table[table_name]['columns']] if schema != 'default': table_name = '{}.{}'.format(schema, table_name) schemas[table_name] = { 'name': table_name, 'columns': columns } except Exception as e: logging.error( "Failed to get schema information from Qubole. Error {}". format(str(e))) return schemas.values() def _get_header(self): return { "Content-type": "application/json", "Accept": "application/json", "X-AUTH-TOKEN": self.configuration['token'] } register(Qubole)
get_list_containers_response = requests.get( get_list_containers_url, headers=headers, auth=auth, params={'limit': maximum_total_containers}) if get_list_containers_response.status_code == 200: list_containers = get_list_containers_response.json()['names'] else: raise Exception("Failed getting list of containers.") for container in list_containers: # Get container info of each container url = get_list_containers_url + '/' + container + '/info' response = requests.get(url, headers=headers, auth=auth) if response.status_code == 200: container_info = {} container_name = response.json()['container_name'] container_info['name'] = container_name list_columns = [] for column in response.json()['columns']: list_columns.append(column['name']) container_info['columns'] = list_columns schema[container_name] = container_info else: raise Exception("Failed getting schema.") return schema.values() register(GridDB)
val = r[i] if val is None: continue if isinstance(val, (dict, list)): val = json.dumps(val, cls=JSONEncoder) col = get_unique_name(cname, TYPES_MAP.get(type(val), TYPE_STRING)) new_row[col] = val output.append(new_row) output_columns = [{ "name": full_name, "type": ctype, "friendly_name": full_name } for cname, types in columns.items() for ctype, full_name in types.items()] return {'columns': output_columns, 'rows': output} def find_error_cause(self, response): while response.get('cause') is not None: cause = response['cause'] if isinstance(cause, list): response = cause[0] else: response = cause return response.get('template') register(ActiveData)
}, 'location': { "type": "string", "title": "Processing Location", "default": "US", }, 'loadSchema': { "type": "boolean", "title": "Load Schema" } } } def _get_project_id(self): return requests.get( 'http://metadata/computeMetadata/v1/project/project-id', headers={ 'Metadata-Flavor': 'Google' }).content def _get_bigquery_service(self): credentials = gce.AppAssertionCredentials( scope='https://www.googleapis.com/auth/bigquery') http = httplib2.Http() http = credentials.authorize(http) return build("bigquery", "v2", http=http) register(BigQueryGCE)
'awsAccessKey': self.configuration['aws_access_key'], 'awsSecretKey': self.configuration['aws_secret_key'], 's3StagingDir': self.configuration['s3_staging_dir'], 'query': query } response = requests.post(PROXY_URL, json=data) response.raise_for_status() json_data = response.content.strip() error = None return json_data, error except requests.RequestException as e: if e.response.status_code == 400: return None, response.content return None, str(e) except KeyboardInterrupt: error = "Query cancelled by user." json_data = None return json_data, error register(Athena)
def run_query(self, query, user): base_url = self.configuration.get("url", None) try: query = query.strip() if base_url is not None and base_url != "": if query.find("://") > -1: return None, "Accepting only relative URLs to '%s'" % base_url if base_url is None: base_url = "" url = base_url + query response, error = self.get_response(url) if error is not None: return None, error json_data = response.content.strip() if json_data: return json_data, None else: return None, "Got empty response from '{}'.".format(url) except KeyboardInterrupt: return None, "Query cancelled by user." register(Url)
rows = self._build_rows(cols, records) columns = self.fetch_columns(cols) error = None data = {"columns": columns, "rows": rows} json_data = json_dumps(data) except SalesforceError as err: error = err.content json_data = None return json_data, error def get_schema(self, get_stats=False): sf = self._get_sf() response = sf.describe() if response is None: raise Exception("Failed describing objects.") schema = {} for sobject in response["sobjects"]: table_name = sobject["name"] if sobject["queryable"] is True and table_name not in schema: desc = sf.__getattr__(sobject["name"]).describe() fields = desc["fields"] schema[table_name] = { "name": table_name, "columns": [f["name"] for f in fields], } return list(schema.values()) register(Salesforce)
columns = self.fetch_columns(set(arr)) print(columns) data = {"columns": columns, "rows": data} r.json_data = json_dumps(data) r.error = None else: r.json_data = None r.error = "No data was returned." except Exception as e: r.json_data = None r.error = e finally: ev.set() if connection: connection.close() def _cancel(self, driver): error = None try: driver.close() except Exception as e: error = e return error register(Neo4j)
data = {'columns': columns, 'rows': rows} error = None json_data = json_dumps(data) finally: cursor.close() connection.close() return json_data, error def _get_tables(self, schema): connection = self.connect_database() try: for table_name in connection.get_tables(): schema[table_name] = {'name': table_name, 'columns': []} for row_column in connection.get_table_details(table_name): schema[table_name]['columns'].append(row_column[0]) finally: connection.close return schema.values() def test_connection(self): connection = self.connect_database() try: tables = connection.get_tables() num_tables = tables.count(tables) finally: connection.close register(Mapd)
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() register(Snowflake)
session.default_timeout = self.configuration.get('timeout', 10) logger.debug("Cassandra running query: %s", query) result = session.execute(query) column_names = result.column_names columns = self.fetch_columns(map(lambda c: (c, 'string'), column_names)) rows = [dict(zip(column_names, row)) for row in result] data = {'columns': columns, 'rows': rows} json_data = json_dumps(data, cls=CassandraJSONEncoder) error = None except KeyboardInterrupt: error = "Query cancelled by user." json_data = None return json_data, error class ScyllaDB(Cassandra): @classmethod def type(cls): return "scylla" register(Cassandra) register(ScyllaDB)
try: error = None query = query.strip() if base_url is not None and base_url != "": if query.find("://") > -1: return None, "Accepting only relative URLs to '%s'" % base_url if base_url is None: base_url = "" url = base_url + query response = requests.get(url) response.raise_for_status() json_data = response.content.strip() if not json_data: error = "Got empty response from '{}'.".format(url) return json_data, error except requests.RequestException as e: return None, str(e) except KeyboardInterrupt: error = "Query cancelled by user." json_data = None return json_data, error register(Url)