Create Azure Synapse Analytics Serverless External Table

Azure Synapse Analytics serverless external tables allow you to query external data assets without moving them from your Data Lake. In this post, I’ll show you how to create an Azure Synapse Analytics Serverless External Table so you can take advantage of the information in your Data Lake. 

Why should I use Azure Synapse Analytics Serverless External Tables? 

To begin, by using Azure Synapse Analytics Serverless external tables, you can query all the information in your Data Lake without the need to build an additional data movement solution. The supported file formats are Delimited/CSV, Parquet, and Delta Lake. 

Other benefits include: 

• Combining external tables with data warehouse tables. Modelling in a data warehouse environment is a time-consuming activity. If you need to build a report, you can start prototyping by creating external tables (without moving data or modelling it) and combine these tables with data warehouse tables. 
• Using Azure Synapse Analytics serverless tables for real-time workloads, without needing to move data to your dedicated SQL Pool. You can also look at pausing the dedicated SQL Pool! 

Create Azure Synapse Analytics Serverless External Table 

Now, let’s create an Azure Synapse Analytics Serverless External Table. 

In the data hub, and without writing any code, you can right-click on a file and select the option to create an external table.   

Next, select the database and the name of the table. I always suggest creating an external table by selecting “Using SQL Script”. 

Once you have the script, you’ll these options: 

1. The serverless option (Built-In) that you use to create the table. 

2. The serverless database where you create an Azure Synapse Analytics external table. 

3. The path that your serverless external table is pointing to.  

Wildcards in Azure Synapse Analytics Serverless External Tables 

One of the main reasons why it’s good to see the script is because you can modify it. In the following picture, I am using wildcards. In this case * is used to browse all the sales files in multiple folders. 

Once you have created a table, it will be available in your Azure Synapse Analytics serverless database. 

 
Access and Query Azure Synapse Analytics External Table 

To access an Azure Synapse Analytics external table, you need to get a connection string (server name) that you can use in your client tools (for example, Power BI). 

Get information from the Azure Synapse Analytics Manage Hub 

When querying an Azure Synapse Analytics external table, you pay per data that you read. Find more information at Cost Control Azure Synapse Analytics Serverless. 

Copy the connections string as described below. 

Now, let’s try to connect to the table using Power BI. Click Get Data and select the Azure Synapse Analytics connector. 

Include the connection string, the database, and query if you’d like to be more specific. Here I’m using Direct Query, but you can also use Import Mode.  

You can preview the information on your table and start working with it. 

Permissions for Azure Synapse Analytics External Tables 

Access to the tables is controlled by Azure Storage Account and files that you are using in your external table.  

If you don’t give the correct access to your Azure Storage Account / Azure Data Lake, you will not be able to access any information. 

An example of the errors you might get if access isn’t granted. 

External table ‘dbo’ is not accessible because the content of the directory cannot be listed. 

Potential conversion error while reading VARCHAR column ‘AccountDescription’ from UTF8 encoded text. Change database collation to a UTF8 collation or specify explicit column schema in WITH clause and assign UTF8 collation to VARCHAR columns. Potential conversion error while reading VARCHAR column ‘AccountType’ from UTF8 encoded text. Change database collation to a UTF8 collation or specify explicit column schema in WITH clause and assign UTF8 collation to VARCHAR columns. Potential conversion error while reading VARCHAR column ‘Operator’ from UTF8 encoded text. Change database collation to a UTF8 collation or specify explicit column schema in WITH clause and assign UTF8 collation to VARCHAR columns. Potential conversion error while reading VARCHAR column ‘CustomMembers’ from UTF8 encoded text. Change database collation to a UTF8 collation or specify explicit column schema in WITH clause and assign UTF8 collation to VARCHAR columns. Potential conversion error while reading VARCHAR column ‘ValueType’ from UTF8 encoded text. Change database collation to a UTF8 collation or specify explicit column schema in WITH clause and assign UTF8 collation to VARCHAR columns. Potential conversion error while reading VARCHAR column ‘CustomMemberOptions’ from UTF8 encoded text. Change database collation to a UTF8 collation or specify explicit column schema in WITH clause and assign UTF8 collation to VARCHAR columns. External table ‘dbo’ is not accessible because location does not exist or it is used by another process. 

Summary 

To summarize, creating an Azure Synapse Analytics external table does not require writing any code. By combining these tables with Azure Synapse Analytics serverless, the number of design options for your data analytics solutions increases considerably. 

WHAT’S NEXT?      

In upcoming blog posts, we’ll continue to explore some of the features within Azure Services.      

Please follow Tech Talk Corner on Twitter for blog updates, virtual presentations, and more!          

You can also connect with me on LinkedIn.

As always, please leave any comments or questions below.          

Check out these other blog posts: