Can someone please let me know below all operation can we do on External Table on Serverless SQL Pool

Question

Can someone please let me know below all operations can be done on the External Table on the Serverless SQL Pool?

DDL

DML

Joins (Based on joins condition data has to place another external table)

Sub-Queries

Performance techniques

Majorly trying to understand Do's and Don'ts on External Table with serverless SQL pool.

This question is related to the following Learning Module

Answer 1

Hi Sai Kishore Amara,

In Azure Synapse Analytics, the Serverless SQL Pool provides restricted support for operations on External Tables in comparison to Dedicated SQL Pools. Here's an analysis of what is and isn't feasible with External Tables in Serverless SQL Pools:

Data Definition Language (DDL):

You can create, modify, and remove External Tables.

Data Manipulation Language (DML):

Limited support exists for DML operations. SELECT statements enable querying data from External Tables, but INSERT, UPDATE, or DELETE operations cannot be directly executed.

Joins:

Regular join syntax allows for joining External Tables with other External or Managed Tables.

Sub-Queries:

Sub-queries can be employed with External Tables to filter or manipulate data within queries.

Performance Techniques:

Indexing: External Tables lack support for traditional indexing; however, enhancing performance is achievable through partitioning and clustering on storage platforms like Azure Data Lake Storage.

Data Skew Management: Ensuring equitable data distribution across partitions mitigates the impact of data skew on query performance.

Query Optimization: Employing appropriate file formats, partition pruning, and minimizing data movement aids in optimizing query performance.

Do’s:

1.Organize data in storage to align with query patterns.

2.Employ efficient file formats such as Parquet or ORC for enhanced performance.

Don’ts:

1.Attempting DML operations (INSERT, UPDATE, DELETE) directly on External Tables.

2.Anticipating the same performance level as Dedicated SQL Pools due to limited resource control.

By adhering to these guidelines and leveraging the supported operations, effective utilization of External Tables within a Serverless SQL Pool environment in Azure Synapse Analytics is achievable.

If the provided information proves beneficial, please indicate your approval by clicking the "Upvote" and "Accept Answer" button.

Thank you.