#standardSQL
CREATE OR REPLACE TABLE ecommerce.partition_by_day
PARTITION BY date_formatted
OPTIONS(
description="a table partitioned by date"
) AS
SELECT DISTINCT
PARSE_DATE("%Y%m%d", date) AS date_formatted,
fullvisitorId
FROM `data-to-insights.ecommerce.all_sessions_raw`
在此查询中,请留意新选项“PARTITION BY”字段。两个可供选择的分区选项是 DATE 和 TIMESTAMP。PARSE_DATE 函数用于日期字段(存储为字符串),将该字段转化为用于分区的正确 DATE 类型。
点击 ecommerce 数据集,然后选择新表 partiton_by_day:
点击详情标签页。
确认您看到的信息如下:
分区依据:Day
分区日期:date_formatted
注意:从日期列中的日期值起 60 天后,您的实验账号中的分区表内的分区将会自动到期。您个人的 Google Cloud 账号若启用了结算功能,则其中的分区表不会过期。鉴于本实验的目的,将对已创建的分区表运行剩下的查询。
点击检查我的进度,验证已完成以下目标:
创建基于日期的新分区表
任务 3. 查看分区表的查询结果
请运行下面的查询,并留意要处理的总字节数:
#standardSQL
SELECT *
FROM `data-to-insights.ecommerce.partition_by_day`
WHERE date_formatted = '2016-08-01'
这次处理的数据量是 25 KB(即 0.025 MB),是之前查询数据量的极小部分。
现在运行下面的查询,并留意要处理的总字节数:
#standardSQL
SELECT *
FROM `data-to-insights.ecommerce.partition_by_day`
WHERE date_formatted = '2018-07-08'
您应该会看到这样的信息:This query will process 0 B when run(本次查询运行时将处理 0 B 的数据)。
#standardSQL
SELECT
DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date,
(SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations
WHERE stations.usaf = stn) AS station_name, -- Stations may have multiple names
prcp
FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather
WHERE prcp < 99.9 -- Filter unknown values
AND prcp > 0 -- Filter stations/days with no precipitation
AND _TABLE_SUFFIX >= '2018'
ORDER BY date DESC -- Where has it rained/snowed recently
LIMIT 10
注意:FROM 子句中使用的表通配符“*”是为了限制 TABLE_SUFFIX 过滤条件中引用的表数量。注意:尽管添加了“LIMIT 10”,但这并不会减少扫描的数据总量(约 1.83 GB),因为还没有分区。
点击运行。
确认日期格式正确无误,并且降水量字段显示的是非零值。
任务 5. 您的任务:创建分区表
修改上一个查询,以创建一个符合以下指定条件的表:
表名:ecommerce.days_with_rain
对于 PARTITION BY,使用日期字段
对于 OPTIONS,指定 partition_expiration_days = 60
添加表说明“weather stations with precipitation, partitioned by day”
您的查询应如下所示:
#standardSQL
CREATE OR REPLACE TABLE ecommerce.days_with_rain
PARTITION BY date
OPTIONS (
partition_expiration_days=60,
description="weather stations with precipitation, partitioned by day"
) AS
SELECT
DATE(CAST(year AS INT64), CAST(mo AS INT64), CAST(da AS INT64)) AS date,
(SELECT ANY_VALUE(name) FROM `bigquery-public-data.noaa_gsod.stations` AS stations
WHERE stations.usaf = stn) AS station_name, -- Stations may have multiple names
prcp
FROM `bigquery-public-data.noaa_gsod.gsod*` AS weather
WHERE prcp < 99.9 -- Filter unknown values
AND prcp > 0 -- Filter
AND _TABLE_SUFFIX >= '2018'
#standardSQL
# avg monthly precipitation
SELECT
AVG(prcp) AS average,
station_name,
date,
CURRENT_DATE() AS today,
DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age,
EXTRACT(MONTH FROM date) AS month
FROM ecommerce.days_with_rain
WHERE station_name = 'WAKAYAMA' #Japan
GROUP BY station_name, date, today, month, partition_age
ORDER BY date DESC; # most recent days first
任务 6. 确认最早的分区存在时间等于或少于 60 天
更新 ORDER BY 子句以先显示最早的分区。
添加并运行此查询:
#standardSQL
# avg monthly precipitation
SELECT
AVG(prcp) AS average,
station_name,
date,
CURRENT_DATE() AS today,
DATE_DIFF(CURRENT_DATE(), date, DAY) AS partition_age,
EXTRACT(MONTH FROM date) AS month
FROM ecommerce.days_with_rain
WHERE station_name = 'WAKAYAMA' #Japan
GROUP BY station_name, date, today, month, partition_age
ORDER BY partition_age DESC
注意:如果您将来重新运行此查询,鉴于天气数据和您的分区都在不断更新,因此结果也会不同。
…可帮助您充分利用 Google Cloud 技术。我们的课程会讲解各项技能与最佳实践,可帮助您迅速上手使用并继续学习更深入的知识。我们提供从基础到高级的全方位培训,并有点播、直播和虚拟三种方式选择,让您可以按照自己的日程安排学习时间。各项认证可以帮助您核实并证明您在 Google Cloud 技术方面的技能与专业知识。
上次更新手册的时间:2025 年 4 月 29 日
本实验的最后测试时间:2025 年 4 月 29 日
版权所有 2025 Google LLC 保留所有权利。Google 和 Google 徽标是 Google LLC 的商标。其他所有公司名和产品名可能是其各自相关公司的商标。