feat(bigquery): Implement getArray in BigQueryResultImpl (#3693)

whuffman36 · renovate-bot · web-flow · commit e2a3f2c1a140 · 2025-03-07T14:26:19.000-08:00
* Implement getArray in BigQueryResultImpl * deps: update dependency com.google.apis:google-api-services-bigquery to v2-rev20250216-2.0.0 (#3688) * build(deps): update dependency org.apache.maven.plugins:maven-compiler-plugin to v3.14.0 (#3689) * deps: update ossf/scorecard-action action to v2.4.1 (#3690) * deps: update actions/upload-artifact action to v4.6.1 (#3691) * Remove public from BigQueryResultSet class def * Remove INTEGER_ARRAY_FIELD_LIST from IT test file * Add ReadApi IT test * Try old IT test values * Convert Time objects to strings for comparison * Fix Formatting --------- Co-authored-by: Mend Renovate <bot@renovateapp.com>
diff --git a/google-cloud-bigquery/src/main/java/com/google/cloud/bigquery/BigQueryResultImpl.java b/google-cloud-bigquery/src/main/java/com/google/cloud/bigquery/BigQueryResultImpl.java
@@ -16,14 +16,19 @@
 
 package com.google.cloud.bigquery;
 
+import com.google.cloud.bigquery.FieldValue.Attribute;
 import java.math.BigDecimal;
 import java.sql.Date;
 import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.sql.SQLFeatureNotSupportedException;
 import java.sql.Time;
 import java.sql.Timestamp;
 import java.time.LocalDateTime;
 import java.time.LocalTime;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.List;
 import java.util.Map;
 import java.util.concurrent.BlockingQueue;
 import java.util.concurrent.TimeUnit;
@@ -114,6 +119,77 @@ private class BigQueryResultSet extends AbstractJdbcResultSet {
     // curTup.isLast(). Ref: https://github.com/googleapis/java-bigquery/issues/2033
     private boolean wasNull = false;
 
+    private class BigQueryArrayResult implements java.sql.Array {
+      List<Object> array;
+
+      public BigQueryArrayResult(Object array) {
+        if (array instanceof Object[]) {
+          this.array = new ArrayList<>(Arrays.asList((Object[]) array));
+        } else if (array instanceof FieldValueList) {
+          this.array = new ArrayList<>((FieldValueList) array);
+        } else {
+          this.array = (List<Object>) array;
+        }
+      }
+
+      @Override
+      public String getBaseTypeName() throws SQLException {
+        throw new SQLFeatureNotSupportedException();
+      }
+
+      @Override
+      public int getBaseType() throws SQLException {
+        throw new SQLFeatureNotSupportedException();
+      }
+
+      @Override
+      public Object getArray() throws SQLException {
+        return array;
+      }
+
+      @Override
+      public Object getArray(java.util.Map<String, Class<?>> map) throws SQLException {
+        throw new SQLFeatureNotSupportedException();
+      }
+
+      @Override
+      public Object getArray(long index, int count) throws SQLException {
+        throw new SQLFeatureNotSupportedException();
+      }
+
+      @Override
+      public Object getArray(long index, int count, java.util.Map<String, Class<?>> map)
+          throws SQLException {
+        throw new SQLFeatureNotSupportedException();
+      }
+
+      @Override
+      public ResultSet getResultSet() throws SQLException {
+        throw new SQLFeatureNotSupportedException();
+      }
+
+      @Override
+      public ResultSet getResultSet(java.util.Map<String, Class<?>> map) throws SQLException {
+        throw new SQLFeatureNotSupportedException();
+      }
+
+      @Override
+      public ResultSet getResultSet(long index, int count) throws SQLException {
+        throw new SQLFeatureNotSupportedException();
+      }
+
+      @Override
+      public ResultSet getResultSet(long index, int count, java.util.Map<String, Class<?>> map)
+          throws SQLException {
+        throw new SQLFeatureNotSupportedException();
+      }
+
+      @Override
+      public void free() throws SQLException {
+        throw new SQLFeatureNotSupportedException();
+      }
+    }
+
     @Override
     /*Advances the result set to the next row, returning false if no such row exists. Potentially blocking operation*/
     public boolean next() throws SQLException {
@@ -160,6 +236,54 @@ private Object getCurrentValueForReadApiData(String fieldName) throws SQLExcepti
       return curRow.get(fieldName);
     }
 
+    @Override
+    public java.sql.Array getArray(String fieldName) throws SQLException {
+      if (fieldName == null) {
+        throw new SQLException("fieldName can't be null");
+      }
+      if (cursor == null) {
+        throw new BigQuerySQLException(NULL_CURSOR_MSG);
+      } else if (cursor instanceof FieldValueList) {
+        FieldValue fieldValue = ((FieldValueList) cursor).get(fieldName);
+        if ((fieldValue == null || fieldValue.getValue() == null)) {
+          wasNull = true;
+          return null;
+        }
+        wasNull = false;
+        if (fieldValue.getAttribute().equals(Attribute.REPEATED)) {
+          return new BigQueryArrayResult(fieldValue.getValue());
+        } else {
+          wasNull = true;
+          return null;
+        }
+      } else { // Data received from Read API (Arrow)
+        Object currentVal = getCurrentValueForReadApiData(fieldName);
+        if (currentVal == null) {
+          wasNull = true;
+          return null;
+        }
+        wasNull = false;
+        return new BigQueryArrayResult(currentVal);
+      }
+    }
+
+    @Override
+    public java.sql.Array getArray(int columnIndex) throws SQLException {
+      if (cursor == null) {
+        return null;
+      } else if (cursor instanceof FieldValueList) {
+        FieldValue fieldValue = ((FieldValueList) cursor).get(columnIndex);
+        if (fieldValue == null || fieldValue.getValue() == null) {
+          wasNull = true;
+          return null;
+        }
+        wasNull = false;
+        return new BigQueryArrayResult(fieldValue.getValue());
+      } else {
+        return getArray(schemaFieldList.get(columnIndex).getName());
+      }
+    }
+
     @Override
     public Object getObject(String fieldName) throws SQLException {
       if (fieldName == null) {
diff --git a/google-cloud-bigquery/src/test/java/com/google/cloud/bigquery/BigQueryResultImplTest.java b/google-cloud-bigquery/src/test/java/com/google/cloud/bigquery/BigQueryResultImplTest.java
@@ -30,6 +30,8 @@
 import java.sql.Timestamp;
 import java.time.LocalTime;
 import java.util.AbstractList;
+import java.util.ArrayList;
+import java.util.Arrays;
 import java.util.HashMap;
 import java.util.Map;
 import java.util.concurrent.BlockingQueue;
@@ -58,7 +60,13 @@ public class BigQueryResultImplTest {
               .setMode(Field.Mode.NULLABLE)
               .build(),
           Field.newBuilder("time", StandardSQLTypeName.TIME).setMode(Field.Mode.NULLABLE).build(),
-          Field.newBuilder("date", StandardSQLTypeName.DATE).setMode(Field.Mode.NULLABLE).build());
+          Field.newBuilder("date", StandardSQLTypeName.DATE).setMode(Field.Mode.NULLABLE).build(),
+          Field.newBuilder("intArray", StandardSQLTypeName.INT64)
+              .setMode(Field.Mode.REPEATED)
+              .build(),
+          Field.newBuilder("stringArray", StandardSQLTypeName.STRING)
+              .setMode(Field.Mode.REPEATED)
+              .build());
 
   private static final FieldList FIELD_LIST_SCHEMA =
       FieldList.of(
@@ -69,7 +77,9 @@ public class BigQueryResultImplTest {
           Field.of("bytes", LegacySQLTypeName.BYTES),
           Field.of("timestamp", LegacySQLTypeName.TIMESTAMP),
           Field.of("time", LegacySQLTypeName.TIME),
-          Field.of("date", LegacySQLTypeName.DATE));
+          Field.of("date", LegacySQLTypeName.DATE),
+          Field.of("intArray", LegacySQLTypeName.INTEGER),
+          Field.of("stringArray", LegacySQLTypeName.STRING));
 
   private static final byte[] BYTES = {0xD, 0xE, 0xA, 0xD};
   private static final String BYTES_BASE64 = BaseEncoding.base64().encode(BYTES);
@@ -79,6 +89,11 @@ public class BigQueryResultImplTest {
   private static final String DATE = "2020-01-21";
   private static final int DATE_INT = 0;
   private static final Date EXPECTED_DATE = java.sql.Date.valueOf(DATE);
+  private static final ArrayList<Integer> EXPECTED_INT_ARRAY =
+      new ArrayList<>(Arrays.asList(0, 1, 2, 3, 4));
+  private static final String[] STRING_ARRAY = {"str1", "str2", "str3"};
+  private static final ArrayList<String> EXPECTED_STRING_ARRAY =
+      new ArrayList<>(Arrays.asList(STRING_ARRAY));
   private static final int BUFFER_SIZE = 10;
 
   @Test
@@ -97,7 +112,9 @@ public void testResultSetFieldValueList() throws InterruptedException, SQLExcept
                     Long.toString(EXPECTED_TIMESTAMP.getTime() / 1000),
                     false), // getTime is in milliseconds.
                 FieldValue.of(Attribute.PRIMITIVE, TIME),
-                FieldValue.of(Attribute.PRIMITIVE, DATE)),
+                FieldValue.of(Attribute.PRIMITIVE, DATE),
+                FieldValue.of(Attribute.REPEATED, EXPECTED_INT_ARRAY),
+                FieldValue.of(Attribute.REPEATED, STRING_ARRAY)),
             FIELD_LIST_SCHEMA);
     buffer.put(fieldValues);
 
@@ -111,7 +128,9 @@ public void testResultSetFieldValueList() throws InterruptedException, SQLExcept
                 FieldValue.of(Attribute.PRIMITIVE, null),
                 FieldValue.of(Attribute.PRIMITIVE, null),
                 FieldValue.of(Attribute.PRIMITIVE, null),
-                FieldValue.of(Attribute.PRIMITIVE, null)),
+                FieldValue.of(Attribute.PRIMITIVE, null),
+                FieldValue.of(Attribute.REPEATED, null),
+                FieldValue.of(Attribute.REPEATED, null)),
             FIELD_LIST_SCHEMA);
     buffer.put(nullValues);
 
