com.google.cloud.bigquery.FieldValue Java Examples

private void checkResult(Iterable<List<String>> expected) throws Exception {
  final List<List<String>> sorted = ImmutableList.sortedCopyOf(ROW_COMPARATOR, expected);

  final String query = "SELECT * REPLACE (" //
      + "FORMAT_TIMESTAMP('%FT%R:%E*SZ', submission_timestamp) AS submission_timestamp, "
      + "CAST(payload AS STRING) AS payload), " //
      + "_TABLE_SUFFIX AS table " //
      + String.format("FROM `%s.%s.*`", bq.project, bq.dataset) //
      + " ORDER BY table";

  // allow retries to handle possible delayed availability of streaming insert
  List<List<String>> actual = null;
  for (int attempt = 0; attempt < 2; attempt++) {
    actual = StreamSupport
        .stream(bq.bigquery.query(QueryJobConfiguration.of(query)).iterateAll().spliterator(),
            false)
        .map(row -> row.stream().map(FieldValue::getStringValue).collect(Collectors.toList()))
        .collect(Collectors.toList());
    if (actual.size() == sorted.size()) {
      break;
    }
  }

  assertEquals(sorted, actual);
}
 

/** Example of running a query. */
// [TARGET query(QueryJobConfiguration, JobOption...)]
public void runQuery() throws InterruptedException {
  // [START bigquery_query]
  // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
  String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
  QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query).build();

  // Print the results.
  for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
    for (FieldValue val : row) {
      System.out.printf("%s,", val.toString());
    }
    System.out.printf("\n");
  }
  // [END bigquery_query]
}
 

/** Example of running a query with the cache disabled. */
public void runUncachedQuery() throws TimeoutException, InterruptedException {
  // [START bigquery_query_no_cache]
  // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
  String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
  QueryJobConfiguration queryConfig =
      QueryJobConfiguration.newBuilder(query)
          // Disable the query cache to force live query evaluation.
          .setUseQueryCache(false)
          .build();

  // Print the results.
  for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
    for (FieldValue val : row) {
      System.out.printf("%s,", val.toString());
    }
    System.out.printf("\n");
  }
  // [END bigquery_query_no_cache]
}
 

/** Example of running a query and saving the results to a table. */
public void runQueryPermanentTable(String destinationDataset, String destinationTable)
    throws InterruptedException {
  // [START bigquery_query_destination_table]
  // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
  // String destinationDataset = 'my_destination_dataset';
  // String destinationTable = 'my_destination_table';
  String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
  QueryJobConfiguration queryConfig =
      // Note that setUseLegacySql is set to false by default
      QueryJobConfiguration.newBuilder(query)
          // Save the results of the query to a permanent table.
          .setDestinationTable(TableId.of(destinationDataset, destinationTable))
          .build();

  // Print the results.
  for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
    for (FieldValue val : row) {
      System.out.printf("%s,", val.toString());
    }
    System.out.printf("\n");
  }
  // [END bigquery_query_destination_table]
}
 

/** Example of running a Legacy SQL query. */
public void runLegacySqlQuery() throws InterruptedException {
  // [START bigquery_query_legacy]
  // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
  String query = "SELECT corpus FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus;";
  QueryJobConfiguration queryConfig =
      // To use legacy SQL syntax, set useLegacySql to true.
      QueryJobConfiguration.newBuilder(query).setUseLegacySql(true).build();

  // Print the results.
  for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
    for (FieldValue val : row) {
      System.out.printf("%s,", val.toString());
    }
    System.out.printf("\n");
  }
  // [END bigquery_query_legacy]
}
 

