def execute_sql_from_jinja_string(conn, sql_string, context=None, dict_cursor=False): # conn: a (psycopg2) connection object # sql_string: query template (Jinja-supported string) # context: the context (dict-like) that will be use with the template # # an extra Jinja filter (surround_by_quote) is available and can be useful to double-quote field names # # returns the cursor object # # examples: # # execute_sql_from_jinja_string(conn, "SELECT version();") # execute_sql_from_jinja_string(conn, "SELECT * FROM biodiv.address LIMIT {{limit}}", {'limit': 5}) e = Environment() e.filters["surround_by_quote"] = surround_by_quote j = JinjaSql(env=e) if context is None: context = {} query, bind_params = j.prepare_query(sql_string, context) if dict_cursor: cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) else: cur = conn.cursor() cur.execute(query, bind_params) return cur
def get_query_results(payment_type: List[int], passenger_count: List[int]): """ Function that accepts 2 inputs: - payment_type: list - passenger_count: list returns a pandas dataframe """ with psycopg2.connect('dbname=analysis user=username password='******'pyformat') data = {} data['payment_type'] = payment_type data['passenger_count'] = passenger_count template = """ SELECT * FROM yellow_tripdata_2016_01 WHERE payment_type in {{ payment_type | inclause }} and passenger_count in {{ passenger_count | inclause }} limit 200 """ query, bind_params = j.prepare_query(template, data) # Execute prepared statement to avoid SQL injection attacks df = pd.read_sql(query, con=conn, params=bind_params) return df
def perform_update_or_delete_operation(sql, binding_params): """ TODO: Facilitate UPDATE &/ DELETE Operation on SQLITE. :param sql: Template: {% raw %} UPDATE tableName SET column_1 = {{column_1_value}}, column_2 = {{column_2_value}} WHERE column_3 = {{ column_3_value }} {% if projectId %} AND projectId = {{ projectId }} {% endif %} {% endraw %} :param binding_params: Template: data = { "column_1_value": 123, "column_2_value": 'dfb', "projectId": u"proton" } :return: """ try: check_if_update = re.compile('update', re.I) check_if_delete = re.compile('delete', re.I) if sql.match(check_if_update) or sql.match(check_if_delete): with self.pg_cursor_generator(self.__cursor_engine) as cursor: __j_sql = JinjaSql(param_style='pyformat') query, bind_params = __j_sql.prepare_query(self.generate_sql_template(sql), binding_params) cursor.execute(query, bind_params) return { 'status': True, 'affected_rowcount': cursor.rowcount } else: return { 'status': False, 'message': 'Illegal use of this method. Please utilize this method to only perform UPDATE ' 'or Delete operation on PROTON supported databases.' } except Exception as e: self.model_{{ modelName }}_logger.exception('[{{modelName}} - Exception during UPDATE operation. ' 'Details: {}]'.format(str(e))) print(Fore.LIGHTRED_EX + '[{{modelName}} - Exception during UPDATE operation. Details: ' '{}]'.format(str(e)) + Style.RESET_ALL) return { 'status': False, 'affected_rowcount': 0 }
def apply_sql_template(template, parameters): ''' Apply a JinjaSql template (string) substituting parameters (dict) and return the final SQL. ''' j = JinjaSql(param_style='pyformat') query, bind_params = j.prepare_query(template, parameters) return get_sql_from_template(query, bind_params)
def test_precompiled_template(self): source = "select * from dummy where project_id = {{ request.project_id }}" j = JinjaSql() query, bind_params = j.prepare_query(j.env.from_string(source), _DATA) expected_query = "select * from dummy where project_id = %s" self.assertEquals(query.strip(), expected_query.strip())
def test_django_request_as_context(self): request = RequestFactory().get('/customer/details/?customer=1232&enterprise=9875') j = JinjaSql() query, bind_params = j.prepare_query("""select {{request.path}} from dual where customer={{request.GET.customer}}""" , {"request": request} ) self.assertEquals(bind_params, [u"/customer/details/", u"1232"])
class HCSReportDBAccessor(ReportDBAccessorBase): """Class to interact with customer reporting tables.""" def __init__(self, schema): """Establish the database connection. :param schema (str): The customer schema to associate with """ super().__init__(schema) self._ebs_acct_num = schema.strip("acct") self.date_accessor = DateAccessor() self.jinja_sql = JinjaSql() def get_hcs_daily_summary(self, date, provider, provider_uuid, sql_summary_file, tracing_id): """Build HCS daily report. :param date (datetime.date) The date to process :param provider (str) The provider name :param provider_uuid (uuid) ID for cost source :param sql_summary_file (str) The sql file used for processing :param tracing_id (id) Logging identifier :returns (None) """ LOG.info(log_json(tracing_id, "acquiring marketplace data...")) LOG.info(log_json(tracing_id, f"schema: {self.schema}, provider: {provider}, date: {date}")) try: sql = pkgutil.get_data("hcs.database", sql_summary_file) sql = sql.decode("utf-8") sql_params = { "date": date, "schema": self.schema, "ebs_acct_num": self._ebs_acct_num, "table": HCS_TABLE_MAP.get(provider), } sql, sql_params = self.jinja_sql.prepare_query(sql, sql_params) data, description = self._execute_presto_raw_sql_query_with_description( self.schema, sql, bind_params=sql_params ) # The format for the description is: # [(name, type_code, display_size, internal_size, precision, scale, null_ok)] # col[0] grabs the column names from the query results cols = [col[0] for col in description] if len(data) > 0: LOG.info(log_json(tracing_id, f"data found for date: {date}")) csv_handler = CSVFileHandler(self.schema, provider, provider_uuid) csv_handler.write_csv_to_s3(date, data, cols, tracing_id) else: LOG.info(log_json(tracing_id, f"no data found for date: {date}")) except FileNotFoundError: LOG.error(log_json(tracing_id, f"unable to locate SQL file: {sql_summary_file}")) except Exception as err: LOG.error(log_json(tracing_id, err))
def test_large_inclause(self): num_of_params = 50000 alphabets = ['A'] * num_of_params source = "SELECT 'x' WHERE 'A' in {{alphabets | inclause}}" j = JinjaSql() query, bind_params = j.prepare_query(source, {"alphabets": alphabets}) self.assertEquals(len(bind_params), num_of_params) self.assertEquals( query, "SELECT 'x' WHERE 'A' in (" + "%s," * (num_of_params - 1) + "%s)")
class Pollerutil(): def __init__(self, poller_id): self.poller_id = poller_id self.jinja = JinjaSql(param_style='pyformat') try: self.conn = pymssql.connect(server=os.environ.get("DB_CONN"), user=os.environ.get("DB_USER"), password=os.environ.get("DB_PASSWORD"), database=os.environ.get("DB_NAME"), login_timeout=30) except Exception as err: sys.exit() def get_poller_info(self): try: template = sql_utils.sql_templates['poller_config'].value data = {'poll_id': str(self.poller_id)} poller_info = self.jinja_select_query(template, data) return_data = {} if poller_info: return poller_info else: log = 'Poller does not exist. Service not started.' print(log) sys.exit() except Exception as err: sys.exit() def jinja_select_query(self, template=None, data=None): resultset = [] query, bind_params = self.jinja.prepare_query(template, data) no_except = True while no_except: try: db_conn = self.conn cursor = db_conn.cursor(as_dict=True) cursor.execute(query, dict(bind_params)) resultset = cursor.fetchall() no_except = False cursor.close() return resultset except Exception as err: db_conn.rollback() if self.deadlock_validator(err): cursor.close() db_conn.close() time.sleep(3) else: raise ValueError( "Error encountered while selecting data from the database: %s" % (err)) finally: db_conn.close()
def build_asneededDF(site_id, area_id=None): """ Create a Pandas DataFrame of the as needed rooms within the select site_id """ postgres_connection_URL = os.environ.get('DATABASE_URL') engine = create_engine(postgres_connection_URL) # Potentially add an area filter as well? # if area_id: # template with area_id # else # template template = """SELECT * FROM room WHERE site_id = {{site_id}} AND freq = -1""" data = {"site_id": site_id} j = JinjaSql() query, bind_params = j.prepare_query(template, data) # Make DF of just as needed rooms for selected site. as_neededDF = pd.read_sql(sql=query, con=engine, params=bind_params) as_neededDF = as_neededDF[['bm_id', 'name', 'date_last_paint']].copy() # split dated from non-dated no_dateDF = as_neededDF.loc[as_neededDF.date_last_paint.isna()].copy() datedDF = as_neededDF.loc[~as_neededDF.date_last_paint.isna()].copy() # Set to datetime, and drop the time part. datedDF.date_last_paint = datedDF.date_last_paint.astype('datetime64[ns]') datedDF.date_last_paint = datedDF.date_last_paint.dt.strftime('%Y-%m-%d') # Sort them by date and bm_id no_dateDF.sort_values(['bm_id'], inplace=True) datedDF.sort_values(['date_last_paint', 'bm_id'], ascending=True, inplace=True) sortedDF = no_dateDF.append(datedDF) sortedDF.rename( { 'bm_id': 'BM ID', 'name': 'Room Name', 'date_last_paint': 'Date of last Painting' }, axis=1, inplace=True) # sortedDF.set_index('BM ID', inplace=True) sortedDF.reset_index(drop=True, inplace=True) return sortedDF
def apply_sql_template(template, parameters, func_list=None): ''' Apply a JinjaSql template (string) substituting parameters (dict) and return the final SQL. Use the func_list to pass any functions called from the template. ''' j = JinjaSql(param_style='pyformat') if func_list: for func in func_list: j.env.globals[func.__name__] = func query, bind_params = j.prepare_query(template, parameters) return get_sql_from_template(query, bind_params)
def yaml_test(self): source = config['template'] for (param_style, expected_sql) in config['expected_sql'].items(): jinja = JinjaSql(param_style=param_style) query, bind_params = jinja.prepare_query(source, _DATA) if 'expected_params' in config: if param_style in ('pyformat', 'named'): expected_params = config['expected_params']['as_dict'] else: expected_params = config['expected_params']['as_list'] self.assertEquals(list(bind_params), expected_params) self.assertEquals(query.strip(), expected_sql.strip())
def test_include(self): where_clause = """where project_id = {{request.project_id}}""" source = """ select * from dummy {% include 'where_clause.sql' %} """ loader = DictLoader({"where_clause.sql": where_clause}) env = Environment(loader=loader) j = JinjaSql(env) query, bind_params = j.prepare_query(source, _DATA) expected_query = "select * from dummy where project_id = %s" self.assertEquals(query.strip(), expected_query.strip()) self.assertEquals(len(bind_params), 1) self.assertEquals(list(bind_params)[0], 123)
def perform_update_or_delete_operation(sql, binding_params): """ TODO: Facilitate UPDATE &/ DELETE Operation on SQLITE. :param sql: Template: {% raw %} UPDATE tableName SET column_1 = {{column_1_value}}, column_2 = {{column_2_value}} WHERE column_3 = {{ column_3_value }} {% if projectId %} AND projectId = {{ projectId }} {% endif %} {% endraw %} :param binding_params: Template: data = { "column_1_value": 123, "column_2_value": 'dfb', "projectId": u"proton" } :return: """ try: with self.pg_cursor_generator(self.__cursor_engine) as cursor: __j_sql = JinjaSql(param_style='pyformat') query, bind_params = __j_sql.prepare_query(self.generate_sql_template(sql), binding_params) cursor.execute(query, bind_params) return { 'status': True, 'affected_rowcount': cursor.rowcount } except Exception as e: self.model_{{ modelName }}_logger.exception('[{{modelName}} - Exception during UPDATE operation. Details: {}]'.format(str(e))) print(Fore.LIGHTRED_EX + '[{{modelName}} - Exception during UPDATE operation. Details: ' '{}]'.format(str(e)) + Style.RESET_ALL) return { 'status': False, 'affected_rowcount': 0 }
def buildquery(host, start_time, end_time): template = ''' select time_bucket('1 minute', ts) AS period , min(usage) AS min_cpu_usage , max(usage) AS max_cpu_usage from cpu_usage where host = {{ host }} and ts > {{ start_time }} and ts < {{ end_time }} group by period ''' params = {"host": host, "start_time": start_time, "end_time": end_time} j = JinjaSql() return j.prepare_query(template, params)
def getDataPointHumidity(sensorID=27, numRows=1): # select * from aranet_trh_data where sensor_id=27 order by timestamp desc limit 10; params = {"sensor_id": sensorID, "num_Rows": numRows} humidity_transaction_template = """ select timestamp, humidity from aranet_trh_data where (sensor_id = {{ sensor_id }}) order by timestamp desc limit {{ num_Rows }} """ j = JinjaSql(param_style="pyformat") query, bind_params = j.prepare_query(humidity_transaction_template, params) return getData(get_sql_from_template(query=query, bind_params=bind_params))
def custom_sql(table, column, args=None): """ Execute a custom (optionally Jinja-fromatted) SQL query and fail if non-zero number of rows is returned. """ j = JinjaSql(param_style='pyformat') if 'query_file' in args: q_str = open(args['query_file']).read() if 'query' in args: q_str = args['query'] args['table'] = table.name args['column'] = column.name query, bind_params = j.prepare_query(q_str, args) return query % bind_params
def test_import(self): utils = """ {% macro print_where(value) -%} WHERE dummy_col = {{value}} {%- endmacro %} """ source = """ {% import 'utils.sql' as utils %} select * from dual {{ utils.print_where(100) }} """ loader = DictLoader({"utils.sql": utils}) env = Environment(loader=loader) j = JinjaSql(env) query, bind_params = j.prepare_query(source, _DATA) expected_query = "select * from dual WHERE dummy_col = %s" self.assertEquals(query.strip(), expected_query.strip()) self.assertEquals(len(bind_params), 1) self.assertEquals(list(bind_params)[0], 100)
def create_query(city): """ Function: This function creats a SQL query in the right format that can successfuly run and fetch results. A city name is passed to the function in order to query the approriate table instead of fetching all the data at once. """ table = '[dbo].' + '[' + city + ']' user_transaction_template = ''' select * from {{ table_name }} ''' params = {'table_name': table} j = JinjaSql(param_style='pyformat') query, bind_params = j.prepare_query(user_transaction_template, params) dataq = query % bind_params return (dataq)
def getDaysWeather(numDays=2, numRows=5): today = datetime.datetime.now() delta = datetime.timedelta(days=numDays) dateNumDaysAgo = today - delta params = { "timestamp": dateNumDaysAgo.strftime("%Y-%m-%d %H:%M:%S"), "numRows": numRows, } weather_transaction_template = """ select timestamp, temperature, relative_humidity from iweather where timestamp >= {{ timestamp }} order by timestamp asc limit {{ numRows }} """ j = JinjaSql(param_style="pyformat") query, bind_params = j.prepare_query(weather_transaction_template, params) return getData(get_sql_from_template(query=query, bind_params=bind_params))
def result(self): try: backend_path = self.query_path.parent backend = self.backend_lookup(backend_path) db = records.Database(backend) try: jinja = JinjaSql(param_style='named') except NameError: raise Exception("jinjasql >= 0.1.7 required for use") with open(self.query_path, 'r') as query_file: query = query_file.readlines() query_text = ''.join(query) qs, bind_params = jinja.prepare_query(query_text, self.kwargs) result = db.query(qs, **bind_params) result = result.as_dict() except Exception as e: result = [ { 'error': repr(e) }, ] return result
def load_sql_query(sql, query_context_params=None): """Read sql file or string and format with a dictionary of params. Parameters ---------- sql : query_context_params : (Default value = None) Returns ------- """ pat = Path(sql).expanduser() if pat.exists(): with open(pat, 'r') as f: sql = f.read() if query_context_params: j = JinjaSql(param_style='pyformat') binded_sql, bind_params = j.prepare_query(sql, query_context_params) missing_placeholders = [ k for k, v in bind_params.items() if jinja2.Undefined() == v ] assert ( len(missing_placeholders) == 0 ), f'Missing placeholders are: {missing_placeholders}' try: sql = binded_sql % bind_params except KeyError as e: print(e) return return sql
def getDaysHumidity(deltaDays=10, numRows=5, sensorID=27): # select * from aranet_trh_data where sensor_id=27 order by timestamp desc limit 10; today = datetime.datetime.now() delta = datetime.timedelta(days=deltaDays) dateNumDaysAgo = today - delta params = { "sensor_id": sensorID, "timestamp": dateNumDaysAgo.strftime("%Y-%m-%d %H:%M:%S"), "numRows": numRows, } humidity_transaction_template = """ select timestamp, humidity from aranet_trh_data where (sensor_id = {{ sensor_id }} AND timestamp >= {{ timestamp }}) order by timestamp asc limit {{ numRows }} """ j = JinjaSql(param_style="pyformat") query, bind_params = j.prepare_query(humidity_transaction_template, params) return getData(get_sql_from_template(query=query, bind_params=bind_params))
def dataframe_from_sql_template( dal, session_scope, path: str, query_params: Dict[str, str], ) -> pandas.core.frame.DataFrame: """ Returns python pandas dataframe from an sql query file using jinja to inject parameters that are arguments in this function. """ with open(path, 'r') as file: sql_query = file.read() jinja_query = JinjaSql(param_style='pyformat') parametrised_query, bind_params = jinja_query.prepare_query( sql_query, query_params) with session_scope(dal) as session: result = pandas.read_sql(parametrised_query, dal.engine, params=bind_params) session.close() return result
class AWSReportDBAccessor(ReportDBAccessorBase): """Class to interact with customer reporting tables.""" def __init__(self, schema): """Establish the database connection. Args: schema (str): The customer schema to associate with """ super().__init__(schema) self._datetime_format = Config.AWS_DATETIME_STR_FORMAT self.date_accessor = DateAccessor() self.jinja_sql = JinjaSql() def get_cost_entry_bills(self): """Get all cost entry bill objects.""" table_name = AWSCostEntryBill with schema_context(self.schema): columns = [ "id", "bill_type", "payer_account_id", "billing_period_start", "provider_id" ] bills = self._get_db_obj_query(table_name).values(*columns) return {(bill["bill_type"], bill["payer_account_id"], bill["billing_period_start"], bill["provider_id"]): bill["id"] for bill in bills} def get_cost_entry_bills_by_date(self, start_date): """Return a cost entry bill for the specified start date.""" table_name = AWSCostEntryBill with schema_context(self.schema): return self._get_db_obj_query(table_name).filter( billing_period_start=start_date) def get_cost_entry_bills_query_by_provider(self, provider_uuid): """Return all cost entry bills for the specified provider.""" table_name = AWSCostEntryBill with schema_context(self.schema): return self._get_db_obj_query(table_name).filter( provider_id=provider_uuid) def bills_for_provider_uuid(self, provider_uuid, start_date=None): """Return all cost entry bills for provider_uuid on date.""" bills = self.get_cost_entry_bills_query_by_provider(provider_uuid) if start_date: if isinstance(start_date, str): start_date = parse(start_date) bill_date = start_date.replace(day=1) bills = bills.filter(billing_period_start=bill_date) return bills def get_bill_query_before_date(self, date, provider_uuid=None): """Get the cost entry bill objects with billing period before provided date.""" table_name = AWSCostEntryBill with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) if provider_uuid: cost_entry_bill_query = base_query.filter( billing_period_start__lte=date, provider_id=provider_uuid) else: cost_entry_bill_query = base_query.filter( billing_period_start__lte=date) return cost_entry_bill_query def get_lineitem_query_for_billid(self, bill_id): """Get the AWS cost entry line item for a given bill query.""" table_name = AWSCostEntryLineItem with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) line_item_query = base_query.filter(cost_entry_bill_id=bill_id) return line_item_query def get_daily_query_for_billid(self, bill_id): """Get the AWS cost daily item for a given bill query.""" table_name = AWSCostEntryLineItemDaily with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) daily_item_query = base_query.filter(cost_entry_bill_id=bill_id) return daily_item_query def get_summary_query_for_billid(self, bill_id): """Get the AWS cost summary item for a given bill query.""" table_name = AWSCostEntryLineItemDailySummary with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) summary_item_query = base_query.filter(cost_entry_bill_id=bill_id) return summary_item_query def get_ocp_aws_summary_query_for_billid(self, bill_id): """Get the OCP-on-AWS report summary item for a given bill query.""" table_name = AWS_CUR_TABLE_MAP["ocp_on_aws_daily_summary"] base_query = self._get_db_obj_query(table_name) summary_item_query = base_query.filter(cost_entry_bill_id=bill_id) return summary_item_query def get_ocp_aws_project_summary_query_for_billid(self, bill_id): """Get the OCP-on-AWS report project summary item for a given bill query.""" table_name = AWS_CUR_TABLE_MAP["ocp_on_aws_project_daily_summary"] base_query = self._get_db_obj_query(table_name) summary_item_query = base_query.filter(cost_entry_bill_id=bill_id) return summary_item_query def get_cost_entry_query_for_billid(self, bill_id): """Get the AWS cost entry data for a given bill query.""" table_name = AWSCostEntry with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) line_item_query = base_query.filter(bill_id=bill_id) return line_item_query def get_cost_entries(self): """Make a mapping of cost entries by start time.""" table_name = AWSCostEntry with schema_context(self.schema): cost_entries = self._get_db_obj_query(table_name).all() return {(ce.bill_id, ce.interval_start.strftime(self._datetime_format)): ce.id for ce in cost_entries} def get_products(self): """Make a mapping of product sku to product objects.""" table_name = AWSCostEntryProduct with schema_context(self.schema): columns = ["id", "sku", "product_name", "region"] products = self._get_db_obj_query(table_name, columns=columns).all() return {(product["sku"], product["product_name"], product["region"]): product["id"] for product in products} def get_pricing(self): """Make a mapping of pricing values string to pricing objects.""" table_name = AWSCostEntryPricing with schema_context(self.schema): pricing = self._get_db_obj_query(table_name).all() return {f"{p.term}-{p.unit}": p.id for p in pricing} def get_reservations(self): """Make a mapping of reservation ARN to reservation objects.""" table_name = AWSCostEntryReservation with schema_context(self.schema): columns = ["id", "reservation_arn"] reservs = self._get_db_obj_query(table_name, columns=columns).all() return {res["reservation_arn"]: res["id"] for res in reservs} def populate_line_item_daily_table(self, start_date, end_date, bill_ids): """Populate the daily aggregate of line items table. Args: start_date (datetime.date) The date to start populating the table. end_date (datetime.date) The date to end on. bill_ids (list) Returns (None) """ table_name = AWS_CUR_TABLE_MAP["line_item_daily"] daily_sql = pkgutil.get_data( "masu.database", "sql/reporting_awscostentrylineitem_daily.sql") daily_sql = daily_sql.decode("utf-8") daily_sql_params = { "uuid": str(uuid.uuid4()).replace("-", "_"), "start_date": start_date, "end_date": end_date, "bill_ids": bill_ids, "schema": self.schema, } daily_sql, daily_sql_params = self.jinja_sql.prepare_query( daily_sql, daily_sql_params) self._execute_raw_sql_query(table_name, daily_sql, start_date, end_date, bind_params=list(daily_sql_params)) def populate_line_item_daily_summary_table(self, start_date, end_date, bill_ids): """Populate the daily aggregated summary of line items table. Args: start_date (datetime.date) The date to start populating the table. end_date (datetime.date) The date to end on. Returns (None) """ table_name = AWS_CUR_TABLE_MAP["line_item_daily_summary"] summary_sql = pkgutil.get_data( "masu.database", "sql/reporting_awscostentrylineitem_daily_summary.sql") summary_sql = summary_sql.decode("utf-8") summary_sql_params = { "uuid": str(uuid.uuid4()).replace("-", "_"), "start_date": start_date, "end_date": end_date, "bill_ids": bill_ids, "schema": self.schema, } summary_sql, summary_sql_params = self.jinja_sql.prepare_query( summary_sql, summary_sql_params) self._execute_raw_sql_query(table_name, summary_sql, start_date, end_date, bind_params=list(summary_sql_params)) def populate_line_item_daily_summary_table_presto(self, start_date, end_date, source_uuid, bill_id, markup_value): """Populate the daily aggregated summary of line items table. Args: start_date (datetime.date) The date to start populating the table. end_date (datetime.date) The date to end on. Returns (None) """ summary_sql = pkgutil.get_data( "masu.database", "presto_sql/reporting_awscostentrylineitem_daily_summary.sql") summary_sql = summary_sql.decode("utf-8") uuid_str = str(uuid.uuid4()).replace("-", "_") summary_sql_params = { "uuid": uuid_str, "start_date": start_date, "end_date": end_date, "schema": self.schema, "table": PRESTO_LINE_ITEM_TABLE, "source_uuid": source_uuid, "year": start_date.strftime("%Y"), "month": start_date.strftime("%m"), "markup": markup_value if markup_value else 0, "bill_id": bill_id, } summary_sql, summary_sql_params = self.jinja_sql.prepare_query( summary_sql, summary_sql_params) LOG.info(f"Summary SQL: {str(summary_sql)}") self._execute_presto_raw_sql_query(self.schema, summary_sql) def mark_bill_as_finalized(self, bill_id): """Mark a bill in the database as finalized.""" table_name = AWSCostEntryBill with schema_context(self.schema): bill = self._get_db_obj_query(table_name).get(id=bill_id) if bill.finalized_datetime is None: bill.finalized_datetime = self.date_accessor.today_with_timezone( "UTC") bill.save() def populate_tags_summary_table(self, bill_ids): """Populate the line item aggregated totals data table.""" table_name = AWS_CUR_TABLE_MAP["tags_summary"] agg_sql = pkgutil.get_data("masu.database", "sql/reporting_awstags_summary.sql") agg_sql = agg_sql.decode("utf-8") agg_sql_params = {"schema": self.schema, "bill_ids": bill_ids} agg_sql, agg_sql_params = self.jinja_sql.prepare_query( agg_sql, agg_sql_params) self._execute_raw_sql_query(table_name, agg_sql, bind_params=list(agg_sql_params)) def populate_ocp_on_aws_cost_daily_summary(self, start_date, end_date, cluster_id, bill_ids, markup_value): """Populate the daily cost aggregated summary for OCP on AWS. Args: start_date (datetime.date) The date to start populating the table. end_date (datetime.date) The date to end on. Returns (None) """ table_name = AWS_CUR_TABLE_MAP["ocp_on_aws_daily_summary"] summary_sql = pkgutil.get_data( "masu.database", "sql/reporting_ocpawscostlineitem_daily_summary.sql") summary_sql = summary_sql.decode("utf-8") summary_sql_params = { "uuid": str(uuid.uuid4()).replace("-", "_"), "start_date": start_date, "end_date": end_date, "bill_ids": bill_ids, "cluster_id": cluster_id, "schema": self.schema, "markup": markup_value, } summary_sql, summary_sql_params = self.jinja_sql.prepare_query( summary_sql, summary_sql_params) self._execute_raw_sql_query(table_name, summary_sql, start_date, end_date, bind_params=list(summary_sql_params)) def populate_ocp_on_aws_tags_summary_table(self): """Populate the line item aggregated totals data table.""" table_name = AWS_CUR_TABLE_MAP["ocp_on_aws_tags_summary"] agg_sql = pkgutil.get_data("masu.database", "sql/reporting_ocpawstags_summary.sql") agg_sql = agg_sql.decode("utf-8") agg_sql_params = {"schema": self.schema} agg_sql, agg_sql_params = self.jinja_sql.prepare_query( agg_sql, agg_sql_params) self._execute_raw_sql_query(table_name, agg_sql, bind_params=list(agg_sql_params)) def populate_markup_cost(self, markup, start_date, end_date, bill_ids=None): """Set markup costs in the database.""" with schema_context(self.schema): if bill_ids and start_date and end_date: for bill_id in bill_ids: AWSCostEntryLineItemDailySummary.objects.filter( cost_entry_bill_id=bill_id, usage_start__gte=start_date, usage_start__lte=end_date).update( markup_cost=(F("unblended_cost") * markup)) elif bill_ids: for bill_id in bill_ids: AWSCostEntryLineItemDailySummary.objects.filter( cost_entry_bill_id=bill_id).update( markup_cost=(F("unblended_cost") * markup)) def populate_enabled_tag_keys(self, start_date, end_date, bill_ids): """Populate the enabled tag key table. Args: start_date (datetime.date) The date to start populating the table. end_date (datetime.date) The date to end on. bill_ids (list) A list of bill IDs. Returns (None) """ table_name = AWS_CUR_TABLE_MAP["enabled_tag_keys"] summary_sql = pkgutil.get_data("masu.database", "sql/reporting_awsenabledtagkeys.sql") summary_sql = summary_sql.decode("utf-8") summary_sql_params = { "start_date": start_date, "end_date": end_date, "bill_ids": bill_ids, "schema": self.schema, } summary_sql, summary_sql_params = self.jinja_sql.prepare_query( summary_sql, summary_sql_params) self._execute_raw_sql_query(table_name, summary_sql, start_date, end_date, bind_params=list(summary_sql_params)) def update_line_item_daily_summary_with_enabled_tags( self, start_date, end_date, bill_ids): """Populate the enabled tag key table. Args: start_date (datetime.date) The date to start populating the table. end_date (datetime.date) The date to end on. bill_ids (list) A list of bill IDs. Returns (None) """ table_name = AWS_CUR_TABLE_MAP["line_item_daily_summary"] summary_sql = pkgutil.get_data( "masu.database", "sql/reporting_awscostentryline_item_daily_summary_update_enabled_tags.sql" ) summary_sql = summary_sql.decode("utf-8") summary_sql_params = { "start_date": start_date, "end_date": end_date, "bill_ids": bill_ids, "schema": self.schema, } summary_sql, summary_sql_params = self.jinja_sql.prepare_query( summary_sql, summary_sql_params) self._execute_raw_sql_query(table_name, summary_sql, start_date, end_date, bind_params=list(summary_sql_params))
class AzureReportDBAccessor(ReportDBAccessorBase): """Class to interact with Azure Report reporting tables.""" def __init__(self, schema): """Establish the database connection. Args: schema (str): The customer schema to associate with """ super().__init__(schema) self._datetime_format = Config.AZURE_DATETIME_STR_FORMAT self.date_accessor = DateAccessor() self.jinja_sql = JinjaSql() def get_cost_entry_bills(self): """Get all cost entry bill objects.""" table_name = AzureCostEntryBill with schema_context(self.schema): columns = ["id", "billing_period_start", "provider_id"] bills = self._get_db_obj_query(table_name).values(*columns) return {(bill["billing_period_start"], bill["provider_id"]): bill["id"] for bill in bills} def get_products(self): """Make a mapping of product objects.""" table_name = AzureCostEntryProductService with schema_context(self.schema): columns = [ "id", "instance_id", "instance_type", "service_name", "service_tier" ] products = self._get_db_obj_query(table_name, columns=columns).all() return {( product["instance_id"], product["instance_type"], product["service_tier"], product["service_name"], ): product["id"] for product in products} def get_meters(self): """Make a mapping of meter objects.""" table_name = AzureMeter with schema_context(self.schema): columns = ["id", "meter_id"] meters = self._get_db_obj_query(table_name, columns=columns).all() return {(meter["meter_id"]): meter["id"] for meter in meters} def get_cost_entry_bills_query_by_provider(self, provider_uuid): """Return all cost entry bills for the specified provider.""" table_name = AzureCostEntryBill with schema_context(self.schema): return self._get_db_obj_query(table_name).filter( provider_id=provider_uuid) def bills_for_provider_uuid(self, provider_uuid, start_date=None): """Return all cost entry bills for provider_uuid on date.""" bills = self.get_cost_entry_bills_query_by_provider(provider_uuid) if start_date: if isinstance(start_date, str): start_date = parse(start_date) bill_date = start_date.replace(day=1) bills = bills.filter(billing_period_start=bill_date) return bills def populate_line_item_daily_summary_table(self, start_date, end_date, bill_ids): """Populate the daily aggregated summary of line items table. Args: start_date (datetime.date) The date to start populating the table. end_date (datetime.date) The date to end on. Returns (None) """ _start_date = start_date.date() if isinstance(start_date, datetime) else start_date _end_date = end_date.date() if isinstance(end_date, datetime) else end_date table_name = AZURE_REPORT_TABLE_MAP["line_item_daily_summary"] summary_sql = pkgutil.get_data( "masu.database", "sql/reporting_azurecostentrylineitem_daily_summary.sql") summary_sql = summary_sql.decode("utf-8") summary_sql_params = { "uuid": str(uuid.uuid4()).replace("-", "_"), "start_date": _start_date, "end_date": _end_date, "bill_ids": bill_ids, "schema": self.schema, } summary_sql, summary_sql_params = self.jinja_sql.prepare_query( summary_sql, summary_sql_params) self._execute_raw_sql_query(table_name, summary_sql, start_date, end_date, bind_params=list(summary_sql_params)) def populate_line_item_daily_summary_table_presto(self, start_date, end_date, source_uuid, bill_id, markup_value): """Populate the daily aggregated summary of line items table. Args: start_date (datetime.date) The date to start populating the table. end_date (datetime.date) The date to end on. Returns (None) """ summary_sql = pkgutil.get_data( "masu.database", "presto_sql/reporting_azurecostentrylineitem_daily_summary.sql") summary_sql = summary_sql.decode("utf-8") uuid_str = str(uuid.uuid4()).replace("-", "_") summary_sql_params = { "uuid": uuid_str, "start_date": start_date, "end_date": end_date, "schema": self.schema, "table": PRESTO_LINE_ITEM_TABLE, "source_uuid": source_uuid, "year": start_date.strftime("%Y"), "month": start_date.strftime("%m"), "bill_id": bill_id, "markup": markup_value if markup_value else 0, } summary_sql, summary_sql_params = self.jinja_sql.prepare_query( summary_sql, summary_sql_params) LOG.info(f"Summary SQL: {str(summary_sql)}") self._execute_presto_raw_sql_query(self.schema, summary_sql) def populate_tags_summary_table(self, bill_ids): """Populate the line item aggregated totals data table.""" table_name = AZURE_REPORT_TABLE_MAP["tags_summary"] agg_sql = pkgutil.get_data("masu.database", "sql/reporting_azuretags_summary.sql") agg_sql = agg_sql.decode("utf-8") agg_sql_params = {"schema": self.schema, "bill_ids": bill_ids} agg_sql, agg_sql_params = self.jinja_sql.prepare_query( agg_sql, agg_sql_params) self._execute_raw_sql_query(table_name, agg_sql, bind_params=list(agg_sql_params)) def get_cost_entry_bills_by_date(self, start_date): """Return a cost entry bill for the specified start date.""" table_name = AzureCostEntryBill with schema_context(self.schema): return self._get_db_obj_query(table_name).filter( billing_period_start=start_date) def populate_markup_cost(self, markup, start_date, end_date, bill_ids=None): """Set markup costs in the database.""" with schema_context(self.schema): if bill_ids and start_date and end_date: for bill_id in bill_ids: AzureCostEntryLineItemDailySummary.objects.filter( cost_entry_bill_id=bill_id, usage_start__gte=start_date, usage_start__lte=end_date).update( markup_cost=(F("pretax_cost") * markup)) elif bill_ids: for bill_id in bill_ids: AzureCostEntryLineItemDailySummary.objects.filter( cost_entry_bill_id=bill_id).update( markup_cost=(F("pretax_cost") * markup)) def get_bill_query_before_date(self, date, provider_uuid=None): """Get the cost entry bill objects with billing period before provided date.""" table_name = AzureCostEntryBill with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) if provider_uuid: cost_entry_bill_query = base_query.filter( billing_period_start__lte=date, provider_id=provider_uuid) else: cost_entry_bill_query = base_query.filter( billing_period_start__lte=date) return cost_entry_bill_query def get_lineitem_query_for_billid(self, bill_id): """Get the Azure cost entry line item for a given bill query.""" table_name = AzureCostEntryLineItemDaily with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) line_item_query = base_query.filter(cost_entry_bill_id=bill_id) return line_item_query def get_summary_query_for_billid(self, bill_id): """Get the Azure cost summary item for a given bill query.""" table_name = AzureCostEntryLineItemDailySummary with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) summary_item_query = base_query.filter(cost_entry_bill_id=bill_id) return summary_item_query def populate_ocp_on_azure_cost_daily_summary(self, start_date, end_date, cluster_id, bill_ids, markup_value): """Populate the daily cost aggregated summary for OCP on AWS. Args: start_date (datetime.date) The date to start populating the table. end_date (datetime.date) The date to end on. Returns (None) """ table_name = AZURE_REPORT_TABLE_MAP["ocp_on_azure_daily_summary"] summary_sql = pkgutil.get_data( "masu.database", "sql/reporting_ocpazurecostlineitem_daily_summary.sql") summary_sql = summary_sql.decode("utf-8") summary_sql_params = { "uuid": str(uuid.uuid4()).replace("-", "_"), "start_date": start_date, "end_date": end_date, "bill_ids": bill_ids, "cluster_id": cluster_id, "schema": self.schema, "markup": markup_value, } summary_sql, summary_sql_params = self.jinja_sql.prepare_query( summary_sql, summary_sql_params) self._execute_raw_sql_query(table_name, summary_sql, start_date, end_date, bind_params=list(summary_sql_params)) def populate_ocp_on_azure_tags_summary_table(self): """Populate the line item aggregated totals data table.""" table_name = AZURE_REPORT_TABLE_MAP["ocp_on_azure_tags_summary"] agg_sql = pkgutil.get_data("masu.database", "sql/reporting_ocpazuretags_summary.sql") agg_sql = agg_sql.decode("utf-8") agg_sql_params = {"schema": self.schema} agg_sql, agg_sql_params = self.jinja_sql.prepare_query( agg_sql, agg_sql_params) self._execute_raw_sql_query(table_name, agg_sql, bind_params=list(agg_sql_params)) def populate_enabled_tag_keys(self, start_date, end_date, bill_ids): """Populate the enabled tag key table. Args: start_date (datetime.date) The date to start populating the table. end_date (datetime.date) The date to end on. bill_ids (list) A list of bill IDs. Returns (None) """ table_name = AZURE_REPORT_TABLE_MAP["enabled_tag_keys"] summary_sql = pkgutil.get_data( "masu.database", "sql/reporting_azureenabledtagkeys.sql") summary_sql = summary_sql.decode("utf-8") summary_sql_params = { "start_date": start_date, "end_date": end_date, "bill_ids": bill_ids, "schema": self.schema, } summary_sql, summary_sql_params = self.jinja_sql.prepare_query( summary_sql, summary_sql_params) self._execute_raw_sql_query(table_name, summary_sql, start_date, end_date, bind_params=list(summary_sql_params)) def update_line_item_daily_summary_with_enabled_tags( self, start_date, end_date, bill_ids): """Populate the enabled tag key table. Args: start_date (datetime.date) The date to start populating the table. end_date (datetime.date) The date to end on. bill_ids (list) A list of bill IDs. Returns (None) """ table_name = AZURE_REPORT_TABLE_MAP["line_item_daily_summary"] summary_sql = pkgutil.get_data( "masu.database", "sql/reporting_azurecostentryline_item_daily_summary_update_enabled_tags.sql" ) summary_sql = summary_sql.decode("utf-8") summary_sql_params = { "start_date": start_date, "end_date": end_date, "bill_ids": bill_ids, "schema": self.schema, } summary_sql, summary_sql_params = self.jinja_sql.prepare_query( summary_sql, summary_sql_params) self._execute_raw_sql_query(table_name, summary_sql, start_date, end_date, bind_params=list(summary_sql_params))
class OCPReportDBAccessor(ReportDBAccessorBase): """Class to interact with customer reporting tables.""" def __init__(self, schema, column_map): """Establish the database connection. Args: schema (str): The customer schema to associate with column_map (dict): A mapping of report columns to database columns """ super().__init__(schema, column_map) self._datetime_format = Config.OCP_DATETIME_STR_FORMAT self.column_map = column_map self.jinja_sql = JinjaSql() def get_current_usage_report(self): """Get the most recent usage report object.""" table_name = OCP_REPORT_TABLE_MAP['report'] with schema_context(self.schema): return self._get_db_obj_query(table_name)\ .order_by('-interval_start')\ .first() def get_current_usage_period(self): """Get the most recent usage report period object.""" table_name = OCP_REPORT_TABLE_MAP['report_period'] with schema_context(self.schema): return self._get_db_obj_query(table_name)\ .order_by('-report_period_start')\ .first() def get_usage_periods_by_date(self, start_date): """Return all report period entries for the specified start date.""" table_name = OCP_REPORT_TABLE_MAP['report_period'] with schema_context(self.schema): return self._get_db_obj_query(table_name)\ .filter(report_period_start=start_date)\ .all() def get_usage_period_before_date(self, date): """Get the usage report period objects before provided date.""" table_name = OCP_REPORT_TABLE_MAP['report_period'] with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) usage_period_query = base_query.filter(report_period_start__lte=date) return usage_period_query # pylint: disable=invalid-name def get_usage_period_query_by_provider(self, provider_uuid): """Return all report periods for the specified provider.""" table_name = OCP_REPORT_TABLE_MAP['report_period'] with schema_context(self.schema): return self._get_db_obj_query(table_name)\ .filter(provider_id=provider_uuid) def report_periods_for_provider_uuid(self, provider_uuid, start_date=None): """Return all report periods for provider_uuid on date.""" report_periods = self.get_usage_period_query_by_provider(provider_uuid) with schema_context(self.schema): if start_date: report_date = parse(start_date).replace(day=1) report_periods = report_periods.filter( report_period_start=report_date ).all() return report_periods def get_lineitem_query_for_reportid(self, query_report_id): """Get the usage report line item for a report id query.""" table_name = OCP_REPORT_TABLE_MAP['line_item'] with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) line_item_query = base_query.filter(report_id=query_report_id) return line_item_query def get_daily_usage_query_for_clusterid(self, cluster_identifier): """Get the usage report daily item for a cluster id query.""" table_name = OCP_REPORT_TABLE_MAP['line_item_daily'] with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) daily_usage_query = base_query.filter(cluster_id=cluster_identifier) return daily_usage_query def get_summary_usage_query_for_clusterid(self, cluster_identifier): """Get the usage report summary for a cluster id query.""" table_name = OCP_REPORT_TABLE_MAP['line_item_daily_summary'] with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) summary_usage_query = base_query.filter(cluster_id=cluster_identifier) return summary_usage_query def get_item_query_report_period_id(self, report_period_id): """Get the usage report line item for a report id query.""" table_name = OCP_REPORT_TABLE_MAP['line_item'] with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) line_item_query = base_query.filter(report_period_id=report_period_id) return line_item_query def get_storage_item_query_report_period_id(self, report_period_id): """Get the storage report line item for a report id query.""" table_name = OCP_REPORT_TABLE_MAP['storage_line_item'] with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) line_item_query = base_query.filter(report_period_id=report_period_id) return line_item_query def get_daily_storage_item_query_cluster_id(self, cluster_identifier): """Get the daily storage report line item for a cluster id query.""" table_name = OCP_REPORT_TABLE_MAP['storage_line_item_daily'] with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) daily_item_query = base_query.filter(cluster_id=cluster_identifier) return daily_item_query def get_storage_summary_query_cluster_id(self, cluster_identifier): """Get the storage report summary for a cluster id query.""" table_name = OCP_REPORT_TABLE_MAP['line_item_daily_summary'] filters = { 'cluster_id': cluster_identifier, 'data_source': 'Storage' } with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) daily_item_query = base_query.filter(**filters) return daily_item_query def get_ocp_aws_summary_query_for_cluster_id(self, cluster_identifier): """Get the OCP-on-AWS report summary item for a given cluster id query.""" table_name = AWS_CUR_TABLE_MAP['ocp_on_aws_daily_summary'] with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) summary_item_query = base_query.filter(cluster_id=cluster_identifier) return summary_item_query def get_ocp_aws_project_summary_query_for_cluster_id(self, cluster_identifier): """Get the OCP-on-AWS report project summary item for a given cluster id query.""" table_name = AWS_CUR_TABLE_MAP['ocp_on_aws_project_daily_summary'] with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) summary_item_query = base_query.filter(cluster_id=cluster_identifier) return summary_item_query def get_report_query_report_period_id(self, report_period_id): """Get the usage report line item for a report id query.""" table_name = OCP_REPORT_TABLE_MAP['report'] with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) usage_report_query = base_query.filter(report_period_id=report_period_id) return usage_report_query def get_report_periods(self): """Get all usage period objects.""" periods = [] with schema_context(self.schema): periods = OCPUsageReportPeriod.objects.values('id', 'cluster_id', 'report_period_start', 'provider_id') return_value = {(p['cluster_id'], p['report_period_start'], p['provider_id']): p['id'] for p in periods} return return_value def get_reports(self): """Make a mapping of reports by time.""" with schema_context(self.schema): reports = OCPUsageReport.objects.all() return {(entry.report_period_id, entry.interval_start.strftime(self._datetime_format)): entry.id for entry in reports} def get_pod_usage_cpu_core_hours(self, cluster_id=None): """Make a mapping of cpu pod usage hours.""" table = OCPUsageLineItemDailySummary filters = { 'data_source': 'Pod' } if cluster_id: filters['cluster_id'] = cluster_id with schema_context(self.schema): reports = self._get_reports(table, filters) return {entry.id: entry.pod_usage_cpu_core_hours for entry in reports} def _get_reports(self, table, filters=None): """Return requested reports from given table. Args: table (Django models.Model object): The table to query against filters (dict): Columns to filter the query on Returns: (QuerySet): Django queryset of objects queried on """ with schema_context(self.schema): if filters: reports = self._get_db_obj_query(table).filter(**filters).all() else: reports = self._get_db_obj_query(table).all() return reports def get_pod_request_cpu_core_hours(self, cluster_id=None): """Make a mapping of cpu pod request hours.""" table = OCPUsageLineItemDailySummary filters = { 'data_source': 'Pod' } if cluster_id: filters['cluster_id'] = cluster_id with schema_context(self.schema): reports = self._get_reports(table, filters) return {entry.id: entry.pod_request_cpu_core_hours for entry in reports} def get_pod_usage_memory_gigabyte_hours(self, cluster_id=None): """Make a mapping of memory_usage hours.""" table = OCPUsageLineItemDailySummary filters = { 'data_source': 'Pod' } if cluster_id: filters['cluster_id'] = cluster_id with schema_context(self.schema): reports = self._get_reports(table, filters) return {entry.id: entry.pod_usage_memory_gigabyte_hours for entry in reports} def get_pod_request_memory_gigabyte_hours(self, cluster_id=None): """Make a mapping of memory_request_hours.""" table = OCPUsageLineItemDailySummary filters = { 'data_source': 'Pod' } if cluster_id: filters['cluster_id'] = cluster_id with schema_context(self.schema): reports = self._get_reports(table, filters) return {entry.id: entry.pod_request_memory_gigabyte_hours for entry in reports} def get_persistentvolumeclaim_usage_gigabyte_months(self, cluster_id=None): """Make a mapping of persistentvolumeclaim_usage_gigabyte_months.""" table = OCPUsageLineItemDailySummary filters = { 'data_source': 'Storage' } if cluster_id: filters['cluster_id'] = cluster_id with schema_context(self.schema): reports = self._get_reports(table, filters) # pylint: disable=line-too-long return {entry.id: entry.persistentvolumeclaim_usage_gigabyte_months for entry in reports} def get_volume_request_storage_gigabyte_months(self, cluster_id=None): """Make a mapping of volume_request_storage_gigabyte_months.""" table = OCPUsageLineItemDailySummary filters = { 'data_source': 'Storage' } if cluster_id: filters['cluster_id'] = cluster_id with schema_context(self.schema): reports = self._get_reports(table, filters) return {entry.id: entry.volume_request_storage_gigabyte_months for entry in reports} def populate_line_item_daily_table(self, start_date, end_date, cluster_id): """Populate the daily aggregate of line items table. Args: start_date (datetime.date) The date to start populating the table. end_date (datetime.date) The date to end on. cluster_id (String) Cluster Identifier Returns (None) """ # Cast start_date and end_date into date object instead of string if isinstance(start_date, str): start_date = datetime.datetime.strptime(start_date, '%Y-%m-%d').date() end_date = datetime.datetime.strptime(end_date, '%Y-%m-%d').date() if isinstance(start_date, datetime.datetime): start_date = start_date.date() end_date = end_date.date() table_name = OCP_REPORT_TABLE_MAP['line_item_daily'] daily_sql = pkgutil.get_data( 'masu.database', 'sql/reporting_ocpusagelineitem_daily.sql' ) daily_sql = daily_sql.decode('utf-8') daily_sql_params = { 'uuid': str(uuid.uuid4()).replace('-', '_'), 'start_date': start_date, 'end_date': end_date, 'cluster_id': cluster_id, 'schema': self.schema } daily_sql, daily_sql_params = self.jinja_sql.prepare_query( daily_sql, daily_sql_params ) self._execute_raw_sql_query( table_name, daily_sql, start_date, end_date, bind_params=list(daily_sql_params)) def get_ocp_infrastructure_map(self, start_date, end_date, **kwargs): """Get the OCP on infrastructure map. Args: start_date (datetime.date) The date to start populating the table. end_date (datetime.date) The date to end on. Returns (None) """ # kwargs here allows us to optionally pass in a provider UUID based on # the provider type this is run for ocp_provider_uuid = kwargs.get('ocp_provider_uuid') aws_provider_uuid = kwargs.get('aws_provider_uuid') azure_provider_uuid = kwargs.get('azure_provider_uuid') # In case someone passes this function a string instead of the date object like we asked... # Cast the string into a date object, end_date into date object instead of string if isinstance(start_date, str): start_date = datetime.datetime.strptime(start_date, '%Y-%m-%d').date() end_date = datetime.datetime.strptime(end_date, '%Y-%m-%d').date() infra_sql = pkgutil.get_data( 'masu.database', 'sql/reporting_ocpinfrastructure_provider_map.sql' ) infra_sql = infra_sql.decode('utf-8') infra_sql_params = { 'uuid': str(uuid.uuid4()).replace('-', '_'), 'start_date': start_date, 'end_date': end_date, 'schema': self.schema, 'aws_provider_uuid': aws_provider_uuid, 'ocp_provider_uuid': ocp_provider_uuid, 'azure_provider_uuid': azure_provider_uuid } infra_sql, infra_sql_params = self.jinja_sql.prepare_query( infra_sql, infra_sql_params) with connection.cursor() as cursor: cursor.db.set_schema(self.schema) cursor.execute(infra_sql, list(infra_sql_params)) results = cursor.fetchall() db_results = {} for entry in results: # This dictionary is keyed on an OpenShift provider UUID # and the tuple contains # (Infrastructure Provider UUID, Infrastructure Provider Type) db_results[entry[0]] = (entry[1], entry[2]) return db_results def populate_storage_line_item_daily_table(self, start_date, end_date, cluster_id): """Populate the daily storage aggregate of line items table. Args: start_date (datetime.date) The date to start populating the table. end_date (datetime.date) The date to end on. cluster_id (String) Cluster Identifier Returns (None) """ # Cast string to date object if isinstance(start_date, str): start_date = datetime.datetime.strptime(start_date, '%Y-%m-%d').date() end_date = datetime.datetime.strptime(end_date, '%Y-%m-%d').date() if isinstance(start_date, datetime.datetime): start_date = start_date.date() end_date = end_date.date() table_name = OCP_REPORT_TABLE_MAP['storage_line_item_daily'] daily_sql = pkgutil.get_data( 'masu.database', 'sql/reporting_ocpstoragelineitem_daily.sql' ) daily_sql = daily_sql.decode('utf-8') daily_sql_params = { 'uuid': str(uuid.uuid4()).replace('-', '_'), 'start_date': start_date, 'end_date': end_date, 'cluster_id': cluster_id, 'schema': self.schema } daily_sql, daily_sql_params = self.jinja_sql.prepare_query( daily_sql, daily_sql_params ) self._execute_raw_sql_query( table_name, daily_sql, start_date, end_date, bind_params=list(daily_sql_params)) def populate_pod_charge(self, cpu_temp_table, mem_temp_table): """Populate the memory and cpu charge on daily summary table. Args: cpu_temp_table (String) Name of cpu charge temp table mem_temp_table (String) Name of mem charge temp table Returns (None) """ table_name = OCP_REPORT_TABLE_MAP['line_item_daily_summary'] daily_charge_sql = pkgutil.get_data( 'masu.database', 'sql/reporting_ocpusagelineitem_daily_pod_charge.sql' ) charge_line_sql = daily_charge_sql.decode('utf-8') charge_line_sql_params = { 'cpu_temp': cpu_temp_table, 'mem_temp': mem_temp_table, 'schema': self.schema } charge_line_sql, charge_line_sql_params = self.jinja_sql.prepare_query( charge_line_sql, charge_line_sql_params ) self._execute_raw_sql_query( table_name, charge_line_sql, bind_params=list(charge_line_sql_params)) def populate_storage_charge(self, temp_table_name): """Populate the storage charge into the daily summary table. Args: storage_charge (Float) Storage charge. Returns (None) """ table_name = OCP_REPORT_TABLE_MAP['line_item_daily_summary'] daily_charge_sql = pkgutil.get_data( 'masu.database', 'sql/reporting_ocp_storage_charge.sql' ) charge_line_sql = daily_charge_sql.decode('utf-8') charge_line_sql_params = { 'temp_table': temp_table_name, 'schema': self.schema } charge_line_sql, charge_line_sql_params = self.jinja_sql.prepare_query( charge_line_sql, charge_line_sql_params ) self._execute_raw_sql_query( table_name, charge_line_sql, bind_params=list(charge_line_sql_params)) def populate_line_item_daily_summary_table(self, start_date, end_date, cluster_id): """Populate the daily aggregate of line items table. Args: start_date (datetime.date) The date to start populating the table. end_date (datetime.date) The date to end on. cluster_id (String) Cluster Identifier Returns (None) """ # Cast start_date to date if isinstance(start_date, str): start_date = datetime.datetime.strptime(start_date, '%Y-%m-%d').date() end_date = datetime.datetime.strptime(end_date, '%Y-%m-%d').date() if isinstance(start_date, datetime.datetime): start_date = start_date.date() end_date = end_date.date() table_name = OCP_REPORT_TABLE_MAP['line_item_daily_summary'] summary_sql = pkgutil.get_data( 'masu.database', 'sql/reporting_ocpusagelineitem_daily_summary.sql' ) summary_sql = summary_sql.decode('utf-8') summary_sql_params = { 'uuid': str(uuid.uuid4()).replace('-', '_'), 'start_date': start_date, 'end_date': end_date, 'cluster_id': cluster_id, 'schema': self.schema } summary_sql, summary_sql_params = self.jinja_sql.prepare_query( summary_sql, summary_sql_params ) self._execute_raw_sql_query( table_name, summary_sql, start_date, end_date, bind_params=list(summary_sql_params)) def populate_storage_line_item_daily_summary_table(self, start_date, end_date, cluster_id): """Populate the daily aggregate of storage line items table. Args: start_date (datetime.date) The date to start populating the table. end_date (datetime.date) The date to end on. cluster_id (String) Cluster Identifier Returns (None) """ # Cast start_date and end_date to date object, if they aren't already if isinstance(start_date, str): start_date = datetime.datetime.strptime(start_date, '%Y-%m-%d').date() end_date = datetime.datetime.strptime(end_date, '%Y-%m-%d').date() if isinstance(start_date, datetime.datetime): start_date = start_date.date() end_date = end_date.date() table_name = OCP_REPORT_TABLE_MAP['line_item_daily_summary'] summary_sql = pkgutil.get_data( 'masu.database', 'sql/reporting_ocpstoragelineitem_daily_summary.sql' ) summary_sql = summary_sql.decode('utf-8') summary_sql_params = { 'uuid': str(uuid.uuid4()).replace('-', '_'), 'start_date': start_date, 'end_date': end_date, 'cluster_id': cluster_id, 'schema': self.schema } summary_sql, summary_sql_params = self.jinja_sql.prepare_query( summary_sql, summary_sql_params ) self._execute_raw_sql_query( table_name, summary_sql, start_date, end_date, list(summary_sql_params)) def update_summary_infrastructure_cost(self, cluster_id, start_date, end_date): """Populate the infrastructure costs on the daily usage summary table. Args: start_date (datetime.date) The date to start populating the table. end_date (datetime.date) The date to end on. cluster_id (String) Cluster Identifier Returns (None) """ # Cast start_date to date object if isinstance(start_date, str): start_date = datetime.datetime.strptime(start_date, '%Y-%m-%d').date() end_date = datetime.datetime.strptime(end_date, '%Y-%m-%d').date() if isinstance(start_date, datetime.datetime): start_date = start_date.date() end_date = end_date.date() table_name = OCP_REPORT_TABLE_MAP['line_item_daily_summary'] if start_date is None: start_date_qry = self._get_db_obj_query(table_name).order_by('usage_start').first() start_date = str(start_date_qry.usage_start) if start_date_qry else None if end_date is None: end_date_qry = self._get_db_obj_query(table_name).order_by('-usage_start').first() end_date = str(end_date_qry.usage_start) if end_date_qry else None summary_sql = pkgutil.get_data( 'masu.database', 'sql/reporting_ocpcosts_summary.sql' ) if start_date and end_date: summary_sql = summary_sql.decode('utf-8') summary_sql_params = { 'uuid': str(uuid.uuid4()).replace('-', '_'), 'start_date': start_date, 'end_date': end_date, 'cluster_id': cluster_id, 'schema': self.schema } summary_sql, summary_sql_params = self.jinja_sql.prepare_query( summary_sql, summary_sql_params) self._execute_raw_sql_query( table_name, summary_sql, start_date, end_date, bind_params=list(summary_sql_params)) def get_cost_summary_for_clusterid(self, cluster_identifier): """Get the cost summary for a cluster id query.""" table_name = OCP_REPORT_TABLE_MAP['cost_summary'] base_query = self._get_db_obj_query(table_name) cost_summary_query = base_query.filter(cluster_id=cluster_identifier) return cost_summary_query # pylint: disable=invalid-name def populate_pod_label_summary_table(self): """Populate the line item aggregated totals data table.""" table_name = OCP_REPORT_TABLE_MAP['pod_label_summary'] agg_sql = pkgutil.get_data( 'masu.database', f'sql/reporting_ocpusagepodlabel_summary.sql' ) agg_sql = agg_sql.decode('utf-8') agg_sql_params = {'schema': self.schema} agg_sql, agg_sql_params = self.jinja_sql.prepare_query( agg_sql, agg_sql_params ) self._execute_raw_sql_query(table_name, agg_sql, bind_params=list(agg_sql_params)) # pylint: disable=invalid-name def populate_volume_claim_label_summary_table(self): """Populate the OCP volume claim label summary table.""" table_name = OCP_REPORT_TABLE_MAP['volume_claim_label_summary'] agg_sql = pkgutil.get_data( 'masu.database', f'sql/reporting_ocpstoragevolumeclaimlabel_summary.sql' ) agg_sql = agg_sql.decode('utf-8') agg_sql_params = {'schema': self.schema} agg_sql, agg_sql_params = self.jinja_sql.prepare_query( agg_sql, agg_sql_params ) self._execute_raw_sql_query(table_name, agg_sql, bind_params=list(agg_sql_params)) # pylint: disable=invalid-name def populate_volume_label_summary_table(self): """Populate the OCP volume label summary table.""" table_name = OCP_REPORT_TABLE_MAP['volume_label_summary'] agg_sql = pkgutil.get_data( 'masu.database', f'sql/reporting_ocpstoragevolumelabel_summary.sql' ) agg_sql = agg_sql.decode('utf-8') agg_sql_params = {'schema': self.schema} agg_sql, agg_sql_params = self.jinja_sql.prepare_query( agg_sql, agg_sql_params ) self._execute_raw_sql_query(table_name, agg_sql, bind_params=list(agg_sql_params)) def populate_markup_cost(self, infra_provider_markup, ocp_markup, cluster_id): """Set markup cost for OCP including infrastructure cost markup.""" with schema_context(self.schema): OCPUsageLineItemDailySummary.objects.filter(cluster_id=cluster_id).update( markup_cost=( ( Coalesce( F('pod_charge_cpu_core_hours'), Value(0, output_field=DecimalField()) ) + Coalesce( F('pod_charge_memory_gigabyte_hours'), Value(0, output_field=DecimalField()) ) + Coalesce( F('persistentvolumeclaim_charge_gb_month'), Value(0, output_field=DecimalField()) ) ) * ocp_markup + ( Coalesce( F('infra_cost'), Value(0, output_field=DecimalField()) ) ) * infra_provider_markup ) ) OCPUsageLineItemDailySummary.objects.filter(cluster_id=cluster_id).update( project_markup_cost=( ( Coalesce( F('pod_charge_cpu_core_hours'), Value(0, output_field=DecimalField()) ) + Coalesce( F('pod_charge_memory_gigabyte_hours'), Value(0, output_field=DecimalField()) ) + Coalesce( F('persistentvolumeclaim_charge_gb_month'), Value(0, output_field=DecimalField()) ) ) * ocp_markup + ( Coalesce(F('project_infra_cost'), Value(0, output_field=DecimalField())) ) * infra_provider_markup ) ) def populate_monthly_cost(self, node_cost, start_date=None, end_date=None): """ Populate the monthly cost of a customer. Right now this is just the node/month cost. Calculated from node_cost * number_unique_nodes. args: node_cost (Decimal): The node cost per month start_date (datetime, str): The start_date to calculate monthly_cost. end_date (datetime, str): The end_date to calculate monthly_cost. """ if isinstance(start_date, str): start_date = parse(start_date) if isinstance(end_date, str): end_date = parse(end_date) if not start_date: # If start_date is not provided, recalculate from the first month start_date = OCPUsageLineItemDailySummary.objects.aggregate( Min('usage_start') )['usage_start__min'] if not end_date: # If end_date is not provided, recalculate till the latest month end_date = OCPUsageLineItemDailySummary.objects.aggregate( Max('usage_end') )['usage_end__max'] LOG.info('Populating Monthly cost from %s to %s.', start_date, end_date) first_month = start_date.replace(day=1, hour=0, minute=0, second=0, microsecond=0) with schema_context(self.schema): # Calculate monthly cost for every month for curr_month in rrule(freq=MONTHLY, until=end_date, dtstart=first_month): first_curr_month, first_next_month = month_date_range_tuple(curr_month) unique_nodes = OCPUsageLineItemDailySummary.objects.\ filter(usage_start__gte=first_curr_month, usage_start__lt=first_next_month, node__isnull=False ).values_list('node').distinct().count() total_cost = node_cost * unique_nodes LOG.info('Total Cost is %s for %s nodes.', total_cost, unique_nodes) # Remove existing monthly costs OCPUsageLineItemDailySummary.objects.filter( usage_start=first_curr_month, monthly_cost__isnull=False ).delete() # Create new monthly cost OCPUsageLineItemDailySummary.objects.create( usage_start=first_curr_month, usage_end=first_curr_month, monthly_cost=total_cost )
def get_column_type(column_name, rows): """Tests to find data type""" conn = pg_conn(**project_conf['db_info']) curs = conn.cursor() j = JinjaSql() ##testing column name templ = """create temp table test_table ({{column_name | sqlsafe}} TEXT)""" vals = dict(column_name=column_name) try: query, bind_params = j.prepare_query(templ, vals) except: print("Unable to prepare query from jinja for: {}".format(key)) return None try: curs.execute(query, list(bind_params)) except: print("Query failed for: {}".format(key)) return None rows_io = io.StringIO('\n'.join(map(str, rows))) curs.copy_from(rows_io, 'test_table') conn.commit() curs.execute("SELECT count(*) from test_table") templ = """SELECT {{column_name|sqlsafe}}::{{test_type|sqlsafe}} FROM test_table TABLESAMPLE SYSTEM ({{percent|sqlsafe}})""" possible_types = [ 'BOOLEAN', 'TIMESTAMP', 'TIME', 'DATE', 'INT', 'FLOAT', 'TEXT' ] working_types = [] for test_type in possible_types: vals = dict(column_name=column_name, test_type=test_type, percent=10) try: query, bind_params = j.prepare_query(templ, vals) except: print("Jinja unable to parse {} for type: {}".format(templ, vals)) try: curs.execute(query, list(bind_params)) sample_good = True except: sample_good = False conn.rollback() if sample_good: vals['percent'] = 100 query, bind_params = j.prepare_query(templ, vals) try: curs.execute(query, list(bind_params)) working_types.append(test_type) except: conn.rollback() pass if working_types: return working_types[0] else: return None return working_types[0]
class AzureReportDBAccessor(ReportDBAccessorBase): """Class to interact with Azure Report reporting tables.""" def __init__(self, schema, column_map): """Establish the database connection. Args: schema (str): The customer schema to associate with column_map (dict): A mapping of report columns to database columns """ super().__init__(schema, column_map) self._datetime_format = Config.AZURE_DATETIME_STR_FORMAT self.column_map = column_map self._schema_name = schema self.date_accessor = DateAccessor() self.jinja_sql = JinjaSql() def get_cost_entry_bills(self): """Get all cost entry bill objects.""" table_name = AzureCostEntryBill with schema_context(self.schema): columns = ['id', 'billing_period_start', 'provider_id'] bills = self._get_db_obj_query(table_name).values(*columns) return {(bill['billing_period_start'], bill['provider_id']): bill['id'] for bill in bills} def get_products(self): """Make a mapping of product objects.""" table_name = AzureCostEntryProductService with schema_context(self.schema): columns = ['id', 'instance_id', 'service_name', 'service_tier'] products = self._get_db_obj_query(table_name, columns=columns).all() return {(product['instance_id'], product['service_name'], product['service_tier']): product['id'] for product in products} def get_meters(self): """Make a mapping of meter objects.""" table_name = AzureMeter with schema_context(self.schema): columns = ['id', 'meter_id'] meters = self._get_db_obj_query(table_name, columns=columns).all() return {(meter['meter_id']): meter['id'] for meter in meters} # pylint: disable=invalid-name def get_cost_entry_bills_query_by_provider(self, provider_id): """Return all cost entry bills for the specified provider.""" table_name = AzureCostEntryBill with schema_context(self.schema): return self._get_db_obj_query(table_name)\ .filter(provider_id=provider_id) def bills_for_provider_id(self, provider_id, start_date=None): """Return all cost entry bills for provider_id on date.""" bills = self.get_cost_entry_bills_query_by_provider(provider_id) if start_date: bill_date = parse(start_date).replace(day=1) bills = bills.filter(billing_period_start=bill_date) return bills def populate_line_item_daily_summary_table(self, start_date, end_date, bill_ids): """Populate the daily aggregated summary of line items table. Args: start_date (datetime.date) The date to start populating the table. end_date (datetime.date) The date to end on. Returns (None) """ table_name = AZURE_REPORT_TABLE_MAP['line_item_daily_summary'] summary_sql = pkgutil.get_data( 'masu.database', 'sql/reporting_azurecostentrylineitem_daily_summary.sql' ) summary_sql = summary_sql.decode('utf-8') summary_sql_params = { 'uuid': str(uuid.uuid4()).replace('-', '_'), 'start_date': start_date, 'end_date': end_date, 'bill_ids': bill_ids, 'schema': self.schema } summary_sql, summary_sql_params = self.jinja_sql.prepare_query( summary_sql, summary_sql_params) self._commit_and_vacuum( table_name, summary_sql, start_date, end_date, bind_params=list(summary_sql_params)) # pylint: disable=invalid-name def populate_tags_summary_table(self): """Populate the line item aggregated totals data table.""" table_name = AZURE_REPORT_TABLE_MAP['tags_summary'] agg_sql = pkgutil.get_data( 'masu.database', f'sql/reporting_azuretags_summary.sql' ) agg_sql = agg_sql.decode('utf-8') agg_sql_params = {'schema': self.schema} agg_sql, agg_sql_params = self.jinja_sql.prepare_query( agg_sql, agg_sql_params ) self._commit_and_vacuum(table_name, agg_sql, bind_params=list(agg_sql_params)) def get_cost_entry_bills_by_date(self, start_date): """Return a cost entry bill for the specified start date.""" table_name = AzureCostEntryBill with schema_context(self.schema): return self._get_db_obj_query(table_name)\ .filter(billing_period_start=start_date) def populate_markup_cost(self, markup, bill_ids=None): """Set markup costs in the database.""" with schema_context(self.schema): if bill_ids: for bill_id in bill_ids: AzureCostEntryLineItemDailySummary.objects.\ filter(cost_entry_bill_id=bill_id).\ update(markup_cost=(F('pretax_cost') * markup)) else: AzureCostEntryLineItemDailySummary.objects.\ update(markup_cost=(F('pretax_cost') * markup)) def get_bill_query_before_date(self, date): """Get the cost entry bill objects with billing period before provided date.""" table_name = AzureCostEntryBill with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) cost_entry_bill_query = base_query.filter(billing_period_start__lte=date) return cost_entry_bill_query def get_lineitem_query_for_billid(self, bill_id): """Get the Azure cost entry line item for a given bill query.""" table_name = AzureCostEntryLineItemDaily with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) line_item_query = base_query.filter(cost_entry_bill_id=bill_id) return line_item_query def get_summary_query_for_billid(self, bill_id): """Get the Azure cost summary item for a given bill query.""" table_name = AzureCostEntryLineItemDailySummary with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) summary_item_query = base_query.filter(cost_entry_bill_id=bill_id) return summary_item_query
class AWSReportDBAccessor(ReportDBAccessorBase): """Class to interact with customer reporting tables.""" def __init__(self, schema, column_map): """Establish the database connection. Args: schema (str): The customer schema to associate with column_map (dict): A mapping of report columns to database columns """ super().__init__(schema, column_map) self._datetime_format = Config.AWS_DATETIME_STR_FORMAT self.column_map = column_map self._schema_name = schema self.date_accessor = DateAccessor() self.jinja_sql = JinjaSql() def get_cost_entry_bills(self): """Get all cost entry bill objects.""" table_name = AWSCostEntryBill with schema_context(self.schema): columns = [ 'id', 'bill_type', 'payer_account_id', 'billing_period_start', 'provider_id' ] bills = self._get_db_obj_query(table_name).values(*columns) return {(bill['bill_type'], bill['payer_account_id'], bill['billing_period_start'], bill['provider_id']): bill['id'] for bill in bills} def get_cost_entry_bills_by_date(self, start_date): """Return a cost entry bill for the specified start date.""" table_name = AWSCostEntryBill with schema_context(self.schema): return self._get_db_obj_query(table_name)\ .filter(billing_period_start=start_date) # pylint: disable=invalid-name def get_cost_entry_bills_query_by_provider(self, provider_uuid): """Return all cost entry bills for the specified provider.""" table_name = AWSCostEntryBill with schema_context(self.schema): return self._get_db_obj_query(table_name)\ .filter(provider_id=provider_uuid) def bills_for_provider_uuid(self, provider_uuid, start_date=None): """Return all cost entry bills for provider_uuid on date.""" bills = self.get_cost_entry_bills_query_by_provider(provider_uuid) if start_date: bill_date = parse(start_date).replace(day=1) bills = bills.filter(billing_period_start=bill_date) return bills def get_bill_query_before_date(self, date): """Get the cost entry bill objects with billing period before provided date.""" table_name = AWSCostEntryBill with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) cost_entry_bill_query = base_query.filter( billing_period_start__lte=date) return cost_entry_bill_query def get_lineitem_query_for_billid(self, bill_id): """Get the AWS cost entry line item for a given bill query.""" table_name = AWSCostEntryLineItem with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) line_item_query = base_query.filter(cost_entry_bill_id=bill_id) return line_item_query def get_daily_query_for_billid(self, bill_id): """Get the AWS cost daily item for a given bill query.""" table_name = AWSCostEntryLineItemDaily with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) daily_item_query = base_query.filter(cost_entry_bill_id=bill_id) return daily_item_query def get_summary_query_for_billid(self, bill_id): """Get the AWS cost summary item for a given bill query.""" table_name = AWSCostEntryLineItemDailySummary with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) summary_item_query = base_query.filter(cost_entry_bill_id=bill_id) return summary_item_query def get_ocp_aws_summary_query_for_billid(self, bill_id): """Get the OCP-on-AWS report summary item for a given bill query.""" table_name = AWS_CUR_TABLE_MAP['ocp_on_aws_daily_summary'] base_query = self._get_db_obj_query(table_name) summary_item_query = base_query.filter(cost_entry_bill_id=bill_id) return summary_item_query def get_ocp_aws_project_summary_query_for_billid(self, bill_id): """Get the OCP-on-AWS report project summary item for a given bill query.""" table_name = AWS_CUR_TABLE_MAP['ocp_on_aws_project_daily_summary'] base_query = self._get_db_obj_query(table_name) summary_item_query = base_query.filter(cost_entry_bill_id=bill_id) return summary_item_query def get_cost_entry_query_for_billid(self, bill_id): """Get the AWS cost entry data for a given bill query.""" table_name = AWSCostEntry with schema_context(self.schema): base_query = self._get_db_obj_query(table_name) line_item_query = base_query.filter(bill_id=bill_id) return line_item_query def get_cost_entries(self): """Make a mapping of cost entries by start time.""" table_name = AWSCostEntry with schema_context(self.schema): cost_entries = self._get_db_obj_query(table_name).all() return {(ce.bill_id, ce.interval_start.strftime(self._datetime_format)): ce.id for ce in cost_entries} def get_products(self): """Make a mapping of product sku to product objects.""" table_name = AWSCostEntryProduct with schema_context(self.schema): columns = ['id', 'sku', 'product_name', 'region'] products = self._get_db_obj_query(table_name, columns=columns).all() return {(product['sku'], product['product_name'], product['region']): product['id'] for product in products} def get_pricing(self): """Make a mapping of pricing values string to pricing objects.""" table_name = AWSCostEntryPricing with schema_context(self.schema): pricing = self._get_db_obj_query(table_name).all() return { '{term}-{unit}'.format(term=p.term, unit=p.unit): p.id for p in pricing } def get_reservations(self): """Make a mapping of reservation ARN to reservation objects.""" table_name = AWSCostEntryReservation with schema_context(self.schema): columns = ['id', 'reservation_arn'] reservs = self._get_db_obj_query(table_name, columns=columns).all() return {res['reservation_arn']: res['id'] for res in reservs} def populate_line_item_daily_table(self, start_date, end_date, bill_ids): """Populate the daily aggregate of line items table. Args: start_date (datetime.date) The date to start populating the table. end_date (datetime.date) The date to end on. bill_ids (list) Returns (None) """ table_name = AWS_CUR_TABLE_MAP['line_item_daily'] daily_sql = pkgutil.get_data( 'masu.database', 'sql/reporting_awscostentrylineitem_daily.sql') daily_sql = daily_sql.decode('utf-8') daily_sql_params = { 'uuid': str(uuid.uuid4()).replace('-', '_'), 'start_date': start_date, 'end_date': end_date, 'bill_ids': bill_ids, 'schema': self.schema } daily_sql, daily_sql_params = self.jinja_sql.prepare_query( daily_sql, daily_sql_params) self._commit_and_vacuum(table_name, daily_sql, start_date, end_date, bind_params=list(daily_sql_params)) # pylint: disable=invalid-name def populate_line_item_daily_summary_table(self, start_date, end_date, bill_ids): """Populate the daily aggregated summary of line items table. Args: start_date (datetime.date) The date to start populating the table. end_date (datetime.date) The date to end on. Returns (None) """ table_name = AWS_CUR_TABLE_MAP['line_item_daily_summary'] summary_sql = pkgutil.get_data( 'masu.database', 'sql/reporting_awscostentrylineitem_daily_summary.sql') summary_sql = summary_sql.decode('utf-8') summary_sql_params = { 'uuid': str(uuid.uuid4()).replace('-', '_'), 'start_date': start_date, 'end_date': end_date, 'bill_ids': bill_ids, 'schema': self.schema } summary_sql, summary_sql_params = self.jinja_sql.prepare_query( summary_sql, summary_sql_params) self._commit_and_vacuum(table_name, summary_sql, start_date, end_date, bind_params=list(summary_sql_params)) def mark_bill_as_finalized(self, bill_id): """Mark a bill in the database as finalized.""" table_name = AWSCostEntryBill with schema_context(self.schema): bill = self._get_db_obj_query(table_name)\ .get(id=bill_id) if bill.finalized_datetime is None: bill.finalized_datetime = self.date_accessor.today_with_timezone( 'UTC') bill.save() # pylint: disable=invalid-name def populate_tags_summary_table(self): """Populate the line item aggregated totals data table.""" table_name = AWS_CUR_TABLE_MAP['tags_summary'] agg_sql = pkgutil.get_data('masu.database', f'sql/reporting_awstags_summary.sql') agg_sql = agg_sql.decode('utf-8') agg_sql_params = {'schema': self.schema} agg_sql, agg_sql_params = self.jinja_sql.prepare_query( agg_sql, agg_sql_params) self._commit_and_vacuum(table_name, agg_sql, bind_params=list(agg_sql_params)) def populate_ocp_on_aws_cost_daily_summary(self, start_date, end_date, cluster_id, bill_ids): """Populate the daily cost aggregated summary for OCP on AWS. Args: start_date (datetime.date) The date to start populating the table. end_date (datetime.date) The date to end on. Returns (None) """ table_name = AWS_CUR_TABLE_MAP['ocp_on_aws_daily_summary'] summary_sql = pkgutil.get_data( 'masu.database', 'sql/reporting_ocpawscostlineitem_daily_summary.sql') summary_sql = summary_sql.decode('utf-8') summary_sql_params = { 'uuid': str(uuid.uuid4()).replace('-', '_'), 'start_date': start_date, 'end_date': end_date, 'bill_ids': bill_ids, 'cluster_id': cluster_id, 'schema': self.schema } summary_sql, summary_sql_params = self.jinja_sql.prepare_query( summary_sql, summary_sql_params) self._commit_and_vacuum(table_name, summary_sql, start_date, end_date, bind_params=list(summary_sql_params)) def populate_markup_cost(self, markup, bill_ids=None): """Set markup costs in the database.""" with schema_context(self.schema): if bill_ids: for bill_id in bill_ids: AWSCostEntryLineItemDailySummary.objects.\ filter(cost_entry_bill_id=bill_id).\ update(markup_cost=(F('unblended_cost') * markup)) else: AWSCostEntryLineItemDailySummary.objects.\ update(markup_cost=(F('unblended_cost') * markup)) def populate_ocp_on_aws_markup_cost(self, markup, bill_ids=None): """Set markup costs in the database.""" with schema_context(self.schema): if bill_ids: for bill_id in bill_ids: OCPAWSCostLineItemDailySummary.objects.\ filter(cost_entry_bill_id=bill_id).\ update(markup_cost=(F('unblended_cost') * markup)) OCPAWSCostLineItemProjectDailySummary.objects.\ filter(cost_entry_bill_id=bill_id).\ update(project_markup_cost=(F('unblended_cost') * markup)) else: OCPAWSCostLineItemDailySummary.objects.\ update(markup_cost=(F('unblended_cost') * markup)) OCPAWSCostLineItemProjectDailySummary.objects.\ update(project_markup_cost=(F('unblended_cost') * markup))