Databricks에서의 SQL 스크립팅 소개, 파트 2

SQL 스크립팅 발표 블로그 시리즈의 두 번째 부분에서, 우리는 첫 번째 부분에서 언급한 관리 작업, 즉 테이블의 모든 STRING 열에 대소문자 구분 없는 규칙을 적용하는 방법을 살펴볼 것입니다. 우리는 그 예제를 단계별로 살펴보고, 사용된 기능을 설명하고, 단일 테이블을 넘어 전체 스키마를 커버하도록 확장할 것입니다.

당신은 또한 따라갈 수 있습니다 이 노트북에서. 

스키마의 모든 테이블에서 모든 텍스트 필드의 정렬 순서를 변경합니다.

Databricks는 다양한 언어 인식, 대소문자 구분 없는, 악센트 구분 없는 콜레이션을 지원합니다. 이 기능은 새로운 테이블과 열에 쉽게 사용할 수 있습니다. 그러나 만약 upper() 또는 lower()를 모든 곳에서 사용하고 있는 기존 시스템이 있고, 원래의 대소문자 구분 없는 콜레이션과 관련된 성능 향상을 얻으면서 쿼리를 단순화하고 싶다면 어떻게 해야 할까요? 그것은 일부 프로그래밍을 필요로 할 것입니다; 이제 SQL에서 모두 수행할 수 있습니다. 

다음 테스트 스키마를 사용해봅시다: 

순서는 모든 대문자가 모든 소문자 앞에 오는 ASCII 코드 포인트에 기반합니다. upper()나 lower()를 추가하지 않고 이 문제를 해결할 수 있나요?

동적 SQL 문장과 변수 설정

첫 번째 단계는 테이블에게 새로 추가된 열에 대한 기본 정렬을 변경하도록 지시하는 것입니다. 노트북이 자동으로 감지하고 위젯을 추가하는 매개변수 마커로 로컬 변수를 공급할 수 있습니다. 또한 EXECUTE IMMEDIATE 를 사용하여 동적으로 구성된 ALTER TABLE 문을 실행할 수 있습니다.

모든 SQL 스크립트는 BEGIN .. END (복합) 문장으로 구성됩니다. 로컬 변수는 복합 문 내에서 먼저 정의되며, 그 다음에 로직이 이어집니다.

이것은 모두 선형 문장의 집합입니다. 지금까지, 복합문 없이 SQL 세션 변수로 이 모든 것을 작성할 수 있었습니다. 당신도 많은 성과를 이루지 못했습니다. 결국, 기존 열의 정렬 순서를 변경하려고 했습니다. 이를 위해서는 다음을 필요로 합니다:

• 테이블에서 모든 기존 문자열 열을 찾아봅니다
• 각 열의 정렬을 변경합니다

간단히 말해서, INFORMATION_SCHEMA.COLUMNS 테이블을 순회해야 합니다.

루프

SQL 스크립팅은 루프를 제어하는 네 가지 방법을 제공합니다.

1. LOOP … END LOOP;
   "영원히" 반복되는 루프입니다.
   이 루프는 예외가 발생하거나 명시적인 ITERATE 또는 LEAVE 명령어가 루프를 빠져나갈 때까지 계속됩니다.
   나중에 예외 처리에 대해 논의하고, 루프를 제어하는 방법을 설명하는 ITERATE와 LEAVE 문서를 참조하겠습니다.
2. WHILE predicate DO … END WHILE;
   이 루프는 조건식이 참으로 평가되거나 예외, ITERATE 또는 LEAVE에 의해 루프가 중단될 때까지 반복적으로 진입하게 됩니다.
3. REPEAT … UNTIL predicate END REPEAT;
   WHILE과 달리, 이 루프는 최소한 한 번은 실행되며, 예외, LEAVE, 또는 ITERATE 명령에 의해 루프가 중단되거나, 술어 표현식이 거짓으로 평가될 때까지 재실행됩니다.
4. FOR query DO …. END FOR;
   이 루프는 쿼리가 반환하는 행마다 한 번씩 실행되며, 예외, LEAVE 또는 ITERATE 문으로 조기에 빠져나가지 않는 한입니다.

이제, 우리의 collation 스크립트에 FOR 루프를 적용해봅시다. 이 쿼리는 테이블의 모든 문자열 열의 열 이름을 가져옵니다. 루프 본문은 차례로 각 열의 collation을 변경합니다:

테이블이 제대로 업데이트되었는지 확인해 봅시다:

지금까지는 잘 진행되고 있습니다. 우리의 코드는 기능적으로 완성되었지만, 파일 스킵의 이점을 얻기 위해 Delta에게 수정한 열을 분석하도록 알려야 합니다. 당신은 이것을 열마다 하고 싶지 않을 것입니다. 하지만 모든 것을 모아서 collation이 변경된 문자열 열이 실제로 있었을 경우에만 작업을 수행하십시오. 결정, 결정 …. 

조건부 로직

SQL 스크립팅은 SQL 문의 조건부 실행을 수행하는 세 가지 방법을 제공합니다.

