Partition View
Warning
The OPENROWSET
syntax can use Azure Synapse Serverless SQL Pool only
Getting Started
Manage Permission
-
Remove bulk operations on master
-
Grant bulk operations on the database level
Partition Pruning
CREATE VIEW [<schema-name>].[<view-name>]
AS
SELECT
*,
CAST(temp.filepath(1) AS INT) AS [year],
CAST(temp.filepath(2) AS TINYINT) AS [month],
CAST(temp.filepath(3) AS TINYINT) AS [day]
FROM
OPENROWSET(
BULK 'data/table/year=*/month=*/day=*/**',
DATA_SOURCE = '<external-storage-name>',
FORMAT = 'PARQUET'
)
WITH (
[timestamp] [datetime],
[edge_id] [varchar](max),
[temperature] [float],
[humidity] [float],
[lqi] [float],
[pm1.0] [float],
[pm2.5] [float],
[pm10.0] [float],
[date] [date]
) AS temp
GO
CREATE VIEW [<schema-name>].[<view-name>]
AS
SELECT
*
FROM
OPENROWSET(
BULK 'data/delta_table/',
DATA_SOURCE = '<external-storage-name>',
FORMAT = 'DELTA'
)
WITH (
[timestamp] [datetime],
[edge_id] [varchar](max),
[temperature] [float],
[humidity] [float],
[lqi] [float],
[pm1.0] [float],
[pm2.5] [float],
[pm10.0] [float],
[date] [date]
) AS temp
GO
CREATE VIEW [<schema-name>].[<view-name>]
AS
SELECT
*
FROM
OPENROWSET(
BULK 'data/delta_table/',
DATA_SOURCE = '<external-storage-name>',
FORMAT = 'CSV',
PARSER_VERSION ='2.0',
FIELDTERMINATOR =',',
ROWTERMINATOR = '\n',
HEADER_ROW = TRUE,
FIRSTROW = 1,
FIELDQUOTE = '"',
ESCAPECHAR = '\\',
ROWSET_OPTIONS = '{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}'
)
WITH (
[timestamp] [datetime],
[edge_id] [varchar](max),
[temperature] [float],
[humidity] [float],
[lqi] [float],
[pm1.0] [float],
[pm2.5] [float],
[pm10.0] [float],
[date] [date]
) AS temp
GO