view

README

Query Example

Generate Calendar

SELECT
   DATE_ADD(DATE(date), INTERVAL - EXTRACT(DAYOFWEEK FROM DATE_ADD(DATE(date), INTERVAL -0 DAY)) +1 DAY) as week
FROM
  UNNEST(GENERATE_DATE_ARRAY(DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH), CURRENT_DATE(), INTERVAL 7 DAY)) date

Login User with last commit timestamp

WITH A AS (
SELECT
  *,
  ROW_NUMBER() OVER (
    PARTITION BY login
    ORDER BY date DESC
  ) as row_num
FROM `$PROJECT_ID.itsubaki_fourkeys.commits`
)

SELECT login, date, sha, message FROM A WHERE row_num = 1 ORDER BY date DESC

Insert Incident

INSERT INTO `$PROJECT_ID.itsubaki_fourkeys.incidents` (
  owner,
  repository,
  id,
  description,
  sha,
  resolved_at,
  inserted_at
)
VALUES (
  'itsubaki',
  'q',
  '1',
  '[TEST] Incident',
  '7b2619e89065d96e683d70a72512e2883c1a2cf6',
  '2021-07-30 13:04:37 UTC',
  CURRENT_TIMESTAMP()
)

JSON Extract

CREATE TEMP FUNCTION JSON2ARRAY(json STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
  return JSON.parse(json).map(x=>JSON.stringify(x));
""";

WITH A AS (
SELECT
  owner,
  repository,
  id,
  login,
  type,
  created_at,
  JSON_EXTRACT_SCALAR(raw_payload,'$.head') as head_sha,
  JSON2ARRAY(JSON_EXTRACT(raw_payload,'$.commits')) as commits
FROM `$PROJECT_ID.itsubaki_fourkeys.events`
WHERE type = "PushEvent"
)

SELECT
  owner,
  repository,
  id,
  login,
  type,
  created_at,
  head_sha,
  JSON_EXTRACT_SCALAR(commit,'$.sha') as sha
FROM A, UNNEST(commits) AS commit

Documentation

Index

• func PullReqs(projectID, dsn string) bigquery.TableMetadata
• func PullReqsFailureRate(projectID, dsn string) bigquery.TableMetadata
• func PullReqsFrequency(projectID, dsn string) bigquery.TableMetadata
• func PullReqsLeadTime(projectID, dsn string) bigquery.TableMetadata
• func PullReqsTimeToRestore(projectID, dsn string) bigquery.TableMetadata
• func Pushed(projectID, dsn string) bigquery.TableMetadata
• func PushedFailureRate(projectID, dsn string) bigquery.TableMetadata
• func PushedFrequency(projectID, dsn string) bigquery.TableMetadata
• func PushedLeadTime(projectID, dsn string) bigquery.TableMetadata
• func PushedTimeToRestore(projectID, dsn string) bigquery.TableMetadata
• func ReleasesFailureRate(projectID, dsn string) bigquery.TableMetadata
• func ReleasesFrequency(projectID, dsn string) bigquery.TableMetadata
• func ReleasesLeadTime(projectID, dsn string) bigquery.TableMetadata
• func ReleasesTimeToRestore(projectID, dsn string) bigquery.TableMetadata

Functions

func PullReqs

func PullReqs(projectID, dsn string) bigquery.TableMetadata

func PullReqsFailureRate

func PullReqsFailureRate(projectID, dsn string) bigquery.TableMetadata

func PullReqsFrequency

func PullReqsFrequency(projectID, dsn string) bigquery.TableMetadata

func PullReqsLeadTime

func PullReqsLeadTime(projectID, dsn string) bigquery.TableMetadata

func PullReqsTimeToRestore

func PullReqsTimeToRestore(projectID, dsn string) bigquery.TableMetadata

func Pushed

func Pushed(projectID, dsn string) bigquery.TableMetadata

func PushedFailureRate

func PushedFailureRate(projectID, dsn string) bigquery.TableMetadata

func PushedFrequency

func PushedFrequency(projectID, dsn string) bigquery.TableMetadata

func PushedLeadTime

func PushedLeadTime(projectID, dsn string) bigquery.TableMetadata

func PushedTimeToRestore

func PushedTimeToRestore(projectID, dsn string) bigquery.TableMetadata

func ReleasesFailureRate

func ReleasesFailureRate(projectID, dsn string) bigquery.TableMetadata

func ReleasesFrequency

func ReleasesFrequency(projectID, dsn string) bigquery.TableMetadata

func ReleasesLeadTime

func ReleasesLeadTime(projectID, dsn string) bigquery.TableMetadata

func ReleasesTimeToRestore

func ReleasesTimeToRestore(projectID, dsn string) bigquery.TableMetadata