concept external data source 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.

You can build bridges from SQLDB to other databases using external data sources. An external data source uses a saved credential, a connection definition, and a table definition to let queries read from outside databases. This saved credential is called a database scoped credential. The external data source defines the location of the external database and the credential to use. An external table provides a schema definition to use when reading data from the external connection. Together, these three objects allow read access to data in separate databases.

An external data source can use either another SQLDB or a Blob file. Different schema, read, and authentication steps are used for each source. Figure 10.6 shows the process of reading data from external data sources using a SQL query.

Figure 12.2. Connecting SQLDB to an external data source

SQL queries using external tables retrieve rows from the external data source, and perform processing like JOINs and column calculations afterward. The retrieved rows are stored in an intermediate temp table during processing. To use data from Blob files in SQL queries, you must first import the data into a database table. Then you can use the table in your query.

External data sources and tables are part of the SQL Database elastic query framework. Vertical partitioning in SQLDB separates tables across multiple databases, and uses external tables for queries. Horizontal partitioning duplicates tables across multiple databases, and splits rows between the databases. This is called sharding. Horizontal partitioning uses a metadata database to manage queries across the sharded tables. When linking an external data source to a sharded source, you connect to this managing database, specifying the SHARD_MAP_MANAGER source type.

Listing 12.5 shows a script for creating the external data source, on the ade-dev-sql2-gamestats SQLDB, using the scoped credential you created in the previous section.

Listing 12.5. Creating an external data connection to SQLDB

CREATE EXTERNAL DATA SOURCE [ade-dev-eastus2-sql2-gamestats]
<lineArrow/>WITH (TYPE = RDBMS,
<lineArrow/>LOCATION = 'ade-dev-eastus2-sql2.database.windows.net',
<lineArrow/>DATABASE_NAME = 'ade-dev-sql2-gamestats',
<lineArrow/>CREDENTIAL = [ade-dev-sql2-gamestats-user]);

copy

Creating an external data source on a Blob file uses the same SQL command. Pass the type, location, and credential parameters. The location is the fully-qualified URL to the Blob file. Because you’re connecting to a file, you don’t use the database name parameter. Connecting to a Blob external data source uses a Blob Storage key or AAD user scoped credential, instead of a SQLDB user. Listing 12.6 shows a script for creating the blob file external data source.