Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
BigQuery scheduled query for daily marshmallow downloads
WITH
dls AS (
SELECT
DATE_SUB(DATE(@run_time), INTERVAL 1 DAY) AS date,
file.project AS package,
details.installer.name AS installer,
details.python AS python_version,
CAST(SPLIT(details.python, '.')[
OFFSET
(0)] AS string) AS python_major,
CAST(CONCAT(SPLIT(details.python, '.')[
OFFSET
(0)],'.',SPLIT(details.python, '.')[
OFFSET
(1)]) AS string) AS python_minor,
file.version AS marshmallow_version,
CAST(SPLIT(file.version, '.')[
OFFSET
(0)] AS string) AS marshmallow_major,
details.system.name AS system
FROM
`the-psf.pypi.downloads*`
WHERE
_TABLE_SUFFIX = FORMAT_DATE('%Y%m%d', DATE_SUB(DATE(@run_time), INTERVAL 1 DAY))
AND file.project = 'marshmallow'
-- Exclude mirrors
AND details.installer.name NOT IN ("bandersnatch",
"z3c.pypimirror",
"Artifactory",
"devpi")
AND details.python IS NOT NULL )
SELECT
date,
'python_major' AS category_label,
python_major AS category_value,
COUNT(*) AS downloads
FROM
dls
GROUP BY
date,
package,
category_value
UNION ALL
SELECT
date,
'python_minor' AS category_label,
python_minor AS category_value,
COUNT(*) AS downloads
FROM
dls
GROUP BY
date,
package,
category_value
UNION ALL
SELECT
date,
'marshmallow_major' AS category_label,
marshmallow_major AS category_value,
COUNT(*) AS downloads
FROM
dls
WHERE
marshmallow_major IN ('2',
'3')
GROUP BY
date,
package,
category_value
UNION ALL
SELECT
date,
'combined' AS category_label,
CAST(CONCAT('py', python_minor, '-', 'marshmallow', marshmallow_major) AS string) AS category_value,
COUNT(*) AS downloads
FROM
dls
WHERE
marshmallow_major IN ('2',
'3')
GROUP BY
date,
package,
category_value
UNION ALL
SELECT
date,
'marshmallow_version' AS category_label,
marshmallow_version AS category_value,
COUNT(*) AS downloads
FROM
dls
WHERE marshmallow_major IN ('2',
'3')
GROUP BY
date,
package,
category_value
------------ Excluding Linux (CI downloads) --------
UNION ALL
SELECT
date,
'python_major' AS category_label,
CONCAT(python_major, '-', 'no_linux') AS category_value,
COUNT(*) AS downloads
FROM
dls
WHERE
system != "Linux"
GROUP BY
date,
package,
category_value
UNION ALL
SELECT
date,
'python_minor' AS category_label,
CONCAT(python_minor, '-', 'no_linux') AS category_value,
COUNT(*) AS downloads
FROM
dls
WHERE
system != 'Linux'
GROUP BY
date,
package,
category_value
UNION ALL
SELECT
date,
'marshmallow_major' AS category_label,
CONCAT(marshmallow_major, '-', 'no_linux') AS category_value,
COUNT(*) AS downloads
FROM
dls
WHERE
marshmallow_major IN ('2',
'3')
AND system != 'Linux'
GROUP BY
date,
package,
category_value
UNION ALL
SELECT
date,
'combined' AS category_label,
CAST(CONCAT('py', python_minor, '-', 'marshmallow', marshmallow_major, '-', 'no_linux') AS string) AS category_value,
COUNT(*) AS downloads
FROM
dls
WHERE
marshmallow_major IN ('2',
'3')
AND system != 'Linux'
GROUP BY
date,
package,
category_value
UNION ALL
SELECT
date,
'marshmallow_version' AS category_label,
CAST(CONCAT(marshmallow_version, '-', 'no_linux') AS string) AS category_value,
COUNT(*) AS downloads
FROM
dls
WHERE
marshmallow_major IN ('2',
'3') AND
system != 'Linux'
GROUP BY
date,
package,
category_value
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.