clickhouse.sql

CREATE TABLE lighthouse
(
    `fetchTime` Nullable(DateTime),
    `s3Path` String,
    `suite` String,
    `device` String,
    `env` String,
    `lighthouseVersion` String,
    `requestedUrl` String,
    `finalUrl` String,
    `formFactor` String,
    `performanceScore` Nullable(Float64),
    `firstContentfulPaint` Nullable(Float64),
    `largestContentfulPaint` Nullable(Float64),
    `largestContentfulPaintScore` Nullable(Float64),
    `largestContentfulPaintElement` String,
    `speedIndex` Nullable(Float64),
    `speedIndexScore` Nullable(Float64),
    `cumulativeLayoutShift` Nullable(Float64),
    `serverResponseTime` Nullable(Float64),
    `timeToInteractive` Nullable(Float64),
    `timeToInteractiveScore` Nullable(Float64),
    `totalBlockingTime` Nullable(Float64),
    `totalBlockingTimeScore` Nullable(Float64),
    `maxPotentialFid` Nullable(Float64),
    `maxPotentialFidScore` Nullable(Float64),
    `firstCpuIdle` Nullable(Float64),
    `firstCpuIdleScore` Nullable(Float64),
    `bootupTime` Nullable(Float64),
    `bootupTimeScore` Nullable(Float64),
    `domSize` Nullable(Int32),
    `domSizeScore` Nullable(Float64),
    `oversizedImageCount` UInt64,
    `oversizedImageWastedBytesTotal` UInt64,
    `inefficientAnimatedCount` UInt64,
    `inefficientAnimatedCountWastedBytesTotal` UInt64,
    `mtti` Nullable(Float64),
    `mintedUserTimings` Array(String),
    `diagnostics` Array(String),
    `metrics` Array(String),
    `configThrottling` String,
    `resourceRequestCountTotal` Nullable(UInt32),
    `resourceTransferSizeTotal` Nullable(UInt32),
    `resourceRequestCountImage` Nullable(UInt32),
    `resourceTransferSizeImage` Nullable(UInt32),
    `seoScore` Nullable(Float64),
    `accessibilityScore` Nullable(Float64),
    `bestPracticesScore` Nullable(Float64),
    `pwaScore` Nullable(Float64),
)
ENGINE = ReplacingMergeTree
ORDER BY (s3Path);

