google / google/googlesql

How to extract the full table identifier from a Struct column with the Java wrapper

Open
#51 0 comments 0 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.