@@ -143,6 +162,10 @@ public void testResultSetFieldValueList() throws InterruptedException, SQLExcept
     assertThat(resultSet.wasNull()).isFalse();
     assertThat(resultSet.getDate("date").getTime()).isEqualTo(EXPECTED_DATE.getTime());
     assertThat(resultSet.wasNull()).isFalse();
+    assertThat(resultSet.getArray("intArray").getArray()).isEqualTo(EXPECTED_INT_ARRAY);
+    assertThat(resultSet.wasNull()).isFalse();
+    assertThat(resultSet.getArray("stringArray").getArray()).isEqualTo(EXPECTED_STRING_ARRAY);
+    assertThat(resultSet.wasNull()).isFalse();
 
     assertThat(resultSet.next()).isTrue();
     assertThat(resultSet.getObject("string")).isNull();
@@ -167,6 +190,10 @@ public void testResultSetFieldValueList() throws InterruptedException, SQLExcept
     assertThat(resultSet.wasNull()).isTrue();
     assertThat(resultSet.getDate("date")).isNull();
     assertThat(resultSet.wasNull()).isTrue();
+    assertThat(resultSet.getArray("intArray")).isNull();
+    assertThat(resultSet.wasNull()).isTrue();
+    assertThat(resultSet.getArray("stringArray")).isNull();
+    assertThat(resultSet.wasNull()).isTrue();
 
     assertThat(resultSet.next()).isFalse();
   }
