def run_query(self, query, user):
connection = atsd_client.connect_url(
self.url,
self.configuration.get("username"),
self.configuration.get("password"),
verify=self.configuration.get("trust_certificate", False),
timeout=self.configuration.get("timeout", 600),
)
sql = SQLService(connection)
query_id = str(uuid.uuid4())
try:
logger.debug("SQL running query: %s", query)
data = sql.query_with_params(
query,
{"outputFormat": "csv", "metadataFormat": "EMBED", "queryId": query_id},
)
columns, rows = generate_rows_and_columns(data)
data = {"columns": columns, "rows": rows}
json_data = json_dumps(data)
error = None
except SQLException as e:
json_data = None
error = e.content
except (KeyboardInterrupt, InterruptException):
sql.cancel_query(query_id)
error = "Query cancelled by user."
json_data = None
return json_data, error
def run_query(self, query, user):
connection = atsd_client.connect_url(
self.url,
self.configuration.get('username'),
self.configuration.get('password'),
verify=self.configuration.get('trust_certificate', False),
timeout=self.configuration.get('timeout', 600))
sql = SQLService(connection)
query_id = str(uuid.uuid4())
try:
logger.debug("SQL running query: %s", query)
data = sql.query_with_params(
query, {
'outputFormat': 'csv',
'metadataFormat': 'EMBED',
'queryId': query_id
})
columns, rows = generate_rows_and_columns(data)
data = {'columns': columns, 'rows': rows}
json_data = json_dumps(data)
error = None
except SQLException as e:
json_data = None
error = e.content
except (KeyboardInterrupt, InterruptException):
sql.cancel_query(query_id)
error = "Query cancelled by user."
json_data = None
return json_data, error
from atsd_client import connect_url
from atsd_client.services import SQLService
conn = connect_url('https://atsd_hostname:8443', 'user', 'passwd')
# Single-line SQL query
# query = 'SELECT datetime, time, entity, value FROM jvm_memory_free LIMIT 3';
# Multi-line SQL query, use triple quotes (single or double)
query = """
SELECT datetime, time, entity, value
FROM "jvm_memory_free"
ORDER BY datetime DESC
LIMIT 3
"""
svc = SQLService(conn)
df = svc.query(query)
print(df)
from atsd_client import connect, connect_url
from atsd_client.services import SQLService
# Connect to ATSD server
#connection = connect('/path/to/connection.properties')
connection = connect_url('https://atsd_hostname:8443', 'username', 'password')
# Initialize SQL service
svc = SQLService(connection)
# Single-line SQL query
# query = 'SELECT datetime, time, entity, value FROM jvm_memory_free LIMIT 3';
# Multi-line SQL query. Use triple quotes (single or double)
query = """
SELECT datetime, time, entity, value
FROM "jvm_memory_free"
ORDER BY datetime DESC
LIMIT 3
"""
# Execute query
df = svc.query(query)
print(df)
from io import StringIO
import pandas
from atsd_client import connect
from atsd_client.services import SQLService, CommandsService
# Connect to source ATSD
# source_db_connection = connect_url('https://atsd_hostname:8443', 'username', 'password')
source_db_connection = connect('/path/to/source.connection.properties')
source_sql_service = SQLService(source_db_connection)
# Connect to target ATSD
# target_db_connection = connect_url('https://atsd_hostname:8443', 'username', 'password')
target_db_connection = connect('/path/to/target.connection.properties')
target_command_service = CommandsService(target_db_connection)
metric_name = 'metric_name'
sql_query = 'SELECT entity,metric, value, text, datetime, tags.* FROM "' + metric_name + '"'
# print sql_query
tags_names_to_remove = ['time_zone']
tags_values_to_remove = ['false']
default_tags_to_remove = {'_index': '1', 'status': '0'}
batch_size = 1000
transformed_commands_batch = []
# read df from response with string dtype