DW(三):polybase基本理論

    PolyBase is a technology that accesses and combines(整合） both non-relational and relational data, all from within SQL Server. It allows you to run queries on external data in Hadoop or Azure blob storage. The queries are optimized(優化） to push computation to Hadoop

目錄：

• feature
• Performance
• cale-out groups
• use cases
• 參考資料

feature：

•  By simply using Transact-SQL (T-SQL) statements, you an import and export data back and forth（反覆、來回） between relational tables in SQL Server and non-relational data stored in Hadoop or Azure Blob Storage. You can also query the external data from within a T-SQL query and join it with relational data
• 
• Query data stored in Hadoop： Users are storing data in cost-effective distributed and scalable systems（可伸縮系統）, such as Hadoop. PolyBase makes it easy to query the data by using T-SQL
• Query data stored in Azure blob storage: Azure blob storage is a convenient（方便） place to store data for use by Azure services. PolyBase makes it easy to access the data by using T-SQL.
• Import data from Hadoop or Azure blob storage: Leverage the speed of Microsoft SQL's columnstore technology and analysis capabilities by importing data from Hadoop or Azure blob storage into relational tables. There is no need for a separate ETL or import tool
• Export data to Hadoop or Azure blob storage： Archive data to Hadoop or Azure blob storage to achieve cost-effective storage and keep it online for easy access
• Integrate with BI tools：Use PolyBase with Microsoft’s business intelligence and analysis stack, or use any third party tools that is compatible with SQL Server

Performance:

• Push computation to Hadoop：The query optimizer (查詢優化器）makes a cost-based decision to push computation to Hadoop when doing so will improve query performance. It uses statistics on external tables to make the cost-based decision. Pushing computation creates MapReduce jobs and leverages Hadoop's distributed computational resources.
• Scale compute resources:To improve query performance, you can use SQL Server PolyBase scale-out groups. This enables parallel data transfer between SQL Server instances and Hadoop nodes, and it adds compute resources for operating on the external data

cale-out groups:

• polybase 使用單一的sqlserver 實例來處理基於hadoop 或 Azure blobl Storage 的大量數據集時，可能會出現性能瓶頸， group feature允許用戶創建 sqlserver instance 集群來處理擴展的大數據集
• headnode:  The head node contains the SQL Server instance to which PolyBase queries are submitted. Each PolyBase group can have only one head node. A head node is a logical group of SQL Database Engine, PolyBase Engine and PolyBase Data Movement Service on the SQL Server instance
• Compute node：A compute node contains the SQL Server instance that assists with(幫助） scale-out query processing on external data. A compute node is a logical group of SQL Server and the PolyBase data movement service on the SQL Server instance. A PolyBase group can have multiple compute nodes
• Distributed query processing:

1. 1. PolyBase queries are submitted to the SQL Server on the head node. The part of the query that refers to external tables is handed-off （移交）to the PolyBase engine
   2. The PolyBase engine is the key component behind PolyBase queries. It parses the query on external data, generates the query plan and distributes the work to the data movement service on the compute nodes for execution. After completion of the work, it receives the results from the compute nodes and submits them to SQL Server for processing and returning to the client
   3. The PolyBase data movement service receives instructions(指令） from the PolyBase engine and transfers data between HDFS and SQL Server, and between SQL Server instances on the head and compute nodes

• Editions availability:

1. 1. After setup of SQL Server, the instance can be designated(指定） as either a head node or a compute node.
   2. The choice depends on which version of SQL Server PolyBase is running on.
   3. On an Enterprise edition installation, the instance can be designated either as head node or a compute node.
   4. On a Standard edition, the instance can only be designated as a compute node

use cases

• polybase primary use cases 如下圖：
• 
• （a) query submitted to PDW requires “unstructured” data from Hadoop for its execution. This might be as simple as a scan whose input is an HDFS file or a join between a file in HDFS and a table in PDW. The output in this case flows back to the user or application program that submitted the query
• (b)  is similar except that the output of the query is materialized as an output file in HDFS, where it might be consumed by either a subsequent PDW query or by a MapReduce job. Polybase, when appropriate,will translate operations on HDFS-resident data into MapReduce jobs and push those jobs to Hadoop for execution in order to minimize the data imported from HDFS into PDW and maximize the use of Hadoop cluster resources. With Hadoop 2.0 we envision supporting a variety of techniques for processing joins that involve HDFS and PDW resident tables, including, for example, the use of semi-join techniques.

pushdown:

• 和linked servers一樣，PolyBase會設法將儘量多的處理工作轉移到源資料庫。也就是說，當查詢Hadoop或Azure blob存儲時，會生成恰當的map/reduce操作。這就是所謂的“下推（pushdown）”，開發人員需要瞭解的下推限制：
  1. 用於數值、日期、時間值的二元比較操作符（<、>、=、!=、<>、>=、<=）
  2. 算術運算符（ +、-、*、/、%）
  3. 邏輯運算符（AND、OR）
  4. 一元運算符（NOT、IS NULL、IS NOT NULL）
  5. BETWEEN、NOT、IN和LIKE操作符可能也可以下推。這取決於查詢優化器如何將它們改寫為一系列使用基本關係運算符的語句
  6. 下推可以通過OPTION (FORCE EXTERNALPUSHDOWN)顯式啟用，或通過OPTION (DISABLE EXTERNALPUSHDOWN)顯式禁用

參考資料：

• jdk: http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html
• 註意：英文版的window2012 OS 不支持安裝 sqlserver2016中文版
•