Interpret query results

Choose a format

Set the result format

Set the format in one of two ways:

• Configuration file — Takes effect globally after SQL starts in OpenSearch Retrieval Engine Edition V3.0. All queries return results in this format by default.

• `kvpair` clause in the query — Takes effect for the current query only. Overrides the configuration file setting.

To set the format per query, add formatType to the kvpair clause:

query=...&&kvpair=...;formatType:{json | string | full_json | flatbuffers};...

For more information about the kvpair clause, see kvpair clause.

String format

Use the string format for debugging and troubleshooting. Results are typeset for human readability.

Example query

/ha3_develop/source_code/ha3_manual_example/sql $ ./search.sh -s "select nid, price, brand, size from phone"
+ python2.7 /ha3_develop/install_root/usr/local/lib/python/site-packages/ha_tools/search.py -a http://localhost:39341/ -s 'select nid, price, brand, size from phone'

Example response

USE_TIME: 0.025, ROW_COUNT: 10

------------------------------- TABLE INFO ---------------------------
                 nid |               price |               brand |                size |
                   1 |                3599 |              Huawei |                 5.9 |
                   2 |                4388 |              Huawei |                 5.5 |
                   3 |                 899 |              Xiaomi |                   5 |
                   4 |                2999 |                OPPO |                 5.5 |
                   5 |                1299 |               Meizu |                 5.5 |
                   6 |                 169 |               Nokia |                 1.4 |
                   7 |                3599 |               Apple |                 4.7 |
                   8 |                5998 |               Apple |                 5.5 |
                   9 |                4298 |               Apple |                 4.7 |
                  10 |                5688 |             Samsung |                 5.6 |

------------------------------- TRACE INFO ---------------------------

------------------------------- SEARCH INFO ---------------------------
scanInfos { kernelName: "ScanKernel" nodeName: "0_0" tableName: "phone" hashKey: 243934**** parallelNum: 1 totalOutputCount: 10 totalScanCount: 10 totalUseTime: 86 totalSeekTime: 29 totalEvaluateTime: 13 totalOutputTime: 43 totalComputeTimes: 1 }

The response contains three sections: TABLE INFO (result rows), TRACE INFO (trace data), and SEARCH INFO (scan and execution metrics).

JSON format

Use the json format for programmatic parsing in service-to-service calls. The response contains more fields than the string format.

Example query

./search.sh -s "select nid, price, brand, size from phone&&kvpair=formatType:json"

Example response

The response contains the following top-level fields: error_info, format_type, row_count, search_info, sql_result, total_time, and trace.

{
  "error_info": "{\"Error\": ERROR_NONE}",
  "format_type": "json",
  "row_count": 10,
  "search_info": "scanInfos { kernelName: \"ScanKernel\" nodeName: \"0_0\" tableName: \"phone\" hashKey: 243934**** parallelNum: 1 totalOutputCount: 10 totalScanCount: 10 totalUseTime: 81 totalSeekTime: 28 totalEvaluateTime: 11 totalOutputTime: 40 totalComputeTimes: 1 }",
  "sql_result": "{\"column_name\":[\"nid\",\"price\",\"brand\",\"size\"],\"column_type\":[\"uint64\",\"double\",\"multi_char\",\"double\"],\"data\":[[1,3599,\"Huawei\",5.9],[2,4388,\"Huawei\",5.5],[3,899,\"Xiaomi\",5],[4,2999,\"OPPO\",5.5],[5,1299,\"Meizu\",5.5],[6,169,\"Nokia\",1.4],[7,3599,\"Apple\",4.7],[8,5998,\"Apple\",5.5],[9,4298,\"Apple\",4.7],[10,5688,\"Samsung\",5.6]]}",
  "total_time": 0.024,
  "trace": []
}

{
  "column_name": ["nid", "price", "brand", "size"],
  "column_type": ["uint64", "double", "multi_char", "double"],
  "data": [
    [1, 3599, "Huawei", 5.9],
    [2, 4388, "Huawei", 5.5],
    ...
  ]
}

full_json format

The full_json format has the same top-level structure as json. The only difference is that sql_result is a parsed JSON object instead of a JSON string, which simplifies client-side parsing.

Example response

{
  "total_time": 34.003,
  "covered_percent": 1,
  "row_count": 10,
  "format_type": "full_json",
  "search_info": {},
  "trace": [],
  "sql_result": {
    "data": [
      [232953260, "Dicos"],
      [239745260, "Ye Brothers"],
      [240084010, "Cailaobao"],
      [240082260, "Zhou Hei Ya"],
      [240086260, "Juewei Duck Neck"],
      [240108260, ""],
      [239256390, "Everyday Life Supermarket"],
      [240079390, "Meiyijia"],
      [265230260, ""],
      [239313011, "Dasanlin"]
    ],
    "column_name": ["store_id", "brand_name"],
    "column_type": ["int64", "multi_char"]
  },
  "error_info": {
    "ErrorCode": 0,
    "Error": "ERROR_NONE",
    "Message": ""
  }
}

In full_json, error_info is a structured JSON object with ErrorCode, Error, and Message fields. The sql_result field is a parsed JSON object with column_name, column_type, and data fields, identical in structure to the parsed sql_result in the json format.

FlatBuffers format

