[go: up one dir, main page]
Skip to content

Commit

Permalink
feat: add preview support for default values (#2244)
Browse files Browse the repository at this point in the history
  • Loading branch information
@Neenu1995
Neenu1995 committed Aug 24, 2022
1 parent 6ae53d1 commit fd3d3c5
Show file tree
Hide file tree
Showing 3 changed files with 122 additions and 0 deletions.
53 changes: 53 additions & 0 deletions google-cloud-bigquery/src/main/java/com/google/cloud/bigquery/Field.java
View file
Expand Up @@ -62,6 +62,7 @@ public TableFieldSchema apply(Field field) {
private final Long maxLength;
private final Long scale;
private final Long precision;
private final String defaultValueExpression;

/**
* Mode for a BigQuery Table field. {@link Mode#NULLABLE} fields can be set to {@code null},
Expand All @@ -85,6 +86,7 @@ public static final class Builder {
private Long maxLength;
private Long scale;
private Long precision;
private String defaultValueExpression;

private Builder() {}

Expand All @@ -98,6 +100,7 @@ private Builder(Field field) {
this.maxLength = field.maxLength;
this.scale = field.scale;
this.precision = field.precision;
this.defaultValueExpression = field.defaultValueExpression;
}

/**
Expand Down Expand Up @@ -245,6 +248,43 @@ public Builder setPrecision(Long precision) {
return this;
}

/**
* DefaultValueExpression is used to specify the default value of a field using a SQL
* expression. It can only be set for top level fields (columns).
*
* <p>You can use struct or array expression to specify default value for the entire struct or
* array. The valid SQL expressions are:
*
* <ul>
* <ul>
* <li>Literals for all data types, including STRUCT and ARRAY.
* </ul>
* <ul>
* <li>The following functions:
* <ul>
* <li>CURRENT_TIMESTAMP
* <li>CURRENT_TIME
* <li>CURRENT_DATE
* <li>CURRENT_DATETIME
* <li>GENERATE_UUID
* <li>RAND
* <li>SESSION_USER
* <li>ST_GEOGPOINT
* </ul>
* </ul>
* <ul>
* <li>Struct or array composed with the above allowed functions, for example:
* <ul>
* <li>"[CURRENT_DATE(), DATE '2020-01-01']"
* </ul>
* </ul>
* </ul>
*/
public Builder setDefaultValueExpression(String defaultValueExpression) {
this.defaultValueExpression = defaultValueExpression;
return this;
}

/** Creates a {@code Field} object. */
public Field build() {
return new Field(this);
Expand All @@ -261,6 +301,7 @@ private Field(Builder builder) {
this.maxLength = builder.maxLength;
this.scale = builder.scale;
this.precision = builder.precision;
this.defaultValueExpression = builder.defaultValueExpression;
}

/** Returns the field name. */
Expand Down Expand Up @@ -311,6 +352,11 @@ public Long getPrecision() {
return precision;
}

/** Return the default value of the field. */
public String getDefaultValueExpression() {
return defaultValueExpression;
}

/**
* Returns the list of sub-fields if {@link #getType()} is a {@link LegacySQLTypeName#RECORD}.
* Returns {@code null} otherwise.
Expand All @@ -335,6 +381,7 @@ public String toString() {
.add("maxLength", maxLength)
.add("scale", scale)
.add("precision", precision)
.add("defaultValueExpression", defaultValueExpression)
.toString();
}

Expand Down Expand Up @@ -414,6 +461,9 @@ TableFieldSchema toPb() {
if (precision != null) {
fieldSchemaPb.setPrecision(precision);
}
if (defaultValueExpression != null) {
fieldSchemaPb.setDefaultValueExpression(defaultValueExpression);
}
if (getSubFields() != null) {
List<TableFieldSchema> fieldsPb = Lists.transform(getSubFields(), TO_PB_FUNCTION);
fieldSchemaPb.setFields(fieldsPb);
Expand Down Expand Up @@ -442,6 +492,9 @@ static Field fromPb(TableFieldSchema fieldSchemaPb) {
if (fieldSchemaPb.getPrecision() != null) {
fieldBuilder.setPrecision(fieldSchemaPb.getPrecision());
}
if (fieldSchemaPb.getDefaultValueExpression() != null) {
fieldBuilder.setDefaultValueExpression(fieldSchemaPb.getDefaultValueExpression());
}
FieldList subFields =
fieldSchemaPb.getFields() != null
? FieldList.of(Lists.transform(fieldSchemaPb.getFields(), FROM_PB_FUNCTION))
Expand Down
6 changes: 6 additions & 0 deletions google-cloud-bigquery/src/test/java/com/google/cloud/bigquery/FieldTest.java
View file
Expand Up @@ -39,10 +39,13 @@ public class FieldTest {
private static final String FIELD_DESCRIPTION1 = "FieldDescription1";
private static final String FIELD_DESCRIPTION2 = "FieldDescription2";
private static final String FIELD_DESCRIPTION3 = "FieldDescription3";
private static final String FIELD_DEFAULT_VALUE_EXPRESSION1 =
"This is default value for this field";
private static final Field FIELD_SCHEMA1 =
Field.newBuilder(FIELD_NAME1, FIELD_TYPE1)
.setMode(FIELD_MODE1)
.setDescription(FIELD_DESCRIPTION1)
.setDefaultValueExpression(FIELD_DEFAULT_VALUE_EXPRESSION1)
.build();
private static final Field FIELD_SCHEMA2 =
Field.newBuilder(FIELD_NAME2, FIELD_TYPE2)
Expand All @@ -60,6 +63,7 @@ public class FieldTest {
Field.newBuilder(FIELD_NAME1, StandardSQLTypeName.STRING)
.setMode(FIELD_MODE1)
.setDescription(FIELD_DESCRIPTION1)
.setDefaultValueExpression(FIELD_DEFAULT_VALUE_EXPRESSION1)
.build();
private static final Field STANDARD_FIELD_SCHEMA2 =
Field.newBuilder(FIELD_NAME2, StandardSQLTypeName.INT64)
Expand Down Expand Up @@ -137,6 +141,7 @@ public void testBuilder() {
assertEquals(FIELD_TYPE1, FIELD_SCHEMA1.getType());
assertEquals(FIELD_MODE1, FIELD_SCHEMA1.getMode());
assertEquals(FIELD_DESCRIPTION1, FIELD_SCHEMA1.getDescription());
assertEquals(FIELD_DEFAULT_VALUE_EXPRESSION1, FIELD_SCHEMA1.getDefaultValueExpression());
assertEquals(null, FIELD_SCHEMA1.getSubFields());
assertEquals(FIELD_NAME3, FIELD_SCHEMA3.getName());
assertEquals(FIELD_TYPE3, FIELD_SCHEMA3.getType());
Expand All @@ -151,6 +156,7 @@ public void testBuilderWithStandardSQLTypeName() {
assertEquals(FIELD_TYPE1, STANDARD_FIELD_SCHEMA1.getType());
assertEquals(FIELD_MODE1, STANDARD_FIELD_SCHEMA1.getMode());
assertEquals(FIELD_DESCRIPTION1, STANDARD_FIELD_SCHEMA1.getDescription());
assertEquals(FIELD_DEFAULT_VALUE_EXPRESSION1, FIELD_SCHEMA1.getDefaultValueExpression());
assertEquals(null, STANDARD_FIELD_SCHEMA1.getSubFields());
assertEquals(FIELD_NAME3, STANDARD_FIELD_SCHEMA3.getName());
assertEquals(FIELD_TYPE3, STANDARD_FIELD_SCHEMA3.getType());
Expand Down
63 changes: 63 additions & 0 deletions google-cloud-bigquery/src/test/java/com/google/cloud/bigquery/it/ITBigQueryTest.java
View file
Expand Up @@ -72,6 +72,7 @@
import com.google.cloud.bigquery.FormatOptions;
import com.google.cloud.bigquery.HivePartitioningOptions;
import com.google.cloud.bigquery.InsertAllRequest;
import com.google.cloud.bigquery.InsertAllRequest.RowToInsert;
import com.google.cloud.bigquery.InsertAllResponse;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobId;
Expand Down Expand Up @@ -1176,6 +1177,68 @@ public void testCreateTableWithConstraints() {
bigquery.delete(tableId);
}

@Test
public void testCreateTableWithDefaultValueExpression() {
String tableName = "test_create_table_with_default_value_expression";
TableId tableId = TableId.of(DATASET, tableName);
Field stringFieldWithDefaultValueExpression =
Field.newBuilder("stringFieldWithDefaultValueExpression", StandardSQLTypeName.STRING)
.setMode(Field.Mode.NULLABLE)
.setDescription("String field with default value expression")
.setDefaultValueExpression("'FOO'")
.setMaxLength(150L)
.build();
Field timestampFieldWithDefaultValueExpression =
Field.newBuilder("timestampFieldWithDefaultValueExpression", StandardSQLTypeName.TIMESTAMP)
.setMode(Field.Mode.NULLABLE)
.setDescription("Timestamp field with default value expression")
.setDefaultValueExpression("CURRENT_TIMESTAMP")
.build();
Schema schema =
Schema.of(stringFieldWithDefaultValueExpression, timestampFieldWithDefaultValueExpression);
StandardTableDefinition tableDefinition =
StandardTableDefinition.newBuilder().setSchema(schema).build();

// Create table with fields that have default value expression
Table createdTable = bigquery.create(TableInfo.of(tableId, tableDefinition));
assertNotNull(createdTable);

// Fetch the created table and its metadata
// to verify default value expression is assigned to fields
Table remoteTable = bigquery.getTable(DATASET, tableName);
Schema remoteSchema = remoteTable.<StandardTableDefinition>getDefinition().getSchema();
assertEquals(schema, remoteSchema);
FieldList fieldList = remoteSchema.getFields();
for (Field field : fieldList) {
if (field.getName().equals("timestampFieldWithDefaultValueExpression")) {
assertEquals("CURRENT_TIMESTAMP", field.getDefaultValueExpression());
}
if (field.getName().equals("stringFieldWithDefaultValueExpression")) {
assertEquals("'FOO'", field.getDefaultValueExpression());
}
}

// Insert value into the created table
// to verify default values are inserted when value is missing
String rowId1 = "rowId1";
String rowId2 = "rowId2";
List<RowToInsert> rows = new ArrayList<>();
Map<String, Object> row1 = new HashMap<>();
row1.put("timestampFieldWithDefaultValueExpression", "2022-08-22 00:45:12 UTC");
Map<String, Object> row2 = new HashMap<>();
row2.put("timestampFieldWithDefaultValueExpression", "2022-08-23 00:44:33 UTC");
rows.add(RowToInsert.of(rowId1, row1));
rows.add(RowToInsert.of(rowId2, row2));
InsertAllResponse response1 = remoteTable.insert(rows);

TableResult tableData = bigquery.listTableData(DATASET, tableName, schema);
String insertedField = "stringFieldWithDefaultValueExpression";
for (FieldValueList row : tableData.iterateAll()) {
assertEquals("FOO", row.get(insertedField).getValue());
}
bigquery.delete(tableId);
}

@Test
public void testCreateAndUpdateTableWithPolicyTags() throws IOException {
// Set up policy tags in the datacatalog service
Expand Down

0 comments on commit fd3d3c5

Please sign in to comment.