Introduction to Microsoft Azure SQL Data Warehouse www

Introduction to Microsoft Azure SQL Data Warehouse www. Henkvandervalk. com

Thanks to our platinum sponsors : 2 | PASS SQL Saturday – Holland - 2015

Thanks to our silver and gold sponsors : 3 | PASS SQL Saturday – Holland - 2015

@Henkvander. Valk Speaker Introduction § § 10 years active in SQLPass community! 10 years of Unisys-EMEA Performance Center 2002 - Largest SQL DWH in the world (SQL 2000) Project Real – (SQL 2005) • ETL WR - loading 1 TB within 30 mins (SQL 2008) • Contributor to SQL performance whitepapers • Perf Tips & tricks: www. henkvandervalk. com § § Schuberg Philis- 100% uptime for mission critical apps Since april 1 st, 2011 – Microsoft ! All info represents my own personal opinion (based upon my own experience)

Agenda § Intro / SQLDW in 5 minutes § Getting started § Create your own SQLDW via Azure Portal § SQL SMP Migration Utility § Load data BCP / Polybase § Tables § Monitoring MPP query execution § Backup/Restore

Microsoft Analytics Suite Transform Data into Intelligent Action Big Data Stores Business Scenarios DATA INTELLIGENCE ACTION

Introducing SQL Data Warehouse § Fully managed relational data warehouse-as-a-service § The first elastic cloud data warehouse with enterprise-grade capabilities § Supports the smallest to largest data sets Market Leading Price & Performance Elastic scale & performance Powered by the Cloud

Elastic Scale § Spin up for heavy workloads, cycle down for daily activity § Buy time to insight based on what you need, when you need it § Choose the combo of compute and storage that meets your needs

Pause § Data remains in place – no reloading / restoring of data § When paused, cloud-scale storage is at minimal cost § Automate via Power. Shell/REST API $$$$

Its all about flex- & scalability

Data Warehouse Unit (DWU) Measure of Power Simply buy the query performance you need, not just hardware Transparency Quantified by workload objectives: how fast rows are scanned, loaded, copied On Demand First DW service to offer compute power on demand, independent of storage Scan 1 B rows 100 DWU = xx sec Scan Rate xx M row/sec 400 DWU = xx sec Loading Rate xx K row/sec 800 DWU = xx sec 1, 600 DWU = xx sec 100 DWU Table Copy Rate xx K row/sec

Market-Leading Price/Performance § Best On-Demand Price/Performance § § SQL DW start small, can grow to PB+ § Pay for performance by scaling compute against storage ce n ma for er W Performance D L Q P gh i H S Advantages in elasticity and pause to reduce customer cost ced D SQL alan W B h Capacity SQL DW Hig 100 GB 1 TB 2 TB 1+PB

Azure Security Configure Server level Firewall for remote access

Demos § Create a SQLDW DB via Azure portal § DWH Migration utility § Lets Scale SQL DW up& down!

Signup for Preview

Create a V 12 SQL DB

SQL DB Logins In Master DB: § CREATE LOGIN [cloudsa] WITH PASSWORD = 'SQLSAT-434‘ In SQLDW DB: § CREATE USER Cloud. SA FOR LOGIN Cloud. SA; § EXEC sp_addrolemember 'db_datareader', 'Cloud. SA'; -- allows Application. User to read data § EXEC sp_addrolemember 'db_datawriter', 'Cloud. SA'; -- allows Application. User to write data

Loading data with BCP sqldw. dbo. lineitem_CCI_RR in C: Toolbox-SQLdbgenlineitem. tbl -c -U cloudsa -P SQLSAT-434 -S henks-sqldw. database. windows. net -q -t"|" 600572 rows copied. Network packet size (bytes): 4096 Clock Time (ms. ) Total : 72875 Average : (8241. 13 rows per sec. )

Scale #DWU’s via Power. Shell C: PS>$Server. Name = “ ” C: PS>$Database. Name = “SQLSAT 434 -DW” C: PS>$Service. Objective = “B 89 B 9 C 6 A-4 EC 2 -4 EB 8 -99 DB-6 D 2807 E 6 AAB” (DW 1000) C: PS>$Database = Get-Azure. Sql. Database -Server. Name $Server. Name -Database. Name $Database. Name C: PS>Set-Azure. Sql. Database –Server. Name $Server. Name –Service. Object $Service. Objective

