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

Example 16 with Connection

use of com.google.cloud.bigquery.Connection 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 17 with Connection

use of com.google.cloud.bigquery.Connection 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());
}

Example 18 with Connection

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

the class ITBigQueryTest method testExecuteSelectArray.

@Test
public void testExecuteSelectArray() throws SQLException {
    String query = "SELECT [1,2,3]";
    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());
    assertEquals(LegacySQLTypeName.INTEGER, schema.getFields().get(0).getType());
    // no subfields for Integers
    assertNull(schema.getFields().get(0).getSubFields());
    ResultSet rs = bigQueryResult.getResultSet();
    assertTrue(rs.next());
    FieldValueList arrayFieldValue = (com.google.cloud.bigquery.FieldValueList) rs.getObject(0);
    assertEquals(1, arrayFieldValue.get(0).getLongValue());
    assertEquals(2, arrayFieldValue.get(1).getLongValue());
    assertEquals(3, arrayFieldValue.get(2).getLongValue());
}

Example 19 with Connection

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

the class ITBigQueryTest method testExecuteSelectWithPositionalQueryParameters.

/* TODO(prasmish): expand below test case with all the fields shown in the above test case */
@Test
public void testExecuteSelectWithPositionalQueryParameters() throws BigQuerySQLException {
    String query = "SELECT TimestampField, StringField FROM " + TABLE_ID.getTable() + " WHERE StringField = ?" + " AND TimestampField > ?";
    QueryParameterValue stringParameter = QueryParameterValue.string("stringValue");
    QueryParameterValue timestampParameter = QueryParameterValue.timestamp("2014-01-01 07:00:00.000000+00:00");
    Parameter stringParam = Parameter.newBuilder().setValue(stringParameter).build();
    Parameter timeStampParam = Parameter.newBuilder().setValue(timestampParameter).build();
    ConnectionSettings connectionSettings = ConnectionSettings.newBuilder().setDefaultDataset(DatasetId.of(DATASET)).build();
    Connection connection = bigquery.createConnection(connectionSettings);
    List<Parameter> parameters = ImmutableList.of(stringParam, timeStampParam);
    BigQueryResult rs = connection.executeSelect(query, parameters);
    assertEquals(2, rs.getTotalRows());
}

Example 20 with Connection

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

the class ITNightlyBigQueryTest method testIterateAndOrder.

/*
  This tests for the order of the records as well as the value of the records using testForAllDataTypeValues
   */
@Test
public void testIterateAndOrder() throws SQLException {
    Connection connection = getConnection();
    BigQueryResult bigQueryResult = connection.executeSelect(QUERY);
    logger.log(Level.INFO, "Query used: {0}", QUERY);
    ResultSet rs = bigQueryResult.getResultSet();
    int cnt = 0;
    int prevIntegerFieldVal = 0;
    while (rs.next()) {
        if (cnt == 0) {
            // first row is supposed to be null
            assertNull(rs.getString("StringField"));
            assertNull(rs.getString("GeographyField"));
            Object intAryField = rs.getObject("IntegerArrayField");
            if (intAryField instanceof JsonStringArrayList) {
                assertEquals(new JsonStringArrayList(), // null array is returned as an empty array
                ((JsonStringArrayList) intAryField));
            }
            assertFalse(rs.getBoolean("BooleanField"));
            assertTrue(0.0d == rs.getDouble("BigNumericField"));
            assertTrue(0 == rs.getInt("IntegerField"));
            assertTrue(0L == rs.getLong("NumericField"));
            assertNull(rs.getBytes("BytesField"));
            assertNull(rs.getTimestamp("TimestampField"));
            assertNull(rs.getTime("TimeField"));
            assertNull(rs.getDate("DateField"));
            assertNull(rs.getString("JSONField"));
            assertFalse(rs.getBoolean("BooleanField_1"));
            assertNull(rs.getString("StringField_1"));
            // equivalent of testJsonType
            assertNull(rs.getString("hello"));
            assertEquals(0, rs.getInt("id"));
        } else {
            // remaining rows are supposed to be non null
            assertNotNull(rs.getString("StringField"));
            assertNotNull(rs.getString("GeographyField"));
            assertNotNull(rs.getObject("IntegerArrayField"));
            assertTrue(rs.getBoolean("BooleanField"));
            assertTrue(0.0d < rs.getDouble("BigNumericField"));
            assertTrue(0 < rs.getInt("IntegerField"));
            assertTrue(0L < rs.getLong("NumericField"));
            assertNotNull(rs.getBytes("BytesField"));
            assertNotNull(rs.getTimestamp("TimestampField"));
            assertNotNull(rs.getTime("TimeField"));
            assertNotNull(rs.getDate("DateField"));
            assertNotNull(rs.getString("JSONField"));
            assertFalse(rs.getBoolean("BooleanField_1"));
            assertNotNull(rs.getString("StringField_1"));
            // check the order of the records
            assertTrue(prevIntegerFieldVal < rs.getInt("IntegerField"));
            prevIntegerFieldVal = rs.getInt("IntegerField");
            // asserts the value of each row
            testForAllDataTypeValues(rs, cnt);
        }
        ++cnt;
    }
    // all the records were retrieved
    assertEquals(LIMIT_RECS, cnt);
    connection.close();
}