def test_null_values_in_data(self, mock_post):
"""Tests response with null values in non nullable column types"""
client = KustoClient("https://somecluster.kusto.windows.net")
query = "PrimaryResultName"
response = client.execute_query("PythonTest", query)
assert response is not None
def __init__(
self,
kusto_cluster,
client_id=None,
client_secret=None,
username=None,
password=None,
certificate=None,
certificate_thumbprint=None,
authority=None,
):
"""
Kusto Client constructor.
Parameters
----------
kusto_cluster : str
Kusto cluster endpoint. Example: https://help.kusto.windows.net
client_id : str
The AAD application ID of the application making the request to Kusto
client_secret : str
The AAD application key of the application making the request to Kusto.
if this is given, then username/password should not be.
username : str
The username of the user making the request to Kusto.
if this is given, then password must follow and the client_secret should not be given.
password : str
The password matching the username of the user making the request to Kusto
authority : 'microsoft.com', optional
In case your tenant is not microsoft please use this param.
"""
if all([username, password]):
kcsb = KustoConnectionStringBuilder.with_aad_user_password_authentication(
kusto_cluster, username, password)
elif all([client_id, client_secret]):
kcsb = KustoConnectionStringBuilder.with_aad_application_key_authentication(
kusto_cluster, client_id, client_secret)
elif all([client_id, certificate, certificate_thumbprint]):
kcsb = KustoConnectionStringBuilder.with_aad_application_certificate_authentication(
kusto_cluster, client_id, certificate, certificate_thumbprint)
else:
kcsb = KustoConnectionStringBuilder.with_aad_device_authentication(
kusto_cluster)
if authority:
kcsb.authority_id = authority
self.client = KustoClient(kcsb)
# replace aadhelper to use remote browser in interactive mode
self.client._aad_helper = _MyAadHelper(kcsb)
self.mgmt_endpoint_version = "v2" if self.client._mgmt_endpoint.endswith(
"v2/rest/query") else "v1"
self.query_endpoint_version = "v2" if self.client._query_endpoint.endswith(
"v2/rest/query") else "v1"
def Ingest(Tag):
# setting
AUTHORITY_ID = "6babcaad-604b-40ac-a9d7-9fd97c0b779f"
INGESTCLUSTER = "https://ingest-cgadataout.kusto.windows.net"
KUSTOCLUSTER = "https://cgadataout.kusto.windows.net"
DATABASE = "DevRelWorkArea"
# Create table
KCSB_DATA = KustoConnectionStringBuilder.with_aad_device_authentication(
KUSTOCLUSTER)
DESTINATION_TABLE = "RepoContributors"
DESTINATION_TABLE_COLUMN_MAPPING = "RepoContributors_CSV_Mapping"
KUSTO_CLIENT = KustoClient(KCSB_DATA)
DROP_TABLE_IF_EXIST = ".drop table RepoContributors ifexists"
RESPONSE = KUSTO_CLIENT.execute_mgmt(DATABASE, DROP_TABLE_IF_EXIST)
CREATE_TABLE_COMMAND = ".create table RepoContributors (Article: string, Contributors: int64, Data: string)"
RESPONSE = KUSTO_CLIENT.execute_mgmt(DATABASE, CREATE_TABLE_COMMAND)
print("RepoContributors table is created")
# Create mapping
CREATE_MAPPING_COMMAND = """.create table RepoContributors ingestion csv mapping 'RepoContributors_CSV_Mapping' '[{"Name": "Article","datatype": "string","Ordinal": 0},{"Name": "Contributors","datatype": "int64","Ordinal": 1},{"Name": "Data","datatype": "string","Ordinal": 2}]'"""
RESPONSE = KUSTO_CLIENT.execute_mgmt(DATABASE, CREATE_MAPPING_COMMAND)
print("mapping is created")
# Ingest
# The authentication method will be taken from the chosen KustoConnectionStringBuilder.
ingestion_props = IngestionProperties(
database="DevRelWorkArea",
table="RepoContributors",
dataFormat=DataFormat.CSV,
ingestByTags=[Tag],
dropByTags=[Tag],
mappingReference=DESTINATION_TABLE_COLUMN_MAPPING,
reportLevel=ReportLevel.FailuresAndSuccesses,
additionalProperties={'ignoreFirstRecord': 'true'})
kcsb = KustoConnectionStringBuilder.with_aad_device_authentication(
INGESTCLUSTER)
client = KustoIngestClient(kcsb)
# ingest from file
file_descriptor = FileDescriptor(
r"D:\test\Results\log_data_merge\merge_microsoftdocs_sql-docs-pr.txt",
3333) # 3333 is the raw size of the data in bytes.
client.ingest_from_file(file_descriptor,
ingestion_properties=ingestion_props)
# if status updates are required, something like this can be done
return 1
def create(cls, server, querybuilders, login=False):
if server is None:
raise ValueError("Failed to create client, no server provided.")
client = KustoClient(server)
client = cls.__inject_queries(client, querybuilders)
# force the user to authenticate before returning
if login:
client.execute(cls.kustoDatabase, ".show version")
return client
def __init__(self, trainingConfig, trainingId):
self.trainingId = trainingId
cluster = "https://usage360.kusto.windows.net"
authority_id = "72f988bf-86f1-41af-91ab-2d7cd011db47"
client_id = kustoClientId
client_secret = kustoClientSecret
kcsb = KustoConnectionStringBuilder.with_aad_application_key_authentication(cluster, client_id, client_secret, authority_id)
self.kustoClient = KustoClient(kcsb)
self.garbageList = [x.strip() for x in open("metadata/garbagePhrases.txt", "r").readlines()]
self.striptrailers = [x.strip() for x in open("metadata/stripTrailers.txt", "r").readlines()]
self.shortPhrases = [x.strip() for x in open("metadata/shortPhrasesList.txt", "r").readlines()]
self.trainingConfig = trainingConfig
def test_partial_results(self, mock_post):
"""Tests partial results."""
client = KustoClient("https://somecluster.kusto.windows.net")
query = """set truncationmaxrecords = 1;
range x from 1 to 2 step 1"""
self.assertRaises(KustoServiceError, client.execute_query, "PythonTest", query)
response = client.execute_query("PythonTest", query, accept_partial_results=True)
self.assertEqual(response.errors_count, 1)
self.assertIn("E_QUERY_RESULT_SET_TOO_LARGE", response.get_exceptions()[0])
self.assertEqual(len(response), 3)
results = list(response.primary_results[0])
self.assertEqual(len(results), 1)
self.assertEqual(results[0]["x"], 1)
def get_client(cluster):
"""
get cached, authenticated client for given cluster
"""
global _client_cache
c = _client_cache.get(cluster)
if c is None:
c = KustoClient(
KustoConnectionStringBuilder.with_aad_device_authentication(
cluster))
c.execute('VSO', 'print "a" | take 0')
_client_cache[cluster] = c
return c
def test_admin_then_query(self, mock_post):
"""Tests partial results."""
client = KustoClient("https://somecluster.kusto.windows.net")
query = ".show tables | project DatabaseName, TableName"
response = client.execute_mgmt("PythonTest", query)
self.assertEqual(response.errors_count, 0)
self.assertEqual(len(response), 4)
results = list(response.primary_results[0])
self.assertEqual(len(results), 2)
self.assertEqual(response[0].table_kind, WellKnownDataSet.PrimaryResult)
self.assertEqual(response[1].table_kind, WellKnownDataSet.QueryProperties)
self.assertEqual(response[2].table_kind, WellKnownDataSet.QueryCompletionInformation)
self.assertEqual(response[3].table_kind, WellKnownDataSet.TableOfContents)
def test_admin_then_query(self, mock_post):
"""Tests admin then query."""
client = KustoClient("https://somecluster.kusto.windows.net")
query = ".show tables | project DatabaseName, TableName"
response = client.execute_mgmt("PythonTest", query)
assert response.errors_count == 0
assert len(response) == 4
results = list(response.primary_results[0])
assert len(results) == 2
assert response[0].table_kind == WellKnownDataSet.PrimaryResult
assert response[1].table_kind == WellKnownDataSet.QueryProperties
assert response[
2].table_kind == WellKnownDataSet.QueryCompletionInformation
assert response[3].table_kind == WellKnownDataSet.TableOfContents
def run_query(self, query, user):
kcsb = KustoConnectionStringBuilder.with_aad_application_key_authentication(
connection_string=self.configuration["cluster"],
aad_app_id=self.configuration["azure_ad_client_id"],
app_key=self.configuration["azure_ad_client_secret"],
authority_id=self.configuration["azure_ad_tenant_id"],
)
client = KustoClient(kcsb)
db = self.configuration["database"]
try:
response = client.execute(db, query)
result_cols = response.primary_results[0].columns
result_rows = response.primary_results[0].rows
columns = []
rows = []
for c in result_cols:
columns.append(
{
"name": c.column_name,
"friendly_name": c.column_name,
"type": TYPES_MAP.get(c.column_type, None),
}
)
# rows must be [{'column1': value, 'column2': value}]
for row in result_rows:
rows.append(row.to_dict())
error = None
data = {"columns": columns, "rows": rows}
json_data = json_dumps(data)
except KustoServiceError as err:
json_data = None
try:
error = err.args[1][0]["error"]["@message"]
except (IndexError, KeyError):
error = err.args[1]
except KeyboardInterrupt:
json_data = None
error = "Query cancelled by user."
return json_data, error
def test_sanity_control_command(self, mock_post, mock_aad):
"""Tests contol command."""
client = KustoClient("https://somecluster.kusto.windows.net")
response = client.execute_mgmt("NetDefaultDB", ".show version")
self.assertEqual(len(response), 1)
primary_table = response.primary_results[0]
row_count = 0
for _ in primary_table:
row_count += 1
self.assertEqual(row_count, 1)
result = primary_table[0]
self.assertEqual(result["BuildVersion"], "1.0.6693.14577")
self.assertEqual(
result["BuildTime"], datetime(year=2018, month=4, day=29, hour=8, minute=5, second=54, tzinfo=tzutc())
)
self.assertEqual(result["ServiceType"], "Engine")
self.assertEqual(result["ProductVersion"], "KustoMain_2018.04.29.5")
def __init__(self, conn_kv):
"""
Kusto Client constructor.
Parameters
----------
kusto_cluster : str
Kusto cluster endpoint. Example: https://help.kusto.windows.net
client_id : str
The AAD application ID of the application making the request to Kusto
client_secret : str
The AAD application key of the application making the request to Kusto.
if this is given, then username/password should not be.
username : str
The username of the user making the request to Kusto.
if this is given, then password must follow and the client_secret should not be given.
password : str
The password matching the username of the user making the request to Kusto
authority : 'microsoft.com', optional
In case your tenant is not microsoft please use this param.
"""
kusto_cluster = "https://{0}.kusto.windows.net".format(conn_kv["cluster"])
if all([conn_kv.get("username"), conn_kv.get("password")]):
kcsb = KustoConnectionStringBuilder.with_aad_user_password_authentication(kusto_cluster, conn_kv.get("username"), conn_kv.get("password"))
if conn_kv.get("tenant") is not None: kcsb.authority_id = conn_kv.get("tenant")
elif all([conn_kv.get("clientid"), conn_kv.get("clientsecret")]):
kcsb = KustoConnectionStringBuilder.with_aad_application_key_authentication(
kusto_cluster, conn_kv.get("clientid"), conn_kv.get("clientsecret"), conn_kv.get("tenant"))
elif all([conn_kv.get("clientid"), conn_kv.get("certificate"), conn_kv.get("certificate_thumbprint")]):
kcsb = KustoConnectionStringBuilder.with_aad_application_certificate_authentication(
kusto_cluster, conn_kv.get("clientid"), conn_kv.get("certificate"), conn_kv.get("certificate_thumbprint", conn_kv.get("tenant"))
)
else:
kcsb = KustoConnectionStringBuilder.with_aad_device_authentication(kusto_cluster)
if conn_kv.get("tenant") is not None: kcsb.authority_id = conn_kv.get("tenant")
self.client = KustoClient(kcsb)
# replace aadhelper to use remote browser in interactive mode
self.client._aad_helper = _MyAadHelper(kcsb, self._DEFAULT_CLIENTID)
self.mgmt_endpoint_version = "v2" if self.client._mgmt_endpoint.endswith("v2/rest/query") else "v1"
self.query_endpoint_version = "v2" if self.client._query_endpoint.endswith("v2/rest/query") else "v1"
def test_partial_results(self, mock_post):
"""Tests partial results."""
client = KustoClient("https://somecluster.kusto.windows.net")
query = """set truncationmaxrecords = 5;
range x from 1 to 10 step 1"""
properties = ClientRequestProperties()
properties.set_option(
ClientRequestProperties.OptionDeferPartialQueryFailures, False)
self.assertRaises(KustoServiceError, client.execute_query,
"PythonTest", query, properties)
properties.set_option(
ClientRequestProperties.OptionDeferPartialQueryFailures, True)
response = client.execute_query("PythonTest", query, properties)
self.assertEqual(response.errors_count, 1)
self.assertIn("E_QUERY_RESULT_SET_TOO_LARGE",
response.get_exceptions()[0])
self.assertEqual(len(response), 3)
results = list(response.primary_results[0])
self.assertEqual(len(results), 5)
self.assertEqual(results[0]["x"], 1)
def get_conn(self) -> KustoClient:
"""Return a KustoClient object."""
conn = self.get_connection(self.conn_id)
cluster = conn.host
if not cluster:
raise AirflowException('Host connection option is required')
def get_required_param(name: str) -> str:
"""Extract required parameter from extra JSON, raise exception if not found"""
value = conn.extra_dejson.get(name)
if not value:
raise AirflowException(
f'Extra connection option is missing required parameter: `{name}`'
)
return value
auth_method = get_required_param('auth_method') or get_required_param(
'extra__azure_data_explorer__auth_method')
if auth_method == 'AAD_APP':
tenant = get_required_param('tenant') or get_required_param(
'extra__azure_data_explorer__tenant')
kcsb = KustoConnectionStringBuilder.with_aad_application_key_authentication(
cluster, conn.login, conn.password, tenant)
elif auth_method == 'AAD_APP_CERT':
certificate = get_required_param(
'certificate') or get_required_param(
'extra__azure_data_explorer__certificate')
thumbprint = get_required_param(
'thumbprint') or get_required_param(
'extra__azure_data_explorer__thumbprint')
tenant = get_required_param('tenant') or get_required_param(
'extra__azure_data_explorer__tenant')
kcsb = KustoConnectionStringBuilder.with_aad_application_certificate_authentication(
cluster,
conn.login,
certificate,
thumbprint,
tenant,
)
elif auth_method == 'AAD_CREDS':
tenant = get_required_param('tenant') or get_required_param(
'extra__azure_data_explorer__tenant')
kcsb = KustoConnectionStringBuilder.with_aad_user_password_authentication(
cluster, conn.login, conn.password, tenant)
elif auth_method == 'AAD_DEVICE':
kcsb = KustoConnectionStringBuilder.with_aad_device_authentication(
cluster)
else:
raise AirflowException(
f'Unknown authentication method: {auth_method}')
return KustoClient(kcsb)
def test_partial_results(self, mock_post):
"""Tests partial results."""
client = KustoClient("https://somecluster.kusto.windows.net")
query = """set truncationmaxrecords = 5;
range x from 1 to 10 step 1"""
properties = ClientRequestProperties()
properties.set_option(
ClientRequestProperties.
results_defer_partial_query_failures_option_name, False)
self.assertRaises(KustoServiceError, client.execute_query,
"PythonTest", query, properties)
properties.set_option(
ClientRequestProperties.
results_defer_partial_query_failures_option_name, True)
response = client.execute_query("PythonTest", query, properties)
assert response.errors_count == 1
assert "E_QUERY_RESULT_SET_TOO_LARGE" in response.get_exceptions()[0]
assert len(response) == 3
results = list(response.primary_results[0])
assert len(results) == 5
assert results[0]["x"] == 1
def test_dynamic(self, mock_post):
"""Tests dynamic responses."""
client = KustoClient("https://somecluster.kusto.windows.net")
query = """print dynamic(123), dynamic("123"), dynamic("test bad json"), dynamic(null), dynamic('{"rowId":2,"arr":[0,2]}'), dynamic({"rowId":2,"arr":[0,2]})"""
row = client.execute_query("PythonTest",
query).primary_results[0].rows[0]
self.assertIsInstance(row[0], int)
self.assertEqual(row[0], 123)
self.assertIsInstance(row[1], text_type)
self.assertEqual(row[1], "123")
self.assertIsInstance(row[2], text_type)
self.assertEqual(row[2], "test bad json")
self.assertEqual(row[3], None)
self.assertIsInstance(row[4], text_type)
self.assertEqual(row[4], '{"rowId":2,"arr":[0,2]}')
self.assertIsInstance(row[5], dict)
self.assertEqual(row[5], {"rowId": 2, "arr": [0, 2]})
def kusto_output():
try:
kcsb = KustoConnectionStringBuilder.with_aad_application_key_authentication(
SOCAlertConsumer.cluster, SOCAlertConsumer.client_id,
SOCAlertConsumer.client_secret, SOCAlertConsumer.authority_id)
logger.AppLogging.auditlogger.info(
"Successfully loaded Kusto Connection Strings" + " " +
SOCAlertConsumer.cluster + " with AppID " +
SOCAlertConsumer.client_id)
except RuntimeError as ie:
logger.AppLogging.auditlogger.error(
"Unable to import Kusto Connection Strings. "
"Please check your configuration" + SOCAlertConsumer.cluster +
"with AppID" + SOCAlertConsumer.client_id)
logger.AppLogging.auditlogger.error(str(ie))
client = KustoClient(kcsb)
kusto_query = open(".//KustoQuery//SOCAlerts.csl",
"r") # change to forward slash for linux (//)
query = kusto_query.read()
kusto_query.close()
try:
response = client.execute(SOCAlertConsumer.db, query)
logger.AppLogging.auditlogger.info(
"Successfully received response from Kusto")
logger.AppLogging.auditlogger.info(
"Query output is saved to output folder")
except RuntimeError as r:
logger.AppLogging.auditlogger.error(
"There was an error in receiving response from Kusto")
logger.AppLogging.auditlogger.error(str(r))
for row in response.primary_results:
filetime = time.strftime("%Y%m%d-%H%M%S")
file = open(".//output//alerts-" + filetime + ".json",
"x") # forward slash works fine on Win & nix
file.write(str(row))
file.close()
def test_dynamic(self, mock_post):
"""Tests dynamic responses."""
client = KustoClient("https://somecluster.kusto.windows.net")
query = """print dynamic(123), dynamic("123"), dynamic("test bad json"),"""
""" dynamic(null), dynamic('{"rowId":2,"arr":[0,2]}'), dynamic({"rowId":2,"arr":[0,2]})"""
row = client.execute_query("PythonTest",
query).primary_results[0].rows[0]
assert isinstance(row[0], int)
assert row[0] == 123
assert isinstance(row[1], str)
assert row[1] == "123"
assert isinstance(row[2], str)
assert row[2] == "test bad json"
assert row[3] is None
assert isinstance(row[4], str)
assert row[4] == '{"rowId":2,"arr":[0,2]}'
assert isinstance(row[5], dict)
assert row[5] == {"rowId": 2, "arr": [0, 2]}
def get_conn(self) -> KustoClient:
"""Return a KustoClient object."""
conn = self.get_connection(self.conn_id)
cluster = conn.host
if not cluster:
raise AirflowException('Host connection option is required')
def get_required_param(name):
"""Extract required parameter from extra JSON, raise exception if not found"""
value = conn.extra_dejson.get(name)
if not value:
raise AirflowException(
'Extra connection option is missing required parameter: `{}`'
.format(name))
return value
auth_method = get_required_param('auth_method')
if auth_method == 'AAD_APP':
kcsb = KustoConnectionStringBuilder.with_aad_application_key_authentication(
cluster, conn.login, conn.password,
get_required_param('tenant'))
elif auth_method == 'AAD_APP_CERT':
kcsb = KustoConnectionStringBuilder.with_aad_application_certificate_authentication(
cluster,
conn.login,
get_required_param('certificate'),
get_required_param('thumbprint'),
get_required_param('tenant'),
)
elif auth_method == 'AAD_CREDS':
kcsb = KustoConnectionStringBuilder.with_aad_user_password_authentication(
cluster, conn.login, conn.password,
get_required_param('tenant'))
elif auth_method == 'AAD_DEVICE':
kcsb = KustoConnectionStringBuilder.with_aad_device_authentication(
cluster)
else:
raise AirflowException(
'Unknown authentication method: {}'.format(auth_method))
return KustoClient(kcsb)
def authenticate_kusto(kusto_cluster):
tenant_id = '72f988bf-86f1-41af-91ab-2d7cd011db47'
KCSB = KustoConnectionStringBuilder.with_aad_device_authentication(kusto_cluster)
KCSB.authority_id = tenant_id
return KustoClient(KCSB)
"""
if (not client or not database.strip() or not file_path.strip()):
raise ValueError("All arguments of this function are mandatory")
kusto_file = open(file_path, 'r')
command = kusto_file.read().replace('\n', ' ')
response = client.execute_mgmt(database, command)
return response.primary_results[0]
# create a connection string with a device code (=interactive) authentication
kcsb = KustoConnectionStringBuilder.with_aad_device_authentication(cluster)
# create the client and do the actual authentication
client = KustoClient(kcsb)
# run the command to create the target table
response = send_kusto_command_from_file(client, database,
'./table-target-create.csl')
print("Created target table. Response = ", response)
# run the command to create the function of data parsing
response = send_kusto_command_from_file(client, database,
'./function-source-data-parsing.csl')
print("Created parsing function. Response = ", response)
# run the command to create the update policy to parse the source data into the target table
response = send_kusto_command_from_file(client, database,
'./update-policy-target.csl')
print("Created update policy. Response = ", response)
mappings.append(
JsonColumnMapping(columnName="xtextWithNulls",
jsonPath="$.xtextWithNulls",
cslDataType="string"))
mappings.append(
JsonColumnMapping(columnName="xdynamicWithNulls",
jsonPath="$.xdynamicWithNulls",
cslDataType="dynamic"))
return mappings
engine_kcsb = KustoConnectionStringBuilder.with_aad_device_authentication(
"https://toshetah.kusto.windows.net")
dm_kcsb = KustoConnectionStringBuilder.with_aad_device_authentication(
"https://ingest-toshetah.kusto.windows.net")
client = KustoClient(engine_kcsb)
ingest_client = KustoIngestClient(dm_kcsb)
ingest_status_q = KustoIngestStatusQueues(ingest_client)
client.execute("PythonTest", ".drop table Deft ifexists")
@pytest.mark.run(order=1)
def test_csv_ingest_non_existing_table():
csv_ingest_props = IngestionProperties(
"PythonTest",
"Deft",
dataFormat=DataFormat.csv,
mapping=Helpers.create_deft_table_csv_mappings(),
reportLevel=ReportLevel.FailuresAndSuccesses,
)
csv_file_path = os.path.join(os.getcwd(), "azure-kusto-ingest", "tests",
def __init__(self, kcsb):
"""Kusto Ingest Client constructor.
:param kcsb: The connection string to initialize KustoClient.
"""
self._resource_manager = _ResourceManager(KustoClient(kcsb))
# read more at https://docs.microsoft.com/en-us/onedrive/find-your-office-365-tenant-id
authority_id = "<insert here your tenant id>"
kcsb = KustoConnectionStringBuilder.with_aad_application_key_authentication(
cluster, client_id, client_secret, authority_id)
# In case you want to authenticate with AAD application certificate.
filename = "path to a PEM certificate"
with open(filename, "r") as pem_file:
PEM = pem_file.read()
thumbprint = "certificate's thumbprint"
kcsb = KustoConnectionStringBuilder.with_aad_application_certificate_authentication(
cluster, client_id, PEM, thumbprint, authority_id)
client = KustoClient(kcsb)
# In case you want to authenticate with the logged in AAD user.
client = KustoClient(cluster)
######################################################
## QUERY ##
######################################################
# once authenticated, usage is as following
db = "Samples"
query = "StormEvents | take 10"
response = client.execute(db, query)
# iterating over rows is possible
mappings.append(
JsonColumnMapping(columnName="xdynamicWithNulls",
jsonPath="$.xdynamicWithNulls",
cslDataType="dynamic"))
return mappings
cluster = "Dadubovs1.westus" # "toshetah"
db_name = "TestingDatabase" # "PythonTest"
table_name = "Deft"
engine_kcsb = KustoConnectionStringBuilder.with_aad_device_authentication(
"https://{}.kusto.windows.net".format(cluster))
dm_kcsb = KustoConnectionStringBuilder.with_aad_device_authentication(
"https://ingest-{}.kusto.windows.net".format(cluster))
client = KustoClient(engine_kcsb)
ingest_client = KustoIngestClient(dm_kcsb)
ingest_status_q = KustoIngestStatusQueues(ingest_client)
client.execute(db_name, ".drop table {} ifexists".format(table_name))
@pytest.mark.run(order=1)
def test_csv_ingest_non_existing_table():
csv_ingest_props = IngestionProperties(
db_name,
table_name,
dataFormat=DataFormat.csv,
mapping=Helpers.create_deft_table_csv_mappings(),
reportLevel=ReportLevel.FailuresAndSuccesses,
)
class Kusto_Client(object):
"""
Kusto client wrapper for Python.
KustoClient works with both 2.x and 3.x flavors of Python. All primitive types are supported.
KustoClient takes care of ADAL authentication, parsing response and giving you typed result set,
and offers familiar Python DB API.
Test are run using nose.
Examples
--------
To use KustoClient, you can choose betwen two ways of authentication.
For the first option, you'll need to have your own AAD application and know your client credentials (client_id and client_secret).
>>> kusto_cluster = 'https://help.kusto.windows.net'
>>> kusto_client = KustoClient(kusto_cluster, client_id, client_secret='your_app_secret')
For the second option, you can use KustoClient's client id and authenticate using your username and password.
>>> kusto_cluster = 'https://help.kusto.windows.net'
>>> client_id = 'e07cf1fb-c6a6-4668-b21a-f74731afa19a'
>>> kusto_client = KustoClient(kusto_cluster, client_id, username='******', password='******')"""
_DEFAULT_CLIENTID = "db662dc1-0cfe-4e1c-a843-19a68e65be58" # kusto client app, don't know app name
# _DEFAULT_CLIENTID = "8430759c-5626-4577-b151-d0755f5355d8" # kusto client app, don't know app name
def __init__(self, conn_kv):
"""
Kusto Client constructor.
Parameters
----------
kusto_cluster : str
Kusto cluster endpoint. Example: https://help.kusto.windows.net
client_id : str
The AAD application ID of the application making the request to Kusto
client_secret : str
The AAD application key of the application making the request to Kusto.
if this is given, then username/password should not be.
username : str
The username of the user making the request to Kusto.
if this is given, then password must follow and the client_secret should not be given.
password : str
The password matching the username of the user making the request to Kusto
authority : 'microsoft.com', optional
In case your tenant is not microsoft please use this param.
"""
kusto_cluster = "https://{0}.kusto.windows.net".format(
conn_kv[ConnStrKeys.CLUSTER])
if all([
conn_kv.get(ConnStrKeys.USERNAME),
conn_kv.get(ConnStrKeys.PASSWORD)
]):
kcsb = KustoConnectionStringBuilder.with_aad_user_password_authentication(
kusto_cluster, conn_kv.get(ConnStrKeys.USERNAME),
conn_kv.get(ConnStrKeys.PASSWORD))
if conn_kv.get(ConnStrKeys.TENANT) is not None:
kcsb.authority_id = conn_kv.get(ConnStrKeys.TENANT)
elif all([
conn_kv.get(ConnStrKeys.CLIENTID),
conn_kv.get(ConnStrKeys.CLIENTSECRET)
]):
kcsb = KustoConnectionStringBuilder.with_aad_application_key_authentication(
kusto_cluster, conn_kv.get(ConnStrKeys.CLIENTID),
conn_kv.get(ConnStrKeys.CLIENTSECRET),
conn_kv.get(ConnStrKeys.TENANT))
elif all([
conn_kv.get(ConnStrKeys.CLIENTID),
conn_kv.get(ConnStrKeys.CERTIFICATE),
conn_kv.get(ConnStrKeys.CERTIFICATE_THUMBPRINT)
]):
kcsb = KustoConnectionStringBuilder.with_aad_application_certificate_authentication(
kusto_cluster,
conn_kv.get(ConnStrKeys.CLIENTID),
conn_kv.get(ConnStrKeys.CERTIFICATE),
conn_kv.get(ConnStrKeys.CERTIFICATE_THUMBPRINT),
conn_kv.get(ConnStrKeys.TENANT),
)
else:
kcsb = KustoConnectionStringBuilder.with_aad_device_authentication(
kusto_cluster)
if conn_kv.get(ConnStrKeys.TENANT) is not None:
kcsb.authority_id = conn_kv.get(ConnStrKeys.TENANT)
self.client = KustoClient(kcsb)
# replace aadhelper to use remote browser in interactive mode
self.client._aad_helper = _MyAadHelper(kcsb, self._DEFAULT_CLIENTID)
self.mgmt_endpoint_version = "v2" if self.client._mgmt_endpoint.endswith(
"v2/rest/query") else "v1"
self.query_endpoint_version = "v2" if self.client._query_endpoint.endswith(
"v2/rest/query") else "v1"
def execute(self,
kusto_database,
query,
accept_partial_results=False,
timeout=None):
""" Execute a simple query or management command
Parameters
----------
kusto_database : str
Database against query will be executed.
query : str
Query to be executed
accept_partial_results : bool
Optional parameter. If query fails, but we receive some results, we consider results as partial.
If this is True, results are returned to client, even if there are exceptions.
If this is False, exception is raised. Default is False.
timeout : float, optional
Optional parameter. Network timeout in seconds. Default is no timeout.
"""
endpoint_version = self.mgmt_endpoint_version if query.startswith(
".") else self.query_endpoint_version
get_raw_response = True
response = self.client.execute(kusto_database, query,
accept_partial_results, timeout,
get_raw_response)
return KqlQueryResponse(response, endpoint_version)
kcsb = KustoConnectionStringBuilder.with_aad_application_certificate_authentication(
cluster, client_id, PEM, thumbprint, authority_id)
# In case you want to authenticate with AAD username and password
username = "******"
password = "******"
kcsb = KustoConnectionStringBuilder.with_aad_user_password_authentication(
cluster, username, password, authority_id)
# In case you want to authenticate with AAD device code.
# Please note that if you choose this option, you'll need to authenticate for every new instance that is initialized.
# It is highly recommended to create one instance and use it for all of your queries.
kcsb = KustoConnectionStringBuilder.with_aad_device_authentication(cluster)
# The authentication method will be taken from the chosen KustoConnectionStringBuilder.
client = KustoClient(kcsb)
######################################################
## QUERY ##
######################################################
# once authenticated, usage is as following
db = "Samples"
query = "StormEvents | take 10"
response = client.execute(db, query)
# iterating over rows is possible
for row in response.primary_results[0]:
# printing specific columns by index
print("value at 0 {}".format(row[0]))
container = adx_cfg_json.get('BLOB_CONTAINER_NAME')
account_name = adx_cfg_json.get('STORAGE_ACCOUNT_NAME')
account_key = adx_cfg_json.get('STORAGE_ACCOUNT_KEY')
sas_token = adx_cfg_json.get('SAS_TOKEN')
# kcsb_ingest = KustoConnectionStringBuilder.with_aad_device_authentication(kusto_ingest_uri, aad_tenant_id)
# kcsb_data = KustoConnectionStringBuilder.with_aad_device_authentication(kusto_uri, aad_tenant_id)
kcsb_ingest = KustoConnectionStringBuilder.with_aad_application_key_authentication(
kusto_ingest_uri, app_client_id, app_client_secret, aad_tenant_id)
kcsb_data = KustoConnectionStringBuilder.with_aad_application_key_authentication(
kusto_uri, app_client_id, app_client_secret, aad_tenant_id)
kusto_client = KustoClient(kcsb_data)
column_mapping = col_map_as_json_dict.get('OBJECT_CONFIG')
blob_service = BlockBlobService(account_name, account_key)
# Function to find latest blob from all available
# Assumes format of <source> + '/' + <YYYYMMDDHHmm> + '_' + <object name>
blob_names_generator = blob_service.list_blobs(container)
# for blob in blob_names_generator:
# length = BlockBlobService.get_blob_properties(blob_service, container, blob.name).properties.content_length
# print('Length of blob {} in bytes {}'.format(blob.name, length))
blobs_list = [blob.name for blob in blob_names_generator]
for obj in column_mapping:
mappings.append(
JsonColumnMapping(columnName="xtime",
jsonPath="$.xtime",
cslDataType="timespan"))
mappings.append(
JsonColumnMapping(columnName="xtextWithNulls",
jsonPath="$.xtextWithNulls",
cslDataType="string"))
mappings.append(
JsonColumnMapping(columnName="xdynamicWithNulls",
jsonPath="$.xdynamicWithNulls",
cslDataType="dynamic"))
return mappings
client = KustoClient("https://toshetah.kusto.windows.net")
ingest_client = KustoIngestClient("https://ingest-toshetah.kusto.windows.net")
ingest_status_q = KustoIngestStatusQueues(ingest_client)
client.execute("PythonTest", ".drop table Deft ifexists")
@pytest.mark.run(order=1)
def test_csv_ingest_non_existing_table():
csv_ingest_props = IngestionProperties(
"PythonTest",
"Deft",
dataFormat=DataFormat.csv,
mapping=Helpers.create_deft_table_csv_mappings(),
reportLevel=ReportLevel.FailuresAndSuccesses,
)
csv_file_path = os.path.join(os.getcwd(), "azure-kusto-ingest", "tests",
def test_sanity_query(self, mock_post, mock_aad):
"""Test query V2."""
client = KustoClient("https://somecluster.kusto.windows.net")
response = client.execute_query("PythonTest", "Deft")
expected = {
"rownumber": None,
"rowguid": text_type(""),
"xdouble": None,
"xfloat": None,
"xbool": None,
"xint16": None,
"xint32": None,
"xint64": None,
"xuint8": None,
"xuint16": None,
"xuint32": None,
"xuint64": None,
"xdate": None,
"xsmalltext": text_type(""),
"xtext": text_type(""),
"xnumberAsText": text_type(""),
"xtime": None,
"xtextWithNulls": text_type(""),
"xdynamicWithNulls": text_type(""),
}
for row in response.primary_results[0]:
self.assertEqual(row["rownumber"], expected["rownumber"])
self.assertEqual(row["rowguid"], expected["rowguid"])
self.assertEqual(row["xdouble"], expected["xdouble"])
self.assertEqual(row["xfloat"], expected["xfloat"])
self.assertEqual(row["xbool"], expected["xbool"])
self.assertEqual(row["xint16"], expected["xint16"])
self.assertEqual(row["xint32"], expected["xint32"])
self.assertEqual(row["xint64"], expected["xint64"])
self.assertEqual(row["xuint8"], expected["xuint8"])
self.assertEqual(row["xuint16"], expected["xuint16"])
self.assertEqual(row["xuint32"], expected["xuint32"])
self.assertEqual(row["xuint64"], expected["xuint64"])
self.assertEqual(row["xdate"], expected["xdate"])
self.assertEqual(row["xsmalltext"], expected["xsmalltext"])
self.assertEqual(row["xtext"], expected["xtext"])
self.assertEqual(row["xnumberAsText"], expected["xnumberAsText"])
self.assertEqual(row["xtime"], expected["xtime"])
self.assertEqual(row["xtextWithNulls"], expected["xtextWithNulls"])
self.assertEqual(row["xdynamicWithNulls"], expected["xdynamicWithNulls"])
self.assertEqual(type(row["rownumber"]), type(expected["rownumber"]))
self.assertEqual(type(row["rowguid"]), type(expected["rowguid"]))
self.assertEqual(type(row["xdouble"]), type(expected["xdouble"]))
self.assertEqual(type(row["xfloat"]), type(expected["xfloat"]))
self.assertEqual(type(row["xbool"]), type(expected["xbool"]))
self.assertEqual(type(row["xint16"]), type(expected["xint16"]))
self.assertEqual(type(row["xint32"]), type(expected["xint32"]))
self.assertEqual(type(row["xint64"]), type(expected["xint64"]))
self.assertEqual(type(row["xuint8"]), type(expected["xuint8"]))
self.assertEqual(type(row["xuint16"]), type(expected["xuint16"]))
self.assertEqual(type(row["xuint32"]), type(expected["xuint32"]))
self.assertEqual(type(row["xuint64"]), type(expected["xuint64"]))
self.assertEqual(type(row["xdate"]), type(expected["xdate"]))
self.assertEqual(type(row["xsmalltext"]), type(expected["xsmalltext"]))
self.assertEqual(type(row["xtext"]), type(expected["xtext"]))
self.assertEqual(type(row["xnumberAsText"]), type(expected["xnumberAsText"]))
self.assertEqual(type(row["xtime"]), type(expected["xtime"]))
self.assertEqual(type(row["xtextWithNulls"]), type(expected["xtextWithNulls"]))
self.assertEqual(type(row["xdynamicWithNulls"]), type(expected["xdynamicWithNulls"]))
expected["rownumber"] = 0 if expected["rownumber"] is None else expected["rownumber"] + 1
expected["rowguid"] = text_type("0000000{0}-0000-0000-0001-020304050607".format(expected["rownumber"]))
expected["xdouble"] = round(float(0) if expected["xdouble"] is None else expected["xdouble"] + 1.0001, 4)
expected["xfloat"] = round(float(0) if expected["xfloat"] is None else expected["xfloat"] + 1.01, 2)
expected["xbool"] = False if expected["xbool"] is None else not expected["xbool"]
expected["xint16"] = 0 if expected["xint16"] is None else expected["xint16"] + 1
expected["xint32"] = 0 if expected["xint32"] is None else expected["xint32"] + 1
expected["xint64"] = 0 if expected["xint64"] is None else expected["xint64"] + 1
expected["xuint8"] = 0 if expected["xuint8"] is None else expected["xuint8"] + 1
expected["xuint16"] = 0 if expected["xuint16"] is None else expected["xuint16"] + 1
expected["xuint32"] = 0 if expected["xuint32"] is None else expected["xuint32"] + 1
expected["xuint64"] = 0 if expected["xuint64"] is None else expected["xuint64"] + 1
expected["xdate"] = expected["xdate"] or datetime(2013, 1, 1, 1, 1, 1, 0, tzinfo=tzutc())
expected["xdate"] = expected["xdate"].replace(year=expected["xdate"].year + 1)
expected["xsmalltext"] = DIGIT_WORDS[int(expected["xint16"])]
expected["xtext"] = DIGIT_WORDS[int(expected["xint16"])]
expected["xnumberAsText"] = text_type(expected["xint16"])
microseconds = 1001 if expected["rownumber"] == 5 else 1000
expected["xtime"] = (
timedelta()
if expected["xtime"] is None
else (abs(expected["xtime"]) + timedelta(seconds=1, microseconds=microseconds))
* (-1) ** (expected["rownumber"] + 1)
)
if expected["xint16"] > 0:
expected["xdynamicWithNulls"] = text_type('{{"rowId":{0},"arr":[0,{0}]}}'.format(expected["xint16"]))
