Skip to main content

Snowflake procedure looping issue with value being incremented implicitly

I am working on a procedure in snowflake (standard edition) that parses query_text from "SNOWFLAKE"."ACCOUNT_USAGE"."QUERY_HISTORY" to identify any tables that are accessed. Since we're on standard edition, we don't have access to the ACCESS_HISTORY table, which appears to only be populated in enterprise edition.

I'm using regexp_cnt to identify how many instances of from's and join's exist in the query text and then attempting to loop through each instance to capture the table it hits. Somehow, it is incrementing my variable that I am setting with the regexp_cnt along with the counter that I am setting for looping. I'm not incrementing the regexp_cnt variables anywhere.

Procedure is a bit lengthy so I will supply the key components here:

This is where I'm setting my from and join count values

// get counts of from and join clauses
    var get_query_history_loops_sql = `create or replace temporary table tmp_query_hist_clean_counts as select query_id, user_name, start_time, query_text, clean_query_text, regexp_count(clean_query_text, 'from ', 1, 'i') as f_count, regexp_count(clean_query_text, 'join ', 1, 'i') as j_count from tmp_query_hist_clean where f_count > 0;`;

This query has two instances each of from and join in the query_text.

Results: result of f_count and j_count

The resulting counts are being set to variables v_from_count and v_join_count

This is the section where I'm trying to loop through the instances to pick up the resulting table

