Creating a table in Azure Database using Azure Data Studio
What is Azure Data Studio
In our previous post we create a table using the Query Editor, which is a tool provided within the Azure portal. Today I wanted to add a short post on how to do the same thing while using Azure Data Studio.
Azure Data Studio is a free tool that is provided by Microsoft and it is an editor that allows you to connect to a number of different sources and run your queries against them. All you would need to do is install the tool and then set up the connection to your Azure Database (or Azure Date Warehouse).
If you want to see how to create tables through Azure portal using Query Editor please see my previous post, "Blob Storage to Power BI part 4: Creating a table in Azure Database", for information on how to do that.
If you want more information about what Azure Data Studio is please follow the link to "What is Azure Data Studio?".
How to install Azure Data Studio
To install Azure Data Studio click on the following link: "Download and install Azure Data Studio". Then follow the instructions to install it for your specific OS version. Since I am running Windows 10 I clicked on "User Installer (recommended)" option.
 |
| Well this does not seem too hard. |
After the file is downloaded click on the execution file and then follow the instructions to install it on your local machine and keep the default settings.
When the installation finishes click on launch Data Studio and it will pop up requesting you to set up your connection information.
 |
| Great, even more information to fill out. |
Configuring Azure Data Studio
To get the required information we will have to go back to the azure portal and grab the server details from the SQL Database in the Overview page. The main information that we will need from that page is the SQL server, userid, and password.
After getting all of that information enter it into Azure Data Studio and then click on "Connect". Make sure to change the "Authentication type" to SQL log in. This is so that we can enter the userid and password that we created in the post about SQL Server.
 |
| Selecting a database is optional, but if you want to it will take up to a minute for Azure Data Studio to bring up a list of all the DBs. You can always leave it on default and specify later. |
Click "Connect" to continue.
After a few seconds you then should see the main console from where you will do your work.
From this main console you are able to view a lot of information so let's take it step by step.
First under the Dashboard you are able to see what type of Azure Database this is. It says that the Edition and Pricing tiers is Basic (which is the level that we selected) and that the owner is dbadmin (which is also the same userid that we created).
Next, under tasks, you have a couple of options. You can select "New Query" and it will open up a Query tab similar to what you saw in the Query Editor in the previous post.
You also have the option of creating a "Notebook". I have not used this feature, but from my understanding it is essentially like Jupyter or Databricks notebooks where you can chain different SQL statements together.
 |
| To read more about this follow click on "How to use notebooks in SQL Server" |
You can also see that any open Query and Notebook will show up on the left hand side. You can save each notebook or Query to your desktop and have it be ready next time you open up Azure Data Studio. One note is that you cannot name Queries until you saved them. Until you do any new ones that you will create will just be called Query1, Query2, and so on.
There is definitely too much stuff about Azure Data Studio that I can't cover in this blog post, but if you are interested in finding out all of the details click on "What is Azure Data Studio?" and then hover over "How to guides" which should have a lot of information.
 |
| Happy reading!! |
For our purposes we will be focusing on the "New Query" page to create our tables.
Creating tables using Azure Data Studio
So since we have already created the required tables in Query Editor this guide won't be too extensive. Both Query Editor and Azure Data Studio have the same syntax (as far as our POC ) is concerned so creating tables in either one will be very easy.
For example lets recreate the POC Stage table (I will drop the table using Azure Data Studio first). In Azure Query Editor the code looks like this:
CREATE TABLE POC_Stage (
Customer varchar(255),
UPC_EIN varchar(255),
Sale_amt_TY decimal(13,2),
Sale_amt_PY decimal (13,2),
"Date_of_Sales _week" DATE
)
In Azure Data Studio the code will look like this:
 |
 |
| Although it highlighted everything in red it still took it when I ran it. |
So as you can see Azure Data Studio is a good addition to your tool set and is a much nicer interface than using Query Editor in the Azure Portal.
In my next blog post I will explore SQL Server Management Studio (SSMS) and how you can do the same thing using that tool.
how to view the created table on azure portal?
ReplyDeleteHi Niharika,
DeleteTo view the table that was created in Azure portal follow these steps:
1) Log into portal
2) Enter databases in the search bar
3) Select the database that you have created
Once at that screen you should see a tab called "Query Editor". If you click on that link you will be brought to the next page on the right where it asks for a user id or password.
After entering that and click on log in button you should be brought to the Query Editor screen and you should see a folders like "Tables", "View" or "Stored Procedure" on the right.
Just expand the "Tables" folder and you should see all the tables that you created.
Let me know if this helps.