Create table in serverless sql pool

Question

I want to create a view/table in the Azure synapse serverless SQL pool (Data -> Workspace -> SQL database -> db_mcm (SQL) workspace from the ADLSGen2 (CSV file) using the pipeline. Could you help me with how I can do this? I have attached a screenshot where I want to create the table/views.

Answer 1

You can use external tables to access data stored in Azure Storage Blob or Azure Data Lake Storage, allowing you to read from or write to files in Azure Storage.

As you mentioned, you want to create an external table for the files in ADLS that you copied from Workspace to SQL database.

Below are the steps to create an external table from ADLS:

For example, I have a CSV file in ADLS, and I created an external table.

Next, you will get a prompt to create an external table like below:

Click continue.

You can create a database in the Serverless pool if you don't have one, and you can also create a table name and click on "open script."

Results:

You can also develop using the script. Below is the script to create an external table:

CREATE DATABASE <Database NAME>
Use <Database NAME>
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'XXXXXX'
CREATE DATABASE SCOPED CREDENTIAL <credential name>
WITH IDENTITY ='SHARED ACCESS SIGNATURE',
SECRET ='<SAS TOKEN>'
GO;
CREATE EXTERNAL DATA SOURCE demoDatasource
WITH( LOCATION     = 'https://<STORAGE ACCOUNT NAME >.dfs.core.windows.net/'
      , CREDENTIAL = <credential name>
);
CREATE EXTERNAL FILE FORMAT F1_fileformat
WITH(
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)
CREATE EXTERNAL TABLE  external_my_table (
    [dateID] INT,
    [product_id] varchar(500),
    [location_id] varchar(500),
    [visits] INT
)
WITH(
    LOCATION ='/<Container>/dbo.my_table.parquet',
    DATA_SOURCE = demoDatasource,
    FILE_FORMAT = F1_fileformat
)
GO

Using the above script, you can also create an external table.

Know more about the Use external tables with Synapse SQL

Answer 2

Below are the steps to create table in the Azure synapse SQL pool on the fly while loading the data from a CSV file present in ADLSGen2 using Synapse pipeline.

Step 1: Upload the file to ADLSGen2 container

Step 2: Create a pipeline and import the 'Copy data' activity.

Step 2a: Under Source settings, create a dataset pointing to ADLSGen2. File type is 'Delimited text'. Create or select the Linked Service pointing to the storage account. In the source Dataset / Connection select Row delimiter as '\n'. Makesure Quote character and Escape character are same.

Step 2b: Under Sink settings, create a dataset pointing to 'Dedicated SQL Pool'. select the sql pool name and mention the table name. In the import schema select None (because we don't have any table already created)

Select the copy method as 'Copy Command' and Table option as 'Auto Create Table' (because we want the table to be created by the pipeline). Mapping is not needed.

Step 3: Run the pipeline and see if the table is created.