APEX日里面每个表的查询次数统计

1.背景

通过APEX的报表查询情况，停止不再被查询的作业

2.思路

方法一：

1.apex自带的日志视图apex_workspace_activity_log里面只有二级菜单栏的统计次数

2.解析APX的.sql文件，找出来表和每个菜单栏对应的关系。或者使用apex的视图APEX_APPLICATION_PAGE_REGIONS里面二级菜单栏和表的对应关系

方法二：

1.查询oracle的查询历史记录，然后解析SQL，把表按照每次查询的pv统计。

2.解析APX的.sql文件，找出来表和每个菜单栏对应的关系。

3.实现步骤

方法一：

1.查询次数统计

-- 每一个页的查询次数
select 
application_id
    , page_id
    , page_name
    , count(1) as view_pv
from oracle_apex_workspace_activity_log
where dt = ${bizdate} 
and application_id in (101,125)
and apex_user != 'nobody'
and to_char(VIEW_DATE,'yyyymmdd') = ${bizdate}
group by application_id
    , page_id
    , page_name

   2.找出表和二级菜单栏的对应关系

import re
import pymysql

def read():
    # 读取文件
    f = open("f125.sql",encoding='utf-8')
    lines = f.read()
    prompt = lines.split('prompt')
    regex = r"\w+(?=@)"
    regex_page = r"(?<=application/pages/page_)\d+"
    page=''
    table=''
    applicationid='125'
    # 数据库不给连接，只好把语句打印出来，放在数据库里面执行了
    # pymysql = MysqlClient("", "", "", "")

    for pro in prompt:
        if( '@' in pro ):
            matches = re.finditer(regex_page, pro, re.MULTILINE)
            for matchNum, match in enumerate(matches, start=1):
                page = match.group()
                matches = re.finditer(regex, pro, re.MULTILINE)
                for matchNum, match in enumerate(matches, start=1):
                    table = match.group()
                    # print(page + "====" + table)
                    sql = "insert INTO oracle_table_page values ('"+applicationid+"','"+ page[2:] +"','"+ table+"');"
                    print(sql)
                    # pymysql.update(sql)

    f.close()

class MysqlClient:
    def __init__(self, db, host, user, password):
        self.conn = pymysql.connect(host=host, user=user, password=password, db=db, charset="utf8mb4")
        self.cursor = self.conn.cursor()

    def select(self, sql, args):
        self.cursor.execute(query=sql, args=args)
        return self.cursor.fetchall()

    # 删除、更新、插入
    def update(self, sql):
        temp = self.cursor.execute(sql)
        self.conn.commit()
        return temp

if __name__ == '__main__':
    read()

3.插入数据库 

因为可能解析出来多行同一个表对应的页面名，所以可以把表名，应用名，页面名做成联合组件，然后插入的时候，如果已经有就更新，没有就新增。

1.mysql可以直接用replace into 

REPLACE INTO test.oracle_table_page (page,table_name) values ('00014','APEX_GOODS_UP_KEEP_D');

 2.oracle没有replace的语法，可以使用merge into。或者在表上面建一个触发器实现

merge into 的方法需要再建一个没有主键的临时表

merge into tablea a        ----要更新或者操作的表
using tablec c ----源表 using (select * from tablec ) c
on a.id=c.id--匹配条件
when matched then set a.price=c.price  --当匹配时进行更新操作
when not matched then --不匹配进行插入操作
insert values values(c.id,c.price)

触发器

CREATE OR REPLACE EDITIONABLE TRIGGER "YVAN"."TAR_oracle_table_page2" BEFORE INSERT ON "YVAN".ORACLE_TABLE_PAGE   FOR EACH ROW 
BEGIN  
DELETE FROM "YVAN".ORACLE_TABLE_PAGE WHERE TABLE_NAME = :NEW.TABLE_NAME and APPLICATION_ID =:NEW.APPLICATION_ID and PAGE = :NEW.PAGE  ;
END ;

ALTER TRIGGER "YVAN"."TAR_oracle_table_page2" ENABLE;

insert INTO oracle_table_page values ('125','257','apex_market_newold_index');

4.同步数据到大数据，然后建立作业

1.同步作业省略

2.调度作业

select a.APPLICATION_ID
    , d.page_parent_name
    , a.page_id
    , a.PAGE_NAME
    , a.TABLE_NAME
    , b.view_pv
    , c.cdm_target_table
    , c.dgc_job_name 
    , c.workspace
    , c.dli_table
    , c.cdm_job_name
    , c.last_execution_time
    , c.cdm_cluster_name 
    , ${bizdate} as dt
from (
    -- 表和page的对应关系
    select
    a.APPLICATION_ID
    ,cast(a.page AS INT)  as page_id
    ,lcase(a.TABLE_NAME) as TABLE_NAME
    ,b.PAGE_NAME
    from ORACLE_TABLE_PAGE a 
    left join (
        select distinct  APPLICATION_ID
            , PAGE_ID
            , PAGE_NAME
         from APEX_APPLICATION_PAGE_REGIONS
         where APPLICATION_ID in (101,125)
    )b
    on a.APPLICATION_ID=b.APPLICATION_ID
    and cast(a.page AS INT) = b.PAGE_ID
)a
left join (
    -- 每个page的访问次数
    select 
    application_id
        , page_id
    	, page_name
    	, count(1) as view_pv
    from oracle_apex_workspace_activity_log
    where dt = ${bizdate} 
    and application_id in (101,125)
    and apex_user != 'nobody'
    and to_char(VIEW_DATE,'yyyymmdd') = ${bizdate}
    group by application_id
        , page_id
    	, page_name
)b
on a.application_id = b.APPLICATION_ID
and a.page_id = b.page_id
left join (
    select cdm_target_table
            , dgc_job_name 
            , workspace
            , dli_table
            , cdm_job_name
            , last_execution_time
            ,   cdm_cluster_name 
            
    from (
        -- 表和作业对应关系
        select 
            lcase(cdm_target_table) as cdm_target_table
            , dgc_job_name 
            , workspace
            , lcase(dli_table) as dli_table
            , cdm_job_name
            , last_execution_time 
            , cdm_cluster_name
            , row_number() over (PARTITION BY lcase(cdm_target_table) order by last_execution_time desc ) as rank_num
        from yishou_data.dwd_hw_cdm_job_info 
        where 
        cdm_export_conn in (
        "apex_oracle_slave12_6"
        ,"oracle_master_16"
        ,"apex_oracle_slave_52"
        ,"oracle52"
        ,"oracle_slave_52"
        ,"oracle_slave12c_6"
        )
    )
    WHERE rank_num = 1
    
)c
on c.cdm_target_table = a.TABLE_NAME
left join (
    -- page和一级菜单栏对应的关系
    select page_name
        , page
        , page_parent_name
        , application
    from oracle_tabular_view   
    where page_parent_name <> '-'
)d
on a.application_id = d.application
and a.page_id = d.page
;

方法二：

1.解析Oracle的SQL

-- 截取函数
SELECT distinct name
,dbms_lob.substr(replace(REGEXP_SUBSTR(DATA_SOURCE, '\w+@', 1, LEVEL, 'i'),'@','')) AS STR
FROM 
(
	select name, DATA_SOURCE from APEX_190200.WWV_FLOW_JET_CHART_SERIES
	where FLOW_ID in (101,125) -- and page_id = '58'
)
CONNECT BY LEVEL < REGEXP_COUNT(DATA_SOURCE, '\w+@') + 1
;