Melhores praticas para carga de dados para o Azure Synapse Analytics - SQL Pool

Nessa sessão apresentei um overview rapido do novo Azure Synapse Analytics com foco nas melhores praticas para carga de dados para o Azure Synapse Analytics - SQL Pool. Você irá entender os porquês de utilizar ELT em vez de ETL em ambiente de Big Data no Azure por meio de uma arquitetura escalável, com computação flexivel e recursos de armazenamento otimizados.

Transcript

1. Melhores práticas para cargas de dados SQL Pool Azure Synapse Analytics Sidney Cirqueira
2. Explore your PASS community Free online webinar events Connect with the global data community Local user groups around the world Online special interest user groups Learning on-demand and delivered to you Get involved Own your career with interactive learning built by community and guided by data experts. Get involved. Get ahead. .org
3. Sidney Cirqueira ✓ Data Analyst – Lanlink; ✓ Microsoft Certified Trainer; ✓ Microsoft Learn Student Ambassador - Beta; ✓ Post Graduation in Big Data & Machine Learning - FASAM; ✓ Experience focused on Data Engineering & Artificial Intelligence on Azure;
4. ✓ What is Azure Synapse Analytics? ✓ Synapse Analytics (formerly SQL DW) – Architecture ✓ Table Designer ✓ Options for loading ✓ Best practices ✓ Demo ✓ Questions ✓ References Agenda:
5. Azure Synapse Analytics Limitless analytics service with unmatched time to insight Platform Azure Data Lake Storage Common Data Model Enterprise Security Optimized for Analytics METASTORE SECURITY MANAGEMENT MONITORING DATA INTEGRATION Analytics Runtimes PROVISIONED ON-DEMAND Form Factors SQL Languages Python .NET Java Scala R Experience Synapse Analytics Studio Artificial Intelligence / Machine Learning / Internet of Things Intelligent Apps / Business Intelligence METASTORE SECURITY MANAGEMENT MONITORING
6. Azure Data Lake Storage Store Azure Synapse Analytics Key Component Of Modern Data warehouse Power BI Azure Machine Learning
7. Azure Synapse Analytics – SQL Pools
8. Data Hub – Databases Explore the different kinds of databases that exist in a workspace. SQL pool SQL on-demand Spark
9. Azure SQL Data Warehouse Architecture Control Node Compute Node Compute Node Compute Node Compute Node SQL DB SQL DB SQL DB SQL DB Blob storage [WASB(S)] Compute Scale compute up or down when required (SLA <= 60 seconds). Pause, Resume, Stop, Start. Storage Add\Load data to WASB(S) without incurring compute costs Massively Parallel Processing (MPP) Engine Azure Infrastructure and Storage 100 DWU < > 30000 DWU Storage and Compute are de-coupled, enabling a true elastic service and separate charging for both compute and storage Application or User connection Data Loading (SSIS, REST, OLE, ADO, ODBC, WebHDFS, AZCopy, PS) DM S DM S DM S DM S DM S DMS (Data Movement Service) executes across all database nodes
10. Row store & Column store ROW STORE COLUMN STORE
11. Creating tables CREATE TABLE [dbo].[DimStore] ( [StoreKey] int NOT NULL , [GeographyKey] int NOT NULL , [StoreName] nvarchar(100) NOT NULL , [StoreType] nvarchar(15) NULL , [StoreDescription] nvarchar(300) NOT NULL , [Status] nvarchar(20) NOT NULL , [OpenDate] datetime NOT NULL , [CloseDate] datetime NULL , [ETLLoadID] int NULL , [LoadDate] datetime NULL , [UpdateDate] datetime NULL ) WITH ( CLUSTERED INDEX([StoreKey]) , DISTRIBUTION = REPLICATE ) ; CREATE TABLE [dbo].[FactOnlineSales] ( [OnlineSalesKey] int NOT NULL , [DateKey] datetime NOT NULL , [StoreKey] int NOT NULL , [ProductKey] int NOT NULL , [PromotionKey] int NOT NULL , [CurrencyKey] int NOT NULL , [CustomerKey] int NOT NULL , [SalesOrderNumber] nvarchar(20) NOT NULL , [SalesOrderLineNumber] int NULL , [SalesQuantity] int NOT NULL , [SalesAmount] money NOT NULL ) WITH ( CLUSTERED COLUMNSTORE INDEX , DISTRIBUTION = HASH([ProductKey]) ) ;
12. Table design
13. Table design
14. Creating tables CREATE TABLE [build].[FactOnlineSales] ( [OnlineSalesKey] int NOT NULL , [DateKey] datetime NOT NULL , [StoreKey] int NOT NULL , [ProductKey] int NOT NULL , [PromotionKey] int NOT NULL , [CurrencyKey] int NOT NULL , [CustomerKey] int NOT NULL , [SalesOrderNumber] nvarchar(20) NOT NULL , [SalesOrderLineNumber] int NULL , [SalesQuantity] int NOT NULL , [SalesAmount] money NOT NULL ) WITH ( CLUSTERED COLUMNSTORE INDEX , DISTRIBUTION = ROUND_ROBIN ) ;
15. Resource class Static Dynamic
16. Options For loading To load data, you can use any of these loading options: ✓ COPY statement ✓ PolyBase with T-SQL ✓ PolyBase and COPY statement with Azure Data Factory (ADF) ✓ PolyBase with SSIS ✓ PolyBase with Azure Databricks
17. Best practices for loading large amount of data PolyBase benefits Variety of file formats PolyBase supports a variety of file formats including Parquet, RC, ORC and Gzip files. Azure Data Factory support Azure Data Factory also supports PolyBase loads and can achieve similar performance to running PolyBase manually Leverages MPP architecture PolyBase is designed to leverage the MPP (Massively Parallel Processing) architecture of Azure Synapse Analytics and will therefore load and export data magnitudes faster than any other tool.
18. Create External Table - Using T-SQL Overview Creates an external table and then exports results of the Select statement. These operations will import data into the database for the duration of the query Steps: 1. Create Master Key 2. Create Credentials 3. Create External Data Source 4. Create External Data Format 5. Create External Table
19. COPY command Overview Copies data from source to destination Benefits Retrieves data from all files from the folder and all its subfolders. Supports multiple locations from the same storage account, separated by comma Supports Azure Data Lake Storage (ADLS) Gen 2 and Azure Blob Storage. Supports CSV, PARQUET, ORC file formats
20. Best practices To load data, you can use any of these Best Practices ✓ Preparing data in Azure Storage ✓ Running loads with enough compute ✓ Creating a loading user ✓ Loading to a staging table ✓ Loading to a columnstore index ✓ Creating statistics after the load ✓ Rotate storage keys (PolyBase)
21. Demo
22. • https://docs.microsoft.com/en-us/azure/synapse-analytics/overview-what-is • https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/guidance- for-loading-data • https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/design-elt- data-loading • https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/resource- classes-for-workload-management • https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/guidance- for-loading-data#loading-to-a-staging-table Referências
23. Obrigado! =D sidney.cirqueira@studentambassadors.com https://github.com/sidneyocirqueira/azure-synapse-analytics mvpconf.com.br/2020 Voucher 10% de desconto: E61C227470 M https://medium.com/@sidneyocirqueira