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

Example 6 with QueryParameterValue

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

the class PhecodesQueryBuilderTest method buildQueryJobConfig.

@Test
public void buildQueryJobConfig() throws Exception {
    String pheCodesNamedParameter = "";
    List<SearchParameter> params = new ArrayList<>();
    params.add(new SearchParameter().value("008"));
    params.add(new SearchParameter().value("010"));
    params.add(new SearchParameter().value("031"));
    /* Check the generated querybuilder */
    QueryJobConfiguration queryJobConfiguration = queryBuilder.buildQueryJobConfig(new QueryParameters().type("PHECODE").parameters(params));
    for (String key : queryJobConfiguration.getNamedParameters().keySet()) {
        if (key.startsWith("PheCodes")) {
            pheCodesNamedParameter = key;
        }
    }
    String expected = "select person_id\n" + "from `${projectId}.${dataSetId}.condition_occurrence` co\n" + "where co.condition_source_concept_id in (select concept_id\n" + "from `${projectId}.${dataSetId}.concept`\n" + "where concept_code in\n" + "(select icd9 from `${projectId}.${dataSetId}.phecode_criteria_icd`\n" + "where phecode in unnest(@" + pheCodesNamedParameter + "))\n" + "and vocabulary_id in ('ICD9Proc', 'ICD9CM')\n" + ")\n" + "union all\n" + "select person_id\n" + "from `${projectId}.${dataSetId}.procedure_occurrence` po\n" + "where po.procedure_source_concept_id in (select concept_id\n" + "from `${projectId}.${dataSetId}.concept`\n" + "where concept_code in\n" + "(select icd9 from `${projectId}.${dataSetId}.phecode_criteria_icd`\n" + "where phecode in unnest(@" + pheCodesNamedParameter + "))\n" + "and vocabulary_id in ('ICD9Proc', 'ICD9CM')\n" + ")\n" + "union all\n" + "select person_id\n" + "from `${projectId}.${dataSetId}.measurement` m\n" + "where m.measurement_source_concept_id in (select concept_id\n" + "from `${projectId}.${dataSetId}.concept`\n" + "where concept_code in\n" + "(select icd9 from `${projectId}.${dataSetId}.phecode_criteria_icd`\n" + "where phecode in unnest(@" + pheCodesNamedParameter + "))\n" + "and vocabulary_id in ('ICD9Proc', 'ICD9CM')\n" + ")\n" + "union all\n" + "select person_id\n" + "from `${projectId}.${dataSetId}.observation` o\n" + "where o.observation_source_concept_id in (select concept_id\n" + "from `${projectId}.${dataSetId}.concept`\n" + "where concept_code in\n" + "(select icd9 from `${projectId}.${dataSetId}.phecode_criteria_icd`\n" + "where phecode in unnest(@" + pheCodesNamedParameter + "))\n" + "and vocabulary_id in ('ICD9Proc', 'ICD9CM')\n" + ")\n";
    assertEquals(expected, queryJobConfiguration.getQuery());
    /* Check the querybuilder parameters */
    List<QueryParameterValue> pheCodes = queryJobConfiguration.getNamedParameters().get(pheCodesNamedParameter).getArrayValues();
    assertTrue(pheCodes.contains(QueryParameterValue.newBuilder().setValue("008").setType(StandardSQLTypeName.STRING).build()));
    assertTrue(pheCodes.contains(QueryParameterValue.newBuilder().setValue("010").setType(StandardSQLTypeName.STRING).build()));
    assertTrue(pheCodes.contains(QueryParameterValue.newBuilder().setValue("031").setType(StandardSQLTypeName.STRING).build()));
}

Example 7 with QueryParameterValue

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

the class ITBigQueryTest method testNamedQueryParameters.

@Test
public void testNamedQueryParameters() throws InterruptedException {
    String query = new StringBuilder().append("SELECT TimestampField, StringField, BooleanField FROM ").append(TABLE_ID.getTable()).append(" WHERE StringField = @stringParam").append(" AND IntegerField IN UNNEST(@integerList)").toString();
    QueryParameterValue stringParameter = QueryParameterValue.string("stringValue");
    QueryParameterValue intArrayParameter = QueryParameterValue.array(new Integer[] { 3, 4 }, Integer.class);
    QueryRequest request = QueryRequest.newBuilder(query).setDefaultDataset(DatasetId.of(DATASET)).setMaxWaitTime(60000L).setPageSize(1000L).setUseLegacySql(false).addNamedParameter("stringParam", stringParameter).addNamedParameter("integerList", intArrayParameter).build();
    QueryResponse response = queryAndWaitForResponse(request);
    assertEquals(QUERY_RESULT_SCHEMA, response.getResult().getSchema());
    assertEquals(2, Iterables.size(response.getResult().getValues()));
}

Example 8 with QueryParameterValue

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

the class ReviewTabQueryBuilder method buildQuery.

public QueryJobConfiguration buildQuery(ReviewTabQueries queries, Long participantId, PageRequest pageRequest) {
    ParticipantConditionsColumns sortColumn = ParticipantConditionsColumns.fromValue(pageRequest.getSortColumn());
    String finalSql = String.format(queries.getQuery(), ParticipantConditionDbInfo.fromName(sortColumn).getDbName(), pageRequest.getSortOrder().toString(), pageRequest.getPageSize(), pageRequest.getPageNumber() * pageRequest.getPageSize());
    Map<String, QueryParameterValue> params = new HashMap<>();
    params.put(queries.NAMED_PARTICIPANTID_PARAM, QueryParameterValue.int64(participantId));
    return QueryJobConfiguration.newBuilder(finalSql).setNamedParameters(params).setUseLegacySql(false).build();
}

Example 9 with QueryParameterValue

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

the class FieldSetQueryBuilder method buildQuery.

public QueryJobConfiguration buildQuery(ParticipantCriteria participantCriteria, TableQueryAndConfig tableQueryAndConfig, long resultSize, long offset) {
    TableQuery tableQuery = tableQueryAndConfig.getTableQuery();
    List<String> columnNames = tableQuery.getColumns();
    String tableName = tableQuery.getTableName();
    StringBuilder startSql = new StringBuilder("select ");
    // TODO: add column aliases, use below
    startSql.append(Joiner.on(", ").join(columnNames));
    startSql.append("\nfrom `${projectId}.${dataSetId}.");
    startSql.append(tableName);
    startSql.append("` ");
    startSql.append(tableName);
    startSql.append("\nwhere\n");
    Map<String, QueryParameterValue> paramMap = new HashMap<>();
    List<List<ColumnFilter>> columnFilters = tableQuery.getFilters();
    if (columnFilters != null && !columnFilters.isEmpty()) {
        startSql.append("(");
        boolean first = true;
        for (List<ColumnFilter> filterList : columnFilters) {
            if (first) {
                first = false;
            } else {
                startSql.append("\nor\n");
            }
            handleColumnFilters(filterList, tableQueryAndConfig, startSql, paramMap);
        }
        startSql.append(")\nand\n");
    }
    StringBuilder endSql = new StringBuilder("order by ");
    List<String> orderBy = tableQuery.getOrderBy();
    if (orderBy.isEmpty()) {
        throw new BadRequestException("Order by list must not be empty");
    }
    endSql.append(Joiner.on(", ").join(orderBy));
    endSql.append(" limit ");
    endSql.append(resultSize);
    if (offset > 0) {
        endSql.append(" offset ");
        endSql.append(offset);
    }
    return participantCounter.buildQuery(participantCriteria, startSql.toString(), endSql.toString(), tableName, paramMap);
}