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

Example 1 with QueryParameterValue

use of com.google.cloud.bigquery.QueryParameterValue in project google-cloud-java by GoogleCloudPlatform.

the class ITBigQueryTest method testPositionalQueryParameters.

@Test
public void testPositionalQueryParameters() throws InterruptedException {
    String query = new StringBuilder().append("SELECT TimestampField, StringField, BooleanField FROM ").append(TABLE_ID.getTable()).append(" WHERE StringField = ?").append(" AND TimestampField > ?").append(" AND IntegerField IN UNNEST(?)").append(" AND IntegerField < ?").append(" AND FloatField > ?").toString();
    QueryParameterValue stringParameter = QueryParameterValue.string("stringValue");
    QueryParameterValue timestampParameter = QueryParameterValue.timestamp("2014-01-01 07:00:00.000000+00:00");
    QueryParameterValue intArrayParameter = QueryParameterValue.array(new Integer[] { 3, 4 }, Integer.class);
    QueryParameterValue int64Parameter = QueryParameterValue.int64(5);
    QueryParameterValue float64Parameter = QueryParameterValue.float64(0.5);
    QueryRequest request = QueryRequest.newBuilder(query).setDefaultDataset(DatasetId.of(DATASET)).setMaxWaitTime(60000L).setPageSize(1000L).setUseLegacySql(false).addPositionalParameter(stringParameter).addPositionalParameter(timestampParameter).addPositionalParameter(intArrayParameter).addPositionalParameter(int64Parameter).addPositionalParameter(float64Parameter).build();
    QueryResponse response = queryAndWaitForResponse(request);
    assertEquals(QUERY_RESULT_SCHEMA, response.getResult().getSchema());
    assertEquals(2, Iterables.size(response.getResult().getValues()));
}

Example 2 with QueryParameterValue

use of com.google.cloud.bigquery.QueryParameterValue in project google-cloud-java by GoogleCloudPlatform.

the class ITBigQueryTest method testBytesParameter.

@Test
public void testBytesParameter() throws Exception {
    String query = new StringBuilder().append("SELECT BYTE_LENGTH(@p) AS length").toString();
    QueryParameterValue bytesParameter = QueryParameterValue.bytes(new byte[] { 1, 3 });
    QueryRequest request = QueryRequest.newBuilder(query).setDefaultDataset(DatasetId.of(DATASET)).setMaxWaitTime(60000L).setPageSize(1000L).setUseLegacySql(false).addNamedParameter("p", bytesParameter).build();
    QueryResponse response = queryAndWaitForResponse(request);
    int rowCount = 0;
    for (List<FieldValue> row : response.getResult().getValues()) {
        rowCount++;
        assertEquals(2, row.get(0).getLongValue());
    }
    assertEquals(1, rowCount);
}

Example 3 with QueryParameterValue

use of com.google.cloud.bigquery.QueryParameterValue in project workbench by all-of-us.

the class CodesQueryBuilder method buildQueryJobConfig.

@Override
public QueryJobConfiguration buildQueryJobConfig(QueryParameters params) {
    Map<GroupType, ListMultimap<String, SearchParameter>> paramMap = getMappedParameters(params.getParameters());
    List<String> queryParts = new ArrayList<String>();
    Map<String, QueryParameterValue> queryParams = new HashMap<>();
    for (GroupType group : paramMap.keySet()) {
        ListMultimap<String, SearchParameter> domainMap = paramMap.get(group);
        for (String domain : domainMap.keySet()) {
            final List<SearchParameter> searchParameterList = domainMap.get(domain);
            final SearchParameter parameter = searchParameterList.get(0);
            final String type = parameter.getType();
            final String subtype = parameter.getSubtype();
            List<String> codes = searchParameterList.stream().map(SearchParameter::getValue).collect(Collectors.toList());
            if (group.equals(GroupType.NOT_GROUP)) {
                buildNotGroupQuery(type, subtype, queryParts, queryParams, domain, codes);
            } else {
                buildGroupQuery(type, subtype, queryParts, queryParams, domain, codes);
            }
        }
    }
    String finalSql = OUTER_SQL_TEMPLATE.replace("${innerSql}", String.join(UNION_TEMPLATE, queryParts));
    return QueryJobConfiguration.newBuilder(finalSql).setNamedParameters(queryParams).setUseLegacySql(false).build();
}

