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

Example 26 with FieldValue

use of com.google.cloud.bigquery.FieldValue in project java-docs-samples by GoogleCloudPlatform.

the class QuerySample method runQuery.

// [END query_config_batch]
// [START run_query]
public static void runQuery(QueryJobConfiguration queryConfig) throws TimeoutException, InterruptedException {
    BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
    // Create a job ID so that we can safely retry.
    JobId jobId = JobId.of(UUID.randomUUID().toString());
    Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());
    // Wait for the query to complete.
    queryJob = queryJob.waitFor();
    // Check for errors
    if (queryJob == null) {
        throw new RuntimeException("Job no longer exists");
    } else if (queryJob.getStatus().getError() != null) {
        // errors, not just the latest one.
        throw new RuntimeException(queryJob.getStatus().getError().toString());
    }
    // Get the results.
    TableResult result = queryJob.getQueryResults();
    // Print all pages of the results.
    while (result != null) {
        for (List<FieldValue> row : result.iterateAll()) {
            for (FieldValue val : row) {
                System.out.printf("%s,", val.toString());
            }
            System.out.printf("\n");
        }
        result = result.getNextPage();
    }
}

Example 27 with FieldValue

use of com.google.cloud.bigquery.FieldValue in project java-docs-samples by GoogleCloudPlatform.

the class QueryParametersSample method runArray.

// [END bigquery_query_params]
/**
 * Query the baby names database to find the most popular names for a gender in a list of states.
 */
// [START bigquery_query_params_arrays]
private static void runArray(String gender, String[] states) throws InterruptedException {
    BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
    String queryString = "SELECT name, sum(number) as count\n" + "FROM `bigquery-public-data.usa_names.usa_1910_2013`\n" + "WHERE gender = @gender\n" + "AND state IN UNNEST(@states)\n" + "GROUP BY name\n" + "ORDER BY count DESC\n" + "LIMIT 10;";
    QueryJobConfiguration queryRequest = QueryJobConfiguration.newBuilder(queryString).addNamedParameter("gender", QueryParameterValue.string(gender)).addNamedParameter("states", QueryParameterValue.array(states, String.class)).setUseLegacySql(false).build();
    // Execute the query.
    TableResult result = bigquery.query(queryRequest);
    // Print all pages of the results.
    while (result != null) {
        for (List<FieldValue> row : result.iterateAll()) {
            System.out.printf("%s: %d\n", row.get(0).getStringValue(), row.get(1).getLongValue());
        }
        result = result.getNextPage();
    }
}

Example 28 with FieldValue

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

the class WorkspacesControllerTest method stubBigQueryCohortCalls.

private void stubBigQueryCohortCalls() {
    QueryResult queryResult = mock(QueryResult.class);
    Iterable testIterable = new Iterable() {

        @Override
        public Iterator iterator() {
            List<FieldValue> list = new ArrayList<>();
            list.add(null);
            return list.iterator();
        }
    };
    Map<String, Integer> rm = ImmutableMap.<String, Integer>builder().put("person_id", 0).put("birth_datetime", 1).put("gender_concept_id", 2).put("race_concept_id", 3).put("ethnicity_concept_id", 4).put("count", 5).build();
    when(bigQueryService.filterBigQueryConfig(null)).thenReturn(null);
    when(bigQueryService.executeQuery(null)).thenReturn(queryResult);
    when(bigQueryService.getResultMapper(queryResult)).thenReturn(rm);
    when(queryResult.iterateAll()).thenReturn(testIterable);
    when(bigQueryService.getLong(null, 0)).thenReturn(0L);
    when(bigQueryService.getString(null, 1)).thenReturn("1");
    when(bigQueryService.getLong(null, 2)).thenReturn(0L);
    when(bigQueryService.getLong(null, 3)).thenReturn(0L);
    when(bigQueryService.getLong(null, 4)).thenReturn(0L);
    when(bigQueryService.getLong(null, 5)).thenReturn(0L);
}

Example 29 with FieldValue

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

the class CohortReviewController method createParticipantCohortStatusesList.

/**
 * Helper method that builds a list of {@link ParticipantCohortStatus} from BigQuery results.
 *
 * @param cohortReviewId
 * @param result
 * @param rm
 * @return
 */
