Examples with TableQuery org.pmiops.workbench.model.TableQuery used on opensource projects

Example 36 with TableQuery

use of org.pmiops.workbench.model.TableQuery in project workbench by all-of-us.

the class CohortMaterializationServiceTest method testMaterializeCohortPersonFieldSetPersonIdWithStringGreaterThanNullNonMatch.

@Test
public void testMaterializeCohortPersonFieldSetPersonIdWithStringGreaterThanNullNonMatch() {
    TableQuery tableQuery = new TableQuery();
    tableQuery.setTableName("person");
    tableQuery.setColumns(ImmutableList.of("person_id"));
    ColumnFilter filter = new ColumnFilter();
    filter.setColumnName("ethnicity_source_value");
    filter.setOperator(Operator.GREATER_THAN);
    filter.setValue("esf");
    tableQuery.addFiltersItem(ImmutableList.of(filter));
    FieldSet fieldSet = new FieldSet();
    fieldSet.setTableQuery(tableQuery);
    MaterializeCohortResponse response = cohortMaterializationService.materializeCohort(null, SearchRequests.allGenders(), makeRequest(fieldSet, 1000));
    assertPersonIds(response, 1L, 2L);
    assertThat(response.getNextPageToken()).isNull();
}

Example 37 with TableQuery

use of org.pmiops.workbench.model.TableQuery in project workbench by all-of-us.

the class CohortMaterializationService method getTableQueryAndConfig.

private TableQueryAndConfig getTableQueryAndConfig(FieldSet fieldSet) {
    TableQuery tableQuery;
    if (fieldSet == null) {
        tableQuery = new TableQuery();
        tableQuery.setTableName(PERSON_TABLE);
        tableQuery.setColumns(ImmutableList.of(PERSON_ID));
    } else {
        tableQuery = fieldSet.getTableQuery();
        if (tableQuery == null) {
            // TODO: support other kinds of field sets besides tableQuery
            throw new BadRequestException("tableQuery must be specified in field sets");
        }
        String tableName = tableQuery.getTableName();
        if (Strings.isNullOrEmpty(tableName)) {
            throw new BadRequestException("Table name must be specified in field sets");
        }
    }
    CdrBigQuerySchemaConfig cdrSchemaConfig = cdrSchemaConfigProvider.get();
    TableConfig tableConfig = cdrSchemaConfig.cohortTables.get(tableQuery.getTableName());
    if (tableConfig == null) {
        throw new BadRequestException("Table " + tableQuery.getTableName() + " is not a valid " + "cohort table; valid tables are: " + cdrSchemaConfig.cohortTables.keySet().stream().sorted().collect(Collectors.joining(",")));
    }
    Map<String, ColumnConfig> columnMap = Maps.uniqueIndex(tableConfig.columns, columnConfig -> columnConfig.name);
    List<String> columnNames = tableQuery.getColumns();
    if (columnNames == null || columnNames.isEmpty()) {
        // By default, return all columns on the table in question in our configuration.
        tableQuery.setColumns(columnMap.keySet().stream().collect(Collectors.toList()));
    } else {
        for (String columnName : columnNames) {
            // TODO: handle columns on foreign key tables
            if (!columnMap.containsKey(columnName)) {
                throw new BadRequestException("Unrecognized column name: " + columnName);
            }
        }
    }
    List<String> orderBy = tableQuery.getOrderBy();
    if (orderBy == null || orderBy.isEmpty()) {
        ColumnConfig primaryKey = findPrimaryKey(tableConfig);
        if (PERSON_ID.equals(primaryKey)) {
            tableQuery.setOrderBy(ImmutableList.of(PERSON_ID));
        } else {
            // TODO: consider having per-table default sort order based on e.g. timestamp
            tableQuery.setOrderBy(ImmutableList.of(PERSON_ID, primaryKey.name));
        }
    } else {
        for (String columnName : orderBy) {
            if (columnName.toUpperCase().endsWith(DESCENDING_SUFFIX)) {
                columnName = columnName.substring(0, columnName.length() - DESCENDING_SUFFIX.length());
            }
            if (!columnMap.containsKey(columnName)) {
                throw new BadRequestException("Invalid column in orderBy: " + columnName);
            }
        }
    }
    return new TableQueryAndConfig(tableQuery, tableConfig, columnMap);
}

Example 38 with TableQuery

use of org.pmiops.workbench.model.TableQuery 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);
}