/** Example of running a query with timestamp query parameters. */ public void runQueryWithTimestampParameters() throws InterruptedException { // [START bigquery_query_params_timestamps] // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService(); DateTime timestamp = new DateTime(2016, 12, 7, 8, 0, 0, DateTimeZone.UTC); String query = "SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);"; // Note: Standard SQL is required to use query parameters. QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query) .addNamedParameter( "ts_value", QueryParameterValue.timestamp( // Timestamp takes microseconds since 1970-01-01T00:00:00 UTC timestamp.getMillis() * 1000)) .build(); // Print the results. DateTimeFormatter formatter = ISODateTimeFormat.dateTimeNoMillis().withZoneUTC(); for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) { System.out.printf( "%s\n", formatter.print( new DateTime( // Timestamp values are returned in microseconds since 1970-01-01T00:00:00 UTC, // but org.joda.time.DateTime constructor accepts times in milliseconds. row.get(0).getTimestampValue() / 1000, DateTimeZone.UTC))); System.out.printf("\n"); } // [END bigquery_query_params_timestamps] }
assertEquals(FieldValue.Attribute.PRIMITIVE, floatCell.getAttribute()); assertEquals(FieldValue.Attribute.PRIMITIVE, numericCell.getAttribute()); assertEquals(1408452095220000L, timestampCell.getTimestampValue()); assertEquals("stringValue", stringCell.getStringValue()); assertEquals(0, integerArrayCell.getRepeatedValue().get(0).getLongValue()); assertEquals(false, booleanCell.getBooleanValue()); assertArrayEquals(BYTES, bytesCell.getBytesValue()); assertEquals(-14182916000000L, recordCell.getRecordValue().get(0).getTimestampValue()); assertTrue(recordCell.getRecordValue().get(1).isNull()); assertEquals(1, recordCell.getRecordValue().get(2).getRepeatedValue().get(0).getLongValue());
assertEquals(FieldValue.Attribute.PRIMITIVE, floatCell.getAttribute()); assertEquals(FieldValue.Attribute.PRIMITIVE, numericCell.getAttribute()); assertEquals(1408452095220000L, timestampCell.getTimestampValue()); assertEquals("stringValue", stringCell.getStringValue()); assertEquals(0, integerArrayCell.getRepeatedValue().get(0).getLongValue()); assertEquals(false, booleanCell.getBooleanValue()); assertArrayEquals(BYTES, bytesCell.getBytesValue()); assertEquals(-14182916000000L, recordCell.getRecordValue().get(0).getTimestampValue()); assertTrue(recordCell.getRecordValue().get(1).isNull()); assertEquals(1, recordCell.getRecordValue().get(2).getRepeatedValue().get(0).getLongValue());
value = FieldValue.fromPb(TIMESTAMP_FIELD); assertEquals(FieldValue.Attribute.PRIMITIVE, value.getAttribute()); assertEquals(42000000, value.getTimestampValue()); value = FieldValue.fromPb(BYTES_FIELD); assertEquals(FieldValue.Attribute.PRIMITIVE, value.getAttribute());
@Test public void testQuery() throws InterruptedException { String query = "SELECT TimestampField, StringField, BooleanField FROM " + TABLE_ID.getTable(); QueryJobConfiguration config = QueryJobConfiguration.newBuilder(query).setDefaultDataset(DatasetId.of(DATASET)).build(); Job job = bigquery.create(JobInfo.of(JobId.of(), config)); TableResult result = job.getQueryResults(); assertEquals(QUERY_RESULT_SCHEMA, result.getSchema()); int rowCount = 0; for (FieldValueList row : result.getValues()) { FieldValue timestampCell = row.get(0); assertEquals(timestampCell, row.get("TimestampField")); FieldValue stringCell = row.get(1); assertEquals(stringCell, row.get("StringField")); FieldValue booleanCell = row.get(2); assertEquals(booleanCell, row.get("BooleanField")); assertEquals(FieldValue.Attribute.PRIMITIVE, timestampCell.getAttribute()); assertEquals(FieldValue.Attribute.PRIMITIVE, stringCell.getAttribute()); assertEquals(FieldValue.Attribute.PRIMITIVE, booleanCell.getAttribute()); assertEquals(1408452095220000L, timestampCell.getTimestampValue()); assertEquals("stringValue", stringCell.getStringValue()); assertEquals(false, booleanCell.getBooleanValue()); rowCount++; } assertEquals(2, rowCount); Job job2 = bigquery.getJob(job.getJobId()); JobStatistics.QueryStatistics statistics = job2.getStatistics(); assertNotNull(statistics.getQueryPlan()); }
assertEquals(FieldValue.Attribute.PRIMITIVE, stringCell.getAttribute()); assertEquals(FieldValue.Attribute.PRIMITIVE, booleanCell.getAttribute()); assertEquals(1408452095220000L, timestampCell.getTimestampValue()); assertEquals("stringValue", stringCell.getStringValue()); assertEquals(false, booleanCell.getBooleanValue());
assertEquals(FieldValue.Attribute.PRIMITIVE, stringCell.getAttribute()); assertEquals(FieldValue.Attribute.PRIMITIVE, booleanCell.getAttribute()); assertEquals(1408452095220000L, timestampCell.getTimestampValue()); assertEquals("stringValue", stringCell.getStringValue()); assertEquals(false, booleanCell.getBooleanValue());
assertEquals(FieldValue.Attribute.PRIMITIVE, integerCell.getAttribute()); assertEquals(FieldValue.Attribute.PRIMITIVE, booleanCell.getAttribute()); assertEquals(1408452095220000L, timestampCell.getTimestampValue()); assertEquals("stringValue", stringCell.getStringValue()); assertEquals(integerValue, integerCell.getLongValue());
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(); }
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(); }