View source for Exam DP203 Hybrid Transactional Analytical

''Hybrid Transactional / Analytical Processing'' (HTAP). This is where '''Azure Synapse Link''' replicates transactional data into an analysis data store.

From CosmosDB: Azure Synapse Link for CosmosDB

For Azure SQL Database or a SQL Server instance: Azure Synapse Link for SQL (to a dedicated pool, and a spark pool can connect to that dedicated pool).

Azure Synapse Link for Dataverse. Where Dataverse (the Power Platform database) is the OLTP database. Note that here the data goes into Gen2, not a dedicated pool for some reason.

== CosmosDB ==
First need to '''Enable Azure Synapse Link''' in the '''CosmosDBb''' resource in the Portal by clicking a button.

Alternatively this can be done in the '''Azure CLI''' by running:
<pre>
az cosmosdb update --name my-cosmos-db --resource-group my-rg --enable-analytical-storage true
</pre>

or '''Powershell''':
<pre>
Update-AzCosmosDBAccount -Name "my-cosmos-db" -ResourceGroupName "my-rg" -EnableAnalyticalStorage 1
</pre>Once enabled it cannot be disabled.

=== Dynamic Schema Maintenance ===
As schema changes are made in the upstream OLTP, these get replicated down to the Analysis database. JSON.

There are two types

'''WellDefined''': First instance of data in the JSON determines the data type

'''FullFidelity''': Each instance of data is sent with its data type, so allowing for changes in data type. Is only used with MongoDB.

I think CosmosDB is implemented with containers. You can enable Azure Synapse Link in them either by configuring this in these containers, or by (as above) configuring it as the resource directly in the portal.

Or in the Azure CLI:

<pre>
az cosmosdb sql container create --resource-group my-rg --account-name my-cosmos-db --database-name my-db --name my-container --partition-key-path "/productID" --analytical-storage-ttl -1
</pre>

or Powershell:
<pre>
New-AzCosmosDBSqlContainer -ResourceGroupName "my-rg" -AccountName "my-cosmos-db" -DatabaseName "my-db" -Name "my-container" -PartitionKeyKind "hash" -PartitionKeyPath "/productID" -AnalyticalStorageTtl -1
</pre>Like with the resource, this config cannot be removed without deleting the container.

=== Linked Service ===
Next, in Synapse Studio, you need to create a linked server to the CosmosDB, to be able to pull the data into Synapse. "Connect to external data".

== Query from Spark ==
You can apparently use a Spark pool to query the CosmosDB using the linked service.
<pre>
 df = spark.read
     .format("cosmos.olap")\
     .option("spark.synapse.linkedService", "my_linked_service")\
     .option("spark.cosmos.container", "my-container")\
     .load()

display(df.limit(10))
</pre>It says "The data is loaded from the analytical store in the container, not from the operational store"

You can also write data using: mydf.write.format

and you can run SQL code on it.

== Query from Serverless pool ==
In addition to using a Spark pool, you can also query an Azure Cosmos DB analytical container by using a built-in ''serverless'' SQL pool in Azure Synapse Analytics

<pre>
SELECT *
FROM OPENROWSET(
    'CosmosDB',
    'Account=my-cosmos-db;Database=my-db;Key=abcd1234....==',
    [my-container]) AS products_data
</pre>

If the source JSON contains multi-level data, like:

<pre>
{
    "productID": 126,
    "productName": "Sprocket",
    "supplier": {
        "supplierName": "Contoso",
        "supplierPhone": "555-123-4567"
    }
    "id": "62588f072-11c3-42b1-a738-...",
    "_rid": "mjMaAL...==",
    ...
}
</pre>

then you can use a select statement like the following, with the methods of parsing these levels given with the '''WITH''' statement in the select:

<pre>
SELECT *
 FROM OPENROWSET(PROVIDER = 'CosmosDB',
                 CONNECTION = 'Account=my-cosmos-db;Database=my-db',
                 OBJECT = 'my-container',
                 SERVER_CREDENTIAL = 'my_credential'
 )
 WITH (
    ProductNo INT '$.productID',
    ProductName VARCHAR(20) '$.productName',
    Supplier VARCHAR(20) '$.supplier.supplierName',
    SupplierPhoneNo VARCHAR(15) '$.supplier.supplierPhone'
 ) AS products_data
</pre>Provision your Azure Cosmos DB analytical storage and any client applications (for example Microsoft Power BI) in the same region as serverless SQL pool.

Azure Cosmos DB containers can be replicated to multiple regions. If you have a multi-region container, you can specify a <code>region</code> parameter in the OPENROWSET connection string to ensure queries are sent to a specific regional replica of the container.

== Azure Synapse Link for SQL ==
From Azure SQL Database or SQL Server 2022, but not Azure SQL Managed Instance

To a Dedicated SQL Pool

It uses the '''change feed''' feature in the source, with changes recorded in the source transaction log either being directly applied to the destination, or being written to files intermediary.

You must assign a system assigned managed identity and have the correct firewall rules.

<pre>
CREATE USER my_synapse_workspace FROM EXTERNAL PROVIDER;
ALTER ROLE [db_owner] ADD MEMBER my_synapse_workspace;
</pre>Then create a linked Service in Synapse Studio to point to the source db

Choose the number of CPU cores to use.

Initial synchonisation by copying a parquet file for each table. But thenafter once synchronised it copies CSV files.

A self-hosted integration runtime is run on your source machine if on-premise SQL Server.

Note you can't use the default Gen2 storage - you must create a new account within your subscription.

In the source, run:

<pre>
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'my$ecretPa$$w0rd';
</pre>Also run: CREATE MASTER KEY in the destination.

In Synapse Studio, create linked services for the source SQL Server/Azure SQL Database, and the Gen2 storage (which will be used as the landing zone).

In Synapse Studio, on the '''Integrate''' page, on the ＋ drop-down menu, select '''Link connection'''