@@ -184,6 +211,8 @@ public void testResultSetReadApi() throws InterruptedException, SQLException {
     rowValues.put("timestamp", EXPECTED_TIMESTAMP.getTime() * 1000);
     rowValues.put("time", EXPECTED_TIME.getTime() * 1000);
     rowValues.put("date", DATE_INT);
+    rowValues.put("intArray", EXPECTED_INT_ARRAY);
+    rowValues.put("stringArray", STRING_ARRAY);
     buffer.put(new BigQueryResultImpl.Row(rowValues));
 
     Map<String, Object> nullValues = new HashMap<>();
@@ -195,6 +224,8 @@ public void testResultSetReadApi() throws InterruptedException, SQLException {
     nullValues.put("timestamp", null);
     nullValues.put("time", null);
     nullValues.put("date", null);
+    nullValues.put("intArray", null);
+    nullValues.put("stringArray", null);
     buffer.put(new BigQueryResultImpl.Row(nullValues));
 
     buffer.put(new BigQueryResultImpl.Row(null, true)); // End of buffer marker.
@@ -227,6 +258,10 @@ public void testResultSetReadApi() throws InterruptedException, SQLException {
     // JVM default timezone which causes flakes in non-UTC zones.
     assertThat(resultSet.getDate("date")).isNotNull();
     assertThat(resultSet.wasNull()).isFalse();
+    assertThat(resultSet.getArray("intArray")).isNotNull();
+    assertThat(resultSet.wasNull()).isFalse();
+    assertThat(resultSet.getArray("stringArray")).isNotNull();
+    assertThat(resultSet.wasNull()).isFalse();
 
     assertThat(resultSet.next()).isTrue();
     assertThat(resultSet.getObject("string")).isNull();
@@ -251,6 +286,10 @@ public void testResultSetReadApi() throws InterruptedException, SQLException {
     assertThat(resultSet.wasNull()).isTrue();
     assertThat(resultSet.getDate("date")).isNull();
     assertThat(resultSet.wasNull()).isTrue();
+    assertThat(resultSet.getArray("intArray")).isNull();
+    assertThat(resultSet.wasNull()).isTrue();
+    assertThat(resultSet.getArray("stringArray")).isNull();
+    assertThat(resultSet.wasNull()).isTrue();
 
     assertThat(resultSet.next()).isFalse();
   }
diff --git a/google-cloud-bigquery/src/test/java/com/google/cloud/bigquery/it/ITBigQueryTest.java b/google-cloud-bigquery/src/test/java/com/google/cloud/bigquery/it/ITBigQueryTest.java
@@ -3925,6 +3925,75 @@ public void testExecuteSelectSinglePageTableRow() throws SQLException {
         (com.google.cloud.bigquery.FieldValueList) rs.getObject("IntegerArrayField");
     assertEquals(4, integerArrayFieldValue.size()); // Array has 4 elements
     assertEquals(3, (integerArrayFieldValue.get(2).getNumericValue()).intValue());
+    List<FieldValue> integerArrayFieldValueList =
+        (List<FieldValue>) rs.getArray("IntegerArrayField").getArray();
+    assertEquals(4, integerArrayFieldValueList.size());
+    assertEquals(3, integerArrayFieldValueList.get(2).getNumericValue().intValue());
+
+    assertFalse(rs.next()); // no 3rd row in the table
+  }
+
+  @Test
+  public void testExecuteSelectSinglePageTableRowWithReadAPI() 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))
+            .setUseReadAPI(true)
+            .setMinResultSize(1)
+            .setTotalToPageRowCountRatio(1)
+            .build();
+    Connection connection = bigquery.createConnection(connectionSettings);
+    BigQueryResult bigQueryResult = connection.executeSelect(query);
+    assertTrue(bigQueryResult.getBigQueryResultStats().getQueryStatistics().getUseReadApi());
+    ResultSet rs = bigQueryResult.getResultSet();
+    Schema sc = bigQueryResult.getSchema();
+
+    assertEquals(BQ_RESULTSET_EXPECTED_SCHEMA, sc); // match the schema
+    assertEquals(2, bigQueryResult.getTotalRows()); // Expecting 2 rows
+
+    assertTrue(rs.next()); // first row
+    // 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"));
+
+    assertTrue(rs.next()); // second row
+    // 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)).toString(),
+        rs.getTime("TimeField").toString());
+    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"));
+    List<BigDecimal> integerArray = (List<BigDecimal>) rs.getArray("IntegerArrayField").getArray();
+    assertEquals(4, integerArray.size());
+    assertEquals(3, integerArray.get(2).intValue());
 
     assertFalse(rs.next()); // no 3rd row in the table
   }
@@ -4278,6 +4347,19 @@ public void testExecuteSelectSinglePageTableRowColInd() throws SQLException {
             (integerArrayFieldValue.get(2).getNumericValue()).intValue(),
             (integerArrayFieldValueColInd.get(2).getNumericValue()).intValue());
       }
+
+      List<FieldValue> integerArrayFieldValueList =
+          (List<FieldValue>) rs.getArray("IntegerArrayField").getArray();
+      List<FieldValue> integerArrayFieldValueListColInd =
+          (List<FieldValue>) rs.getArray(14).getArray();
+      assertEquals(
+          integerArrayFieldValueList.size(),
+          integerArrayFieldValueListColInd.size()); // Array has 4 elements
+      if (integerArrayFieldValueList.size() == 4) { // as we are picking the third index
+        assertEquals(
+            (integerArrayFieldValueList.get(2).getNumericValue()).intValue(),
+            (integerArrayFieldValueListColInd.get(2).getNumericValue()).intValue());
+      }
     }
   }
 
