Kusto Ingest - KustoServiceError 'BadRequest_SyntaxError'

Question

I have the following code for ingesting data into Azure Data Explore using Python in Databricks:

df=pd.DataFrame({"StringCol": ["123ABC", 'B123', 'C123','D123'],"NumberCol": [1,2,3,4],"DecimalCol": [1,2.2,3.3,4.4],"DateCol": ['1/1/20','2/2/20','3/3/30','4/4/20']})
ingestion_props = IngestionProperties(database=db, table='TestTable_DeleteMe')
connWrite.ingest_from_dataframe(df, ingestion_properties=ingestion_props)

This gives me the error:

BadRequest_SyntaxError', 'message': 'Request is invalid and cannot be executed

Earlier in my code I created a table using the same data types as this dummy pandas dataframe. Now I'm trying to load the data into the table. Full stack trace:

KustoServiceError                         Traceback (most recent call last)
<command-3953651275234016> in <module>
      1 df=pd.DataFrame({"StringCol": ["123ABC", 'B123', 'C123','D123'],"NumberCol": [1,2,3,4],"DecimalCol": [1,2.2,3.3,4.4],"DateCol": ['1/1/20','2/2/20','3/3/30','4/4/20']})
      2 ingestion_props = IngestionProperties(database=db, table='TestTable_DeleteMe')
----> 3 connWrite.ingest_from_dataframe(df, ingestion_properties=ingestion_props)
      4 
      5 #adx_loadIntoTable(connWrite,db,df,'TestTable_DeleteMe')

/databricks/python/lib/python3.7/site-packages/azure/kusto/ingest/ingest_client.py in ingest_from_dataframe(self, df, ingestion_properties)
     52         ingestion_properties.format = DataFormat.CSV
     53 
---> 54         self.ingest_from_file(temp_file_path, ingestion_properties)
     55 
     56         os.unlink(temp_file_path)

/databricks/python/lib/python3.7/site-packages/azure/kusto/ingest/ingest_client.py in ingest_from_file(self, file_descriptor, ingestion_properties)
     64         :param azure.kusto.ingest.IngestionProperties ingestion_properties: Ingestion properties.
     65         """
---> 66         containers = self._resource_manager.get_containers()
     67 
     68         if isinstance(file_descriptor, FileDescriptor):

/databricks/python/lib/python3.7/site-packages/azure/kusto/ingest/_resource_manager.py in get_containers(self)
    121 
    122     def get_containers(self) -> List[_ResourceUri]:
--> 123         self._refresh_ingest_client_resources()
    124         return self._ingest_client_resources.containers
    125 

/databricks/python/lib/python3.7/site-packages/azure/kusto/ingest/_resource_manager.py in _refresh_ingest_client_resources(self)
     79             or not self._ingest_client_resources.is_applicable()
     80         ):
---> 81             self._ingest_client_resources = self._get_ingest_client_resources_from_service()
     82             self._ingest_client_resources_last_update = datetime.utcnow()
     83 

/databricks/python/lib/python3.7/site-packages/azure/kusto/ingest/_resource_manager.py in _get_ingest_client_resources_from_service(self)
     86 
     87     def _get_ingest_client_resources_from_service(self):
---> 88         table = self._kusto_client.execute("NetDefaultDB", ".get ingestion resources").primary_results[0]
     89 
     90         secured_ready_for_aggregation_queues = self._get_resource_by_name(table, "SecuredReadyForAggregationQueue")

/databricks/python/lib/python3.7/site-packages/azure/kusto/data/client.py in execute(self, database, query, properties)
    553         query = query.strip()
    554         if query.startswith("."):
--> 555             return self.execute_mgmt(database, query, properties)
    556         return self.execute_query(database, query, properties)
    557 

/databricks/python/lib/python3.7/site-packages/azure/kusto/data/client.py in execute_mgmt(self, database, query, properties)
    578         :rtype: azure.kusto.data.response.KustoResponseDataSet
    579         """
--> 580         return self._execute(self._mgmt_endpoint, database, query, None, KustoClient._mgmt_default_timeout, properties)
    581 
    582     def execute_streaming_ingest(

/databricks/python/lib/python3.7/site-packages/azure/kusto/data/client.py in _execute(self, endpoint, database, query, payload, timeout, properties)
    654             )
    655 
--> 656         raise KustoServiceError([response.json()], response)

KustoServiceError: (KustoServiceError(...), [{'error': {'code': 'BadRequest_SyntaxError', 'message': 'Request is invalid and cannot be executed.', '@type': 'Kusto.Data.Exceptions.SyntaxException', '@message': "Syntax error: Query could not be parsed: . Query: '.get ingestion resources'", '@context': {'timestamp': '2020-06-27T21:44:48.0697658Z', 'serviceAlias': 'USCPIRSTASADE01', 'machineName': 'KEngine000000', 'processName': 'Kusto.WinSvc.Svc', 'processId': 7124, 'threadId': 7240, 'appDomainName': 'Kusto.WinSvc.Svc.exe', 'clientRequestId': 'KPC.execute;0c2173bf-ea69-4253-bbaf-0203f3aa298c', 'activityId': 'cf41c806-8e15-458e-b388-386613f63952', 'subActivityId': 'df366667-ca8d-487b-a281-723f696a8f68', 'activityType': 'DN.FE.ExecuteControlCommand', 'parentActivityId': 'f8cd0bb8-04e9-48cf-8a84-8b16e1e24197', 'activityStack': '(Activity stack: CRID=KPC.execute;0c2173bf-ea69-4253-bbaf-0203f3aa298c ARID=cf41c806-8e15-458e-b388-386613f63952 > DN.Admin.Client.ExecuteControlCommand/7271d9ec-2adf-4714-b19e-69495ad80d65 > P.WCF.Service.ExecuteControlCommandInternal..IAdminClientServiceCommunicationContract/f8cd0bb8-04e9-48cf-8a84-8b16e1e24197 > DN.FE.ExecuteControlCommand/df366667-ca8d-487b-a281-723f696a8f68)'}, '@permanent': True}}])

  

Answer 1

It is likely that your connection has the engine endpoint instead of the data management endpoint. Can you check that the connection to the cluster starts with "ingest-"? See here an example:

client = KustoIngestClient("https://ingest-{cluster_name}.kusto.windows.net")