Questions tagged [azure-synapse-analytics]
For questions about Azure Synapse (formerly Azure SQL Data Warehouse). It is an analytics service that brings together enterprise data warehousing and Big Data analytics.
32
questions with no upvoted or accepted answers
4
votes
1
answer
618
views
Revision Tracking & Source Control for Azure SQL Data Warehouse
What is a good approaching for tracking incremental changes to database tables, stored procedures, etc for Azure SQL Data Warehouse?
I am in the process of moving a large database over to Azure SQL ...
- 149
3
votes
1
answer
1k
views
How are deadlocks detected by Synapse
I'm trying to produce a deadlock in Azure Synapse. I would have expected it to work much as on-prem SQL Server does given the documentation says it "applies to" Synapse. I've waited an ...
- 24.7k
2
votes
1
answer
1k
views
Azure Synapse SQL Pool: Issue in MERGE INSERT UPDATE statement
I have a table in Azure Synapse Dedicated SQL POOL.
CREATE TABLE table_A
(
ID int IDENTITY(1,1),
ClientID varchar(10),
fitrstname varchar(20),
lastname varchar(30),
phone ...
- 131
2
votes
0
answers
211
views
Exactly-once FIFO queue in Synapse
Creating a queue table in SQL Server is a much-studied problem. However, I would like to implement one in Azure Synapse where many of the building blocks do not exists. Specifically
no table hints (...
- 24.7k
2
votes
0
answers
117
views
Why does outer apply cause a broadcast move?
I've got an outer apply with a condition on the distribution keys.
select e.a
,e.b
,p1.c
from e
outer apply
(
select top 1
p.DateStamp
from p
where e....
- 1,244
1
vote
0
answers
24
views
Schema Drift & Delta Table update - cannot resolve `target.new_column` in UPDATE clause given columns [list of columns]
Using Azure Synapse Analytics, I have configured a pipeline and a Mapping Data Flow that were working great until I recently added a Derived Column to my Mapping Data Flow, which created a column that ...
- 11
1
vote
1
answer
108
views
Azure Dedicated SQL Pool - How to checksum a table?
We're moving all our on premise tables into the azure dedicated sql pool. We're using synapse workspace pipelines to import the data into the dedicated pool.
Is there a way to checksum a table to ...
- 11
1
vote
0
answers
183
views
How do I change the database owner in Synapse Analytics Serverless SQL Pool?
I am working with Azure Synapse Serverless SQL Pool. A former employee created the databases in the pool, and is listed as their owner. Consequently, I cannot drop the login.
I would like to change ...
- 111
1
vote
0
answers
235
views
Synapse Dedicated SQL Pool: temporary CREATE TABLE failed despite number of columns are well below the maximum limit
I am performing an UPSERT operation in a Synapse Dedicated SQL Pool.
The TARGET table has 394 column and I am trying to update the TARGET with 5 Unique Keys.
MERGE table_A AS TARGET using
(SELECT &...
- 131
1
vote
0
answers
15
views
Resource overlap between Synapse databases
I have an Azure Synapse dedicated pool with a single database. It is scaled to DW500c which the documentation says means it has a single compute node.
If I add a second database how does work against ...
- 24.7k
1
vote
0
answers
58
views
Shrinking 150 TB data warehouse instance is feasible option?
We are using azure SQL data warehouse(azure synapse analytics) with size of 150 TB among which 75 TB is used and we are planning to shrink database with help of MS. What are the challenges should we ...
1
vote
0
answers
48
views
how to identify cross login in azure synapse analytics?
In my environment user are logged in to SQL server via MFA authentication with their email id.
Its possible for single user to log in to multiple laptops with same credentials.
Can we track user ...
1
vote
1
answer
1k
views
How to see what's using TempDb space in Azure SQL DW?
I'm having problems with TempDb being full. Are there any system views I can see to diagnose what's using up TempDb and/or how much space is still available on it?
It would be awesome to also see ...
- 1,244
1
vote
1
answer
50
views
How to index cube style queries with changing aggrigations
I've got a query that emulates the CUBE function.
This is many different aggregations unioned together.
select x,y,z, sum(a) from tbl group by x,y,z
union all
select x,'all',z, sum(a) from tbl group ...
- 1,244
1
vote
0
answers
278
views
Output result of DBCC PDW_SHOWEXECUTIONPLAN into a variable
I would like to output the results of DBCC PDW_SHOWEXECUTIONPLAN into a variable (or table, I'm not fussy!).
I know the PDW syntax is quite different when it comes to outputing results, which means ...
- 1,244
0
votes
0
answers
24
views
Azure Synapse query to multiple JSON files in data lake, parse data into table format
I want to query RAW telemetry data stored in JSON format in my data lake. I am using Azure Synapse to query the files.
I can successfully pull the data I want into a result table, but I struggle to ...
- 193
0
votes
1
answer
247
views
Generating MD5 Hash for NVARCHAR and VARCHAR columns across different databases
I am building a utility that generates a single MD5 hash across all CONCATENATED column values for any table row. To make this work, I have to eliminate NULL values using COALESCE and must CAST NUMBER ...
- 69
0
votes
1
answer
181
views
Database Scoped Credential with User-Assigned Managed Identity possible?
We have a data lake (Azure Data Lake Gen 2) that contains single folders with data for specific use cases in the 'gold' container, e.g. for a financial dashboard (folder 1) or a production dashboard (...
- 15
0
votes
0
answers
49
views
How to store LineString or Point data type in Azure Synapse?
I have JSON files that I need to store in Azure Synapse table.
One record looks like this:
{ "type": "Feature", "properties": { "id": 12356}, "geometry&...
0
votes
0
answers
159
views
Copy data from Data Lakes to Dedicated SQL Pool - Data Type overflow error
We have a synapse pipeline that copies data from Data Lakes (Parquet Files) to the Dedicated SQL Pool in Azure. The problem is that some of the Copy Data tasks fail with a "Arithmetic overflow ...
- 11
0
votes
1
answer
199
views
Hash, Replication, and Round Robin Distributions - Need more clarification
I will give a few examples of the tables we have:
Table1 - Most queries are State Code and Year, has about 1000 rows, will grow by 1000 rows a year, used Round Robin
Table2 - Most queries are by ...
- 11
0
votes
1
answer
332
views
Azure Dedicated SQL pool - group has db_datareader access but cannot login
We have created Test AD Group and they should have readonly access to the database (schemas and tables, views) in the Azure SQL dedicated pool.
Our DBA team did it but the users in the Test AD Group ...
- 11
0
votes
0
answers
670
views
Accessing Secured Storage account from Synapse Notebook
We have a Firewalled storage account which has a few files that we need to access via Synapse notebook. We do that with below code :
abfss_path = f'abfss://[email protected]....
- 2,288
0
votes
0
answers
743
views
Azure Synapse - Need to have table access to View?
I've been trying to find information on this for quite some time but I couldn't find any confirmed results. I know that we can grant access to view without granting explicit access to the base table ...
- 1
0
votes
1
answer
72
views
Can the Data Export Service in Microsoft Dynamics export data to an Azure Synapse Data Warehouse?
I am able to use the Data Export Service (DES) to export data to an Azure SQL Database. When I try to create a new profile in DES that connects to a Data Warehouse (aka Azure Synapse Analytics) on the ...
- 103
0
votes
0
answers
677
views
Best way to archive old data from SQL Azure?
I have an app that stores event data. We use SQL Azure. Right now it's very expensive. Over $800/month to host. Once the event is over, I want to move the data to a DB that has a lower cost. Thus this ...
- 585
-1
votes
1
answer
1k
views
How to copy data from Azure SQL DB into Azure SQL DW
I need to copy some data from an azure SQl DB instance into my azure data warehouse.
The most obvious way to do this is to use Elastic Query, similar to the below:
CREATE EXTERNAL DATA SOURCE SqlDb ...
- 1,244