Example 4 with QueryParameterValue

use of com.google.cloud.bigquery.QueryParameterValue in project workbench by all-of-us.

the class CodesQueryBuilderTest method buildQueryJobConfigICD9Group.

@Test
public void buildQueryJobConfigICD9Group() throws Exception {
    String procedureNamedParameter = "";
    String cmProcedureParameter = "";
    String procProcedureParameter = "";
    String conditionNamedParameter = "";
    String cmConditionParameter = "";
    String procConditionParameter = "";
    String measurementNamedParameter = "";
    String cmMeasurementParameter = "";
    String procMeasurementParameter = "";
    List<SearchParameter> params = new ArrayList<>();
    params.add(new SearchParameter().group(false).type("ICD9").domain("Condition").value("10.1"));
    params.add(new SearchParameter().group(false).type("ICD9").domain("Condition").value("20.2"));
    params.add(new SearchParameter().group(true).type("ICD9").domain("Measurement").value("0"));
    params.add(new SearchParameter().group(true).type("ICD9").domain("Procedure").value("1"));
    /* Check the generated querybuilder */
    QueryJobConfiguration queryJobConfiguration = queryBuilder.buildQueryJobConfig(new QueryParameters().type("ICD9").parameters(params));
    for (String key : queryJobConfiguration.getNamedParameters().keySet()) {
        if (key.startsWith("Condition")) {
            conditionNamedParameter = key;
            cmConditionParameter = "cm" + key.replace("Condition", "");
            procConditionParameter = "proc" + key.replace("Condition", "");
        } else if (key.startsWith("Measurement")) {
            measurementNamedParameter = key;
            cmMeasurementParameter = "cm" + key.replace("Measurement", "");
            procMeasurementParameter = "proc" + key.replace("Measurement", "");
        } else if (key.startsWith("Procedure")) {
            procedureNamedParameter = key;
            cmProcedureParameter = "cm" + key.replace("Procedure", "");
            procProcedureParameter = "proc" + key.replace("Procedure", "");
        }
    }
    String expected = "select person_id\n" + "from `${projectId}.${dataSetId}.person` p\n" + "where person_id in (select person_id\n" + "from `${projectId}.${dataSetId}.measurement` a, `${projectId}.${dataSetId}.concept` b\n" + "where a.measurement_source_concept_id = b.concept_id\n" + "and b.vocabulary_id in (@" + cmMeasurementParameter + ",@" + procMeasurementParameter + ")\n" + "and b.concept_code like @" + measurementNamedParameter + "\n" + " union all\n" + "select person_id\n" + "from `${projectId}.${dataSetId}.procedure_occurrence` a, `${projectId}.${dataSetId}.concept` b\n" + "where a.procedure_source_concept_id = b.concept_id\n" + "and b.vocabulary_id in (@" + cmProcedureParameter + ",@" + procProcedureParameter + ")\n" + "and b.concept_code like @" + procedureNamedParameter + "\n" + " union all\n" + "select person_id\n" + "from `${projectId}.${dataSetId}.condition_occurrence` a, `${projectId}.${dataSetId}.concept` b\n" + "where a.condition_source_concept_id = b.concept_id\n" + "and b.vocabulary_id in (@" + cmConditionParameter + ",@" + procConditionParameter + ")\n" + "and b.concept_code in unnest(@" + conditionNamedParameter + ")\n" + ")\n";
    assertEquals(expected, queryJobConfiguration.getQuery());
    /* Check the querybuilder parameters */
    List<QueryParameterValue> conditionCodes = queryJobConfiguration.getNamedParameters().get(conditionNamedParameter).getArrayValues();
    assertTrue(conditionCodes.contains(QueryParameterValue.newBuilder().setValue("10.1").setType(StandardSQLTypeName.STRING).build()));
    assertTrue(conditionCodes.contains(QueryParameterValue.newBuilder().setValue("20.2").setType(StandardSQLTypeName.STRING).build()));
    String measurementCode = queryJobConfiguration.getNamedParameters().get(measurementNamedParameter).getValue();
    assertTrue("0%".equals(measurementCode));
    String procedureCode = queryJobConfiguration.getNamedParameters().get(procedureNamedParameter).getValue();
    assertTrue("1%".equals(procedureCode));
    assertEquals("ICD9CM", queryJobConfiguration.getNamedParameters().get(cmConditionParameter).getValue());
    assertEquals("ICD9Proc", queryJobConfiguration.getNamedParameters().get(procConditionParameter).getValue());
}

