google cloud BigQuery REST API, table creation with expirationTime

Question

I am using the BigQuery API in my appengine project to create a table and then load data using insertAll streaming input method. [ Ref: https://cloud.google.com/bigquery/docs/reference/v2/tables/insert ]

Sample python Code to create table:

scope = "https://www.googleapis.com/auth/bigquery"
authorization_token, _ = app_identity.get_access_token(scope)
Bodyfields = {
                "kind": "bigquery#table",
                "tableReference": {
                    "projectId": BIGQUERY_PROJECTID,
                    "datasetId": BIGQUERY_DATASETID,
                    "tableId": BIGQUERY_TABLEID
                },
                "friendlyName": 'Table 1',
                "description": 'My first table in big query',
                "schema": {
                    "fields": [
                        {
                            "name": 'A',
                            "type": 'STRING'
                        },
                        {
                            "name": 'B',
                            "type": 'STRING'
                        }
                    ]
                }
            }
result = urlfetch.fetch(url="https://www.googleapis.com/bigquery/v2/projects/" + BIGQUERY_PROJECTID + "/datasets/" + BIGQUERY_DATASETID + "/tables", method=urlfetch.POST, payload=json.dumps(Bodyfields), headers = {'Content-Type': 'application/json', "Authorization": "Bearer " + authorization_token})
logging.info(result.content)

This works as expected, but unfortunately the 'expirationTime' of table is set to 1 day.

According to the docs, when expirationTime is not provided in the requestBody, table is persisted indefinitely. [ Ref: https://cloud.google.com/bigquery/docs/reference/v2/tables#expirationTime ]. As seen in the image, my table is expiring in 1 day.

So I modified the code to include 'expirationTime' in the requestBody of insert table method.

Code sample:

ist_one_year_later = datetime.now() + timedelta(weeks=52)
ist_one_year_later_epoch_timestamp = long(float((ist_one_year_later - datetime(1970,1,1)).total_seconds()))
scope = "https://www.googleapis.com/auth/bigquery"
authorization_token, _ = app_identity.get_access_token(scope)
Bodyfields = {
                "kind": "bigquery#table",
                "tableReference": {
                    "projectId": BIGQUERY_PROJECTID,
                    "datasetId": BIGQUERY_DATASETID,
                    "tableId": BIGQUERY_TABLEID
                },
                "friendlyName": 'Table 1',
                "description": 'My first table in big query',,
                "expirationTime": ist_one_year_later_epoch_timestamp,
                "schema": {
                    "fields": [
                        {
                            "name": 'A',
                            "type": 'STRING'
                        },
                        {
                            "name": 'B',
                            "type": 'STRING'
                        }
                    ]
                }
            }
result = urlfetch.fetch(url="https://www.googleapis.com/bigquery/v2/projects/" + BIGQUERY_PROJECTID + "/datasets/" + BIGQUERY_DATASETID + "/tables", method=urlfetch.POST, payload=json.dumps(Bodyfields), headers = {'Content-Type': 'application/json', "Authorization": "Bearer " + authorization_token})
logging.info(result.content)

Now the outcome is even more bizzare. The response of this request, says table is created. But when i try to list the tables in the dataset, the table is not available.

Can someone tell me how to set expirationTime of a table to 1 year from creationTime?

EDIT: I have tried the patch and update method. Both does not give any error, but does not extend the expirationTime also.

This looks like a bug to me. Fill a bug report here: code.google.com/p/google-bigquery/issues/entry — Pentium10
@Pentium10 i have filed a bug report. meanwhile what is the workaround available to make a table with longer expirationTime for our current project — Sriram
create the table from the interface, I think that way you don't have this problem — Pentium10
Was the dataset created with a default expiration time for its table? — Felipe Hoffa
@FelipeHoffa thanks. thats a great catch. yes, i created the dataset during testing phase by ticking the 'expire in 1 day' checkbox. But even then, according the docs, when i provide expirationTime to a new table, it must override the settings of dataset. Hope someone from google can fix that bug. For now, i can fix my issue by creating a new dataset. Thanks — Sriram

Matthew Wesley Matthew Wesley · Accepted Answer · 2015-08-06T15:33:22

Per the comments on your question, the tables created without an expiration time take the default expiration time of one day from your dataset.

Your code to create the table with an explicit expiration time is incorrect. Per https://cloud.google.com/bigquery/docs/reference/v2/tables#expirationTime, the expiration time is expressed in milliseconds, but you have provided seconds since the epoch. Thus, the table is successfully created with an expiration time in the past, and does not exist when you list the tables in your dataset.

google cloud BigQuery REST API, table creation with expirationTime

1 Answers