[FEATURE] BI tools compliance with complex objects

[Yury-Fridlyand]

Is your feature request related to a problem?

Opensearch and SQL plugin use dot notation to delimit fields and subfields for nested objects. BI tools (Power BI and Tableau Desktop) don't use DESCRIBE request to get list of columns¹.

Example

Part of ecommerce index mapping (sample data set from Dashboards).

{
  "mappings" : {
    "properties" : {
      "geoip" : {
        "properties" : {
          "city_name" : {
            "type" : "keyword"
          },
          "continent_name" : {
            "type" : "keyword"
          },
          "country_iso_code" : {
            "type" : "keyword"
          },
          "location" : {
            "type" : "geo_point"
          },
          "region_name" : {
            "type" : "keyword"
          }
        }
      }
    }
  }
}

DESCRIBE response:

Table	Column	Other info
ecommerce	geoip	...
ecommerce	geoip.city_name	...
ecommerce	geoip.continent_name	...
ecommerce	geoip.country_iso_code	...
ecommerce	geoip.location	...
ecommerce	geoip.region_name	...

SELECT * response:

geoip
...data...

(there are no geoip.something columns)

What solution would you like?

Add new SQL plugin setting with boolean value. By default it is deactivated (unset). Once set, it will enforce expanding all complex objects in select * query.

What alternatives have you considered?

JDBC and ODBC drivers could be updated by having new connection string parameter. Once set, driver will send new request parameter

Do you have any additional context?

Add any other context or screenshots about the feature request here.

Footnotes

1. PBI runs select * from ... limit 0, Tableau runs select * from ... where 1 = 0. ↩

[Yury-Fridlyand]

sql/core/src/main/java/org/opensearch/sql/analysis/symbol/SymbolTable.java

Lines 119 to 128 in 9fbcf11

	public Map<String, ExprType> lookupAllFields(Namespace namespace) {
	final LinkedHashMap<String, ExprType> allSymbols =
	orderedTable.getOrDefault(namespace, new LinkedHashMap<>());
	final LinkedHashMap<String, ExprType> results = new LinkedHashMap<>();
	allSymbols.entrySet().stream().filter(entry -> {
	String symbolName = entry.getKey();
	int lastDot = symbolName.lastIndexOf(".");
	return -1 == lastDot || !allSymbols.containsKey(symbolName.substring(0, lastDot));
	}).forEach(entry -> results.put(entry.getKey(), entry.getValue()));
	return results;

When SymbolTable::lookupAllFields patched to return allSymbols, Tableau and PBI can operate with <field>.<subfield> columns.

[GumpacG]

Be aware of nested objects as it may unexpectedly return nulls.

[acarbonetto]

@GumpacG nested type is out of scope for this issue. Please create a separate issue to discuss the nested type as it may require a different solution.

[kushagraag2003]

Is this issue resolved?
select * does not return the list of columns as used by BI tools

If so then how do I enable it?