130 lines
6.9 KiB
Python
130 lines
6.9 KiB
Python
metadata_table = "DW_RAR.NH_METADATA_INVENTORY"
|
|
|
|
def get_query_metadata(metadata_table, owner, table_name):
|
|
query_metadata = (
|
|
"WITH metaDF AS ( "
|
|
"SELECT owner, table_name, column_id, column_name, data_type, data_precision, data_scale, "
|
|
"CASE WHEN data_precision IS NULL AND data_scale IS NULL THEN NULL "
|
|
"WHEN data_precision IS NOT NULL AND data_scale IS NULL THEN data_precision "
|
|
"WHEN CAST(data_precision AS INT) >= CAST(data_scale AS INT) AND CAST(data_scale AS INT) >= 0 THEN data_precision "
|
|
"WHEN CAST(data_precision AS INT) < CAST(data_scale AS INT) AND CAST(data_scale AS INT) <= 38 AND CAST(data_scale AS INT) > 0 THEN data_scale "
|
|
"WHEN CAST(data_precision AS INT) < CAST(data_scale AS INT) AND (CAST(data_scale AS INT) > 38 OR CAST(data_scale AS INT) < 0) THEN NULL "
|
|
"ELSE NULL END AS data_precision_hive, "
|
|
"CASE WHEN data_precision IS NULL AND data_scale IS NULL THEN NULL "
|
|
"WHEN data_precision IS NOT NULL AND data_scale IS NULL THEN CAST(0 AS INT) "
|
|
"WHEN CAST(data_precision AS INT) >= CAST(data_scale AS INT) AND CAST(data_scale AS INT) >= 0 THEN data_scale "
|
|
"WHEN CAST(data_precision AS INT) < CAST(data_scale AS INT) AND CAST(data_scale AS INT) <= 38 AND CAST(data_scale AS INT) > 0 THEN data_scale "
|
|
"WHEN CAST(data_precision AS INT) < CAST(data_scale AS INT) AND (CAST(data_scale AS INT) > 38 OR CAST(data_scale AS INT) < 0) THEN NULL "
|
|
"ELSE NULL END AS data_scale_hive, "
|
|
"CASE WHEN data_type LIKE '%NUMBER%' AND data_precision IS NULL AND data_scale IS NULL THEN 'String' "
|
|
"WHEN data_type LIKE '%NUMBER%' AND data_precision IS NOT NULL AND data_scale IS NULL THEN 'Decimal' "
|
|
"WHEN data_type LIKE '%NUMBER%' AND CAST(data_precision AS INT) >= CAST(data_scale AS INT) AND CAST(data_scale AS INT) >= 0 THEN 'Decimal' "
|
|
"WHEN data_type LIKE '%NUMBER%' AND CAST(data_precision AS INT) < CAST(data_scale AS INT) AND CAST(data_scale AS INT) <= 38 AND CAST(data_scale AS INT) > 0 THEN 'Decimal' "
|
|
"WHEN data_type LIKE '%NUMBER%' AND CAST(data_precision AS INT) < CAST(data_scale AS INT) AND (CAST(data_scale AS INT) > 38 OR CAST(data_scale AS INT) < 0) THEN 'String' "
|
|
"WHEN data_type LIKE '%CHAR%' THEN 'String' "
|
|
"WHEN data_type LIKE '%VARCHAR2%' THEN 'String' "
|
|
"WHEN data_type LIKE '%TIMESTAMP%' THEN 'String' "
|
|
"WHEN data_type LIKE '%DATE%' THEN 'String' "
|
|
"ELSE 'String' END AS data_type_hive, "
|
|
"REGEXP_REPLACE(data_description, '''', '\\''') AS data_description "
|
|
"FROM {0} "
|
|
"WHERE lower(owner||'.'||table_name) = lower('{1}'||'.'||'{2}') "
|
|
"AND a_valid_to > sysdate) "
|
|
"SELECT owner, table_name, column_id, column_name, data_type, data_precision, data_scale, "
|
|
"data_precision_hive, data_scale_hive, data_type_hive, "
|
|
"CASE WHEN data_type_hive = 'Decimal' THEN 'Decimal(' || COALESCE(CAST(data_precision_hive AS VARCHAR2(30)), '') || ',' || COALESCE(CAST(data_scale_hive AS VARCHAR2(30)), '') || ')' "
|
|
"ELSE data_type_hive END AS data_type_string, data_description "
|
|
"FROM metaDF "
|
|
"ORDER BY CAST(column_id AS INT) "
|
|
).format(metadata_table, owner, table_name)
|
|
|
|
return query_metadata
|
|
|
|
def get_query_metadata_access_type1(metadata_table):
|
|
query_metadata_access_type1 = (
|
|
"SELECT owner, table_name, list_of_sources as SOURCE, rar3_type_of_access "
|
|
"FROM {0} "
|
|
"WHERE a_valid_to > sysdate "
|
|
"AND rar3_type_of_access = '1' "
|
|
"AND list_of_sources NOT IN 'RAR' "
|
|
"AND lower(owner||'.'||table_name) = lower($$$1) "
|
|
"UNION "
|
|
"SELECT owner, table_name, list_of_sources as SOURCE, rar3_type_of_access "
|
|
"FROM {0} "
|
|
"WHERE a_valid_to > sysdate "
|
|
"AND rar3_type_of_access = '1' "
|
|
"AND owner = 'CORR_REF_MAIN' "
|
|
"AND lower(owner||'.'||table_name) = lower($$$1) "
|
|
).format(metadata_table)
|
|
|
|
return query_metadata_access_type1
|
|
|
|
def get_query_metadata_access_type2a(metadata_table):
|
|
query_metadata_access_type2a = (
|
|
"WITH rar_columns AS ( "
|
|
"SELECT owner, table_name, column_name, source, rar3_type_of_access "
|
|
"FROM ( "
|
|
"SELECT owner, table_name, column_name, rar3_type_of_access, list_of_sources, "
|
|
"tms, c2d_ea, c2d_ela, c2d_mpec, c2d_uc, ceph, lm, csdb_reference, "
|
|
"csdb_ratings, fxcd, mdp_bbg, mdp_reu, riad_cl, riad_ou, sdw_estr, sdw_fx, "
|
|
"top, rar, rtm, led, mdp_cma "
|
|
"FROM {0} "
|
|
"WHERE a_valid_to > sysdate "
|
|
"AND rar3_type_of_access = '2a' "
|
|
"AND lower(owner || '.' || table_name) = lower($$$1) "
|
|
"AND list_of_sources NOT LIKE '%,%' "
|
|
"AND upper(column_name) NOT IN ('DATABASE') "
|
|
") a "
|
|
"UNPIVOT ( "
|
|
"val FOR (source) IN ( "
|
|
"tms AS 'TMS', c2d_ea AS 'C2D_EA', c2d_ela AS 'C2D_ELA', c2d_mpec AS 'C2D_MPEC', "
|
|
"c2d_uc AS 'C2D_UC', ceph AS 'CEPH', lm AS 'LM', csdb_reference AS 'CSDB_REFERENCE', "
|
|
"csdb_ratings AS 'CSDB_RATINGS', fxcd AS 'FXCD', mdp_bbg AS 'MDP_BBG', mdp_reu AS 'MDP_REU', "
|
|
"riad_cl AS 'RIAD_CL', riad_ou AS 'RIAD_OU', sdw_estr AS 'SDW_ESTR', sdw_fx AS 'SDW_FX', "
|
|
"top AS 'TOP', rar AS 'RAR', rtm AS 'RTM', led AS 'LED', mdp_cma AS 'MDP_CMA') "
|
|
") "
|
|
"ORDER BY owner, table_name, column_name "
|
|
"), "
|
|
"dummy_entry AS ( "
|
|
"SELECT owner, table_name, 'RAR' as SOURCE, rar3_type_of_access "
|
|
"FROM rar_columns "
|
|
"FETCH FIRST ROW ONLY "
|
|
"), "
|
|
"disc_tec_fields AS ( "
|
|
"SELECT owner, table_name, 'TEC_INGESTION_DATE' AS column_name, source, rar3_type_of_access "
|
|
"FROM dummy_entry "
|
|
"UNION "
|
|
"( "
|
|
"SELECT owner, table_name, 'TEC_EXECUTION_DATE' AS column_name, source, rar3_type_of_access "
|
|
"FROM dummy_entry "
|
|
") "
|
|
"UNION "
|
|
"( "
|
|
"SELECT owner, table_name, 'TEC_RUN_ID' AS column_name, source, rar3_type_of_access "
|
|
"FROM dummy_entry "
|
|
") "
|
|
") "
|
|
"SELECT owner, table_name, column_name, source, rar3_type_of_access "
|
|
"FROM disc_tec_fields "
|
|
"UNION "
|
|
"SELECT owner, table_name, column_name, source, rar3_type_of_access "
|
|
"FROM rar_columns "
|
|
).format(metadata_table)
|
|
|
|
return query_metadata_access_type2a
|
|
|
|
def get_query_igam_roles(igam_table,service):
|
|
if service.lower() == 'rar':
|
|
service_entitlement='mrds'
|
|
elif service.lower()=='mopdb':
|
|
service_entitlement='mrds'
|
|
else:
|
|
service_entitlement='mrds'
|
|
query_igam_roles = (
|
|
"SELECT MRDS_subsource_id as Datasource, "
|
|
"MRDS_subsource_id as subsource_id, "
|
|
"MRDS_entitlement as IGAM_Entitlement, "
|
|
"environment "
|
|
"FROM {0} where lower(environment) = lower($$$1) and SERVICE_NAME='{1}'").format(igam_table,service.upper())
|
|
|
|
return query_igam_roles |