use of bio.terra.grammar.google.BigQueryVisitor in project jade-data-repo by DataBiosphere.
the class GrammarTest method testBqTranslateTwoTables.
@Test
public void testBqTranslateTwoTables() {
BigQueryVisitor bqVisitor = new BigQueryVisitor(datasetMap);
String bqDataset1Name = PdaoConstant.PDAO_PREFIX + "foo";
String bqDataset2Name = PdaoConstant.PDAO_PREFIX + "baz";
String table1Name = "bar";
String table2Name = "quux";
String explicitTable1Name = String.join(".", datasetMap.get("foo").getDataProject(), bqDataset1Name, table1Name);
String explicitTable2Name = String.join(".", datasetMap.get("baz").getDataProject(), bqDataset2Name, table2Name);
Query query = Query.parse("SELECT foo.bar.datarepo_row_id FROM foo.bar, baz.quux WHERE foo.bar.x = baz.quux.y");
String translated = query.translateSql(bqVisitor);
String aliasedTable1Name = bqVisitor.generateAlias(bqDataset1Name, table1Name);
String aliasedTable2Name = bqVisitor.generateAlias(bqDataset2Name, table2Name);
assertThat("query translates to valid bigquery syntax", translated, equalTo("SELECT `" + aliasedTable1Name + "`.datarepo_row_id " + "FROM `" + explicitTable1Name + "` AS `" + aliasedTable1Name + "` ," + " `" + explicitTable2Name + "` AS `" + aliasedTable2Name + "` " + "WHERE `" + aliasedTable1Name + "`.x = `" + aliasedTable2Name + "`.y"));
}
use of bio.terra.grammar.google.BigQueryVisitor in project jade-data-repo by DataBiosphere.
the class CreateSnapshotPrimaryDataQueryStep method doStep.
@Override
public StepResult doStep(FlightContext context) throws InterruptedException {
// TODO: this assumes single-dataset snapshots, will need to add a loop for multiple
// (based on the validation flight step that already occurred.)
/*
* get dataset and assetName
* get asset from dataset
* which gives the root table
* to use in conjunction with the filtered row ids to create this snapshot
*/
Snapshot snapshot = snapshotDao.retrieveSnapshotByName(snapshotReq.getName());
SnapshotRequestQueryModel snapshotQuerySpec = snapshotReq.getContents().get(0).getQuerySpec();
String snapshotAssetName = snapshotQuerySpec.getAssetName();
String snapshotQuery = snapshotReq.getContents().get(0).getQuerySpec().getQuery();
Query query = Query.parse(snapshotQuery);
List<String> datasetNames = query.getDatasetNames();
// TODO this makes the assumption that there is only one dataset
// (based on the validation flight step that already occurred.)
// This will change when more than 1 dataset is allowed
String datasetName = datasetNames.get(0);
Dataset dataset = datasetService.retrieveByName(datasetName);
DatasetModel datasetModel = datasetService.retrieveModel(dataset);
// get asset out of dataset
Optional<AssetSpecification> assetSpecOp = dataset.getAssetSpecificationByName(snapshotAssetName);
AssetSpecification assetSpec = assetSpecOp.orElseThrow(() -> new AssetNotFoundException("Expected asset specification"));
Map<String, DatasetModel> datasetMap = Collections.singletonMap(datasetName, datasetModel);
BigQueryVisitor bqVisitor = new BigQueryVisitor(datasetMap);
String sqlQuery = query.translateSql(bqVisitor);
// validate that the root table is actually a table being queried in the query -->
// and the grammar only picks up tables names in the from clause (though there may be more than one)
List<String> tableNames = query.getTableNames();
String rootTablename = assetSpec.getRootTable().getTable().getName();
if (!tableNames.contains(rootTablename)) {
throw new InvalidQueryException("The root table of the selected asset is not present in this query");
}
// now using the query, get the rowIds
// insert the rowIds into the snapshot row ids table and then kick off the rest of the relationship walking
bigQueryPdao.queryForRowIds(assetSpec, snapshot, sqlQuery);
return StepResult.getStepResultSuccess();
}
use of bio.terra.grammar.google.BigQueryVisitor in project jade-data-repo by DataBiosphere.
the class GrammarTest method testBqTranslate.
@Test
public void testBqTranslate() {
BigQueryVisitor bqVisitor = new BigQueryVisitor(datasetMap);
String bqDatasetName = PdaoConstant.PDAO_PREFIX + "dataset";
String tableName = "table";
String explicitTableName = String.join(".", datasetMap.get("dataset").getDataProject(), bqDatasetName, tableName);
Query query = Query.parse("SELECT dataset.table.datarepo_row_id FROM dataset.table WHERE dataset.table.x = 'string'");
String translated = query.translateSql(bqVisitor);
String aliasedTableName = bqVisitor.generateAlias(bqDatasetName, tableName);
assertThat("query translates to valid bigquery syntax", translated, equalTo("SELECT `" + aliasedTableName + "`.datarepo_row_id FROM `" + explicitTableName + "` " + "AS `" + aliasedTableName + "` WHERE `" + aliasedTableName + "`.x = 'string'"));
}
use of bio.terra.grammar.google.BigQueryVisitor in project jade-data-repo by DataBiosphere.
the class GrammarTest method testInvalidDataset.
@Test(expected = MissingDatasetException.class)
public void testInvalidDataset() {
BigQueryVisitor bqVisitor = new BigQueryVisitor(datasetMap);
Query query = Query.parse("SELECT * FROM noDataset.table WHERE noDataset.table.x = 'string'");
query.translateSql(bqVisitor);
}
use of bio.terra.grammar.google.BigQueryVisitor in project jade-data-repo by DataBiosphere.
the class GrammarTest method testBqTranslateTwoTablesOrdered.
@Test
public void testBqTranslateTwoTablesOrdered() {
BigQueryVisitor bqVisitor = new BigQueryVisitor(datasetMap);
String bqDataset1Name = PdaoConstant.PDAO_PREFIX + "foo";
String bqDataset2Name = PdaoConstant.PDAO_PREFIX + "baz";
String table1Name = "bar";
String table2Name = "quux";
String explicitTable1Name = String.join(".", datasetMap.get("foo").getDataProject(), bqDataset1Name, table1Name);
String explicitTable2Name = String.join(".", datasetMap.get("baz").getDataProject(), bqDataset2Name, table2Name);
Query query = Query.parse("SELECT baz.quux.datarepo_row_id FROM foo.bar, baz.quux WHERE foo.bar.x = baz.quux.y");
String translated = query.translateSql(bqVisitor);
String aliasedTable1Name = bqVisitor.generateAlias(bqDataset1Name, table1Name);
String aliasedTable2Name = bqVisitor.generateAlias(bqDataset2Name, table2Name);
assertThat("query translates to valid bigquery syntax", translated, equalTo("SELECT `" + aliasedTable2Name + "`.datarepo_row_id " + "FROM `" + explicitTable1Name + "` AS `" + aliasedTable1Name + "` ," + " `" + explicitTable2Name + "` AS `" + aliasedTable2Name + "` " + "WHERE `" + aliasedTable1Name + "`.x = `" + aliasedTable2Name + "`.y"));
}
Aggregations