Scale #DWU’s via TSQL Via master DB: ALTER DATABASE [dbo]. [SQLSATDW] { SET SERVICE_OBJECTIVE = 'DW 1000' };

MPP vs. SMP Architecture vs

SQL DW: Building on SQL DB Foundation SQL DW Elastic, Petabyte Scale DW Optimized 99. 99% uptime SLA, Geo-restore Azure Compliance (ISO, HIPAA, EU, etc. ) SQL DB Service Tiers True SQL Server Experience; Existing Tools Just Work

Broad Ecosystem Leverage Azure ML, a Power BI, ADF, Stream Analytics and more Streamlined deployment with Azure Portal Deep tool integration with top partners Azure SQL DW Azure Event Hub Azure HDInsight Azure ML

Query Unstructured Data via Polybase Capability Quote: ************ T-SQL query *********************** SQL Server Name DOB State Jim Gray 11/13/58 WA Ann Smith 04/29/76 ME Hadoop $658. 39 T-SQL for querying relational and non-relational data across SQL Server and Hadoop Benefits Æ New business insights across your data lake Æ Leverage existing skillsets and BI tools Æ Faster time to insights and simplified ETL process

Polybase demo Direct Read/Write Into SQLDW From/to WASB (Windows Azure blob storage data)

SQLDW Backup & Restore

Monitoring SQLDW Tracing exact query response timings: select command, * from sys. dm_pdw_exec_requests where submit_time > '2015 -09 -25 00: 01. 953' and command not like '%SELECT SERVERPROPERTY(%' and command not like '%DECLARE @edition sysname% ' Find the longest running queries: SELECT * FROM sys. dm_pdw_exec_requests ORDER BY total_elapsed_time DESC

Check if queries are waiting for resources SELECT waits. session_id, waits. request_id, requests. command, requests. status, requests. start_time, waits. type, waits. object_name, waits. state FROM sys. dm_pdw_waits JOIN sys. dm_pdw_exec_requests ON waits. request_id=requests. request_id WHERE waits. request_id = 'QID 33188' ORDER BY waits. object_name, waits. object_type, waits. state;

Example – Setup Keys & Credentials § § § -- This is only one time for a database CREATE MASTER KEY; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Secure. Password'; § § -- This is required once per a new credential. CREATE CREDENTIAL Prod. Creds ON DATABASE WITH IDENTITY = ‘Storage. Name', Secret = 'Storage. Key. Value. Goes. Here';

Example – Data Source § § § § -- Create a data source CREATE EXTERNAL DATA SOURCE [Web. Log. Source] WITH ( TYPE = HADOOP, LOCATION = 'wasbs: //weblogs@sqldwdemo. blob. core. windows. net/', CREDENTIAL = Prod. Creds );

