def initiate_usage_scheduler(self): usage_scheduler_run_time = datetime.datetime.now() if self.run_count != 0: print 'usage_scheduler started at: ' + str(usage_scheduler_run_time) logger.info('usage_scheduler started at: ' + str(usage_scheduler_run_time)) datasets = bq.get_datasets() for dataset in datasets: try: storage_query = "SELECT SUM(size_bytes) as storage_bq FROM [{0}.__TABLES__]".format( dataset['datasetReference']['datasetId']) storage_bq = bq.execute_query(storage_query, user_id=0, tenant='DigInEngine')[0]['storage_bq'] user_id = db.get_data( "SELECT user_id FROM digin_user_settings WHERE REPLACE(REPLACE(email, '.', '_'), '@', '_') = '{0}' limit 1".format( dataset['datasetReference']['datasetId']))['rows'] if user_id == (): print 'No user_Settings found for user: '******'datasetReference']['datasetId'] logger.info('No user_Settings found for user: '******'datasetReference']['datasetId']) else: usages = {'storage_bq': 0 if storage_bq is None else storage_bq} obj = dre.RatingEngine(user_id[0][0], 'undefined', **usages) obj.set_usage() print 'Storage calculated for user ' + str(user_id[0][0]) logger.info('Storage calculated for user ' + str(user_id[0][0])) except Exception, err: print err
def ret_data(dbtype, rec_data,user_id=None, tenant=None): df = pd.DataFrame() for i in range(0,len(rec_data)): tables = rec_data[i].keys() fields = rec_data[i].values() fields = fields[0] fields_str = ', '.join(fields) tables_str = ', '.join(tables) if dbtype.lower() == 'mssql': try: query = 'SELECT {0} FROM {1}'.format(fields_str, tables_str) result = mssql.execute_query(query) except Exception, err: result = cmg.format_response(False, err, 'Error occurred while getting data from MSSQL!', sys.exc_info()) return result elif dbtype.lower() == 'bigquery': try: query = 'SELECT {0} FROM {1}'.format(fields_str, tables_str) result = BQ.execute_query(query, user_id=user_id, tenant=tenant) except Exception, err: result = cmg.format_response(False, err, 'Error occurred while getting data from BigQuery Handler!', sys.exc_info()) return result
def create_Dataset(params): datasetID = params.dataSetName db = params.db if db.lower() == 'bigquery': try: result = bqhandler.create_dataset(datasetID) return comm.format_response(True,result,"",exception=None) except Exception, err: print err return False
def get_tables(params): datasetID = params.dataSetName db = params.db if db.lower() == 'bigquery': try: result = bqhandler.get_tables(datasetID) except Exception, err: return comm.format_response(False,err,"Error Occurred when retrieving tables!",exception=sys.exc_info()) return comm.format_response(True,result,"Tables retrieved!",exception=None)
def set_initial_user_env(params,email,user_id,domain): default_sys_settings = conf.get_conf('DefaultConfigurations.ini','System Settings') dataset_name = email.replace(".", "_").replace("@","_") if ast.literal_eval(default_sys_settings['signup_dataset_creation']): db = params['db'] if db.lower() == 'bigquery': logger.info("Creation of dataset started!") print "Creation of dataset started!" try: result_ds= bq.create_dataset(dataset_name) print result_ds logger.info("Creation of dataset status " + str(result_ds)) print "Creation of dataset " + str(result_ds) except Exception, err: print err print "Creation of dataset failed!" return cmg.format_response(False,err,"Error Occurred while creating dataset in bigquery!",exception=sys.exc_info()) else: raise
def get_fields(params): tablename = params.tableName db = params.db if db.lower() == 'bigquery': datasetname = params.dataSetName fields = bqhandler.get_fields(datasetname, tablename) return comm.format_response(True,fields,"",exception=None) elif db.lower() == 'mssql': fields = mssqlhandler.get_fields(tablename) return comm.format_response(True,fields,"",exception=None) elif db.lower() == 'postgresql': schema_name = params.schema colnames = pgsqlhandler.get_fields(tablename,schema_name) return comm.format_response(True,colnames,"",exception=None) elif db.lower() == 'mysql': colnames = mysqlhandler.get_fields(params.tableName) return comm.format_response(True,colnames,"",exception=None) else: return comm.format_response(False,db,"DB not implemented!",exception=None)
def _table_creation_bq(_schema, db, data_set_name, table_name): if db.lower() == 'bigquery': bq_schema = [] # schema_dict = {} for i in _schema: schema_dict = {} t = i['type'] if t == 'object': schema_dict['name'] = i['name'] schema_dict['type'] = 'string' schema_dict['mode'] = 'nullable' elif t == 'int64': schema_dict['name'] = i['name'] schema_dict['type'] = 'integer' schema_dict['mode'] = 'nullable' elif t == 'float64': schema_dict['name'] = i['name'] schema_dict['type'] = 'float' schema_dict['mode'] = 'nullable' elif t == 'datetime64[ns]': schema_dict['name'] = i['name'] schema_dict['type'] = 'TIMESTAMP' schema_dict['mode'] = 'nullable' bq_schema.append(schema_dict) print 'Table creation started!' print table_name try: print data_set_name result = bq.create_Table(data_set_name,table_name,bq_schema) if result: print "Table creation succcessful!" else: print "Error occurred while creating table! If table already exists data might insert to the existing table!" except Exception, err: print "Error occurred while creating table!" print err raise
for i in range(columns.size): if columns[i] == 'object': C.append(i) try: file_csv = pd.read_csv(file_path+'/'+filename, date_parser=C, error_bad_lines=False, dayfirst=True) except Exception,err: print err result = comm.format_response(False,err,"failed read csv file",exception=sys.exc_info()) return result print "Field type recognition successful" if db.lower() == 'bigquery': try: table_existance = bq.check_table(dataset_name,table_name) if table_existance : if parms.folder_type.lower() == 'singlefile': bq.delete_table(dataset_name,table_name) print "Existing Table deleted" try: print dataset_name result = bq.create_Table(dataset_name, table_name, schema) if result: print "Table creation succcessful!" else: print "Error occurred while creating table! If table already exists data might insert to the existing table!" except Exception, err: print "Error occurred while creating table!" print err
window_functions_set.append(window_functions) total_str = '{0}({1}) OVER () as total'.format(agg, measure) fields_str = ', '.join(fields) window_functions_set_str = ', '.join(window_functions_set) fields_from_inner_query_str = ', '.join(fields_from_inner_query) query = 'SELECT {0}, total, {1} FROM (SELECT {0} , {2}, {3} FROM {4} {5})z GROUP BY {0}, total, {1} ORDER BY {0}' \ .format(fields_str, fields_from_inner_query_str, total_str, window_functions_set_str, table_name, where_clause) print query result = '' if db.lower() == 'bigquery': try: result = BQ.execute_query(query,user_id=user_id, tenant=tenant) logger.info('Data received!') logger.debug('Result %s' % result) except Exception, err: logger.error('Error occurred while getting data from BigQuery Handler! %s' % err) return cmg.format_response(False,None,'Error occurred while getting data from BigQuery Handler!',sys.exc_info()) elif db.lower() == 'mssql': try: result = mssql.execute_query(query) logger.info('Data received!') logger.debug('Result %s' % result) except Exception, err: logger.error('Error occurred while getting data from sql Handler! %s' % err) return cmg.format_response(False,None,'Error occurred while getting data from BigQuery Handler!',sys.exc_info())
cache_existance = CC.get_cached_data("SELECT expirydatetime >= '{0}' FROM cache_execute_query WHERE id = '{1}'".format(time, cache_key))['rows'] except Exception, err: logger.error("Error connecting to cache..") logger.error(err) cache_existance = () pass if len(cache_existance) != 0: try: data = CC.get_cached_data("SELECT data, query FROM cache_execute_query WHERE id = '{0}'".format(cache_key))['rows'] except Exception,err: return comm.format_response(False,None,"Error occurred while retrieving data from cache!",exception=sys.exc_info()) return comm.format_response(True,json.loads(data[0][0]),data[0][1],exception=None) if db.lower() == 'bigquery': try: results = bqhandler.execute_query(query, offset=offset_, limit=limit_,user_id=user_id,tenant=tenant) except Exception, err: err_content = json.loads(err.content) return comm.format_response(False, err_content['error']['errors'][0]['reason']+ ' ' + err_content['error']['errors'][0]['message'], query) try: logger.info('Inserting to cache..') # p = Process(target=MEM_insert,args=(cache_key,json.dumps(results),query,cache_timeout)) # p.start() t = threading.Thread(target=MEM_insert, args=(cache_key,json.dumps(results),query,cache_timeout)) t.start() except Exception, err: logger.error("Cache insertion failed. %s" % err) pass return comm.format_response(True,results,query,exception=None) elif db.lower() == 'mssql':
result = cmg.format_response(False,None,'Error occurred while getting data from sql Handler!',sys.exc_info()) #result_dict = json.loads(result) finally: return result elif db.lower() == 'bigquery': try: agg_ = aggregations["Date, '%m'"] except Exception: agg_ = '' if agg_ == 'STRFTIME_UTC_USEC': query = "SELECT STRFTIME_UTC_USEC(Date, '%Y') as year, STRFTIME_UTC_USEC(Date, '%m') as month," \ " SUM(Sales) as sales, SUM(OrderQuantity) as tot_units FROM [Demo.forcast_superstoresales]" \ " GROUP BY year, month ORDER BY year, month" result_ = BQ.execute_query(query,limit=limit,user_id=user_id,tenant=tenant) result = cmg.format_response(True,result_,query) return result else: logger.info("BigQuery - Processing started!") query_body = tablenames[1] if join_types and join_keys != {}: for i in range(0, len(join_types)): sub_join_body = join_types[i+1] + ' ' + tablenames[i+2] + ' ' + join_keys[i+1] query_body += ' ' query_body += sub_join_body if conditions: conditions = 'WHERE %s' %(conditions) if group_bys_dict != {}:
field_name_date = params.field_name_d field_name_forecast = params.field_name_f interval = str(params.interval) db_type = params.dbtype except Exception, err: return cmg.format_response(False, err, 'Input parameters caused the service to raise an error', sys.exc_info()) if interval == 'Daily': if db_type.lower() == 'bigquery': query = "SELECT TIMESTAMP_TO_SEC({0}) as date, SUM({1}) as value from {2} group by date order by date".\ format(field_name_date,field_name_forecast,table_name) try: result = BQ.execute_query(query) except: result = cmg.format_response(False,None,'Error occurred while getting data from BQ Handler!',sys.exc_info()) return result elif db_type.lower() == 'postgresql': query = "SELECT date_part('epoch',{0}::date) as date, SUM({1})::FLOAT as value from {2} group by date order by date".\ format(field_name_date,field_name_forecast,table_name) try: result = PG.execute_query(query) except: result = cmg.format_response(False,None,'Error occurred while getting data from PG Handler!',sys.exc_info()) return result elif db_type.lower() == 'mssql': query = "SELECT DATEDIFF(s, '1970-01-01 00:00:00', cast({0} as Date)) date, SUM({1}) as value from {2} " \ "group by DATEDIFF(s, '1970-01-01 00:00:00', cast({0} as Date)) " \ "order by DATEDIFF(s, '1970-01-01 00:00:00', cast({0} as Date)) ".format(field_name_date,field_name_forecast,table_name)