INSERT INTO lighthouse
SELECT *
EXCEPT (_report, _audits, _categories, _config, _auditUserTimings, _auditResourceSummary, _auditInefficientImageItems, _auditInefficientAnimatedItems)
FROM (
	SELECT
		/* Helpers */
		json as _report,
		JSON_QUERY(_report, '$.audits') as _audits,
		JSON_QUERY(_report, '$.categories') as _categories,
		JSON_QUERY(_report, '$.configSettings') as _config,
		JSONExtractArrayRaw(arrayJoin(JSONExtractArrayRaw(JSON_QUERY(_report, '$.audits."user-timings".details.items')))) as _auditUserTimings,
		JSONExtractArrayRaw(arrayJoin(JSONExtractArrayRaw(JSON_QUERY(_report, '$.audits."resource-summary".details.items')))) as _auditResourceSummary,
		JSONExtractArrayRaw(arrayJoin(JSONExtractArrayRaw(JSON_QUERY(_audits, '$[0]."uses-responsive-images".details.items')))) as _auditInefficientImageItems,
		JSONExtractArrayRaw(arrayJoin(JSONExtractArrayRaw(JSON_QUERY(_audits, '$[0]."efficient-animated-content".details.items')))) as _auditInefficientAnimatedItems,
		/* End helpers */
		
		/* General */
		parseDateTimeBestEffortOrNull(JSON_VALUE(_report, '$.fetchTime')) as fetchTime,
		_path as s3Path, 
		replaceRegexpAll(_file, '(-mobile-3g-fast-metrics-only|-mobile-LTE-metrics-only|-metrics-only)?-[\d]*.json', '') as suite,
		multiIf(match(_file, 'LTE'), 'lte', match(_file, '3g'), '3g', 'desktop') as device,
		multiIf(match(_path, 'sandbox/prod'), 'prod', match(_path, 'sandbox/qa'), 'qa', 'test') as env,
		JSON_VALUE(_report, '$.lighthouseVersion') as lighthouseVersion,
		JSON_VALUE(_report, '$.requestedUrl') as requestedUrl,
		JSON_VALUE(_report, '$.finalUrl') as finalUrl,
		JSON_VALUE(_config, '$[0].emulatedFormFactor') as formFactor,

		/* Performance Score */
		toFloat64OrNull(JSON_VALUE(_categories, '$[0].performance.score')) as performanceScore,
		
		/* FCP */
		round(toFloat64OrNull(JSON_VALUE(_audits, '$[0]."first-contentful-paint".numericValue'))) as firstContentfulPaint,
		
		/* LCP */
		round(toFloat64OrNull(JSON_VALUE(_audits, '$[0]."largest-contentful-paint".numericValue'))) as largestContentfulPaint,
		toFloat64OrNull(JSON_VALUE(_audits, '$[0]."largest-contentful-paint".score')) as largestContentfulPaintScore,
		
		/* LCP element */
		JSON_QUERY(_audits, '$[0]."largest-contentful-paint-element".details') as largestContentfulPaintElement,
		
		/* Speed Index */
		round(toFloat64OrNull(JSON_VALUE(_audits, '$[0]."speed-index".numericValue'))) as speedIndex,
		toFloat64OrNull(JSON_VALUE(_audits, '$[0]."speed-index".score')) as speedIndexScore,
		
		/* CLS */
		toFloat64OrNull(JSON_VALUE(_audits, '$[0]."cumulative-layout-shift".numericValue')) as cumulativeLayoutShift,
		
		/* SRT */
		round(toFloat64OrNull(JSON_VALUE(_audits, '$[0]."server-response-time".numericValue'))) as serverResponseTime,
		
		/* TTI */
		round(toFloat64OrNull(JSON_VALUE(_audits, '$[0]."interactive".numericValue'))) as timeToInteractive,
		toFloat64OrNull(JSON_VALUE(_audits, '$[0]."interactive".score')) as timeToInteractiveScore,
		
		/* TBT */
		round(toFloat64OrNull(JSON_VALUE(_audits, '$[0]."total-blocking-time".numericValue'))) as totalBlockingTime,
		toFloat64OrNull(JSON_VALUE(_audits, '$[0]."total-blocking-time".score')) as totalBlockingTimeScore,
		
		/* Potential FID */
		round(toFloat64OrNull(JSON_VALUE(_audits, '$[0]."max-potential-fid".numericValue'))) as maxPotentialFid,
		toFloat64OrNull(JSON_VALUE(_audits, '$[0]."max-potential-fid".score')) as maxPotentialFidScore,
		
		/* First CPU Idle */
		round(toFloat64OrNull(JSON_VALUE(_audits, '$[0]."first-cpu-idle".numericValue'))) as firstCpuIdle,
		toFloat64OrNull(JSON_VALUE(_audits, '$[0]."first-cpu-idle".score')) as firstCpuIdleScore,
		
		/* Bootup Time */
		round(toFloat64OrNull(JSON_VALUE(_audits, '$[0]."bootup-time".numericValue'))) as bootupTime,
		toFloat64OrNull(JSON_VALUE(_audits, '$[0]."bootup-time".score')) as bootupTimeScore,
		
		/* DOM Size */
		toInt32OrNull(JSON_VALUE(_audits, '$[0]."dom-size".numericValue')) as domSize,
		toFloat64OrNull(JSON_VALUE(_audits, '$[0]."dom-size".score')) as domSizeScore,
		
		/* Oversized Images */
		length(_auditInefficientImageItems) as oversizedImageCount,
		arrayReduce('sum', arrayMap(i -> (JSONExtractUInt(i, 'wastedBytes')), _auditInefficientImageItems)) as oversizedImageWastedBytesTotal,
		
		/* Animated Gifs */
		length(_auditInefficientAnimatedItems) as inefficientAnimatedCount,
		arrayReduce('sum', arrayMap(i -> (JSONExtractUInt(i, 'wastedBytes')), _auditInefficientAnimatedItems)) as inefficientAnimatedCountWastedBytesTotal,
		
		/* MTTI */
		round(toFloat64OrNull(JSON_VALUE(arrayFirst(t -> (JSONExtractString(t, 'name') = 'minted-time-to-interactive'), _auditUserTimings), '$.duration'))) as mtti,
		
		/* Minted User Timings */
		arrayFilter(t -> (JSONExtractString(t, 'name') LIKE 'minted%'), _auditUserTimings) as mintedUserTimings,
		
		/* Diagnostics */
		JSONExtractArrayRaw(arrayJoin(JSONExtractArrayRaw(JSON_QUERY(_report, '$.audits."diagnostics".details.items')))) as diagnostics,
		
		/* Metrics */
		JSONExtractArrayRaw(arrayJoin(JSONExtractArrayRaw(JSON_QUERY(_report, '$.audits."metrics".details.items')))) as metrics,
		
		/* Throttling */
		JSON_QUERY(_report, '$.configSettings.throttling') as configThrottling,
		
		/* Resources */
		toUInt32OrNull(JSON_VALUE(arrayFirst(t -> (JSONExtractString(t, 'resourceType') = 'total'), _auditResourceSummary), '$.requestCount')) as resourceRequestCountTotal,
		toUInt32OrNull(JSON_VALUE(arrayFirst(t -> (JSONExtractString(t, 'resourceType') = 'total'), _auditResourceSummary), '$.transferSize')) as resourceTransferSizeTotal,
		toUInt32OrNull(JSON_VALUE(arrayFirst(t -> (JSONExtractString(t, 'resourceType') = 'image'), _auditResourceSummary), '$.requestCount')) as resourceRequestCountImage,
		toUInt32OrNull(JSON_VALUE(arrayFirst(t -> (JSONExtractString(t, 'resourceType') = 'image'), _auditResourceSummary), '$.transferSize')) as resourceTransferSizeImage,
		
		/* SEO Score */
		toFloat64OrNull(JSON_VALUE(_categories, '$[0].seo.score')) as seoScore,
		
		/* A11y Score */
		toFloat64OrNull(JSON_VALUE(_categories, '$[0].accessibility.score')) as accessibilityScore,
		
		/* Best Practices Score */
		toFloat64OrNull(JSON_VALUE(_categories, '$[0]."best-practices".score')) as bestPracticesScore,
		
		/* PWA Score */
		toFloat64OrNull(JSON_VALUE(_categories, '$[0].pwa.score')) as pwaScore
    FROM s3(`http://s3.us-east-1.amazonaws.com/${my_s3_bucket}/lighthouse_reports/*.lighthouse.json`, aws_access_key_id, aws_secret_access_key, 'JSONAsString')
	  SETTINGS max_threads=64, max_insert_threads=64, input_format_parallel_parsing=0
);