Clever Geek Handbook

《 Google BigQuery》一书。关于数据仓库，分析和机器学习的所有内容

图片你好居住者！您对处理PB数据集的需求感到害怕吗？了解Google BigQuery，这是一个信息存储系统，可以整合整个企业中的数据，促进交互式分析，并支持机器学习。现在，您可以在一个方便的环境中有效地存储，查询，检索和浏览数据。 Walyappa Lakshmanan和Jordan Jordan会教您如何使用可扩展的无服务器公共云的全部功能在现代数据仓库中工作。借助这本书，您将：-深入了解BigQuery的内部知识-探索Big Query支持的数据类型，功能和运算符-优化查询并实施方案以提高性能或降低成本-了解GIS，时间旅行，DDL / DML。自定义函数和SQL脚本-解决许多机器学习问题-了解如何保护数据，跟踪性能以及对用户进行身份验证。



最小化网络成本



最大限度地降低网络成本BigQuery是一项全球性的区域服务。例如，如果您请求存储在欧盟区域中的数据集，则该请求将在位于欧盟数据中心的服务器上运行。为了能够将查询结果存储在表中，它必须位于欧盟区域中的数据集中。但是，BigQuery REST API可以在世界任何地方调用，也可以在GCP以外的计算机上调用（即运行查询）。使用其他GCP资源（例如Google Cloud Storage或Cloud Pub / Sub）时，如果它们与数据集位于同一区域，则可获得最佳性能。因此，如果请求是从Compute Engine实例或Cloud Dataproc集群执行的，则网络开销将最小化，如果实例或群集也与请求的数据集位于同一区域中。从GCP外部访问BigQuery时，请考虑您的网络拓扑，并尝试最小化客户端计算机与数据集所在的GCP中心之间的跳数。



简洁，不完整的响应



通过直接访问REST API，可以通过接受简洁，不完整的响应来减少网络开销。要接受压缩的响应，可以在HTTP标头中指定已准备好接受gzip存档，并确保“ gzip”行出现在User-Agent标头中，例如： 



Accept-Encoding: gzip
User-Agent: programName (gzip)


在这种情况下，所有响应都将使用gzip压缩。默认情况下，BigQuery响应包含文档中列出的所有字段。但是，如果我们知道答案的哪一部分感兴趣，我们可以要求BigQuery仅发送那一部分，从而减少网络开销。例如，在本章中，我们了解了如何使用Jobs API获得有关作业的完整信息。如果您只对完整答案的一部分感兴趣（例如，仅查询计划中的步骤），则可以指定感兴趣的字段以限制响应的大小： 



JOBSURL="https://www.googleapis.com/bigquery/v2/projects/$PROJECT/jobs"
FIELDS="statistics(query(queryPlan(steps)))"
curl --silent \
    -H "Authorization: Bearer $access_token" \
    -H "Accept-Encoding: gzip" \
    -H "User-Agent: get_job_details (gzip)" \
    -X GET \
    "${JOBSURL}/${JOBID}?fields=${FIELDS}" \
| zcat


请注意：它还声明我们接受gzip压缩数据。



将多个请求合并到包中



使用REST API时，可以使用多部分/混合内容类型和每个部分中的嵌套HTTP请求来组合多个BigQuery API调用。每个部分的主体指定HTTP操作（GET，PUT等），URL的路径，标头和主体。作为响应，服务器将发送具有多部分/混合内容类型的单个HTTP响应，其中每个部分将包含（按顺序）对批处理请求中相应请求的响应。尽管响应是按特定顺序返回的，但是服务器可以按任何顺序处理呼叫。因此，批处理请求可以被视为并行执行的一组请求。这是一个发送批处理请求以从项目中最后五个请求的执行计划中获取一些细节的示例。我们首先使用BigQuery命令行工具，获得最后五个成功的任务： 



# 5   
JOBS=$(bq ls -j -n 50 | grep SUCCESS | head -5 | awk '{print $1}')


该请求将发送到BigQuery端点进行批处理： 



BATCHURL="https://www.googleapis.com/batch/bigquery/v2"
JOBSPATH="/projects/$PROJECT/jobs"
FIELDS="statistics(query(queryPlan(steps)))"


您可以在URL路径中定义单个请求： 



