def genNetworkPolicyDDL(ofile, cursor): cursor.execute( " select name, comment,entries_in_allowed_ip_list, entries_in_blocked_ip_list from " + crossrep.tb_np + " order by name") rec = cursor.fetchall() ipDic = {} for r in rec: name = r[0] comment = r[1] entries_in_allowed_ip_list = r[2] entries_in_blocked_ip_list = r[3] if name.isdigit() == True: continue crSQL = "CREATE NETWORK POLICY IF NOT EXISTS \"" + name + "\"" if entries_in_allowed_ip_list != 0 or entries_in_blocked_ip_list != 0: ipDic = descNetworkPolicy(name, cursor) if entries_in_allowed_ip_list != 0: allowed_ip_list = ipDic["ALLOWED_IP_LIST"] crSQL = crSQL + " allowed_ip_list = (" + allowed_ip_list + ")" if entries_in_blocked_ip_list != 0: blocked_ip_list = ipDic["BLOCKED_IP_LIST"] crSQL = crSQL + " blocked_ip_list = (" + blocked_ip_list + ")" if not crossrep.isBlank(comment): crSQL = crSQL + ' comment = "' + comment + '"' ofile.write(crSQL + ';\n')
def grantFutureObj( ofile, cursor): gquery = ( "select distinct object_name, object_type, priv, grantee_name, grant_option from " + crossrep.tb_fgrant + " where priv in ('OWNERSHIP','SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'USAGE', 'READ', 'WRITE')" " and object_type in ('TABLE','VIEW', 'STAGE', 'FILE_FORMAT', 'FUNCTION', 'PROCEDURE', 'SEQUENCE')" " order by object_name, object_type ") if crossrep.verbose==True: print(gquery) cursor.execute(gquery) row_set = cursor.fetchall() for row in row_set: obj_name = row[0].split('.') obj_type = row[1] priv = row[2] role = row[3] grant_option = row[4] db = obj_name[0] sc = obj_name[1] with_grant = '' if not crossrep.isBlank(grant_option): if grant_option == 'true': with_grant = ' WITH GRANT OPTION' if priv == 'OWNERSHIP': grantSQL = ( "GRANT ALL ON FUTURE "+ obj_type + "S IN SCHEMA " + db + "." + sc + " TO ROLE " + role + with_grant ) ofile.write(grantSQL+';\n') #print(grantSQL) else: if obj_type == 'FILE_FORMAT': grantSQL = ( "GRANT " + priv + " ON FUTURE FILE FORMATS IN SCHEMA " + db + "." + sc + " TO ROLE " + role + with_grant ) else: grantSQL = ( "GRANT " + priv + " ON FUTURE "+ obj_type + "S IN SCHEMA " + db + "." + sc + " TO ROLE " + role + with_grant ) ofile.write(grantSQL+';\n') #print(grantSQL) if crossrep.verbose==True: print(grantSQL)
def RMtrigger(percent, action): trigger = '' if not crossrep.isBlank(percent): for p in percent.split(','): p = re.sub(r'%', '', p) trigger = trigger + " ON " + p + " PERCENT DO " + action return trigger
def genPipeDDLByDBList(dblist, ofile, cursor): inlist = crossrep.genInList(dblist) if inlist != '': inPred = "and database_name " + inlist else: inPred = '' query = ( "select distinct database_name, schema_name, name, definition, comment, owner from " + crossrep.tb_pipe + " where owner is not null " + inPred + " order by database_name, schema_name, name ") print("Pipe Query:" + query) cursor.execute(query) rec = cursor.fetchall() for r in rec: dbname = r[0] scname = r[1] pipename = r[2] definition = r[3] comment = r[4] if not comment: comment = '' owner = r[5] if pipename.isdigit() == True: continue crSQL = 'CREATE PIPE IF NOT EXISTS "' + dbname + '"."' + scname + '"."' + pipename + '" ' if crossrep.isBlank(comment) == False: crSQL = crSQL + " COMMENT = '" + comment + "' " crSQL = crSQL + ' AS ' + definition ofile.write(crSQL + ';\n') if crossrep.verbose == True: print(crSQL)
def grantPrivsByDatabase( dbname, ofile, cursor): ## query to get owner role and granted role for certain object type, object name, and privilege ## for grant privileges, only need to grant on the role that's different from its owner role if crossrep.verbose == True: print('starting grant privilege on database: ' + dbname + ' ...') if crossrep.isBlank(dbname): return inPredicate = ( " and ( (object_type = 'DATABASE' and object_name = '"+ dbname + "') or ( object_type != 'DATABASE' and object_name like '" + dbname + ".%' ) )") privquery = ("select p1.object_type, p1.object_name, p1.priv, p1.role , p2.role as ownerrole " + " from (" + " select distinct object_type, object_name, role, priv from privileges where priv != 'OWNERSHIP' " + inPredicate + " and object_type not in ('ACCOUNT','MANAGED_ACCOUNT','ROLE','USER','SHARE','WAREHOUSE','NETWORK_POLICY','RESOURCE_MONITOR', 'STAGE','PIPE', 'NOTIFICATION_SUBSCRIPTION') ) p1" + " join (" + " select distinct object_type, object_name, role from privileges " + " where priv = 'OWNERSHIP' " + inPredicate + " and object_type not in ('ACCOUNT','MANAGED_ACCOUNT','ROLE','USER','SHARE','WAREHOUSE','NETWORK_POLICY','RESOURCE_MONITOR', 'STAGE', 'PIPE', 'NOTIFICATION_SUBSCRIPTION')" + " ) p2 on p1.object_type = p2.object_type" + " and p1.object_name = p2.object_name" + " where p1.role != p2.role" + " order by p1.object_name,p1.object_type,p1.priv,p1.role" ) if crossrep.verbose == True: print(privquery) cursor.execute(privquery) record = cursor.fetchall() for row in record: object_type = row[0] object_name = row[1] priv = row[2] role = row[3] ownerRole = row[4] command_object_type = object_type command_object_name = object_name if crossrep.verbose == True: print(object_type, object_name, ownerRole) print(command_object_type, command_object_name) if object_type == 'FUNCTION' or object_type == 'PROCEDURE' : command_object_name = quoteIdentifier(object_name,True) if crossrep.verbose == True: print('function: '+ object_name + '; ' +command_object_name ) ''' elif object_type == 'NOTIFICATION_SUBSCRIPTION' : continue ''' else: command_object_name = quoteIdentifier(object_name,False) command_object_type = objTypeHandling(object_type) grantSQL = 'GRANT ' + priv + ' ON '+ command_object_type + ' ' + command_object_name + ' TO ROLE ' + role ofile.write(grantSQL+';\n') if crossrep.verbose == True: print(grantSQL) if crossrep.verbose == True: print('Finish grant privilege on database: ' + dbname + ' ... \n')
def genAllStageDDL(ofile, cursor): #query = ("select distinct stage_catalog, stage_schema, stage_name, stage_url, region, type, comment, owner from "+tbstage + #" where owner is not null order by stage_catalog, stage_schema, stage_name ") if crossrep.mode == 'SNOWFLAKE': query = ( "select distinct stage_catalog, stage_schema, stage_name, stage_url, stage_region, stage_type, comment, stage_owner from " + crossrep.tb_stage + " where stage_owner is not null order by stage_catalog, stage_schema, stage_name " ) elif crossrep.mode == 'CUSTOMER': query = ( "select distinct stage_catalog, stage_schema, stage_name, stage_url, stage_region, stage_type, comment, stage_owner from snowflake.account_usage.stages " + " where stage_owner is not null and deleted is null order by stage_catalog, stage_schema, stage_name " ) if crossrep.verbose == True: print("stageQuery:" + query) cursor.execute(query) rec = cursor.fetchall() for r in rec: stage_catalog = r[0] stage_schema = r[1] stage_name = r[2] stage_url = r[3] region = r[4] type = r[5] comment = r[6] owner = r[7] if not stage_url: stage_url = '' if not region: region = '' if not type: type = '' if not comment: comment = '' if crossrep.verbose == True: print("stage_catalog:" + stage_catalog) print("stage_schema:" + stage_schema) print("stage_name:" + stage_name) print("stage_url:" + stage_url) print("region:" + region) print("type:" + type) print("comment:" + comment) print("owner:" + owner) if stage_name.isdigit() == True: continue crSQL = 'CREATE STAGE IF NOT EXISTS "' + stage_catalog + '"."' + stage_schema + '"."' + stage_name + '"' if crossrep.isBlank(stage_url) == False: crSQL = crSQL + " URL = '" + stage_url + "' comment='" + comment + "'" if crossrep.verbose == True: print(crSQL) ofile.write(crSQL + ';\n')
def genStageDDLByDBList(dblist, ofile, cursor): inlist = crossrep.genInList(dblist) if inlist != '': inPred = "and stage_catalog " + inlist else: inPred = '' if crossrep.mode == 'SNOWFLAKE': query = ( "select distinct stage_catalog, stage_schema, stage_name, stage_url, stage_region, stage_type, comment, stage_owner from " + crossrep.tb_stage + " where stage_owner is not null " + inPred + " order by stage_catalog, stage_schema, stage_name ") elif crossrep.mode == 'CUSTOMER': query = ( "select distinct stage_catalog, stage_schema, stage_name, stage_url, stage_region, stage_type, comment, stage_owner from snowflake.account_usage.stages " + " where stage_owner is not null and deleted is null " + inPred + " order by stage_catalog, stage_schema, stage_name ") if crossrep.verbose == True: print("stageQuery:" + query) cursor.execute(query) rec = cursor.fetchall() for r in rec: stage_catalog = r[0] stage_schema = r[1] stage_name = r[2] stage_url = r[3] region = r[4] type = r[5] comment = r[6] if not stage_url: stage_url = '' if not region: region = '' if not type: type = '' if not comment: comment = '' if stage_name.isdigit() == True: continue crSQL = 'CREATE STAGE IF NOT EXISTS "' + stage_catalog + '"."' + stage_schema + '"."' + stage_name + '"' if crossrep.isBlank(stage_url) == False: crSQL = crSQL + " URL = '" + stage_url + "' comment='" + comment + "'" if crossrep.verbose == True: print(crSQL) ofile.write(crSQL + ';\n')
def FKCrossDB(tbddl, tabledb, tablesc, tablename, dropfk_file, addfk_file, tbseq, cursor): isCrossDB = False usedRole = False #print(tbddl) ## constraint D_PATIENT_FK2 foreign key (SOURCE_SYSTEM_ID) references PDX_DEV.EDW.D_SOURCE_SYSTEM(SOURCE_SYSTEM_ID) wlist = re.findall( r'((constraint\s+(\S+)\s+)?foreign\s+key\s*\(\s*(.+?)\)\s*references\s+(\S+?)\s*\((\S+?)\))', tbddl, re.MULTILINE | re.IGNORECASE) #print(wlist) for fk in wlist: #print('fk: ' + fk[0]) #print('ref qualifier: ' + fk[4]) dname = fk[4].split('.')[0] if (dname.startswith("\"")): dname = dname.strip('"') else: dname = dname.upper() #print('ref db: ' + dname + '; table db: ' + tabledb) if dname != tabledb: #print('crossing db') dropfk_file.write('-- table sequence ' + str(tbseq) + '\n') addfk_file.write('-- table sequence ' + str(tbseq) + '\n') if usedRole == False: #print('finding owner role') role = findOwnerRole(tabledb, tablesc, tablename, cursor) dropfk_file.write('-- use role ' + role + ';\n') addfk_file.write('-- use role ' + role + ';\n') usedRole = True if crossrep.isBlank(fk[1]) == True: dropfk_file.write('alter table ' + tabledb + '.' + tablesc + '.' + tablename + ' drop foreign key (' + fk[3] + ');\n') else: dropfk_file.write('alter table ' + tabledb + '.' + tablesc + '.' + tablename + ' drop constraint ' + fk[2] + ';\n') addfk_file.write('alter table ' + tabledb + '.' + tablesc + '.' + tablename + ' add ' + fk[0] + ';\n') #print('referring to db:' + dname) if isCrossDB == False: isCrossDB = True return isCrossDB
def grantOwnerByDatabase(dbname, ofile, cursor): if crossrep.verbose == True: print('starting grant ownership on database: ' + dbname) if crossrep.isBlank(dbname): return inPredicate = ( " and ( (object_type = 'DATABASE' and object_name = '"+ dbname + "') or ( object_type != 'DATABASE' and object_name like '" + dbname + ".%' ) )") query = ( "select distinct object_type, object_name, role from " + crossrep.tb_priv + " where priv = 'OWNERSHIP' and object_type not in ('ACCOUNT','MANAGED_ACCOUNT','ROLE','USER','SHARE', 'WAREHOUSE','NETWORK_POLICY','RESOURCE_MONITOR','STAGE') " + inPredicate + " order by object_name,object_type, role ") if crossrep.verbose == True: print(query) cursor.execute(query) rec = cursor.fetchall() for r in rec: object_type = r[0] object_name = r[1] ownerRole = r[2] if crossrep.verbose == True: print('object_name: '+ object_name + '; object_type: ' + object_type + '; owner role:' + ownerRole ) command_object_name = object_name command_object_type = object_type if object_type == 'NOTIFICATION_SUBSCRIPTION' : continue elif object_type == 'FUNCTION' : #if object_type == 'FUNCTION' and priv == 'USAGE': command_object_name = quoteIdentifier(object_name,True) if crossrep.verbose == True: print('function: '+ object_name + '; ' + command_object_name ) else: command_object_name = quoteIdentifier(object_name,False) command_object_type = objTypeHandling(object_type) grantSQL = 'grant ownership on ' + command_object_type + ' ' + command_object_name + ' TO ROLE ' + ownerRole + ' copy current grants ;' ofile.write(grantSQL + '\n') if crossrep.verbose == True: print(grantSQL) if crossrep.verbose == True: print('Finish grant ownership on database: ' + dbname + ' ... \n')
def genRoleDDL( ofile, cursor): role_name = None comment = None ofile.write("use role securityadmin;\n") query = "select role_name, comments from "+ crossrep.tb_role + " where role_name not in ('ACCOUNTADMIN','SECURITYADMIN','SYSADMIN','PUBLIC') order by role_name " cursor.execute(query) rec = cursor.fetchall() for r in rec: role_name = r[0] if crossrep.hasSpecial(role_name) == True: role_name = "\"" + role_name + "\"" if crossrep.verbose==True: print(' role name: ' + role_name) comment = r[1] if role_name.isdigit()==True: continue croleSQL = "CREATE ROLE IF NOT EXISTS " + role_name if not crossrep.isBlank (comment ): croleSQL = croleSQL + ' comment = "' + comment + '"' ofile.write(croleSQL+';\n')
def alterAllDBs(ofile, pfile, ralist, falist, cursor): # check whether table exists query = ("select distinct DATABASE_NAME from " + crossrep.tb_db) if crossrep.verbose == True: print(query) try: cursor.execute(query) rec = cursor.fetchall() for r in rec: if crossrep.verbose == True: print('db name: ' + r[0]) if crossrep.isBlank(r[0]) == False: ofile.write("alter database \"" + r[0] + "\" enable replication to accounts " + ralist + ";\n") pfile.write("alter database \"" + r[0] + "\" enable failover to accounts " + falist + ";\n") except Exception as err: print( 'An error occurred during alter database to enable replication:' + str(err)) print('\n' + query)
def genResMonitor(ofile, cursor): query = " select name, credit_quota , frequency, start_time, end_time, notify_at, suspend_at, suspend_immediately_at, comment from " + crossrep.tb_rm + " order by name" cursor.execute(query) rec = cursor.fetchall() for r in rec: name = r[0] credit_quota = r[1] frequency = r[2] start_time = r[3] end_time = r[4] notify_at = r[5] suspend_at = r[6] suspend_immediately_at = r[7] comment = r[8] if name.isdigit() == True: continue cquery = " CREATE RESOURCE MONITOR IF NOT EXISTS " + name + " WITH " if crossrep.isBlank(credit_quota) == False: if credit_quota.isdigit() == True: cquery = cquery + " CREDIT_QUOTA=" + credit_quota if crossrep.isBlank(frequency) == True: frequency = 'NEVER' elif frequency not in ['MONTHLY', 'DAILY', 'WEEKLY', 'YEARLY']: frequency = 'NEVER' if crossrep.isBlank(start_time) == True: start_time = 'IMMEDIATELY' ''' else: # not setting timestamp due to diff system start_time = 'IMMEDIATELY' ''' cquery = cquery + " FREQUENCY='" + frequency + "'" + " START_TIMESTAMP='" + start_time + "'" #no end time if crossrep.isBlank(end_time) == False: cquery = cquery + " END_TIMESTAMP='" + end_time + "'" trigger = RMtrigger(notify_at, 'NOTIFY') + RMtrigger( suspend_at, 'SUSPEND') + RMtrigger(suspend_immediately_at, 'SUSPEND_IMMEDIATE') if not crossrep.isBlank(trigger): cquery = cquery + " TRIGGERS " + trigger if not crossrep.isBlank(comment): cquery = cquery + ' comment = "' + comment + '"' ofile.write(cquery + ';\n')
def genUserDDL(options, ofile, cursor): user_name = None login_name = None display_name = None first_name = None last_name = None email = None mins_to_unlock = None days_to_expiry = None comment = None query = ( "select user_name,login_name,display_name,first_name,last_name,email,mins_to_unlock,days_to_expiry,comment, "+ " must_change_password, snowflake_lock, default_warehouse, default_namespace, default_role from " + crossrep.tb_user + " order by user_name ") #" from " + tbusers+ " where disabled = false order by owner, user_name " ofile.write("use role securityadmin;\n") cursor.execute(query) rec = cursor.fetchall() for r in rec: #user_name = quoteID(r[0]) user_name = r[0] login_name = r[1] display_name = r[2] first_name = r[3] last_name = r[4] email = r[5] mins_to_unlock = r[6] days_to_expiry = r[7] comment = r[8] must_change_password = r[9] default_warehouse = r[11] default_namespace = r[12] default_role = r[13] if user_name.isdigit() == True: continue if crossrep.hasSpecial(user_name) == True: user_name = "\"" + user_name + "\"" if crossrep.verbose==True: print(' user name: ' + user_name) cuserSQL = "CREATE USER IF NOT EXISTS " + user_name if not crossrep.isBlank (login_name) : cuserSQL = cuserSQL + " login_name='" + login_name + "'" if not crossrep.isBlank (display_name ): cuserSQL = cuserSQL + " display_name='" + display_name + "'" if not crossrep.isBlank (first_name ): cuserSQL = cuserSQL + " first_name='" + first_name + "'" if not crossrep.isBlank (last_name ): cuserSQL = cuserSQL + " last_name='" + last_name + "'" if not crossrep.isBlank (email ): cuserSQL = cuserSQL + " email='" + email + "'" if not crossrep.isBlank (mins_to_unlock ): cuserSQL = cuserSQL + " mins_to_unlock=" + mins_to_unlock if not crossrep.isBlank (days_to_expiry ): days_to_expiry = str(int(float(days_to_expiry))) cuserSQL = cuserSQL + " days_to_expiry=" + days_to_expiry if not crossrep.isBlank (comment ): cuserSQL = cuserSQL + ' comment=\'' + comment + '\'' if not crossrep.isBlank (default_warehouse ): cuserSQL = cuserSQL + ' default_warehouse=\'' + default_warehouse + '\'' if not crossrep.isBlank (default_namespace ): cuserSQL = cuserSQL + ' default_namespace=\'' + default_namespace + '\'' if not crossrep.isBlank (default_role ): cuserSQL = cuserSQL + ' default_role=\'' + default_role + '\'' if crossrep.verbose == True: print(cuserSQL) if options == 'samepwd': cuserSQL = cuserSQL + " password='******' MUST_CHANGE_PASSWORD=TRUE " elif options == 'randpwd': pwd = crossrep.genPWD() cuserSQL = cuserSQL + " password='******' MUST_CHANGE_PASSWORD=TRUE " ofile.write(cuserSQL+';\n')
def genWarehouseDDL(ofile, cursor): tbname = crossrep.tb_wh query = ( "select name, size, min_cluster_count, max_cluster_count, auto_suspend, auto_resume, comment, resource_monitor, scaling_policy from " + tbname + " order by name ") if crossrep.verbose == True: print(query) cursor.execute(query) rec = cursor.fetchall() for r in rec: name = r[0] size = re.sub(r'-', '', r[1]).upper() if size == '4XLARGE': size = 'X4LARGE' elif size == '3XLARGE': size = 'XXXLARGE' elif size == '2XLARGE': size = 'XXLARGE' min_cluster_count = str(r[2]) max_cluster_count = str(r[3]) auto_suspend = str(r[4]) auto_resume = r[5] comment = r[6] resource_monitor = r[7] scaling_policy = r[8] if name.isdigit() == True: continue if crossrep.isBlank(size) == True: size = 'XLARGE' if crossrep.verbose == True: print(name, size) crSQL = "CREATE WAREHOUSE IF NOT EXISTS " + name + " warehouse_size = " + size if crossrep.isBlank(min_cluster_count) == False: if crossrep.verbose == True: print(min_cluster_count) crSQL = crSQL + " min_cluster_count=" + min_cluster_count if crossrep.verbose == True: print(crSQL) if crossrep.isBlank(max_cluster_count) == False: if crossrep.verbose == True: print(max_cluster_count) crSQL = crSQL + " max_cluster_count=" + max_cluster_count if crossrep.isBlank(auto_suspend) == False: if crossrep.verbose == True: print(auto_suspend) crSQL = crSQL + " auto_suspend = " + auto_suspend if crossrep.isBlank(auto_resume) == False: if crossrep.verbose == True: print(auto_resume) crSQL = crSQL + " auto_resume = " + auto_resume if crossrep.isBlank(resource_monitor) == False: if crossrep.verbose == True: print(resource_monitor) crSQL = crSQL + " resource_monitor = " + resource_monitor if crossrep.isBlank(scaling_policy) == False: if crossrep.verbose == True: print(scaling_policy) crSQL = crSQL + " scaling_policy = " + scaling_policy if crossrep.isBlank(comment) == False: if crossrep.verbose == True: print(comment) crSQL = crSQL + ' comment = "' + comment + '"' ofile.write(crSQL + ';\n') if crossrep.verbose == True: print(crSQL)
def linkGlobalDBsRepGroup(opt, ofile2, ofile3, ofile4, ofile5, ofile6, cursor): if opt == 'all' or crossrep.isBlank(opt): inPred = '' else: dfile = crossrep.getEnv('MIGRATION_HOME') + opt link_dblist = crossrep.readFile(dfile) inPred = crossrep.genInPredicate('name', link_dblist) query = ("select distinct name, snowflake_region, account_name from " + crossrep.tb_gldb + " where is_primary = 'true' " + inPred + " order by name ") if crossrep.verbose == True: print(query) cursor.execute(query) rec = cursor.fetchall() for r in rec: name = r[0] rep_region = r[1] acct_name = r[2] #print ('db name: '+ r[0]) #cquery = " CREATE DATABASE \"" + name + "\" REPLICATION GROUP '" + rep_group + "'" cquery = (" CREATE DATABASE \"" + name + "\" as replica of " + rep_region + "." + acct_name + ".\"" + name + "\"" + " auto_refresh_materialized_views_on_secondary = true;\n") if crossrep.verbose == True: print(cquery) try: ofile2.write(cquery + ";\n") aquery = " ALTER DATABASE \"" + name + "\" REFRESH " if crossrep.verbose == True: print(cquery) ofile3.write(aquery + ";\n") # Generate SQL to monitor Replication Progress mquery = ( "with top_monitor as ( " + " select '" + name + "' as dbname, f.value:phaseName::string as Phase, f.value:resultName::string as Result, to_timestamp_ltz(f.value:startTimeUTC::numeric,3) as startTime, " + " NVL(to_timestamp_ltz(f.value:endTimeUTC::numeric,3),CURRENT_TIMESTAMP()) as endTime " + " from lateral flatten(input=> parse_json(system$database_refresh_progress('" + name + "'))) f) " + ", detail_monitor as ( " + " select '" + name + "' as dbname, f.value:phaseName::string as Phase " + ", (d.value/1024/1024/1024/1024) tb_bytes " + " from lateral flatten(input=> parse_json(system$database_refresh_progress('" + name + "'))) f " + ", lateral flatten(input=> parse_json(f.value:details)) d " + " where phase = 'Copying Primary Data' ) " + " select tm.dbname, timediff(seconds, min(tm.startTime), max(tm.endTime))/60/60 as exec_hours ,max(dm.tb_bytes) total_copy_tbbytes , min(dm.tb_bytes) completed_copy_tbbytes, 100*completed_copy_tbbytes/total_copy_tbbytes percent " + " from top_monitor tm left join detail_monitor dm " + " on tm.dbname = dm.dbname group by tm.dbname ") if crossrep.verbose == True: print(mquery) ofile4.write(mquery + ";\n") squery = " ALTER DATABASE \"" + name + "\" PRIMARY " ofile5.write(squery + ";\n") sfquery = ( "with top_monitor as ( " + " select '" + name + "' as dbname, f.value:phaseName::string as Phase, f.value:resultName::string as Result, to_timestamp_ltz(f.value:startTimeUTC::numeric,3) as startTime, " + " NVL(to_timestamp_ltz(f.value:endTimeUTC::numeric,3),CURRENT_TIMESTAMP()) as endTime " + " from lateral flatten(input=> parse_json(system$database_refresh_progress('" + crossrep.acctpref_qualifier + name + "'))) f) " + ", detail_monitor as ( " + " select '" + name + "' as dbname, f.value:phaseName::string as Phase " + ", (d.value/1024/1024/1024/1024) tb_bytes " + " from lateral flatten(input=> parse_json(system$database_refresh_progress('" + crossrep.acctpref_qualifier + name + "'))) f " + ", lateral flatten(input=> parse_json(f.value:details)) d " + " where phase = 'Copying Primary Data' ) " + " select tm.dbname, timediff(seconds, min(tm.startTime), max(tm.endTime))/60/60 as exec_hours ,max(dm.tb_bytes) total_copy_tbbytes , min(dm.tb_bytes) completed_copy_tbbytes, 100*completed_copy_tbbytes/total_copy_tbbytes percent " + " from top_monitor tm left join detail_monitor dm " + " on tm.dbname = dm.dbname group by tm.dbname ") if crossrep.verbose == True: print(sfquery) ofile6.write(sfquery + ";\n") except Exception as err: print( 'An error occurred during alter database to enable replication for database:' + name + '; ' + str(err)) print('\n refreshing query:' + aquery) print('\n monitoring query:' + mquery) print('\n switching to primary query:' + squery) print('\n monitoring query with snowflake version:' + sfquery) # Generate query to monitor Replication Progress cquery = ( " select database_name, credits_used, bytes_transferred/1024/1024/1024/1024 as tbs_transferred, TIMEDIFF('SECOND', END_TIME, START_TIME) " + "from table( information_schema.replication_usage_history(" + "date_range_start=>dateadd(d, -7, current_date)," + "date_range_end=>current_date)) order by 1 ") ofile4.write(cquery + ";\n")