How to create a view of table that contains a timestamp column?

Question

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.

2014_05_31_transformed.Video schema

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?

Can confirm that this is reproducible, and it looks like a bug. Looks like the simplest working example so the devs can look at it is SELECT TIMESTAMP("2014-04-01 12:22:12");. Run query; it's fine. Now try to save it as a view; it returns the same error as OPs. — Rohit

Pentium10 Pentium10 · Accepted Answer · 2014-06-05T06:40:47

You need to de-reference nested record type fields for views:

SELECT utm.campaign as utm_campaign ...

You need to write as:

select FORMAT_UTC_USEC(timestamp) as timestamp ...

Timestamps are stored/selected internally as nested types, and views currently don't allow nested results. BQ team is working on a fix, but unfortunately, it isn't straightforward.

How to create a view of table that contains a timestamp column?

1 Answers