title: Setting variable value dynamically in Google BigQuery
body: <p>we are running K means clustering in BigQuery using below syntax.</p>
  <p>In the first step we are running k means clustering for k values 1 to 7 and storing k value along with dbi statistic in &quot;grid&quot; table.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>k</th>
  <th>dbi</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>0.16</td>
  </tr>
  <tr>
  <td>2</td>
  <td>2.22</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>In the second step k means clustering is run using k value that has least dbi statistic stored in &quot;grid&quot; table (k=1 as per above example)</p>
  <p>step 1:</p>
  <pre><code>EXECUTE IMMEDIATE
    ('CREATE OR REPLACE TABLE ' ||dataset_name|| '.grid (dbi float64, k int64)');
  WHILE
    n &lt; 7 DO
  EXECUTE IMMEDIATE
    &quot;CREATE OR REPLACE MODEL &quot; ||dataset_name|| &quot;.clusters OPTIONS(model_type='kmeans',num_clusters=@x,distance_type = 'euclidean',standardize_features = TRUE,kmeans_init_method = 'KMEANS++') AS SELECT * EXCEPT(Id) FROM &quot; ||dataset_name|| &quot;. raw_data&quot;
  USING
    n AS x;
  EXECUTE IMMEDIATE
    &quot;insert &quot; ||dataset_name|| &quot;.grid SELECT davies_bouldin_index, @y as k FROM ML.EVALUATE(model &quot;||dataset_name||&quot;.clusters)&quot;
  USING
    n AS y;
  SET
    n = n + 1;
  END WHILE
    ;
  </code></pre>
  <p>step 2:</p>
  <pre><code>SET k = 'SELECT k FROM (SELECT dbi,k, RANK() OVER(ORDER BY dbi) AS ranking FROM '||dataset_name||'.grid) WHERE ranking =1'; 
  EXECUTE IMMEDIATE k;
  
  EXECUTE IMMEDIATE
    (&quot;CREATE OR REPLACE MODEL &quot;||dataset_name||&quot;.clusters OPTIONS(model_type='kmeans',num_clusters=@x,distance_type = 'euclidean',standardize_features = TRUE,kmeans_init_method = 'KMEANS++') AS SELECT * EXCEPT(Id) FROM &quot;||dataset_name||&quot;.raw_table&quot;)
  USING 
    k AS x; 
  EXECUTE IMMEDIATE  
    ('SELECT * FROM '||dataset_name||'.grid_demographics_emr&quot;');
  </code></pre>
  <p>i'm getting below error when i run step 2</p>
  <pre><code>Query error: Option num_clusters value has type STRING which cannot be coerced to expected type INT64; failed to set 'num_clusters' in OPTIONS()
  </code></pre>
  <p>while I know what the error is I'm not sure on how to fix it. I want to set the k value using dynamic dataset, coding as above is actually assigning string to k. hence, it is failing. I'm looking if there are any other ways to set k value using dynamic dataset in BigQuery?</p>
  <p>Thanks</p>