private List<ParticipantCohortStatus> createParticipantCohortStatusesList(Long cohortReviewId, QueryResult result, Map<String, Integer> rm) {
    List<ParticipantCohortStatus> participantCohortStatuses = new ArrayList<>();
    for (List<FieldValue> row : result.iterateAll()) {
        String birthDateTimeString = bigQueryService.getString(row, rm.get("birth_datetime"));
        if (birthDateTimeString == null) {
            throw new BigQueryException(500, "birth_datetime is null at position: " + rm.get("birth_datetime"));
        }
        java.util.Date birthDate = Date.from(Instant.ofEpochMilli(Double.valueOf(birthDateTimeString).longValue() * 1000));
        participantCohortStatuses.add(new ParticipantCohortStatus().participantKey(new ParticipantCohortStatusKey(cohortReviewId, bigQueryService.getLong(row, rm.get("person_id")))).status(CohortStatus.NOT_REVIEWED).birthDate(new java.sql.Date(birthDate.getTime())).genderConceptId(bigQueryService.getLong(row, rm.get("gender_concept_id"))).raceConceptId(bigQueryService.getLong(row, rm.get("race_concept_id"))).ethnicityConceptId(bigQueryService.getLong(row, rm.get("ethnicity_concept_id"))));
    }
    return participantCohortStatuses;
}

Example 30 with FieldValue

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

the class CohortMaterializationService method materializeCohort.

public MaterializeCohortResponse materializeCohort(@Nullable CohortReview cohortReview, SearchRequest searchRequest, MaterializeCohortRequest request) {
    long offset = 0L;
    FieldSet fieldSet = request.getFieldSet();
    List<CohortStatus> statusFilter = request.getStatusFilter();
    String paginationToken = request.getPageToken();
    int pageSize = request.getPageSize();
    // TODO: add CDR version ID here
    Object[] paginationParameters = new Object[] { searchRequest, statusFilter };
    if (paginationToken != null) {
        PaginationToken token = PaginationToken.fromBase64(paginationToken);
        if (token.matchesParameters(paginationParameters)) {
            offset = token.getOffset();
        } else {
            throw new BadRequestException(String.format("Use of pagination token %s with new parameter values", paginationToken));
        }
    }
    int limit = pageSize + 1;
    if (statusFilter == null) {
        statusFilter = ALL_STATUSES;
    }
    ParticipantCriteria criteria;
    MaterializeCohortResponse response = new MaterializeCohortResponse();
    if (statusFilter.contains(CohortStatus.NOT_REVIEWED)) {
        Set<Long> participantIdsToExclude;
        if (statusFilter.size() < CohortStatus.values().length) {
            // Find the participant IDs that have statuses which *aren't* in the filter.
            Set<CohortStatus> statusesToExclude = Sets.difference(ImmutableSet.copyOf(CohortStatus.values()), ImmutableSet.copyOf(statusFilter));
            participantIdsToExclude = getParticipantIdsWithStatus(cohortReview, ImmutableList.copyOf(statusesToExclude));
        } else {
            participantIdsToExclude = ImmutableSet.of();
        }
        criteria = new ParticipantCriteria(searchRequest, participantIdsToExclude);
    } else {
        Set<Long> participantIds = getParticipantIdsWithStatus(cohortReview, statusFilter);
        if (participantIds.isEmpty()) {
            // return an empty response.
            return response;
        }
        criteria = new ParticipantCriteria(participantIds);
    }
    TableQueryAndConfig tableQueryAndConfig = getTableQueryAndConfig(fieldSet);
    QueryJobConfiguration jobConfiguration = fieldSetQueryBuilder.buildQuery(criteria, tableQueryAndConfig, limit, offset);
    QueryResult result;
    try {
        result = bigQueryService.executeQuery(bigQueryService.filterBigQueryConfig(jobConfiguration));
    } catch (BigQueryException e) {
        if (e.getCode() == HttpServletResponse.SC_SERVICE_UNAVAILABLE) {
            throw new ServerUnavailableException("BigQuery was temporarily unavailable, try again later", e);
        } else if (e.getCode() == HttpServletResponse.SC_FORBIDDEN) {
            throw new ForbiddenException("Access to the CDR is denied", e);
        } else {
            throw new ServerErrorException(String.format("An unexpected error occurred materializing the cohort with " + "query = (%s), params = (%s)", jobConfiguration.getQuery(), jobConfiguration.getNamedParameters()), e);
        }
    }
    Map<String, Integer> rm = bigQueryService.getResultMapper(result);
    int numResults = 0;
    boolean hasMoreResults = false;
    ArrayList<Object> results = new ArrayList<>();
    for (List<FieldValue> row : result.iterateAll()) {
        if (numResults == pageSize) {
            hasMoreResults = true;
            break;
        }
        Map<String, Object> resultMap = fieldSetQueryBuilder.extractResults(tableQueryAndConfig, row);
        results.add(resultMap);
        numResults++;
    }
    response.setResults(results);
    if (hasMoreResults) {
        // TODO: consider pagination based on cursor / values rather than offset
        PaginationToken token = PaginationToken.of(offset + pageSize, paginationParameters);
        response.setNextPageToken(token.toBase64());
    }
    return response;
}