Questions tagged [azure-synapse]
Questions about Azure Synapse Analytics, bringing together enterprise data warehousing and big data analytics.
azure-synapse
2,879
questions
15
votes
4
answers
20k
views
DROP TABLE IF EXISTS not working on Azure SQL Data Warehouse
I used the SQL Server management studio to generate script against Azure Data Warehouse. I selected Edition Azure Data Warehouse, it generates below the script to drop table if it exists and create ...
- 327
14
votes
4
answers
26k
views
How to calculate Azure SQL Data Warehouse DWU?
I am analyzing Azure SQL DW and I came across the term DWU (Data warehouse units). The link on Azure site only mentions a crude definition of DWU. I want to understand how DWU is calculated and how ...
- 472
12
votes
3
answers
14k
views
What is a difference between table distribution and table partition in sql?
I am still struggling with identifying how the concept of table distribution in azure sql data warehouse differs from concept of table partition in Sql server?
Definition of both seems to be ...
- 147
11
votes
4
answers
22k
views
Do you know how to install the 'ODBC Driver 17 for SQL Server' on a Databricks cluster?
I'm trying to connect from a Databricks notebook to an Azure SQL Datawarehouse using the pyodbc python library. When I execute the code I get this error:
Error: ('01000', "[01000] [unixODBC][Driver ...
- 148
11
votes
3
answers
14k
views
how to change Data Factory in Microsoft Integration Runtime COnfiguration Manager?
I have Installed Microsoft Integration Runtime configuration Manager When I have Migrated Data from On-Premise SQL Server to Azure Data Lake and when I'm trying to use for another Azure Data Factory I ...
- 430
11
votes
4
answers
30k
views
How to set any column as primary key in Azure SQL data warehouse
I am trying to set one column in DB as primary key but I always get this error:
Enforced unique constraints are not supported in Azure SQL Data Warehouse. To create an unenforced unique constraint ...
- 151
10
votes
4
answers
23k
views
Insert values statement can contain only constant literal values or variable references in SQL Data Warehouse
Consider this table:
CREATE TABLE t (i int, j int, ...);
I want to insert data into a table from a set of SELECT statements. The simplified version of my query is:
INSERT INTO t VALUES ((SELECT 1), ...
- 218k
9
votes
1
answer
14k
views
Data Factory, Synapse Analytics and DataBricks Comparison
I'm little bit new on Azure and I'm wondering when is recommendable to use ADF, Synapse, or DataBricks. What are their use cases for best practices and performance?
Could you help me with this ...
- 413
9
votes
3
answers
8k
views
SQLAlchemy error: An attempt to complete a transaction has failed. No corresponding transaction found
I have installed:
Ubuntu (18.04)
Python (3.6.8)
msodbcsql17 (Microsoft ODBC Driver 17 for SQL Server)
SQLAlchemy (1.3.5)
Pandas (0.24.2)
and I want to create just a proof of concept using SQLAlchemy ...
9
votes
2
answers
7k
views
Azure Data Explorer (ADX) vs Polybase vs Databricks
Question
Today I discovered another Azure service called Azure Data Explorer (ADX). Sorry for such comparison of services, I have good understanding of all except ADX. I feel like there is a big ...
- 45.4k
8
votes
2
answers
11k
views
Not able to create table in Azure Synapse Analytics
I am new in Azure Synapse Analytics. I have created database in workspace but when I am trying to create a new table it is showing below error.
Failed to execute query. Error: CREATE TABLE <...
- 2,113
8
votes
2
answers
12k
views
Azure Analysis Services versus Synapse Analytics
Can someone explain what is the difference between Azure Analysis Services and Azure Synapse Analytics? Why would one use Analysis Services over Synapse Analytics?
Thanks,
- 533
7
votes
3
answers
9k
views
How to retrieve View definition on Synapse (Azure SQL DW)?
I am new to Synapse (Azure SQL DW). Currently, the DW has lots of views and I need to modify a handful of them. The issue is that I do not know how the views were created.
Is there a query to check ...
- 271
7
votes
1
answer
2k
views
Optimal SSIS data flow settings to load to stage table in Azure SQL DW
I have a 350MB table that's fairly wide with two varchar(2000) columns. Via an SSIS data flow it takes 60 minutes to load via OLEDB "fast load" destination to Azure SQL DW. I changed the destination ...
- 11.6k
7
votes
2
answers
11k
views
Azure Synapse Analytics | Login failed for user '<token-identified principal>'
I have created a dedicated SQL pool in Azure Synapse Analytics. When I am trying to create a table in my dedicated SQL pool then it is showing below error.
Login failed for user < token-identified ...
- 2,113
7
votes
1
answer
2k
views
Azure SQL Data Warehouse and Entity Framework
Does anybody know if/when we will be able to use Entity Framework with the Azure SQL Data Warehouse? I have searched high and low and cannot seem to find anything other than a question posted on MSDN ...
- 2,317
7
votes
0
answers
534
views
Synapse Spark - Deltalake configs for Schema evolution and write optimizations
I am looking for databricks equivalent properties in Synapse spark. Please let me know if there are any or workaround for the same.
Using MERGE command to Insert/update the data. However, it does not ...
- 71
7
votes
3
answers
6k
views
Spark pool taking time to start in azure synapse Analytics
I have created 3 different notebook using pyspark code in Azure synapse Analytics. Notebook is running using spark pool.
There is only one spark pool for all 3 notebook. when these 3 notebook run ...
- 113
6
votes
6
answers
23k
views
Access issue with Azure Synapse studio
I created a Synapse workspace in my Azure Portal and tried opening the Synapse studio and I received the following error:
Failed to load one or more resources due to No access, error code 403.
...
- 1,311
6
votes
3
answers
11k
views
Azure SQL Data Warehouse DWU vs Azure SQL DTU
I am considering migration from Azure SQL to Azure SQL Data Warehouse. It seems to offer some of the features that we need, however price is a concern for starting small. 100 DWU Data Warehouse is ...
- 1,051
6
votes
2
answers
17k
views
EXTERNAL TABLE access failed due to internal error: 'Java exception raised on call to HdfsBridge_IsDirExist. Java exception message:
I am trying to create external table through polybase with below syntax on Visual Studio 2015. Its giving me the below error. Can some one pls help on this
CREATE EXTERNAL TABLE dbo.DimDate2External (...
- 371
6
votes
1
answer
14k
views
List content of a directory in Spark code in Azure Synapse
In Databricks' Scala language, the command dbutils.fs.ls lists the content of a directory. However, I'm working on a notebook in Azure Synapse and it doesn't have dbutils package. What is a Spark ...
- 403
6
votes
2
answers
13k
views
Rename Object not supported in Azure SQL Data Warehouse?
I tried to run the following commands in SQL DW:
RENAME OBJECT dbo.test TO test2
RENAME OBJECT test TO test2
Both failed with the following error:
No item by the name of '[DemoDB].[dbo].[test]' ...
6
votes
3
answers
3k
views
Azure SQL Data Warehouse ''NoCount' Error
I just got approved for the Azure SQL Data Warehouse Preview, and just finished "provisioning" my new server and database. I followed a link to a 'Getting Started' page from Microsoft (Get started: ...
- 544
6
votes
2
answers
6k
views
insert on synapse DW in ssms
simple insert code but i keep getting syntax errors the values lines have a value for each column in the table, it only has 3 columns, i've tried removing the comma, tried using semi colon tried ...
6
votes
6
answers
18k
views
Using Delta Tables in Azure Synapse Dedicated/Serverless SQL Pools
I am currently employed as a Junior Data Developer and recently saw a post saying that Azure Synapse can now create SQL tables from Delta tables. I tried creating an SQL table from a Delta table which ...
6
votes
1
answer
4k
views
Azure Databricks to Azure SQL DW: Long text columns
I would like to populate an Azure SQL DW from an Azure Databricks notebook environment. I am using the built-in connector with pyspark:
sdf.write \
.format("com.databricks.spark.sqldw") \
.option(...
- 3,710
6
votes
2
answers
7k
views
Error accessing Key Vault while executing Azure Synapse Notebook via Synapse Pipelines
I'm trying to execute an Azure Synapse Notebook using Notebook Activity in Synapse Pipelines and it keeps coming up with errors while debuging the Pipelines, the Notebook is using TokenLibrary....
6
votes
1
answer
7k
views
Write data to Azure Data Lake Storage Gen 2 using Azure Synapse Analytics notebook
I am connecting to a RESTful api using Azure Synapse Analytics notebook and write the json file to Azure Data Lake Storage Gen 2.
pyspark code:
import requests
response = requests.get('https://api.web....
- 890
6
votes
2
answers
5k
views
Foreign Key constraints Synapse Azure
Am currently creating a datawarehouse in Azure Synapse, however Synapse does not allow for the creation of foreign keys. This is vital for referential integrity between the fact and dimension table. ...
- 79
6
votes
1
answer
2k
views
Handling embedded new lines when creating/selecting External Tables in SQL Data Warehouse
In SQL Data Warehouse (editors please don't change this, it is the actual name see: here) I have a JobCandidate_ext external table that looks like this.
CREATE EXTERNAL TABLE [HumanResources].[...
- 618
6
votes
0
answers
929
views
mssparkutils on local python environment
I'm building some reusable libraries to use in Azure synapse. And these libraries need mssparkutils, built-in synapse library. Is it possible to install this library/module on local python environment?...
- 61
5
votes
6
answers
5k
views
Azure SQL Data Warehouse Surrogate Keys
So Azure SQL Data Warehouse doesn't support identity columns, and therefore it's tricky to deal with surrogate keys.. anyone got any bold solutions on this one?
This is best i have found, and it's ...
- 1,387
5
votes
3
answers
7k
views
Drop Database Error For Azure Synapse Database
When I try to drop a database in my builtin synapse pool, I'm getting the following error:
Cannot drop database "database name" because it is currently in use.
I've tried in both SSMS and ...
5
votes
2
answers
4k
views
SQL Azure Data warehouse Dynamic SQL Select query
In Azure SQL Data Warehouse stored procedure I try to form Dynamic SQL and perform select query,I unable to fetch the resutSet
Below is code Snippet:
DECLARE @sql nvarchar(400)
DECLARE @cnt int
BEGIN
...
- 273
5
votes
1
answer
3k
views
Unable to Query Serverless Pool View in Azure Synapse using SQL Admin Credentials
I have set up a Serverless SQL pool in Azure Synapse that is querying a view I had set up of a linked Azure Data Lake.
CREATE VIEW DeviceTelemetryView
AS SELECT corporationid, deviceid, version, ...
- 1,462
5
votes
2
answers
11k
views
Rename Column in Azure Data Warehouse
I know I can accomplish my objective with this process:
Create new column
Update new from old
Drop old
I am looking for a way to do this with one command. I know that remane object works at the ...
- 20.8k
5
votes
1
answer
2k
views
OPENJSON collation in Azure Synapse causes a collation conflict error
I have an OPENJSON command that takes the parsed JSON and LEFT joins it onto an existing table.
When I add the LEFT JOIN I get the error:
collation conflict between "Latin1_General_BIN2" ...
- 3,343
5
votes
2
answers
8k
views
Azure synapse Merger syntax error for Merge into TSQL
Just would like to know what's the problem of the following merge .
thanks
- 749
5
votes
2
answers
2k
views
Character length over 130 does not show in column
I have a lot of questions from a survey im using for a pivot table. To collect all the questions to my pivot dynamically im using stuff and for xml path. However it seems like question text > 130 in ...
- 3,591
5
votes
3
answers
13k
views
UPDATE FROM in Azure SQL DW?
I'm receiving an error in Azure SQL DW trying to do an UPDATE FROM query. The error is "FROM clause in UPDATE and DELETE statements cannot contain subquery sources or joins"
Is this just specific to ...
- 10k
5
votes
1
answer
3k
views
Stored Procedure result into temp table in Azure Data Warehouse
In Azure Data Warehouse, i have a Stored Procedure which will return the result of SELECT command.
How to push the Stored Procedure result into temp table?
I tried the below query and it is ...
- 103
5
votes
1
answer
968
views
Azure SQL Data Warehouse and PolyBase can't read CSV files generated using U-SQL and ADLA
I have a U-SQL script that generates a CSV file in Azure storage and an external table in Azure SQL Data Warehouse (SDW) using PolyBase to read that CSV file.
When the CSV file is generated using the ...
- 73
5
votes
1
answer
2k
views
How does Azure SQL DW know the row count without statistics?
If I run a CREATE EXTERNAL TABLE cetasTable AS SELECT command then run:
EXPLAIN
select * from cetasTable
I see in the distributed query plan:
<operation_cost cost="4231.099968" accumulative_cost=...
- 11.6k
5
votes
2
answers
6k
views
Recommended tools to ETL data from Azure SQL Database to Azure SQL Data Warehouse? [closed]
I have a requirement to build a data warehouse using Azure SQL Data Warehouse, sourced with data from a single Azure SQL Database. Looking at this article, it seems I have two options:
SSIS
Azure ...
- 2,187
5
votes
2
answers
3k
views
Polybase create external datasource giving syntax error
I'm trying to execute the following command in Azure Data Warehouse:
CREATE EXTERNAL DATA SOURCE Brand WITH (TYPE=HADOOP, LOCATION ='wasbs://[email protected]' CREDENTIAL = ...
- 153
5
votes
1
answer
4k
views
can we execute sql query in serverless pool from pipeline azure synapse?
Can we execute SQL query in the serverless pool from the pipeline in Azure Synapse?
We are trying to run the SQL query in the Synapse pipeline, we are not able to find a way to execute the same from ...
5
votes
2
answers
4k
views
Dump table and view DDL from Azure SQL Data Warehouse
How does one dump/export the DDL at the schema-level on Microsoft's Azure SQL Data Warehouse?
- 558
5
votes
4
answers
3k
views
Azure SQL Data Warehouse Db Development Tools
With the new release of SSDT I noticed the addition of Azure objects such as External Tables and such. However in the Target Db platform Configuration of the Database project I do not see Azure SQL ...
5
votes
2
answers
589
views
Why does Group by Grouping Sets work on SQL Server and not on the Azure SQL Data Warehouse?
The Group By Grouping Sets, Cube and Rollup work on SQL Server, but they don't appear to be supported yet on the Azure SQL Data Warehouse. The same goes with some Ordered Analytics that use the ...