@Test
public void fieldsToMap() throws Exception {
  Schema schema = createTestSchema();
  List<FieldValue> fieldValues = createTestValues();

  BigQueryDelegate delegate = new BigQueryDelegate(mockBigquery, useLegacySql);
  LinkedHashMap<String, com.streamsets.pipeline.api.Field> map = delegate.fieldsToMap(schema.getFields(), fieldValues);
  assertTrue(map.containsKey("a"));
  assertEquals("a string", map.get("a").getValueAsString());
  assertArrayEquals("bytes".getBytes(), map.get("b").getValueAsByteArray());
  List<com.streamsets.pipeline.api.Field> c = map.get("c").getValueAsList();
  assertEquals(1L, c.get(0).getValueAsLong());
  assertEquals(2L, c.get(1).getValueAsLong());
  assertEquals(3L, c.get(2).getValueAsLong());
  assertEquals(2.0d, map.get("d").getValueAsDouble(), 1e-15);
  assertEquals(true, map.get("e").getValueAsBoolean());
  assertEquals(new Date(1351700038292L), map.get("f").getValueAsDatetime());
  assertEquals((new SimpleDateFormat("HH:mm:ss.SSS")).parse("08:39:01.123"), map.get("g").getValueAsDatetime());
  assertEquals((new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS")).parse("2019-02-05T23:59:59.123"), map.get("h").getValueAsDatetime());
  assertEquals((new SimpleDateFormat("yyy-MM-dd")).parse("2019-02-05"), map.get("i").getValueAsDate());
  Map<String, com.streamsets.pipeline.api.Field> j = map.get("j").getValueAsListMap();
  assertEquals("nested string", j.get("x").getValueAsString());
  Map<String, com.streamsets.pipeline.api.Field> y = j.get("y").getValueAsListMap();
  assertEquals("z", y.get("z").getValueAsString());
}
 

public Field fromPrimitiveField(com.google.cloud.bigquery.Field field, FieldValue value) {
  Field.Type type = asRecordFieldType(field);
  Field f;
  if (value.isNull()) {
    f = Field.create(type, null);
  } else if (TEMPORAL_TYPES.contains(type)) {
    if (field.getType().getStandardType() == StandardSQLTypeName.TIMESTAMP) {
      // in general only TIMESTAMP should be a Unix epoch value.  However, to be certain will test for getTimeStampValue() and process as as string format if that fails
      f = Field.create(type, value.getTimestampValue() / 1000L); // micro to milli
    } else {
      // not a timestamp value.  Assume it's a date string format
      // Other BigQuery temporal types come as string representations.
      try {
        String dval = value.getStringValue();
        if (dval != null) {
          dval = stripMicrosec.matcher(dval).replaceAll("$1"); // strip out microseconds, for milli precision
        }
        f = Field.create(type, dateTimeFormatter.apply(field).parse(dval));
      } catch (ParseException ex) {
        // In case of failed date/time parsing, simply allow the value to proceed as a string
        LOG.error(String.format("Unable to convert BigQuery field type %s to field type %s", field.getType().toString(), type.toString()), ex);
        f = Field.create(Field.Type.STRING, value.getStringValue()); // allow it to proceed with a null value
        f.setAttribute("bq.parseException", String.format("%s using format %s", ex.getMessage(), dateTimeFormatter.apply(field).toPattern()) );
      }
    }
    if (f != null) f.setAttribute("bq.fullValue", value.getStringValue()); // add the parse error as a field header
  } else if (type == Field.Type.BYTE_ARRAY) {
    f = Field.create(type, value.getBytesValue());
  } else {
    f = Field.create(type, value.getValue());
  }
  return f;
}
 

/**
 * Repeated fields are simply fields that may appear more than once. In SDC we will
 * represent them as a list field. For example a repeated field of type RECORD would be a
 * {@link Field.Type#LIST} of
 * {@link Field.Type#LIST_MAP} and a repeated field of type STRING would be
 * a {@link Field.Type#LIST} of {@link Field.Type#STRING}.
 *
 * @param schema The field metadata for the repeated field
 * @param repeatedValue a list of individual field values that represent the repeated field
 * @return a list of SDC record fields.
 * Intended to be used for creating a {@link Field.Type#LIST} {@link Field}
 */
public List<Field> fromRepeatedField(com.google.cloud.bigquery.Field schema,
    List<FieldValue> repeatedValue) {

  if (repeatedValue.isEmpty()) {
    return Collections.emptyList();
  }
  FieldValue.Attribute repeatedFieldType = repeatedValue.get(0).getAttribute();

  BiFunction<com.google.cloud.bigquery.Field, FieldValue, Field> transform =
      transforms.get(repeatedFieldType);
  return repeatedValue
      .stream()
      .map( v -> transform.apply(schema, v))
      .collect(Collectors.toList());
}
 

/**
 * Converts a list of BigQuery fields to SDC Record fields.
 * The provided parameters must have matching lengths.
 *
 * If not, an unchecked exception will be thrown. This method is called when the resulting
 * container type should be a {@link Field.Type#LIST_MAP}
 *
 * @param schema List of {@link com.google.cloud.bigquery.Field} representing the schema
 * at the current level of nesting. For example, if processing a
 * {@link LegacySQLTypeName#RECORD} or {@link StandardSQLTypeName#STRUCT}
 * this would only include the fields for that particular data structure and not the entire
 * result set.
 *
 * @param values List of {@link FieldValue} representing the values to set in the generated
 * fields.
 * @return Specifically, a LinkedHashMap as the return value of this method is expected to be
 * used to create a
 * {@link Field.Type#LIST_MAP} field.
 */
public LinkedHashMap<String, Field> fieldsToMap( // NOSONAR
    List<com.google.cloud.bigquery.Field> schema,
    List<FieldValue> values
) {
  checkState(
      schema.size() == values.size(),
      "Schema '{}' and Values '{}' sizes do not match.",
      schema.size(),
      values.size()
  );

  LinkedHashMap<String, Field> root = new LinkedHashMap<>();
  for (int i = 0; i < values.size(); i++) {
    FieldValue value = values.get(i);
    com.google.cloud.bigquery.Field field = schema.get(i);
    if (value.getAttribute().equals(FieldValue.Attribute.PRIMITIVE)) {
      root.put(field.getName(), fromPrimitiveField(field, value));
    } else if (value.getAttribute().equals(FieldValue.Attribute.RECORD)) {
      root.put(
          field.getName(),
          Field.create(fieldsToMap(field.getSubFields(), value.getRecordValue()))
      );
    } else if (value.getAttribute().equals(FieldValue.Attribute.REPEATED)) {
      root.put(field.getName(), Field.create(fromRepeatedField(field, value.getRepeatedValue())));
    }
  }
  return root;
}
 

public static FieldValueList createTestValues() {
  return FieldValueList.of(ImmutableList.<FieldValue>builder()
      .add(createFieldValue("a string"))
      .add(createFieldValue("bytes".getBytes()))
      .add(createFieldValue(
          ImmutableList.of(
              createFieldValue(1L),
              createFieldValue(2L),
              createFieldValue(3L)
          ),
          FieldValue.Attribute.REPEATED)
      )
      .add(createFieldValue(2.0d))
      .add(createFieldValue(true))
      .add(createFieldValue(1351700038292387L))
      .add(createFieldValue("08:39:01.123"))
      .add(createFieldValue("2019-02-05T23:59:59.123"))
      .add(createFieldValue("2019-02-05"))
      .add(createFieldValue(
          ImmutableList.of(
              createFieldValue("nested string"),
              createFieldValue(ImmutableList.of(createFieldValue("z")), FieldValue.Attribute.RECORD)
          ),
          FieldValue.Attribute.RECORD
      ))
      .build());
}
 

@SuppressWarnings("unchecked")
private static FieldValue createFieldValue(Object value, FieldValue.Attribute attribute) {
  FieldValue fieldValue = mock(FieldValue.class);
  when(fieldValue.getAttribute()).thenReturn(attribute);
  when(fieldValue.getValue()).thenReturn(value);

  if (value instanceof Long) {
    when(fieldValue.getTimestampValue()).thenReturn((long) value);
  }
  if (value instanceof byte[]) {
    when(fieldValue.getBytesValue()).thenReturn((byte[]) value);
  }


  if (! (attribute.equals(FieldValue.Attribute.RECORD) || attribute.equals(FieldValue.Attribute.REPEATED))) {
    when(fieldValue.getStringValue()).thenReturn(value.toString());
  }

  if (attribute.equals(FieldValue.Attribute.RECORD)) {
    when(fieldValue.getRecordValue()).thenReturn(FieldValueList.of((List<FieldValue>) value));
  }
  if (attribute.equals(FieldValue.Attribute.REPEATED)) {
    when(fieldValue.getRepeatedValue()).thenReturn((List<FieldValue>) value);
  }

  return fieldValue;
}
 

public Map<String, Object> convertFileds(List<FieldValue> fields, org.apache.avro.Schema schema) {
    Map<String, Object> container = new HashMap<>();
    for (int i = 0; i < fields.size(); i++) {
        FieldValue fieldValue = fields.get(i);
        org.apache.avro.Schema.Field fieldMeta = schema.getFields().get(i);
        container.put(fieldMeta.name(), convertField(fieldValue, fieldMeta.schema()));
    }
    return container;
}
 

private Object convertField(FieldValue fieldValue, org.apache.avro.Schema fieldSchema) {
    boolean nullable = AvroUtils.isNullable(fieldSchema);
    if (nullable && fieldValue.isNull()) {
        return null;
    }
    fieldSchema = AvroUtils.unwrapIfNullable(fieldSchema);
    switch (fieldValue.getAttribute()) {
    case PRIMITIVE:
        if (BigQueryType.TIMESTAMP.toString().equals(fieldSchema.getProp(TALEND_COLUMN_DB_TYPE))) {
            Double doubleValue = ((Long) fieldValue.getTimestampValue()) / 1000000.0;
            return formatTimestamp(doubleValue.toString());
        } else if (AvroUtils.isSameType(fieldSchema, AvroUtils._double())) {
            return fieldValue.getDoubleValue();
        } else if (AvroUtils.isSameType(fieldSchema, AvroUtils._boolean())) {
            return fieldValue.getBooleanValue();
        } else {
            return fieldValue.getValue();
        }
    case REPEATED:
        List<Object> listValue = new ArrayList<>();
        List<FieldValue> repeatedChildValue = fieldValue.getRepeatedValue();
        for (FieldValue childValue : repeatedChildValue) {
            listValue.add(convertField(childValue, fieldSchema.getElementType()));
        }
        return listValue;
    case RECORD:
        return convertFileds(fieldValue.getRecordValue(), fieldSchema);
    }
    throw TalendRuntimeException.build(CommonErrorCodes.UNEXPECTED_ARGUMENT).create();
}
 

/** Example of running a query and saving the results to a table. */
public void runQueryLargeResults(String destinationDataset, String destinationTable)
    throws InterruptedException {
  // [START bigquery_query_legacy_large_results]
  // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
  // String destinationDataset = 'my_destination_dataset';
  // String destinationTable = 'my_destination_table';
  String query = "SELECT corpus FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus;";
  QueryJobConfiguration queryConfig =
      // To use legacy SQL syntax, set useLegacySql to true.
      QueryJobConfiguration.newBuilder(query)
          .setUseLegacySql(true)
          // Save the results of the query to a permanent table.
          .setDestinationTable(TableId.of(destinationDataset, destinationTable))
          // Allow results larger than the maximum response size.
          // If true, a destination table must be set.
          .setAllowLargeResults(true)
          .build();

  // Print the results.
  for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
    for (FieldValue val : row) {
      System.out.printf("%s,", val.toString());
    }
    System.out.printf("\n");
  }
  // [END bigquery_query_legacy_large_results]
}
 

private List<String> stringValuesQuery(String query) throws InterruptedException {
  return Lists.newArrayList(bigquery.create(JobInfo.of(QueryJobConfiguration.of(query)))
      .getQueryResults().iterateAll().iterator()).stream().map(fieldValues -> {
        FieldValue field = fieldValues.get(0);
        return field.isNull() ? null : field.getStringValue();
      }).collect(Collectors.toList());
}
 

/** Example of running a query with named query parameters. */
public void runQueryWithNamedParameters() throws InterruptedException {
  // [START bigquery_query_params_named]
  // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
  String corpus = "romeoandjuliet";
  long minWordCount = 250;
  String query =
      "SELECT word, word_count\n"
          + "FROM `bigquery-public-data.samples.shakespeare`\n"
          + "WHERE corpus = @corpus\n"
          + "AND word_count >= @min_word_count\n"
          + "ORDER BY word_count DESC";
  // Note: Standard SQL is required to use query parameters.
  QueryJobConfiguration queryConfig =
      QueryJobConfiguration.newBuilder(query)
          .addNamedParameter("corpus", QueryParameterValue.string(corpus))
          .addNamedParameter("min_word_count", QueryParameterValue.int64(minWordCount))
          .build();

  // Print the results.
  for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
    for (FieldValue val : row) {
      System.out.printf("%s,", val.toString());
    }
    System.out.printf("\n");
  }
  // [END bigquery_query_params_named]
}
 