request=""
for JOBID in $JOBS; do
read -d '' part << EOF
--batch_part_starts_here
GET ${JOBSPATH}/${JOBID}?fields=${FIELDS}
EOF
request=$(echo "$request"; echo "$part")
done


然后，您可以将请求作为复合请求发送： 



curl --silent \
   -H "Authorization: Bearer $access_token" \
   -H "Content-Type: multipart/mixed; boundary=batch_part_starts_here" \
   -X POST \
   -d "$request" \
   "${BATCHURL}"


使用BigQuery Storage API进行批量读取



在第5章中，我们讨论了使用BigQuery REST API和客户端库枚举表并检索查询结果。 REST API以分页记录的形式返回数据，这些记录更适合于相对较小的结果集。但是，随着机器学习和分布式提取，转换和加载（ETL）工具的出现，外部工具现在需要对BigQuery受管存储库进行快速有效的批量访问。 BigQuery Storage API通过远程过程调用（RPC）协议提供了这种批量读取访问权限。借助BigQuery Storage API，结构化数据以二进制序列化格式通过网络传输，该格式与列式数据存储格式更加匹配。这提供了跨多个使用者的结果集的附加并行化。



最终用户不会直接使用BigQuery Storage API。相反，他们使用Cloud Dataflow，Cloud Dataproc，TensorFlow，AutoML和其他使用Storage API的工具直接从托管存储中读取数据，而不是通过BigQuery API。



由于Storage API直接访问存储的数据，因此访问BigQuery Storage API的权限不同于现有的BigQuery API。必须独立于BigQuery权限配置BigQuery Storage API权限。



BigQuery Storage API为直接从BigQuery受管存储读取数据的工具提供了许多好处。例如，消费者可以使用多个线程（例如，通过允许从Cloud Dataproc中的不同生产服务器进行分布式读取）从表中读取非重叠记录集，对这些线程进行动态分段（从而减少尾部等待时间，这对于MapReduce作业可能是一个严重的问题） ，选择要读取的列子集（仅将模型使用的功能传递给机器学习结构），过滤列值（减少通过网络传输的数据量），同时确保快照的一致性（即从特定时间点读取数据）。



在第5章中，我们介绍了如何使用Jupyter Notebook中的%% bigquery扩展将查询结果加载到DataFrames中。但是，这些示例使用了相对较小的数据集-从十几条记录到数百条记录。是否可以将整个london_bicycles数据集（2400万条记录）加载到DataFrame中？是的，可以，但是在这种情况下，您应该使用Storage API而非BigQuery API将数据加载到DataFrame中。首先，您需要安装具有Avro和pandas支持的Python Storage API客户端库。这可以通过命令来完成 



%pip install google-cloud-bigquery-storage[fastavro,pandas]


然后剩下的就是像以前一样使用%% bigquery扩展，但是添加一个要求使用Storage API的参数： 



%%bigquery df --use_bqstorage_api --project $PROJECT
SELECT 
   start_station_name 
   , end_station_name 
   , start_date 
   , duration
FROM `bigquery-public-data`.london_bicycles.cycle_hire


请注意，我们正在利用Storage API的功能来直接访问此处的各个列。不必将整个BigQuery表读入一个DataFrame中。如果请求返回少量数据，则扩展名将自动使用BigQuery API。因此，如果您始终在笔记本计算机的单元格中指示此标志，这并不令人害怕。要在笔记本的所有单元格中启用--usebqstorageapi标志，可以设置上下文标志： 



import google.cloud.bigquery.magics
google.cloud.bigquery.magics.context.use_bqstorage_api = True


选择一种有效的存储格式



查询的性能取决于构成表的数据的存储位置和存储格式。通常，查询执行搜索或类型转换所需的次数越少，性能就越好。



内部和外部数据源



BigQuery支持查询外部资源，例如Google Cloud Storage，Cloud Bigtable和Google Sheets，但是只有当您使用自己的表时，才能实现最佳查询性能。



我们建议将BigQuery用作所有结构化和半结构化数据的分析数据存储库。外部数据源最适合用于暂存存储（Google Cloud Storage），实时上传（Cloud Pub / Sub，Cloud Bigtable）或定期更新（Cloud SQL，Cloud Spanner）。接下来，设置数据管道以按计划将这些外部源中的数据加载到BigQuery中（请参见第4章）。



