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")
