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();
}
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);
}
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);
}
Aggregations