analytical.go

Documentation: edge-infra.dev/pkg/f8n/kinform/sql

     1  package sql
     2  
     3  import (
     4  	"context"
     5  )
     6  
     7  type SummarizeGVKsRow struct {
     8  	APIVersionCount int    `json:"c0"`
     9  	APIVersion      string `json:"api_version"`
    10  	KindCount       int    `json:"c1"`
    11  	Kind            string `json:"kind"`
    12  }
    13  
    14  // SummarizeGVKs gives a high level rollup-style summary of watched resources across all clusters, counting
    15  // by apiVersion and kind.
    16  // For example:
    17  // c0	api_version										c1	kind
    18  // 614	"iam.cnrm.cloud.google.com/v1beta1"				532	"IAMPolicyMember"
    19  // 614	"iam.cnrm.cloud.google.com/v1beta1"				41	"IAMPartialPolicy"
    20  // 614	"iam.cnrm.cloud.google.com/v1beta1"				21	"IAMServiceAccount"
    21  // 614	"iam.cnrm.cloud.google.com/v1beta1"				15	"IAMServiceAccountKey"
    22  // 614	"iam.cnrm.cloud.google.com/v1beta1"				5	"IAMCustomRole"
    23  // 230	"serviceusage.cnrm.cloud.google.com/v1beta1"	230	"Service"
    24  // 127	"compute.cnrm.cloud.google.com/v1beta1"			97	"ComputeFirewall"
    25  // shows the total count of all resources in group version, followed by a break down of which kinds contribute
    26  // to that count.
    27  func (db *DBHandle) SummarizeGVKs(ctx context.Context) ([]SummarizeGVKsRow, error) {
    28  	query := `
    29  SELECT max(c0) AS c0,
    30      max(api_version) AS api_version,
    31      max(c1) AS c1,
    32      kind
    33  FROM (
    34          SELECT COUNT(*) over (PARTITION by api_version) AS c0,
    35              api_version,
    36              COUNT(*) over (PARTITION by kind) AS c1,
    37              kind
    38          FROM watched_resources
    39          ORDER BY c0 DESC,
    40              c1 DESC
    41      ) AS agg
    42  GROUP BY kind
    43  ORDER BY c0 DESC,
    44      c1 DESC
    45  `
    46  
    47  	rows, err := db.QueryContext(ctx, query)
    48  	if err != nil {
    49  		return nil, err
    50  	}
    51  	defer rows.Close()
    52  
    53  	var results []SummarizeGVKsRow
    54  	for rows.Next() {
    55  		var result SummarizeGVKsRow
    56  		if err := rows.Scan(&result.APIVersionCount, &result.APIVersion, &result.KindCount, &result.Kind); err != nil {
    57  			return results, err
    58  		}
    59  		results = append(results, result)
    60  	}
    61  
    62  	if err := rows.Err(); err != nil {
    63  		return results, err
    64  	}
    65  
    66  	return results, nil
    67  }
    68  
    69  type CountByGVKRows struct {
    70  	Count      int    `json:"c"`
    71  	APIVersion string `json:"api_version"`
    72  	Kind       string `json:"kind"`
    73  }
    74  
    75  // CountByGVK gives a simple count summary of watched resources across all clusters, counting
    76  // by apiVersion and kind. This is a more efficient, unsorted query suitable for gathering metrics data.
    77  func (db *DBHandle) CountByGVK(ctx context.Context) ([]CountByGVKRows, error) {
    78  	query := `
    79  SELECT COUNT(*) as c, api_version, kind
    80  FROM watched_resources
    81  GROUP BY api_version, kind
    82  `
    83  
    84  	rows, err := db.QueryContext(ctx, query)
    85  	if err != nil {
    86  		return nil, err
    87  	}
    88  	defer rows.Close()
    89  
    90  	var results []CountByGVKRows
    91  	for rows.Next() {
    92  		var result CountByGVKRows
    93  		if err := rows.Scan(&result.Count, &result.APIVersion, &result.Kind); err != nil {
    94  			return results, err
    95  		}
    96  		results = append(results, result)
    97  	}
    98  
    99  	if err := rows.Err(); err != nil {
   100  		return results, err
   101  	}
   102  
   103  	return results, nil
   104  }
   105
View as plain text