여러 데이터베이스(Azure SQL Database)에 대해 임시 분석 쿼리 실행

아티클
07/31/2023

이 자습서에서는 임시 대화형 보고를 가능케 하기 위해 테넌트 데이터베이스의 전체 집합에서 분산 쿼리를 실행합니다. 이러한 쿼리는 Wingtip Tickets SaaS 앱의 일일 운영 데이터에 담긴 정보를 추출할 수 있습니다. 이러한 추출을 위해 추가 분석 데이터베이스를 카탈로그 서버에 배포하고 탄력적 쿼리를 사용하여 분산 쿼리를 사용하도록 설정합니다.

이 자습서에서는 다음에 대해 알아봅니다.

임시 보고 데이터베이스를 배포하는 방법
모든 테넌트 데이터베이스에서 분산 쿼리 실행 방법

이 자습서를 수행하려면 다음 필수 조건이 완료되었는지 확인합니다.

Wingtip Tickets SaaS 다중 테넌트 데이터베이스 앱이 배포되어 있어야 합니다. 5분 내에 배포하려면 Wingtip 티켓 SaaS 다중 테넌트 데이터베이스 애플리케이션 배포 및 탐색을 참조하세요.
Azure PowerShell이 설치되었습니다. 자세한 내용은 Azure PowerShell 시작을 참조하세요.
SSMS(SQL Server Management Studio) 설치됨. SSMS 다운로드 및 설치를 위해, SSMS(SQL Server Management Studio) 다운로드를 참조하세요.

임시 보고 패턴

SaaS 애플리케이션은 클라우드에 중앙에 저장된 방대한 양의 테넌트 데이터를 분석할 수 있습니다. 분석을 통해 애플리케이션의 작업 및 사용에 대한 인사이트를 확인할 수 있습니다. 이와 같은 인사이트는 기능 개발, 사용성 개선, 앱 및 서비스의 기타 투자에 대한 방향성을 제공할 수 있습니다.

단일 다중 테넌트 데이터베이스에 있을 때 이 데이터에 액세스하는 것은 쉽지만 잠재적인 수천 개의 데이터베이스 규모로 분산되는 경우는 그렇게 쉽지 않습니다. 한 가지 방법은 공통 스키마를 통해 배포된 데이터베이스 집합 전체에서 쿼리를 구현하는 탄력적 쿼리를 사용하는 것입니다. 이러한 데이터베이스는 여러 리소스 그룹 및 구독에 분산될 수 있습니다. 그러나 일반 로그인에 모든 데이터베이스에서 데이터를 추출할 수 있는 액세스 권한이 있어야 합니다. 탄력적 쿼리는 분산된(테넌트) 데이터베이스에서 테이블을 미러링하는 외부 테이블 또는 뷰가 정의되는 단일 헤드 데이터베이스를 사용합니다. 이 헤드 데이터베이스에 제출된 쿼리는 분산 쿼리 계획을 생성하기 위해 컴파일되며, 필요에 따라 쿼리의 일부가 테넌트 데이터베이스로 푸시됩니다. 탄력적 쿼리는 카탈로그 데이터베이스에서 분할된 데이터베이스 맵을 사용하여 모든 테넌트 데이터베이스의 위치를 확인합니다. 설정 및 쿼리는 일반 Transact-SQL을 사용하여 바로 진행되며 Power BI 또는 Excel 같은 도구에서의 임시 쿼리를 지원합니다.

탄력적 쿼리는 테넌트 데이터베이스에 쿼리를 배포하여 라이브 프로덕션 데이터에 즉시 정보를 제공합니다. 그러나 탄력적 쿼리가 잠재적으로 많은 데이터베이스에서 데이터를 끌어오면 쿼리 대기 시간이 단일 다중 테넌트 데이터베이스에 제출된 동일한 쿼리보다 높을 수 있습니다. 반환되는 데이터를 최소화하기 위해 쿼리를 디자인해야 합니다. 탄력적 쿼리는 자주 사용되거나 복잡한 분석 쿼리 또는 보고서를 작성하는 것과 달리 적은 양의 실시간 데이터를 쿼리하는 데 가장 적합합니다. 쿼리가 잘 수행되지 않는 경우 실행 계획을 확인하여 쿼리의 일부가 원격 데이터베이스로 푸시된 부분을 확인합니다. 그리고 반환되는 데이터의 양을 평가합니다. 추출된 테넌트 데이터를 분석 쿼리에 최적화된 데이터베이스에 저장하여 복잡한 분석 처리가 필요한 쿼리를 더 효율적으로 처리할 수 있습니다. SQL Database 및 Azure Synapse Analytics는 이러한 분석 데이터베이스를 호스트할 수 있습니다.

