04-16-2024 10:19 AM
I see two articles on databricks documentations
https://docs.databricks.com/en/archive/azure/synapse-polybase.html#language-python
https://docs.databricks.com/en/connect/external-systems/synapse-analytics.html#service-principal
Polybase one is legacy one and is not supported anymore
Copy Into one is new one and it ideally should not need CONTROL access on whole dw
Here comes confusion:
looking at query structure itself I do not see any difference on how we access DW and code block looks exactly same, so how do we make sure we are using COPY option and not POLYBASE?
I ran code and it is still giving access issue, I gave read access on schema and table. CREATE access on DW & ADMINISTER DATABASE BULK OPERATIONS permissions as mentioned in document.
Not clear what am I missing here.
04-17-2024 04:51 AM
Hi @dilkushpatel, Thank you for sharing your confusion regarding PolyBase and the COPY INTO command in Databricks when working with Azure Synapse.
PolyBase (Legacy):
COPY INTO:
Access Issue:
Remember that COPY INTO is the way forward, and it should not require CONTROL access on the entire data warehouse.
Feel free to share more details or ask additional questions—I’m here to help! 😊
04-17-2024 05:23 AM
Hi @Kaniz_Fatma
thanks for taking time and wiriting detailed text.
So i'm not writing exlicit copy into, I'm just trying to read from table in SQL DW, syntax looks like this
04-17-2024 06:01 AM
Hi @dilkushpatel,
abfss://<storage container>@<storage account>.dfs.core.windows.net/temp-data
) is accurate.04-17-2024 10:31 AM
Hi @Kaniz_Fatma
Double-check the credentials (username and password) you’re using. Ensure they are correct and have the necessary privileges.
Im able to run select using same user from SSMS
Confirm that the server name and port are accurate.
They are
You mentioned blocking where you assign storage app ID and secret for access from both DW and Databricks.
Sorry I mean code block.
Ensure that the storage app ID and secret are correctly configured and have the required permissions to access the storage container.
They are correct, I can list and read files from that location.
Verify that the storage container path (abfss://<storage container>@<storage account>.dfs.core.windows.net/temp-data) is accurate.
It is
Confirm that the firewall rules on the Azure Synapse side allow connections from the Databricks cluster
IP blocking is not configured, everyone from anywhere can connect to DW
Whitelist the Databricks cluster’s IP address if necessary.
IP blocking is not configured, everyone from anywhere can connect to DW
Temporarily grant the user more permissions (e.g., CONTROL on the entire data warehouse) to see if the issue persists.
Will try this and update
If it works with additional permissions, gradually reduce them to the minimum required.
As such other than control rest permissions look fine
Consider using the COPY INTO command (which is the recommended method) for loading data into Azure Synapse.
Im not loading anything into Synapse DW. Reading from DW
If you decide to switch, ensure that your Databricks Runtime version supports COPY INTO.
Databricks runtime version is 13.1 if I remember correctly
Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.
If there isn’t a group near you, start one and help create a community that brings people together.
Request a New Group