def __get_report_queries(extract_params): extract_type = extract_params[Extract.REPORT_TYPE] queries = {} if extract_type == ExtractType.studentRegistrationStatistics: academic_year_query = student_reg_statistics.get_academic_year_query( extract_params[TaskConstants.ACADEMIC_YEAR], extract_params[TaskConstants.STATE_CODE]) match_id_query = student_reg_statistics.get_match_id_query( extract_params[TaskConstants.ACADEMIC_YEAR], extract_params[TaskConstants.STATE_CODE]) queries = { QueryType.QUERY: compile_query_to_sql_text(academic_year_query), QueryType.MATCH_ID_QUERY: compile_query_to_sql_text(match_id_query) } elif extract_type == ExtractType.studentAssessmentCompletion: registered_query = student_reg_completion.get_academic_year_query( extract_params[TaskConstants.ACADEMIC_YEAR], extract_params[TaskConstants.STATE_CODE]) asmt_query = student_reg_completion.get_assessment_query( extract_params[TaskConstants.ACADEMIC_YEAR], extract_params[TaskConstants.STATE_CODE]) queries = { QueryType.QUERY: compile_query_to_sql_text(registered_query), QueryType.ASMT_OUTCOME_QUERY: compile_query_to_sql_text(asmt_query) } return queries
def test_compile_query_to_sql_text(self): with UnittestEdcoreDBConnection() as connection: fact = connection.get_table('fact_asmt_outcome_vw') query = select([fact.c.state_code], from_obj=[fact]) query = query.where(fact.c.state_code == 'UT') str_query = compile_query_to_sql_text(query) self.assertIn("fact_asmt_outcome_vw.state_code = 'UT'", str_query)
def explode_data_to_dim_table(conf, source_table, target_table, column_mapping, column_types): ''' Main function to move data from source table to target tables. Source table can be INT_SBAC_ASMT, and INT_SBAC_ASMT_OUTCOME. Target table can be any dim tables in star schema. @param conf: one dictionary which has database settings, and guid_batch @param source_table: name of the source table where the data comes from @param target_table: name of the target table where the data should be moved to @param column_mapping: list of tuple of: column_name_in_target, column_name_in_source @param column_types: data types of all columns in one target table ''' # create database connection to target with get_target_connection(conf[mk.TENANT_NAME], conf[mk.GUID_BATCH]) as conn: # create insertion query # TODO: find out if the affected rows, time can be returned, so that the returned info can be put in the log # send only data that is needed to be inserted (such insert, update) to dimenstion table query = create_insert_query(conf, source_table, target_table, column_mapping, column_types, True, 'C' if source_table in op_table_conf else None) logger.info(compile_query_to_sql_text(query)) # execute the query affected_rows = execute_udl_queries(conn, [query], 'Exception -- exploding data from integration to target ' + '{target_table}'.format(target_table=target_table), 'move_to_target', 'explode_data_to_dim_table') return affected_rows
def explode_data_to_dim_table(conf, source_table, target_table, column_mapping, column_types): ''' Main function to move data from source table to target tables. Source table can be INT_SBAC_ASMT, and INT_SBAC_ASMT_OUTCOME. Target table can be any dim tables in star schema. @param conf: one dictionary which has database settings, and guid_batch @param source_table: name of the source table where the data comes from @param target_table: name of the target table where the data should be moved to @param column_mapping: list of tuple of: column_name_in_target, column_name_in_source @param column_types: data types of all columns in one target table ''' # create database connection to target with get_target_connection(conf[mk.TENANT_NAME], conf[mk.GUID_BATCH]) as conn: # create insertion query # TODO: find out if the affected rows, time can be returned, so that the returned info can be put in the log # send only data that is needed to be inserted (such insert, update) to dimenstion table query = create_insert_query( conf, source_table, target_table, column_mapping, column_types, True, 'C' if source_table in op_table_conf else None) logger.info(compile_query_to_sql_text(query)) # execute the query affected_rows = execute_udl_queries( conn, [query], 'Exception -- exploding data from integration to target ' + '{target_table}'.format(target_table=target_table), 'move_to_target', 'explode_data_to_dim_table') return affected_rows
def test_get_extract_assessment_query_compiled(self): params = {'stateCode': 'NC', 'asmtYear': '2015', 'asmtType': 'SUMMATIVE', 'asmtSubject': 'Math', 'extractType': 'studentAssessment'} query = compile_query_to_sql_text(get_extract_assessment_query(params)) self.assertIsNotNone(query) self.assertIsInstance(query, str) self.assertIn('SUMMATIVE', query)
def test_get_extract_items_query_compiled(self): params = {'stateCode': 'NC', 'asmtYear': '2015', 'asmtType': 'SUMMATIVE', 'asmtSubject': 'Math', 'asmtGrade': '03', 'extractType': 'itemLevel'} query = compile_query_to_sql_text(get_extract_assessment_item_and_raw_query(params, ExtractType.itemLevel)) self.assertIsNotNone(query) self.assertIsInstance(query, str) self.assertIn('SUMMATIVE', query)
def test_get_extract_assessment_query_compiled(self): params = { 'stateCode': 'NC', 'asmtYear': '2015', 'asmtType': 'SUMMATIVE', 'asmtSubject': 'Math', 'extractType': 'studentAssessment' } query = compile_query_to_sql_text(get_extract_assessment_query(params)) self.assertIsNotNone(query) self.assertIsInstance(query, str) self.assertIn('SUMMATIVE', query)
def test_get_extract_items_query_compiled(self): params = { 'stateCode': 'NC', 'asmtYear': '2015', 'asmtType': 'SUMMATIVE', 'asmtSubject': 'Math', 'asmtGrade': '03', 'extractType': 'itemLevel' } query = compile_query_to_sql_text( get_extract_assessment_item_and_raw_query(params, ExtractType.itemLevel)) self.assertIsNotNone(query) self.assertIsInstance(query, str) self.assertIn('SUMMATIVE', query)
def _create_new_task(request_id, user, tenant, params, query, extract_type=None, asmt_metadata=False, is_tenant_level=False, extract_file_path=None): ''' TODO comment ''' task = {} task[TaskConstants.TASK_TASK_ID] = create_new_entry(user, request_id, params) task[TaskConstants.TASK_QUERIES] = {QueryType.QUERY: compile_query_to_sql_text(query)} if asmt_metadata: task[TaskConstants.TASK_FILE_NAME] = get_asmt_metadata_file_path(params, tenant, request_id) task[TaskConstants.EXTRACTION_DATA_TYPE] = ExtractionDataType.QUERY_JSON else: if extract_file_path is not None: task[TaskConstants.TASK_FILE_NAME] = extract_file_path(params, tenant, request_id, is_tenant_level=is_tenant_level) if extract_type and extract_type is ExtractType.itemLevel: task[TaskConstants.EXTRACTION_DATA_TYPE] = ExtractionDataType.QUERY_ITEMS_CSV elif extract_type and extract_type is ExtractType.rawData: task[TaskConstants.EXTRACTION_DATA_TYPE] = ExtractionDataType.QUERY_RAW_XML else: task[TaskConstants.EXTRACTION_DATA_TYPE] = ExtractionDataType.QUERY_CSV return task
def _create_new_task(request_id, user, tenant, params, query, extract_type=None, asmt_metadata=False, is_tenant_level=False, extract_file_path=None): ''' TODO comment ''' task = {} task[TaskConstants.TASK_TASK_ID] = create_new_entry( user, request_id, params) task[TaskConstants.TASK_QUERIES] = { QueryType.QUERY: compile_query_to_sql_text(query) } if asmt_metadata: task[TaskConstants.TASK_FILE_NAME] = get_asmt_metadata_file_path( params, tenant, request_id) task[ TaskConstants.EXTRACTION_DATA_TYPE] = ExtractionDataType.QUERY_JSON else: if extract_file_path is not None: task[TaskConstants.TASK_FILE_NAME] = extract_file_path( params, tenant, request_id, is_tenant_level=is_tenant_level) if extract_type and extract_type is ExtractType.itemLevel: task[TaskConstants. EXTRACTION_DATA_TYPE] = ExtractionDataType.QUERY_ITEMS_CSV elif extract_type and extract_type is ExtractType.rawData: task[TaskConstants. EXTRACTION_DATA_TYPE] = ExtractionDataType.QUERY_RAW_XML else: task[TaskConstants. EXTRACTION_DATA_TYPE] = ExtractionDataType.QUERY_CSV return task
def test_get_schema_check_query(self): expected_query = "SELECT schema_name FROM information_schema.schemata WHERE schema_name = '90901b70-ddaa-11e2-a95d-68a86d3c2f82'" query = _get_schema_check_query('90901b70-ddaa-11e2-a95d-68a86d3c2f82') query_string = str(compile_query_to_sql_text(query)).replace("\n", "") self.assertEquals(query_string, expected_query)