[Bigquery] 프로시저 관련 꿀쿼리 모음 (SP 실행 기록, 대상 테이블이 실행되는 프로시저, 스케줄 찾기, 특정 쿼리가 들어간 프로시저 찾기)

하..쓸 글이 엄청 많은데 정리할 것도 많고.. 개인적인 큰 일이 줄줄이 있어 손도 못대고 있다. 

가볍게 쓸 수 있는 소스들 부터 차근차근 정리하는중 

 

자자 그럼 본론으로! 

프로시저를 생성할 때 마다 문서 정리를 기깔나게 해놓지만, 내가 생성한 프로시저가 아닌 경우에 수~많은 테이블과 프로시저 중에서 이 테이블이 어떤 프로시저에서 생성되고 있는지, 스케줄은 어디서 돌고있는지 찾기가 힘들다. 

 

이를 쿼리로 조회하는 방법이 있으니..! 

1.SP 실행 기록 조회 

b_date 기준 실행된 SP 기록을 조회할 수 있는 쿼리이다. 

SELECT *
FROM `project_id.dataset_name.sp_run_info2` ## 본인의 프로젝트.데이터셋으로 변경
WHERE 1=1
  AND b_date = DATE_ADD(CURRENT_DATE('Asia/Seoul'), INTERVAL 0 DAY) ##언제 돌아갔는지
  AND sp_name IN ('SP_name') ##찾고싶은 SP 이름 기재
ORDER BY sp_name, step_start DESC
;

 

 

2.해당 프로시저가 등록된 스케줄 찾기 

근데, 내가 어떤 프로시저를 어떤 스케줄에 올린지 헷갈릴 때가 분명 있다. 그럴땐 아래 쿼리를 사용해주면 된다. 

SELECT routine_catalog, routine_schema, routine_name, routine_definition
FROM `project_id.region-us`.INFORMATION_SCHEMA.ROUTINES ##project_id만 변경해주고, region에 따라 바꿔주면 된다. 보통 us
WHERE specific_schema = 'dataset_name' ## 데이터셋이름은 여기서 지정해준다.
  AND routine_type = 'PROCEDURE' ## 프로시저만
  AND ddl LIKE '%프로시저명%' ## 찾을 대상

 

3.대상 테이블이 실행되는 프로시저 찾기 

이 테이블이 어떤 프로시저에서 생성됐는지 확인하고 싶을 때 사용하는 쿼리 ! 

SELECT routine_catalog, routine_schema, routine_name, routine_definition
FROM `com2us-bigquery.region-us`.INFORMATION_SCHEMA.ROUTINES
WHERE specific_schema = 'dataset_name' ## 데이터셋이름 적어주기
  AND routine_type = 'PROCEDURE'  
  AND ddl LIKE '%table_name%' ## 테이블 이름 검색

 

4.특정 쿼리가 사용된 프로시저 찾기 

개인적으로 가장 많이 쓰는 쿼리. 특정 테이블이 들어간 프로시저를 찾기 위해 많이 사용하는 편이다. 

SELECT
  routine_name,
FROM
  `your_project_id.your_dataset.INFORMATION_SCHEMA.ROUTINES`
WHERE
  routine_type = 'PROCEDURE'
  AND routine_definition LIKE '%특정쿼리%'; #검색은 여기서 써줘야하며 %를 꼭 사용해줘야한다.

 

 

외우기 보다는, 즐겨찾기 해놓거나 스티커 메모같은데 복사해두면 정말 유용하게 쓸 수 있다!