Search in sources :

Example 16 with BigQueryResult

use of com.google.cloud.bigquery.BigQueryResult 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());
}
Also used : Schema(com.google.cloud.bigquery.Schema) Connection(com.google.cloud.bigquery.Connection) ResultSet(java.sql.ResultSet) BigQueryResult(com.google.cloud.bigquery.BigQueryResult) FieldValueList(com.google.cloud.bigquery.FieldValueList) ConnectionSettings(com.google.cloud.bigquery.ConnectionSettings) Test(org.junit.Test)

Example 17 with BigQueryResult

use of com.google.cloud.bigquery.BigQueryResult 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());
}
Also used : QueryParameterValue(com.google.cloud.bigquery.QueryParameterValue) Connection(com.google.cloud.bigquery.Connection) Parameter(com.google.cloud.bigquery.Parameter) BigQueryResult(com.google.cloud.bigquery.BigQueryResult) ConnectionSettings(com.google.cloud.bigquery.ConnectionSettings) Test(org.junit.Test)

Example 18 with BigQueryResult

use of com.google.cloud.bigquery.BigQueryResult 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();
}
Also used : Connection(com.google.cloud.bigquery.Connection) ResultSet(java.sql.ResultSet) BigQueryResult(com.google.cloud.bigquery.BigQueryResult) JsonStringArrayList(org.apache.arrow.vector.util.JsonStringArrayList) Test(org.junit.Test)

Example 19 with BigQueryResult

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

the class ITNightlyBigQueryTest method testConnectionClose.

/*
  This tests interrupts the execution in between and checks if it has been interrupted successfully while using ReadAPI
   */
@Test
public void testConnectionClose() throws SQLException {
    Connection connection = bigquery.createConnection();
    assertNotNull("bigquery.createConnection() returned null", connection);
    BigQueryResult bigQueryResult = connection.executeSelect(QUERY);
    logger.log(Level.INFO, "Query used: {0}", QUERY);
    ResultSet rs = bigQueryResult.getResultSet();
    int cnt = 0;
    while (rs.next()) {
        ++cnt;
        if (cnt == 50000) {
            // interrupt at 50K
            assertTrue(connection.close());
        }
    }
    assertTrue(LIMIT_RECS > // we stopped at 50K but still we can expect additional records (typically ~100)
    cnt);
// to be retrieved
// as a number of records should have been already buffered. less than
// LIMIT_RECS should be retrieved
}
Also used : Connection(com.google.cloud.bigquery.Connection) ResultSet(java.sql.ResultSet) BigQueryResult(com.google.cloud.bigquery.BigQueryResult) Test(org.junit.Test)

Example 20 with BigQueryResult

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

the class ITNightlyBigQueryTest method testPositionalParams.

@Test
public void testPositionalParams() throws SQLException {
    // Bypasses Read API as it doesnt support Positional Params
    Connection connection = getConnection();
    Parameter dateParam = Parameter.newBuilder().setValue(QueryParameterValue.date("2022-01-01")).build();
    Parameter boolParam = Parameter.newBuilder().setValue(QueryParameterValue.bool(true)).build();
    Parameter intParam = Parameter.newBuilder().setValue(QueryParameterValue.int64(1)).build();
    Parameter numericParam = Parameter.newBuilder().setValue(QueryParameterValue.numeric(new BigDecimal(100))).build();
    List<Parameter> parameters = ImmutableList.of(dateParam, boolParam, intParam, numericParam);
    BigQueryResult bigQueryResult = connection.executeSelect(POSITIONAL_QUERY, parameters);
    logger.log(Level.INFO, "Query used: {0}", POSITIONAL_QUERY);
    ResultSet rs = bigQueryResult.getResultSet();
    int cnt = 0;
    while (rs.next()) {
        assertFalse(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"));
        assertTrue(rs.getBoolean("BooleanField_1"));
        assertNotNull(rs.getString("StringField_1"));
        ++cnt;
    }
    connection.close();
    assertEquals(MULTI_LIMIT_RECS, cnt);
}
Also used : Connection(com.google.cloud.bigquery.Connection) ResultSet(java.sql.ResultSet) Parameter(com.google.cloud.bigquery.Parameter) BigQueryResult(com.google.cloud.bigquery.BigQueryResult) BigDecimal(java.math.BigDecimal) Test(org.junit.Test)

Aggregations

BigQueryResult (com.google.cloud.bigquery.BigQueryResult)22 Connection (com.google.cloud.bigquery.Connection)22 Test (org.junit.Test)22 ResultSet (java.sql.ResultSet)17 ConnectionSettings (com.google.cloud.bigquery.ConnectionSettings)15 Schema (com.google.cloud.bigquery.Schema)6 FieldValueList (com.google.cloud.bigquery.FieldValueList)5 Parameter (com.google.cloud.bigquery.Parameter)3 JsonStringArrayList (org.apache.arrow.vector.util.JsonStringArrayList)3 QueryParameterValue (com.google.cloud.bigquery.QueryParameterValue)2 BigQuerySQLException (com.google.cloud.bigquery.BigQuerySQLException)1 BigDecimal (java.math.BigDecimal)1