search

microsoft / jupyter-Kqlmagic

Extension (Magic) to Jupyter notebook and Jupyter lab, that enable notebook experience working with Kusto, ApplicationInsights, and LogAnalytics data.
Other
85 stars 31 forks source link

timespan to timedelta dataframe conversion incorrect #99

Open JPvRiel opened 1 year ago

JPvRiel commented 1 year ago

Code Sample, a copy-pastable example if possible

q = '''
let now = now();
let T = datatable(start:datetime, end:datetime) [
    datetime(2023-02-10T00:00:00.000000000+00:00), datetime(2023-02-11T00:00:00.327242700+00:00),
    datetime(2023-02-10T00:00:00.000000000+00:00), datetime(2023-02-11T00:00:00.000000000+00:00),
];
T
| extend delta = end - start
'''

%kql test_df << -auto_dataframe=True -query q
test_df

Problem description

When the timestamp has sub-second precision as per the above code example's first table row, it causes the conversion to a pandas data-frame to have an incorrect timedelta. E.g. it resulted in 38 days 21:00:27 instead of the correct detla of 1 days 0:00:00.32724700. When run in Log Analytics, the timespan value returned was 1.00:00:00.3272427. So it seems like there is some timespan -> timedelta conversion issue when sub-second precision occurs.

Interestingly, this problem does not manifest if the timespan did not inlcude sub-second precision values as per the second row in the example table.

If query related, does it happen on other platforms (Kusto Web UI, Kusto Explorer, Log Analytics in Azure Monitor)?

No, Log Analytics returns expected timespan values.

Output of pip freeze and %kql --bugreport

[bugreport must be executed immediatly after bug noticed] 

{'kqlmagic': {'version': '0.1.114.post22'},
 'platform': {'system': 'Linux', 'release': '5.15.0-58-generic'},
 'packages': {'python-dateutil': '2.8.2',
  'traitlets': '5.4.0',
  'prettytable': '3.4.1',
  'requests': '2.27.1',
  'msal': '1.19.0',
  'azure-identity': '1.11.0',
  'pandas': '1.5.2',
  'ipython': '8.5.0',
  'ipykernel': '6.15.3',
  'pygments': '2.13.0',
  'pyperclip': '1.8.2',
  'azure-common': '1.1.28',
  'msrestazure': '0.6.4',
  'psutil': '5.9.2',
  'matplotlib': '3.5.3',
  'setuptools': '65.3.0',
  'plotly': '5.10.0',
  'flask': '2.2.2',
  'isodate': '0.6.1',
  'markdown': '3.4.1',
  'beautifulsoup4': '4.11.1',
  'lxml': '4.9.1',
  'ipywidgets': '7.7.2',
  'cryptography': '36.0.1',
  'password_strength': '0.0.3.post2'},
 'python': {'name': 'CPython', 'version': '3.10.9', 'branch': ''},
 'kqlmagic_default_options': {'add_kql_ref_to_help': True,
  'add_schema_to_help': True,
  'allow_py_comments_before_cell': True,
  'allow_single_line_cell': True,
  'assign_var': None,
  'auth_token_warnings': False,
  'auth_use_http_client': False,
  'auto_dataframe': False,
  'auto_limit': 1000000,
  'auto_popup_schema': True,
  'cache': None,
  'cache_folder_name': 'cache_files',
  'check_magic_version': True,
  'cloud': 'public',
  'code_auth_interactive_mode': 'device_code',
  'columns_to_local_vars': False,
  'cursor_var': None,
  'debug': False,
  'device_code_login_notification': 'button',
  'device_code_notification_email': '',
  'display_limit': 100,
  'dsn_filename': 'odbc.ini',
  'dynamic_to_dataframe': 'object',
  'enable_add_items_to_help': True,
  'enable_curly_brackets_params': False,
  'enable_sso': False,
  'enable_suppress_result': True,
  'export_folder_name': 'exported_files',
  'extras_require': 'default',
  'feedback': True,
  'is_kernel_intializtion': False,
  'is_magic': True,
  'json_display': 'raw',
  'kernel_id': '***',
  'kernel_location': 'local',
  'kqlmagic_kernel': False,
  'last_raw_result_var': '_kql_raw_result_',
  'notebook_app': 'visualstudiocode',
  'notebook_service_address': None,
  'palette_colors': 10,
  'palette_desaturation': 1.0,
  'palette_name': 'tab10',
  'plot_package': 'plotly',
  'plotly_config': None,
  'plotly_fs_includejs': False,
  'plotly_layout': None,
  'popup_interaction': 'auto',
  'prettytable_style': 'DEFAULT',
  'query_link_destination': 'Kusto.WebExplorer',
  'request_app_tag': None,
  'request_cache_max_age': 0,
  'request_id_tag': None,
  'request_user_agent_tag': None,
  'request_user_tag': None,
  'schema_json_display': 'raw',
  'short_errors': True,
  'show_conn_info': 'current',
  'show_init_banner': False,
  'show_query': False,
  'show_query_link': False,
  'show_query_time': True,
  'show_what_new': True,
  'sso_db_gc_interval': 168,
  'table_package': 'prettytable',
  'temp_files_server': 'auto',
  'temp_files_server_address': 'http://127.0.0.1:47111',
  'temp_folder_location': 'user_dir',
  'temp_folder_name': 'temp_files',
  'test_notebook_app': 'none',
  'timeout': 120,
  'try_azcli_login': False,
  'try_azcli_login_by_profile': False,
  'try_azcli_login_subscription': None,
  'try_kqlmaic_sso': False,
  'try_msi': None,
  'try_token': None,
  'try_vscode_login': False,
  'use_cache': None,
  'validate_connection_string': True,
  'warn_missing_dependencies': True,
  'warn_missing_env_variables': True},
'kqlmagic_default_env': {'KQLMAGIC_CONFIGURATION': '\'show_init_banner=False;device_code_login_notification="button";timeout=120;auto_limit=1000000;display_limit=100;json_display="raw";schema_json_display="raw"\''}
}