Use the flatbuffers format in high-performance scenarios where binary serialization reduces overhead. Results must be deserialized by a compatible client before use.

The format is defined by three FlatBuffers schema files.

include "TwoDimTable.fbs";

namespace isearch.fbs;

table SqlErrorResult {
    partitionId: string (id:0);
    hostName: string (id:1);
    errorCode: uint (id:2);
    errorDescription: string (id:3);
}

table SqlResult {
    processTime: double (id:0);
    rowCount: uint32 (id:1);
    errorResult: SqlErrorResult (id:2);
    sqlTable: TwoDimTable (id:3);
    searchInfo: string (id:4);
    coveredPercent: double (id:5);
}

root_type SqlResult;

include "TsdbColumn.fbs";
namespace isearch.fbs;

// multi value
table MultiInt8   { value: [byte];   }
table MultiInt16  { value: [short];  }
table MultiInt32  { value: [int];    }
table MultiInt64  { value: [long];   }
table MultiUInt8  { value: [ubyte];  }
table MultiUInt16 { value: [ushort]; }
table MultiUInt32 { value: [uint];   }
table MultiUInt64 { value: [ulong];  }
table MultiFloat  { value: [float];  }
table MultiDouble { value: [double]; }
table MultiString { value: [string]; }

// column base storage
table Int8Column   { value: [byte];   }
table Int16Column  { value: [short];  }
table Int32Column  { value: [int];    }
table Int64Column  { value: [long];   }
table UInt8Column  { value: [ubyte];  }
table UInt16Column { value: [ushort]; }
table UInt32Column { value: [uint];   }
table UInt64Column { value: [ulong];  }
table FloatColumn  { value: [float];  }
table DoubleColumn { value: [double]; }
table StringColumn { value: [string]; }

table MultiInt8Column   { value: [MultiInt8];   }
table MultiUInt8Column  { value: [MultiUInt8];  }
table MultiInt16Column  { value: [MultiInt16];  }
table MultiUInt16Column { value: [MultiUInt16]; }
table MultiInt32Column  { value: [MultiInt32];  }
table MultiUInt32Column { value: [MultiUInt32]; }
table MultiInt64Column  { value: [MultiInt64];  }
table MultiUInt64Column { value: [MultiUInt64]; }
table MultiFloatColumn  { value: [MultiFloat];  }
table MultiDoubleColumn { value: [MultiDouble]; }
table MultiStringColumn { value: [MultiString]; }

// column type
union ColumnType {
      Int8Column,
      Int16Column,
      Int32Column,
      Int64Column,
      UInt8Column,
      UInt16Column,
      UInt32Column,
      UInt64Column,
      FloatColumn,
      DoubleColumn,
      StringColumn,
      MultiInt8Column,
      MultiInt16Column,
      MultiInt32Column,
      MultiInt64Column,
      MultiUInt8Column,
      MultiUInt16Column,
      MultiUInt32Column,
      MultiUInt64Column,
      MultiFloatColumn,
      MultiDoubleColumn,
      MultiStringColumn,
      TsdbDpsColumn,
}

table Column {
      name: string;
      value: ColumnType;
}

table TwoDimTable {
      rowCount: uint (id:0);
      columns: [Column] (id:1);
}

namespace isearch.fbs;

struct TsdbDataPoint {
      ts: int64 (id:0);
      value: double (id:1);
}
table TsdbDataPointSeries { points: [TsdbDataPoint]; }
table TsdbDpsColumn { value : [TsdbDataPointSeries]; }

search_info fields

search_info captures detailed execution metrics. Add searchInfo:true to the kvpair clause to include it in the response. Use search_info to analyze query execution and diagnose performance issues.

"search_info": {
    "exchangeInfos": [
        {
            "fillResultUseTime": 1276,
            "hashKey": 413114****,
            "kernelName": "ExchangeKernel",
            "nodeName": "1",
            "poolSize": 472,
            "rowCount": 2,
            "searcherUseTime": 7335,
            "totalAccessCount": 4
        }
    ],
    "rpcInfos": [
        {
            "beginTime": 1588131436272843,
            "rpcNodeInfos": [
                {
                    "callUseTime": 5431,
                    "isReturned": true,
                    "netLatency": 664,
                    "rpcBegin": 1588131436272857,
                    "rpcEnd": 1588131436278288,
                    "rpcUseTime": 5175,
                    "specStr": "11.1.XX.XX:20412"
                },
                ...
            ],
            "totalRpcCount": 4,
            "useTime": 7335
        }
    ],
    "runSqlTimeInfo": {
        "sqlPlan2GraphTime": 174,
        "sqlPlanStartTime": 1588143112640920,
        "sqlPlanTime": 10295,
        "sqlRunGraphTime": 13407
    },
    "scanInfos": [
        {
            "hashKey": 691673167,
            "kernelName": "ScanKernel",
            "parallelNum": 2,
            "parallelIndex": 1,
            "tableName": "store",
            "totalComputeTimes": 4,
            "totalEvaluateTime": 9,
            "totalInitTime": 3758,
            "totalOutputCount": 2,
            "totalOutputTime": 264,
            "totalScanCount": 2,
            "totalSeekTime": 2,
            "totalUseTime": 4217
        }
    ]
}