Add a column using a query job

import (
	"context"
	"fmt"

	"cloud.google.com/go/bigquery"
)

// createTableAndWidenQuery demonstrates how the schema of a table can be modified to add columns by appending
// query results that include the new columns.
func createTableAndWidenQuery(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %w", err)
	}
	defer client.Close()

	// First, we create a sample table.
	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
		{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
	}
	original := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, original); err != nil {
		return err
	}
	// Our table has two columns.  We'll introduce a new favorite_color column via
	// a subsequent query that appends to the table.
	q := client.Query("SELECT \"Timmy\" as full_name, 85 as age, \"Blue\" as favorite_color")
	q.SchemaUpdateOptions = []string{"ALLOW_FIELD_ADDITION"}
	q.QueryConfig.Dst = client.Dataset(datasetID).Table(tableID)
	q.WriteDisposition = bigquery.WriteAppend
	q.Location = "US"
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	_, err = job.Wait(ctx)
	if err != nil {
		return err
	}
	return nil
}

use Google\Cloud\BigQuery\BigQueryClient;

/**
 * Append a column using a query job.
 *
 * @param string $projectId The project Id of your Google Cloud Project.
 * @param string $datasetId The BigQuery dataset ID.
 * @param string $tableId The BigQuery table ID.
 */
function add_column_query_append(
    string $projectId,
    string $datasetId,
    string $tableId
): void {
    $bigQuery = new BigQueryClient([
      'projectId' => $projectId,
    ]);
    $dataset = $bigQuery->dataset($datasetId);
    $table = $dataset->table($tableId);

    // In this example, the existing table contains only the 'Name' and 'Title'.
    // A new column 'Description' gets added after the query job.

    // Define query
    $query = sprintf('SELECT "John" as name, "Unknown" as title, "Dummy person" as description;');

    // Set job configs
    $queryJobConfig = $bigQuery->query($query);
    $queryJobConfig->destinationTable($table);
    $queryJobConfig->schemaUpdateOptions(['ALLOW_FIELD_ADDITION']);
    $queryJobConfig->writeDisposition('WRITE_APPEND');

    // Run query with query job configuration
    $bigQuery->runQuery($queryJobConfig);

    // Print all the columns
    $columns = $table->info()['schema']['fields'];
    printf('The columns in the table are ');
    foreach ($columns as $column) {
        printf('%s ', $column['name']);
    }
}

use google_cloud_bigquery::client::BigQuery;
use google_cloud_bigquery::model::TableReference;

pub async fn sample(project_id: &str, dataset_id: &str, table_id: &str) -> anyhow::Result<()> {
    let client = BigQuery::builder().build().await?;

    // Query parameters cannot be used as substitutes for identifiers, column names or table
    // names. Ensure resource identifiers are validated before formatting into SQL statements
    // to prevent SQL injection.
    validate_resource_names(project_id, dataset_id, table_id)?;

    // First, initialize the destination table with a single column
    let create_sql = format!(
        "CREATE OR REPLACE TABLE `{project_id}.{dataset_id}.{table_id}` \
         (name STRING) AS \
         SELECT 'Alice' AS name;"
    );
    client
        .query(create_sql)
        .with_project_id(project_id)
        .set_location("US")
        .run()
        .await?
        .until_done()
        .await?;

    // Append rows using a query job that introduces a new column (`age`),
    // automatically updating the destination table schema.
    let append_sql = "SELECT 'Bob' AS name, 30 AS age;";
    let res = client
        .query(append_sql)
        .with_project_id(project_id)
        .set_destination_table(
            TableReference::new()
                .set_project_id(project_id)
                .set_dataset_id(dataset_id)
                .set_table_id(table_id),
        )
        .set_write_disposition("WRITE_APPEND")
        .set_schema_update_options(["ALLOW_FIELD_ADDITION"])
        .set_location("US")
        .run()
        .await?
        .until_done()
        .await?;

    println!("Appended rows with new column successfully: {:?}", res);
    Ok(())
}