Examples with BigQueryResult com.google.cloud.bigquery.BigQueryResult used on opensource projects

Example 11 with BigQueryResult

use of com.google.cloud.bigquery.BigQueryResult in project java-bigquery by googleapis.

the class ITBigQueryTest method testReadAPIConnectionMultiClose.

@Test
public void testReadAPIConnectionMultiClose() throws SQLException {
    // use read API to read 300K records, then closes the connection. This test
    // repeats it multiple times and assets if the connection was closed
    String query = "SELECT date, county, state_name, confirmed_cases, deaths FROM " + TABLE_ID_LARGE.getTable() + " where date is not null and county is not null and state_name is not null order by confirmed_cases asc limit 300000";
    ConnectionSettings connectionSettings = ConnectionSettings.newBuilder().setDefaultDataset(DatasetId.of(DATASET)).setPriority(QueryJobConfiguration.Priority.INTERACTIVE).build();
    int closeCnt = 0, runCnt = 3;
    for (int run = 0; run < runCnt; run++) {
        Connection connection = bigquery.createConnection(connectionSettings);
        BigQueryResult bigQueryResult = connection.executeSelect(query);
        ResultSet rs = bigQueryResult.getResultSet();
        int cnt = 0;
        while (rs.next()) {
            // pagination starts after approx 120,000 records
            assertNotNull(rs.getDate(0));
            ++cnt;
        }
        // total 300000 rows should be read
        assertEquals(300000, cnt);
        // check if connection closed
        assertTrue(connection.close());
        closeCnt++;
    }
    assertEquals(closeCnt, // check if the connection closed for the required number of times
    runCnt);
}

Example 12 with BigQueryResult

use of com.google.cloud.bigquery.BigQueryResult in project java-bigquery by googleapis.

the class ITBigQueryTest method testExecuteSelectStruct.

@Test
public void testExecuteSelectStruct() throws SQLException {
    String query = "select (STRUCT(\"Vancouver\" as city, 5 as years)) as address";
    ConnectionSettings connectionSettings = ConnectionSettings.newBuilder().setDefaultDataset(DatasetId.of(DATASET)).build();
    Connection connection = bigquery.createConnection(connectionSettings);
    BigQueryResult bigQueryResult = connection.executeSelect(query);
    assertEquals(1, bigQueryResult.getTotalRows());
    Schema schema = bigQueryResult.getSchema();
    assertEquals("address", schema.getFields().get(0).getName());
    assertEquals(Field.Mode.NULLABLE, schema.getFields().get(0).getMode());
    // Backend is currently returning LegacySQLTypeName. Tracking bug: b/202977620
    assertEquals(LegacySQLTypeName.RECORD, schema.getFields().get(0).getType());
    assertEquals("city", schema.getFields().get(0).getSubFields().get(0).getName());
    assertEquals(LegacySQLTypeName.STRING, schema.getFields().get(0).getSubFields().get(0).getType());
    assertEquals(Field.Mode.NULLABLE, schema.getFields().get(0).getSubFields().get(0).getMode());
    assertEquals("years", schema.getFields().get(0).getSubFields().get(1).getName());
    assertEquals(LegacySQLTypeName.INTEGER, schema.getFields().get(0).getSubFields().get(1).getType());
    assertEquals(Field.Mode.NULLABLE, schema.getFields().get(0).getSubFields().get(1).getMode());
    ResultSet rs = bigQueryResult.getResultSet();
    assertTrue(rs.next());
    FieldValueList addressFieldValue = (com.google.cloud.bigquery.FieldValueList) rs.getObject("address");
    assertEquals(rs.getObject("address"), rs.getObject(0));
    assertEquals("Vancouver", addressFieldValue.get(0).getStringValue());
    assertEquals(5, addressFieldValue.get(1).getLongValue());
    // only 1 row of data
    assertFalse(rs.next());
}

Example 13 with BigQueryResult

use of com.google.cloud.bigquery.BigQueryResult in project java-bigquery by googleapis.

