Enable a connection to an analytical store and query from Azure Synapse Spark or Azure Synapse SQL

Before delving into enabling a connection to an analytical store and running queries from Azure Synapse Spark or SQL, we need to understand these two services by Microsoft Azure.

Azure Synapse Spark is an analytics service that brings the best of both data lakes and data warehouses. Essentially, it combines big data and data warehousing for limitless analytics. Azure Synapse SQL, on the other hand, provides SQL-based analytics based on the control plane, on-demand serverless, and provisioned compute pool.

2. Enabling Connection to an Analytical Store

The first step towards analyzing data with Azure Synapse is enabling your Cosmos DB account’s Analytical store. Follow these steps to enable an analytical store for a container.

• Login to the Azure portal.
• Go to your Azure Cosmos DB account.
• Navigate to the “Features” tab and turn on the “Analytical Store” feature.
• Save your changes.

Once enabled, it automatically transforms transactional data in near-real-time facilitating analytical workloads.

3. Querying with Azure Synapse

Using both Azure Synapse Spark and Azure Synapse SQL give you distinct capabilities when it comes to querying.

A. Azure Synapse Spark

Apache Spark in Azure Synapse incorporates the native capabilities of Apache Spark. It simplifies big data pipelines. The steps to use Spark to connect to the analytical store are as follows:

• Create a spark session. The spark session is a single gateway to interact with Spark functionalities.

val Cosmos_conn_config = {
"spark.cosmos.accountEndpoint": "",
"spark.cosmos.accountKey": "",
"spark.cosmos.database": "",
"spark.cosmos.container": ""
}

imple SparkSession.builder().appName("Spark SQL basic example").config(Cosmos_conn_config).getOrCreate()


• Next, you can read the data in the analytical store using the read method.

val df = spark.read.format("cosmos.olap").option("spark.synapse.linkedService", "CosmosDb1").load()

B. Azure Synapse SQL

Azure Synapse SQL allows you to use the power of T-SQL to read, write, and manipulate data. Here’s how you can use SQL with the analytical store.

• Navigate to your Azure Synapse workspace in the Azure portal.
• In the Synapse Studio, navigate to the ‘Develop’ tab.
• Create a new SQL script, and in the script use the ‘OPENROWSET’ function to read the data.

SELECT *
FROM
OPENROWSET(
'CosmosDB',
'account=;database=;key=',
)


4. Conclusion

With the ability to enable a connection to an analytical store from Azure Synapse Spark or Azure Synapse SQL, Microsoft allows for easier data analysis. Both services have distinct capabilities, and choosing between them depends on the specific needs of your analytical workload. With Azure Synapse’s robustness and Cosmos DB analytical store’s real-time data transformation, handling data has never been easier.

Practice Test

True/False: Azure Synapse Spark and Synapse SQL can be used to query data from an Azure Cosmos DB analytical store.

• True
• False

Answer: True

Explanation: Both Azure Synapse Spark and Synapse SQL have tools that allow users to query data from an Azure Cosmos DB analytical store.

What Azure service allows you to enable a connection to an analytical store?

• A. Azure Synapse SQL
• B. Azure Synapse Spark
• C. Azure Cosmos DB
• D. Azure Cognitive Services

Answer: C. Azure Cosmos DB

Explanation: Azure Cosmos DB is the service that houses the analytical store that you wish to connect to.

True/False: Using Azure Synapse SQL to query data from an Azure Cosmos DB analytical store requires ETL (Extract, Transform, Load) operations.

• True
• False

Answer: False

Explanation: The integration of Azure Synapse SQL with Azure Cosmos DB eliminates the need for ETL operations.

Multiple select: Which of the following platforms can be utilized for querying from Azure Cosmos DB analytical store?

• A. Azure Cognitive Services
• B. Azure Synapse SQL
• C. Azure Synapse Spark
• D. Azure Machine Learning

Answer: B. Azure Synapse SQL and C. Azure Synapse Spark

Explanation: Both Azure Synapse SQL and Synapse Spark provide tools and functionalities that allow users to query data from an Azure Cosmos DB analytical store.

True/False: Analytical store in Azure Cosmos DB is always enabled by default.

• True
• False

Answer: False

Explanation: Analytical store is not enabled by default. It needs to be specifically enabled for each container where analytical store is needed.

What operation is needed to move operational data into an analytical store in Azure Cosmos?

• A. Export
• B. Import
• C. No operation
• D. Load

Answer: C. No operation

Explanation: The movement of operational data into an analytical store happens automatically once it is enabled in Azure Cosmos DB.

