• Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About
Microsoft Data & AI

All Things Azure

Azure SQL Database Elastic Query

7/20/2018

0 Comments

 
Querying across cloud databases is supported in Azure through elastic queries (in preview).  You can read more about that here, but I thought a good talking point would be to briefly compare to elastic query to PolyBase.  You can read about PolyBase here.
Picture
​Note: At the righting of this blog post, an Azure Data Warehouse could not serve as a "principal" in an elastic query, but it can be the "secondary".

​
These two Azure features have similar setup.  They both require ...
  1. A master key
  2. A database scoped credential
  3. External table definitions
Elastic queries; however, allow you to not only SELECT from an external data source, but, you can also execute stored procedures.  In my thinking, the elastic query is as close as we are going to come to linked servers in Azure.  The definite downside is the defining of external tables in the principal.  These definitions must match the secondary schema name, table or view name.  The external table can omit columns, but it cannot rename or add columns.   This poses a bit of a deployment problem for the secondary when table definitions are changed -- the DDL changes must now be perpetuated in the principal external table definition.  The above images depicts vertical partitioning, but horizontal partitioning is worth a read.

Polybase is about linking to unstructured data, not another database.  That is truly the short version of the matter.  On both principal servers shown above the t-sql syntax is the same SELECT ColumnName FROM externalSchemaName.TableName.   It is not evident what feature you are using: Elastic Query or PolyBase.  Although you can JOIN an internal and external table together, this might fall under the heading "I can, but I won't".  It really depends on the size of your tables.  I personally do not feel that UNION ALL poses the same performance risk.

Conclusion: All said, elastic query is really a nice Azure feature which can solve data migration problems and an easy sharing of reference data.  It surely is not a replacement for ETL -- all things in moderation, my friend!  There remains a solid need for SSIS or ADFv2.   For every Azure offering there is an appropriate implementation place.
0 Comments



Leave a Reply.

    Categories

    All
    Agile Methodology
    Azure Blob Storage
    Azure Data Factory
    Azure Data Lake
    Azure Data Warehouse
    Azure SQL Database
    Cosmos DB
    Data Architecture
    Databricks
    Elastic Query
    External Tables
    Linked Services
    Migrating To The Cloud
    Parameters
    PolyBase
    Project Management

    RSS Feed

Powered by Create your own unique website with customizable templates.
  • Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About