the class ITBigQueryTest method testExecuteSelectWithNamedQueryParameters.

@Test
public void testExecuteSelectWithNamedQueryParameters() throws BigQuerySQLException {
    String query = "SELECT TimestampField, StringField, BooleanField FROM " + TABLE_ID.getTable() + " WHERE StringField = @stringParam" + " AND IntegerField IN UNNEST(@integerList)";
    QueryParameterValue stringParameter = QueryParameterValue.string("stringValue");
    QueryParameterValue intArrayParameter = QueryParameterValue.array(new Integer[] { 3, 4 }, Integer.class);
    Parameter stringParam = Parameter.newBuilder().setName("stringParam").setValue(stringParameter).build();
    Parameter intArrayParam = Parameter.newBuilder().setName("integerList").setValue(intArrayParameter).build();
    ConnectionSettings connectionSettings = ConnectionSettings.newBuilder().setDefaultDataset(DatasetId.of(DATASET)).build();
    Connection connection = bigquery.createConnection(connectionSettings);
    List<Parameter> parameters = ImmutableList.of(stringParam, intArrayParam);
    BigQueryResult rs = connection.executeSelect(query, parameters);
    assertEquals(2, rs.getTotalRows());
}

Example 14 with BigQueryResult

use of com.google.cloud.bigquery.BigQueryResult in project java-bigquery by googleapis.

the class ITBigQueryTest method testExecuteSelectSinglePageTableRow.

@Test
public void testExecuteSelectSinglePageTableRow() throws SQLException {
    String query = "select StringField,  BigNumericField, BooleanField, BytesField, IntegerField, TimestampField, FloatField, " + "NumericField, TimeField, DateField,  DateTimeField , GeographyField, RecordField.BytesField, RecordField.BooleanField, IntegerArrayField from " + TABLE_ID_FASTQUERY_BQ_RESULTSET.getTable() + " order by TimestampField";
    ConnectionSettings connectionSettings = ConnectionSettings.newBuilder().setDefaultDataset(DatasetId.of(DATASET)).build();
    Connection connection = bigquery.createConnection(connectionSettings);
    BigQueryResult bigQueryResult = connection.executeSelect(query);
    ResultSet rs = bigQueryResult.getResultSet();
    Schema sc = bigQueryResult.getSchema();
    // match the schema
    assertEquals(BQ_RESULTSET_EXPECTED_SCHEMA, sc);
    // Expecting 2 rows
    assertEquals(2, bigQueryResult.getTotalRows());
    // first row
    assertTrue(rs.next());
    // checking for the null or 0 column values
    assertNull(rs.getString("StringField"));
    assertTrue(rs.getDouble("BigNumericField") == 0.0d);
    assertFalse(rs.getBoolean("BooleanField"));
    assertNull(rs.getBytes("BytesField"));
    assertEquals(rs.getInt("IntegerField"), 0);
    assertNull(rs.getTimestamp("TimestampField"));
    assertNull(rs.getDate("DateField"));
    assertTrue(rs.getDouble("FloatField") == 0.0d);
    assertTrue(rs.getDouble("NumericField") == 0.0d);
    assertNull(rs.getTime("TimeField"));
    assertNull(rs.getString("DateTimeField"));
    assertNull(rs.getString("GeographyField"));
    assertNull(rs.getBytes("BytesField_1"));
    assertFalse(rs.getBoolean("BooleanField_1"));
    // second row
    assertTrue(rs.next());
    // second row is non null, comparing the values
    assertEquals("StringValue1", rs.getString("StringField"));
    assertTrue(rs.getDouble("BigNumericField") == 0.3333333333333333d);
    assertFalse(rs.getBoolean("BooleanField"));
    assertNotNull(rs.getBytes("BytesField"));
    assertEquals(1, rs.getInt("IntegerField"));
    assertEquals(1534680695123L, rs.getTimestamp("TimestampField").getTime());
    assertEquals(java.sql.Date.valueOf("2018-08-19"), rs.getDate("DateField"));
    assertTrue(rs.getDouble("FloatField") == 10.1d);
    assertTrue(rs.getDouble("NumericField") == 100.0d);
    assertEquals(Time.valueOf(LocalTime.of(12, 11, 35, 123456)), rs.getTime("TimeField"));
    assertEquals("2018-08-19T12:11:35.123456", rs.getString("DateTimeField"));
    assertEquals("POINT(-122.35022 47.649154)", rs.getString("GeographyField"));
    assertNotNull(rs.getBytes("BytesField_1"));
    assertTrue(rs.getBoolean("BooleanField_1"));
    assertTrue(rs.getObject("IntegerArrayField") instanceof com.google.cloud.bigquery.FieldValueList);
    FieldValueList integerArrayFieldValue = (com.google.cloud.bigquery.FieldValueList) rs.getObject("IntegerArrayField");
    // Array has 4 elements
    assertEquals(4, integerArrayFieldValue.size());
    assertEquals(3, (integerArrayFieldValue.get(2).getNumericValue()).intValue());
    // no 3rd row in the table
    assertFalse(rs.next());
}

