def run_query(self, query, user):
connection = tdclient.connect(
endpoint=self.configuration.get('endpoint', 'https://api.treasuredata.com'),
apikey=self.configuration.get('apikey'),
type=self.configuration.get('type', 'hive').lower(),
db=self.configuration.get('db'))
cursor = connection.cursor()
try:
cursor.execute(query)
columns_data = [(row[0], cursor.show_job()['hive_result_schema'][i][1]) for i,row in enumerate(cursor.description)]
columns = [{'name': col[0],
'friendly_name': col[0],
'type': TD_TYPES_MAPPING.get(col[1], None)} for col in columns_data]
if cursor.rowcount == 0:
rows = []
else:
rows = [dict(zip(([c[0] for c in columns_data]), r)) for i, r in enumerate(cursor.fetchall())]
data = {'columns': columns, 'rows': rows}
json_data = json.dumps(data, cls=JSONEncoder)
error = None
except errors.InternalError as e:
json_data = None
error = "%s: %s" % (e.message, cursor.show_job().get('debug', {}).get('stderr', 'No stderr message in the response'))
return json_data, error
def run_query(self, query, user):
connection = tdclient.connect(
endpoint=self.configuration.get('endpoint',
'https://api.treasuredata.com'),
apikey=self.configuration.get('apikey'),
type=self.configuration.get('type', 'hive').lower(),
db=self.configuration.get('db'))
cursor = connection.cursor()
try:
cursor.execute(query)
columns_tuples = [(i[0], TD_TYPES_MAPPING.get(i[1], None))
for i in cursor.show_job()['hive_result_schema']]
columns = self.fetch_columns(columns_tuples)
if cursor.rowcount == 0:
rows = []
else:
rows = [
dict(zip(([c['name'] for c in columns]), r))
for i, r in enumerate(cursor.fetchall())
]
data = {'columns': columns, 'rows': rows}
json_data = json_dumps(data)
error = None
except errors.InternalError as e:
json_data = None
error = "%s: %s" % (e.message, cursor.show_job().get(
'debug', {}).get('stderr',
'No stderr message in the response'))
return json_data, error
def run_query(self, query, user):
connection = tdclient.connect(
endpoint=self.configuration.get("endpoint",
"https://api.treasuredata.com"),
apikey=self.configuration.get("apikey"),
type=self.configuration.get("type", "hive").lower(),
db=self.configuration.get("db"),
)
cursor = connection.cursor()
try:
cursor.execute(query)
columns_tuples = [(i[0], TD_TYPES_MAPPING.get(i[1], None))
for i in cursor.show_job()["hive_result_schema"]]
columns = self.fetch_columns(columns_tuples)
if cursor.rowcount == 0:
rows = []
else:
rows = [
dict(zip(([column["name"] for column in columns]), r))
for r in cursor.fetchall()
]
data = {"columns": columns, "rows": rows}
json_data = json_dumps(data)
error = None
except errors.InternalError as e:
json_data = None
error = "%s: %s" % (
str(e),
cursor.show_job().get("debug", {}).get(
"stderr", "No stderr message in the response"),
)
return json_data, error
def run_query(self, query, user):
connection = tdclient.connect(
endpoint=self.configuration.get('endpoint',
'https://api.treasuredata.com'),
apikey=self.configuration.get('apikey'),
type=self.configuration.get('type', 'hive').lower(),
db=self.configuration.get('db'))
cursor = connection.cursor()
cursor.execute(query)
columns_data = [(row[0], cursor.show_job()['hive_result_schema'][i][1])
for i, row in enumerate(cursor.description)]
columns = [{
'name': col[0],
'friendly_name': col[0],
'type': TD_TYPES_MAPPING.get(col[1], None)
} for col in columns_data]
if cursor.rowcount == 0:
rows = []
else:
rows = [
dict(zip(([c[0] for c in columns_data]), r))
for i, r in enumerate(cursor.fetchall())
]
data = {'columns': columns, 'rows': rows}
json_data = json.dumps(data, cls=JSONEncoder)
error = None
return json_data, error
def _connect(self):
return tdclient.connect(
apikey=self.apikey,
endpoint=self.endpoint,
db=self.database,
user_agent=self.user_agent,
type="hive",
)
def _connect(self):
return (
prestodb.dbapi.connect(
host=self.presto_api_host,
port=443,
http_scheme="https",
user=self.apikey,
catalog="td-presto",
schema=self.database,
http_headers={"user-agent": self.user_agent},
),
tdclient.connect(
apikey=self.apikey,
endpoint=self.endpoint,
db=self.database,
user_agent=self.user_agent,
type="presto",
),
)
def run_query(self, query, user):
connection = tdclient.connect(
endpoint=self.configuration.get('endpoint', 'https://api.treasuredata.com'),
apikey=self.configuration.get('apikey'),
type=self.configuration.get('type', 'hive').lower(),
db=self.configuration.get('db'))
cursor = connection.cursor()
cursor.execute(query)
columns_data = [(row[0], cursor.show_job()['hive_result_schema'][i][1]) for i,row in enumerate(cursor.description)]
columns = [{'name': col[0],
'friendly_name': col[0],
'type': TD_TYPES_MAPPING.get(col[1], None)} for col in columns_data]
rows = [dict(zip(([c[0] for c in columns_data]), r)) for i, r in enumerate(cursor.fetchall())]
data = {'columns': columns, 'rows': rows}
json_data = json.dumps(data, cls=JSONEncoder)
error = None
return json_data, error
def run_query(self, query, user):
connection = tdclient.connect(
endpoint=self.configuration.get('endpoint', 'https://api.treasuredata.com'),
apikey=self.configuration.get('apikey'),
type=self.configuration.get('type', 'hive').lower(),
db=self.configuration.get('db'))
cursor = connection.cursor()
try:
cursor.execute(query)
columns_tuples = [(i[0], TD_TYPES_MAPPING.get(i[1], None)) for i in cursor.show_job()['hive_result_schema']]
columns = self.fetch_columns(columns_tuples)
if cursor.rowcount == 0:
rows = []
else:
rows = [dict(zip(([c['name'] for c in columns]), r)) for i, r in enumerate(cursor.fetchall())]
data = {'columns': columns, 'rows': rows}
json_data = json_dumps(data)
error = None
except errors.InternalError as e:
json_data = None
error = "%s: %s" % (e.message, cursor.show_job().get('debug', {}).get('stderr', 'No stderr message in the response'))
return json_data, error
else:
raise ValueError('LIMIT must be greater than 0')
q_query = 'SELECT ' + q_col + ' FROM ' + q_table + q_where + q_limit + ';'
# Display gathered arguments
print('-' * 28)
print('{0:>14s} {1}'.format('DB Name:', q_db))
print('{0:>14s} {1}'.format('Table Name:', q_table))
print('{0:>14s} {1}'.format('Output Format:', q_format))
print('{0:>14s} {1}'.format('Query Engine:', q_engine))
print('{0:>14s} {1}'.format('Running Query:', q_query))
print('-' * 28)
try:
with tdclient.connect(db=q_db, type=q_engine,
wait_callback=on_waiting) as td:
print('Getting table columns. Please wait ...')
col_data = pd.read_sql('DESCRIBE ' + q_table, td)
if q_engine == 'hive':
col_name = [i.strip() for i in col_data['col_name'].values]
else:
# Un-tested: Presto query engine not available in test account
col_name = [i.strip() for i in col_data['Column'].values]
# Check column names
if q_col != '*':
invalid_col = []
for i in q_col.split(','):
if i not in col_name:
invalid_col.append(i)
