Azure Data Explorer support for inline Python is GA

Published 04-06-2020 12:48 AM 2,725 Views
Microsoft

Azure Data Explorer (ADX) supports running Python code embedded in Kusto Query Language (KQL) using the python() plugin . The plugin runtime is hosted in a sandbox, an isolated and secured environment hosted on ADX existing compute nodes. This sandbox contains the language engine as well as common mathematical and scientific packages. The plugin extends KQL native functionalities with a huge archive of OSS packages, enabling ADX users to run advanced algorithms, such as machine learning, artificial intelligence, statistical tests, time series analysis and many more as part of the KQL query.

We launched this plugin about a year ago, as a private preview with early adopters, followed by a public preview with large community of internal and external users. During this period we worked directly with selected customers on common use cases, gained a lot of feedback and improved the plugin robustness, functional capabilities and scale. Today we are pleased to finish this preview and move to GA, making the python() plugin available to all ADX users.

 

Capabilities

  • The Python image is
    • based on Anaconda distribution, thus many mathematical and scientific packages are pre-installed by default
    • can be customized with additional private/public packages
  • The plugin can be run in distributed mode, on multiple nodes, handling significant workloads with large amounts of data
  • The inline Python code can be authored and debugged in VS code as explained here
  • The python plugin can be enabled/disabled via Azure portal

 

adieldar_0-1586155268966.png

 

Figure 1: enabling the python plugin from Azure Portal

 

Examples

The plugin is invoked using the “tbl | evaluate python(…)” operator. The input table is sent to the Python sandbox and is mapped to a pandas DataFrame named ‘df’, while the ‘result’ DataFrame should be set in the Python script and is sent back to ADX.

 

  • Regression analysis:

In this example we leverage numpy polyfit() to find the optimal cubic curve that fits the (x,y) points:

 

range x from -50 to 50 step 1
| extend y = 0.5*pow(x, 3) + rand(100000) - 50000, ry=0
| summarize x=make_list(x), y=make_list(y), ry=make_list(ry)
| evaluate python(typeof(*),
        'def fit(s, deg):\n'
        '    x = np.arange(len(s))\n'
        '    coeff = np.polyfit(x, s, deg)\n'
        '    p = np.poly1d(coeff)\n'
        '    z = p(x)\n'
        '    return z\n'
        '\n'
        'result = df\n'
        'result["ry"] = df["y"].apply(fit, args=(3,))\n')
| render scatterchart with(title='Polynomial Regression')

 

adieldar_1-1586155268992.png

 

  • Scoring using a trained ML model:

We trained a logistic regression model externally and serialized the model as a string into a table in ADX. Here we use ADX as a compute target, for fast

scoring of new samples, calculating the confusion matrix:

 

let model_str = toscalar(ML_Models| where name == 'Occupancy' | top 1 by timestamp desc | project model);
OccupancyDetection 
| where Test == 1
| extend pred_Occupancy=bool(0)
| evaluate python(typeof(*),
    'import pickle\n'
    'import binascii\n'
    'smodel = kargs["smodel"]\n'
    'bmodel = binascii.unhexlify(smodel)\n'
    'clf1 = pickle.loads(bmodel)\n'
    'df1 = df[["Temperature", "Humidity", "Light", "CO2", "HumidityRatio"]]\n'
    'predictions = clf1.predict(df1)\n'
    'result = df\n'
    'result["pred_Occupancy"] = pd.DataFrame(predictions, columns=["pred_Occupancy"])',
    pack('smodel', model_str))
| summarize n=count() by Occupancy, pred_Occupancy  //  confusion matrix

 

Occupancy

pred_Occupancy

n

1

1

3006

0

1

112

1

0

15

0

0

9284

 

For further information see the documentation

