bigquery: how to create a BQ schema

[laurentsimon]

Hi

I've been trying to create a BQ schema to use with the bq from a Go structure:

func GenerateBQSchema(t interface{}) (string, error) {
}

and use it as input to the bq command to create/update tables:

bq mk --table   ...   myschema.schema

The only way I found to create this schema is to generate it myself. The schema needed seems to be the non-exported structure defined in

google-cloud-go/bigquery/schema.go

Line 544 in 5a2ed6b

type bigQueryJSONField struct {

I was wondering if I missed something and it's already feasible to generate the schema, or whether this is a missing feature which would be nice to have.

Can you advise?

[shollyman]

Not sure I understand the question. Take a look at InferSchema if you want to generate a schema for a Go datastructure: https://pkg.go.dev/cloud.google.com/go/bigquery#InferSchema

If you're trying to mapping unexported fields, this likely needs custom code.

[laurentsimon]

InferSchema generates some different schema when I tried, and was not accepted by the bq command to generate tables. All the fields in the structure are public.
Is there a different command to use to create table with the results of InferSchema?

[shollyman]

Oh, I think I understand now. The "schema file" used by CLI is just the underlying API's representation of the schema. We have support via SchemaFromJSON for parsing the API representation into a Schema type, but don't expose the converse.

If you're asking about how to create a table with the library and InferSchema, take a look at the examples:

func main() {
	ctx := context.Background()
	// Infer table schema from a Go type.
	schema, err := bigquery.InferSchema(Item{})
	if err != nil {
		// TODO: Handle error.
	}
	client, err := bigquery.NewClient(ctx, "project-id")
	if err != nil {
		// TODO: Handle error.
	}
	t := client.Dataset("my_dataset").Table("new-table")
	if err := t.Create(ctx,
		&bigquery.TableMetadata{
			Name:           "My New Table",
			Schema:         schema,
			ExpirationTime: time.Now().Add(24 * time.Hour),
		}); err != nil {
		// TODO: Handle error.
	}
}

type Item struct {
	Name  string
	Size  float64
	Count int
}

If you mean using this in other tools, could you elaborate more about the workflow you're trying to build? It seems odd to use the library for all the schema detection and then use the CLI for the actual resource creation, but I may just not be understanding.

[laurentsimon]

right, thanks. I actually just use the CLI. I ended up coding it as follows (which works):

func generateSchema(schema bigquery.Schema) []bigQueryJSONField {
	var bqs []bigQueryJSONField
	for _, fs := range schema {
		bq := bigQueryJSONField{
			Description: fs.Description,
			Name:        fs.Name,
			Type:        string(fs.Type),
			Fields:      generateSchema(fs.Schema),
		}
		// https://github.com/googleapis/google-cloud-go/blob/bigquery/v1.30.0/bigquery/schema.go#L125

		switch {
		// Make all fields optional to give us flexibility:
		// discard `fs.Required`.
		// An alternative would be to let the caller
		// use https://pkg.go.dev/cloud.google.com/go/bigquery#Schema.Relax.
		case fs.Repeated:
			bq.Mode = "REPEATED"
		default:
			bq.Mode = "NULLABLE"
		}

		bqs = append(bqs, bq)
	}

	return bqs
}

// GenerateBQSchema generates the BQ schema in JSON format.
// Can be used to generate a BQ table:
// `bq mk --table    ...  the.schema`.
// The structure `t` must be annotated using BQ fields:
// a string `bigquery:"name"`.
func GenerateBQSchema(t interface{}) (string, error) {
	schema, err := bigquery.InferSchema(t)
	if err != nil {
		return "", fmt.Errorf("bigquery.InferSchema: %w", err)
	}
	jsonFields := generateSchema(schema)

	jsonData, err := json.Marshal(jsonFields)
	if err != nil {
		return "", fmt.Errorf("json.Marshal: %w", err)
	}
	return string(jsonData), nil
}

but it would make more sense for your repo to have this, rather than me keeping up with possible changes to the schema.