0

I am new to Azure Synapse Analytics and just trying to figure things out.

Right now I have a pipeline which calls an API, downloads the request body, then merges and stores it in a data lake. I then wrote a separate SQL script in the develops tab to write that data into a database.

I access this data through the connector Azure Synapse Analytics SQL Database on PowerBI.

The problem I have is that I want the data in the serverless sql to be upto date, the pipelines can be ran on triggers which solve partially this issue but writing this to the database is a manual step and want it to be automated everytime the pipeline is ran

I looked at stored procedures but read ASA does not support stored procedures and I have no idea where to start.

Some people have suggested to use ADF however I think my boss wants the functionality of synapse to be core.

MahiC98
  • 11
  • 3

1 Answers1

0

If you want to write data to serverless pool through pipeline while writing the activity will perform create table option but as per this It is not supported in Azure server less pool. You can try with SQL script to write data from data lake to server less database by creating external tables.

Create External data source format with below code:

CREATE  EXTERNAL  DATA  SOURCE [ADLS] WITH
(
   LOCATION = 'https://<ADLSName>.dfs.core.windows.net/<container>
)

Create file format with below code:

CREATE  EXTERNAL  FILE  FORMAT [<Name>] WITH
(
    FORMAT_TYPE = DELIMITEDTEXT
)

Create external table using above resources.

CREATE  EXTERNAL  TABLE product(
    Id NVARCHAR,
    Name varchar(20)
)
WITH
(
     LOCATION='<directory>/<file>',
     DATA_SOURCE=<data source>,
     FILE_FORMAT=<fileFormat> 
)

enter image description here

Serverless Sql pool is not supported in Azure data factory you can feature request for this issue.

Bhavani
  • 1,725
  • 1
  • 3
  • 6