Glance of SQL Data Warehouse in China Azure

Key features:


 

  • Azure SQL Data Warehouse, storage and compute scale independently. You can dynamically deploy, grow, shrink, and even pause compute, taking advantage of best-in-class price/performance.
  • SQL Data Warehouse uses the power and familiarity of T-SQL to let you easily integrate query results across relational data in your data warehouse and non-relational data in Azure blob storage.
  • Using PolyBase, leverage Transact-SQL to query seamlessly across both relational data in a relational database and non-relational data in common Hadoop formats.
  • When compute is paused, you pay only for storage, leveraging our market-leading on-demand price per terabyte.
  • SQL DW only provide PowerShell interface to mooncake in Preview.

Backend architecture of SQL DW:

sqldwtp

Details of each module layer: https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-overview-what-is/

 

  • Pricing meter of SQL DW in global: (calculated by DWU and storage in use)

Pricing : https://azure.microsoft.com/en-us/pricing/details/sql-data-warehouse/

DWU definitions: https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-develop-concurrency/

 

  • Create SQL DW:

Portal is not available to Mooncake, so PowerShell should be best way for today.

Only PowerShell with version 1.0.2 works for the creating work because of ARM, PowerShell 1.0.2 is here https://github.com/Azure/azure-powershell/commit/71babdf502ae9e4d99e87757a481f7776328d63b

Guide for creating SQL DW: https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-get-started-provision-powershell/

 

Sample script:

Add-AzureRmAccount –$EnvironmentName AzureChinaCloud

Select-AzureRmSubscription -SubscriptionId 030***8-7b69-4c75-9870-0*****61

Get-AzureRmResourceGroup -Name EastRG1

New-AzureRmSqlDatabase -RequestedServiceObjectiveName “DW2000” -DatabaseName “testSQLDW” -ServerName “servername” -ResourceGroupName “Default-SQL-ChinaEast” -Edition “DataWarehouse”

 

  • Load data into SQL DW:

BCP: https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-load-with-bcp/

PolyBase: https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-load-with-bcp/

 

  • Performance and Scale of SQL DW:

https://azure.microsoft.com/en-us/documentation/articles/sql-data-warehouse-performance-scale/

 

  • Performance in loading data into SQL DW VS SQL DW (by BCP tool)

SQL DW to local file (around 68000 rows per second)

SQL Database to local file (around 65000 rows per second)

Local file to SQL DW (around 5400 rows per second)

Local file to SQL Database (around 2000 rows per second)

Leave a Reply

Your email address will not be published. Required fields are marked *