분산 쿼리를 사용한 교차 테넌트 보고

아티클
03/19/2024

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

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

보고 데이터베이스를 배포하는 방법
모든 테넌트 데이터베이스에서 분산 쿼리 실행 방법
각 데이터베이스의 전역 보기를 통해 테넌트 전체에서 효율적인 쿼리를 가능하게 하는 방법

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

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

교차 테넌트 보고 패턴

테넌트 간 분산 쿼리 패턴

SaaS 애플리케이션을 사용했을 때의 장점은 애플리케이션 작업 및 사용에 대한 정보를 얻기 위해 클라우드에 저장된 광범위한 테넌트 데이터를 활용할 수 있다는 점입니다. 이와 같은 인사이트는 기능 개발, 사용성 개선, 앱 및 서비스의 기타 투자에 대한 방향성을 제공할 수 있습니다.

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

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

Wingtip Tickets SaaS Database Per Tenant 애플리케이션 스크립트 가져오기

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

티켓 판매 데이터 생성

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

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

전역 보기 살펴보기

Wingtip Tickets SaaS 테넌트당 데이터베이스 애플리케이션에서 각 테넌트에는 데이터베이스가 제공됩니다. 따라서 데이터베이스 테이블에 포함된 데이터는 단일 테넌트 관점으로 범위가 지정됩니다. 그러나 모든 테넌트 데이터베이스에 걸쳐 쿼리하는 경우 Elastic Query가 해당 데이터를 테넌트가 분할한 단일 논리 데이터베이스의 일부인 것처럼 처리할 수 있다는 점이 중요합니다.

이 패턴을 시뮬레이션하기 위해 '전역' 뷰 집합이 전역적으로 쿼리되는 각 테이블에 테넌트 ID를 프로젝션하는 테넌트 데이터베이스에 추가됩니다. 예를 들어 VenueEvents 보기는 이벤트 테이블에서 프로젝트된 열에 계산된 VenueId를 추가합니다. 마찬가지로 VenueTicketPurchases 및 VenueTickets 뷰는 해당 테이블에서 프로젝트된 계산된 VenueId 열을 추가합니다. 이러한 보기는 VenueId 열이 게시되어 있을 때 Elastic Query에서 쿼리를 병렬화하고 적절한 원격 테넌트 데이터베이스로 푸시하는 데 사용됩니다. 이렇게 하면 반환되는 데이터의 양이 크게 줄어듭니다. 그 결과 많은 쿼리의 성능이 크게 증가합니다. 이러한 전역 보기는 모든 테넌트 데이터베이스에서 미리 만들어졌습니다.

SSMS를 열고 tenants1-<USER> 서버 에 연결합니다.
데이터베이스를 확장하고 contosoconcerthall을 마우스 오른쪽 단추로 클릭하고 새 쿼리를 선택합니다.

다음 쿼리를 실행하여 단일 테넌트 테이블과 전역 보기기 간의 차이를 살펴봅니다.

-- The base Venue table, that has no VenueId associated.
SELECT * FROM Venue

-- Notice the plural name 'Venues'. This view projects a VenueId column.
SELECT * FROM Venues

-- The base Events table, which has no VenueId column.
SELECT * FROM Events

-- This view projects the VenueId retrieved from the Venues table.
SELECT * FROM VenueEvents

이러한 보기에서, VenueId는 장소 이름의 해시로 계산되지만 임의의 방법을 사용하여 고유한 값을 도입할 수 있습니다. 이 방법은 카탈로그에서 사용하기 위해 테넌트 키를 계산하는 방식과 비슷합니다.

장소 보기의 정의를 검사하려면 다음을 수행합니다.

개체 탐색기에서, contosoconcerthall>보기를 확장합니다.
마우스 오른쪽 단추로 dbo.Venues를 클릭합니다.
스크립트 보기>CREATE >새 쿼리 에디터 창을 선택합니다.

다른 부문 보기를 스크립팅하여 VenueId를 추가하는 방법을 확인합니다.

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

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

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

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

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

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

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

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

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

이전 섹션에서 설명한 전역 보기를 참조하고 DISTRIBUTION = SHARDED(VenueId)로 정의된 외부 테이블입니다. 각 VenueId 는 개별 데이터베이스에 매핑되므로 다음 섹션에 표시된 것처럼 많은 시나리오의 성능이 향상됩니다.

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

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

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

분산 쿼리 실행

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

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

기억할 점으로, 외부 데이터 원본이 정의되었을 때 DISTRIBUTION = SHARDED(VenueId)를 설정하면 여러 시나리오의 성능이 향상됩니다. 각 VenueId가 개별 데이터베이스에 매핑하기 때문에 쉽게 원격으로 필터링할 수 있으며 필요한 데이터만 반환합니다.

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

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

계획을 검사하고 전체 비용이 원격 쿼리에 있는지 확인합니다. 각 테넌트 데이터베이스는 쿼리를 원격으로 실행하고 해당 장소 정보를 헤드 데이터베이스에 반환합니다.
다음 쿼리를 선택하고 F5 키를 누릅니다.

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

계획을 검사하여 비용의 상당 부분이 원격 쿼리 때문에 발생하는지 확인합니다. 각 테넌트 데이터베이스는 해당 장소 정보를 반환하고 로컬 VenueTypes 테이블과 로컬 조인을 수행하여 친숙한 이름을 표시합니다.
어떤 날짜에 티켓이 가장 많이 판매되었습니까?를 선택하고 F5 키를 누릅니다.

이 쿼리는 좀 더 복잡한 조인 및 집계를 수행합니다. 대부분의 처리는 원격으로 발생합니다. 각 장소의 일일 티켓에 대한 일별 판매 횟수가 포함된 단일 행만 헤드 데이터베이스로 반환됩니다.

다음 단계

이 자습서에서는 다음 방법에 대해 알아보았습니다.

모든 테넌트 데이터베이스에서 분산 쿼리 실행
보고 데이터베이스를 배포하고 분산 쿼리를 실행하는 데 필요한 스키마를 정의합니다.

이제 테넌트 분석 자습서를 시도하여 복잡한 분석을 처리하기 위해 별도 분석 데이터베이스에 추출하는 데이터를 탐색합니다.

다음을 통해 공유