How to create a view of table that contains a timestamp column?
- by Matt Faus
This question is an extension of a previous one I have asked.
I have a table (2014_05_31_transformed.Video) with a schema that looks like this. I have put up the JSON returned by the BigQuery API describing it's schema in this gist.
I am trying to create a view against this table with an API call that looks like this:
{
'view': {
'query': u 'SELECT deleted_mod_time FROM [2014_05_31_transformed.Video]'
},
'tableReference': {
'datasetId': 'latest_transformed',
'tableId': u 'Video',
'projectId': 'redacted'
}
}
But, the BigQuery API is returning this error:
HttpError: https://www.googleapis.com/bigquery/v2/projects/124072386181/datasets/latest_transformed/tables?alt=json returned "Invalid field name "deleted_mod_time.usec". Fields must contain only letters, numbers, and underscores, start with a letter or underscore, and be at most 128 characters long."
The schema that the BigQuery API does not make any distinction between a TIMESTAMP data type and a regular nullable INTEGER data type, so I can't think of a way to programmatically correct this problem. Is there anything I can do, or is this a bug with BigQuery's view implementation?
