USE master;

-- Create database if not exists
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'demo_data')
    CREATE DATABASE demo_data;
ELSE
    PRINT('The database exists.')
GO

-- Create external data source if not exists
IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'DemoData')
    CREATE EXTERNAL DATA SOURCE DemoData WITH (
        LOCATION = 'https://sthinadsdemodfs.dfs.core.windows.net/demo-data'
    )
ELSE
    PRINT('The external data source exists.')

-- Create external file format if not exists
IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'Parquet')
    CREATE EXTERNAL FILE FORMAT Parquet WITH (
        FORMAT_TYPE = PARQUET
    )
ELSE
    PRINT('The external file format exists.')

-- Drop external table if exists
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[parquet_olist_customers_dataset]') AND type in (N'U'))
    DROP EXTERNAL TABLE [dbo].[parquet_olist_customers_dataset]
ELSE
    PRINT('The external table does not exist.')

-- Create external tabl
CREATE EXTERNAL TABLE [dbo].[parquet_olist_customers_dataset]
WITH (
    LOCATION = 'SynapseServerlessAsPipelineActivity/parquet/olist_customers_dataset/',
    DATA_SOURCE = DemoData,
    FILE_FORMAT = Parquet
)
AS
SELECT
    *
FROM
    OPENROWSET(
        BULK 'https://sthinadsdemodfs.dfs.core.windows.net/demo-data/SynapseServerlessAsPipelineActivity/csv/olist_customers_dataset.csv',
        FORMAT = 'CSV',
        HEADER_ROW = TRUE,
        PARSER_VERSION = '2.0'
    ) AS [result]