Search in sources :

Example 1 with BigQueryVisitor

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"));
}
Also used : BigQueryVisitor(bio.terra.grammar.google.BigQueryVisitor) Test(org.junit.Test)

Example 2 with BigQueryVisitor

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();
}
Also used : BigQueryVisitor(bio.terra.grammar.google.BigQueryVisitor) Query(bio.terra.grammar.Query) SnapshotRequestQueryModel(bio.terra.model.SnapshotRequestQueryModel) Dataset(bio.terra.service.dataset.Dataset) AssetSpecification(bio.terra.service.dataset.AssetSpecification) AssetNotFoundException(bio.terra.service.snapshot.exception.AssetNotFoundException) Snapshot(bio.terra.service.snapshot.Snapshot) DatasetModel(bio.terra.model.DatasetModel) InvalidQueryException(bio.terra.grammar.exception.InvalidQueryException)

Example 3 with BigQueryVisitor

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'"));
}
Also used : BigQueryVisitor(bio.terra.grammar.google.BigQueryVisitor) Test(org.junit.Test)

Example 4 with BigQueryVisitor

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);
}
Also used : BigQueryVisitor(bio.terra.grammar.google.BigQueryVisitor) Test(org.junit.Test)

Example 5 with BigQueryVisitor

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"));
}
Also used : BigQueryVisitor(bio.terra.grammar.google.BigQueryVisitor) Test(org.junit.Test)

Aggregations

BigQueryVisitor (bio.terra.grammar.google.BigQueryVisitor)5 Test (org.junit.Test)4 Query (bio.terra.grammar.Query)1 InvalidQueryException (bio.terra.grammar.exception.InvalidQueryException)1 DatasetModel (bio.terra.model.DatasetModel)1 SnapshotRequestQueryModel (bio.terra.model.SnapshotRequestQueryModel)1 AssetSpecification (bio.terra.service.dataset.AssetSpecification)1 Dataset (bio.terra.service.dataset.Dataset)1 Snapshot (bio.terra.service.snapshot.Snapshot)1 AssetNotFoundException (bio.terra.service.snapshot.exception.AssetNotFoundException)1