이 분석 패턴은 테넌트 분석 자습서에 설명되어 있습니다.

Wingtip Tickets SaaS 다중 테넌트 데이터베이스 애플리케이션 소스 코드 및 스크립트 가져오기

Wingtip Tickets SaaS 다중 테넌트 데이터베이스 스크립트 및 애플리케이션 소스 코드는 WingtipTicketsSaaS-MultitenantDB GitHub 리포지토리에서 확인할 수 있습니다. Wingtip Tickets SaaS 스크립트를 다운로드하고 차단을 해제하는 단계는 일반 지침을 확인하세요.

티켓 판매 데이터 생성

더 흥미로운 데이터 집합에 대해 쿼리를 실행하려면 티켓 생성기를 실행하여 티켓 판매 데이터를 만듭니다.

PowerShell ISE에서 ...\Learning Modules\Operational Analytics\Adhoc Reporting\Demo-AdhocReporting.ps1 스크립트를 열고 다음 값을 설정합니다.
- $DemoScenario = 1, 모든 행사장에서 이벤트 티켓 구입
F5 키를 눌러 스크립트를 실행하고 티켓 판매를 생성합니다. 스크립트가 실행되는 동안 이 자습서의 단계를 계속 진행합니다. 임시 배포된 쿼리 실행 섹션에서 티켓 데이터가 쿼리되니, 티켓 생성기가 완료할 때까지 기다립니다.

테넌트 테이블 탐색

Wingtip Tickets SaaS 다중 테넌트 데이터베이스 애플리케이션에서 테넌트는 하이브리드 테넌트 관리 모델에 저장됩니다. - 여기서 테넌트 데이터는 다중 테넌트 데이터베이스 또는 단일 테넌트 데이터베이스에 저장되고 둘 간에 이동할 수 있습니다. 모든 테넌트 데이터베이스에 걸쳐 쿼리하는 경우 Elastic Query가 해당 데이터를 테넌트가 분할한 단일 논리 데이터베이스의 일부인 것처럼 처리할 수 있다는 점이 중요합니다.

이 패턴을 달성하기 위해 모든 테넌트 테이블에는 데이터가 속한 테넌트를 식별하는 VenueId 열이 포함됩니다. VenueId는 장소 이름의 해시로 계산되지만 임의의 방법을 사용하여 이 열에 고유한 값을 도입할 수 있습니다. 이 방법은 카탈로그에서 사용하기 위해 테넌트 키를 계산하는 방식과 비슷합니다. VenueId가 포함된 테이블은 Elastic Query에서 쿼리를 병렬화하고 적절한 원격 테넌트 데이터베이스로 푸시하는 데 사용됩니다. 이렇게 하면 반환되는 데이터의 양이 크게 줄어들고 특히 단일 테넌트 데이터베이스에 저장된 데이터의 테넌트가 여러 개 있는 경우 성능이 향상됩니다.

임시 분산 쿼리에 사용되는 데이터베이스 배포

이 연습에서는 adhocreporting 데이터베이스를 배포합니다. 모든 테넌트 데이터베이스에서 쿼리하는 데 사용되는 스키마가 포함된 헤드 데이터베이스입니다. 데이터베이스는 샘플 앱에서 모든 관리 관련 데이터베이스에 사용되는 서버인 기존 카탈로그 서버에 배포됩니다.

PowerShell ISE에서 ...\Learning Modules\Operational Analytics\Adhoc Reporting\Demo-AdhocReporting.ps1을 열고 다음 값을 설정합니다.
- $DemoScenario = 2, 임시 분석 데이터베이스 배포
F5 키를 눌러 스크립트를 실행하고 adhocreporting 데이터베이스를 만듭니다.

다음 섹션에서 배포된 쿼리를 실행하는 데 사용할 수 있도록 데이터베이스에 스키마를 추가합니다.

분산 쿼리를 실행하기 위한 'head' 데이터베이스 구성

이 연습에서는 모든 테넌트 데이터베이스에서 쿼리할 수 있도록 하는 임시 보고 데이터베이스에 스키마(외부 데이터 원본 및 외부 테이블 정의)를 추가합니다.