Example 15 with BigQueryResult

use of com.google.cloud.bigquery.BigQueryResult in project java-bigquery by googleapis.

the class ITBigQueryTest method testExecuteSelectArrayOfStruct.

@Test
public void testExecuteSelectArrayOfStruct() throws SQLException {
    String query = "SELECT [STRUCT(\"Vancouver\" as city, 5 as years), STRUCT(\"Boston\" as city, 10 as years)]";
    ConnectionSettings connectionSettings = ConnectionSettings.newBuilder().setDefaultDataset(DatasetId.of(DATASET)).build();
    Connection connection = bigquery.createConnection(connectionSettings);
    BigQueryResult bigQueryResult = connection.executeSelect(query);
    assertEquals(1, bigQueryResult.getTotalRows());
    Schema schema = bigQueryResult.getSchema();
    assertEquals("f0_", schema.getFields().get(0).getName());
    assertEquals(Field.Mode.REPEATED, schema.getFields().get(0).getMode());
    // Backend is currently returning LegacySQLTypeName. Tracking bug: b/202977620
    // Verify the field metadata of the two subfields of the struct
    assertEquals(LegacySQLTypeName.RECORD, schema.getFields().get(0).getType());
    assertEquals("city", schema.getFields().get(0).getSubFields().get(0).getName());
    assertEquals(LegacySQLTypeName.STRING, schema.getFields().get(0).getSubFields().get(0).getType());
    assertEquals(Field.Mode.NULLABLE, schema.getFields().get(0).getSubFields().get(0).getMode());
    assertEquals("years", schema.getFields().get(0).getSubFields().get(1).getName());
    assertEquals(LegacySQLTypeName.INTEGER, schema.getFields().get(0).getSubFields().get(1).getType());
    assertEquals(Field.Mode.NULLABLE, schema.getFields().get(0).getSubFields().get(1).getMode());
    ResultSet rs = bigQueryResult.getResultSet();
    assertTrue(rs.next());
    FieldValueList arrayOfStructFieldValue = (com.google.cloud.bigquery.FieldValueList) rs.getObject(0);
    // Verify the values of the two structs in the array
    assertEquals(Attribute.RECORD, arrayOfStructFieldValue.get(0).getAttribute());
    assertEquals("Vancouver", arrayOfStructFieldValue.get(0).getRecordValue().get(0).getStringValue());
    assertEquals(5, arrayOfStructFieldValue.get(0).getRecordValue().get(1).getLongValue());
    assertEquals(Attribute.RECORD, arrayOfStructFieldValue.get(1).getAttribute());
    assertEquals("Boston", arrayOfStructFieldValue.get(1).getRecordValue().get(0).getStringValue());
    assertEquals(10, arrayOfStructFieldValue.get(1).getRecordValue().get(1).getLongValue());
}