Example 5 with QueryParameterValue

use of com.google.cloud.bigquery.QueryParameterValue in project workbench by all-of-us.

the class CodesQueryBuilderTest method buildQueryJobConfigICD9NotGroup.

@Test
public void buildQueryJobConfigICD9NotGroup() throws Exception {
    String measurementNamedParameter = "";
    String conditionNamedParameter = "";
    String cmConditionParameter = "";
    String procConditionParameter = "";
    String cmMeasurementParameter = "";
    String procMeasurementParameter = "";
    List<SearchParameter> params = new ArrayList<>();
    params.add(new SearchParameter().group(false).type("ICD9").domain("Condition").value("10.1"));
    params.add(new SearchParameter().group(false).type("ICD9").domain("Condition").value("20.2"));
    params.add(new SearchParameter().group(false).type("ICD9").domain("Measurement").value("30.3"));
    /* Check the generated querybuilder */
    QueryJobConfiguration queryJobConfiguration = queryBuilder.buildQueryJobConfig(new QueryParameters().type("ICD9").parameters(params));
    for (String key : queryJobConfiguration.getNamedParameters().keySet()) {
        if (key.startsWith("Condition")) {
            conditionNamedParameter = key;
            cmConditionParameter = "cm" + key.replace("Condition", "");
            procConditionParameter = "proc" + key.replace("Condition", "");
        } else if (key.startsWith("Measurement")) {
            measurementNamedParameter = key;
            cmMeasurementParameter = "cm" + key.replace("Measurement", "");
            procMeasurementParameter = "proc" + key.replace("Measurement", "");
        }
    }
    String expected = "select person_id\n" + "from `${projectId}.${dataSetId}.person` p\n" + "where person_id in (select person_id\n" + "from `${projectId}.${dataSetId}.condition_occurrence` a, `${projectId}.${dataSetId}.concept` b\n" + "where a.condition_source_concept_id = b.concept_id\n" + "and b.vocabulary_id in (@" + cmConditionParameter + ",@" + procConditionParameter + ")\n" + "and b.concept_code in unnest(@" + conditionNamedParameter + ")\n" + " union all\n" + "select person_id\n" + "from `${projectId}.${dataSetId}.measurement` a, `${projectId}.${dataSetId}.concept` b\n" + "where a.measurement_source_concept_id = b.concept_id\n" + "and b.vocabulary_id in (@" + cmMeasurementParameter + ",@" + procMeasurementParameter + ")\n" + "and b.concept_code in unnest(@" + measurementNamedParameter + ")\n" + ")\n";
    assertEquals(expected, queryJobConfiguration.getQuery());
    /* Check the querybuilder parameters */
    List<QueryParameterValue> conditionCodes = queryJobConfiguration.getNamedParameters().get(conditionNamedParameter).getArrayValues();
    assertTrue(conditionCodes.contains(QueryParameterValue.newBuilder().setValue("10.1").setType(StandardSQLTypeName.STRING).build()));
    assertTrue(conditionCodes.contains(QueryParameterValue.newBuilder().setValue("20.2").setType(StandardSQLTypeName.STRING).build()));
    List<QueryParameterValue> measurementCodes = queryJobConfiguration.getNamedParameters().get(measurementNamedParameter).getArrayValues();
    assertTrue(measurementCodes.contains(QueryParameterValue.newBuilder().setValue("30.3").setType(StandardSQLTypeName.STRING).build()));
    assertEquals("ICD9CM", queryJobConfiguration.getNamedParameters().get(cmConditionParameter).getValue());
    assertEquals("ICD9Proc", queryJobConfiguration.getNamedParameters().get(procConditionParameter).getValue());
}