Serverless SQL Pool: Dynamically Remove Spaces

Table of contents (click to expand)

• Overview
• Demo
• Set Up a Synapse Workspace
• Upload Sample Data to Storage Account
• Create User Database
• Create an External Data Source and File Format
• Create an External Table
• Create Views with Modified Tables/Column Names

Overview

Important

Serverless SQL pools do not support internal tables. They primarily support external and temporary tables. Use a dedicated SQL pool when you need internal tables with local storage.

Table Type	Description	Use Cases
External Tables	- Reference data stored in external sources like Azure Data Lake Storage, Azure Blob Storage, Azure Cosmos DB, etc. - Data is not physically stored in the SQL pool. - Useful for querying large datasets without loading them into the SQL pool.	- Querying large datasets stored externally. - Performing data analysis on data stored in various formats.
Temporary Tables	- Created and used within the scope of a session. - Data is stored temporarily and is dropped when the session ends. - Useful for intermediate data processing and transformations.	- Storing intermediate results during complex queries. - Performing temporary data transformations and aggregations.

Demo

Set Up a Synapse Workspace

1. Sign in to the Azure Portal: Go to the Azure Portal and sign in with your Azure account.

2. Navigate to Your Synapse Workspace: In the Azure Portal, search for your Synapse workspace or create a new one if you don't have one.

   

   

   

3. Launch Synapse Studio: From the Synapse workspace overview, click on the Open Synapse Studio button.

   

   

Upload Sample Data to Storage Account

Important

This demo uses the storage account created with the Synapse workspace. CREATE EXTERNAL DATA SOURCE is not supported in the serverless SQL pool's master database, so create a user database first and perform the remaining operations there.

1. Create a Container in the Storage Account:
2. Go to the Azure portal and navigate to your storage account.
3. In the left-hand menu, select Containers.
4. Click on + Container to create a new container.
5. Enter a name for the container, such as sample-tables-container.
6. Set the Public access level to your preference (e.g., Private).

7. Click Create.

   

8. Navigate to the Container:

9. In the Azure portal, go to your storage account.
10. Select Containers from the left-hand menu.
11. Click on the container you created (e.g., sample-tables-container).
12. Upload the Sample CSV File:
13. Click on the Upload button.
14. In the upload blade, click on Browse to select the sample CSV file from your local machine.

15. Choose the file, click Upload to upload the file to the container.

    

Create User Database

1. First, create a new database in your Synapse workspace.

   CREATE DATABASE {User Database Name};
   

   

2. Switch to the User Database: Use the newly created database.

Create an External Data Source and File Format

1. Integrate this task with the previous step by establishing the external data source within the user database.

   USE {User Database Name};
   
   CREATE EXTERNAL DATA SOURCE {Data Source Name}
   WITH (
       LOCATION = 'https://<your-storage-account>.dfs.core.windows.net/<your-container>/'
   );
   

   

2. Create an External File Format: As part of the same flow, define the format of the CSV file.

   CREATE EXTERNAL FILE FORMAT {File Format Name}
   WITH (
       FORMAT_TYPE = DELIMITEDTEXT,
       FORMAT_OPTIONS (
           FIELD_TERMINATOR = ',',
           STRING_DELIMITER = '"',
           FIRST_ROW = 2
       )
   );
   

   

Create an External Table

1. Create an external table that references the sample data.

   CREATE EXTERNAL TABLE [Table Name] (
       [Employee ID] INT,
       [Employee Name] NVARCHAR(50),
       [Hire Date] DATE
   )
   WITH (
       LOCATION = 'employee data with spaces.csv',
       DATA_SOURCE = {Data Source Name},
       FILE_FORMAT = {File Format Name}
   );
   
   CREATE EXTERNAL TABLE [Table Name] (
       [Product ID] INT,
       [Product Name] NVARCHAR(50),
       [Release Date] DATE
   )
   WITH (
       LOCATION = 'product data with spaces.csv',
       DATA_SOURCE = {Data Source Name},
       FILE_FORMAT = {File Format Name}
   );
   

   

2. Confirm the existence of the tables

   SELECT * FROM sys.tables 
   WHERE name IN ('Product Data', 'Employee Data');
   

   

3. Query the External Table: You can now query the external table to see the sample data.

   SELECT * FROM {Table Name};
   

Create Views with Modified Tables/Column Names

This script is designed to dynamically create views for each table in a database, renaming columns to remove spaces. It starts by creating a temporary table to store the SQL statements and assigns a unique row number to each statement. The script then loops through these statements, executing each one in turn. Finally, it cleans up by dropping the temporary table.

1. Temporary Table Creation: A temporary table #CreateViewStatements is created to store the dynamic SQL statements and their corresponding row numbers.
   
2. Inserting SQL Statements: The script generates SQL statements to create views for each table in the database. It uses the INFORMATION_SCHEMA.COLUMNS to get the table and column names, renaming columns to remove spaces. These statements, along with a row number, are inserted into the temporary table.
   
3. Variable Declaration: Variables are declared to hold the current SQL statement, the current row number, and the maximum row number.
   
4. Getting Maximum Row Number: The script retrieves the maximum row number from the temporary table to determine how many statements need to be executed.
   
5. Executing SQL Statements: A loop iterates through each row in the temporary table, retrieves the SQL statement, executes it, and increments the row number until all statements are executed.
   
6. Cleanup: The temporary table is dropped to clean up after the script has finished executing.
   

-- Create a temporary table to store the dynamic SQL statements
CREATE TABLE #CreateViewStatements (SQLStatement NVARCHAR(MAX), RowNum INT);

-- Insert dynamic SQL statements for each table with a row number
INSERT INTO #CreateViewStatements (SQLStatement, RowNum)
SELECT 
    'CREATE VIEW ' + QUOTENAME(REPLACE(TABLE_NAME, ' ', '_')) + ' AS SELECT ' +
    STRING_AGG('[' + COLUMN_NAME + '] AS [' + REPLACE(COLUMN_NAME, ' ', '') + ']', ', ') +
    ' FROM ' + QUOTENAME(TABLE_NAME),
    ROW_NUMBER() OVER (ORDER BY TABLE_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME;

-- Declare variables to hold the SQL statement and row number
DECLARE @sql NVARCHAR(MAX);
DECLARE @rowNum INT = 1;
DECLARE @maxRowNum INT;

-- Get the maximum row number
SELECT @maxRowNum = MAX(RowNum) FROM #CreateViewStatements;

-- Loop through the temporary table and execute each SQL statement
WHILE @rowNum <= @maxRowNum
BEGIN
    -- Get the next SQL statement
    SELECT @sql = SQLStatement FROM #CreateViewStatements WHERE RowNum = @rowNum;

    -- Execute the SQL statement
    EXEC sp_executesql @sql;

    -- Increment the row number
    SET @rowNum = @rowNum + 1;
END;

-- Drop the temporary table
DROP TABLE #CreateViewStatements;

Back to the Azure Synapse overview

Continue to the dedicated SQL pool walkthrough