Questions tagged [azure-synapse-analytics]
The azure-synapse-analytics tag has no usage guidance.
azure-synapse-analytics
174
questions
2
votes
1
answer
513
views
Is it possible to create temporal table in Azure synapse datawarehouse
I m trying to create temporal table in azure synapse datawarehouse.
CREATE TABLE dbo.Department
(
DeptID INT NOT NULL PRIMARY KEY CLUSTERED
, DeptName VARCHAR(50) NOT NULL
, ManagerID INT ...
- 45
2
votes
0
answers
70
views
How to disable Publish button in Azure Synapse Workspace?
We have CI/CD enabled for our Synapse environment, which automatically validates and deploys ARM templates to the target environments when master branch is updated.
As such, we want to be able to ...
- 33
2
votes
2
answers
458
views
How troubleshoot Synapse Spark Notebook that runs manually, but times out if run from a Pipeline
Spark Notebook, if run manually from the notebook (whether under my
credentials or running Managed Identity) runs successfully in about
3 minutes
If placed in a Pipeline - prior to Tuesday, it ran ...
- 189
2
votes
0
answers
184
views
How to make use of custom jar in Synapse Notebook after uploading it in Azure Synapse Workspace?
I am trying to add my customized jar in Azure Synapse Workspace to make use of user defined function (udf) present in the jar while running the sql query in Synapse Notebook.
An Example: There is udf ...
2
votes
1
answer
996
views
Unable to saveAsTable in pyspark dataframe : apache.spark.sql.delta.schema.InvariantViolationException error: Exceeds char/varchar type length
I am trying to merge 5 dataframes from csv's and creating new dataframe than in the second step I am creating empty table with custom schema, now I am want to load the records from the dataframe.
Here ...
1
vote
1
answer
260
views
Synapse Analytics Data tab not showing data pool
I am learning Azure, and I've created a Synapse Analytics workspace. Inside it, I've created a new dedicated sql pool:
But fore some reason, unknown to me, that sql pool is not shown (it's missing) ...
- 33
1
vote
1
answer
99
views
Access our built-in serverless SQL pool from our notebook
We are currently trying to access our built-in serverless SQL pool from our notebook. Our goal is to be able to drop views from the notebook, which is something we need to put into our pipeline as per ...
- 83
1
vote
1
answer
86
views
Copy data Activity Azure synapse
Hi All, we are looking at automating a copy data activity using a for each loop. The Metadata1 gets the list of the file names, from here we use Bronze/@activity('Get Metadata1').output.childItems / * ...
- 83
1
vote
1
answer
780
views
notebook activity in ADF not able to call another synapse notebook
Synapse Notebook reference - Call Synapse notebook from another with parameters
notebook_name = '/NB_dim_load'
using
mssparkutils.notebook.run(notebook_name, 90, {"notebookparams": ...
1
vote
1
answer
417
views
Azure Synapse Pipeline Stuck / Hung in-Progress state; how to debug? LocalVM runtime performance
This appears to be a common problem but a complete mystery for how to resolve.
This happens when I try to process many hundreds of files. My pipeline has 1588 items. It works great for an hour or so ...
- 33
1
vote
1
answer
257
views
Problems in UDF function in Spark 3.3 (scala)
We are changing some code from spark 2.4 to spark 3.3 and we are having problems with our UDF function. We generate the JAR (after modifying the library versions in the POM) with all the logic and run ...
- 473
1
vote
1
answer
1k
views
Write delta table to ADLS from Azure Synapse Pyspark notebook
I'm tying to write delta formatted table to ADLS Gen2 from Azure Synapse Pyspark notebook with Serverless SQL pool.
It is throwing me error, while writing to ADLS Gen2 as below.
Py4JJavaError: An ...
- 1,288
1
vote
1
answer
623
views
Load MongoDB data incrementally through Azure data factory
I would like to load data from MongoDB incrementally to Azure storage using Azure data factory. I couldn't find any relavent documentation to do this..Appreicate if there is a way to achieve this with ...
- 25
1
vote
1
answer
822
views
How can I connect to Azure Synapse Dedicated SQL pool ( SQL DW) from powershell using service principal authentication?
I want to deploy my SQL scripts to synapse from powershell using Invoke-Sqlcmd utility
We have disable username/password authentication and the only way to do it is by using Managed Identity/AD ...
- 159
1
vote
3
answers
3k
views
Access dedicated SQL Pool from Synapse Analytics notebook
I am new to synapse analytics and I want to create a notebook, which could be used further as a databrick in the pipeline in order to process the data in one of the tables from the DB.
I want to do ...
- 53
1
vote
2
answers
1k
views
Get a list of all Synapse notebook names in Azure Synapse Analytics
In Azure Synapse Analytics, I'm trying to create a code that returns a list with all notebook names.
In databricks I verified that it is possible by these two solutions:
https://docs.databricks.com/...
- 147
1
vote
1
answer
55
views
How to Connect Synapse Notebook to SQL Serverless Pool
I'm working with Azure Synapse Analytics and I need to connect my Synapse Notebook to my SQL Serverless Pool to drop and create views directly within the notebook. However, when I try to run the DROP ...
- 83
1
vote
0
answers
14
views
Querying azure synape serverless through SQL server management studio getting ERROR
Querying azure synape serverless through SQL server management studio getting below ERROR.
Content of directory on path 'Tables/Finance/AccountsReceivable/Transaction/CustTrans/*.csv' cannot be listed....
- 21
1
vote
2
answers
599
views
How to grant user permission to a specific database in Azure Synapse Analytics Lake database?
I have multiple databases in Azure Synapse Analytics lake database. Now I am trying to limit the read access to one of the users. I was using the following script to create the login for the user:
Use ...
- 179
1
vote
1
answer
43
views
GROUP BY with multiple nested queries T-SQL
I am trying to perform multiple counts based on different conditions and group the results by year and month.
I have a complaints table, and I want to count:
Total Received complaints per year and ...
- 53
1
vote
0
answers
89
views
Tenant Isolation with spark structured streaming
I am currently exploring Azure Synapse Analytics, focusing on near-real-time (NRT). I am planning to utilize Delta tables and we have a multi-tenant setup for our product where each tenant's data ...
- 1,669
1
vote
0
answers
94
views
Fabric capacity not being used completely
Iam using azure fabric capacity F4 for running my spark application. This spark application written in pyspark reads messages (URLS of news articles) from eventhub with 8 partitions and process it ...
- 11
1
vote
0
answers
148
views
How can superset connect Azure Synapse Data Warehouse(not build-in)?
I'd like to use superset to connect with Azure Synapse Data Warehouse.(Not Azure SQL Server)
I can use the pyodbc python driver to connect it successfully.
But I cannot connect it using superset ...
- 11
1
vote
1
answer
738
views
How to insert huge data from PySpark dataframe to sql server table
I have nearly 38190123 rows of data to be pushed to SQL server table. I have tried the following:
df.write.format("jdbc").option("url", jdbc_url).option("dbtable",...
- 171
1
vote
0
answers
63
views
How to move terabytes of data from Blob Storage to Synapse?
I have thousands of compressed CSV files to be copied to a SQL dedicated pool in Synapse. I have tried the following and all of them either failed or just took too long (days) to complete and I'm ...
- 520
1
vote
2
answers
852
views
Azure Synapse Pipeline Get Metadata Last Modified Date on file instead of on folder
I am trying to get the Last Modified date on the files (CSV linked service) in my repository using Get Metadata activity.
Field List
However, I am getting the same date/time for all of the files... ...
- 33
1
vote
1
answer
238
views
Single Node Cluster in Azure Synapse
Please let me know if its possible to setup a single node cluster using Azure Synapse Spark pool, i don't want any executor / worker nodes for my requirement .
Please advice.
- 611
1
vote
1
answer
648
views
regexMatch and regexReplace functions in Azure Synapse Analytics are not yielding results in simple SELECT statements
I'm trying to execute simple SELECT statements in dedicated SQL Pool's script in Azure Synapse. One of those were:
SELECT regexMatch('abctest','^[A-Za-z]*$');
This statement should yield return True ...
- 77
1
vote
0
answers
989
views
Azure devops pipeline task "Synapse workspace deployment" failing
Im tyring to run Synapse workspace deployment@2 task from azure pipeline as shown below
- task: Synapse workspace deployment@2
continueOnError: false
inputs:
...
- 11
1
vote
1
answer
1k
views
Execute Serverless SQL Pool external table / Views from Synapse notebook using Python
I have a Serverless SQL pool which contains external tables/Views write against ADLS Gen2 parquet files.
I want to call external table / views from synapse notebook using python.
FYI. Server less SQL ...
- 13
1
vote
1
answer
952
views
What version of Delta Lake is supported by Synapse built-in SQL pool
I'm using Synapse Analytics to read from a Data lake containing Delta tables. The Delta tables were written using the latest Delta version.
How can I verify what version of Delta is running in the ...
- 23
1
vote
1
answer
260
views
Azure Synapse Analytics Dedicated SQL Pool: How can I create an external table as select ( CETAS ) and partition by CustomerID?
I can create CETAS tables in Azure Blob storage fairly easily ( link ), however, is there a simple way to automatically partition by CustomerID so that the rows associated with each customer will end ...
- 1,324
1
vote
1
answer
277
views
@@ Variables not working in Synapse Dedicated SQL pool (@@error, @@rowcount, etc.,)
We are working on DW to Synapse migration. In that I am migrating an Stored Procedure which having @@error, @@rowcount variables.
@@ Variables not working in Synapse Dedicated SQL pool (@@error, @@...
- 81
1
vote
0
answers
537
views
Azure Synapse Spark Pool - Add workspace package is very slow
I have the Azure Synapse Analytics Workspace and the Spark pool in it. I have added a .jar file (SQL Server jar file) to the workspace packages as shown below -
Now I am trying to add this jar file ...
- 159
1
vote
0
answers
533
views
Deployment of self hosted integration runtime in azure synapse using azure devops
I'm working on building the CICD pipeline for deployment of Azure Synapse Analytics workspace using Azure DevOps. I was reading this article and see a need to handle the deployment of self hosted ...
- 890
1
vote
1
answer
351
views
Parquet File from Data Lakes to SQL Pool - Arithmetic overflow converting tinyint to TINYINT
We're trying to import a parquet File from Data Lakes to the Azure dedicated SQL Pool but getting an error randomly.
The error is:
HadoopSqlException: Arithmetic overflow error converting tinyint to ...
- 71
1
vote
2
answers
606
views
PySpark with SynapseSQL throws error when using queries with restricted keywords in column name
I'm querying SQL Database on Azure Synapse Analytics server with PySpark. However, some of my column names may contain restricted keyword(s).
Here, I've prepared a generic example which I could bypass ...
- 267
0
votes
1
answer
580
views
Azure Synapse Link for Dataverse - Tables from F&O - only available via Spark pool for Delta Lake?
I am migrating a project from one tenant to another, which in essence is ETL job from Dynamics 365 F&O to Power BI Reporting.
I am using Azure Synapse Link for Dataverse to fetch data from ...
- 530
0
votes
1
answer
677
views
UPDATE, DROP COLUMN and EXCEPT not working in Spark SQL
I'm working on Spark SQL through Azure Synapse Apache Spark Pool and when I use the UPDATE command the error I get is :
UPDATE destination only supports Delta Sources.
I tried dropping the column I ...
- 77
0
votes
1
answer
291
views
Microsoft Purview is not able to scan serverless table's fields
While scanning Synapse Serverless SQL tables from Microsoft Purview, not able to reach till column-level details.
Trying to figure out what could be the reason for not scanning the table columns.
Any ...
- 393
0
votes
1
answer
439
views
Running into errors creating a linked service in Azure Synapse
I am trying to create a linked service to Azure Datalake Gen 2 using SAS URI. I was able to sucessfully create the required SAS Token that has all the permissions for a particular container.
Under the ...
0
votes
1
answer
249
views
How to retrieve object_id without hardcoding values for schema and stored procedure name
In a stored procedure on Azure synapse Dedicated SQL pool I need to get the object_id of the currently executing stored procedure
SQL server has available extremely useful system variable @@PROCID
...
- 27.9k
0
votes
2
answers
341
views
Split .csv file column in 2 in Azure Synapse Analytics using PySpark
I have a .csv file (in Azure Data Lake Storage), which looks approximately like this ->
I want to create a notebook (PySpark (Python)), which could be implemented in the synapse analytics (...
- 53
0
votes
2
answers
126
views
Query to fetch multiple time range data in single query
I have a table named Order which have transaction related information like
order_id
customer_id
net_amount
net_discount
order_date
I want to fetch the details of a customer over different time ...
- 5
0
votes
1
answer
60
views
Azure Synapse Dataflow - Unable to use a parameter in Pre SQL scripts
In my Sink I'm running a Pre SQL Script to delete data after a certain date but I'm having trouble using parameters in the script.
Here is my date parameter variable inside dataflow and below is my ...
- 41
0
votes
1
answer
81
views
Third party rest API call using oAuth 2.0 from Azure synapse web activity
I have a task where I need to connect to external URL from Azure synapse and get few data. We were doing that using synapse web activity and using basic authentication. Now the provider has asked us ...
- 617
0
votes
1
answer
102
views
How to Copy Files into Individual Folders in Synapse Analytics
I originally had files in my silver folder. I was using notebooks to make transformations to these files and then overwriting the original files with the transformed ones. After speaking to a Synapse ...
- 83
0
votes
1
answer
62
views
Export or query for all requests SQL in database SQL in Azure Synapse
I need to analyse the quantity of SQL requests in views of DW database in Azure Synapse. In the tool there is a monitor of SQL requests, but not is possible export the data. There is a table where I ...
0
votes
1
answer
199
views
Read file from filesystem using Spark in Synapse
I'm trying to read a file as a DataFrame in a Synapse Spark notebook using spark.read.format('csv').load('/path/to/file'). The file is located in an ADLS Gen2, which is mounted on all Spark pools in ...
0
votes
1
answer
69
views
Dynamically switch databases and create their views from deltalake
I am a newbie to Synapse. I need your help with a task I've been working on for a few days. I want to create views in SA for my delta tables from Deltalake. I have the below folder structure in my ...
- 25