concept external table in category azure

This is an excerpt from Manning's book Azure Storage, Streaming, and Batch Analytics: A guide for data engineers MEAP V11. Login to get full access to this book.

Managed tables store the schema definition, and the table data, in files within the catalog folders in the associated Data Lake store. External tables store the schema definition, but reference external data sources. When your data is stored in an Azure SQL Database, you can leave the raw data there and configure read access through a U-SQL external table. When your data sources are files in your Data Lake store, or your database is not located in Azure, you can use a U-SQL managed table to restructure the data storage.

Figure 8.3. Managed versus external tables

U-SQL external tables support Azure SQL Database, SQL Data Warehouse, and Azure VM-hosted SQL Server. Chapter 9 covers integrating ADLA with external tables.

Figure 8.3. Managed versus external tables

This chapter explores two methods of importing data into SQLDB: external tables and ADF. External tables read data from data stores other than the one storing the SQLDB’s native tables. These data stores can be external files or separate databases. ADF connects to both file services and databases, and copies data between services. Along the way you’ll enhance your use of both SQLDB and ADF. You’ll learn how to share data between otherwise isolated SQLDB instances, which works the same as accessing data from file stores. You’ll also attach Git repositories to your ADF services, providing change management for your configuration files.

12.2.3  Creating an external table

External tables form the entry point to retrieving data from remote SQLDBs. Use external tables like internal tables in queries, views, and stored procedures, with a few caveats.

External tables allow only read access. You can use remote execution of stored procedures to update the remote table, or you can copy the data into a local table for use.

External tables don’t get local indexes. Queries using external tables will scan tables as necessary, but remote processing can include indexes on the remote table to improve query speed.

Creating an external table requires a few pieces of information: a name for the table, and the name of the data source. You also define the schema for the table, just as when creating a local table.

Use the SQL command CREATE EXTERNAL TABLE {NAME} to create the external table. The name of the external table must match the name of the remote table. Provide the data source using WITH (DATA_SOURCE = {NAME}). Listing 12.7 shows a script to create a table that matches the PlayerDetails.txt file from chapter 7. A script for creating this table in the remote database can be found in appendix A.

Listing 12.7. Creating an external table

CREATE EXTERNAL TABLE [dbo].[playerdetails] #1
<lineArrow/>(PlayerId nvarchar(8),PlayerName nvarchar(100),
<lineArrow/>TeamName nvarchar(100),TeamPosition nvarchar(100),
<lineArrow/>PositionStart DateTime,PositionEnd DateTime)
<lineArrow/>WITH (DATA_SOURCE = [ade-dev-eastus2-sql2-gamestats]); #2

copy