Dremio Community

Example: Number of Rows

This example determines the number of rows that were projected by each operator.

To find out how many rows were projected by each operator at the major fragment level:

  1. Upload your profile_attempt_0.json. Alternatively, you can use the sample profile_attempt_0.json file.
  2. Create a file called operator_type.json and add the sample field values in Example: Operator Type Mapping to the file.
  3. Upload the operator type JSON file.
  4. Using a SQL query, obtain the number of rows projected. Group by majorFragmentId, operatorId, op_type.name and order by majorFragmentId, operatorId.

The following is a sample SQL query snippet for obtaining the number of rows projected by the operator. The results are grouped/ordered by MAJOR FRAGMENT, OPERATOR_ID.
This information is very useful when trying to troubleshoot incorrect results.

SELECT majorFragmentId, operatorId, op_type.name, sum(records) records
-- minorFragmentId, nested_4.minorFragmentProfile.maxMemoryUsed AS maxMemoryUsed, nested_4.minorFragmentProfile.memoryUsed AS memoryUsed, nested_4.endpoint.address AS address, nested_4.endpoint.maxDirectMemory AS maxDirectMemory
FROM (
  SELECT minorFragmentProfile, nested_3.minorFragmentProfile.endpoint AS endpoint, minorFragmentId, operatorType, operatorId, nested_3.inputProfile.records AS records, majorFragmentId
  FROM (
    SELECT minorFragmentProfile, nested_2.minorFragmentProfile.minorFragmentId AS minorFragmentId, operatorProfile, nested_2.operatorProfile.operatorType AS operatorType, nested_2.operatorProfile.operatorId AS operatorId, flatten(nested_2.operatorProfile.inputProfile) AS inputProfile, majorFragmentId
    FROM (
      SELECT fragmentProfile, minorFragmentProfile, flatten(nested_1.minorFragmentProfile.operatorProfile) AS operatorProfile, majorFragmentId
      FROM (
        SELECT fragmentProfile, flatten(nested_0.fragmentProfile.minorFragmentProfile) AS minorFragmentProfile, nested_0.fragmentProfile.majorFragmentId AS majorFragmentId
        FROM (
          SELECT flatten(fragmentProfile) AS fragmentProfile
          FROM "@dremio"."profile_attempt_0" AS "profile_attempt_0"
        ) nested_0
      ) nested_1
    ) nested_2
  ) nested_3
) nested_4, "@dremio".operator_type op_type
where nested_4.operatorType = op_type.id
group by majorFragmentId, operatorId, op_type.name
order by majorFragmentId, operatorId

The following screenshots shows the profile data where you can expand joins to see where the issue of incorrect results originated. This screenshot uses the provided sample profile data.

For More Information