In this article, we will explore the concept of creating external tables in Azure Synapse Analytics with dedicated SQL pools. External tables allow us to access data stored outside of Azure Synapse Analytics without the need to physically copy the entire dataset to the local storage of the dedicated SQL pool.
Introduction
Azure Synapse Analytics offers two types of SQL pools – SQL on-demand pool and dedicated SQL pool. SQL on-demand pools do not have any local storage, so accessing data from different sources in-place is the only option. On the other hand, dedicated SQL pools provide a distributed parallel-processing engine with the ability to store massive data volumes locally. However, there may be scenarios where we need to reference data stored externally, outside of Azure Synapse Analytics. This is where external tables come into play.
Prerequisites
To create external tables in Azure Synapse Analytics with dedicated SQL pools, we need the following prerequisites:
- An Azure Synapse Analytics workspace
- A dedicated SQL pool
- An Azure Data Lake Storage Gen2 account with sample data
Creating External Tables
Assuming the prerequisites are in place, we can proceed with creating external tables in Azure Synapse Analytics. Here are the steps:
- Connect to the Synapse dedicated SQL pool instance using SQL Server Management Studio (SSMS).
- Create a master key in the database for encryption.
- Create a database scoped credential to connect to the Azure Data Lake Storage Gen2 account.
- Register a new data source and file format with the SQL pool.
- Create an external table that points to the data file stored in Azure Data Lake Storage Gen2.
Once the external table is created, we can test it by executing a select query on the table. If the credentials, data source, file format, and schema of the data file match with the external table, the query will return the expected results.
Benefits of External Tables
External tables in Azure Synapse Analytics offer several benefits:
- Fast and ad-hoc data access to data hosted outside of Azure Synapse Analytics
- No need to physically copy the entire dataset to the local storage of the dedicated SQL pool
- Ability to access data from different sources in-place
Conclusion
In this article, we explored the concept of creating external tables in Azure Synapse Analytics with dedicated SQL pools. External tables provide a convenient way to access data stored externally without the need for data duplication. By leveraging external tables, we can efficiently work with data hosted outside of Azure Synapse Analytics and perform ad-hoc data analysis.