1// Copyright 2019 Google LLC 2// 3// Licensed under the Apache License, Version 2.0 (the "License"); 4// you may not use this file except in compliance with the License. 5// You may obtain a copy of the License at 6// 7// http://www.apache.org/licenses/LICENSE-2.0 8// 9// Unless required by applicable law or agreed to in writing, software 10// distributed under the License is distributed on an "AS IS" BASIS, 11// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 12// See the License for the specific language governing permissions and 13// limitations under the License. 14 15package bigquery 16 17import ( 18 "context" 19 "errors" 20 "fmt" 21 "time" 22 23 "cloud.google.com/go/internal/optional" 24 "cloud.google.com/go/internal/trace" 25 bq "google.golang.org/api/bigquery/v2" 26) 27 28// Routine represents a reference to a BigQuery routine. There are multiple 29// types of routines including stored procedures and scalar user-defined functions (UDFs). 30// For more information, see the BigQuery documentation at https://cloud.google.com/bigquery/docs/ 31type Routine struct { 32 ProjectID string 33 DatasetID string 34 RoutineID string 35 36 c *Client 37} 38 39func (r *Routine) toBQ() *bq.RoutineReference { 40 return &bq.RoutineReference{ 41 ProjectId: r.ProjectID, 42 DatasetId: r.DatasetID, 43 RoutineId: r.RoutineID, 44 } 45} 46 47// FullyQualifiedName returns an identifer for the routine in project.dataset.routine format. 48func (r *Routine) FullyQualifiedName() string { 49 return fmt.Sprintf("%s.%s.%s", r.ProjectID, r.DatasetID, r.RoutineID) 50} 51 52// Create creates a Routine in the BigQuery service. 53// Pass in a RoutineMetadata to define the routine. 54func (r *Routine) Create(ctx context.Context, rm *RoutineMetadata) (err error) { 55 ctx = trace.StartSpan(ctx, "cloud.google.com/go/bigquery.Routine.Create") 56 defer func() { trace.EndSpan(ctx, err) }() 57 58 routine, err := rm.toBQ() 59 if err != nil { 60 return err 61 } 62 routine.RoutineReference = &bq.RoutineReference{ 63 ProjectId: r.ProjectID, 64 DatasetId: r.DatasetID, 65 RoutineId: r.RoutineID, 66 } 67 req := r.c.bqs.Routines.Insert(r.ProjectID, r.DatasetID, routine).Context(ctx) 68 setClientHeader(req.Header()) 69 _, err = req.Do() 70 return err 71} 72 73// Metadata fetches the metadata for a given Routine. 74func (r *Routine) Metadata(ctx context.Context) (rm *RoutineMetadata, err error) { 75 ctx = trace.StartSpan(ctx, "cloud.google.com/go/bigquery.Routine.Metadata") 76 defer func() { trace.EndSpan(ctx, err) }() 77 78 req := r.c.bqs.Routines.Get(r.ProjectID, r.DatasetID, r.RoutineID).Context(ctx) 79 setClientHeader(req.Header()) 80 var routine *bq.Routine 81 err = runWithRetry(ctx, func() (err error) { 82 routine, err = req.Do() 83 return err 84 }) 85 if err != nil { 86 return nil, err 87 } 88 return bqToRoutineMetadata(routine) 89} 90 91// Update modifies properties of a Routine using the API. 92func (r *Routine) Update(ctx context.Context, upd *RoutineMetadataToUpdate, etag string) (rm *RoutineMetadata, err error) { 93 ctx = trace.StartSpan(ctx, "cloud.google.com/go/bigquery.Routine.Update") 94 defer func() { trace.EndSpan(ctx, err) }() 95 96 bqr, err := upd.toBQ() 97 if err != nil { 98 return nil, err 99 } 100 //TODO: remove when routines update supports partial requests. 101 bqr.RoutineReference = &bq.RoutineReference{ 102 ProjectId: r.ProjectID, 103 DatasetId: r.DatasetID, 104 RoutineId: r.RoutineID, 105 } 106 107 call := r.c.bqs.Routines.Update(r.ProjectID, r.DatasetID, r.RoutineID, bqr).Context(ctx) 108 setClientHeader(call.Header()) 109 if etag != "" { 110 call.Header().Set("If-Match", etag) 111 } 112 var res *bq.Routine 113 if err := runWithRetry(ctx, func() (err error) { 114 res, err = call.Do() 115 return err 116 }); err != nil { 117 return nil, err 118 } 119 return bqToRoutineMetadata(res) 120} 121 122// Delete removes a Routine from a dataset. 123func (r *Routine) Delete(ctx context.Context) (err error) { 124 ctx = trace.StartSpan(ctx, "cloud.google.com/go/bigquery.Model.Delete") 125 defer func() { trace.EndSpan(ctx, err) }() 126 127 req := r.c.bqs.Routines.Delete(r.ProjectID, r.DatasetID, r.RoutineID).Context(ctx) 128 setClientHeader(req.Header()) 129 return req.Do() 130} 131 132// RoutineDeterminism specifies the level of determinism that javascript User Defined Functions 133// exhibit. 134type RoutineDeterminism string 135 136const ( 137 // Deterministic indicates that two calls with the same input to a UDF yield the same output. 138 Deterministic RoutineDeterminism = "DETERMINISTIC" 139 // NotDeterministic indicates that the output of the UDF is not guaranteed to yield the same 140 // output each time for a given set of inputs. 141 NotDeterministic RoutineDeterminism = "NOT_DETERMINISTIC" 142) 143 144// RoutineMetadata represents details of a given BigQuery Routine. 145type RoutineMetadata struct { 146 ETag string 147 // Type indicates the type of routine, such as SCALAR_FUNCTION, PROCEDURE, 148 // or TABLE_VALUED_FUNCTION. 149 Type string 150 CreationTime time.Time 151 Description string 152 // DeterminismLevel is only applicable to Javascript UDFs. 153 DeterminismLevel RoutineDeterminism 154 LastModifiedTime time.Time 155 // Language of the routine, such as SQL or JAVASCRIPT. 156 Language string 157 // The list of arguments for the the routine. 158 Arguments []*RoutineArgument 159 ReturnType *StandardSQLDataType 160 161 // Set only if the routine type is TABLE_VALUED_FUNCTION. 162 ReturnTableType *StandardSQLTableType 163 // For javascript routines, this indicates the paths for imported libraries. 164 ImportedLibraries []string 165 // Body contains the routine's body. 166 // For functions, Body is the expression in the AS clause. 167 // 168 // For SQL functions, it is the substring inside the parentheses of a CREATE 169 // FUNCTION statement. 170 // 171 // For JAVASCRIPT function, it is the evaluated string in the AS clause of 172 // a CREATE FUNCTION statement. 173 Body string 174} 175 176func (rm *RoutineMetadata) toBQ() (*bq.Routine, error) { 177 r := &bq.Routine{} 178 if rm == nil { 179 return r, nil 180 } 181 r.Description = rm.Description 182 r.DeterminismLevel = string(rm.DeterminismLevel) 183 r.Language = rm.Language 184 r.RoutineType = rm.Type 185 r.DefinitionBody = rm.Body 186 rt, err := rm.ReturnType.toBQ() 187 if err != nil { 188 return nil, err 189 } 190 r.ReturnType = rt 191 if rm.ReturnTableType != nil { 192 tt, err := rm.ReturnTableType.toBQ() 193 if err != nil { 194 return nil, fmt.Errorf("couldn't convert return table type: %v", err) 195 } 196 r.ReturnTableType = tt 197 } 198 var args []*bq.Argument 199 for _, v := range rm.Arguments { 200 bqa, err := v.toBQ() 201 if err != nil { 202 return nil, err 203 } 204 args = append(args, bqa) 205 } 206 r.Arguments = args 207 r.ImportedLibraries = rm.ImportedLibraries 208 if !rm.CreationTime.IsZero() { 209 return nil, errors.New("cannot set CreationTime on create") 210 } 211 if !rm.LastModifiedTime.IsZero() { 212 return nil, errors.New("cannot set LastModifiedTime on create") 213 } 214 if rm.ETag != "" { 215 return nil, errors.New("cannot set ETag on create") 216 } 217 return r, nil 218} 219 220// RoutineArgument represents an argument supplied to a routine such as a UDF or 221// stored procedured. 222type RoutineArgument struct { 223 // The name of this argument. Can be absent for function return argument. 224 Name string 225 // Kind indicates the kind of argument represented. 226 // Possible values: 227 // ARGUMENT_KIND_UNSPECIFIED 228 // FIXED_TYPE - The argument is a variable with fully specified 229 // type, which can be a struct or an array, but not a table. 230 // ANY_TYPE - The argument is any type, including struct or array, 231 // but not a table. 232 Kind string 233 // Mode is optional, and indicates whether an argument is input or output. 234 // Mode can only be set for procedures. 235 // 236 // Possible values: 237 // MODE_UNSPECIFIED 238 // IN - The argument is input-only. 239 // OUT - The argument is output-only. 240 // INOUT - The argument is both an input and an output. 241 Mode string 242 // DataType provides typing information. Unnecessary for ANY_TYPE Kind 243 // arguments. 244 DataType *StandardSQLDataType 245} 246 247func (ra *RoutineArgument) toBQ() (*bq.Argument, error) { 248 if ra == nil { 249 return nil, nil 250 } 251 a := &bq.Argument{ 252 Name: ra.Name, 253 ArgumentKind: ra.Kind, 254 Mode: ra.Mode, 255 } 256 if ra.DataType != nil { 257 dt, err := ra.DataType.toBQ() 258 if err != nil { 259 return nil, err 260 } 261 a.DataType = dt 262 } 263 return a, nil 264} 265 266func bqToRoutineArgument(bqa *bq.Argument) (*RoutineArgument, error) { 267 arg := &RoutineArgument{ 268 Name: bqa.Name, 269 Kind: bqa.ArgumentKind, 270 Mode: bqa.Mode, 271 } 272 dt, err := bqToStandardSQLDataType(bqa.DataType) 273 if err != nil { 274 return nil, err 275 } 276 arg.DataType = dt 277 return arg, nil 278} 279 280func bqToArgs(in []*bq.Argument) ([]*RoutineArgument, error) { 281 var out []*RoutineArgument 282 for _, a := range in { 283 arg, err := bqToRoutineArgument(a) 284 if err != nil { 285 return nil, err 286 } 287 out = append(out, arg) 288 } 289 return out, nil 290} 291 292func routineArgumentsToBQ(in []*RoutineArgument) ([]*bq.Argument, error) { 293 var out []*bq.Argument 294 for _, inarg := range in { 295 arg, err := inarg.toBQ() 296 if err != nil { 297 return nil, err 298 } 299 out = append(out, arg) 300 } 301 return out, nil 302} 303 304// RoutineMetadataToUpdate governs updating a routine. 305type RoutineMetadataToUpdate struct { 306 Arguments []*RoutineArgument 307 Description optional.String 308 DeterminismLevel optional.String 309 Type optional.String 310 Language optional.String 311 Body optional.String 312 ImportedLibraries []string 313 ReturnType *StandardSQLDataType 314 ReturnTableType *StandardSQLTableType 315} 316 317func (rm *RoutineMetadataToUpdate) toBQ() (*bq.Routine, error) { 318 r := &bq.Routine{} 319 forceSend := func(field string) { 320 r.ForceSendFields = append(r.ForceSendFields, field) 321 } 322 nullField := func(field string) { 323 r.NullFields = append(r.NullFields, field) 324 } 325 if rm.Description != nil { 326 r.Description = optional.ToString(rm.Description) 327 forceSend("Description") 328 } 329 if rm.DeterminismLevel != nil { 330 processed := false 331 // Allow either string or RoutineDeterminism, a type based on string. 332 if x, ok := rm.DeterminismLevel.(RoutineDeterminism); ok { 333 r.DeterminismLevel = string(x) 334 processed = true 335 } 336 if x, ok := rm.DeterminismLevel.(string); ok { 337 r.DeterminismLevel = x 338 processed = true 339 } 340 if !processed { 341 panic(fmt.Sprintf("DeterminismLevel should be either type string or RoutineDetermism in update, got %T", rm.DeterminismLevel)) 342 } 343 } 344 if rm.Arguments != nil { 345 if len(rm.Arguments) == 0 { 346 nullField("Arguments") 347 } else { 348 args, err := routineArgumentsToBQ(rm.Arguments) 349 if err != nil { 350 return nil, err 351 } 352 r.Arguments = args 353 forceSend("Arguments") 354 } 355 } 356 if rm.Type != nil { 357 r.RoutineType = optional.ToString(rm.Type) 358 forceSend("RoutineType") 359 } 360 if rm.Language != nil { 361 r.Language = optional.ToString(rm.Language) 362 forceSend("Language") 363 } 364 if rm.Body != nil { 365 r.DefinitionBody = optional.ToString(rm.Body) 366 forceSend("DefinitionBody") 367 } 368 if rm.ImportedLibraries != nil { 369 if len(rm.ImportedLibraries) == 0 { 370 nullField("ImportedLibraries") 371 } else { 372 r.ImportedLibraries = rm.ImportedLibraries 373 forceSend("ImportedLibraries") 374 } 375 } 376 if rm.ReturnType != nil { 377 dt, err := rm.ReturnType.toBQ() 378 if err != nil { 379 return nil, err 380 } 381 r.ReturnType = dt 382 forceSend("ReturnType") 383 } 384 if rm.ReturnTableType != nil { 385 tt, err := rm.ReturnTableType.toBQ() 386 if err != nil { 387 return nil, err 388 } 389 r.ReturnTableType = tt 390 forceSend("ReturnTableType") 391 } 392 return r, nil 393} 394 395func bqToRoutineMetadata(r *bq.Routine) (*RoutineMetadata, error) { 396 meta := &RoutineMetadata{ 397 ETag: r.Etag, 398 Type: r.RoutineType, 399 CreationTime: unixMillisToTime(r.CreationTime), 400 Description: r.Description, 401 DeterminismLevel: RoutineDeterminism(r.DeterminismLevel), 402 LastModifiedTime: unixMillisToTime(r.LastModifiedTime), 403 Language: r.Language, 404 ImportedLibraries: r.ImportedLibraries, 405 Body: r.DefinitionBody, 406 } 407 args, err := bqToArgs(r.Arguments) 408 if err != nil { 409 return nil, err 410 } 411 meta.Arguments = args 412 ret, err := bqToStandardSQLDataType(r.ReturnType) 413 if err != nil { 414 return nil, err 415 } 416 meta.ReturnType = ret 417 tt, err := bqToStandardSQLTableType(r.ReturnTableType) 418 if err != nil { 419 return nil, err 420 } 421 meta.ReturnTableType = tt 422 return meta, nil 423} 424