Azure Databricks
An Apache Spark-based analytics platform optimized for Azure.
changetable on notebook and sql query in one query
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(140.8, 75%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESR%3C/text%3E%3C/svg%3E)
Shambhu Rai
1,411
Reputation points
Hi Expert ,
how to run a query on notebook and azure sql table in one query on ADF Lookup
inside query on notebook: select col1 from table2
inside query azure sql table:select col1 from table1
select count(1) As CurrentChangeTrackingVersion
from
CHANGETABLE(CHANGES ['@{activity('Notebook1').output.runOutput}'],
@{activity('LookupLastTrackingVersionActivity').output.runOutput}) a
i am able to run single statement on notebook and sql database. but change table is not working on notbook
Azure Databricks
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
{count} votes
-
Shambhu Rai 1,411 Reputation points
2023-10-02T04:08:43.8266667+00:00 Suggestion pls
-
Shambhu Rai 1,411 Reputation points
2023-10-02T10:08:28.62+00:00 suggestion pls
-
Shambhu Rai 1,411 Reputation points
2023-10-02T14:50:58.5+00:00 suggestion pls
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(268.8, 8%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EST%3C/text%3E%3C/svg%3E)
Smaran Thoomu 32,535 Reputation points Microsoft External Staff Moderator2023-10-04T14:38:29.8366667+00:00 @Shambhu Rai
To run a query on a notebook and Azure SQL table in one query on ADF Lookup, you can use the CHANGETABLE function. The CHANGETABLE function returns a table that contains the changes made to a table since the last synchronization. You can use this function to track changes made to a table and synchronize them with another table.
To use CHANGETABLE in ADF Lookup, you can follow these steps:- In your ADF pipeline, add a query field in Lookup activity as
SELECT col1 FROM table1 - Add another Lookup activity, select the notebook as the source dataset. In the query field, enter the following query:
SELECT col1 FROM table2 - To combine the two queries using CHANGETABLE, modify the query in the first Lookup activity as follows:
SELECT count(1) As CurrentChangeTrackingVersion from CHANGETABLE(CHANGES ['@{activity('Notebook1').output.runOutput}'], @{activity('LookupLastTrackingVersionActivity').output.runOutput}) a- In this query, CHANGETABLE is used to track changes made to the notebook query since the last synchronization. The CHANGETABLE function takes two arguments: the first argument is the changes made to the table, and the second argument is the last synchronization version. In this case, the changes made to the notebook query are passed as the first argument, and the last synchronization version is passed as the second argument.
Please note that the CHANGETABLE function is only available for tables that have change tracking enabled. If change tracking is not enabled for your table, you will need to enable it before you can use CHANGETABLE. Click here on how to enable change tracking.
I hope this helps! Let me know if you have any further questions. - In your ADF pipeline, add a query field in Lookup activity as
-
Shambhu Rai 1,411 Reputation points
2023-10-04T16:32:02.22+00:00 CHANGE TABLE does not work with data bricks notebook
-
Smaran Thoomu 32,535 Reputation points Microsoft External Staff Moderator
2023-10-05T12:18:00.65+00:00 @Shambhu Rai Thank you for letting me know that you are still experiencing issues to run a query on a notebook and Azure SQL table in one query on ADF Lookup. I apologize for any confusion my previous response may have caused.
You are correct, the CHANGETABLE function does not work with Databricks notebooks.
Before proceeding, can you provide more details about the data you are trying to retrieve from the notebook and the Azure SQL table? What is the format of the data? Are there any common columns between the notebook and the table?Instead, can use the Delta Lake feature in Azure Databricks to track changes made to a table. Delta Lake is an open-source storage layer that brings reliability to data lakes. Delta Lake provides ACID transactions, scalable metadata handling, and unifies streaming and batch data processing.
To use Delta Lake to track changes made to a table can follow these steps:- Convert the table to a Delta table by running the following command in the Azure Databricks notebook:
CONVERT TO DELTA table_name - Enable Delta Lake time travel by running the following command:
ALTER TABLE table_name SET TBLPROPERTIES ('delta.enableChangeDataCapture' = 'true') - You can use the
deltafunction to read the changes made to the table since a specific version.
from delta.tables import * table = DeltaTable.forPath(spark, "path/to/table") changes = table.history().filter("version > 0")This code retrieves the changes made to the table since version 0.
- To retrieve the output of the Notebook activity in the Lookup activity, can use the
activity('Notebook1').output.runOutputexpression. This expression retrieves the output of the Notebook activity and passes it as a parameter to the Lookup activity. - To retrieve the count of changes made to the table, can use the
countfunction in the SQL query.
I hope this helps! Let me know if you have any further questions.
- Convert the table to a Delta table by running the following command in the Azure Databricks notebook:
-
Smaran Thoomu 32,535 Reputation points Microsoft External Staff Moderator
2023-10-06T13:19:44.3033333+00:00 @Shambhu Rai Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.
-
Shambhu Rai 1,411 Reputation points
2023-10-06T14:43:53.3466667+00:00 Sir, we want to track the changes on source and target both
-
Smaran Thoomu 32,535 Reputation points Microsoft External Staff Moderator
2023-10-09T12:07:51.19+00:00 Hi @Shambhu Rai If you need to track changes on both the source and target, You can create a change tracking table in Azure SQL Database for both the source and target tables. Then, you can use two Databricks notebooks to query the change tracking tables to get the latest changes. Finally, you can use a Change Data Capture (CDC) for Databricks to merge the changes from the two notebooks into a single table.
By using CDC and Delta Lake in Azure Databricks, you can track changes to a table in SQL Server and store the data in a reliable, performant, and scalable way.Please refer to the following documentation:
Sign in to comment
Sign in to answer