Search in sources :

Example 1 with Parameter

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

the class ITBigQueryTest method testConnectionImplDryRun.

@Test
public void testConnectionImplDryRun() throws SQLException {
    String query = String.format("select StringField,  BigNumericField, BooleanField, BytesField, IntegerField, TimestampField, FloatField, NumericField, TimeField, DateField,  DateTimeField , GeographyField, RecordField.BytesField, RecordField.BooleanField, IntegerArrayField from %s where StringField = ? order by TimestampField", TABLE_ID_FASTQUERY_BQ_RESULTSET.getTable());
    ConnectionSettings connectionSettings = ConnectionSettings.newBuilder().setDefaultDataset(DatasetId.of(DATASET)).setCreateSession(true).build();
    Connection connection = bigquery.createConnection(connectionSettings);
    BigQueryDryRunResult bigQueryDryRunResultSet = connection.dryRun(query);
    assertNotNull(bigQueryDryRunResultSet.getSchema());
    assertEquals(BQ_RESULTSET_EXPECTED_SCHEMA, // match the schema
    bigQueryDryRunResultSet.getSchema());
    List<Parameter> queryParameters = bigQueryDryRunResultSet.getQueryParameters();
    assertEquals(StandardSQLTypeName.STRING, queryParameters.get(0).getValue().getType());
    QueryStatistics queryStatistics = bigQueryDryRunResultSet.getStatistics().getQueryStatistics();
    assertNotNull(queryStatistics);
    SessionInfo sessionInfo = bigQueryDryRunResultSet.getStatistics().getSessionInfo();
    assertNotNull(sessionInfo.getSessionId());
    assertEquals(StatementType.SELECT, queryStatistics.getStatementType());
}
Also used : QueryStatistics(com.google.cloud.bigquery.JobStatistics.QueryStatistics) Connection(com.google.cloud.bigquery.Connection) BigQueryDryRunResult(com.google.cloud.bigquery.BigQueryDryRunResult) Parameter(com.google.cloud.bigquery.Parameter) SessionInfo(com.google.cloud.bigquery.JobStatistics.SessionInfo) ConnectionSettings(com.google.cloud.bigquery.ConnectionSettings) Test(org.junit.Test)

Example 2 with Parameter

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

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

use of com.google.cloud.bigquery.Parameter 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

Connection (com.google.cloud.bigquery.Connection)4 Parameter (com.google.cloud.bigquery.Parameter)4 Test (org.junit.Test)4 BigQueryResult (com.google.cloud.bigquery.BigQueryResult)3 ConnectionSettings (com.google.cloud.bigquery.ConnectionSettings)3 QueryParameterValue (com.google.cloud.bigquery.QueryParameterValue)2 BigQueryDryRunResult (com.google.cloud.bigquery.BigQueryDryRunResult)1 QueryStatistics (com.google.cloud.bigquery.JobStatistics.QueryStatistics)1 SessionInfo (com.google.cloud.bigquery.JobStatistics.SessionInfo)1 BigDecimal (java.math.BigDecimal)1 ResultSet (java.sql.ResultSet)1