How to extract the full table identifier from a Struct column with the Java wrapper
- Dominant language
- C++
- Stars
- 2.6k
- Forks
- 260
- PR merge metrics
- No merged PRs in 30d
Description
Hello, I have the following BigQuery SQL statement which queries a single struct column:
```
select teststruct.col1 from project1.dataset1.table1
```
I am trying to parse out the full identifier (with table) of the column, i.e., `project1.dataset1.table1.teststruct.col1` but thus far I have only succeeded parsing out `table1.teststruct.col1`.
The following will outline my approach, followed by a full example to reproduce the above:
First, I am adding catalogs for each of the table identifiers, followed by the struct column and the table:
```
// create struct type
SimpleType structFieldType = TypeFactory.createSimpleType(ZetaSQLMap.SIMPLE_TYPE_KIND.get("string"));
StructType testStruct = TypeFactory.createStructType(Collections.singletonList(new StructType.StructField("col1", structFieldType)));
SimpleColumn structCol = new SimpleColumn("project1.dataset1.table1", "teststruct", testStruct);
// create table catalog
SimpleCatalog catalog = new SimpleCatalog("tableCatalog");
SimpleCatalog catalogProject = catalog.addNewSimpleCatalog("project1");
SimpleCatalog catalogDataset = catalogProject.addNewSimpleCatalog("dataset1");
SimpleTable table = new SimpleTable("table1", Collections.singletonList(structCol));
catalogDataset.addSimpleTable(table);
```
Adding language options, initializing Analyzer:
```
catalog.addZetaSQLFunctions(new ZetaSQLBuiltinFunctionOptions());
LanguageOptions languageOptions = new LanguageOptions();
languageOptions.enableMaximumLanguageFeatures();
AnalyzerOptions options = new AnalyzerOptions();
options.setLanguageOptions(languageOptions);
Analyzer analyzer = new Analyzer(options, catalog);
```
Finally, retrieving the `ResolvedStatment` from the analyzer and implement a ResolvedNode Visitor for the ResolvedGetStructField, which:
1. Retrieves the name of the struct field, `col1` in this case.
2. Resolves the column of the struct field, `teststruct` in this case, and retrieves the table name, `table1` in this case. **My issue is here: how do I retrieve the full identifier for the tablename, i.e. `project1.dataset1.table1`**
**Note**: the below example is just a working example for this particular use case and will not work for multiple nested struct fields.
```
ResolvedNodes.ResolvedStatement statement = analyzer.analyzeStatement(sql);
ResolvedNodes.Visitor visitor = new ResolvedNodes.Visitor() {
@Override
public void visit(ResolvedNodes.ResolvedGetStructField structField) {
// get struct field index so that we can look up the name of the field
int structFieldIdx = (int) structField.getFieldIdx();
ResolvedNodes.ResolvedColumnRef resolvedColumnRef = (ResolvedNodes.ResolvedColumnRef) structField.getExpr();
// get list of fields in struct and extract name using struct field index
ImmutableList fieldList = ((StructType) resolvedColumnRef.getType()).getFieldList();
String fieldName = fieldList.get(structFieldIdx).getName();
// get struct column and table name
ResolvedColumn resolvedColumn = resolvedColumnRef.getColumn();
String structRoot = resolvedColumn.getName();
String tableName = resolvedColumn.getTableName();
// fullIdentifier will be 'table1.teststruct.col1', how to get full table identifier
String fullIdentifier = String.join(".", Arrays.asList(tableName, structRoot, fieldName));
System.out.println(fullIdentifier);
}
};
statement.accept(visitor);
```
Anyone know how I can extract the full table identifier along with the column? Thank you.
Full example to reproduce:
```
String sql = "select teststruct.col1 from project1.dataset1.table1";
// create struct type
SimpleType structFieldType = TypeFactory.createSimpleType(ZetaSQLMap.SIMPLE_TYPE_KIND.get("string"));
StructType testStruct = TypeFactory.createStructType(Collections.singletonList(new StructType.StructField("col1", structFieldType)));
SimpleColumn structCol = new SimpleColumn("project1.dataset1.table1", "teststruct", testStruct);
// create table catalog
SimpleCatalog catalog = new SimpleCatalog("tableCatalog");
SimpleCatalog catalogProject = catalog.addNewSimpleCatalog("project1");
SimpleCatalog catalogDataset = catalogProject.addNewSimpleCatalog("dataset1");
SimpleTable table = new SimpleTable("table1", Collections.singletonList(structCol));
catalogDataset.addSimpleTable(table);
// add analyzer, language options
catalog.addZetaSQLFunctions(new ZetaSQLBuiltinFunctionOptions());
LanguageOptions languageOptions = new LanguageOptions();
languageOptions.enableMaximumLanguageFeatures();
AnalyzerOptions options = new AnalyzerOptions();
options.setLanguageOptions(languageOptions);
Analyzer analyzer = new Analyzer(options, catalog);
//List> test = Analyzer.extractTableNamesFromStatement(sql);
ResolvedNodes.ResolvedStatement statement = analyzer.analyzeStatement(sql);
ResolvedNodes.Visitor visitor = new ResolvedNodes.Visitor() {
@Override
public void visit(ResolvedNodes.ResolvedGetStructField structField) {
// get struct field index so that we can look up the name of the field
int structFieldIdx = (int) structField.getFieldIdx();
ResolvedNodes.ResolvedColumnRef resolvedColumnRef = (ResolvedNodes.ResolvedColumnRef) structField.getExpr();
// get list of fields in struct and extract name using structfield project1.dataset1.table1
ImmutableList fieldList = ((StructType) resolvedColumnRef.getType()).getFieldList();
String fieldName = fieldList.get(structFieldIdx).getName();
ResolvedColumn resolvedColumn = resolvedColumnRef.getColumn();
String structRoot = resolvedColumn.getName();
String tableName = resolvedColumn.getTableName();
// fullIdentifier will be 'table1.teststruct.col1' - how to extract table as
String fullIdentifier = String.join(".", Arrays.asList(tableName, structRoot, fieldName));
System.out.println(fullIdentifier);
}
};
statement.accept(visitor);
```
Contributor guide
Research direction
Start with the Java wrapper's Analyzer.analyzeStatement entry point and the ResolvedColumn and ResolvedColumnRef APIs used by the visitor. Reproduce the query with SimpleCatalog, SimpleTable, and SimpleColumn, then determine whether the resolved column exposes the project and dataset components. Done means establishing how the full table identifier should be obtained or documenting that limitation.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 38/100