如果您需要从Google Cloud Storage请求数据，请尽可能以压缩的列格式（例如Parquet）将其保存。万不得已，请使用基于记录的格式，例如JSON或CSV。



阶段存储桶生命周期管理



如果您在将数据放入Google Cloud Storage后将其上传到BigQuery，请确保在上传后将其从云中删除。如果您使用ETL管道将数据加载到BigQuery中（以进行重大转换或沿途仅保留部分数据），则可能需要将原始数据保存到Google Cloud Storage。在这种情况下，您可以通过定义存储桶生命周期管理规则来降低Google Cloud Storage中的存储，从而降低成本。



您可以通过以下方式来启动存储桶生命周期管理，并设置将来自30天以上的联合区域或标准类的数据自动移动到Nearline Storage，并将在Nearline Storage中存储超过90天的数据移动到Coldline Storage： 



gsutil lifecycle set lifecycle.yaml gs://some_bucket/


在此示例中，lifecycle.yaml文件包含以下代码： 



{
"lifecycle": {
  "rule": [
  {
   "action": {
    "type": "SetStorageClass",
    "storageClass": "NEARLINE"
   },
   "condition": {
    "age": 30,
    "matchesStorageClass": ["MULTI_REGIONAL", "STANDARD"]
   }
 },
 {
  "action": {
   "type": "SetStorageClass",
   "storageClass": "COLDLINE"
  },
  "condition": {
   "age": 90,
   "matchesStorageClass": ["NEARLINE"]
  }
 }
]}}


您不仅可以使用生命周期管理来更改对象的类别，还可以删除早于特定阈值的对象。



将数据存储为数组和结构



除其他公开可用的数据集外，BigQuery的数据集还包含有关全球气象服务机构获得的气旋风暴（飓风，台风，气旋等）的信息。气旋风暴可持续长达数周，其气象参数大约每三个小时测量一次。假设您决定在此数据集中查找2018年发生的所有风暴，每个风暴达到的最大风速以及达到最大速度时的风暴时间和位置。以下查询从公共数据集中检索所有这些信息： 



SELECT
  sid, number, basin, name,
  ARRAY_AGG(STRUCT(iso_time, usa_latitude, usa_longitude, usa_wind) ORDER BY
usa_wind DESC LIMIT 1)[OFFSET(0)].*
FROM
  `bigquery-public-data`.noaa_hurricanes.hurricanes
WHERE
  season = '2018'
GROUP BY
  sid, number, basin, name
ORDER BY number ASC


该查询将检索风暴标识符（sid），其季节，水库和风暴名称（如果已分配），然后找到针对该风暴进行的一系列观测，以风速从高到低的顺序排列观测值，并为每个风暴选择最大速度... 风暴本身按序号排序。结果包括88条记录，看起来像这样：





该请求花了1.4秒，处理了41.7 MB。第一个条目描述了风暴Bolaven，它于2018年1月2日世界标准时间18:00达到最高速度29 m / s。



由于观测是由多种气象服务进行的，因此可以使用嵌套字段将这些数据标准化并存储在BigQuery中，如下所示： 



CREATE OR REPLACE TABLE ch07.hurricanes_nested AS

SELECT sid, season, number, basin, name, iso_time, nature, usa_sshs,
    STRUCT(usa_latitude AS latitude, usa_longitude AS longitude, usa_wind AS
wind, usa_pressure AS pressure) AS usa,
    STRUCT(tokyo_latitude AS latitude, tokyo_longitude AS longitude,
tokyo_wind AS wind, tokyo_pressure AS pressure) AS tokyo,
    ... AS cma,
    ... AS hko,
    ... AS newdelhi,
    ... AS reunion,
    ... bom,
    ... AS wellington,
    ... nadi
FROM `bigquery-public-data`.noaa_hurricanes.hurricanes


该表上的查询看起来与原始表上的查询相同，但列名稍有变化（usa.latitude而不是usa_latitude）： 



SELECT
  sid, number, basin, name,
  ARRAY_AGG(STRUCT(iso_time, usa.latitude, usa.longitude, usa.wind) ORDER BY
usa.wind DESC LIMIT 1)[OFFSET(0)].*
FROM
  ch07.hurricanes_nested
WHERE
  season = '2018'
GROUP BY
  sid, number, basin, name
ORDER BY number ASC