True/False: It is not possible to run real time analytics on operational data in Azure Cosmos DB.

• True
• False

Answer: False

Explanation: The analytical store feature in Azure Cosmos DB automatically syncs with the transactional data, allowing real-time analytics on operational data.

Which query language is used in Azure Synapse SQL to retrieve data from Azure Cosmos DB analytical store?

• A. SQL
• B. Python
• C. JavaScript
• D. C#

Answer: A. SQL

Explanation: Azure Synapse SQL uses SQL language for querying data from Azure Cosmos DB analytical store.

Multiple select: Which of the following querying capabilities are supported by Azure Synapse Spark when dealing with Azure Cosmos DB analytical store?

• A. Tabular data queries
• B. Batch processing
• C. Streaming
• D. Failover

Answer: A. Tabular data queries, B. Batch processing, C. Streaming

Explanation: When querying the analytical store, Azure Synapse Spark supports tabular data queries, batch processing, and streaming.

True/False: Making changes to your operational data automatically updates your analytical data instantly.

• True
• False

Answer: True

Explanation: Any changes in the operational data in Cosmos DB are automatically synced with the analytical store.

Interview Questions

What is the Azure Synapse Spark?

Azure Synapse Spark is an analytics service that provides rapid, on-demand processing of massive data streams and files for big data analytics and reporting. It’s a part of Azure Synapse Analytics.

How does Azure Synapse SQL work?

Azure Synapse SQL allows querying data using either serverless SQL pool or a dedicated SQL pool within Azure Synapse Analytics. This allows for on-demand exploration of data and enables decision-making processes based on real-time data analysis.

What is the use of connecting Azure Synapse Spark or Azure Synapse SQL to an analytical store?

Connecting Azure Synapse Spark or Azure Synapse SQL to an analytical store allows to run large scale analytics workloads by processing and analyzing large volumes of raw data stored in the analytical store.

How can you enable a connection to the analytical store in Azure Cosmos DB?

You can enable the connection to the analytical store in Azure Cosmos DB using Azure portal, CLI, PowerShell, or ARM templates. You just need to set the ‘analyticalStoreEnabled’ property to ‘true’.

What type of query language does Azure Synapse SQL use?

Azure Synapse SQL uses the SQL query language which is familiar and worldwide-accepted for its ability to pull valuable data insights.

What is the purpose of Azure Synapse Analytics?

Azure Synapse Analytics brings together enterprise data warehousing and big data analytics. It helps to ingest, prepare, manage, and serve data for immediate business intelligence and machine learning needs.

What is the function of Azure Cosmos DB in relation to Azure Synapse Spark or Azure Synapse SQL?

Azure Cosmos DB serves as the globally distributed, multi-model database service where data can be ingested and then analyzed by Azure Synapse Spark or Azure Synapse SQL.

How can you access the analytical store from Azure Synapse Spark?

You can access the analytical store from Azure Synapse Spark using either the Spark connector for Azure Cosmos DB or the built-in Azure Synapse Notebook.

What is the Azure Cosmos DB Spark connector?

The Azure Cosmos DB Spark connector provides an interface for reading from and writing to Azure Cosmos DB from Apache Spark jobs. It enables processing and analytics of large scale data.

How can you query data from an analytical store using Azure Synapse SQL?

Data can be queried from an analytical store using Azure Synapse SQL by creating external tables in Azure Synapse SQL pointing to the analytical store and then using SQL statements for querying data from these tables.

Can Azure Synapse SQL and Azure Synapse Spark access the transactional store of Azure Cosmos DB?

No, Azure Synapse SQL and Azure Synapse Spark cannot access the transactional store of Azure Cosmos DB; they can only access the data in the analytical store.

How does Azure Cosmos DB’s analytical store handle analytical queries?

Azure Cosmos DB’s analytical store performs analytical queries by separating transactional and analytical workloads. This reduces the performance impact on the transactional workload and provides analytics performance at any scale.

Does Azure Synapse Analytics support real-time analytics?

Yes, Azure Synapse Analytics, particularly through its integration with Azure Cosmos DB’s analytical store, can process and analyze real-time data, enabling organizations to make timely business decisions.

What permissions do you need to access Azure Cosmos DB’s analytical store from Azure Synapse SQL?

You need to have either the Azure Cosmos DB Account Reader role or the Azure Cosmos DB SQL Role-Based Access Control (RBAC) Reader role to access Azure Cosmos DB’s analytical store from Azure Synapse SQL.

Is it possible to disable the analytical store once it is enabled in Azure Cosmos DB?

No, once you enable the analytical store in Azure Cosmos DB, it cannot be disabled.