Skip to content

Commit aef68ab

Browse files
ash-ddogalvarowolfx
ash-ddog
and
alvarowolfx
authored
feat(bigquery): add performance insights (#12101)
Co-authored-by: Alvaro Viebrantz <aviebrantz@google.com>
1 parent 77b08e8 commit aef68ab

2 files changed

Lines changed: 293 additions & 0 deletions

File tree

‎bigquery/job.go‎

Lines changed: 192 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -537,6 +537,9 @@ type QueryStatistics struct {
537537

538538
// Statistics for the EXPORT DATA statement as part of Query Job.
539539
ExportDataStatistics *ExportDataStatistics
540+
541+
// Performance insights.
542+
PerformanceInsights *PerformanceInsights
540543
}
541544

542545
// ExportDataStatistics represents statistics for
@@ -842,6 +845,194 @@ func bqToDMLStatistics(q *bq.DmlStatistics) *DMLStatistics {
842845
}
843846
}
844847

848+
// PerformanceInsights contains performance insights for the job.
849+
type PerformanceInsights struct {
850+
// Average execution of previous runs.
851+
AvgPreviousExecution time.Duration
852+
853+
// Standalone query stage performance insights, for exploring potential improvements.
854+
StagePerformanceStandaloneInsights []*StagePerformanceStandaloneInsight
855+
856+
// jobs.query stage performance insights compared to previous runs, for diagnosing performance regression.
857+
StagePerformanceChangeInsights []*StagePerformanceChangeInsight
858+
}
859+
860+
func bqToPerformanceInsights(in *bq.PerformanceInsights) *PerformanceInsights {
861+
if in == nil {
862+
return nil
863+
}
864+
865+
var standaloneInsights []*StagePerformanceStandaloneInsight
866+
if sis := in.StagePerformanceStandaloneInsights; len(sis) > 0 {
867+
standaloneInsights = make([]*StagePerformanceStandaloneInsight, 0, len(sis))
868+
for _, si := range sis {
869+
standaloneInsights = append(standaloneInsights, bqToStagePerformanceStandaloneInsight(si))
870+
}
871+
}
872+
873+
var changeInsights []*StagePerformanceChangeInsight
874+
if cis := in.StagePerformanceChangeInsights; len(cis) > 0 {
875+
changeInsights = make([]*StagePerformanceChangeInsight, 0, len(cis))
876+
for _, ci := range cis {
877+
changeInsights = append(changeInsights, bqToStagePerformanceChangeInsight(ci))
878+
}
879+
}
880+
881+
return &PerformanceInsights{
882+
AvgPreviousExecution: time.Duration(in.AvgPreviousExecutionMs) * time.Millisecond,
883+
StagePerformanceStandaloneInsights: standaloneInsights,
884+
StagePerformanceChangeInsights: changeInsights,
885+
}
886+
}
887+
888+
// StagePerformanceStandaloneInsight describes standalone performance insights for a specific stage.
889+
type StagePerformanceStandaloneInsight struct {
890+
// The stage id that the insight mapped to.
891+
StageID int64
892+
893+
// If present, the stage had the following reasons for being disqualified from BI Engine execution.
894+
BIEngineReasons []*BIEngineReason
895+
896+
// High cardinality joins in the stage.
897+
HighCardinalityJoins []*HighCardinalityJoin
898+
899+
// True if the stage has a slot contention issue.
900+
SlotContention bool
901+
902+
// True if the stage has insufficient shuffle quota.
903+
InsufficientShuffleQuota bool
904+
905+
// Partition skew in the stage.
906+
PartitionSkew *PartitionSkew
907+
}
908+
909+
func bqToStagePerformanceStandaloneInsight(in *bq.StagePerformanceStandaloneInsight) *StagePerformanceStandaloneInsight {
910+
if in == nil {
911+
return nil
912+
}
913+
914+
var biEngineReasons []*BIEngineReason
915+
if bers := in.BiEngineReasons; len(bers) > 0 {
916+
biEngineReasons = make([]*BIEngineReason, 0, len(bers))
917+
for _, r := range bers {
918+
biEngineReasons = append(biEngineReasons, bqToBIEngineReason(r))
919+
}
920+
}
921+
922+
var highCardinalityJoins []*HighCardinalityJoin
923+
if hcjs := in.HighCardinalityJoins; len(hcjs) > 0 {
924+
highCardinalityJoins = make([]*HighCardinalityJoin, 0, len(hcjs))
925+
for _, hcj := range hcjs {
926+
highCardinalityJoins = append(highCardinalityJoins, bqToHighCardinalityJoin(hcj))
927+
}
928+
}
929+
930+
return &StagePerformanceStandaloneInsight{
931+
StageID: in.StageId,
932+
BIEngineReasons: biEngineReasons,
933+
HighCardinalityJoins: highCardinalityJoins,
934+
SlotContention: in.SlotContention,
935+
InsufficientShuffleQuota: in.InsufficientShuffleQuota,
936+
PartitionSkew: bqToPartitionSkew(in.PartitionSkew),
937+
}
938+
}
939+
940+
// StagePerformanceChangeInsight contains performance insights compared to the previous executions for a specific stage.
941+
type StagePerformanceChangeInsight struct {
942+
// The stage id that the insight mapped to.
943+
StageID int64
944+
945+
InputDataChange *InputDataChange
946+
}
947+
948+
func bqToStagePerformanceChangeInsight(in *bq.StagePerformanceChangeInsight) *StagePerformanceChangeInsight {
949+
if in == nil {
950+
return nil
951+
}
952+
953+
return &StagePerformanceChangeInsight{
954+
StageID: in.StageId,
955+
InputDataChange: bqToInputDataChange(in.InputDataChange),
956+
}
957+
}
958+
959+
// HighCardinalityJoin contains high cardinality join detailed information.
960+
type HighCardinalityJoin struct {
961+
// Count of left input rows.
962+
LeftRows int64
963+
964+
// Count of right input rows.
965+
RightRows int64
966+
967+
// Count of the output rows.
968+
OutputRows int64
969+
970+
// The index of the join operator in the ExplainQueryStep lists.
971+
StepIndex int64
972+
}
973+
974+
func bqToHighCardinalityJoin(in *bq.HighCardinalityJoin) *HighCardinalityJoin {
975+
if in == nil {
976+
return nil
977+
}
978+
979+
return &HighCardinalityJoin{
980+
LeftRows: in.LeftRows,
981+
RightRows: in.RightRows,
982+
OutputRows: in.OutputRows,
983+
StepIndex: in.StepIndex,
984+
}
985+
}
986+
987+
// PartitionSkew contains partition skew detailed information.
988+
type PartitionSkew struct {
989+
// Source stages which produce skewed data.
990+
SkewSources []*SkewSource
991+
}
992+
993+
func bqToPartitionSkew(in *bq.PartitionSkew) *PartitionSkew {
994+
if in == nil {
995+
return nil
996+
}
997+
998+
var skewSources []*SkewSource
999+
if sss := in.SkewSources; len(sss) > 0 {
1000+
skewSources = make([]*SkewSource, 0, len(sss))
1001+
for _, s := range sss {
1002+
skewSources = append(skewSources, bqToSkewSource(s))
1003+
}
1004+
}
1005+
return &PartitionSkew{SkewSources: skewSources}
1006+
}
1007+
1008+
// SkewSource contains details about source stages which produce skewed data.
1009+
type SkewSource struct {
1010+
// Stage id of the skew source stage.
1011+
StageID int64
1012+
}
1013+
1014+
func bqToSkewSource(in *bq.SkewSource) *SkewSource {
1015+
if in == nil {
1016+
return nil
1017+
}
1018+
1019+
return &SkewSource{StageID: in.StageId}
1020+
}
1021+
1022+
// InputDataChange contains details about the input data change insight.
1023+
type InputDataChange struct {
1024+
// Records read difference percentage compared to a previous run.
1025+
RecordsReadDiffPercentage float64
1026+
}
1027+
1028+
func bqToInputDataChange(in *bq.InputDataChange) *InputDataChange {
1029+
if in == nil {
1030+
return nil
1031+
}
1032+
1033+
return &InputDataChange{RecordsReadDiffPercentage: in.RecordsReadDiffPercentage}
1034+
}
1035+
8451036
func (*ExtractStatistics) implementsStatistics() {}
8461037
func (*LoadStatistics) implementsStatistics() {}
8471038
func (*QueryStatistics) implementsStatistics() {}
@@ -1115,6 +1306,7 @@ func (j *Job) setStatistics(s *bq.JobStatistics, c *Client) {
11151306
Timeline: timelineFromProto(s.Query.Timeline),
11161307
ReferencedTables: tables,
11171308
UndeclaredQueryParameterNames: names,
1309+
PerformanceInsights: bqToPerformanceInsights(s.Query.PerformanceInsights),
11181310
}
11191311
}
11201312
j.lastStatus.Statistics = js