[paste the output of pip freeze here below this line]

absl-py==1.2.0
adal==1.2.7
ansible-compat==2.2.1
ansible-core==2.12.9
ansible-lint==6.8.2
anyio==3.6.1
argon2-cffi==21.3.0
argon2-cffi-bindings==21.2.0
asn1crypto==1.5.1
astroid==2.12.10
asttokens==2.0.8
astunparse==1.6.3
attrs==22.1.0
azure-common==1.1.28
azure-core==1.25.1
azure-identity==1.11.0
Babel==2.10.3
backcall==0.2.0
beautifulsoup4==4.11.1
black==22.10.0
bleach==5.0.1
blessed==1.19.1
bokeh==2.4.3
bpython==0.23
bracex==2.3.post1
cachetools==5.2.0
certifi==2022.9.14
certvalidator==0.11.1
cffi==1.15.1
chardet==5.0.0
charset-normalizer==2.0.12
click==8.1.3
cloudpickle==2.2.0
colorama==0.4.5
colorlover==0.3.0
commonmark==0.9.1
contourpy==1.0.5
cramjam==2.6.2
cryptography==36.0.1
cufflinks==0.17.3
curtsies==0.4.0
cwcwidth==0.1.7
cycler==0.11.0
dask==2022.9.1
debugpy==1.6.3
decorator==5.1.1
defusedxml==0.7.1
dill==0.3.5.1
distributed==2022.9.1
entrypoints==0.4
executing==1.0.0
fastjsonschema==2.16.2
fastparquet==2023.1.0
filelock==3.8.0
Flask==2.2.2
flatbuffers==2.0.7
fonttools==4.37.3
fsspec==2022.8.2
gast==0.4.0
google-auth==2.11.1
google-auth-oauthlib==0.4.6
google-pasta==0.2.0
greenlet==1.1.3
grpcio==1.49.1
h5py==3.7.0
HeapDict==1.0.1
htmlmin==0.1.12
humanize==4.4.0
humanreadable==0.1.0
idna==3.4
ImageHash==4.3.0
ipykernel==6.15.3
ipython==8.5.0
ipython-genutils==0.2.0
ipywidgets==7.7.2
isodate==0.6.1
isort==5.10.1
itsdangerous==2.1.2
jedi==0.18.1
Jinja2==3.1.2
jmespath==1.0.1
joblib==1.1.0
json5==0.9.10
jsonschema==4.16.0
jupyter==1.0.0
jupyter-client==7.3.4
jupyter-console==6.4.4
jupyter-core==4.11.1
jupyter-server==1.19.1
jupyterlab==3.4.8
jupyterlab-pygments==0.2.2
jupyterlab-widgets==1.1.1
jupyterlab_server==2.15.2
keras==2.10.0
Keras-Preprocessing==1.1.2
kiwisolver==1.4.4
Kqlmagic==0.1.114.post22
lazy-object-proxy==1.7.1
libclang==14.0.6
locket==1.0.0
lxml==4.9.1
Markdown==3.4.1
MarkupSafe==2.1.1
matplotlib==3.5.3
matplotlib-inline==0.1.6
mbstrdecoder==1.1.1
mccabe==0.7.0
missingno==0.5.1
mistune==2.0.4
modin==0.15.3
msal==1.19.0
msal-extensions==1.0.0
msgpack==1.0.4
msrest==0.7.1
msrestazure==0.6.4
multimethod==1.8
mypy-extensions==0.4.3
nbclassic==0.4.4
nbclient==0.6.8
nbconvert==7.0.0
nbformat==5.6.0
nest-asyncio==1.5.5
networkx==2.8.6
notebook==6.4.12
notebook-shim==0.1.0
numpy==1.23.3
oauthlib==3.2.1
opt-einsum==3.3.0
oscrypto==1.3.0
packaging==21.3
pandas==1.5.2
pandas-profiling==3.3.0
pandocfilters==1.5.0
parso==0.8.3
partd==1.3.0
passlib==1.7.4
password-strength==0.0.3.post2
pathspec==0.10.1
patsy==0.5.2
pexpect==4.8.0
phik==0.12.2
pickleshare==0.7.5
Pillow==9.2.0
platformdirs==2.5.2
plotly==5.10.0
portalocker==2.5.1
prettytable==3.4.1
prometheus-client==0.14.1
prompt-toolkit==3.0.31
protobuf==3.19.5
psutil==5.9.2
ptyprocess==0.7.0
pure-eval==0.2.2
pyarrow==10.0.1
pyasn1==0.4.8
pyasn1-modules==0.2.8
pycparser==2.21
pydantic==1.9.2
Pygments==2.13.0
PyJWT==2.5.0
pylint==2.15.3
PyNaCl==1.5.0
pyparsing==3.0.9
pyperclip==1.8.2
pyrsistent==0.18.1
python-dateutil==2.8.2
pytz==2022.2.1
PyWavelets==1.4.1
pyxdg==0.28
PyYAML==6.0
pyzmq==24.0.1
qtconsole==5.3.2
QtPy==2.2.0
requests==2.27.1
requests-oauthlib==1.3.1
resolvelib==0.5.4
rich==12.6.0
rsa==4.9
ruamel.yaml==0.17.20
ruamel.yaml.clib==0.2.6
scikit-learn==1.1.2
scipy==1.9.1
screeninfo==0.8.1
seaborn==0.11.2
Send2Trash==1.8.0
shtab==1.5.5
six==1.16.0
sklearn==0.0
sniffio==1.3.0
sortedcontainers==2.4.0
soupsieve==2.3.2.post1
stack-data==0.5.0
statsmodels==0.13.2
subprocess-tee==0.3.5
tangled-up-in-unicode==0.2.0
tblib==1.7.0
tenacity==8.1.0
tensorboard==2.10.0
tensorboard-data-server==0.6.1
tensorboard-plugin-wit==1.8.1
tensorflow==2.10.0
tensorflow-estimator==2.10.0
tensorflow-io-gcs-filesystem==0.27.0
termcolor==2.0.1
terminado==0.15.0
threadpoolctl==3.1.0
tinycss2==1.1.1
tldr==3.1.0
tomli==2.0.1
tomlkit==0.11.4
toolz==0.12.0
tornado==6.1
tqdm==4.64.1
traitlets==5.4.0
typepy==1.3.0
types-cryptography==3.3.23
typing_extensions==4.3.0
urllib3==1.26.12
vcert==0.15.0
visions==0.7.5
wcmatch==8.4.1
wcwidth==0.2.5
webencodings==0.5.1
websocket-client==1.4.1
Werkzeug==2.2.2
widgetsnbextension==3.6.1
wrapt==1.14.1
yamllint==1.28.0
zict==2.2.0
JPvRiel commented 1 year ago

Just bumping this with a screenshot as a demo of the issue. As a workaround, converting to a csv and then into a dataframe avoids this problem.

image