[Big Query] 샤드 테이블 전체 삭제하는 방법. DROP shard tables

빅쿼리에서 샤드 테이블 개념을 사용한다고 했다. 해서 날짜별로 스키마가 같은 테이블이 여러개 생성되게 되는데 그렇다면 테이블을 삭제해야하는 경우 어떻게 해야할까? 

 

* 샤드 테이블이란?  

https://cheershennah.tistory.com/296

[Big Query] 테이블 샤딩이란? Table Sharding.

 

SQL 에서 테이블 삭제시 아래와 같이 DROP 문을 사용하는데 

DROP TABLE [database_name.]table_name;

 

샤드 테이ㅏ블은 suffix 가  table_name_20240101  형태로 날짜로 모두 다르기 때문에 한번에 삭제가 불가하다. 이론상으로 SELECT에서 사용할 수 있는 방법처럼 아스타( table_name_ *) 를 사용할수 있다면 좋겠지만 DROP문에서 아스타를 사용하면 아래와 같이 에러가 발생함. 

 

 

 

그래서 구글링 및 삽질의 결과 2가지 방법을 사용해볼수 있다. 첫번째는 약간의 야매(?) 방법이고 2번째는 앞 포스팅에 활용한 방법과 같이 동적 SQL을 활용하는 거다. 테이블수가 그리 많지 않다면 유용한 야매식 방법. 

 

1. SELECT로 각 테이블의 DROP 문을 뽑아내서 복붙하여 전체 실행하는 방법. (feat.야매)

SELECT Concat('DROP TABLE ','`project_name.dataset.', table_id, '`;')
from `project_name.dataset.`.__TABLES__
WHERE
  table_id LIKE 'table_name_%'

 

위와같이 Concat을 활용하여 DROP TABLE 텍스트를 붙여주고, 삭제하고자 하는 테이블의 project_name.dataset 이름을 넣어서 실행해주면 테이블 suffix 값을 붙여서 SELECT 할 수 있다. 

 

결과) 

 

이제 해당 값들을 쿼리창에 복붙해서 전체 실행해주면 순서대로 table이 DROP 된다. 

 

실행결과

다음과 같이 쿼리를 순차실행하여 삭제가(야매로) 완료된다.

 

 

BUT !!!!

뭔가 찝찝하고 맘에 들지않음......... 솔직히 너무 짜치잖아...ㅠㅠ 

스택오버플로에서도 이방법을 많이 추천하던데 이게 날짜가 적으면 그나마 편한 방법이 될 수 있겠지만 만약에 날짜기간이 5년치 데이터라고 해보자.... 쿼리를 365 * 5 줄을 적어야 하는 것이다. ;;;;;; 

 

 

그래서 finally  찾아낸 방법. 동적 SQL 활용하기! 

2. 동적 SQL 을 활용하여 간결한 쿼리로 장기간 샤드 테이블 DROP 하는 방법!! 

 /* DROP Sharded tables  */

DECLARE start_date DATE default '2023-01-01'; -- 시작일자 
DECLARE end_date DATE default '2028-01-01'; -- 마지막일자
DECLARE table_date STRING;

SET table_date = FORMAT_DATE('%Y%m%d', start_date); -- YYYYMMDD

LOOP

  IF start_date > end_date THEN 

  BREAK; 
  END IF;

  EXECUTE IMMEDIATE FORMAT(
    
    """
      DROP TABLE `project_name.dataset.table_name_%s`; 
    
    """
    ,table_date
  );

  SET start_date = DATE_ADD(start_date , INTERVAL 1 DAY);
  SET table_date = FORMAT_DATE('%Y%m%d', start_date);

END LOOP;

 

 위의 쿼리를 활용하면 기간이 얼마나 되든 시작일자 / 마지막 일자만 설정해주면 1번의 결과와 똑같이 반복문을 돌면서 DROP문을 실행한다. 테이블 전체가 아닌 특정 기간만 삭제하기에도 용이하다. 

 

 

참고에러) ❌ Dataset Location Error❌

 

참고로 동적 쿼리에서 데이터셋 로케이션 에러가 발생하는 경우가 있다. (US가 아닌 경우)

일반 쿼리에서는 자동으로 location을 인식하는데 동적 SQL 에서는 text 라 인식이 안되어 default 값인 US으로 자동 셋팅이 되는 듯하다. 

 

이 경우에는 MORE > 쿼리 설정> 고급설정 에서 로케이션 자동 인식을 해제하고 직접 지정해주면 된다.

 

 

누군가에겐 더 복잡한 방법으로 보일수 있지만 고작 일주일치 테이블은 1번이 편할지라도 기간이 년단위로 길어질수록 훨씬 간결한 쿼리가 될 수 있다. 앞서 CREATE 샤드 테이블 만들때 작성해놨던 쿼리라 어렵지 않게 짤 수 있었다. Nice! 

 

* 참고 포스팅) 

https://cheershennah.tistory.com/297

[Bigquery] Shard 테이블 날짜별 마이그레이션 (feat. 빅쿼리 절차적 언어_동적 SQL 만들기)

 

 

 

+ 여담) 

1번 방법이 별로라고 생각하는건 나만 느낀 건 아닌듯... 스택오버플로에도 정확히 똑같은 니즈의 질문이 올라왔는데 이렇다할 해결법이 없었음.. 항상 눈팅만 해왔는데 회원가입해서 첨으로 내소스를 댓글로 남겨보았따! 기념박제ㅋ

 

 

https://stackoverflow.com/questions/72728149/how-can-i-drop-many-bigquery-tables-without-having-to-delete-each-one-individual

How can I drop many BigQuery tables without having to delete each one individually?