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)