load.sql

INSERT INTO wikistat WITH
    parseDateTimeBestEffort(extract(_file, '^pageviews-([\\d\\-]+)\\.gz$')) AS time,
    splitByChar(' ', line) AS values,
    splitByChar('.', values[1]) AS projects
SELECT
    time,
    projects[1] AS project,
    projects[2] AS subproject,
    decodeURLComponent(values[2]) AS path,
    CAST(values[3], 'UInt64') AS hits
FROM s3Cluster('default',
    'https://clickhouse-public-datasets.s3.amazonaws.com/wikistat/original/pageviews-20220{1..6}*.gz',
    'LineAsString')
WHERE length(values) >= 3
SETTINGS
    parallel_distributed_insert_select = 2,
    max_insert_threads = 16;