Parameterized notebook errors on a line break after %%kql which is supposed to work

[MsSQLGirl]

• Azure Data Studio Version: Version: 1.24.0-rc1 (user setup) Commit: 582a3c05fdff72ff5e1af5bc68adbadd5eaf35a5 Date: 2020-11-07T03:04:12.672Z VS Code: 1.48.0 Electron: 9.3.0 Chrome: 83.0.4103.122 Node.js: 12.14.1 V8: 8.3.110.13-electron.0 OS: Windows_NT x64 10.0.19042

Steps to Reproduce:

1. Create a new Kqlmagic
2. Write a query with a line right after %%kql.

In parameterized execution, the following does not work

%%kql
let _server = logicalServerName;
AzureDiagnostics
| where LogicalServerName_s == _server and action_name_s == "BATCH COMPLETED"
| project Event = action_name_s, SQLStatement = statement_s, TimeStamp = originalEventTimestamp_t
| take 10

The following does though:

%%kql let _server = logicalServerName;
AzureDiagnostics
| where LogicalServerName_s == _server and action_name_s == "BATCH COMPLETED"
| project Event = action_name_s, SQLStatement = statement_s, TimeStamp = originalEventTimestamp_t
| take 10

Expected: Both methods should work.

[VasuBhog]

Hey @MsSQLGirl, what is the error message when you try the first one?

[VasuBhog]

@MsSQLGirl - I was able to reproduce this in Jupyter Notebooks (this is not a papermill specific issue). You are unable to put a new line after %kql magic command it seems otherwise it gives a syntax error as shown below. This would be a feature request in my opinion since we would specifically have to look for the %kql magic command and ensure the new line is taken into account.

Jupyter notebook:

Azure Data Studio:

[MsSQLGirl]

In Kqlmagic, there are two ways of writing KQL queries:

1. %kql for single line
2. %%kql for multiline

My issue above is specifically for %%kql where I was expecting multiline to work. It usually works with normal notebook. But it doesn't work with papermill.

[VasuBhog]

@MsSQLGirl I have tested this multiple times with both ADS and Jupyter Notebook and it seems to work for me. The only error that I would ever get is if there is only one line with the multiline KqlMagic format as shown below:

KqlMagic Usage Error:

I also tested this Command line and Python API papermill, both methods work for me in my KqlMagic test. I was able to pass in the parameter properly either way and papermill executed it properly as shown below. It's possible that there must have been another issue within that query itself that papermill needs to verify. Please let me know if there is a repro or error message that should be outputted in the parameterized notebook.

[MsSQLGirl]

@VasuBhog - The bug is only reproable when there is more than one code cell with %%kql (for querying).

[VasuBhog]

@MsSQLGirl I am waiting to hear back from papermill contributors as this seems to be something papermill should inform the user about. I will continue to investigate why the notebook you use gets this issue but the notebook I created following your repro steps does not (including having another code cell after).

I am tracking the papermill open issue here (seems to happen with other magics as well for papermill): https://github.com/nteract/papermill/issues/557

[mbnshtck]

I failed to replicate.

I suggest:

After the cell that you run %reload_ext Kqlmagic run in a cell: %kql --config "short_errors=False" this option set to False will cause the full code stack to be displayed when an error happens. It will be much easier to troubleshoot it.

[MsSQLGirl]

I added the cell as @mbnshtck suggested and it shows this

The workaround when using %%kql for parameterization, is to add a space after kql, i.e. %%kql then a new line break.