Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,295 questions
Synapse SQL CI/CD release pipeline - creating external data source without adding TYPE = HADOOP
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(275.2, 31%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EB%3C/text%3E%3C/svg%3E)
BGO
46
Reputation points
Hello,
I just created Synapse SQL CI/CD release pipeline. It should recreate everything from the DEV dedicated sql pool in the UAT dedicated sql pool.
I'm using external tables based on the parquet files. I created External Data Source without the "TYPE = HADOOP" as adding the "TYPE = HADOOP" is not working/supported and also not recommended by Microsoft. This works fine.
CREATE EXTERNAL DATA SOURCE [dsname] WITH
( LOCATION = N'abfss://********.dfs.core.windows.net',
CREDENTIAL = [*****]
);
The problem is when I'm running the release pipeline it creates the External Data Source and adds the "TYPE = HADOOP" automatically even though it doesn't exist in my initial script.
CREATE EXTERNAL DATA SOURCE [dsname] WITH
( TYPE = HADOOP,
LOCATION = N'abfss://********.dfs.core.windows.net',
CREDENTIAL = [*****]
);
It causes the pipeline to fail as after creating the external data source it's trying to create the external tables and fails because the "TYPE = HADOOP" is not supported.
Is there any way I can force the pipeline to avoid adding the "TYPE = HADOOP"?
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(134.4, 9%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EKM%3C/text%3E%3C/svg%3E)
KranthiPakala-MSFT 46,627 Reputation points Microsoft Employee2023-08-20T18:08:00.4066667+00:00 @BGO Welcome to Microsoft Q&A forum and thanks for reaching out here.
As per the official documentation, it states that
TYPE = HADOOPis only supported for Dedicated SQL pools, and the recommend is use it for legacy implementations and do not specify theTYPEargument for newer native data access.Ref doc: External Data Source - TYPE = HADOOP
But irrespective you shouldn't receive and error message saying
TYPE = HADOOPis not supported when trying to create external table using Dedicated SQL Pool unless you are pointing to Serverless SQL instead of Dedicated SQL as only Serverless does not supportTYPE=Hadoop.I would recommend double checking the DEV source code to ensure the External data source T-SQL scripts are not having
TYPE = HADOOP.In case if you are sure that the T-SQL scripts are valid and doesn't contain TYPE mentioned, then I recommend filing a support ticket for deeper investigation as something seems off from CICD standpoint. And also, the error message needs to be improved with relevant error message.
Please do share the support ticket number once filed so that we can track it internally. In case if you don't have a support plan, kindly let me know here.
Thank you
-
BGO 46 Reputation points
2023-08-21T15:09:35.5933333+00:00 @KranthiPakala-MSFT Just to clarify - I'm only using the Dedicated SQL Pool.
When I'm creating External Data Source I'm not getting any error (no matter if I add the "TYPE = HADOOP" or not). I'm only getting an error when creating the External Table based on that External Data Source. Here is an example:
- This code works fine
CREATE EXTERNAL DATA SOURCE [test-datasource] WITH ( LOCATION = N'abfss://*****@******.dfs.core.windows.net', CREDENTIAL = [SynapseIdentity] ) CREATE EXTERNAL TABLE [dbo].[test_table] ( [ID] [nvarchar](4000) NULL, [DESCR] [nvarchar](4000) NULL ) WITH ( DATA_SOURCE = [test-datasource], LOCATION = N'ParquetFilesPath/*.parquet', FILE_FORMAT = [SynapseParquetFormat], REJECT_TYPE = VALUE, REJECT_VALUE = 0 )- This code fails when trying to create an external table
CREATE EXTERNAL DATA SOURCE [test-datasource] WITH ( TYPE = HADOOP, LOCATION = N'abfss://*****@******.dfs.core.windows.net', CREDENTIAL = [SynapseIdentity] ) CREATE EXTERNAL TABLE [dbo].[test_table] ( [ID] [nvarchar](4000) NULL, [DESCR] [nvarchar](4000) NULL ) WITH ( DATA_SOURCE = [test-datasource], LOCATION = N'ParquetFilesPath/*.parquet', FILE_FORMAT = [SynapseParquetFormat], REJECT_TYPE = VALUE, REJECT_VALUE = 0 )Error message: External file access failed because the specified path name 'ParquetFilesPath/.parquet' does not exist. Enter a valid path and try again.*
-
KranthiPakala-MSFT 46,627 Reputation points Microsoft Employee
2023-08-21T23:29:06.5166667+00:00 @BGO Thanks for the response and clarification.
Could you please help confirm if the External Source creation scripts or regular SQL Scripts or Notebook scripts so that I shall try to replicate the issue and work with respective product owners.
Looking forward to your confirmation.
Thank you
-
BGO 46 Reputation points
2023-08-22T00:44:27.66+00:00 KranthiPakala-MSFT Sorry, I'm not sure I understand your question. The scripts I provided above are real with just masked/changed names. Executing the script #2 should replicate the issue.
-
KranthiPakala-MSFT 46,627 Reputation points Microsoft Employee
2023-08-22T22:00:11.6933333+00:00 @BGO Thanks for providing additional clarification. I'm reaching out to internal team to get additional information about this inconsistent behavior and will keep you posted as soon as I hear back from the team.
Sign in to comment
Sign in to answer