PART- 11 Read external data using Serverless SQL pool

EXTERNAL TABLES---They store file level metadata about data files such as filename , version identifier and related propertied. Data is stored in files in an external Stage. External Tables are read only(no DML Operations)

--------------------------------------------------------------------------------------------------------------------
Depending on the type of the external data source, you can use two types of external tables:

Hadoop external tables that you can use to read and export data in various data formats.
Hadoop external tables are available in dedicated SQL pools, but they aren't available in serverless SQL pools.

Supported formats - Delimited/CSV, Parquet, ORC, Hive RC, and RC

Storage authentication - Storage Access Key(SAK), AAD passthrough, Managed identity, Custom application Azure AD identity

Native external tables that you can use to read and export data in various data formats such as CSV and Parquet. Native external tables are available in serverless SQL pools, and they are in preview in dedicated Synapse SQL pools.

Supported formats - Serverless SQL pool: Delimited/CSV, Parquet, and Delta Lake(preview) .Dedicated SQL pool: Parquet

Storage authentication - Shared Access Signature(SAS), AAD passthrough, Managed identity

-----------------------------------------------------------------------------------------------

External tables in dedicated SQL pool and serverless SQL pool

You can use external tables to:

Query Azure Blob Storage and Azure Data Lake Gen2 with Transact-SQL statements.

Store query results to files in Azure Blob Storage or Azure Data Lake Storage using CETAS

Import data from Azure Blob Storage and Azure Data Lake Storage and store it in a dedicated SQL pool (only Hadoop tables in dedicated pool).

You can create external tables in Synapse SQL pools via the following steps:

CREATE EXTERNAL DATA SOURCE to reference an external Azure storage and specify the credential that should be used to access the storage.

CREATE EXTERNAL FILE FORMAT to describe format of CSV or Parquet files.

CREATE EXTERNAL TABLE on top of the files placed on the data source with the same file format.