Post
Ask Question

Azure Synapse Analytics - Exploring Query Editor

In this article, we’ll explore the query editor features in-built into Azure to explore the Data Warehouse in Azure Synapse Analytics. In the previous articles, we had been using Azure Data Studio to do the job but if we want to preview and explore without any external software installation, we can do it by following this article.

Azure Synapse Analytics

Azure Synapse is a limitless enterprise analytics service that enables us to get insight from data analytics and data warehousing. Using dedicated resources or serverless architecture, data can be queried and provides scalability as per the increase in the size of the data. You can learn more about it in the previous article, Azure Synapse Analytics. 

Data Warehouse

Data Warehouse can be understood as a core part of business intelligence which enables data analysis and reporting and are basically the major repository which consists of data that are integrated from different disparate sources. The endless possibility of data mining starts with Data Warehousing. 

This article is a part of the Azure Synapse Analytics Articles Series. You can check out other articles in the series from the following links. 

  1. Azure Synapse Analytics   
  2. Azure Synapse Analytics - Create Dedicated SQL Pool   
  3. Azure Synapse Analytics - Creating Firewall at Server-level   
  4. Azure Synapse Analytics - Connect, Query and Delete Data Warehouse SQL Pool   
  5. Azure Synapse Analytics – Load Dataset to Warehouse from Azure Blob Storage   
  6. Azure Synapse Analytics - Best Practices to Load Data into SQL Pool Data Warehouse  
  7. Azure Synapse Analytics – Restore Point 
  8. Azure Synapse Analytics – Exploring Query Editor 

Now, let us learn to explore our data warehouse using the Query Editor in Azure.

Step 1

First of all, login to Azure. Create a Dedicated SQL Pool and make sure to create the sample data warehouse to explore. Follow up Azure Synapse Analytics - Create Dedicated SQL Pool.

Step 2

Now, when the SQL Pool is created, visit the page.

Step 3

On the left-hand side, under Common Tasks you can view the Query Editor. Select it.

Now, fill in the login details. Here, my username was adminojash. Since we created the SQL Server login, use the SQL Server Authentication.

Once, done, click on Ok.

Step 4

Now, we can create a new Query. Do this by clicking on the New Query button on the menu.

A new query page will now open.

Step 5

We can now explore with our queries here.

Let us attempt to view our system databases with the following query. 

SELECT * FROM sys.databases 

Once we run the query, we can see the list of our databases, master and ojash-pool-warehouse we just created.

Step 6

Now, let us explore with a few more queries.

SELECT LastName, FirstName FROM dbo.dimCustomer 

Here, dimCustomer is basically a table and we are querying to display all the first and last name from the table.

Once, we run the query, we can see the list of all the names on the results. The query took 2 seconds to execute in full. 

Step 7

Let, us explore more.

SELECT EmailAddress FROM dbo.dimCustomer 

With this query, we listed all the email addresses in the table dbo.dimCustomer.

Step 8

If we want to see all the data in the table, we can use this query.

SELECT * FROM dbo.dimCustomer

 

We can see, everything within the table in the Results.

Moreover, in order to hide or list the tables in the database, use this button.

Step 9

We can also be more specific for conditions such as lastname and number of children. Here, we have the lastname as 'Malhotra' with 2 children.

SELECT LastName, FirstName FROM dbo.dimCustomer
WHERE LastName = 'Malhotra' AND NumberChildrenAtHome = 2;

Step 10

Similarly, we can explore all other tables. Here, we are doing for Geography and Employee and Organization.

SELECT * FROM [dbo].[DimGeography] 

SELECT * FROM [dbo].[DimEmployee] 

SELECT * FROM [dbo].[DimOrganization] 

Step 11

Furthermore, we can also explore the views which is basically a virtual table that is extracted from database.

SELECT * FROM [dbo].[SalesByCategory] 

SELECT * FROM [dbo].[SalesByCustomer] 

Step 12

In order to view the activities detail which shows session id, start and end time, importance, login, resource allocation and more, click on the Query Activity on the left side under Monitoring.

Deleting Resource

Step 13

Once you are done using the resource, make sure you delete them to save yourself from any unwanted charges that may incur.

Simply visit your home page and Select the resource you created. Here, the ojash-pool-warehouse.

On the page, you’ll see the Delete button. Click on it.

A dialog box will pop up. Write the name of the SQL Pool.

Once, done click on Delete. You’ll be notified with the process update.

Once, the process is completed, you’ll be notified with success. 

On the home page, you can refresh it to make sure the resource has now been deleted.

Conclusion

Thus, in this article, we learned about the Query Editor feature provided in Azure. With this, we can explore the databases here in our case, the Dedicated SQL Pool. This will save up our need to install or use the Azure Data Studio or other platforms. Howsoever, for more intensive task you would need SSMS and Azure Data Studio.


Similar Articles