Load data from Azure Blob storage into Azure SQL | Data Exposed
Вставка
- Опубліковано 5 жов 2024
- In this video, Anna Hoffman and Jeroen ter Heerdt discuss and show one way for loading data from Azure Blob storage into Azure SQL Database. For more info, see docs.microsoft...
[00:25] Background
[01:25] Azure SQL Notebook in Azure Data Studio
[02:06] Step 1: Create a table and schema
[02:20] Step 2: Create a master key
[02:37] Step 3: Create a database scoped credential
[03:16] Step 4: Create an external data source to the container
[04:15] Step 5: Bulk insert a single file and tips
[06:18] Summary
Follow Anna Hoffman and Jeroen ter Heerdt on Twitter at / analyticanna and / jeroenterheerdt .
More Data Exposed: • Data Exposed
At 3:15 she says she is creating a SAS token. That does not appear to be accurate. To be precise, that step she is performing refers to creating a database scoped credential, not SAS. A database scoped credential is required because Blob storage isn't configured to allow public (anonymous) access. We create the SAS separately and use the value to create the database scoped credentials for access to the database. Thank you
Excellent Example!
Good one!.
How to import image files from blob to azure sql database table ..?
The notebook can be found here:
github.com/MicrosoftDocs/mslearn-azure-sql-fundamentals/blob/master/02-DeployAndConfigure/loaddata/LoadData.ipynb?azure-portal=true
Great video! I blogged it! :)
Thanks for your support, Frank!
I've got it to work for one evening, the next day, no luck.
Getting this error:
Cannot bulk load because the file "[MYJSONFILE].json" could not be opened. Operating system error code 5(Access is denied.).
The blured screenshot does not show it but the secret must be enclosed in single quotes ' '
NOT Brakets [ ]
Thanks for the demo team, but I hv got a query,
Here SAS token is visible which raise security concern, what is the safer way of define external data source?
Or Can we access blob storage from Azure sql using managed identity?
Please reply...
Hi, It is possible to use Manage Identity instead of SAS token?
I followed exact same steps to set up master key, creds and the data source
And after that when I try query OPENROWSET on a blob file it fails with below error
Cannot bulk load because the file "DATESET/MASKpo1.csv" could not be opened. Operating system error code 5(Access is denied.).
Searched internet and many others have reported similar issues and resolutions but non works for me. If anyone having similar issues and found solution, please share
this only work with SQLSERVER in Azure? doen't on premise sqlserver 2016? thanks
great stuff :)
how to import data which is encrypted ?
Gr8 video.
Hello Sir,
I am seeing all your videos and all are very knowledgeable.
I have below query could you please help me with these.
I have 1 external table conatins 10 columns which reads data from processing blob file .But external table unable to read the blob file because few rows has 9 files and few have 10 and this is happening because last column if no data then no delimeter coming for that from SAP .So how can we assign last filed as NULL if not present through external table.
Thanks in advance
how can we bulk insert multiple blob files that have different columns into a single destination table
May be, Import them into Individual tables and then use a view to get your final table format.
Nice
Like Like🙂
I know God Anna congratulations u made it this far. This is my alpha personality jimjones
I followed exact same steps to set up master key, creds and the data source
And after that when I try query OPENROWSET on a blob file it fails with below error
Msg 4861, Level 16, State 1, Line 19
Cannot bulk load because the file "blobcontproducts/abc/820/folderyyyyyMM/test.xml" could not be opened. Operating system error code 5(Access is denied.).
Searched internet and many others have reported similar issues and resolutions but non works for me. If anyone having similar issues and found solution, please share