Create SQL views based on SQL Managed Instance in Synapse

[Dinosauris]

Hello,

I have integrated the SQL managed instance into my Synapse environment and I can create a linked dataset which works as expected. However, what I want is to create views in an SQL view or external table via the lake-, or sql databse without having to copy the data first into a blob storage for example.

Can I create an external table to do so or access via SQL statement directly the tables from the SQL managed instance without having to copy the data to another place first?

The final goals is to create SQL views based on it which can further be accessed from Customer Insights.

Thank you for your help!

[Ray_Minds · Accepted solution]

Your first step is to create a database where the tables will be created. Before creating a database scoped credential, the database must have a master key to protect the credential.

CREATE DATABASE SCOPED CREDENTIAL SqlMICredential
WITH IDENTITY = 'your-username',
SECRET = 'your-password';

2)create an external data source that points to your SQL Managed Instance.

CREATE EXTERNAL DATA SOURCE SqlMIDataSource
WITH (
    TYPE = RDBMS,
    LOCATION = 'your-sql-mi-server.database.windows.net',
    DATABASE_NAME = 'your-database-name',
    CREDENTIAL = SqlMICredential
);


3). create an external table that maps to the table in your SQL Managed Instance.

 CREATE EXTERNAL TABLE dbo.YourExternalTable
(
    Column1 datatype,
    Column2 datatype,
    ...
)
WITH (
    DATA_SOURCE = SqlMIDataSource,
    LOCATION = 'schema-name.table-name'
);


NOTE- for better Understanding, you can follow this link also
link - https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/create-use-external-tables

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

[Dinosauris]

Hello Yang!

Thank you for your help, however, when executing the following code I get the error message "Icorrect syntax near 'RDBMS'. Can you help out here?

Do I need certain permissions? What about CETAS?

Best regards,

Iris

[Anonymous]

Hi @Dinosauris ,

I think you can use Mirroring, which is a low-cost, low-latency solution in Fabric that brings together data from different systems into a single analytics platform. Existing data assets can be continuously replicated to Fabric's OneLake, including data in Azure SQL Database, Azure Cosmos DB, and Snowflake.

With the latest data provided by OneLake in a queryable format, you can now use various services in Fabric, such as running analysis with Spark, executing notebooks, data engineering, visualization through Power BI reports, etc.

For more information about Mirroring, refer to the following official documents:

Mirroring - Microsoft Fabric | Microsoft Learn

If your data is already in the fabric, it is more convenient to call the Shortcuts.

For more information about shortcuts, see:

OneLake shortcuts - Microsoft Fabric | Microsoft Learn

If you want to use SQL statements, you can use the CREATE EXTERNAL DATA SOURCE statement to define the data source information and reference the SQL managed instance.

Use the CREATE EXTERNAL TABLE statement to define the table schema and link it to the external data source.

Please try:

-- Create an external data source
CREATE EXTERNAL DATA SOURCE SqlManagedInstance
WITH (
    TYPE = RDBMS,
    LOCATION = 'your-managed-instance.database.windows.net',
    DATABASE_NAME = 'your-database-name',
    CREDENTIAL = 'your-credential'
);

-- Create an external table
CREATE EXTERNAL TABLE dbo.YourExternalTable
(
    Column1 INT,
    Column2 NVARCHAR(50),
    -- Define other columns as needed
)
WITH (
    DATA_SOURCE = SqlManagedInstance
);

Try these methods to see if they solve your needs.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!