SQL Server Management Studio를 열고 이전 단계에서 만든 임시 보고 데이터베이스에 연결합니다. 데이터베이스의 이름은 adhocreporting입니다.
SSMS에서 ....\Learning Modules\Operational Analytics\Adhoc Reporting\ Initialize-AdhocReportingDB.sql을 엽니다.
SQL 스크립트를 검토하고 다음을 확인합니다.

Elastic Query는 데이터베이스 범위 자격 증명을 사용하여 각 테넌트 데이터베이스에 액세스합니다. 이 자격 증명은 모든 데이터베이스에서 사용할 수 있어야 하며 일반적으로 이러한 임시 쿼리를 사용하는 데 필요한 최소 권한이 부여되어야 합니다.

이 카탈로그 데이터베이스를 외부 데이터 원본으로 사용하면 쿼리가 실행되는 경우 카탈로그에 등록된 모든 데이터베이스에 쿼리가 배포됩니다. 각 배포에 대한 서버 이름이 다르기 때문에 이 초기화 스크립트는 스크립트가 실행되는 현재 서버(@@servername)를 검색하여 카탈로그 데이터베이스의 위치를 가져옵니다.

테넌트 테이블을 참조하는 외부 테이블은 DISTRIBUTION = SHARDED(VenueId)로 정의됩니다. 이렇게 하면 특정 VenueId 에 대한 쿼리를 적절한 데이터베이스로 라우팅하며 다음 섹션에 표시된 것처럼 많은 시나리오에 대한 성능이 향상됩니다.

생성되고 채워져 있는 로컬 테이블 VenueTypes 입니다. 이 참조 데이터 테이블은 모든 테넌트 데이터베이스에서 일반적으로 사용되므로 로컬 테이블로 표시하고 공통 데이터로 채울 수 있습니다. 이 일부 쿼리의 경우 테넌트 데이터베이스와 adhocreporting 데이터베이스 간에 이동되는 데이터의 양을 줄일 수 있습니다.

이러한 방식으로 참조 테이블을 포함하는 경우 테넌트 데이터베이스를 업데이트할 때마다 테이블 스키마 및 데이터를 업데이트해야 합니다.
F5 키를 눌러 스크립트를 실행하고 adhocreporting 데이터베이스를 초기화합니다.

이제 분산 쿼리를 실행하고 모든 테넌트에서 인사이트를 수집할 수 있습니다.

임시 배포된 쿼리 실행

이제 adhocreporting 데이터베이스가 설정되었으므로 앞에서 배포된 몇 가지 쿼리를 실행합니다. 쿼리 처리가 발생하는 위치를 이해하는 실행 계획을 포함합니다.

실행 계획을 검사할 때 계획 아이콘 위로 마우스를 가져가서 세부 정보를 확인합니다.

SSMS에서 ...\Learning Modules\Operational Analytics\Ad Hoc Reporting\Demo-AdhocReportingQueries.sql을 엽니다.
adhocreporting 데이터베이스에 연결되었는지 확인합니다.
쿼리 메뉴를 선택하고 실제 실행 계획 포함을 클릭합니다.
현재 등록된 장소는 무엇입니까? 쿼리를 강조 표시하고 F5 키를 누릅니다.

쿼리는 전체 부문 목록을 반환하고 모든 테넌트에 쉽고 빠르게 쿼리하는 방법 및 각 테넌트의 데이터를 반환하는 방법을 보여줍니다.

각 테넌트 데이터베이스로 이동하고 장소 정보를 선택하는 것에 불과하므로 계획을 검사하고 전체 비용이 원격 쿼리인지 확인합니다.
다음 쿼리를 선택하고 F5 키를 누릅니다.

이 쿼리는 테넌트 데이터베이스와 로컬 VenueTypes 테이블의 데이터를 조인합니다(adhocreporting 데이터베이스의 테이블인 로컬).

각 테넌트의 부문 정보(dbo.Venues)를 쿼리한 다음 VenueTypes 테이블과 빠르게 로컬 조인하여 친숙한 이름을 표시하기 때문에 계획을 검사하고 비용의 대부분이 원격 쿼리인지 확인합니다.
어떤 날짜에 티켓이 가장 많이 판매되었습니까?를 선택하고 F5 키를 누릅니다.

이 쿼리는 좀 더 복잡한 조인 및 집계를 수행합니다. 주의해야 할 중요한 점은 대부분의 처리가 원격으로 수행되고 다시 한 번 필요한 행만 다시 가져와서 각 장소의 집계 티켓 판매 횟수에 대해 하루에 한 행만 반환한다는 것입니다.