Azure Synapse support for federated access

Question

Hello,

Is it possible to access SQL views created in one synapse workspace from another one?

One potential option is to access it via the sql endpoint from a python code. However, is it possible to access the SQL serverless views of one workspace from another sql serverless pool in a different workspaces ?

Not sure if external data source can be created in synapse pointing to another sql serverless pool objects.

Answer 1

Thanks @BhargavaGunnam-MSFT . Yes, this option will work. However it involves additional pipeline and operational overhead. I was trying to explore other options, wherein one need not to copy the data.

I am thinking to explore the usage of external hive metadata store for all the synapse environments. With this approach, I am anticipating that all the different synapse workspaces should be able to see the tables created in any of the environments, and if the user has access to to the underlying storage account, they should be able to access data. Haven't tried this approach yet, so not 100% confident on the approach.

Answer 2

Hello @saurabh,

Welcome to the MS Q&A platform.

Since synapse serverless views point to the external datasets, you can create an external data source and external tables and point to the location.

Please see the below syntax:

Create external data source

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
(    LOCATION         = '<prefix>://<path>'
     [, CREDENTIAL = <database scoped credential> ]
     , TYPE = HADOOP
)
[;]

Create external table:

USE [mydbname];
GO
CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat
);

Reference documents:

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-external-tables?tabs=hadoop

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/create-use-external-tables

I hope this helps. Please let me now if you have any further questions.