Forum Discussion

sudhav's avatar
sudhav
Helper V
3 years ago
Solved

Need a Datavalidation part by using notebook activity

Hi Team,

there is one incremental load scenario and which is working fine.

 

input source:ADLS, every day one file comming to adls and appending that to table in lakehouse.

 

so 1st day i have one file and having 3 records

2nd day again one file comming to adls and having again 3 records so total 6 records in adls at the End of 2nd day

 

so same number of records i need in LH target table at the End of 2nd day.

 

so i need to count number of records in source and sink, i am able to do this by using lookup activity, but i need this data validation in notebook activity and i am able to write the pyspark query also.

 

code for SourceCount in notebook
spark.conf.set("fs.azure.account.auth.type.sudhadls.dfs.core.windows.net","SAS TOKEN")
access_path = "wasbs://[email protected]/"
df1 = spark.read.csv(access_path + "*.csv", header="True")
 
df1.count()
 
code for SinkCount in notebook
df2 = spark.sql("SELECT * FROM LH_Demo.Test")
df2.count()
 
Now i need to check df.appendcount from source side and df3.count() from sink side equal or not
 
if df1.count == df2.count():
    print("Record count validation passed.")
else:
    print("Record count validation failed")
 
this code i am using in notebook.
Now my question is if i dump this notebook in notebook activity, its publishing. but my scenario is if i got Record count validation failed then i need to trigger a mail by using outlook activity. so in outlook activity i need to give notebook activity output.
 
i have done same scenario using lookup activities but i need notebook activity. please help me team.
TIA

 

 

 

Need Help
  • HimanshuS-msft's avatar
    HimanshuS-msft
    3 years ago

    Hello sudhav 
    Thanks for using the Fabric community.
    As I undesrtand the ask right now is how to run an validation logic from a notebook and send an email.

    Please correct me if my understanding is not right .

     

    Step 1 

    We can achieve this my adding  the mssparkutils.notebook.exit() in the notebook.

     

    df1 = spark.createDataFrame(
        [
            (1, "foo"),  # create your data here, be consistent in the types.
            (2, "bar"),
        ],
        ["id", "label"]  # add your column names here
    )


    df2 = spark.createDataFrame(
        [
            (1, "foo"),  # create your data here, be consistent in the types.
            (2, "bar"),
            (3, "bar1"),
        ],
        ["id", "label"]  # add your column names here
    )


    df1.count()
    df2.count()

    if df1.count == df2.count():
        print("Record count validation passed.")
        mssparkutils.notebook.exit(100)
    else:
        print("Record count validation failed")
        mssparkutils.notebook.exit(200)

     

    Step 2 

    When you run the the notebook from a notebook activity , please add a set variabe activity and read the exitcode 

    Expression should be somelike this

    @activity('Notebook1').output.result['exitValue']

     

    Step 3 

    Add a If activity and add the below expression 

    @equals(variables('SendAnEmail'),'200') 
    Inside the True condition add the Office 365 Outlook activity .
    I have tested this logic and it works fine 
     

    Hope this helps .

    Thanks
    HImanshu

2 Replies

  • HimanshuS-msft's avatar
    HimanshuS-msft
    Microsoft Employee
    3 years ago

    Hello sudhav 
    Thanks for using the Fabric community.
    As I undesrtand the ask right now is how to run an validation logic from a notebook and send an email.

    Please correct me if my understanding is not right .

     

    Step 1 

    We can achieve this my adding  the mssparkutils.notebook.exit() in the notebook.

     

    df1 = spark.createDataFrame(
        [
            (1, "foo"),  # create your data here, be consistent in the types.
            (2, "bar"),
        ],
        ["id", "label"]  # add your column names here
    )


    df2 = spark.createDataFrame(
        [
            (1, "foo"),  # create your data here, be consistent in the types.
            (2, "bar"),
            (3, "bar1"),
        ],
        ["id", "label"]  # add your column names here
    )


    df1.count()
    df2.count()

    if df1.count == df2.count():
        print("Record count validation passed.")
        mssparkutils.notebook.exit(100)
    else:
        print("Record count validation failed")
        mssparkutils.notebook.exit(200)

     

    Step 2 

    When you run the the notebook from a notebook activity , please add a set variabe activity and read the exitcode 

    Expression should be somelike this

    @activity('Notebook1').output.result['exitValue']

     

    Step 3 

    Add a If activity and add the below expression 

    @equals(variables('SendAnEmail'),'200') 
    Inside the True condition add the Office 365 Outlook activity .
    I have tested this logic and it works fine 
     

    Hope this helps .

    Thanks
    HImanshu

    • sudhav's avatar
      sudhav
      Helper V
      3 years ago

      Great, thank you Himanshu. I thought no one will give answer to this lengthy question, but you proved that i am wrong and its working as expected. thankyou man.