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

Example 1 with Connection

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

the class ITBigQueryTest method testConnectionClose.

@Test
public void testConnectionClose() throws SQLException {
    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 date limit 300000";
    ConnectionSettings connectionSettings = ConnectionSettings.newBuilder().setDefaultDataset(DatasetId.of(DATASET)).setNumBufferedRows(// page size
    10000).build();
    Connection connection = bigquery.createConnection(connectionSettings);
    BigQueryResult bigQueryResult = connection.executeSelect(query);
    ResultSet rs = bigQueryResult.getResultSet();
    int cnt = 0;
    while (rs.next()) {
        ++cnt;
        if (cnt == 57000) {
            // breaking at 57000th record, query reads 300K
            // we should be able to cancel the connection
            assertTrue(connection.close());
        }
    }
    // Extra records are still read even after canceling, as
    assertTrue(cnt < 100000);
// the backgrounds threads are still active while the interrupt occurs and the
// buffer and pageCache are cleared
}

Example 2 with Connection

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

the class ITBigQueryTest method testExecuteSelectStructSubField.

@Test
public void testExecuteSelectStructSubField() throws SQLException {
    String query = "select address.city from (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("city", 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.STRING, schema.getFields().get(0).getType());
    assertNull(// this is a String field without any subfields
    schema.getFields().get(0).getSubFields());
    ResultSet rs = bigQueryResult.getResultSet();
    assertTrue(rs.next());
    String cityFieldValue = rs.getString("city");
    assertEquals(rs.getString("city"), rs.getObject(0));
    assertEquals("Vancouver", cityFieldValue);
    // only 1 row of data
    assertFalse(rs.next());
}

Example 3 with Connection

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

the class ITBigQueryTest method testExecuteSelectSessionSupport.

// TODO: uncomment this testcase when executeUpdate is implemented
// @Test
// public void testExecuteSelectWithSession() throws BigQuerySQLException {
// String query = "CREATE TEMPORARY TABLE temptable AS SELECT 17 as foo";
// ConnectionSettings connectionSettings =
// ConnectionSettings.newBuilder().setDefaultDataset(DatasetId.of(DATASET)).setCreateSession(true).build();
// Connection connection = bigquery.createConnection(connectionSettings);
// BigQueryResult bigQueryResult = connection.execute(query);
// BigQueryResultStats stats = bigQueryResult.getBigQueryResultStats();
// assertNotNull(stats.getSessionInfo().getSessionId());
// }
@Test
public void testExecuteSelectSessionSupport() throws BigQuerySQLException {
    String query = "SELECT 17 as foo";
    ConnectionSettings connectionSettings = ConnectionSettings.newBuilder().setDefaultDataset(DatasetId.of(DATASET)).setCreateSession(true).build();
    Connection connection = bigquery.createConnection(connectionSettings);
    BigQueryResult bigQueryResult = connection.executeSelect(query);
    String sessionId = bigQueryResult.getBigQueryResultStats().getSessionInfo().getSessionId();
    assertNotNull(sessionId);
}

Example 4 with Connection

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

the class ITBigQueryTest method testExecuteSelectSinglePageTableRowColInd.

@Test
public void testExecuteSelectSinglePageTableRowColInd() 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";
    /*
    Column Index mapping for ref:
    StringField,  0    BigNumericField, 1    BooleanField,  2   BytesField,  3    IntegerField, 4   TimestampField, 5   FloatField, " 6
    NumericField, 7    TimeField, 8     DateField,  9    DateTimeField , 10   GeographyField, 11    RecordField.BytesField, 12     RecordField.BooleanField, 13   IntegerArrayField 14
         */
    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());
    while (rs.next()) {
        assertEquals(rs.getString(0), rs.getString("StringField"));
        assertTrue(rs.getDouble(1) == rs.getDouble("BigNumericField"));
        assertEquals(rs.getBoolean(2), rs.getBoolean("BooleanField"));
        if (rs.getBytes(3) == null) {
            // both overloads should be null
            assertEquals(rs.getBytes(3), rs.getBytes("BytesField"));
        } else {
            // value in String representation should be the same
            assertEquals(new String(rs.getBytes(3), StandardCharsets.UTF_8), new String(rs.getBytes("BytesField"), StandardCharsets.UTF_8));
        }
        assertEquals(rs.getInt(4), rs.getInt("IntegerField"));
        assertEquals(rs.getTimestamp(5), rs.getTimestamp("TimestampField"));
        assertEquals(rs.getDate(9), rs.getDate("DateField"));
        assertTrue(rs.getDouble("FloatField") == rs.getDouble(6));
        assertTrue(rs.getDouble("NumericField") == rs.getDouble(7));
        assertEquals(rs.getTime(8), rs.getTime("TimeField"));
        assertEquals(rs.getString(10), rs.getString("DateTimeField"));
        assertEquals(rs.getString(11), rs.getString("GeographyField"));
        if (rs.getBytes(12) == null) {
            // both overloads should be null
            assertEquals(rs.getBytes(12), rs.getBytes("BytesField_1"));
        } else {
            // value in String representation should be the same
            assertEquals(new String(rs.getBytes(12), StandardCharsets.UTF_8), new String(rs.getBytes("BytesField_1"), StandardCharsets.UTF_8));
        }
        assertEquals(rs.getBoolean(13), rs.getBoolean("BooleanField_1"));
        assertTrue(rs.getObject("IntegerArrayField") instanceof com.google.cloud.bigquery.FieldValueList);
        FieldValueList integerArrayFieldValue = (com.google.cloud.bigquery.FieldValueList) rs.getObject("IntegerArrayField");
        assertTrue(rs.getObject(14) instanceof com.google.cloud.bigquery.FieldValueList);
        FieldValueList integerArrayFieldValueColInd = (com.google.cloud.bigquery.FieldValueList) rs.getObject(14);
        assertEquals(integerArrayFieldValue.size(), // Array has 4 elements
        integerArrayFieldValueColInd.size());
        if (integerArrayFieldValue.size() == 4) {
            // as we are picking the third index
            assertEquals((integerArrayFieldValue.get(2).getNumericValue()).intValue(), (integerArrayFieldValueColInd.get(2).getNumericValue()).intValue());
        }
    }
}

Example 5 with Connection

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

the class ITBigQueryTest method testReadAPIIterationAndOrder.

@Test
public void testReadAPIIterationAndOrder() throws SQLException {
    // use read API to read 300K records and check the order
    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();
    Connection connection = bigquery.createConnection(connectionSettings);
    BigQueryResult bigQueryResult = connection.executeSelect(query);
    ResultSet rs = bigQueryResult.getResultSet();
    int cnt = 0;
    int lasConfirmedCases = Integer.MIN_VALUE;
    while (rs.next()) {
        // pagination starts after approx 120,000 records
        assertNotNull(rs.getDate(0));
        assertNotNull(rs.getString(1));
        assertNotNull(rs.getString(2));
        assertTrue(rs.getInt(3) >= 0);
        assertTrue(rs.getInt(4) >= 0);
        // check if the records are sorted
        assertTrue(rs.getInt(3) >= lasConfirmedCases);
        lasConfirmedCases = rs.getInt(3);
        ++cnt;
    }
    // total 300000 rows should be read
    assertEquals(300000, cnt);
    connection.close();
}