/** Example of running a query with array query parameters. */
public void runQueryWithArrayParameters() throws InterruptedException {
  // [START bigquery_query_params_arrays]
  // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
  String gender = "M";
  String[] states = {"WA", "WI", "WV", "WY"};
  String query =
      "SELECT name, sum(number) as count\n"
          + "FROM `bigquery-public-data.usa_names.usa_1910_2013`\n"
          + "WHERE gender = @gender\n"
          + "AND state IN UNNEST(@states)\n"
          + "GROUP BY name\n"
          + "ORDER BY count DESC\n"
          + "LIMIT 10;";
  // Note: Standard SQL is required to use query parameters.
  QueryJobConfiguration queryConfig =
      QueryJobConfiguration.newBuilder(query)
          .addNamedParameter("gender", QueryParameterValue.string(gender))
          .addNamedParameter("states", QueryParameterValue.array(states, String.class))
          .build();

  // Print the results.
  for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
    for (FieldValue val : row) {
      System.out.printf("%s,", val.toString());
    }
    System.out.printf("\n");
  }
  // [END bigquery_query_params_arrays]
}
 

private static FieldValue createFieldValue(Object value) {
  return createFieldValue(value, FieldValue.Attribute.PRIMITIVE);
}
 

@Override
public void getSample(int limit, Consumer<IndexedRecord> consumer) {
    BigQuery bigquery = BigQueryConnection.createClient(properties.getDatastoreProperties());
    com.google.cloud.bigquery.Schema bqRowSchema = null;
    String query = null;
    boolean useLegacySql = true;
    switch (properties.sourceType.getValue()) {
    case TABLE_NAME: {
        query = String.format("select * from `%s.%s.%s` LIMIT %d",
                properties.getDatastoreProperties().projectName.getValue(), properties.bqDataset.getValue(),
                properties.tableName.getValue(), limit);
        useLegacySql = false;
        break;
    }
    case QUERY: {
        query = properties.query.getValue();
        useLegacySql = properties.useLegacySql.getValue();
        break;
    }
    default:
        throw new RuntimeException("To be implemented: " + properties.sourceType.getValue());
    }
    QueryJobConfiguration queryRequest = QueryJobConfiguration
            .newBuilder(query)
            .setUseLegacySql(useLegacySql)
            .build();
    // todo: proper pagination, not critical for getSample yet
    TableResult queryResponse =
            query(bigquery, queryRequest, properties.getDatastoreProperties().projectName.getValue());
    bqRowSchema = queryResponse.getSchema();
    Schema schema = BigQueryAvroRegistry.get().inferSchema(bqRowSchema);
    Iterator<FieldValueList> iterator = queryResponse.getValues().iterator();
    IndexedRecordConverter<Map<String, Object>, IndexedRecord> converter =
            new BigQueryFieldValueListIndexedRecordConverter();
    converter.setSchema(schema);
    int count = 0; // need this only for legacy sql with large result
    while (iterator.hasNext() && count < limit) {
        List<FieldValue> values = iterator.next();
        consumer.accept(converter.convertToAvro(BigQueryAvroRegistry.get().convertFileds(values, schema)));
        count++;
    }
}
 