该请求使用公共数据集处理与原始数据相同数量的数据，并在相同的时间内运行。嵌套字段（结构）的使用不会改变查询的速度或成本，但是可以使查询更具可读性。由于同一风暴在持续时间内有很多观测值，因此我们可以更改存储，以在一条记录中适合每个风暴的整个观测数组： 



CREATE OR REPLACE TABLE ch07.hurricanes_nested_track AS

SELECT sid, season, number, basin, name,
 ARRAY_AGG(
   STRUCT(
    iso_time,
    nature,
    usa_sshs,
    STRUCT(usa_latitude AS latitude, usa_longitude AS longitude, usa_wind AS
wind, usa_pressure AS pressure) AS usa,
    STRUCT(tokyo_latitude AS latitude, tokyo_longitude AS longitude,
      tokyo_wind AS wind, tokyo_pressure AS pressure) AS tokyo,
    ... AS cma,
    ... AS hko,
    ... AS newdelhi,
    ... AS reunion,
    ... bom,
    ... AS wellington,
    ... nadi
  ) ORDER BY iso_time ASC ) AS obs
FROM `bigquery-public-data`.noaa_hurricanes.hurricanes
GROUP BY sid, season, number, basin, name


请注意，我们现在将风暴的sid，季节和其他特征存储为标量列，因为它们不会根据持续时间而变化。



其余数据（随每次观察而变化）存储为结构数组。这是新表查询的样子： 



SELECT
  number, name, basin,
  (SELECT AS STRUCT iso_time, usa.latitude, usa.longitude, usa.wind
     FROM UNNEST(obs) ORDER BY usa.wind DESC LIMIT 1).*
FROM ch07.hurricanes_nested_track
WHERE season = '2018'
ORDER BY number ASC


该请求将返回相同的结果，但这次仅处理14.7 MB（成本降低了三倍），并在一秒钟内完成（速度提高了30％）。是什么导致性能提高？当数据存储为数组时，表中的记录数急剧下降（从682,000减少到14,000），2是因为现在每场暴风雨只有一个记录，而没有很多记录-每个观测一个。然后，当我们按季节过滤行时，BigQuery可以同时删除许多相关案例，如图1所示。 7.13。





另一个优点是，当具有不同详细程度的案例存储在同一表中时，无需重复数据记录。单个表可以存储风暴的纬度和经度变化数据以及风暴名称和季节等高级数据。而且由于BigQuery使用压缩将表格数据存储在列中，因此您可以查询和处理高级数据，而不必担心使用详细数据的成本-现在将其存储为每个风暴的单独值数组。



例如，要按年份查找风暴数量，可以仅查询必需的列： 



WITH hurricane_detail AS (
SELECT sid, season, number, basin, name,
 ARRAY_AGG(
  STRUCT(
    iso_time,
    nature,
    usa_sshs,
    STRUCT(usa_latitude AS latitude, usa_longitude AS longitude, usa_wind AS
wind, usa_pressure AS pressure) AS usa,
    STRUCT(tokyo_latitude AS latitude, tokyo_longitude AS longitude,
        tokyo_wind
AS wind, tokyo_pressure AS pressure) AS tokyo
  ) ORDER BY iso_time ASC ) AS obs
FROM `bigquery-public-data`.noaa_hurricanes.hurricanes
GROUP BY sid, season, number, basin, name
)
SELECT
  COUNT(sid) AS count_of_storms,
  season
FROM hurricane_detail
GROUP BY season
ORDER BY season DESC


上一个请求处理了27 MB，这是不使用嵌套重复字段时必须处理的56 MB的一半。



尽管嵌套字段可以通过实际执行对其他相关表的联接来提高可读性，但是它们并不能单独提高性能。另外，从性能的角度来看，嵌套的重复字段非常有用。考虑在架构中使用嵌套的重复字段，因为它们可以显着提高速度，并减少在非嵌套列或重复列上进行查询过滤的成本（在本例中为季节）。



嵌套重复字段的主要缺点是，如果流更新涉及将项添加到现有数组，则难以将流实现到此类表中。这比添加新记录要难得多：您将需要修改现有记录-对于风暴信息表，这是一个重大缺陷，因为不断向其中添加新的观测值，这解释了为什么此公共数据集不使用嵌套重复项领域。



使用数组的实践



