Configure SQL Server 2019 Polybase Scale-out Groups
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 |
/******************* PolyBase SacleOut Group事前確認 *******************/ --ヘッドノード、計算ノードの確認 select * from sys.dm_exec_compute_nodes select * from sys.dm_exec_compute_node_status /******************* 環境作成、データベース、認証(資格情報)、外部ソース、外部テーブル *******************/ --データベース作成 USE [master] GO CREATE DATABASE [PolyBaseExcel] CONTAINMENT = NONE ON PRIMARY ( NAME = N'PolyBaseExcel', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\PolyBaseExcel.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) LOG ON ( NAME = N'PolyBaseExcel_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\PolyBaseExcel_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) COLLATE Japanese_CI_AS GO ALTER DATABASE [PolyBaseExcel] SET RECOVERY SIMPLE GO --マスタキーを作成 USE [PolyBaseExcel] GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = <your_password> GO --EXCELの資格情報を作成する --資格情報はダミーだが、外部データソースを作成するときに、文法合わせるため、必要である。 USE [PolyBaseExcel] GO CREATE DATABASE SCOPED CREDENTIAL [ExcelCredential] WITH IDENTITY = 'MyExcel', Secret = '' GO --Credentialsを確認 select * from sys.database_scoped_credentials --外部データソースを作成する。 CREATE EXTERNAL DATA SOURCE [ExcelInstance] WITH ( LOCATION = 'odbc://SQLPB01.jssug.local', CONNECTION_OPTIONS ='DRIVER={CData ODBC Driver for Excel};DSN=MyExcel', CREDENTIAL = [ExcelCredential]) GO --外部データソースを確認する select * from sys.external_data_sources --外部テーブルを作成する(Person) CREATE EXTERNAL TABLE [extExcel_Person]( [RowID] [int] NOT NULL, [PersonID] [nvarchar](10) NOT NULL, [Name] [nvarchar](100) NOT NULL, [CorpCode] [nvarchar](2) NOT NULL, [PostCode] [nvarchar](7) NOT NULL) WITH (LOCATION = 'PersonTbl',DATA_SOURCE = [ExcelInstance]) GO --統計作成、おすすめ、パフォーマンス向上 CREATE STATISTICS [Statistics_PersonID] ON [extExcel_Person]([PersonID]) WITH FULLSCAN GO --データ確認 SELECT [PersonID],[Name],[CorpCode],[PostCode] FROM [PolyBaseExcel].[dbo].[extExcel_Person] --外部テーブルを作成する(Sales) CREATE EXTERNAL TABLE [extExcel_Sales]( [RowID] [int] NOT NULL, [PersonID] [nvarchar](10) NOT NULL, [Qty] [nvarchar](100) NOT NULL, [Price] [nvarchar](100) NOT NULL, [Total] [nvarchar](100) NOT NULL) WITH (LOCATION = 'SalesTbl',DATA_SOURCE = [ExcelInstance]) GO --統計作成、おすすめ、パフォーマンス向上 CREATE STATISTICS [Statistics_PersonID2] ON [extExcel_Sales]([PersonID]) WITH FULLSCAN GO --データ確認 --SELECT [PersonID],[Qty],[Price],[Total] --FROM [PolyBaseExcel].[dbo].[extExcel_Sales] --外部テーブルを作成する(Tel) CREATE EXTERNAL TABLE [extExcel_Tel]( [RowID] [int] NOT NULL, [PersonID] [nvarchar](10) NOT NULL, [Tel] [nvarchar](12) NOT NULL) WITH (LOCATION = 'TelTbl',DATA_SOURCE = [ExcelInstance]) GO --統計作成、おすすめ、パフォーマンス向上 CREATE STATISTICS [Statistics_PersonID3] ON [extExcel_Tel]([PersonID]) WITH FULLSCAN GO --データ確認 --SELECT [PersonID],[Tel] --FROM [PolyBaseExcel].[dbo].[extExcel_Tel] --外部テーブルを作成する(Mail) CREATE EXTERNAL TABLE [extExcel_Mail]( [RowID] [int] NOT NULL, [PersonID] [nvarchar](10) NOT NULL, [Mail] [nvarchar](100) NOT NULL) WITH (LOCATION = 'MailTbl',DATA_SOURCE = [ExcelInstance]) GO --統計作成、おすすめ、パフォーマンス向上 CREATE STATISTICS [Statistics_PersonID4] ON [extExcel_Mail]([PersonID]) WITH FULLSCAN GO --データ確認 --SELECT [PersonID],[Mail] --FROM [PolyBaseExcel].[dbo].[extExcel_Mail] --外部テーブルを作成する(BrithDay) CREATE EXTERNAL TABLE [extExcel_BrithDay]( [RowID] [int] NOT NULL, [PersonID] [nvarchar](10) NOT NULL, [BrithDay] [nvarchar](12) NOT NULL, [C] [nvarchar](12) NOT NULL) WITH (LOCATION = 'BrithDayTbl',DATA_SOURCE = [ExcelInstance]) GO --統計作成、おすすめ、パフォーマンス向上 CREATE STATISTICS [Statistics_PersonID5] ON [extExcel_BrithDay]([PersonID]) WITH FULLSCAN GO --データ確認 --SELECT [PersonID],[BrithDay] --FROM [PolyBaseExcel].[dbo].[extExcel_BrithDay] --外部テーブルを作成する(BrithDay) CREATE EXTERNAL TABLE [extExcel_Password]( [RowID] [int] NOT NULL, [PersonID] [nvarchar](10) NOT NULL, [Password] [nvarchar](8) NOT NULL) WITH (LOCATION = 'PasswordTbl',DATA_SOURCE = [ExcelInstance]) GO --統計作成、おすすめ、パフォーマンス向上 CREATE STATISTICS [Statistics_PersonID6] ON [extExcel_Password]([PersonID]) WITH FULLSCAN GO --データ確認 --SELECT [PersonID],[Password] --FROM [PolyBaseExcel].[dbo].[extExcel_Password] /******************* 外部テーブル結合Join *******************/ --単一テーブルクエリ SELECT tblExcel1.PersonID,tblExcel1.[Name] FROM [PolyBaseExcel].[dbo].[extExcel_Person] AS tblExcel1 --各テーブル結合クエリ SELECT tblExcel1.PersonID,tblExcel1.[Name],tblExcel2.Total,tblExcel3.[Tel],tblExcel4.[Mail],tblExcel5.[BrithDay],tblExcel6.[Password] FROM [PolyBaseExcel].[dbo].[extExcel_Person] AS tblExcel1 INNER JOIN [PolyBaseExcel].[dbo].[extExcel_Sales] AS tblExcel2 ON tblExcel1.PersonID = tblExcel2.PersonID INNER JOIN [PolyBaseExcel].[dbo].[extExcel_Tel] AS tblExcel3 ON tblExcel1.PersonID = tblExcel3.PersonID INNER JOIN [PolyBaseExcel].[dbo].[extExcel_Mail] AS tblExcel4 ON tblExcel1.PersonID = tblExcel4.PersonID INNER JOIN [PolyBaseExcel].[dbo].[extExcel_BrithDay] AS tblExcel5 ON tblExcel1.PersonID = tblExcel5.PersonID INNER JOIN [PolyBaseExcel].[dbo].[extExcel_Password] AS tblExcel6 ON tblExcel1.PersonID = tblExcel6.PersonID ORDER BY tblExcel1.PersonID /******************* PolyBaseスケールアウト確認 *******************/ --SQLクエリ実行履歴を検索して、PolyBaseのクエリのIDを探し出す SELECT execution_id, st.[text], dr.total_elapsed_time, dr.start_time FROM sys.dm_exec_distributed_requests as dr CROSS APPLY sys.dm_exec_sql_text(sql_handle) as st ORDER BY dr.start_time DESC --履歴確認したら、該当クエリIDをメモする DECLARE @StrExecution_id varchar(50) = 'QID5316' --ExternalGenericRoundRobinOperationを確認する SELECT execution_id, step_index, operation_type, distribution_type, location_type, [status], total_elapsed_time, command FROM sys.dm_exec_distributed_request_steps WHERE execution_id = @StrExecution_id ORDER BY step_index --使われている計算ノードを確認する1 SELECT DISTINCT execution_id, cn.[name] as compute_node, distribution_id, [status], total_elapsed_time, command FROM sys.dm_exec_distributed_sql_requests as dsr JOIN sys.dm_exec_compute_nodes as cn ON dsr.compute_node_id = cn.compute_node_id WHERE execution_id = @StrExecution_id AND step_index = 2 ORDER BY distribution_id --External RoundRobinの各計算ノードのStep確認 SELECT DISTINCT execution_id, cn.[name] as compute_node, step_index, dms_step_index, [status], dw.[type], bytes_processed, total_elapsed_time FROM sys.dm_exec_dms_workers as dw JOIN sys.dm_exec_compute_nodes as cn ON dw.compute_node_id = cn.compute_node_id WHERE execution_id = @StrExecution_id AND step_index = 5 ORDER BY dms_step_index /******************* 削除する場合 *******************/ USE [master] GO ALTER DATABASE [PolyBaseExcel] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO USE [master] GO DROP DATABASE [PolyBaseExcel] GO |