/*
Create view of a table defined by a query.
*/
func (self *BigQuery) AssertView(viewName string, query string) (err error) {
tablesService := gbigquery.NewTablesService(self.service)
_, err = tablesService.Get(self.projectId, self.datasetId, viewName).Do()
if err != nil {
if gapiErr, ok := err.(*googleapi.Error); ok && gapiErr.Code == 404 {
viewTable := &gbigquery.Table{
TableReference: &gbigquery.TableReference{
DatasetId: self.datasetId,
ProjectId: self.projectId,
TableId: viewName,
},
View: &gbigquery.ViewDefinition{
Query: query,
},
}
if _, err = tablesService.Insert(self.projectId, self.datasetId, viewTable).Do(); err != nil {
if gapiErr, ok := err.(*googleapi.Error); ok && gapiErr.Code == 409 {
self.Infof("Unable to create %v, someone else already did it", viewName)
err = nil
return
} else {
err = errors.Errorf("Unable to create %#v with\n%v\n%v", viewName, utils.Prettify(viewTable), err)
return
}
}
}
}
return
}
func (self *BigQuery) DropTable(tableName string) (err error) {
tablesService := gbigquery.NewTablesService(self.service)
if err = tablesService.Delete(self.projectId, self.datasetId, tableName).Do(); err != nil {
if gapiErr, ok := err.(*googleapi.Error); ok && gapiErr.Code == 404 {
self.Infof("Unable to delete %v, someone else already did it", tableName)
err = nil
} else {
return
}
}
return
}
/*
AssertTable will check if a table named after i exists.
If it does, it will patch it so that it has all missing columns.
If it does not, it will create it.
Then it will check if there exists a view of the same table that only shows
the latest (counted by UpdatedAt) row per unique Id.
It assumes that i has a field "Id" that is a key.Key, and a field "UpdatedAt" that is a utils.Time.
*/
func (self *BigQuery) AssertTable(i interface{}) (err error) {
typ := reflect.TypeOf(i)
for typ.Kind() == reflect.Ptr {
typ = typ.Elem()
}
tablesService := gbigquery.NewTablesService(self.service)
table, err := tablesService.Get(self.projectId, self.datasetId, typ.Name()).Do()
if err != nil {
if gapiErr, ok := err.(*googleapi.Error); ok && gapiErr.Code == 404 {
return self.createTable(typ, tablesService)
} else {
return
}
}
return self.patchTable(typ, tablesService, table)
}
/*
AssertCurrentVersionView will create a 'Current[tableName]' view by nesting a series of joins
that result in a view consisting of only the most recent (as in UpdatedAt, and then _inserted_at)
version of each 'id' present in the tableName table.
*/
func (self *BigQuery) AssertCurrentVersionView(tableName string) (err error) {
tablesService := gbigquery.NewTablesService(self.service)
table, err := tablesService.Get(self.projectId, self.datasetId, tableName).Do()
if err != nil {
return
}
cols := []string{}
self.addFieldNames(table.Schema.Fields, "data.", &cols)
currentTableQuery := fmt.Sprintf(`
SELECT
%v FROM [%v.%v] AS data
INNER JOIN EACH (
SELECT
latest.id AS id,
latest.iso8601_updated_at AS iso8601_updated_at,
MAX(latest._inserted_at) AS _inserted_at
FROM [%v.%v] AS latest INNER JOIN EACH (
SELECT
id AS id,
MAX(iso8601_updated_at) AS iso8601_updated_at
FROM [%v.%v] GROUP EACH BY id
) AS current ON
current.id = latest.id AND
current.iso8601_updated_at = latest.iso8601_updated_at
GROUP EACH BY id, iso8601_updated_at
) AS key ON
key.id = data.id AND
key.iso8601_updated_at = data.iso8601_updated_at AND
key._inserted_at = data._inserted_at`, strings.Join(cols, ", "), self.datasetId, tableName, self.datasetId, tableName, self.datasetId, tableName)
currentTableName := fmt.Sprintf("Current%v", tableName)
if err = self.AssertView(currentTableName, currentTableQuery); err != nil {
return
}
return
}