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))
