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

적용 대상:Azure SQL Database

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

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

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

이 자습서를 완료하려면 다음 필수 구성 요소가 완료되었는지 확인합니다.

• Wingtip Tickets SaaS의 '테넌트별 데이터베이스' 앱이 배포되었습니다. 5분 이내에 배포하려면 Wingtip Tickets 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 데이터베이스를 테넌트별로 애플리케이션 스크립트 가져오기

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

티켓 판매 데이터 만들기

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

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

세계적인 관점 살펴보기

Wingtip Tickets SaaS 테넌트당 데이터베이스 애플리케이션에서 각 테넌트에는 데이터베이스가 제공됩니다. 따라서 데이터베이스 테이블에 포함된 데이터는 단일 테넌트 관점으로 범위가 지정됩니다. 그러나 모든 데이터베이스에서 쿼리할 때, 탄력적 쿼리가 데이터를 테넌트 단위로 샤드된 단일 논리 데이터베이스의 일부로 취급하는 것이 중요합니다.

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

1. SSMS를 열고 tenants1-USER<> 서버에 연결합니다.

2. 데이터베이스를 확장하고 contosoconcerthall을 마우스 오른쪽 단추로 클릭한 다음 새 쿼리를 선택합니다.

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

   -- 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는 장소 이름의 해시로 계산되지만 모든 방법을 사용하여 고유한 값을 도입할 수 있습니다. 이 방법은 테넌트 키가 카탈로그에서 사용하기 위해 계산되는 방식과 유사합니다.

Venues 보기의 정의를 검토하려면 다음을 따르십시오.

1. 개체 탐색기에서 contosoconcerthall> 뷰를 확장합니다.

   

2. dbo.Venues를 마우스 오른쪽 버튼으로 클릭합니다.

3. 새 쿼리 편집> 대한 CREATE> 스크립트 보기 선택

다른 장소 보기에 대해 스크립트를 작성하여 VenueId를 어떻게 추가되는지를 확인합니다.

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

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

1. PowerShell ISE에서 ...\Learning Modules\Operational Analytics\Adhoc Reporting\Demo-AdhocReporting.ps1엽니다.

2. $DemoScenario = 2를 설정하고 임시 보고 데이터베이스를 배포합니다.

3. F5 키를 눌러 스크립트를 실행하여 adhocreporting 데이터베이스를 생성합니다.

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

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

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

1. SQL Server Management Studio를 열고 이전 단계에서 만든 임시 보고 데이터베이스에 연결합니다. 데이터베이스의 이름은 adhocreporting입니다.

2. SSMS에서 ...\Learning Modules\Operational Analytics\Adhoc Reporting\ Initialize-AdhocReportingDB.sql 엽니다.

3. SQL 스크립트를 검토하고 다음을 확인합니다.

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

   

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

   

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

   

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

   

   이러한 방식으로 참조 테이블을 포함하는 경우 테넌트 데이터베이스를 업데이트할 때마다 테이블 스키마 및 데이터를 업데이트해야 합니다.

4. F5 키를 눌러 스크립트를 실행하고 애드 혹 보고 데이터베이스를 초기화합니다.

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

분산 쿼리 실행

이제 임시 보고 데이터베이스가 설정되었으므로 분산 쿼리를 실행하십시오. 쿼리 처리가 발생하는 위치를 더 잘 이해할 수 있도록 실행 계획을 포함합니다.

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

중요한 점은 외부 데이터 원본이 정의될 때 DISTRIBUTION = SHARDED(VenueId) 를 설정하면 많은 시나리오의 성능이 향상된다는 점입니다. 각 VenueId 가 개별 데이터베이스에 매핑되므로 필터링은 원격으로 쉽게 수행되어 필요한 데이터만 반환합니다.

1. SSMS에서 ...\Learning Modules\Operational Analytics\Adhoc Reporting\Demo-AdhocReportingQueries.sql 엽니다.

2. adhocreporting 데이터베이스에 연결되어 있는지 확인하세요.

3. 쿼리 메뉴를 선택하고 실제 실행 계획 포함을 클릭합니다.

4. 현재 등록된 장소는 무엇입니까? 쿼리를 강조 표시하고 F5 키를 누릅니다.

   쿼리는 전체 장소 목록을 반환하며, 모든 테넌트에서 쿼리하고 각 테넌트에서 데이터를 반환하는 것이 얼마나 빠르고 쉬운지를 보여 줍니다.

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

   

5. 다음 쿼리를 선택하고 F5 키를 누릅니다.

   이 쿼리는 테넌트 데이터베이스의 데이터와 애드혹보고 데이터베이스에 있는 VenueTypes 테이블의 데이터를 조인합니다.

   계획을 검사하고 대부분의 비용이 원격 쿼리인지 확인합니다. 각 테넌트 데이터베이스는 해당 장소 정보를 반환하고 로컬 VenueTypes 테이블과 로컬 조인을 수행하여 친숙한 이름을 표시합니다.

   

6. 이제 가장 많은 티켓이 판매된 날을 선택하고 F5 키를 누릅니다.

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

   

다음 단계

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

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

이제 테넌트 분석 자습서 를 사용하여 더 복잡한 분석 처리를 위해 별도의 분석 데이터베이스로 데이터를 추출하는 방법을 살펴봅니다.

추가 리소스

• Wingtip Tickets SaaS 각 테넌트 데이터베이스 애플리케이션을 기반하여 개발된 추가 튜토리얼
• 탄력적 쿼리