1. If-then-else 로직. 이에 대한 문법은 다음과 같습니다:
   IF 조건 THEN … ELSEIF 조건 THEN … ELSE …. END IF;
   당연히, 선택적인 ELSEIF 블록은 얼마든지 있을 수 있고, 마지막 ELSE 또한 선택적입니다.
2. 간단한 CASE 문
   이 문장은 SQL 스크립팅 버전의 간단한 case 표현식입니다.
   CASE 표현식 WHEN 옵션 THEN … ELSE … END CASE;
   표현식의 단일 실행은 여러 옵션과 비교되며, 첫 번째 일치 항목이 어떤 SQL 문장 집합이 실행되어야 하는지 결정합니다. 일치하는 항목이 없으면 선택적인 ELSE 블록이 실행됩니다.
3. 검색된 CASE 문
   이 문장은 검색된 case 표현식의 SQL 스크립팅 버전입니다.
   CASE WHEN 조건 THEN …. ELSE … END CASE;
   THEN 블록은 참으로 평가되는 조건 중 첫 번째에 대해 실행됩니다. 일치하는 것이 없으면 선택적인 ELSE 블록이 실행됩니다.

우리의 collation 스크립트에는 간단한 IF THEN END IF 가 충분합니다. 또한 적용할 컬럼 집합을 수집하고 ANALYZE 를 적용하고 컬럼 목록을 생성하기 위해 고차 함수 마법이 필요합니다:

중첩

지금까지 작성한 것은 개별 테이블에 대해 작동합니다. 스키마의 모든 테이블에 작업을 수행하려면 어떻게 해야 할까요? SQL 스크립팅은 완전히 구성 가능합니다. 복합 문, 조건문, 루프를 다른 SQL 스크립팅 문 내에 중첩할 수 있습니다.

그러므로 여기서 할 일은 두 가지입니다:

1. 외부 FOR 루프를 추가하여 INFORMATION_SCHEMA.TABLES를 사용하여 스키마 내의 모든 테이블을 찾습니다. 이 과정에서 테이블 이름 변수에 대한 참조를 FOR 루프 쿼리 결과에 대한 참조로 바꿔야 합니다. 
2. 열 목록 변수를 외부 FOR 루프로 이동시키는 중첩된 복합문을 추가합니다. FOR 루프 본문에서 직접 변수를 선언할 수 없습니다; 새로운 범위를 추가하지 않습니다. 이는 주로 코딩 스타일과 관련된 결정이지만, 새로운 범위에 대해 더 심각한 이유가 있을 것입니다..

이 오류는 이해가 됩니다. 진행할 수 있는 여러 가지 방법이 있습니다:

1. 정보 스키마 쿼리에서 지원되지 않는 테이블 유형, 예를 들어 뷰,를 필터링합니다. 문제는 테이블 유형이 많고, 가끔 새로운 것이 추가된다는 것입니다.
2. 뷰 처리. 그것은 훌륭한 아이디어입니다. 그것을 숙제로 생각해봅시다.
3. 오류 상황을 용인합니다

예외 처리

SQL 스크립팅의 핵심 기능 중 하나는 예외를 가로채고 처리하는 능력입니다. 조건 핸들러는 복합 문의 선언 섹션에서 정의되며, 그것은 그 복합 문 내의 모든 문장, 중첩된 문장을 포함하여 적용됩니다. 특정 오류 조건을 이름으로, 특정 SQLSTATEs를 처리하여 여러 오류 조건을 처리하거나 모든 오류 조건을 처리할 수 있습니다. 조건 핸들러의 본문에서는 GET DIAGNOSTICS 문을 사용하여 처리 중인 예외에 대한 정보를 검색하고, 로그에 오류를 기록하거나 실패한 로직에 대한 대체 로직을 실행하는 등 적절하다고 판단하는 SQL 스크립팅을 실행할 수 있습니다. 그런 다음 SIGNAL 로 새로운 오류 조건을, RESIGNAL 로 원래의 조건을, 또는 단순히 핸들러가 정의된 복합 문을 종료하고 다음 문장을 계속할 수 있습니다.

스크립트에서, ALTER TABLE DEFAULT COLLATION 문이 적용되지 않는 모든 문을 건너뛰고 객체의 이름을 로그에 기록하려고 합니다.

위에서는 순수 SQL로 관리 스크립트를 개발했습니다. 또한 ELT 스크립트를 작성하고 이를 작업으로 변환할 수 있습니다. SQL 스크립팅은 반드시 활용해야 할 강력한 도구입니다.  

다음에 무엇을 해야 할까요

기존 Databricks 사용자이든 다른 제품에서 이전하든, SQL 스크립팅은 사용해야 하는 기능입니다. SQL 스크립팅은 ANSI 표준을 따르며 OSS Apache Spark™와 완벽하게 호환됩니다. SQL 스크립팅에 대한 자세한 설명은 SQL Scripting | Databricks Documentation에서 확인할 수 있습니다. 

또한 이 노트북 을 사용하여 직접 확인해 볼 수도 있습니다. 

 

(이 글은 AI의 도움을 받아 번역되었습니다. 원문이 궁금하시다면 여기를 클릭해 주세요)