Example – File Format § § § § § -- Create the file format CREATE EXTERNAL FILE FORMAT [Web. Log. Format] WITH ( FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS ( FIELD_TERMINATOR = ‘|', DATE_FORMAT = 'yyyy-MM-dd HH: mm: ss' ) );

Example – External Table § § § § -- Create the external Table CREATE EXTERNAL TABLE [Web. Logs] ( url VARCHAR(50), event_date DATE, user_IP VARCHAR(50) ) WITH ( LOCATION='weblogs', DATA_SOURCE = Web. Log. Data, FILE_FORMAT = Web. Log. Format );

Symmetry § Integrated with Power BI, Azure Machine Learning, and Azure Data Factory Power BI Hadoop App Service. Azure SQL Database Azure SQL Data Warehouse Intelligent App Azure Machine Learning

Online Resources § SQLDW Landing page: https: //azure. microsoft. com/en-us/documentation/articles/sql-data-warehouseget-started-provision/ § § § Subscribe to fortnightly newsletter Channel 9 Sessions on Demand MVA Free Online Learning Henkvandervalk. com http: //aka. ms/technetnz http: //aka. ms/msdnnz http: //aka. ms/ch 9 nz http: //aka. ms/mva

Please review the event & sessions! § http: //www. sqlsaturday. com/434/eventeval. aspx § http: //www. sqlsaturday. com/434/sessions/sessionevaluation. aspx k n a h T 37 | PASS SQL Saturday – Holland - 2015 ! u yo

Appendix - Demo scripts ----------------------------------- SQLSATURDAY 434 - Create distributed CCI table ------------------------------------ Setup notes: --- Server: henks-sqldw. database. windows. net -----------------------------------select @@VERSION CREATE TABLE [dbo]. [lineitem 1] ( [l_shipdate] Date. Time NOT NULL , [l_orderkey] Big. Int NOT NULL , [l_discount] Float NOT NULL , [l_extendedprice] Float NOT NULL , [l_suppkey] Int NOT NULL , [l_quantity] Float NOT NULL , [l_returnflag] Char(1) NOT NULL , [l_partkey] Int NOT NULL , [l_linestatus] Char(1) NOT NULL , [l_tax] Float NOT NULL , [l_commitdate] Date. Time NOT NULL , [l_receiptdate] Date. Time NOT NULL , [l_shipmode] Char(10) NOT NULL , [l_linenumber] Int NOT NULL , [l_shipinstruct] Char(25) NOT NULL , [l_comment] Var. Char(132) NOT NULL ) WITH (CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = HASH([l_orderkey])) -------------------------------------------------------------- SQLSATURDAY 434 - Polybase CETAS reading from Flatfile on Azure Blob Storage --------------------------------------------------------------SELECT * FROM sys. external_tables SELECT * FROM sys. external_data_sources SELECT * FROM sys. external_file_formats CREATE EXTERNAL DATA SOURCE dws_wasb_storage WITH ( TYPE = HADOOP, LOCATION = 'wasbs: //SQLSAT 434_Container@henks_storage_account. blob. core. windows. net/' ); CREATE EXTERNAL FILE FORMAT pipe_delimited_text_file_format WITH ( FORMAT_TYPE = DELIMITEDTEXT ) CREATE EXTERNAL TABLE [stage]. [Fact. Sales. Stage_FULL_WASB] ( [Online. Sales. Key] bigint NULL, [Date. Key] datetime NULL, [Store. Key] int NULL, [Product. Key] int NULL, [Promotion. Key] int NULL, [Currency. Key] int NULL, [Customer. Key] int NULL, [Sales. Order. Number] varchar(28) COLLATE SQL_Latin 1_General_CP 1_CI_AS NULL, [Total. Cost] money NULL, [Unit. Price] money NULL, [ETLLoad. ID] int NULL, [Load. Date] datetime NULL, [Update. Date] datetime NULL ) WITH (LOCATION='Fact. txt', DATA_SOURCE = dws_wasb_storage, FILE_FORMAT = pipe_delimited_text_file_format, REJECT_TYPE = VALUE, REJECT_VALUE = 0 ) select count_big (*) from stage. [Fact. Sales. Stage_WASB]

Appendix – Demo scripts (cont. ) ---------------------------------- SQLSATURDAY 434 - Monitoring SQLDW ----------------------------------SELECT * from sys. tables -- List all tables with distribution types: SELECT a. name AS [Table Name], b. distribution_policy_desc AS [Distribution Type] FROM sys. tables AS a JOIN sys. pdw_table_distribution_properties AS b ON a. object_id = b. object_id --Data alignment over all distributions: DBCC PDW_SHOWSPACEUSED("dbo. Fact. Online. Sales"); -- Active Sessions and connections: : SELECT top (10) * FROM sys. dm_pdw_nodes_exec_connections; SELECT top (10) * FROM sys. dm_pdw_nodes_exec_sessions where login_time > '2015 -09 -26' -- Info on current connection: SELECT * FROM sys. dm_pdw_nodes_exec_connections AS c JOIN sys. dm_pdw_nodes_exec_sessions AS s ON c. session_id = s. session_id WHERE c. session_id = @@SPID; -- Query History: select command, * from sys. dm_pdw_exec_requests where submit_time > '2015 -09 -25 00: 00. 953' and command not like '%SELECT SERVERPROPERTY(%' and command not like '%DECLARE @edition sysname% ' --- Query Execution: -- Monitor running queries: SELECT * FROM sys. dm_pdw_exec_requests WHERE status = 'Running'; -- Find the longest running queries: SELECT * FROM sys. dm_pdw_exec_requests ORDER BY total_elapsed_time DESC; -- Sample : QID 152698 -- Zoom into MPP execution plan: -- Find the distribution run times for a SQL step. SELECT * FROM sys. dm_pdw_sql_requests WHERE request_id = 'QID 152698' --AND step_index = 2; -- Find the information about all the workers completing a Data Movement Step. -- (Rows processed/ node ID/ duration! -- USE Explain to see MPP execution plan: explain SELECT * FROM sys. dm_pdw_dms_workers WHERE request_id = 'QID 152698'