title: Converting loop with variables to BigQuery SQL
body: <p>I have thousands of script that include loop over dataset and use variables for accumulation. Eg:</p>
  
  <pre><code>// assuming that 'ids' is populated from some BQ table
  ids = [1, 2, 3, 4, 5]
  var1 = "v1"   //initialize variable
  var2 = "v2"   //initialize variable
  
  for id in ids
      var1 = var2
      if (id &gt; 2)
        var2 = var1 + "-" + item
      else
        var2 = id
      print(id, var1, var2)
  </code></pre>
  
  <p>This would produce below output:</p>
  
  <pre><code>1,v2,1
  2,1,2
  3,2,2-3
  4,2-3,2-3-4
  5,2-3-4,2-3-4-5
  </code></pre>
  
  <p>Few other things to consider:</p>
  
  <ul>
  <li>Script can involve n number of variables. </li>
  <li>Variables can act as
  accumulators or can simply have literals.</li>
  <li>Condition in loop can be
  more complex, multiple nested <code>if</code> / <code>else</code> conditions.</li>
  <li>Script can have nested loop as well which would loop for n times or till certain condition is not met.</li>
  </ul>
  
  <p>Is it possible to support it in BigQuery SQL? I'm open for BigQuery UDF as well but UDF does not support mutable state.</p>
  
  <p>Also since there are thousands of such scripts, instead of doing it manually, I want to automate conversion. Parsing these scripts is not an issue for me, I only need to convert this to equivalent BigQuery SQL. Let me know how I can approach this problem.</p>
  
  <p><strong>Edit:</strong>
  Though I'm more inclined to use SQL only approach but <a href="https://cloud.google.com/blog/products/data-analytics/command-and-control-now-easier-in-bigquery-with-scripting-and-stored-procedures" rel="nofollow noreferrer">BigQuery script</a> also seems promising option.</p>