/** * Gets field value by index. * * @param name field name (defined in schema) * @throws IllegalArgumentException if schema is not provided or if {@code name} was not found in * the schema */ public FieldValue get(String name) { if (schema == null) { throw new UnsupportedOperationException( "Retrieving field value by name is not supported when there is no fields schema provided"); } return get(schema.getIndex(name)); }
/** Example of listing rows in the table given a schema. */ // [TARGET list(Schema, TableDataListOption...)] // [VARIABLE ...] // [VARIABLE "my_field"] public Page<FieldValueList> list(Schema schema, String field) { // [START ] Page<FieldValueList> page = table.list(schema); for (FieldValueList row : page.iterateAll()) { row.get(field); } // [END ] return page; }
/** Example of listing table rows with schema. */ // [TARGET listTableData(String, String, Schema, TableDataListOption...)] // [VARIABLE "my_dataset_name"] // [VARIABLE "my_table_name"] // [VARIABLE ...] // [VARIABLE "field"] public TableResult listTableDataSchema( String datasetName, String tableName, Schema schema, String field) { // [START ] TableResult tableData = bigquery.listTableData(datasetName, tableName, schema); for (FieldValueList row : tableData.iterateAll()) { row.get(field); } // [END ] return tableData; }
/** 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] }
@Test public void testGetNonExistentField() { IllegalArgumentException exception = null; try { fieldValues.get("nonexistent"); } catch (IllegalArgumentException e) { exception = e; } assertNotNull(exception); } }
@Test public void testGetByIndex() { assertEquals(10, fieldValues.size()); assertEquals(booleanFv, fieldValues.get(0)); assertEquals(integerFv, fieldValues.get(1)); assertEquals(floatFv, fieldValues.get(2)); assertEquals(stringFv, fieldValues.get(3)); assertEquals(timestampFv, fieldValues.get(4)); assertEquals(bytesFv, fieldValues.get(5)); assertEquals(nullFv, fieldValues.get(6)); assertEquals(repeatedFv, fieldValues.get(7)); assertEquals(2, fieldValues.get(7).getRepeatedValue().size()); assertEquals(integerFv, fieldValues.get(7).getRepeatedValue().get(0)); assertEquals(integerFv, fieldValues.get(7).getRepeatedValue().get(1)); assertEquals(recordFv, fieldValues.get(8)); assertEquals(2, fieldValues.get(8).getRecordValue().size()); assertEquals(floatFv, fieldValues.get(8).getRecordValue().get(0)); assertEquals(timestampFv, fieldValues.get(8).getRecordValue().get(1)); assertEquals(numericFv, fieldValues.get(9)); }
@Test public void testGetByName() { assertEquals(10, fieldValues.size()); assertEquals(booleanFv, fieldValues.get("first")); assertEquals(integerFv, fieldValues.get("second")); assertEquals(floatFv, fieldValues.get("third")); assertEquals(stringFv, fieldValues.get("fourth")); assertEquals(timestampFv, fieldValues.get("fifth")); assertEquals(bytesFv, fieldValues.get("sixth")); assertEquals(nullFv, fieldValues.get("seventh")); assertEquals(repeatedFv, fieldValues.get("eight")); assertEquals(2, fieldValues.get("eight").getRepeatedValue().size()); assertEquals(integerFv, fieldValues.get("eight").getRepeatedValue().get(0)); assertEquals(integerFv, fieldValues.get("eight").getRepeatedValue().get(1)); assertEquals(recordFv, fieldValues.get("ninth")); assertEquals(2, fieldValues.get("ninth").getRecordValue().size()); assertEquals(floatFv, fieldValues.get("ninth").getRecordValue().get("first")); assertEquals(timestampFv, fieldValues.get("ninth").getRecordValue().get("second")); assertEquals(numericFv, fieldValues.get("tenth")); }
/** Example of listing table rows with schema. */ // [TARGET listTableData(TableId, Schema, TableDataListOption...)] public FieldValueList listTableDataSchemaId() { // [START ] Schema schema = Schema.of( Field.of("word", LegacySQLTypeName.STRING), Field.of("word_count", LegacySQLTypeName.STRING), Field.of("corpus", LegacySQLTypeName.STRING), Field.of("corpus_date", LegacySQLTypeName.STRING)); TableResult tableData = bigquery.listTableData( TableId.of("bigquery-public-data", "samples", "shakespeare"), schema); FieldValueList row = tableData.getValues().iterator().next(); System.out.println(row.get("word").getStringValue()); // [END ] return row; }
@Test public void testNullSchema() { FieldValueList fieldValuesNoSchema = FieldValueList.of( ImmutableList.of( booleanFv, integerFv, floatFv, stringFv, timestampFv, bytesFv, nullFv, repeatedFv, recordFv, numericFv)); assertEquals(fieldValues, fieldValuesNoSchema); UnsupportedOperationException exception = null; try { fieldValuesNoSchema.get("first"); } catch (UnsupportedOperationException e) { exception = e; } assertNotNull(exception); }
int rowCount = 0; for (FieldValueList row : rows.getValues()) { FieldValue timestampCell = row.get(0); FieldValue stringCell = row.get(1); FieldValue integerArrayCell = row.get(2); FieldValue booleanCell = row.get(3); FieldValue bytesCell = row.get(4); FieldValue recordCell = row.get(5); FieldValue integerCell = row.get(6); FieldValue floatCell = row.get(7); FieldValue numericCell = row.get(8); assertEquals(FieldValue.Attribute.PRIMITIVE, timestampCell.getAttribute()); assertEquals(FieldValue.Attribute.PRIMITIVE, stringCell.getAttribute()); 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(0, recordCell.getRecordValue().get(2).getRepeatedValue().get(1).getLongValue()); assertEquals(true, recordCell.getRecordValue().get(3).getBooleanValue()); assertEquals(3, integerCell.getLongValue()); assertEquals(1.2, floatCell.getDoubleValue(), 0.0001);
@Test public void testBytesParameter() throws Exception { String query = "SELECT BYTE_LENGTH(@p) AS length"; QueryParameterValue bytesParameter = QueryParameterValue.bytes(new byte[] {1, 3}); QueryJobConfiguration config = QueryJobConfiguration.newBuilder(query) .setDefaultDataset(DatasetId.of(DATASET)) .setUseLegacySql(false) .addNamedParameter("p", bytesParameter) .build(); TableResult result = bigquery.query(config); int rowCount = 0; for (FieldValueList row : result.getValues()) { rowCount++; assertEquals(2, row.get(0).getLongValue()); assertEquals(2, row.get("length").getLongValue()); } assertEquals(1, rowCount); }
@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()); }
int rowCount = 0; for (FieldValueList row : rows.getValues()) { FieldValue timestampCell = row.get(0); FieldValue stringCell = row.get(1); FieldValue integerArrayCell = row.get(2); FieldValue booleanCell = row.get(3); FieldValue bytesCell = row.get(4); FieldValue recordCell = row.get(5); FieldValue integerCell = row.get(6); FieldValue floatCell = row.get(7); FieldValue numericCell = row.get(8); assertEquals(FieldValue.Attribute.PRIMITIVE, timestampCell.getAttribute()); assertEquals(FieldValue.Attribute.PRIMITIVE, stringCell.getAttribute()); 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(0, recordCell.getRecordValue().get(2).getRepeatedValue().get(1).getLongValue()); assertEquals(true, recordCell.getRecordValue().get(3).getBooleanValue()); assertEquals(3, integerCell.getLongValue()); assertEquals(1.2, floatCell.getDoubleValue(), 0.0001);
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 integerCell = row.get(2); assertEquals(integerCell, row.get("IntegerArrayField")); FieldValue booleanCell = row.get(3); assertEquals(booleanCell, row.get("BooleanField"));
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());
int rowCount = 0; for (FieldValueList row : result.getValues()) { FieldValue timestampCell = row.get(0); FieldValue stringCell = row.get(1); FieldValue booleanCell = row.get(2); assertEquals(FieldValue.Attribute.PRIMITIVE, timestampCell.getAttribute()); assertEquals(FieldValue.Attribute.PRIMITIVE, stringCell.getAttribute());