// loop through from counts and join counts to retrieve table information 
    var get_from_join_cnt_sql = `select query_id, user_name, start_time, query_text, clean_query_text, f_count, j_count from tmp_query_hist_clean_counts order by query_id;`

    try{
        processes=processes+"call get_from_join_cnt_sql to select query information to loop through for from cnt and join cnt / "
        stmt = snowflake.createStatement(
        {sqlText: get_from_join_cnt_sql}); 
        from_join_cnt_result = stmt.execute();
        
        var while_counter=0;
        while (from_join_cnt_result.next()){
            v_from_count = from_join_cnt_result.getColumnValue('F_COUNT');
            v_join_count = from_join_cnt_result.getColumnValue('J_COUNT');
            v_q_id = from_join_cnt_result.getColumnValue('QUERY_ID');

            if (v_from_count !== 0) {
                v_from_counter = 1;
//              for (let v_from_counter=1; v_from_counter < v_from_count+1; v_from_counter++) {
                while (v_from_counter < v_from_count+1) {
                get_from_loop_table_name_values_sql=`insert into tmp_tbl_query_hist_loops (query_id, user_name, start_time, query_text, clean_query_text, table_name, table_name_split_0, table_name_split_1, table_name_split_2, table_name_split_3, qualified_table_name, table_catalog, table_schema, from_count, from_counter, join_count, join_counter) 

                select c.query_id, c.user_name, c.start_time, c.query_text, c.clean_query_text, c.table_name, c.table_name_split_0, c.table_name_split_1, c.table_name_split_2, c.table_name_split_3, c.qualified_table_name, c.table_catalog, c.table_schema, `+v_from_count+` as from_count, c.from_counter, `+v_join_count+` as join_count, c.join_counter
                from 
                    (select b.query_id, b.user_name, b.start_time, b.query_text, b.clean_query_text, b.table_name, b.from_counter, b.join_counter
                    , b.table_name_split_0, b.table_name_split_1, b.table_name_split_2, b.table_name_split_3
                    , case when b.table_name_split_3 <> '' then b.table_name_split_3 
                            when b.table_name_split_1 <> '' and b.table_name_split_2 <> '' and b.table_name_split_3 = '' 
                            then b.table_name_split_2 
                            else b.table_name end as qualified_table_name
                    , case when b.table_name_split_3 <> '' then b.table_name_split_1 else null end as table_catalog 
                    , case when b.table_name_split_1 <> '' and b.table_name_split_2 <> '' and b.table_name_split_3 <> '' then b.table_name_split_2 
                            when b.table_name_split_1 <> '' and b.table_name_split_2 <> '' and b.table_name_split_3 = '' then b.table_name_split_1 
                            else null end as table_schema 
                    from 
                        (select a.query_id, a.user_name, a.start_time, a.query_text, a.clean_query_text, a.table_name, a.from_counter, a.join_counter
                        , split_part(a.table_name, '.', 0) as table_name_split_0
                        , split_part(a.table_name, '.', 1) as table_name_split_1
                        , split_part(a.table_name, '.', 2) as table_name_split_2
                        , split_part(a.table_name, '.', 3) as table_name_split_3
                        from 
                            (select query_id, user_name, start_time, query_text, clean_query_text, REGEXP_SUBSTR(clean_query_text, ' from ([^\\\\ ]*)', 1, `+v_from_counter+`, 'ie', 1) as table_name, `+v_from_counter+` as from_counter, 0 as join_counter from tmp_query_hist_clean) a ) b) c
                        where c.qualified_table_name in (select table_name from tmp_tbl_vw_list group by table_name) and c.qualified_table_name <> '' group by c.query_id, c.user_name, c.start_time, c.query_text, c.clean_query_text, c.table_name, c.table_name_split_0, c.table_name_split_1, c.table_name_split_2, c.table_name_split_3, c.qualified_table_name, c.table_catalog, c.table_schema, c.from_counter, c.join_counter;`;

                    try{
                        processes=processes+"call get_from_loop_table_name_values_sql to insert table name data - query_id: "+v_q_id+"; from_count: "+v_from_count+"; join_count: "+v_join_count+"; v_from_counter: "+v_from_counter+"; while_counter: "+while_counter+" / "
                        stmt = snowflake.createStatement(
                        {sqlText: get_from_loop_table_name_values_sql}); 
                        from_loop_table_name_values_result = stmt.execute();
                        from_loop_table_name_values_result.next();
                        }

                    catch (err)  {
                        flg_build_sql="fail";
                        status="F";
                        errmsg_build_sql =  "Failed: Code: " + err.code + "\\n  State: " + err.state;
                        errmsg_build_sql += "\\n  Message: " + err.message;
                        errmsg_build_sql += "\\nStack Trace:\\n" + err.stackTraceTxt; 
                        processes=processes+" ERROR: call get_table_name_values_sql to insert table name data - "+v_q_id+" / - "+errmsg_build_sql+" / "
                    }   
                    v_from_counter++;
                } // from_count loop 
                if (v_join_count > 0) {
                    v_join_counter = 1;
//                  for (let v_join_counter=1; v_join_counter < v_join_count+1; v_join_counter++) {
                    while (v_join_counter < v_join_count+1) {
                            get_join_loop_table_name_values_sql=`insert into tmp_tbl_query_hist_loops (query_id, user_name, start_time, query_text, clean_query_text, table_name, table_name_split_0, table_name_split_1, table_name_split_2, table_name_split_3, qualified_table_name, table_catalog, table_schema, from_count, from_counter, join_count, join_counter) 
                            select c.query_id, c.user_name, c.start_time, c.query_text, c.clean_query_text, c.table_name, c.table_name_split_0, c.table_name_split_1, c.table_name_split_2, c.table_name_split_3, c.qualified_table_name, c.table_catalog, c.table_schema, `+v_from_count+` as from_count, c.from_counter, `+v_join_count+` as join_count, c.join_counter
                            from 
                            (select b.query_id, b.user_name, b.start_time, b.query_text, b.clean_query_text, b.table_name, b.from_counter, b.join_counter
                            , b.table_name_split_0, b.table_name_split_1, b.table_name_split_2, b.table_name_split_3
                            , case when b.table_name_split_3 <> '' then b.table_name_split_3 
                                    when b.table_name_split_1 <> '' and b.table_name_split_2 <> '' and b.table_name_split_3 = '' 
                                    then b.table_name_split_2 
                                    else b.table_name end as qualified_table_name
                            , case when b.table_name_split_3 <> '' then b.table_name_split_1 else null end as table_catalog 
                            , case when b.table_name_split_1 <> '' and b.table_name_split_2 <> '' and b.table_name_split_3 <> '' then b.table_name_split_2 
                                    when b.table_name_split_1 <> '' and b.table_name_split_2 <> '' and b.table_name_split_3 = '' then b.table_name_split_1 
                                    else null end as table_schema 
                            from 
                            
                            (select a.query_id, a.user_name, a.start_time, a.query_text, a.clean_query_text, a.table_name, a.from_counter, a.join_counter
                            , split_part(a.table_name, '.', 0) as table_name_split_0
                            , split_part(a.table_name, '.', 1) as table_name_split_1
                            , split_part(a.table_name, '.', 2) as table_name_split_2
                            , split_part(a.table_name, '.', 3) as table_name_split_3
                            from 
                            (select query_id, user_name, start_time, query_text, clean_query_text, REGEXP_SUBSTR(clean_query_text, ' join ([^\\\\ ]*)', 1, `+v_join_counter+`, 'ie', 1) as table_name, 0 as from_counter, `+v_join_counter+` as join_counter from tmp_query_hist_clean) a ) b) c
                            
                            where c.qualified_table_name in (select table_name from tmp_tbl_vw_list group by table_name) and c.qualified_table_name <> '' group by c.query_id, c.user_name, c.start_time, c.query_text, c.clean_query_text, c.table_name, c.table_name_split_0, c.table_name_split_1, c.table_name_split_2, c.table_name_split_3, c.qualified_table_name, c.table_catalog, c.table_schema, c.from_counter, c.join_counter;`;

                        try{
                            processes=processes+"call get_join_loop_table_name_values_sql to insert table name data - "+v_q_id+"; join_count: "+v_join_count+"; v_join_counter: "+v_join_counter+"; while_counter: "+while_counter+" / "
                            stmt = snowflake.createStatement(
                            {sqlText: get_join_loop_table_name_values_sql}); 
                            join_loop_table_name_values_result = stmt.execute();
                            join_loop_table_name_values_result.next();
                            }

                        catch (err)  {
                            flg_build_sql="fail";
                            status="F";
                            errmsg_build_sql =  "Failed: Code: " + err.code + "\\n  State: " + err.state;
                            errmsg_build_sql += "\\n  Message: " + err.message;
                            errmsg_build_sql += "\\nStack Trace:\\n" + err.stackTraceTxt; 
                            processes=processes+" ERROR: call get_join_loop_table_name_values_sql to insert table name data - "+v_q_id+" / - "+errmsg_build_sql+" / "
                        }   
                        v_join_counter++;
                    } // join_count loop
                } // join_count > 0
            } // v_from_count > 0
            else {processes=processes+"No tables present / "}
            processes = processes+" while_counter: "+while_counter+" / ";
        } // while statement

Here's an example of a result I'm seeing: results

Via Active questions tagged javascript - Stack Overflow https://ift.tt/BpEImlW

Labels

Labels:

Comments

Post a Comment

Popular posts from this blog

ValueError: X has 10 features, but LinearRegression is expecting 1 features as input

So, I am trying to predict the model but its throwing error like it has 10 features but it expacts only 1. So I am confused can anyone help me with it? more importantly its not working for me when my friend runs it. It works perfectly fine dose anyone know the reason about it? cv = KFold(n_splits = 10) all_loss = [] for i in range(9): # 1st for loop over polynomial orders poly_order = i X_train = make_polynomial(x, poly_order) loss_at_order = [] # initiate a set to collect loss for CV for train_index, test_index in cv.split(X_train): print('TRAIN:', train_index, 'TEST:', test_index) X_train_cv, X_test_cv = X_train[train_index], X_test[test_index] t_train_cv, t_test_cv = t[train_index], t[test_index] reg.fit(X_train_cv, t_train_cv) loss_at_order.append(np.mean((t_test_cv - reg.predict(X_test_cv))**2)) # collect loss at fold all_loss.append(np.mean(loss_at_order)) # collect loss at order plt.plot(np.log(al...
Post a Comment
Read more

Sorting large arrays of big numeric stings

I was solving bigSorting() problem from hackerrank: Consider an array of numeric strings where each string is a positive number with anywhere from to digits. Sort the array's elements in non-decreasing, or ascending order of their integer values and return the sorted array. I know it works as follows: def bigSorting(unsorted): return sorted(unsorted, key=int) But I didnt guess this approach earlier. Initially I tried below: def bigSorting(unsorted): int_unsorted = [int(i) for i in unsorted] int_sorted = sorted(int_unsorted) return [str(i) for i in int_sorted] However, for some of the test cases, it was showing time limit exceeded. Why is it so? PS: I dont know exactly what those test cases were as hacker rank does not reveal all test cases. source https://stackoverflow.com/questions/73007397/sorting-large-arrays-of-big-numeric-stings
Post a Comment
Read more

How to load Javascript with imported modules?

I am trying to import modules from tensorflowjs, and below is my code. test.html <!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Document</title </head> <body> <script src="https://cdn.jsdelivr.net/npm/@tensorflow/tfjs@2.0.0/dist/tf.min.js"></script> <script type="module" src="./test.js"></script> </body> </html> test.js import * as tf from "./node_modules/@tensorflow/tfjs"; import {loadGraphModel} from "./node_modules/@tensorflow/tfjs-converter"; const MODEL_URL = './model.json'; const model = await loadGraphModel(MODEL_URL); const cat = document.getElementById('cat'); model.execute(tf.browser.fromPixels(cat)); Besides, I run the server using python -m http.server in my command prompt(Windows 10), and this is the error prompt in the console log of my browser: Failed to loa...
Post a Comment
Read more