Windows PowerShell (파워셸)에서 SQL 서버 데이터를 필터링하는 방법

이전의 포스팅에서는 파워셸을 이용하여 SQL 서버의 데이터에 접속하는 방법을 설명하였습니다.  데이터를 불러오거나 테이블에 삽입하는 방법을 알아보았는데요, 당연히 파워셸 말고도 다른 여러 방법들이 존재합니다.  

 

파워셸 (PowerShell)을 이용하는 이유는 여러 스크립트를 이용하여 데이터 분석이 가능하다는 것입니다.  게다가 데이터에 따라 다양한 액션을 취할 수 있는 자동화 엔진처럼 사용이 가능하다는 이점이 있습니다.  예를 들면, 파워셸을 이용하여 기업 내의 인사 데이터베이스에 새로운 사원이 추가되면 새로운 계정을 만드는 자동화 기능같이 모니터를 할 수 있죠.

 

이 포스팅은 다음의 세가지 포스팅과 연결되어 있습니다.

 

1. 간단한 SQL 서버 데이터베이스 구축하기 (https://ranku.tistory.com/487)
2. Windows PowerShell (파워셸)에서 SQL 서버 데이터베이스에 연결하는 방법 (https://ranku.tistory.com/486)
3. Windows PowerShell (파워셸)에서 SQL 서버 데이터를 필터링하는 방법 (https://ranku.tistory.com/485)

 

데이터 분석을 하던지, 또는 데이터를 이용한 오케스트레이션을 하는 이유 등에는 파워셸에 필요한 데이터를 필터링하는 것이 중요합니다.  데이터 필터링은 SQL 서버에서도 할 수 있고 파워셸에서도 가능합니다. 그렇지만, 많은 양의 데이터를 다룰 시에는 SQL 서버에서 필터링을 하는 것이 더 효율적입니다.  이 포스팅에서는 이 두 가지의 방법을 알아보겠습니다.

 

728x90

 

파워셸에서 필터링하는 방법

 

아래의 이미지에서 볼수 있듯이 파워셸의 간단한 커맨드를 이용하여 데이터를 읽습니다.  이 예제에서는 $Data라는 변수에 결과를 저장하였습니다.

 

 

파워셸 변수에 결과 값이 저장이 되었으니, 다음의 기술을 사용하여 부분적인 데이터를 필터링하여 보겠습니다.

 

위의 데이터에는 7월 (July)의 매출 갯수는 35개이죠.  예제로 이 7월의 데이터를 필터링하여 매출 개수만 보이도록 하여 보겠습니다.

 

먼저 다음의 커맨드를 사용합니다:

 

$Data | Where-Object {$_.Month -eq ‘July’}

 

이 커맨드는 7월의 데이터를 리턴하게 되죠. 

 

• Where-Object의 다음에 들어가는 { } 사이가 Where를 지정하는 컨디션입니다.  
• $_ 는 연결된 변수 ($Data)이고 . 다음에 보이는 것이 칼럼 이름이죠.  
• -eq는 = 과 같은 뜻이고 (equal을 줄임말) 그리고 ‘July’가 필터링하는 컨디션입니다.  

 

그래서 Month가 July인 데이터를 리턴하라는 의미이죠.

 

하지만 매출개수만 보이는 것이 목표입니다.  다 끝이 난 것이 아니죠.  한 가지의 방법은 이 커맨드를 또 하나의 변수에 지정을 하는 것입니다.  이 예제에서는 아래의 이미지와 같이 $RawData라는 변수에 지정을 하겠습니다.  

 

 

$RawData에 필터링된 데이터를 부르는 커맨드가 지정이 되면, 35라는 매출 개수를 불러오는 방법은 $RawData.ItemsSold를 사용하면 됩니다.  

 

이유는 $RawData라는 변수는 필터링된 데이터가 저장이 되어 있고 (클래스라고 생각하면 됨), 그 데이터의 한 Property (칼럼)을 불러오는 것은 클래스의 Property를 부르는 것 같이 변수 뒤의 . (점)을 사용하면 되죠.

 

당연히 파워셸에는 고급 필터링 방법도 존재합니다.  예를 들자면, J로 시작하는 Month 칼럼의 데이터를 필터링 하려면 다음과 같은 커맨드를 사용하면 됩니다.

 

$RawData = $Data | Where-Object {$_.Month -like ‘J*’}
$RawData

 

 

위의 예제에서 다른 부분은 -eq였던 부분이 -like로 수정이 되었습니다.  그리고 J*라는 부분인데 * 키는 Where의 와일드카드 서치를 할 수 있게 하죠.  -like는 와일드카드 서치용이므로 찾는 부분에 와일드카드인 *를 사용하여야 필터링이 원하는 대로 됩니다.  참고로 SQL 서버에서는 와일드카드 키가 %입니다.

 

반응형

 

SQL 서버에서 필터링 하는 방법

 

앞에서 보인 필터링 방법은 데이터의 수가 적을 때 사용하면 좋습니다.  하지만, 몇백만 개의 데이터를 다루게 된다면 파워셸은 무리입니다.  파워셸로는 가능하지만 파워셸이 구동되고 있는 시스템 (대부분이 서버나 개발자 컴퓨터)에 무리가 가게 됩니다.  이런 경우에는 데이터를 아예 SQL 서버에서 필터링하여 필터링된 데이터만 파워셸로 보내는 것이 현명한 방법입니다.

 

SQL 서버 데이터를 필터링 하는 방법은 아주 많이 있습니다.  이 포스팅에서는 파워셸에서 보내어지는 SQL 쿼리 문구를 수정하는 방법을 사용해 보겠습니다.

 

이전의 예제는 다음과 같은 커맨드를 사용하였습니다.

 

$Data=Invoke-SQLCmd -ServerInstance Win11SQL\SQLEXPRESS -Database MyAppDB -Query “Select Month, ItemsSold From Table1”

 

이 방법은 모든 데이터를 파워셸로 먼저 가지고 와서 파워셸에서 필터링하게 되죠.  데이터가 몇백만 개라면 먼저 그 모든 데이터를 가지고 오게 됩니다.  

 

-Query 파라미터 부분 다음에 오는 파라미터 값이 SQL 쿼리인데 이 쿼리에 Where의 문구를 사용하면 서버에서 먼저 필터링된 데이터만 파워셸로 오게 됩니다. 

 

이 전의 예제를 그대로 사용한다고 하면, July의 35라는 매출 개수를 불러오는 것이 목표이죠.  그러면 SQL 쿼리를 “Select Month, ItemsSold From Table1” 에서 “Select ItemsSold From Table1 Where Month = ‘July’” 라고 수정하면 되겠죠?  하지만 이렇게 간단하지는 않습니다.  다음의 두 가지 문제가 있습니다.

 

파워셸의 SQL 쿼리 문구를 보면 ItemsSold라는 칼럼만 가지고 오지만 Where의 컨디션에는 Month가 존재하죠.  Where 컨디션을 사용 하려면 Select에도 같은 칼럼이 존재하여야 합니다.  그렇지 않으면 필터링을 할 수 없습니다.

 

이 테이블의 Month 칼럼의 데이터 타입은 text입니다.  July라는 문자를 비교하려면 text 타입의 데이터는 오류가 생깁니다.  Where의 문구에 Month를 varchar의 데이터 타입으로 변형을 하여야 하죠. 

 

그래서 오류가 없는 SQL 쿼리는 다음과 같습니다:

 

Select Month, ItemsSold From Table1 Where Convert(VARCHAR,Month) = ‘July’;

 

그리고 완전한 파워셸 커맨드는 다음과 같게 됩니다:

 

$Data=Invoke-SQLCmd -ServerInstance Win11SQL\SQLEXPRESS -Database MyAppDB -Query “Select Month, ItemsSold From Table1 Where Convert(VARCHAR,Month) = ‘July’;”

 

그리고 아래의 이미지와 같이 $Data.ItemsSold 커맨드를 이용하여 35라는 매출 갯수를 불러오게 됩니다.

 

 

마지막의 예제로, SQL 쿼리를 Month 칼럼이 J로만 시작되는 데이터를 불러 보겠습니다.  이전의 쿼리와 비슷하지만 = 대신에 Like를 사용하여야 합니다.  그리고 앞에서 말했다시피 와일드카드 키를 SQL용 와일드카드 키인 %를 이용해야 하죠.

 

그래서 SQL 쿼리는 다음과 같습니다:

 

Select Month, ItemsSold From Table1 Where Convert(VARCHAR,Month) Like ‘J%’

마지막으로 이 쿼리를 사용하는 파워셸 커맨드는 다음과 같게 되죠:

 

$Data=Invoke-SQLCmd -ServerInstance Win11SQL\SQLEXPRESS -Database MyAppDB -Query “Select Month, ItemsSold From Table1 Where Convert(VARCHAR,Month) Like ‘J%’;”
$Data

 

 

기억해야 할 중요한 부분은 -Query 파라미터 다음의 쿼리 문장에 넣는 필터링은 SQL 서버에서 필터링이 먼저 되어 파워셸의 변수에 저장이 된다는 것입니다.  만약에 SQL 쿼리로 필터링을 안하고 파워셸 커맨드로 필터링 (첫 번째 섹션 같이) 한다면 모든 데이터를 SQL 서버에서 불러와 변수에 저장을 먼저 하고 나서 필터링을 하는 것입니다.

 

그리고 데이터가 저장된 변수는 클래스와 같아서 속해있는 칼럼은 클래스의 Property와 같이 . (점)을 사용하여 값을 호출할 수 있습니다.

 

---

유익하거나 즐거우셨다면 아래의 ❤️공감 버튼이나 구독 (SUBSCRIBE) 버튼을 눌러 주세요~ 

감사합니다

 

 

 

참고:

itprotoday.com