En lugar de aprovisionar grandes recursos para tu DW, Azure ofrece una versión especial de SQL Server como DataWarehouse. Si está familiarizado con el appliance APS, SQLDW en Azure viene a ser su versión como servicio. Usted crea su DW desde el portal de Azure y ya puede empezar a cargar datos y explotarlos. En esta sesión veremos cómo habilitar el servicio y cómo empezar a explotar SQLDW como tu DW en la nube.
Presentación realizada en el SolidQ Summit por: Enrique Catalá
[slideshare id=62537863&doc=datawarehousecomoservicioenazuresqldw-160530132318&h=495&w=595]1. #SQSummit Data warehouse como servicio en Azure (SQLDW) Enrique Catalá Bañuls Mentor ecatala@solidq.com
2. Contenido de la sesión • ¿Qué es SQLDW? • Topología de SQLDW • Escalabilidad y DWUs • Cómo cargar datos en SQLDW • Limitaciones • Experiencias en proyectos reales
3. Soluciones data warehousing de MS Microsoft SQL Server Entorno escalable SMP y NUMA en cualquier hardware OnPremise y Azure IaaS Ideal para datamarts desde pequeños a “medianos” Solo software 10s de TB Microsoft Analytics Platform System (APS) Appliance para entornos datawarehouse de alto rendimiento MPP Solo OnPremise Ideal para alto rendimiento y alta escalabilidad de DW Applicance (SW y HW) 10s de TB – 6 PB (PDW) 24TB – 1.2 PB (Hadoop) Microsoft Azure SQL Data Warehouse (SQLDW) Cloud data warehouse para entornosd de alto rendimiento MPP Solo cloud Ideal para alto rendimiento, alta escalabilidad en cloud Solución completa PaaS 10s de TB – PBs
4. ¿Qué es SQLDW? PaaS Escalable horizontal Arquitectura MPP Integrado (SSDT, TSQL) Hibrido (Polybase) Coste efectivo
5. Qué es el MPP?
6. Contenido de la sesión • ¿Qué es SQLDW? • Topología de SQLDW • Escalabilidad y DWUs • Cómo cargar datos en SQLDW • Limitaciones • Experiencias en proyectos reales
7. Cerebro vs Músculo (divide y vencerás)
8. Control Node (cerebro) • Acepta requests de usuario • Interpreta la petición y la escala • Optimiza la petición • Orquestra acciones • Realiza la consolidación final • Devuelve los resultados Un único cerebro Requiere un motor relacional distribuido
9. Compute node (Músculo) • Realiza computación extrema • Acepta requests del “cerebro” • Es un entorno SMP altamente tuneado • Pensado para peticiones del “cerebro” • Es el que finalmente accede al dato Muchos elementos “músculo” No podemos conectar a él
10. Distribución de datos Pero muchos nodos de procesamiento! Pequeñas queries Divide y vencerás Bueno para la escalabilidad Introduce sobrecostes
11. Topología Azure SQL Data Warehouse
12. Contenido de la sesión • ¿Qué es SQLDW? • Topología de SQLDW • Escalabilidad y DWUs • Cómo cargar datos en SQLDW • Limitaciones • Experiencias en proyectos reales
13. Escalabilidad y DWUs • DWU = Data Warehouse Unit • Load rate: records/seg metiendo datos • Scan rate: records/seg leyendo datos (CPU+IO) • Escalabilidad lineal
14. Escalabilidad y DWUs
15. Escalabilidad y DWUs
16. Escalabilidad y DWUs • Concurrent query • Petición con independencia de si es serie o paralela • Concurrency slot • Slot de hilos disponibles (mas slots, mas paralelismo)
17. Arquitectura para SQLDW con DWU100
18. Arquitectura para SQLDW con DWU600
19. Arquitectura para SQLDW en Pausa
20. Cómo escalar • Azure Portal Slider • T-SQL Command • Powershell cmdlet • Y esperar unos pocos minutos Documentación en http://aka.ms/sqldw
21. Contenido de la sesión • ¿Qué es SQLDW? • Topología de SQLDW • Escalabilidad y DWUs • Cómo cargar datos en SQLDW • Limitaciones • Experiencias en proyectos reales
22. Arquitectura de carga – SSIS ó BCP (1)
23. Arquitectura de carga – SSIS ó BCP (2)
24. Arquitectura de carga – SSIS ó BCP (2)
25. Arquitectura de carga – Polybase
26. Arquitectura de carga – Polybase –Import data from CREATE TABLE Prod_DB.dbo.TargetTable_EXCH WITH (Distribution=HASH (ColA), CLUSTERED INDEX (ColB), PARTITION (ColC) RANGE RIGHT ON VALUES (xxx, xxx, xxx … )) AS SELECT poly.PK_Field, poly.ColA, poly.ColB … FROM Prod_DB.dbo.ExternalTable_In_Polybase poly
27. BCP bcp {database}.dbo.lineitem_cci in C:Toolboxdbgenlineitem.tbl -c -U username -P password -S xxxxxxxx.database.windows.net -q -t”|” -r n -w
28. SSIS Nuevo destino SQL PDW • SSDT 2012 • SSDT 2014 • SSDT 2015
29. SQL Server PDW Destination Editor
30. Rendimiento de carga BCP SSIS Polybase PolyBase BCP SQLBulkCopy/ADF SSIS Load Rate Rate increase as you increase DWU Yes No No No Rate increase as you add concurrent load No Yes Yes Yes FASTEST============>>>>>>>>>>SLOWEST Pero si que se incrementa en Polybase cuando tenemos muchos ficheros por carpeta
31. Contenido de la sesión • ¿Qué es SQLDW? • Topología de SQLDW • Escalabilidad y DWUs • Cómo cargar datos en SQLDW • Limitaciones • Experiencias en proyectos reales
32. Limitaciones (mínimas) • No se crean automáticamente estadísticas (ni se mantienen) • Pensado para cargas con Polybase • Cuello de botella en nodo de computación • Las vistas son metadata only • No schemabinding • No se pueden actualizar datos de las tablas base • No hay indexed views
33. Limitaciones (medianas) • Tamaño de fila máximo 32k • Es transaccional pero: • Solo READ UNCOMMITED • No hay nested transactions • No hay distributed transactions
34. Limitaciones (críticas) • No se soporta • Identity, PK, FK, Checks, UNIQUE, computed columns, sequences, triggers, sparse • No se soportan todos los tipos de datos • varchar(max), nvarchar(max),uniqueidentifier text, image, timestamp,xml,geometry,geography, hierarchyid, sql_variant, table NOTA: Todos los tipos de datos tienen workaround, la mayoría valen con varbinary(8000)
35. Limitaciones • Tamaño de una transacción (ojo aquí)
36. Limitaciones… • ANSI joins on updates • ANSI joins on deletes • merge statement • cross-database joins • cursors • SELECT..INTO • INSERT..EXEC • output clause • inline user-defined functions • multi-statement functions • common table expressions • recursive common table expressions (CTE) • CLR functions and procedures • $partition function • table variables • table value parameters • distributed transactions • commit / rollback work • save transaction • execution contexts (EXECUTE AS) • group by clause with rollup / cube / grouping sets options • nesting levels beyond 8 • updating through views • use of select for variable assignment • no MAX data type for dynamic SQL strings
37. ETL vs ELT • Extract • Transform • Load ETL SERVER DATA WAREHOUSE SERVER EXTRACT TRANSFORM LOAD ELT SERVER DATA WAREHOUSE SERVER EXTRACT TRANSFORM LOAD ELTETL IN-MEMORY TRANSFORMS FEWER CPUS IN-DB MORE CPUS GOOD SORT
38. Mapeo de datos
39. Contenido de la sesión • ¿Qué es SQLDW? • Topología de SQLDW • Escalabilidad y DWUs • Cómo cargar datos en SQLDW • Limitaciones • Experiencias en proyectos reales
40. Fases del Proyecto • Carga de datos en SQLDW • Ajustes en modelo • Patrón para subir de OnPrem a SQLDW • Marcas de cambios • Cambio de ETL a ELT (si tenemos solución BI) • Traducir SSIS a Reports (ya no habrán SSIS) • Pruebas y mediciones • 500 y 1000 DTUs como mínimo probarlas
41. Carga de Datos en SQLDW • Subir backups a Azure • Restaurar backups en Azure • Exportar datos a CSV • Subir CSVs a Azure Storage • Cargar CSVs en SQLDW
42. Patrón para subir OnPrem a SQLDW • Marcas en tablas origen (timestamp) • En SQL IaaS Azure (staging) • Subir datos desde OnPrem (Staging) • Comparar marcas OnPrem vs SQLDW • Cargar cambios en SQLDW
43. Ajustes en Reports • Convertir los SSIS a Query SQLDW • Queries complejas • 24 horas convertir SSIS a query (generalmente)
44. Pruebas y Mediciones Operativa Fase MBps Nº hilos Tamaño (GB) Tiempo necesario horas Subir Backups Carga – Restaurar Backups Carga 16.00 Generacion CSV Carga 20 1 975 13.88 Subiendo CSV Carga 55 1 975 5.05 Cargando CSV Carga 20 8 304 1.74 Sincronización Sync – Query Excel Excel 500 DTW 120 secs Query Excel Excel 1000 DTW 75 secs Nota. Margen de mejora. Máquinas A6 (4 cores) sin almacenamiento Premium ni múltiples discos
45. Estimaciónes para particionado Bytes_per_row: 250 #_rows: 60,000,000,000 B_to_GB_factor: 1,000,000,000 Distribution_count: 60 Compression_factor: 5 (typical) Partition_count: 36 (monthly over three years) Max_rows_per_rowgroup rows_per_distribution = #_rows / distribution_count rows_per_partition = rows_per_distribution / partition_count max_rowgroups_per_partition = partition_count / max_rows_per_rowgroup Uncompressed_table_size_b = bytes_per_row x # rows Uncompressed_table_size_gb = Uncompressed_table_size_b / B_to_GB_factor Uncompressed_table_distribution_size_gb = uncompressed_table_size_gb/distribution_count Compressed_distribution_size_gb = Uncompressed_table_distribution_size_gb /compression_factor Compressed_partition_size_gb = Compressed_distribution_size_gb / partition_count
46. Precios (abril 2016) Nivel Hora Mensual Conc. Query Conc. Slot 100 DWU 0,66 € 492,00 € 32 4 500 DWU 3,31 € 2.459,00 € 32 20 1000 DWU 6,61 € 4.919,00 € 32 40 1500 DWU 9,92 € 7.378,00 € 32 60 2000 DWU 12,22 € 9.838,00 € 32 80 • Recuerda SIEMPRE pausar la implementación cuando no la uses • Puedes seguir subiendo datos si usas Polybase
47. Lecciones aprendidas en proyectos reales 1. Diseña tus objetos pensando en distribución de datos 2. Crea siempre estadísticas tras el CREATE TABLE 3. Actualiza siempre estadísticas tras una carga 4. Particiona tus objetos 5. Comienza con DTW 300 para hacer mediciones
48. Lecciones aprendidas en proyectos reales 1. Utiliza BIML 2. Carga mediante Polybase 3. Crea tus objetos mediante plantillas T4 4. Utiliza un separador de texto compuesto “$|x” 5. Tu máquina de desarrollo en Azure • Subiendo datos ganarás tiempo en cada prueba
49. Conclusión: ¿Necesito entonces SQLDW? • Si el dinero no es problema • Si ya había estado valorando la opción de APS • Si no tengo una solución de BI • O si no quiero invertir más en BI y quiero ir por la vía del Hierro • Si tengo tanto dato que es inviable hacerlo con solución de BI • Necesito saltarme la fase de transformación y ver cuanto antes el dato
50. También puedes preguntar tus dudas con el hashtag #SQSummit en Twitter ADAPTIVE BI FRAMEWORK Te ayudaremos a mejorar la velocidad de desarrollo de tu plataforma de analítica de negocio basada en nuestra experiencia: •Diseña antes de construir •Automatización de procesos por ETL •Servicios de mentoring para ayudarte a conseguir mejores prácticas para la construcción de procesos específicos y plataformas de analítica de negocio •Muy fácil de mantener SOLIDQ FLEX SERVICES Con SolidQ Flex Services evitarás sustos, consiguiendo que tus sistemas sean estables. Desde una solución sencilla de monitorización, hasta un servicio de atención de incidencias 24/7, mantenimiento proactivo, resolución de problemas y línea de soporte. Todo con un coste fijo mensual… y tú dedica el tiempo a las cosas importantes. ¡Gracias!
