SlideServe
Télécharger Obtenir Pro
Menu
Accueil Pro
Préférences
ApparenceLight Aide et commentaires
© SlideServe · v2026

SQL Server Polybase: Data Virtualization Made Easy

ikeb avatar
ikeb
22 diapositives · 527 Views · Dec 19, 2019

Explore the capabilities of SQL Server Polybase for data virtualization, including setup, usage, and integration with various data sources like HDFS, Cosmos DB, and more. Learn how to build a data hub with SQL Server Polybase effortlessly. Enhance your understanding of Polybase's architecture, performance, and data movement service. Dive into modern data virtualization concepts and best practices with SQL Server 2019 and Big Data Clusters.

SQL Server Polybase: Data Virtualization Made Easy - slide 1
01 / 22SQL Server Polybase: Data Virtualization Made Easy
1 / 17

Télécharger la présentation

SQL Server Polybase: Data Virtualization Made Easy
Une image / un lien ci-dessous est fourni (tel quel) pour télécharger la présentation

Politique de téléchargement : le contenu du site vous est fourni TEL QUEL pour votre information et votre usage personnel ; il ne peut être vendu, concédé sous licence ni partagé sur d’autres sites sans l’accord de son auteur.Le contenu vous est fourni TEL QUEL, pour votre information et votre usage personnel uniquement. Téléchargez la présentation en cliquant sur ce lien. Lors du téléchargement, si vous ne parvenez pas à télécharger une présentation, l’éditeur a peut-être supprimé le fichier de son serveur.Pendant le téléchargement, si vous ne parvenez pas à obtenir une présentation, le fichier a peut-être été supprimé par l’éditeur.

F I N

Plus de contenu associé