经验表明，成功使用嵌套重复字段需要一些实践。为此，BigQuery中的Google Analytics（分析）示例数据集是理想的选择。在模式中标识嵌套数据的最简单方法是在“类型”列中找到单词RECORD（对应于数据类型STRUCT），在“模式”列中找到单词REPEATED，如下所示：





在此示例中，TOTALS字段为STRUCT（但不重复），而HITS字段为STRUCT并重复。这是有道理的，因为Google Analytics（分析）会追踪汇总层级（总点击数的一个工作阶段值）和粒度层级（每个页面和从您的网站撷取的图片的分开的hit.time值）的访客工作阶段资料...只有在使用数组的情况下，才能以这些不同的详细程度存储数据而不在记录中复制visitorId。在使用数组以重复格式保存数据之后，您需要考虑使用UNNEST在请求中部署该数据，例如： 



SELECT DISTINCT
  visitId
  , totals.pageviews
  , totals.timeOnsite
  , trafficSource.source
  , device.browser
  , device.isMobile
  , h.page.pageTitle
FROM
  `bigquery-public-data`.google_analytics_sample.ga_sessions_20170801,
  UNNEST(hits) AS h
WHERE
  totals.timeOnSite IS NOT NULL AND h.page.pageTitle =
'Shopping Cart'
ORDER BY pageviews DESC
LIMIT 10
     ,   [1,2,3,4,5]   :
[1,
2
3
4
5]


然后，您可以执行常规的SQL操作（例如WHERE）来过滤带有标题（例如购物车）的页面上的匹配。试试吧！



另一方面，GitHub公共提交信息数据集（bigquery-publicdata.githubrepos.commits）使用嵌套的重复字段（reponame）来存储受提交操作影响的存储库列表。它不会随时间变化，并提供可在任何其他字段上过滤的更快查询。



将数据存储为地理类型



BigQuery公开数据集包含一个美国邮政编码区域边界的表（bigquery-public-data.utilityus.zipcodearea）和另一个具有描述美国城市边界的多边形的表（bigquery-publicdata.utilityus.uscitiesarea）。zipcodegeom列是字符串，而city_geom列是地理类型。



从这两张表中，您可以获取新墨西哥州圣达菲的所有邮政编码列表，如下所示： 



SELECT name, zipcode
FROM `bigquery-public-data`.utility_us.zipcode_area
JOIN `bigquery-public-data`.utility_us.us_cities_area
ON ST_INTERSECTS(ST_GeogFromText(zipcode_geom), city_geom)
WHERE name LIKE '%Santa Fe%'


该查询耗时51.9秒，处理305.5 MB的数据，并返回以下结果：





为什么这个请求要花这么长时间？不是因为STINTERSECTS操作，而是因为STGeogFromText函数必须评估S2单元并构建与每个邮政编码对应的GEOGRAPHY类型。



我们可以尝试通过预先执行以下操作来修改邮政编码表，并将几何形状存储为GEOGRAPHY值： 



CREATE OR REPLACE TABLE ch07.zipcode_area AS
SELECT 
  * REPLACE(ST_GeogFromText(zipcode_geom) AS zipcode_geom)
FROM 
  `bigquery-public-data`.utility_us.zipcode_area


REPLACE（请参阅上一个查询）是从SELECT *表达式替换列的便捷方法。
新数据集的大小为131.8 MB，大大大于原始表中的116.5 MB。但是，针对该表的查询可以使用S2覆盖率并且速度更快。例如，以下查询需要5.3秒（速度提高10倍）并处理320.8 MB（使用“按需”资费计划时成本略有增加）： 



SELECT name, zipcode
FROM ch07.zipcode_area
JOIN `bigquery-public-data`.utility_us.us_cities_area
ON ST_INTERSECTS(zipcode_geom, city_geom)
WHERE name LIKE '%Santa Fe%'


将地理数据存储在GEOGRAPHY列中的性能优势远远超过了引人注目。这就是为什么不推荐使用Utilityus数据集的原因（仍然可以使用它来保持已编写的查询）。我们建议使用bigquery-public-data.geousboundaries.uszip_codes表，该表将地理信息存储在GEOGRAPHY列中并不断更新。



»可以在上找到关于这本书的更多细节出版社的网站

»目录表

»摘录



对于居住者对优惠券的优惠25％ -谷歌



在支付书的纸质版本，电子书发送到电子邮件。


More articles:


All Articles