Esempio n. 1
0
    def execute_query(self, parsed, user_ns, result_set=None):
        if Help_html.showfiles_base_url is None:
            window_location = user_ns.get("NOTEBOOK_URL")
            if window_location is not None:
                Help_html.flush(window_location,
                                notebook_app=self.notebook_app)
            else:
                self.submit_get_notebook_url()

        query = parsed["query"].strip()
        options = parsed["options"]
        suppress_results = options.get(
            "suppress_results", False) and options.get(
                "enable_suppress_result", self.enable_suppress_result)
        connection_string = parsed["connection"]

        special_info = False
        if options.get("version"):
            print("{0} version: {1}".format(Constants.MAGIC_PACKAGE_NAME,
                                            VERSION))
            special_info = True

        if options.get("palette"):
            palette = Palette(
                palette_name=options.get("palette_name", self.palette_name),
                n_colors=options.get("palette_colors", self.palette_colors),
                desaturation=options.get("palette_desaturation",
                                         self.palette_desaturation),
                to_reverse=options.get("palette_reverse", False),
            )
            html_str = palette._repr_html_()
            Display.show_html(html_str)
            special_info = True

        if options.get("popup_palettes"):
            n_colors = options.get("palette_colors", self.palette_colors)
            desaturation = options.get("palette_desaturation",
                                       self.palette_desaturation)
            palettes = Palettes(n_colors=n_colors, desaturation=desaturation)
            html_str = palettes._repr_html_()
            button_text = "popup {0} colors palettes".format(n_colors)
            file_name = "{0}_colors_palettes".format(n_colors)
            if desaturation is not None and desaturation != 1.0 and desaturation != 0:
                file_name += "_desaturation{0}".format(str(desaturation))
                button_text += " (desaturation {0})".format(str(desaturation))
            file_path = Display._html_to_file_path(html_str, file_name,
                                                   **options)
            Display.show_window(file_name,
                                file_path,
                                button_text=button_text,
                                onclick_visibility="visible")
            special_info = True

        if options.get("popup_help"):
            help_url = "http://aka.ms/kdocs"
            # 'https://docs.loganalytics.io/docs/Language-Reference/Tabular-operators'
            # 'http://aka.ms/kdocs'
            # 'https://kusdoc2.azurewebsites.net/docs/queryLanguage/query-essentials/readme.html'
            # import requests
            # f = requests.get(help_url)
            # html = f.text.replace('width=device-width','width=500')
            # Display.show(html, **{"popup_window" : True, 'name': 'KustoQueryLanguage'})
            button_text = "popup kql help "
            Display.show_window("KustoQueryLanguage",
                                help_url,
                                button_text,
                                onclick_visibility="visible")
            special_info = True

        if special_info and not query and not connection_string:
            return None

        try:
            #
            # set connection
            #
            conn = Connection.get_connection(connection_string, **options)

        # parse error
        except KqlEngineError as e:
            if options.get("short_errors", self.short_errors):
                msg = Connection.tell_format(connection_string)
                Display.showDangerMessage(str(e))
                Display.showInfoMessage(msg)
                return None
            else:
                raise

        # parse error
        except ConnectionError as e:
            if options.get("short_errors", self.short_errors):
                Display.showDangerMessage(str(e))
                self._show_connection_info(show_conn_info="list")
                return None
            else:
                raise

        try:
            # validate connection
            if not conn.options.get(
                    "validate_connection_string_done") and options.get(
                        "validate_connection_string",
                        self.validate_connection_string):
                retry_with_code = False
                try:
                    conn.validate(**options)
                    conn.set_validation_result(True)
                except Exception as e:
                    msg = str(e)
                    if msg.find("AADSTS50079") > 0 and msg.find(
                            "multi-factor authentication") > 0 and isinstance(
                                conn, KustoEngine):
                        Display.showDangerMessage(str(e))
                        retry_with_code = True
                    else:
                        raise e

                if retry_with_code:
                    Display.showInfoMessage(
                        "replaced connection with code authentication")
                    database_name = conn.get_database()
                    cluster_name = conn.get_cluster()
                    uri_schema_name = conn._URI_SCHEMA_NAME
                    connection_string = "{0}://code().cluster('{1}').database('{2}')".format(
                        uri_schema_name, cluster_name, database_name)
                    conn = Connection.get_connection(connection_string,
                                                     **options)
                    conn.validate(**options)
                    conn.set_validation_result(True)

            conn.options["validate_connection_string_done"] = True

            schema_file_path = None
            if options.get("popup_schema") or (
                    not conn.options.get("auto_popup_schema_done") and
                    options.get("auto_popup_schema", self.auto_popup_schema)):
                schema_file_path = Database_html.get_schema_file_path(
                    conn, **options)
                Database_html.popup_schema(schema_file_path, conn)

            conn.options["auto_popup_schema_done"] = True
            if not conn.options.get("add_schema_to_help_done") and options.get(
                    "add_schema_to_help"):
                schema_file_path = schema_file_path or Database_html.get_schema_file_path(
                    conn, **options)
                Help_html.add_menu_item(conn.get_conn_name(), schema_file_path,
                                        **options)
                conn.options["add_schema_to_help_done"] = True

            if not query:
                #
                # If NO  kql query, just return the current connection
                #
                if not connection_string and Connection.connections and not suppress_results:
                    self._show_connection_info(**options)
                return None
            #
            # submit query
            #
            start_time = time.time()

            params_dict_name = options.get('params_dict')
            dictionary = user_ns.get(
                params_dict_name) if params_dict_name is not None and len(
                    params_dict_name) > 0 else user_ns
            parametrized_query = Parameterizer(dictionary).expand(
                query) if result_set is None else result_set.parametrized_query
            raw_query_result = conn.execute(parametrized_query, user_ns,
                                            **options)

            end_time = time.time()

            #
            # model query results
            #
            if result_set is None:
                fork_table_id = 0
                saved_result = ResultSet(raw_query_result,
                                         parametrized_query,
                                         fork_table_id=0,
                                         fork_table_resultSets={},
                                         metadata={},
                                         options=options)
                saved_result.metadata["magic"] = self
                saved_result.metadata["parsed"] = parsed
                saved_result.metadata["connection"] = conn.get_conn_name()
            else:
                fork_table_id = result_set.fork_table_id
                saved_result = result_set.fork_result(0)
                saved_result.feedback_info = []
                saved_result._update(raw_query_result)

            result = saved_result

            if not connection_string and Connection.connections:
                saved_result.metadata["conn_info"] = self._get_connection_info(
                    **options)
            else:
                saved_result.metadata["conn_info"] = []

            saved_result.metadata["start_time"] = start_time
            saved_result.metadata["end_time"] = end_time

            if options.get("feedback", self.feedback):
                minutes, seconds = divmod(end_time - start_time, 60)
                saved_result.feedback_info.append(
                    "Done ({:0>2}:{:06.3f}): {} records".format(
                        int(minutes), seconds, saved_result.records_count))

            if options.get("columns_to_local_vars",
                           self.columns_to_local_vars):
                # Instead of returning values, set variables directly in the
                # users namespace. Variable names given by column names

                if options.get("feedback", self.feedback):
                    saved_result.feedback_info.append(
                        "Returning raw data to local variables")

                self.shell.user_ns.update(saved_result.to_dict())
                result = None

            if options.get("auto_dataframe", self.auto_dataframe):
                if options.get("feedback", self.feedback):
                    saved_result.feedback_info.append(
                        "Returning data converted to pandas dataframe")
                result = saved_result.to_dataframe()

            if options.get("result_var") and result_set is None:
                result_var = options["result_var"]
                if options.get("feedback", self.feedback):
                    saved_result.feedback_info.append(
                        "Returning data to local variable {}".format(
                            result_var))
                self.shell.user_ns.update({
                    result_var:
                    result if result is not None else saved_result
                })
                result = None

            if options.get('cache') and not options.get(
                    'use_cache') and not isinstance(conn, CacheEngine):
                file_path = CacheClient().save(raw_query_result,
                                               conn.get_database(),
                                               conn.get_cluster(),
                                               parametrized_query, **options)
                if options.get("feedback", self.feedback):
                    saved_result.feedback_info.append("query results cached")

            if options.get('save_as') is not None:
                file_path = CacheClient().save(raw_query_result,
                                               conn.get_database(),
                                               conn.get_cluster(),
                                               parametrized_query,
                                               filepath=options.get('save_as'),
                                               **options)
                if options.get("feedback", self.feedback):
                    saved_result.feedback_info.append(
                        "query results saved as {0}".format(file_path))

            saved_result.suppress_result = False
            saved_result.display_info = False
            if result is not None:
                if suppress_results:
                    saved_result.suppress_result = True
                elif options.get("auto_dataframe", self.auto_dataframe):
                    Display.showSuccessMessage(saved_result.feedback_info)
                else:
                    saved_result.display_info = True

            if result_set is None:
                saved_result._create_fork_results()
            else:
                saved_result._update_fork_results()

            # Return results into the default ipython _ variable
            self.shell.user_ns.update({
                options.get("last_raw_result_var", self.last_raw_result_var):
                saved_result
            })

            if result == saved_result:
                result = saved_result.fork_result(fork_table_id)
            return result

        except Exception as e:
            if not connection_string and Connection.connections and not suppress_results:
                # display list of all connections
                self._show_connection_info(**options)

            if options.get("short_errors", self.short_errors):
                Display.showDangerMessage(e)
                return None
            else:
                raise e
