Question 103468 in Azure Question posted in
The official documentation to get you started can be found here.

Writing output of Azure Synapse SQL to Azure blob

I have a Azure Synapse SQL workspace. I want it to: (a) read the data from Azure blob storage, (b) run the SQL query, and (c) write the output back to Azure blob.

I got (a) and (b) above working — it runs the query by reading the data from blob. I am not able to write the output back to blob. I was just wondering how do I change my below SQL query to write to the blob.

SELECT * FROM
 OPENROWSET(BULK 'https://myblob.blob.core.windows.net/testblob/',
 FORMAT = 'CSV')
 WITH
(    
    date1 DATETIME2,
    media varchar(8000),
    callconfig VARCHAR(8000),
    count1 INT
) AS [callconfigTable]

It will be great if someone can help. Thank you!

Tags:

2

Answers


  1. 0

    You can use CETAS functionality:
    https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-tables-cetas

    That way you can write back data to blob via TSQL functionality.

    Else you always have ADF/synapse pipelines to copy the select query into blob

    Login or Signup to reply.
  2. 0

    If you want to set "DATA_SOURCE" and write SQL query output to blob storage.
    Follow below approach:

    As per MS_doc,

    I reproduce same in my environment. I got this result.

    First Create master key on database.

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Provide valid_key';

    Create database Scope and credentials.

    --Azure storage account key.

CREATE  DATABASE SCOPED CREDENTIAL AzureStorageCredential

WITH  IDENTITY = '<storage_account_name>', Secret = 'Access_key';

    create external data source

    CREATE  EXTERNAL  DATA SOURCE AzureStorage with (
TYPE = HADOOP,
LOCATION ='wasbs://<container>@<storage_account>.blob.core.windows.net',
CREDENTIAL = AzureStorageCredential

);

    enter image description here

    -- CREATE EXTERNAL FORMAT TYPE:
CREATE  EXTERNAL  FILE FORMAT [TextFileFormat] WITH (
FORMAT_TYPE = DELIMITEDTEXT
);
GO

    Create external table:

    CREATE  EXTERNAL  TABLE [dbo].[dbotest6]

( [PassengerId] int,
[Survived] int
)
WITH
(
LOCATION='/vam/' ,
DATA_SOURCE = AzureStorage ,
FILE_FORMAT = TextFileFormat ,
REJECT_TYPE = VALUE ,
REJECT_VALUE = 0
) ;

    Note:

    the location should be like this as shown below image:

    enter image description here

    enter image description here

    You can directly insert data into an external table by using the INSERT INTO command. The external table is already connected to the storage account.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search