title: How to create a view against a table that has record fields?
body: <p>We have a weekly backup process which exports our production Google Appengine Datastore onto Google Cloud Storage, and then into Google BigQuery. Each week, we create a new dataset named like <code>YYYY_MM_DD</code> that contains a copy of the production tables on that day. Over time, we have collected many datasets, like <code>2014_05_10</code>, <code>2014_05_17</code>, etc. I want to create a data set <code>Latest_Production_Data</code> that contains a view for each of the tables in the most recent <code>YYYY_MM_DD</code> dataset. This will make it easier for downstream reports to write their query once and always retrieve the most recent data.</p>
  
  <p>To do this, I have code that gets the most recent dataset and the names of all the tables that dataset contains from the BigQuery API. Then, for each of these tables, I fire a <a href="https://developers.google.com/bigquery/docs/reference/v2/tables/insert" rel="nofollow noreferrer">tables.insert</a> call to create a view that is a <code>SELECT *</code> from the table I am looking to create a reference to.</p>
  
  <p>This fails for tables that contain a <code>RECORD</code> field, from what looks to be a pretty benign column-naming rule.</p>
  
  <p>For example, I have this table:</p>
  
  <p><img src="https://i.stack.imgur.com/0bQ0l.png" alt="AccounDeletionRequest Schema"></p>
  
  <p>For which I issue this API call:</p>
  
  <pre><code>{
    'tableReference': {
      'projectId': 'redacted',
      'tableId': u'AccountDeletionRequest',
      'datasetId': 'Latest_Production_Data'
    }
    'view': {
      'query': u'SELECT * FROM [2014_05_17.AccountDeletionRequest]'
    },
  }
  </code></pre>
  
  <p>This results in the following error:</p>
  
  <blockquote>
    <p>HttpError: https://www.googleapis.com/bigquery/v2/projects//datasets/Latest_Production_Data/tables?alt=json returned "Invalid field name "__key__.namespace". Fields must contain only letters, numbers, and underscores, start with a letter or underscore, and be at most 128 characters long."></p>
  </blockquote>
  
  <p>When I execute this query in the BigQuery web console, the columns are renamed to translate the <code>.</code> to an <code>_</code>. I kind of expected the same thing to happen when I issued the create view API call.</p>
  
  <p><img src="https://i.stack.imgur.com/auEdy.png" alt="Select star results"></p>
  
  <p>Is there an easy way I can programmatically create a view for each of the tables in my dataset, regardless of their underlying schema? The problem I'm encountering now is for record columns, but another problem I anticipate is for tables that have repeated fields. Is there some magic alternative to <code>SELECT *</code> that will take care of all these intricacies for me?</p>
  
  <p>Another idea I had was doing a <a href="https://developers.google.com/bigquery/docs/tables#copyingtable" rel="nofollow noreferrer">table copy</a>, but I would prefer not to duplicate the data if I can at all avoid it.</p>