0

In one SQL Server instance, I have these 2 data warehouses:

sqlwarehouse1 
sqlwarehouse2

And in sqlwarehouse2, I have a table dbo.test.

How can I refer to this table from sqlwarehouse1?

Is this cross data warehouse communication possible in Azure SQL data warehouses?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Anna
  • 37
  • 6
  • You would have to configure External Data Sources to achieve this. – Thom A Feb 07 '20 at 12:28
  • Thanks Larnu, Could please elaborate on this.. You meant, the table dbo.test should point to an external source like blob and "sqlwarehouse1" should refer from blob? – Anna Feb 07 '20 at 12:39
  • I don't use Azure very often, and when i have, I've never had to do it. There are answers out there though, such as [Not able to do cross database query in SQL Azure](https://stackoverflow.com/q/45715668/2029983) – Thom A Feb 07 '20 at 12:43
  • Can you describe the business goal you are trying to accomplish (not what you want but why)? – GregGalloway Feb 09 '20 at 08:50

1 Answers1

0

Azure SQL DW (now called Azure Synapse Analytics) can’t currently connect to another DW. Polybase currently just reads from files.

You could create a small Azure SQL Database to use to connect to the DWs as follows:

 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pass@word1'; 
 CREATE DATABASE SCOPED CREDENTIAL DWCredential 
 WITH IDENTITY = 'usernameHere', 
 SECRET = 'passwordHere';  


 CREATE EXTERNAL DATA SOURCE DWDataSource WITH 
    (TYPE = RDBMS, 
    LOCATION = 'yourservername.database.windows.net', 
    DATABASE_NAME = 'yourdw', 
    CREDENTIAL = DWCredential, 
) ;



CREATE EXTERNAL TABLE [dbo].MyName 
(   [ProductKey] [int] NOT NULL,
    [ProductAlternateKey] [nvarchar](25) NULL,
    [EndDate] [datetime] NULL,
    [Status] [nvarchar](7) NULL) 
WITH 
( DATA_SOURCE = DWDataSource
,SCHEMA_NAME = N'dbo',  
        OBJECT_NAME = N'YourDwTableName') 


select * from MyName



--or run an exact query against SQL DW directly:
exec sp_execute_remote @data_source_name = N'DWDataSource', @stmt  = N'SELECT distinct ModelName from dbo.DimProduct'
GregGalloway
  • 11,355
  • 3
  • 16
  • 47
  • Thanks a lot Greg, I will work out this and update here...So the external table in SQL Database, I can call from sqlware house2? – Anna Feb 09 '20 at 05:49
  • Hi Greg, Using the above solution, I am able to create an external table in SQL data base and query tables in DW1...Now How can I refer an external table in SQL DB from DW2?..I think we achieved half part...Please suggest me the rest – Anna Feb 09 '20 at 07:17
  • Microsoft reference are saying Azure SQL DW only supports Blob storage as external data source "https://social.msdn.microsoft.com/Forums/sqlserver/en-US/587ab9cc-d3ec-4183-9f79-bdacf3cf74d4/unable-to-create-external-table-at-azure-sql-data-warehouse-pointing-to-azure-sql-server?forum=AzureSQLDataWarehouse" – Anna Feb 09 '20 at 08:03
  • One solution found is Export data from DW1 to Blob and import to DW2 as external table from Blob "https://docs.qubole.com/en/latest/user-guide/analyze/tsql.html" – Anna Feb 09 '20 at 08:09
  • `CREATE EXTERNAL TABLE AS SELECT` to export to Blob from DW1 and the selecting from an external table in DW2 is certainly the highest bandwidth option if you want to move a large amount of data. – GregGalloway Feb 09 '20 at 08:51
  • @Anna you can read from both DWs from your DB. You can’t easily write to DW2 using the DB solution I suggested. It would be a change in your plan if you follow my answer. But thus answer will work well if you need to combine small summarized results from multiple DWs into a DB – GregGalloway Feb 09 '20 at 08:54