Esempio n. 2
0
    def execute(self, line, cell="", local_ns={}):
        """Query Kusto or ApplicationInsights using kusto query language (kql). Repository specified by a connect string.

        Magic Syntax::

            %%kql <connection-string>
            <KQL statement>
            # Note: establish connection and query.

            %%kql <established-connection-reference>
            <KQL statemnt>
            # Note: query using an established connection.

            %%kql
            <KQL statement>
            # Note: query using current established connection.

            %kql <KQL statment>
            # Note: single line query using current established connection.

            %kql <connection-string>
            # Note: established connection only.


        Connection string Syntax::

            kusto://username('<username>).password(<password>).cluster(<cluster>).database(<database>')

            appinsights://appid(<appid>).appkey(<appkey>)

            loganalytics://workspace(<workspaceid>).appkey(<appkey>)

            %<connectionStringVariable>%
            # Note: connection string is taken from the environment variable.

            [<sectionName>]
            # Note: connection string is built from the dsn file settings, section <sectionName>. 
            #       The dsn filename value is taken from configuartion value Kqlmagic.dsn_filename.

            # Note: if password or appkey component is missing, user will be prompted.
            # Note: connection string doesn't have to include all components, see examples below.
            # Note: substring of the form $name or ${name} in windows also %name%, are replaced by environment variables if exist.


        Examples::

            %%kql kusto://username('myName').password('myPassword').cluster('myCluster').database('myDatabase')
            <KQL statement>
            # Note: establish connection to kusto and submit query.

            %%kql myDatabase@myCluster
            <KQL statement>
            # Note: submit query using using an established kusto connection to myDatabase database at cluster myCluster.

            %%kql appinsights://appid('myAppid').appkey('myAppkey')
            <KQL statement>
            # Note: establish connection to ApplicationInsights and submit query.

            %%kql myAppid@appinsights
            <KQL statement>
            # Note: submit query using established ApplicationInsights connection to myAppid.

            %%kql loganalytics://workspace('myWorkspaceid').appkey('myAppkey')
            <KQL statement>
            # Note: establish connection to LogAnalytics and submit query.

            %%kql myWorkspaceid@loganalytics
            <KQL statement>
            # Note: submit query using established LogAnalytics connection to myWorkspaceid.

            %%kql
            <KQL statement>
            # Note: submit query using current established connection.

            %kql <KQL statement>
            # Note: submit single line query using current established connection.

            %%kql kusto://cluster('myCluster').database('myDatabase')
            <KQL statement>
            # Note: establish connection to kusto using current username and password to form the full connection string and submit query.

            %%kql kusto://database('myDatabase')
            <KQL statement>
            # Note: establish connection to kusto using current username, password and cluster to form the full connection string and submit query.

            %kql kusto://username('myName').password('myPassword').cluster('myCluster')
            # Note: set current (default) username, passsword and cluster to kusto.

            %kql kusto://username('myName').password('myPassword')
            # Note: set current (default) username and password to kusto.

            %kql kusto://cluster('myCluster')
            # Note set current (default) cluster to kusto.
        """

        set_logger(Logger(None, create_log_context()))

        # save globals and locals so they can be referenced in bind vars
        user_ns = self.shell.user_ns.copy()
        user_ns.update(local_ns)

        logger().debug("To Parsed: \n\rline: {}\n\rcell:\n\r{}".format(
            line, cell))
        try:
            parsed = None
            parsed_queries = Parser.parse("%s\n%s" % (line, cell), self)
            logger().debug("Parsed: {}".format(parsed_queries))
            result = None
            for parsed in parsed_queries:
                result = self.execute_query(parsed, user_ns)
            return result
        except Exception as e:
            if parsed:
                if parsed["options"].get("short_errors", self.short_errors):
                    Display.showDangerMessage(str(e))
                    return None
            elif self.short_errors:
                Display.showDangerMessage(str(e))
                return None
            raise