helper-non_tasks-to-dynamic-tables/notebook-non_tasks-to-dynamic-tables.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "079fe083-c828-4c86-ad31-08d50310486c",
   "metadata": {
    "collapsed": false,
    "name": "Overview"
   },
   "source": [
    "# Replace Non-Task commands with Dynamic Tables\n",
    "\n",
    "This notebook identifies `CTAS` and `INSERT OVERWRITE` commands executed multiple times over a given time frame (not via a task), that can be converted to Dynamic tables.  Dynamic tables simplify data engineering in Snowflake by providing a reliable, cost-effective, and automated way to transform data. Not every command can or should be replaced.  \n",
    "\n",
    "This notebook will:\n",
    "- check the `QUERY_HISTORY` account usage view for the commands that have successfully completed, more than once, over the last 24 hours.\n",
    "- identify whether the current target table is in a share.\n",
    "    - **NOTE:** Data Providers should take additional steps to ensure any affected shared tables don't impact Consumers before switching to Dynamic tables.\n",
    "- generate the DDL to create the Dynamic table that will replace the commands\n",
    "- execute the Dynamic table DDL (optional)\n",
    "- remove the existing target table from the share, if applicable (optional)\n",
    "- drop the existing target table (optional)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5045de21-9f8c-443f-96c0-9a67c1b893d9",
   "metadata": {
    "collapsed": false,
    "name": "Prerequisites"
   },
   "source": [
    "## Prerequisites:\n",
    "\n",
    "- The user executing this notebook, must have access to the `SNOWFLAKE` database.\n",
    "- The user must have the `CREATE DYNAMIC TABLE` privilge on the schema where the new Dynamic Table will be created."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a9ce2fba-7a16-4bc5-9929-afda2e534efb",
   "metadata": {
    "collapsed": false,
    "name": "Step_1_Label"
   },
   "source": [
    "## STEP 1: Initiaize Session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96f7b910-866d-481d-9454-9053a2fcb075",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "Step_1_Initialize_Session"
   },
   "outputs": [],
   "source": [
    "import json\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import re\n",
    "import streamlit as st\n",
    "from st_aggrid import AgGrid, GridUpdateMode, JsCode\n",
    "from st_aggrid.grid_options_builder import GridOptionsBuilder\n",
    "import sqlparse\n",
    "\n",
    "session = get_active_session()\n",
    "\n",
    "#tag session\n",
    "session.sql(f\"\"\"ALTER SESSION SET QUERY_TAG = '{{\"origin\":\"sf_sit\",\"name\":\"dt_conversion_non_task\",\"version\":{{\"major\":1, \"minor\":0}},\"attributes\":\"session_tag\"}}'\"\"\").collect()\n",
    "\n",
    "#get current_role\n",
    "current_role = session.get_current_role().replace('\"','')\n",
    "\n",
    "st.success(f\"Session initialized for role: {current_role} 🎉\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66492742-c41a-4570-81ee-fc874914ed7d",
   "metadata": {
    "collapsed": false,
    "name": "Step_2_Label"
   },
   "source": [
    "## STEP 2: Function definition"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ac72105-496b-4e17-a807-495078083cba",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "Step_2_Function_Definition"
   },
   "outputs": [],
   "source": [
    "def paginate_data(df):\n",
    "\tst.divider()\n",
    "\t\t\t\n",
    "\tpagination = st.empty()\n",
    "\tbatch_size = 20  # Set the number of items per page\n",
    "\n",
    "\tif len(df) > 0:\n",
    "\t\tbottom_menu = st.columns((4, 1, 1))\n",
    "\t\twith bottom_menu[2]:\n",
    "\t\t\ttotal_pages = (\n",
    "    \t\t\tint(len(df) / batch_size) if int(len(df) / batch_size) > 0 else 1\n",
    "    \t\t)\n",
    "\t\t\tcurrent_page = st.number_input(\n",
    "    \t\t\t\"Page\", min_value=1, max_value=total_pages, step=1\n",
    "    \t\t)\n",
    "\t\twith bottom_menu[0]:\n",
    "\t\t\tst.markdown(f\"Page **{current_page}** of **{total_pages}** \")\n",
    "    \n",
    "\t\tpages = split_frame(df, batch_size)\n",
    "\t\tpagination.dataframe(data=pages[current_page - 1], use_container_width=True)\n",
    "    \n",
    "\t\tst.divider()\n",
    "\telse:\n",
    "\t\tst.caption(\"No results to display.\")\n",
    "\n",
    "@st.cache_data(show_spinner=False)\n",
    "def split_frame(input_df, rows):\n",
    "\tdf = [input_df.loc[i : i + rows - 1, :] for i in range(0, len(input_df), rows)]\n",
    "\treturn df\n",
    "\n",
    "st.success(f\"Functions created 🎉\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d1ac194-cbf3-4073-bef7-eee8628810f8",
   "metadata": {
    "collapsed": false,
    "name": "Step_3_Label"
   },
   "source": [
    "## STEP 3: Get all shared tables/views\n",
    "\n",
    "This step compiles a list of all tables/views shared by your role."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6bbdf769-efb5-42d8-9ea6-3ccf22ad4452",
   "metadata": {
    "codeCollapsed": false,
    "collapsed": false,
    "language": "python",
    "name": "Step_3_Shared_Objs"
   },
   "outputs": [],
   "source": [
    "list_shares_objs = []\n",
    "\n",
    "#show all shares\n",
    "session.sql(f\"\"\"SHOW SHARES;\"\"\").collect()\n",
    "\n",
    "#get outbound shares only\n",
    "df_outbound_shares = pd.DataFrame(session.sql(f\"\"\"SELECT \"name\" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) WHERE LOWER(\"kind\") = 'outbound' AND LOWER(\"owner\") = '{current_role.lower()}';\"\"\").collect())\n",
    "\n",
    "for index, row in df_outbound_shares.iterrows():\n",
    "    share = row[\"name\"]\n",
    "\n",
    "    try:\n",
    "        #describe shares\n",
    "        session.sql(f\"\"\"DESCRIBE SHARE {share};\"\"\").collect()\n",
    "        \n",
    "        #get shared objects\n",
    "        df_shared_objs = pd.DataFrame(session.sql(f\"\"\"SELECT \"name\", \"kind\" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) \n",
    "                                                        WHERE \n",
    "                                                            LOWER(\"kind\") IN ('table', 'view', 'materialized view');\"\"\").collect())\n",
    "\n",
    "        #add each object to the list_obj list\n",
    "        for index, row in df_shared_objs.iterrows():\n",
    "            name = row[\"name\"]\n",
    "            kind = row[\"kind\"]\n",
    "\n",
    "            if not row.empty:\n",
    "                list_shares_objs.append([share, kind, name])\n",
    "    except:\n",
    "        pass\n",
    "\n",
    "#create list of shares, object types, and objs shared\n",
    "list_shares = [item[0] for item in list_shares_objs]\n",
    "list_obj_types = [item[1] for item in list_shares_objs]\n",
    "list_objs = [item[2] for item in list_shares_objs]\n",
    "   \n",
    "df_shared_objs = pd.DataFrame({'share': list_shares, 'object_type': list_obj_types, 'object': list_objs} )\n",
    "\n",
    "#show shared objects\n",
    "paginate_data(df_shared_objs)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f5cf79f-71c3-4040-9b5b-ec494ec39eaa",
   "metadata": {
    "collapsed": false,
    "name": "Step_4_Label"
   },
   "source": [
    "## STEP 4: Find the completed CTAS/INSERT OVERWRITE commands.\n",
    "\n",
    "This step compiles a list of latest `CTAS` and `INSERT OVERWRITE` commands completed within the last 24 hours."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ec013ae3-0733-4def-a0f6-200f0b508f45",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "Step_4_Get_Cmds"
   },
   "outputs": [],
   "source": [
    "query_history_range_list = ['Choose a Date Range', 'Last day', 'Last 7 days', 'Last 14 days']\n",
    "st.write(\"\")\n",
    "st.selectbox(\"Select Query History Date Range:\", query_history_range_list, key=\"sb_query_history_range\")\n",
    "\n",
    "date_time_part = \"\"\n",
    "increment = \"\"\n",
    "df_query_history_range = None\n",
    "\n",
    "if st.session_state.sb_query_history_range == \"Last day\":\n",
    "    date_time_part = \"hours\"\n",
    "    increment = \"24\"\n",
    "elif st.session_state.sb_query_history_range == \"Last 7 days\":\n",
    "    date_time_part = \"days\"\n",
    "    increment = \"7\"\n",
    "elif st.session_state.sb_query_history_range == \"Last 14 days\":\n",
    "    date_time_part = \"days\"\n",
    "    increment = \"14\"\n",
    "\n",
    "if st.session_state.sb_query_history_range == \"Choose a Date Range\":\n",
    "    st.write(\"#\")\n",
    "    st.write(\"#\")\n",
    "    st.write(\"#\")\n",
    "    st.write(\"#\")\n",
    "    st.write(\"#\")\n",
    "    st.write(\"#\")\n",
    "\n",
    "if st.session_state.sb_query_history_range != \"Choose a Date Range\":\n",
    "    df_query_history_range = pd.DataFrame(session.sql(f\"\"\"\n",
    "                                                SELECT * \n",
    "                                                FROM (\n",
    "                                                    SELECT\n",
    "                                                        MAX(QUERY_ID) QUERY_ID\n",
    "                                                        ,QUERY_TEXT\n",
    "                                                        ,COUNT(QUERY_TEXT) NUM_OF_EXECUTIONS\n",
    "                                                        ,MIN(START_TIME) FIRST_EXECUTION\n",
    "                                                        ,MAX(END_TIME) LATEST_EXECUTION\n",
    "                                                        ,ROUND(TIMEDIFF('minute', MIN(START_TIME), MAX(START_TIME)) / (NUM_OF_EXECUTIONS-1), 0) AVG_SCHEDULE_MINS\n",
    "                                                        ,MAX(QUERY_TYPE) QUERY_TYPE\n",
    "                                                        ,USER_NAME\n",
    "                                                        ,MAX(WAREHOUSE_NAME) WAREHOUSE_NAME\n",
    "                                                        ,MAX(WAREHOUSE_SIZE) WAREHOUSE_SIZE\n",
    "                                                        ,MAX(WAREHOUSE_TYPE) WAREHOUSE_TYPE\n",
    "                                                        ,MAX(CLUSTER_NUMBER) CLUSTER_NUMBER\n",
    "                                                    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY\n",
    "                                                    WHERE\n",
    "                                                        (QUERY_TEXT ILIKE 'create%table%as%'\n",
    "                                                        OR QUERY_TEXT ILIKE 'insert%overwrite%into%')\n",
    "                                                        AND LOWER(EXECUTION_STATUS) = 'success'\n",
    "                                                        AND LOWER(USER_NAME) <> 'system'\n",
    "                                                        AND END_TIME > DATEADD({date_time_part}, -{increment}, CURRENT_TIMESTAMP())\n",
    "                                                    GROUP BY\n",
    "                                                        QUERY_TEXT\n",
    "                                                        ,USER_NAME\n",
    "                                                ) qh\n",
    "                                                WHERE qh.NUM_OF_EXECUTIONS > 1\n",
    "                                                ;\n",
    "                                                \"\"\").collect())\n",
    "\n",
    "    \n",
    "    #dynamically set data_editor height, based on number of rows in data frame\n",
    "    df_query_history_range_height = int((len(df_query_history_range) + 1.5) * 35 + 3.5)\n",
    "\n",
    "    #preview dataframe\n",
    "    st.write(\"\")\n",
    "    st.subheader(f\"Latest Non-Task commands ({st.session_state.sb_query_history_range})\")\n",
    "    st.dataframe(df_query_history_range, hide_index=True, height=df_query_history_range_height, use_container_width=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9076bf89-97c5-411a-af8e-6d39e9233a22",
   "metadata": {
    "collapsed": false,
    "name": "Step_5_Label"
   },
   "source": [
    "## STEP 5: Get command details\n",
    "\n",
    "This step: compiles a list of commands eligible to be converted to dynamic tables, along with whether the target table is included in a data share."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "edf627ae-b4be-46b0-8481-8e0008784844",
   "metadata": {
    "codeCollapsed": false,
    "collapsed": false,
    "language": "python",
    "name": "Step_5_Cmd_Details"
   },
   "outputs": [],
   "source": [
    "#check each command\n",
    "list_eligible_cmds = []\n",
    "list_ineligible_cmds = []\n",
    "\n",
    "if not df_query_history_range.empty:\n",
    "    for index, row in df_query_history_range.iterrows():\n",
    "        db = \"\"\n",
    "        sch = \"\"\n",
    "        tbl = \"\"\n",
    "    \n",
    "        source_select = \"\"\n",
    "    \n",
    "        query_id = row[\"QUERY_ID\"]\n",
    "        query_text = sqlparse.format(row[\"QUERY_TEXT\"], reindent=True, keyword_case=\"upper\")\n",
    "        schedule = 1 if row[\"AVG_SCHEDULE_MINS\"] < 1 else row[\"AVG_SCHEDULE_MINS\"]\n",
    "        query_type = \"CTAS\" if row[\"QUERY_TYPE\"].lower() == 'create_table_as_select' else \"INSERT_OVERWRITE\"\n",
    "        warehouse = row[\"WAREHOUSE_NAME\"]\n",
    "    \n",
    "        #use get_query_operator_stats to get target table name\n",
    "        df_target_table_full = pd.DataFrame(session.sql(f\"\"\"SELECT\n",
    "                                                            OPERATOR_ATTRIBUTES:table_name::varchar TARGET_TABLE\n",
    "                                                            ,OPERATOR_ATTRIBUTES:table_names[0]::varchar TARGET_TABLES\n",
    "                                                        FROM TABLE(GET_QUERY_OPERATOR_STATS('{query_id}')) \n",
    "                                                        WHERE LOWER(OPERATOR_TYPE) IN('insert', 'createtableasselect')\n",
    "                                                        ;\"\"\").collect())\n",
    "            \n",
    "        target_table_full = df_target_table_full.iloc[0,0] if df_target_table_full.iloc[0,0] else df_target_table_full.iloc[0,1]\n",
    "    \n",
    "        if len(target_table_full.split(\".\")) == 4:\n",
    "            acct = target_table_full.split(\".\")[0]\n",
    "            db = target_table_full.split(\".\")[1]\n",
    "            sch = target_table_full.split(\".\")[2]\n",
    "            tbl = target_table_full.split(\".\")[3]\n",
    "    \n",
    "            target_table = f\"{db}.{sch}.{tbl}\"\n",
    "            \n",
    "        if len(target_table_full.split(\".\")) == 3:\n",
    "            target_table = target_table_full\n",
    "    \n",
    "        #set share flag whether target is in a share:\n",
    "        share_details = {}\n",
    "        flag_target_shared = \"Y\" if target_table in list_objs else \"N\"\n",
    "    \n",
    "        share_details.update({\"target_shared\" : f\"{flag_target_shared}\"})\n",
    "    \n",
    "        if flag_target_shared == \"Y\":\n",
    "            shares_target = []\n",
    "    \n",
    "            df_shared_objs_filtered = df_shared_objs.query(f\"\"\"object == '{target_table}'\"\"\")\n",
    "    \n",
    "            for index, row in df_shared_objs_filtered.iterrows():\n",
    "                share_details.update({\"object\" : f\"\"\"{row[\"object\"]}\"\"\"})\n",
    "                share_details.update({\"object_type\" : f\"\"\"{row[\"object_type\"]}\"\"\"})\n",
    "                shares_target.append(row[\"share\"])\n",
    "                \n",
    "            share_details.update({\"shares\" : shares_target})\n",
    "    \n",
    "        #create dynamic table DDL prefix        \n",
    "        create_dt_ddl = f\"\"\"CREATE OR REPLACE DYNAMIC TABLE {target_table}_DT\n",
    "                            TARGET_LAG = '{schedule} MINUTES'\n",
    "                            WAREHOUSE = {warehouse}\n",
    "                            COMMENT = '{{\"origin\":\"sf_sit\",\"name\":\"dt_conversion_non_task\",\"version\":{{\"major\":1, \"minor\":0}},\"attributes\":{{\"source\":\"command\", \"type\":\"{query_type}\"}}}}'\n",
    "                            AS\n",
    "                            \"\"\"\n",
    "        \n",
    "        #check if query selects from a base object\n",
    "        if re.search(r\"(?s)(?=SELECT)(.*?\\s+FROM.*)\", query_text):\n",
    "            #get source select statement\n",
    "            source_select = re.search(r\"(?s)(?=SELECT)(.*?\\s+FROM.*)\", query_text).group(1)\n",
    "        \n",
    "            #append source select statement to DT DDL \n",
    "            create_dt_ddl += source_select\n",
    "\n",
    "            #check if create DT statement is valid using EXPLAIN\n",
    "            try:\n",
    "                explain_dt_statement = pd.DataFrame(session.sql(f\"\"\"EXPLAIN USING JSON {create_dt_ddl}\"\"\").collect()).iloc[0,0]\n",
    "            except Exception as e:\n",
    "                reason = str(e)\n",
    "\n",
    "                #add command to ineligible list\n",
    "                list_ineligible_cmds.append([query_type, query_text, reason])\n",
    "            else:\n",
    "                #add command to eligible list\n",
    "                list_eligible_cmds.append([False, query_type, json.dumps([share_details], indent=2), query_text, create_dt_ddl])\n",
    "        else:\n",
    "            reason = \"This command does not select from a base object.\"\n",
    "            #add command to ineligible list\n",
    "            list_ineligible_cmds.append([query_type, query_text, reason])\n",
    "\n",
    "    \n",
    "    st.write(\"\")\n",
    "    st.subheader(\"Ineligible Commands:\")\n",
    "    st.write(\"The following command(s) cannot be converted to Dynamic Tables\")\n",
    "    \n",
    "    #create a dataframe from list_ineligible_cmds\n",
    "    df_inelibible_cmd_clmns = ['Command Type'\n",
    "                                 ,'Command DDL'\n",
    "                                 ,'Reason'\n",
    "                                ]\n",
    "    \n",
    "    df_inelibible_cmd = pd.DataFrame(list_ineligible_cmds, columns = df_inelibible_cmd_clmns)\n",
    "    \n",
    "    #dynamically set data_editor height, based on number of rows in data frame\n",
    "    de_inelibible_cmd_height = int((len(df_inelibible_cmd) + 1.5) * 35 + 3.5)\n",
    "    \n",
    "    de_inelibible_cmd = st.dataframe(\n",
    "        df_inelibible_cmd\n",
    "        ,height=de_inelibible_cmd_height\n",
    "        ,hide_index=True\n",
    "        ,use_container_width=True\n",
    "    )\n",
    "    \n",
    "    st.write(\"#\")\n",
    "    st.subheader(\"Eligible Commands:\")\n",
    "    st.write(\"Please choose scheduled command(s) to convert, using the `Convert` checkbox.  Any command selected will be converted in Step 6.\")\n",
    "    \n",
    "    #create a dataframe from list_eligible_cmds\n",
    "    df_convert_cmd_clmns = ['Convert'\n",
    "                             ,'Command Type'\n",
    "                             ,'Shared Objects'\n",
    "                             ,'Command DDL'\n",
    "                             ,'Dynamic Table DDL'\n",
    "                            ]\n",
    "    \n",
    "    df_convert_cmd = pd.DataFrame(list_eligible_cmds, columns = df_convert_cmd_clmns)\n",
    "    \n",
    "    #dynamically set data_editor height, based on number of rows in data frame\n",
    "    de_convert_cmd_height = int((len(df_convert_cmd) + 1.5) * 35 + 3.5)\n",
    "    \n",
    "    de_convert_cmd = st.data_editor(\n",
    "        df_convert_cmd\n",
    "        ,height=de_convert_cmd_height\n",
    "        ,disabled=('Command Type','Shared Objects','Command DDL','Dynamic Table DDL')\n",
    "        ,hide_index=True\n",
    "        ,use_container_width=True\n",
    "        ,num_rows=\"fixed\"\n",
    "    )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e8543d0-097a-46c8-b254-1a8260e1d934",
   "metadata": {
    "collapsed": false,
    "name": "Step_6_Label"
   },
   "source": [
    "## STEP 6: Convert commands (optional)\n",
    "\n",
    "This step converts the chosen commands from STEP 5 to Dynamic Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3292a590-caed-4447-ac7f-57957264586f",
   "metadata": {
    "codeCollapsed": false,
    "collapsed": false,
    "language": "python",
    "name": "Step_6_Convert_Cmds"
   },
   "outputs": [],
   "source": [
    "df_selected_cmds = de_convert_cmd.query('Convert == True')\n",
    "\n",
    "flag_disable_convert_btn = True\n",
    "\n",
    "if True in set(de_convert_cmd['Convert']):\n",
    "    flag_disable_convert_btn = False\n",
    "\n",
    "btn_convert = st.button(\"Convert\", disabled=flag_disable_convert_btn, type=\"primary\")\n",
    "\n",
    "if btn_convert:\n",
    "    for index, row in df_selected_cmds.iterrows():\n",
    "        ddl = row[\"Dynamic Table DDL\"]\n",
    "\n",
    "        for stmt in ddl.rstrip(';').split(\";\"):\n",
    "            #get dt table name\n",
    "            dt =  re.search(r\"(?<=CREATE OR REPLACE DYNAMIC TABLE\\s)(.*?)(?=\\s+TARGET_LAG)\", stmt).group(1)\n",
    "            \n",
    "            #create dynamic table(s)\n",
    "            session.sql(f\"\"\"{stmt}\"\"\").collect()\n",
    "            st.success(f\"Dynamic Table: {dt} successfully created 🎉\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "15270dc1-03ff-4c5f-99bb-e021b4216281",
   "metadata": {
    "collapsed": false,
    "name": "Step_7_Label"
   },
   "source": [
    "## STEP 7: Cleanup (optional)\n",
    "\n",
    "This step can perform the following:\n",
    "- removes target table(s) from any shares\n",
    "- drops the target table(s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "82a7206a-11e7-4785-8672-747a03bb2a03",
   "metadata": {
    "collapsed": false,
    "language": "python",
    "name": "Step_7_Cleanup"
   },
   "outputs": [],
   "source": [
    "flag_disable_cleanup_btn = True\n",
    "flag_disable_checkbox = True\n",
    "\n",
    "if (True in set(de_convert_cmd['Convert'])):\n",
    "    flag_disable_checkbox = False\n",
    "    \n",
    "st.write(\"\")\n",
    "st.checkbox(\"Remove target table(s) from shares\", key=\"cb_remove_from_share\", disabled=flag_disable_checkbox)\n",
    "st.checkbox(\"Drop target table(s)\", key=\"cb_drop_target_table\", disabled=flag_disable_checkbox)\n",
    "\n",
    "if (True in set(de_convert_cmd['Convert'])) and (st.session_state[\"cb_remove_from_share\"] or st.session_state[\"cb_drop_target_table\"]):\n",
    "    flag_disable_cleanup_btn = False\n",
    "\n",
    "btn_cleanup = st.button(\"Cleanup\", disabled=flag_disable_cleanup_btn, type=\"primary\")\n",
    "\n",
    "if btn_cleanup:\n",
    "    list_tbls_drop = []\n",
    "    for index, row in df_selected_cmds.iterrows():\n",
    "        shared_objs = json.loads(row[\"Shared Objects\"])\n",
    "        command_type = row[\"Command Type\"]\n",
    "        command_ddl = row[\"Command DDL\"]\n",
    "\n",
    "        if command_type == \"CTAS\":\n",
    "            tbl = re.search(r\"(?<=CREATE OR REPLACE TABLE\\s)(.*?)(?=\\s+AS)\", command_ddl).group(1)\n",
    "        if command_type == \"INSERT_OVERWRITE\":\n",
    "            tbl = re.search(r\"(?<=INSERT OVERWRITE INTO\\s)(.*?)(?=\\s+SELECT)\", command_ddl).group(1)\n",
    "        \n",
    "        if st.session_state[\"cb_remove_from_share\"]:\n",
    "            #REMOVE FROM SHARE(S)\n",
    "            for dict_obj in shared_objs:\n",
    "                flag_shared = dict_obj[\"target_shared\"]\n",
    "    \n",
    "                if flag_shared.lower() == 'y':\n",
    "                    obj = dict_obj[\"object\"]\n",
    "                    obj_type = dict_obj[\"object_type\"]\n",
    "                    list_share = dict_obj[\"shares\"]\n",
    "    \n",
    "                    #add obj/type to list\n",
    "                    list_tbls_drop.append([obj, obj_type])\n",
    "    \n",
    "                    for share in list_share:\n",
    "                        #remove obj from share\n",
    "                        session.sql(f\"\"\"REVOKE SELECT ON {obj_type} {obj} FROM SHARE {share}\"\"\").collect()\n",
    "                        st.success(f\"{obj_type} {obj} successfully removed from share: {share}. 🎉\")\n",
    "                else:    \n",
    "                    st.warning(f\"Table: {tbl} is not currently shared\", icon=\"⚠️\")\n",
    "\n",
    "        \n",
    "        if st.session_state[\"cb_drop_target_table\"]:\n",
    "            session.sql(f\"\"\"DROP TABLE IF EXISTS {tbl}\"\"\").collect()\n",
    "            st.success(f\"Table: {tbl} successfully dropped. 🎉\")\n",
    "\n",
    "        st.divider()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Streamlit Notebook",
   "name": "streamlit"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}