@NewSpan()
public <T> List<T> processQuery(String queryString, Class<T> t, boolean updateCache) throws Exception {

	QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(queryString).build();
	// Create a job ID so that we can safely retry.
	JobId jobId = JobId.of(UUID.randomUUID().toString());
	Job queryJob = bqInstance.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());

	// Wait for the query to complete.
	queryJob = queryJob.waitFor();

	// Check for errors
	if (queryJob == null) {
		throw new RuntimeException("Job no longer exists");
	} else if (queryJob.getStatus().getError() != null) {
		// You can also look at queryJob.getStatus().getExecutionErrors() for all
		// errors, not just the latest one.
		throw new RuntimeException(queryJob.getStatus().getError().toString());
	}

	// Get the results.
	TableResult result = queryJob.getQueryResults();
	// init counters
	long count = 0;
	long total = result.getTotalRows();
	LOGGER.info("Fetched " + total + " products.");
	long start = System.currentTimeMillis();
	// Print all pages of the results.
	List<T> results = new ArrayList<T>();
	// filter
	String filter = "[^A-Za-z0-9 ()-]";
	while (result != null) {
		for (List<FieldValue> row : result.iterateAll()) {
			Object type = t.getConstructor().newInstance();
			String productName = null;
			// query for sku, name
			if (type instanceof Product) {
				productName = row.get(1).getValue() != null
						? row.get(1).getStringValue().replaceAll(filter, "").trim()
						: "";
				if (!updateCache) {
					Product product = new Product();
					product.setSku(row.get(0).getValue().toString());

					product.setName(productName);
					results.add(t.cast(product));
				}
				// query for name
			} else if (type instanceof String) {
				productName = row.get(0).getValue() != null
						? row.get(0).getStringValue().replaceAll(filter, "").trim()
						: "";
				if (!updateCache) {
					results.add(t.cast(productName));
				}
			}

			if (updateCache) {
				getZSetOps().add(productName.toLowerCase() + ":" + productName, 0);
			}
			count++;
		}
		LOGGER.info("Processed " + count + " records..");
		result = result.getNextPage();
	}
	if (updateCache) {
		long actual = getZSetOps().zCard();
		LOGGER.info("Indexing completed for " + count + " products.");
		LOGGER.info("Products in cache: " + actual);
		LOGGER.info("Duplicate product names: " + (count - actual));
		LOGGER.info("Time taken: " + (System.currentTimeMillis() - start) / 1000 + "s.");
	}

	return results;

}