(5) Convert Kusto query (KQL) to SQL using Kusto parser library

[HannahBagtasos]

Successfully execute queries to convert KQL to SQL

Using this Kusto library, parse the KQL to SQL: https://github.com/microsoft/Kusto-Query-Language

Aim for 10 examples then do general examples.

[Denis12484]

Logs
| where Timestamp >= datetime(2015-08-22 05:00) and Timestamp < datetime(2015-08-22 06:00)
| where Level == "e" and Service == "Inferences.UnusualEvents_Main"
| project Level, Timestamp, Message
| limit 10

[HannahBagtasos]

Parse a single KQL query Kusto parser:

 string KQLquery = "T | project a = a + b | where a > 10.0";
            KustoCode code = KustoCode.Parse(KQLquery);

Parse multiple KQL query using Kusto parser:

 List<string> queries = new List<string>()

                {
                 "Logs | where Timestamp >= datetime(2015-08-22 05:00) and Timestamp < datetime(2015-08-22 06:00) | where Level == \"e\" and Service == \"Inferences.UnusualEvents_Main\" | project Level, Timestamp, Message | limit 10",
                 "Logs | where Timestamp >= datetime(2015-08-22 07:00) and Timestamp < datetime(2015-08-22 08:00) | where Level == \"e\" and Service == \"Inferences.UnusualEvents_Main\" | project Level, Timestamp, Message | limit 11",
                 "Logs | where Timestamp >= datetime(2015-09-22 05:00) and Timestamp < datetime(2015-09-22 06:00) | where Level == \"e\" and Service == \"Inferences.UnusualEvents_Main\" | project Level, Timestamp, Message | limit 12",
                 "Logs | where Timestamp >= datetime(2015-10-22 05:00) and Timestamp < datetime(2015-10-22 06:00) | where Level == \"e\" and Service == \"Inferences.UnusualEvents_Main\" | project Level, Timestamp, Message | limit 13",
                 "Logs | where Timestamp >= datetime(2015-10-22 08:00) and Timestamp < datetime(2015-10-22 09:00) | where Level == \"e\" and Service == \"Inferences.UnusualEvents_Main\" | project Level, Timestamp, Message | limit 14",
                 "Logs | where Timestamp >= datetime(2015-11-22 05:00) and Timestamp < datetime(2015-11-22 06:00) | where Level == \"e\" and Service == \"Inferences.UnusualEvents_Main\" | project Level, Timestamp, Message | limit 15",
                 "Logs | where Timestamp >= datetime(2015-12-22 05:00) and Timestamp < datetime(2015-12-22 06:00) | where Level == \"e\" and Service == \"Inferences.UnusualEvents_Main\" | project Level, Timestamp, Message | limit 16",
                 "Logs | where Timestamp >= datetime(2016-09-22 05:00) and Timestamp < datetime(2016-09-22 06:00) | where Level == \"e\" and Service == \"Inferences.UnusualEvents_Main\" | project Level, Timestamp, Message | limit 17",
                 "Logs | where Timestamp >= datetime(2016-09-22 05:00) and Timestamp < datetime(2016-10-22 06:00) | where Level == \"e\" and Service == \"Inferences.UnusualEvents_Main\" | project Level, Timestamp, Message | limit 18",
                 "Logs | where Timestamp >= datetime(2016-10-22 05:00) and Timestamp < datetime(2016-10-22 06:00) | where Level == \"e\" and Service == \"Inferences.UnusualEvents_Main\" | project Level, Timestamp, Message | limit 19",
                };

            foreach (string query in queries)
            {
                KustoCode code = KustoCode.Parse(query);
                Console.WriteLine(code.ToString()); // or code.Syntax.ToString() for just the syntax tree
            }

[Denis12484]

Logs | where Timestamp >= datetime(2015-08-22 05:00) | where Level == "e" | limit 10

[HannahBagtasos]

No longer using Kusto parse library, now using regex to convert KQL to SQL

 List<string> queries = new List<string>()

                {

                 "Logs | where Timestamp >= datetime(2015-08-22 05:00) | where Level == \"e\" | limit 10",
                 "Logs | where Timestamp >= datetime(2015-08-22 07:00) | where Level == \"e\" | limit 11",
                 "Logs | where Timestamp >= datetime(2015-09-22 05:00) | where Level == \"e\" | limit 12",
                 "Logs | where Timestamp >= datetime(2015-10-22 05:00) | where Level == \"e\" | limit 13",
                 "Logs | where Timestamp >= datetime(2015-10-22 08:00) | where Level == \"e\" | limit 14",
                 "Logs | where Timestamp >= datetime(2015-11-22 05:00) | where Level == \"e\" | limit 15",
                 "Logs | where Timestamp >= datetime(2015-12-22 05:00) | where Level == \"e\" | limit 16",
                 "Logs | where Timestamp >= datetime(2016-09-22 05:00) | where Level == \"e\" | limit 17",
                 "Logs | where Timestamp >= datetime(2016-09-22 05:00) | where Level == \"e\" | limit 18",
                 "Logs | where Timestamp >= datetime(2016-10-22 05:00) | where Level == \"e\" | limit 19",
                };

            //All occurrences of the name "a" in the C# code represented are found 
            //and returned as a collection of NameReference nodes in the referencesToA variable.
            string tableReg = @"(?<table>Logs)\s";
            string timestampReg = @"Timestamp\s+>=\s+datetime\((\d+-\d+-\d+\s+\d+:\d+)\)";
            string levelReg = @"Level\s+==\s+""(\w+)""";
            string limitReg = @"limit\s+(\d+)";

            foreach (string kql_query in queries)
            {

                Match table = Regex.Match(kql_query, tableReg);
                Match timestamp = Regex.Match(kql_query, timestampReg);
                Match level= Regex.Match(kql_query, levelReg);
                Match limit = Regex.Match(kql_query, limitReg);

                string sqlQuery = string.Format("SELECT * FROM '{0}' WHERE Timestamp >= '{1}' AND Level = '{2}' LIMIT {3};",
                                                 table.Groups[1].Value,
                                                 timestamp.Groups[1].Value,
                                                 level.Groups[1].Value,
                                                 limit.Groups[1].Value);

                Console.WriteLine(sqlQuery);

            }