Introduction

Elastic query is a feature that allows to query data remote databases using SQL. This feature is useful to access and query data from remote databases like the CRM, ERP, Inventory, HR and other databases to create company reports. In this example, we will query an ASDW table from an Azure SQL Database.

Requirements

1. An Azure SQL Data Warehouse installed with the AdventureworksLT
2. SSMS installed in a local machine.

Getting started

We will first create an Azure SQL Database that will be used to query the ASDW database. In the Azure Portal, go to databases (the blue database icon) and then press the Add icon:

Specify a name, create a new group or use an existing one:

In the price tier, we will use the cheapest option:

If the firewall was not set before, in the database just created, press the icon, set server firewall:

We will need to create a Firewall rule to allow the IP of the local machine with SSMS to connect to our Azure SQL Server. Press Add client IP and save to add the local machine IP:

In properties, you can see the erver name and server admin login. We will use this information to login with SSMS later:

In a local machine, connect to the Azure SQL Server:

In the master database, right click and select the option, New query:

We will create a login to access to the data from different souces named datascientist. Run this command in SSMS to create the login:

CREATE LOGIN datascientist WITH PASSWORD = 'MysuperStrongPw0rd';

If everything is correct, you will be able to see the login created in Security>Logins

Another option to verify that the login was created is to run this query:

select * from sys.sql_logins

The query will list the logins created:

In our ASDW database, we will create a user with the following sentence in a new session in the ASDW database:

CREATE USER datascientistdb FOR LOGIN datascientist;

You can verify that the database user was creating by expanding the database in SSMS,  going to Security>Users:

You can list the database users using the following query:

SELECT * FROM sys.sysusers;

Once that the user is created, we will grant select permissions:

GRANT SELECT ON SCHEMA :: [dbo] TO datascientistdb

Connect to the Azure SQL database and create a master key:

CREATE MASTER KEY; 

Create a database scoped credential named datascientistelasticCredential (the credential should match with the password of the login created before):

CREATE DATABASE SCOPED CREDENTIAL datascientistElasticCredential
 WITH IDENTITY = 'datascientist',
 SECRET = 'MysuperStrongPw0rd';

This sentence will create a credential. You can verify credentials created using this query:

select *
 from sys.database_scoped_credentials 

The query will show the database scoped credentials:

We will now create an External Data Source named DataScienceExternalData. We need to specify the type which is Remote Database Management System. The location is the Azure Server name where the ASDW is. The Database and credentials are the database name and the database scoped credential just created:

CREATE EXTERNAL DATA SOURCE DataScienceExternalData WITH 
    (TYPE = RDBMS, 
    LOCATION = 'sqlcentralserver.database.windows.net', 
    DATABASE_NAME = 'sqlcentralwarehouse', 
    CREDENTIAL = datascientistElasticCredential, 
;

You can verify in the External resources the creation of the data source:

You can also use the following query to verify:

select *
 from sys.external_data_sources

We will finally create an external table. We will create in the Azure SQL Database an external table pointing to the DimCurrency table that is in the ASDW database:

CREATE EXTERNAL TABLE [dbo].[DimCurrency]
(
[CurrencyKey] [int] NOT NULL,
[CurrencyAlternateKey] [nchar](3) NOT NULL,
[CurrencyName] [nvarchar](50) NOT NULL
)
WITH 
(
    DATA_SOURCE = DataScienceExternalData
    SCHEMA_NAME = N'dbo'
,   OBJECT_NAME = N'DimCurrency'
)

You can verify in Tables>External Tables that this table was created successfully:

The following query shows the existing external tables:

select *
 from sys.external_tables 

It will show all the external tables created:

Now, let's try to query the external table to test:

SELECT TOP (1000) [CurrencyKey]
  ,[CurrencyAlternateKey]
  ,[CurrencyName]
  FROM [dbo].[DimCurrency]

The results displayed are the following:

We will now try to insert data into the table:

  Insert into DimCurrency
  values (456,'BTC','Bitcoin') 

The error message displayed is the following:

Msg 46519, Level 16, State 16, Line 7¡
DML Operations are not supported with external tables.

As you can see, the external table is read only and you cannot insert data to an external table. The same with updates, you can also do a select and not updates to external tables.

Conclusion

In this article, we learned how to create an external table in Azure SQL to access a table in ASDW we created elastic queries to query data to the external table.

References

• Azure SQL Database elastic query overview (preview)
• Controlling and granting database access
• Get started with cross-database queries (vertical partitioning) (preview)