May 13, 2024 at 10:13 am
Dears,
I was told that I cannot access delta files in SQL Server Managed instance.
Those delta files are generated by synapse spark
But we need to move this information into a SQL Server Managed instance
Hence we have created a layer (curated layer) in synapse which converts this deltas into parquet files.
Goal is to read those files via views and load that information into the SQL Server Managed instance.
Currently we have some views which source information from this parquet files and load those informations into temporary tables in SQL Server Managed instance.
Code sample
SELECT
*
FROM
OPENROWSET(
BULK 'SALESF/Account/*.parquet',
DATA_SOURCE = 'datalake_curated',
FORMAT='PARQUET'
)
WITH (
Solumns of the file..
)
AS [result]
But I was checking on the net and I saw below example
M. Use OPENROWSET to access multiple delta files from Azure Data Lake Gen2
Applies to: SQL Server 2022 (16.x) and later versions.
In this example, the data table container is named and is located on an Azure Data Lake Gen2 storage account.Contoso
SQLCopy
CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';
CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
CREDENTIAL = delta_storage_dsc
);
SELECT *
FROM OPENROWSET(
BULK '/Contoso',
FORMAT = 'DELTA',
DATA_SOURCE = 'Delta_ED'
) AS result;
Is it really possible in SQL Server to create views like this? or this is only for SQL Server and not to SQL Server Ayure Managed instances?
Thanks a lot,
Pedro
May 13, 2024 at 7:52 pm
I believe this works. I haven't tested it, but this article appears to do what you want: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-delta-lake-format
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply