Automating the creation of views from Oracle to Snowflake

In this blog, we’ll learn how to automating the creation of views from Oracle to Snowflake using c#.

The following steps will guide to automate the creation of views,

1. Creating folders

2. Config the JSON file

3. Calling required methods in the main method.

The code has been developed in Microsoft Visual Studio using C#. It features an interactive debugger that enables users to navigate through source code, check variables, view call stacks, and execute commands in the console.

PRE-REQUISITES

1. In visual studio, Create a new project and give Console app(.Net Core). A console app takes input and displays output in a command-line window, also known as a console.
2. Paste the code in program.cs which is provided in the Github

3. Install the following packages with the same version,

• Newtonsoft.Json version 13.0.1
• Newtonsoft.Json version 13.0.1
• Snowflake.Data version 2.0.11

STEP 1 : Creating folders

Create 2 folders such as view ddl and error list.

• View ddl - This folder is to store the files which contains all the finally created view ddl.
• Error list- This folder is to store the errors which will be catched in the try catch of every method.

STEP 2 : Config the JSON file

CONFIG JSON STRUCTURE:

{
"ViewList":
          [],
"replace_list":
[{
   "old_value":"userenv('LANG')",
   "new_value":"'US'"
}],
"ORACLECONNECTIONSTRING":"............",
"SNOWFLAKECONNECTIONSTRING":"........."
}

• Create a JSON file which consists of view names to be created in snowflake in an array.
• In some scenario, oracle and snowflake doesn’t have same syntax so we need to replace it. Replace list consist of old value which need to be replaced and new value which needed in the view.
• Oracle and Snowflake connection strings are also stored in the JSON file to maintain the privacy so that the credentials won’t show in the c# code.
  It can also be stored in Key vault and can access it.

STEP 3 : Calling required methods in the main method.

• First step in main method is to clear all the created folders to avoid over writing or appending in the same file.

public static bool ClearTempFolder(string tempPath)
{
 try
    {
      var path = Directory.GetCurrentDirectory()+"/"+ tempPath + "/";
      if (!Directory.Exists(path))
      {
          Directory.CreateDirectory(path);
       }
       else
       {
          foreach (var file in Directory.GetFiles(path))
          {
               File.Delete(file);
           }
            foreach (var directory in Directory.GetDirectories(path, "*", SearchOption.TopDirectoryOnly))
            {
                Directory.Delete(directory, true);
             }
         }
         return true;
     }
  catch
  {
     return false;
   }
}

• Read the view name, connection string of oracle and snowflake, replace list to the respected variables.
• Calling all_dependency_list method will have all dependencies.
• First, we will call oracle_connector method.

public static OracleCommand Oracle_connector()
{
  try
    {
     OracleConnection connection = new OracleConnection(Oracle_Conn_string);
     connection.Open();
     OracleCommand command = new OracleCommand();
     command.Connection = connection;                
     return command;
    }
   catch (Exception ex)
   {
     Error_list.Add(new Tuple<string, string>(null, "Exception in Oracle : " + ex.Message.ToString()));
     return null;
   }
}
}

• all_dependencies describe dependencies between procedures, packages, functions, package bodies, and triggers accessible to the current user, including dependencies on views created without any database links.
• This method will calculate the dependencies among the views ie. Which view should create first (the priority to create view is calculated)

public static void AllDependency_list()
{
  try
  {               
    OracleCommand command = Oracle_connector();
    command.CommandText = "SELECT          OWNER,NAME,REFERENCED_OWNER,REFERENCED_NAME,REFERENCED_TYPE FROM SYS.ALL_DEPENDENCIES where REFERENCED_TYPE IN ('TABLE','VIEW','SYNONYM')";
    OracleDataReader reader6 = command.ExecuteReader();
       
        if (reader6.HasRows)
        {
           while (reader6.Read())
           {
              All_Dependency_list.Add(new Tuple<string, string, string, string, string>(reader6[0].ToString(), reader6[1].ToString(), reader6[2].ToString(), reader6[3].ToString(), reader6[4].ToString()));
            }
        }
  }
  catch (Exception ex)
  {
     Error_list.Add(new Tuple<string, string>(null, "Exception in AllDependency_list method :" + ex.Message.ToString()));
   }
}

• View name in JSON file consists of schema name + view name. Splitting the view and schema name and sending them as a parameter to Dependency method.
• A synonym is an alias or friendly name for the database objects (such as tables, views, stored procedures, functions, and packages)
• ALL_SYNONYMS describes the synonyms accessible to the current user.
• GetSynonym method is used to access the respected name for the synonym from all_synonym in oracle.

public static void GetSynonym()
{
 try 
   {
 var list = new ConcurrentBag<string>((from d1 in ref_oracle_views where d1.Item6 == "SYNONYM"
                            select d1.Item4 + "." + d1.Item5).ToList());
 OracleCommand command = Oracle_connector();
      command.CommandText = "SELECT S.OWNER , S.SYNONYM_NAME , T.OWNER , T.TABLE_NAME ,V.OWNER , V.VIEW_NAME , CASE WHEN T.OWNER IS NOT NULL AND T.TABLE_NAME IS NOT NULL THEN 'TABLE'   WHEN V.OWNER IS NOT NULL AND V.VIEW_NAME IS NOT NULL THEN 'VIEW'   ELSE 'NA' END AS TYPE_SYN FROM SYS.ALL_SYNONYMS S LEFT JOIN SYS.ALL_TABLES T ON T.OWNER = S.TABLE_OWNER AND T.TABLE_NAME = S.TABLE_NAME LEFT JOIN SYS.ALL_VIEWS V ON S.TABLE_OWNER = V.OWNER AND S.TABLE_NAME = V.VIEW_NAME WHERE S.owner || '.' || S.SYNONYM_NAME IN ('"+string.Join("','",list)+ "')";
      OracleDataReader reader6 = command.ExecuteReader();
   if (reader6.HasRows)
         {
           while (reader6.Read())
           {
              SynonymList.Add(new Tuple<string, string, string, string, string, string, string>(reader6[0].ToString(), reader6[1].ToString(), reader6[2].ToString(), reader6[3].ToString(), reader6[4].ToString(), reader6[5].ToString(), reader6[6].ToString()));
            }
          }
          var vw_list = new ConcurrentBag<string>((from d1 in SynonymList where d1.Item7 == "VIEW"
           select d1.Item5 + "." + d1.Item6).ToList().Distinct());
             foreach (var vw in vw_list)
             {
                 var split_view = vw.Split('.');
                 Dependency(vw, split_view[1], split_view[0]);
              }
           var all_list = new ConcurrentBag<string>((from d1 in SynonymList select d1.Item1 + "." + d1.Item2).ToList().Distinct());
           var all_list2= new ConcurrentBag<string>((from d1 in ref_oracle_views where d1.Item6 == "SYNONYM"
           select d1.Item4 + "." + d1.Item5).ToList().Distinct());
           var extra_obj = all_list2.Except(all_list);
            if(extra_obj.Count()>0)
            {
               GetSynonym();
             }
                   
            }
 catch (Exception ex)
 {
    Error_list.Add(new Tuple<string, string>(null, "Exception in GetSynonym" + ex.Message.ToString()));
  }
}

• Oracle_ddl_original method will have the original ddl of the view in oracle.
• all_views contain the ddl of the view.

public static void Oracle_ddl_original()
{
try
 {
  OracleCommand command = Oracle_connector();
  IEnumerable<string> Unique_Enum= new ConcurrentBag<string>((from r in ref_oracle_views
  select r.Item2 + "." + r.Item3).Distinct().ToList());
  Unique_List = new ConcurrentBag<string>(Unique_Enum);
  IEnumerable<string> Actual_Enum = (from r in ref_oracle_views where r.Item6 == "VIEW"
select r.Item4 + "." + r.Item5).Distinct().Union(Unique_List);
  Actual_list = new ConcurrentBag<string>(Actual_Enum);
  var viewList = "";
  foreach (var ul in Actual_list)
  {
     viewList += "'" + ul.Split(".")[1] + "',";
  }
  command.CommandText = "SELECT OWNER,VIEW_NAME,TEXT FROM SYS.ALL_VIEWS WHERE VIEW_NAME in(" + viewList.Remove(viewList.Length - 1) + ")";
  command.InitialLONGFetchSize = -1;
  OracleDataReader reader = command.ExecuteReader();
  if (reader.HasRows)
  {
   while (reader.Read())
   {
      var name = reader[0].ToString() +"."+ reader[1].ToString();
       else 
       {
           Oracle_list_ddl_original.Add(new Tuple<string, string, string>(reader[0].ToString(), reader[1].ToString(), " AS " +reader[2].ToString()));
         }
     }
   }
 }
 catch (Exception ex)
   {
     Error_list.Add(new Tuple<string, string>(null, "Exception in  oracle ddl original method : " + ex.Message.ToString()));
   }
}

• Oracle_ddl_replaced method will have the final ddl which will be created in the snowflake with all the replacements done.

public static void Oracle_ddl_replaced()
{
 try
 {
   foreach (var org in Oracle_list_ddl_original)
   {
     var flterView = new ConcurrentBag<Tuple<string, string, string, string, string>>((from refer in ref_oracle_views
     where refer.Item3 == org.Item2 && refer.Item2==org.Item1
     select  new Tuple<string, string, string, string, string>(refer.Item2, refer.Item3, refer.Item4, refer.Item5, refer.Item6)).ToList().Distinct().OrderByDescending(x=>x.Item5.Length));
     ddl = org.Item3;
     foreach (var fltr in flterView)
     {
       ref_name = fltr.Item3 + "." + fltr.Item4;
       var replace_value = "";
       if (fltr.Item5 == "SYNONYM")
       {
         var syn = (from sy in SynonymList where sy.Item1 == fltr.Item3 && sy.Item2 == fltr.Item4 select sy).FirstOrDefault();
         if (syn.Item7 == "VIEW")
         {
           replace_value = "DEV_AN.SRC_IMG_VW.CDC_ORACLE_" + syn.Item5 + "_" + syn.Item6 + "_VW";
         }
         else
         {
           replace_value = "PROD.SRC_IMG_VW.CDC_ORACLE_" + syn.Item3 + "_" + syn.Item4 + "_CT_CURRENT_VW";
          }
        }
      else
      {
        if (fltr.Item5 == "VIEW")
        {
          replace_value = "DEV_AN.SRC_IMG_VW.CDC_ORACLE_" + fltr.Item3 + "_" + fltr.Item4 + "_VW";
        }
        else
        {
          replace_value = "PROD.SRC_IMG_VW.CDC_ORACLE_" + fltr.Item3 + "_" + fltr.Item4 + "_CT_CURRENT_VW";
        }
       }
       if (ddl.ToUpper().Contains(ref_name.ToUpper()))
       {
         ddl = Regex.Replace(ddl, ref_name, replace_value, RegexOptions.IgnoreCase);
       }
       else
       {                            
         ddl = Regex.Replace(ddl, fltr.Item4, replace_value, RegexOptions.IgnoreCase);                            
       }
     }
    Oracle_list_ddl_replaced.Add(new Tuple<string,string, string>(org.Item1,org.Item2, ddl));
   }
 }
 catch (Exception ex)
 {
  Error_list.Add(new Tuple<string, string>(null, "Exception in oracle replaced ddl method : " + ex.Message.ToString()));
 }
}

• Dependency_score is calculated. Based on this, the view will be created.

public static void Dependency_Score()
{
  try
  {
    var Dep_score = ref_oracle_views.GroupBy(n => n.Item1).Select(n => new
    {
      Item1 = n.Key,
      Item2 = n.Count()
    });
    var Dep0 = Actual_list.Where(a => !Dep_score.Any(b => a ==  b.Item1));
    foreach (var De in Dep0)
    {
      var a =(from orc in Oracle_list_ddl_replaced where orc.Item1 == De.Split('.')[0] && orc.Item2 == De.Split('.')[1]
select orc.Item3).FirstOrDefault();
      File.WriteAllText(Directory.GetCurrentDirectory() + "/viewddl/" + De + "_VW.sql", final_ddl);
      DependencyScore.Add(new Tuple<string, int,string>(De, 0,final_ddl));
    }
    foreach (var De in Dep_score)
    {
      var a = (from orc in Oracle_list_ddl_replaced
where orc.Item1 ==  De.Item1.Split('.')[0] && orc.Item2 == De.Item1.Split('.')[1]
select orc.Item3).FirstOrDefault();
      File.WriteAllText(Directory.GetCurrentDirectory() + "/viewddl/" + De.Item1 + "_VW.sql", final_ddl);
      DependencyScore.Add(new Tuple<string, int, string>(De.Item1,   De.Item2, final_ddl));
    }
  }
   catch (Exception ex)
   {
     Error_list.Add(new Tuple<string, string>(null, "Exception in main method : " + ex.Message.ToString()));
   }
}

• SnowflakeQuery Executer method is used to connect the snowflake so that the views will be created.

public static DbDataReader SnowflakeQueryExecuter(string vw_name ,string snowflakeSQLToExecute)
{
  try
  {
    using (var conn = new SnowflakeDbConnection())
    {
      string snowflakeConnectionString = Snowflake_Conn_string;
      snowflakeConnectionString = string.Format(snowflakeConnectionString, "DEV"); // warehouse should be given like this not directly
      conn.ConnectionString = snowflakeConnectionString;
      conn.Open();                    
      var cmd = conn.CreateCommand();
      cmd.CommandText = snowflakeSQLToExecute;
      var reader = cmd.ExecuteReader();
      conn.Close();
      return reader;
     }
  }
  catch (Exception ex)
  {
    Error_list.Add(new Tuple<string, string>(vw_name, "Exception in snowflake connection: " + ex.Message.ToString()));
    return null;
   }
}

FOR REFERENCE : Janani-Nallasivam/Blog_project (github.com)

CONCLUSION

Finally, We have done a console app and found a solution to automate the creation of views from oracle to snowflake. I hope this article will be helpful to you.

About Us

Bi3 has been recognized for being one of the fastest-growing companies in Australia. Our team has delivered substantial and complex projects for some of the largest organizations around the globe and we’re quickly building a brand that is well known for superior delivery.

Website:https://bi3technologies.com/

Follow us on,
LinkedIn: https://www.linkedin.com/company/bi3technologies
Instagram: https://www.instagram.com/bi3technologies/
Twitter: https://twitter.com/Bi3Technologies