‎bigquery/job_test.go‎

Lines changed: 101 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,9 @@
1515
package bigquery
1616

1717
import (
18+
"reflect"
1819
"testing"
20+
"time"
1921

2022
"cloud.google.com/go/internal/testutil"
2123
bq "google.golang.org/api/bigquery/v2"
@@ -79,6 +81,105 @@ func TestCreateJobRef(t *testing.T) {
7981
}
8082
}
8183

84+
// Ideally this would be covered by an integration test but simulating
85+
// performance issues in a dummy project is difficult and requires a lot of set
86+
// up.
87+
func Test_JobPerformanceInsights(t *testing.T) {
88+
for _, test := range []struct {
89+
name string
90+
in *bq.PerformanceInsights
91+
want *PerformanceInsights
92+
}{
93+
{
94+
name: "nil",
95+
},
96+
{
97+
name: "time only",
98+
in: &bq.PerformanceInsights{AvgPreviousExecutionMs: 128},
99+
want: &PerformanceInsights{AvgPreviousExecution: 128 * time.Millisecond},
100+
},
101+
{
102+
name: "full",
103+
in: &bq.PerformanceInsights{
104+
AvgPreviousExecutionMs: 128,
105+
StagePerformanceChangeInsights: []*bq.StagePerformanceChangeInsight{
106+
{InputDataChange: &bq.InputDataChange{RecordsReadDiffPercentage: 1.23}, StageId: 123},
107+
{InputDataChange: &bq.InputDataChange{RecordsReadDiffPercentage: 4.56}, StageId: 456},
108+
},
109+
StagePerformanceStandaloneInsights: []*bq.StagePerformanceStandaloneInsight{
110+
{
111+
BiEngineReasons: []*bq.BiEngineReason{
112+
{Code: "bi-code-1", Message: "bi-message-1"},
113+
},
114+
HighCardinalityJoins: []*bq.HighCardinalityJoin{
115+
{LeftRows: 11, OutputRows: 22, RightRows: 33, StepIndex: 112233},
116+
{LeftRows: 44, OutputRows: 55, RightRows: 66, StepIndex: 445566},
117+
},
118+
InsufficientShuffleQuota: true,
119+
PartitionSkew: &bq.PartitionSkew{SkewSources: []*bq.SkewSource{
120+
{StageId: 321},
121+
{StageId: 654},
122+
}},
123+
StageId: 123456,
124+
},
125+
{
126+
BiEngineReasons: []*bq.BiEngineReason{
127+
{Code: "bi-code-2", Message: "bi-message-2"},
128+
{Code: "bi-code-3", Message: "bi-message-3"},
129+
},
130+
HighCardinalityJoins: []*bq.HighCardinalityJoin{
131+
{LeftRows: 77, OutputRows: 88, RightRows: 99, StepIndex: 778899},
132+
},
133+
PartitionSkew: &bq.PartitionSkew{SkewSources: []*bq.SkewSource{{StageId: 987}}},
134+
SlotContention: true,
135+
StageId: 654321,
136+
},
137+
},
138+
},
139+
want: &PerformanceInsights{
140+
AvgPreviousExecution: 128 * time.Millisecond,
141+
StagePerformanceChangeInsights: []*StagePerformanceChangeInsight{
142+
{InputDataChange: &InputDataChange{RecordsReadDiffPercentage: 1.23}, StageID: 123},
143+
{InputDataChange: &InputDataChange{RecordsReadDiffPercentage: 4.56}, StageID: 456},
144+
},
145+
StagePerformanceStandaloneInsights: []*StagePerformanceStandaloneInsight{
146+
{
147+
BIEngineReasons: []*BIEngineReason{
148+
{Code: "bi-code-1", Message: "bi-message-1"},
149+
},
150+
HighCardinalityJoins: []*HighCardinalityJoin{
151+
{LeftRows: 11, OutputRows: 22, RightRows: 33, StepIndex: 112233},
152+
{LeftRows: 44, OutputRows: 55, RightRows: 66, StepIndex: 445566},
153+
},
154+
InsufficientShuffleQuota: true,
155+
PartitionSkew: &PartitionSkew{SkewSources: []*SkewSource{{StageID: 321}, {StageID: 654}}},
156+
StageID: 123456,
157+
},
158+
{
159+
BIEngineReasons: []*BIEngineReason{
160+
{Code: "bi-code-2", Message: "bi-message-2"},
161+
{Code: "bi-code-3", Message: "bi-message-3"},
162+
},
163+
HighCardinalityJoins: []*HighCardinalityJoin{
164+
{LeftRows: 77, OutputRows: 88, RightRows: 99, StepIndex: 778899},
165+
},
166+
PartitionSkew: &PartitionSkew{SkewSources: []*SkewSource{{StageID: 987}}},
167+
SlotContention: true,
168+
StageID: 654321,
169+
},
170+
},
171+
},
172+
},
173+
} {
174+
t.Run(test.name, func(t *testing.T) {
175+
out := bqToPerformanceInsights(test.in)
176+
if !reflect.DeepEqual(test.want, out) {
177+
t.Error("out != want")
178+
}
179+
})
180+
}
181+
}
182+
82183
func fixRandomID(s string) func() {
83184
prev := randomIDFn
84185
randomIDFn = func() string { return s }

0 commit comments

Comments
 (0)