Transcription de la présentation

  1. Inside SQL Server Polybase Bob Ward, Principal Architect, Microsoft https://aka.ms/bobsql https://aka.ms/bobwardms https://aka.ms/bobsqldemos

  2. Session learning objectives At the end of this session, you should be better able to… • Understand what is Polybase and how to use it. • Understand how Polybase works to know its capabilities for data virtualization • Learn how to use Polybase to build a data hub with SQL Server

  3. “It’s all about Data Virtualization” What is SQL Server Polybase? Distributed compute engine integrated with SQL Server Query data where it lives using T-SQL Distributed, scalable query performance Manual/deploy with SQL Server Auto deploy/optimize with Big Data Clusters Analytics Apps T-SQL SQL Server PolyBase external tables NoSQL ODBC Relational databases Big data Excel Cosmos DB HDFS Intelligence over all data

  4. The Journey to Data Virtualization in SQL Server 2019 Linux support is coming David Dewitt Rimma Nehme SQL Server 2019 is in Preview Some details subject to change

  5. Using Polybase in SQL Server: T-SQL EXTERNAL TABLE Only for HDFS Login and password Not simple without BDC WWI SQL And join to any other table or external table metadata Results streamed HDFS INSERT only for HDFS Data lives here Cosmos DB

  6. control and execution The SQL Server Polybase Architecture Data flow All editions EE only All editions “Compute” node “Head” node “Compute” node DW dbs DW dbs DW dbs SQL Engine SQL Engine SQL Engine Need more scale? Add compute nodes tempdb tempdb tempdb Polybase Engine Polybase Engine Polybase Engine ……... Polybase Data Movement Service Polybase Data Movement Service Polybase Data Movement Service Scan or pushdown Scale out with partitions shuffle mpdwsvc.exe Your data sources HDFS Cosmos DB

  7. DemoDive into Polybase

  8. SQL Server Polybase and Hadoop

  9. Polybase and Other Connectors \binn\Polybase\ODBC Drivers LOCATION “string” in EXTERNAL DATA SOURCE Built-in Connectors (use ODBC) sqlserver oracle teradata mongodb SQL Server, Azure SQL Database, Azure SQL Data Warehouse No client software install required Scale out with partitions MongoDB or CosmosDB (using MongoDB API) 64bit ODBC 3.0+ compliant ODBC Connector odbc You install the driver Ex. SAP HANA (HDBCODBC Driver)

  10. Polybase as a Semantic Layer

  11. External Tables vs Linked Servers * Insert into HDFS allowed

  12. SQL Server 2019: Data Virtualization Modern StockItems Legacy Suppliers Mobile App Orders WWI SQL SQL Server 2019 WideWorldimporters Accounts Receivable Customers from Acquisition Order Reviews

  13. DemoData Virtualization around the WideWorld

  14. Lessons Learned COLLATE required for character columns. Compat may be required ORACLE case sensitive for LOCATION = <instance>.<schema>.<table> LOCATION for SQL Server is <db>.<schema>.<table> EXTERNAL tables don’t support these types (they may be more) VARCHAR(MAX) GEOGRAPHY Computed Columns JSON MongoDB (CosmosDB) observations Be careful of types in your document LOCATION = <database>.<collection> Need to dive in EXTERNAL TABLE compatibility (Ex. Row Level Security)

  15. SQL Server 2019 Big Data Clusters and Polybase The Problem Customers want to integrate with Big Data and other data sources easily Polybase is not simple to setup, configure, maintain, and scale elastically Customers may not have a Hadoop cluster or want to build one Polybase “classic” (MapReduce) could be better The Solution Kubernetes and containers to deploy and scale elastically Everything pre-installed including HDFS cluster Build a control plane to help manage and monitor Enhance SQL Server to read from HDFS natively Provide a data mart for cached results Introduce Spark and Notebooks for Data scientists

  16. mpdwsvc.exe uses SQLPAL Polybase in SQL Server 2019 Big Data Clusters Control Plane • Controller Svc • Azure FSM Engine • Kibana • Grafana Customapps SQL BI Analytics • Elastic Search • InfluxDB • Configuration Store (SQL Server) Polybase head node In Linux container Cluster Polybase compute nodes In Linux containers SQL Server master instance Spark Spark Spark SQL Server SQL Server SQL Server Compute pool Compute pool Directly read from HDFS Compute pool • SQL Compute Node • SQL Compute Node • SQL Compute Node • SQL Compute Node • SQL Compute Node • … • HDFS Data Node • HDFS Data Node • HDFS Data Node “Built-in” Data Sources Storage pool Data mart External data sources • … MapReduce Not used • SQL Data Node • SQL Data Node • Storage • Storage Kubernetes pod IoT data Persistent storage Node Node Node Node Node Node Node

  17. Storage and Data Pools Data Sources CREATE EXTERNAL DATA SOURCE SqlDataPool WITH (LOCATION = 'sqldatapool://service-mssql-controller:8080/datapools/default’); CREATE EXTERNAL TABLE… WITH ( DATA_SOURCE = SqlDataPool, DISTRIBUTION = ROUND_ROBIN ); CREATE EXTERNAL DATA SOURCE SqlStoragePool WITH (LOCATION = 'sqlhdfs://service-mssql-controller:8080’); CREATE EXTERNAL TABLE … WITH ( DATA_SOURCE = SqlStoragePool, LOCATION = '/clickstream_data', FILE_FORMAT = csv_file ); Preinstalled in model of Master Instance mssql-controller REST endpoint

  18. Azure Data Studio and Data Virtualization Using PROSE for intelligent import and schema detection

  19. Session takeaways • Polybase = Data Virtualization = Reduced Need for ETL • Polybase provides distributed read scale performance • Big Data Clusters automate the deployment of Polybase • Download and try it yourself • Sign up for EAP for SQL Server 2019 Big Data Clusters

  20. Questions? aka.ms/SQLBits19

  21. https://aka.ms/bobsql https://aka.ms/bobwardms https://aka.ms/bobsqldemos Session resources SQL Server 2019 Polybase documentation SQL Server Big Data Clusters documentation Polybase demos on GitHub Azure SQL Database Elastic Query documentation Loading data into Azure SQL Data Warehouse with Polybase