%3CLINGO-SUB%20id%3D%22lingo-sub-1283836%22%20slang%3D%22en-US%22%3EAzure%20Data%20Explorer%20support%20for%20inline%20Python%20is%20GA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1283836%22%20slang%3D%22en-US%22%3E%3CP%3EAzure%20Data%20Explorer%20(ADX)%20supports%20running%20Python%20code%20embedded%20in%20Kusto%20Query%20Language%20(KQL)%20using%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fpythonplugin%3Fpivots%3Dazuredataexplorer%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Epython()%20plugin%3C%2FA%3E%26nbsp%3B.%20The%20plugin%20runtime%20is%20hosted%20in%20a%20sandbox%2C%20an%20isolated%20and%20secured%20environment%20hosted%20on%20ADX%20existing%20compute%20nodes.%20This%20sandbox%20contains%20the%20language%20engine%20as%20well%20as%20common%20mathematical%20and%20scientific%20packages.%20The%20plugin%20extends%20KQL%20native%20functionalities%20with%20a%20huge%20archive%20of%20OSS%20packages%2C%20enabling%20ADX%20users%20to%20run%20advanced%20algorithms%2C%20such%20as%20machine%20learning%2C%20artificial%20intelligence%2C%20statistical%20tests%2C%20time%20series%20analysis%20and%20many%20more%20as%20part%20of%20the%20KQL%20query.%3C%2FP%3E%0A%3CP%3EWe%20launched%20this%20plugin%20about%20a%20year%20ago%2C%20as%20a%20private%20preview%20with%20early%20adopters%2C%20followed%20by%20a%20public%20preview%20with%20large%20community%20of%20internal%20and%20external%20users.%20During%20this%20period%20we%20worked%20directly%20with%20selected%20customers%20on%20common%20use%20cases%2C%20gained%20a%20lot%20of%20feedback%20and%20improved%20the%20plugin%20robustness%2C%20functional%20capabilities%20and%20scale.%20Today%20we%20are%20pleased%20to%20finish%20this%20preview%20and%20move%20to%20GA%2C%20making%20the%20python()%20plugin%20available%20to%20all%20ADX%20users.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%225%22%3E%3CSTRONG%3ECapabilities%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EThe%20Python%20image%20is%3CUL%3E%0A%3CLI%3Ebased%20on%20Anaconda%20distribution%2C%20thus%20many%20mathematical%20and%20scientific%20packages%20are%20pre-installed%20by%20default%3C%2FLI%3E%0A%3CLI%3Ecan%20be%20customized%20with%20additional%20private%2Fpublic%20packages%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FLI%3E%0A%3CLI%3EThe%20plugin%20can%20be%20run%20in%20distributed%20mode%2C%20on%20multiple%20nodes%2C%20handling%20significant%20workloads%20with%20large%20amounts%20of%20data%3C%2FLI%3E%0A%3CLI%3EThe%20inline%20Python%20code%20can%20be%20authored%20and%20debugged%20in%20VS%20code%20as%20explained%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fdebug-inline-python%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehere%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3EThe%20python%20plugin%20can%20be%20enabled%2Fdisabled%20via%20Azure%20portal%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22adieldar_0-1586155268966.png%22%20style%3D%22width%3A%201045px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F182297iE9C5C36FEF138687%2Fimage-dimensions%2F1045x431%3Fv%3D1.0%22%20width%3D%221045%22%20height%3D%22431%22%20title%3D%22adieldar_0-1586155268966.png%22%20alt%3D%22adieldar_0-1586155268966.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3EFigure%201%3A%20enabling%20the%20python%20plugin%20from%20Azure%20Portal%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EExamples%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EThe%20plugin%20is%20invoked%20using%20the%20%E2%80%9Ctbl%20%7C%20evaluate%20python(%E2%80%A6)%E2%80%9D%20operator.%20The%20input%20table%20is%20sent%20to%20the%20Python%20sandbox%20and%20is%20mapped%20to%20a%20pandas%20DataFrame%20named%20%E2%80%98df%E2%80%99%2C%20while%20the%20%E2%80%98result%E2%80%99%20DataFrame%20should%20be%20set%20in%20the%20Python%20script%20and%20is%20sent%20back%20to%20ADX.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3ERegression%20analysis%3A%20%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EIn%20this%20example%20we%20leverage%20numpy%20polyfit()%20to%20find%20the%20optimal%20cubic%20curve%20that%20fits%20the%20(x%2Cy)%20points%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Erange%20x%20from%20-50%20to%2050%20step%201%0A%7C%20extend%20y%20%3D%200.5*pow(x%2C%203)%20%2B%20rand(100000)%20-%2050000%2C%20ry%3D0%0A%7C%20summarize%20x%3Dmake_list(x)%2C%20y%3Dmake_list(y)%2C%20ry%3Dmake_list(ry)%0A%7C%20evaluate%20python(typeof(*)%2C%0A%20%20%20%20%20%20%20%20'def%20fit(s%2C%20deg)%3A%5Cn'%0A%20%20%20%20%20%20%20%20'%20%20%20%20x%20%3D%20np.arange(len(s))%5Cn'%0A%20%20%20%20%20%20%20%20'%20%20%20%20coeff%20%3D%20np.polyfit(x%2C%20s%2C%20deg)%5Cn'%0A%20%20%20%20%20%20%20%20'%20%20%20%20p%20%3D%20np.poly1d(coeff)%5Cn'%0A%20%20%20%20%20%20%20%20'%20%20%20%20z%20%3D%20p(x)%5Cn'%0A%20%20%20%20%20%20%20%20'%20%20%20%20return%20z%5Cn'%0A%20%20%20%20%20%20%20%20'%5Cn'%0A%20%20%20%20%20%20%20%20'result%20%3D%20df%5Cn'%0A%20%20%20%20%20%20%20%20'result%5B%22ry%22%5D%20%3D%20df%5B%22y%22%5D.apply(fit%2C%20args%3D(3%2C))%5Cn')%0A%7C%20render%20scatterchart%20with(title%3D'Polynomial%20Regression')%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22adieldar_1-1586155268992.png%22%20style%3D%22width%3A%201225px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F182296iE3F94D1A6CFE453E%2Fimage-dimensions%2F1225x435%3Fv%3D1.0%22%20width%3D%221225%22%20height%3D%22435%22%20title%3D%22adieldar_1-1586155268992.png%22%20alt%3D%22adieldar_1-1586155268992.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EScoring%20using%20a%20trained%20ML%20model%3A%20%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EWe%20trained%20a%20logistic%20regression%20model%20externally%20and%20serialized%20the%20model%20as%20a%20string%20into%20a%20table%20in%20ADX.%20Here%20we%20use%20ADX%20as%20a%20compute%20target%2C%20for%20fast%3C%2FP%3E%0A%3CP%3Escoring%20of%20new%20samples%2C%20calculating%20the%20confusion%20matrix%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%20model_str%20%3D%20toscalar(ML_Models%7C%20where%20name%20%3D%3D%20'Occupancy'%20%7C%20top%201%20by%20timestamp%20desc%20%7C%20project%20model)%3B%0AOccupancyDetection%20%0A%7C%20where%20Test%20%3D%3D%201%0A%7C%20extend%20pred_Occupancy%3Dbool(0)%0A%7C%20evaluate%20python(typeof(*)%2C%0A%20%20%20%20'import%20pickle%5Cn'%0A%20%20%20%20'import%20binascii%5Cn'%0A%20%20%20%20'smodel%20%3D%20kargs%5B%22smodel%22%5D%5Cn'%0A%20%20%20%20'bmodel%20%3D%20binascii.unhexlify(smodel)%5Cn'%0A%20%20%20%20'clf1%20%3D%20pickle.loads(bmodel)%5Cn'%0A%20%20%20%20'df1%20%3D%20df%5B%5B%22Temperature%22%2C%20%22Humidity%22%2C%20%22Light%22%2C%20%22CO2%22%2C%20%22HumidityRatio%22%5D%5D%5Cn'%0A%20%20%20%20'predictions%20%3D%20clf1.predict(df1)%5Cn'%0A%20%20%20%20'result%20%3D%20df%5Cn'%0A%20%20%20%20'result%5B%22pred_Occupancy%22%5D%20%3D%20pd.DataFrame(predictions%2C%20columns%3D%5B%22pred_Occupancy%22%5D)'%2C%0A%20%20%20%20pack('smodel'%2C%20model_str))%0A%7C%20summarize%20n%3Dcount()%20by%20Occupancy%2C%20pred_Occupancy%20%20%2F%2F%20%20confusion%20matrix%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%20width%3D%22262px%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2288.6667px%22%3E%0A%3CP%3E%3CSTRONG%3EOccupancy%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22128px%22%3E%0A%3CP%3E%3CSTRONG%3Epred_Occupancy%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2245.3333px%22%3E%0A%3CP%3E%3CSTRONG%3En%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2288.6667px%22%3E%0A%3CP%3E1%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22128px%22%3E%0A%3CP%3E1%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2245.3333px%22%3E%0A%3CP%3E3006%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2288.6667px%22%3E%0A%3CP%3E0%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22128px%22%3E%0A%3CP%3E1%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2245.3333px%22%3E%0A%3CP%3E112%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2288.6667px%22%3E%0A%3CP%3E1%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22128px%22%3E%0A%3CP%3E0%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2245.3333px%22%3E%0A%3CP%3E15%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2288.6667px%22%3E%0A%3CP%3E0%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%22128px%22%3E%0A%3CP%3E0%3C%2FP%3E%0A%3C%2FTD%3E%0A%3CTD%20width%3D%2245.3333px%22%3E%0A%3CP%3E9284%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%3CSTRONG%3E%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EFor%20further%20information%20see%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fpythonplugin%3Fpivots%3Dazuredataexplorer%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ethe%20documentation%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1283836%22%20slang%3D%22en-US%22%3E%3CP%3EEmbedding%20Python%20code%20in%20KQL%20query%20is%20Generally%20Available!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYour%20data%20is%20stored%20in%20ADX%20and%20you%20need%20to%20analyze%20it%20with%20advanced%20algorithms%3F%20scoring%20ML%20models%3F%20No%20need%20anymore%20for%20the%20complex%20process%20of%20exporting%20the%20data%2C%20processing%20it%20and%20ingesting%20the%20results%20back%20to%20ADX!%20now%20you%20can%20do%20everything%20inside%20ADX%2C%20near%20your%20data%2C%20by%20embedding%20Python%20code%20with%20your%20KQL%20query%2C%20leveraging%20the%20powerful%20Python%20ecosystem.%20Customers%20are%20already%20using%20this%20capability%20for%20scoring%20models%2C%20extending%20time%20series%20analysis%2C%20performing%20statistical%20tests%20and%20many%20other%20scenarios%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1283836%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eadx%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20Data%20Explorer%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Ekql%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EKusto%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Epython%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Apr 06 2020 12:48 AM
Updated by: