使用 Amazon Redshift、AWS 数据交换和 Amazon QuickSight，通过地

利用 Amazon Redshift、AWS Data Exchange 和 Amazon QuickSight 增强您的客户数据

重要要点

在这篇文章中，我们将介绍如何利用 AWS Data Exchange 上的公开普查数据来增强您的客户数据。透过使用 Amazon Redshift 和 Amazon QuickSight，您可以挖掘地理空间洞察，进一步优化您的客户细分与行销策略。

了解您的客户始终是有利的，而 AWS Data Exchange 使得利用公开可用的人口普查数据来增强您的客户数据集变得相当简单。美国人口普查局每十年进行一次美国普查，并收集家庭调查数据。这些数据被匿名、汇总，并公开使用。人口普查局收集并汇总数据的最小地理区域是普查区块，这些区块由街道、道路、铁路、溪流和其他水体、以及其他可见的物理和文化特征及法律边界构成。

如果您知道某位客户所属的普查区块，您就能对他们的人口特征做出一般性推断。透过这些新属性，您可以建立一个细分模型，以识别您可以针对其发送个性化讯息的不同客群。这些数据可在 AWS Data Exchange 订阅，透过数据共享，您无需付费将其在帐户中存储副本，就可以进行查询。

在本篇文章中，我们将展示如何使用客户地址来增强数据集，获取来自美国人口普查局数据集的更多人口统计细节。

解决方案概述

该解决方案包括以下高级步骤：

设置 Amazon Redshift Serverless 端点并加载客户数据。在 Amazon Location Service 中设置地点索引。编写 AWS Lambda 用户自定义函数UDF，以从 Amazon Redshift 调用位置服务。在 AWS Data Exchange 上订阅人口普查数据。使用地理空间查询将地址标记到普查区块。在 Amazon Redshift 中创建新的客户数据集。在 Amazon QuickSight 中评估新的客户数据。

以下图示展示了解决方案架构。

前提条件

您可以使用以下 AWS CloudFormation 模板 部署所需的基础设施。在部署之前，您需要通过 AWS 管理控制台 注册 QuickSight 的访问权限。

将通用地址数据加载到 Amazon Redshift

Amazon Redshift 是一个全面管理的、支持 PB 级的云数据仓库服务。Redshift Serverless 使得运行任何大小的分析工作负载变得简便，而无需管理数据仓库基础设施。

为了加载我们的地址数据，我们首先创建一个 Redshift Serverless 工作组。然后，我们使用 Amazon Redshift 查询编辑器 v2从 Amazon Simple Storage Service (Amazon S3) 加载客户数据。

创建 Redshift Serverless 工作组

Redshift Serverless 架构的主要组件有两个：

命名空间 数据库对象和用户的集合。命名空间将您在 Redshift Serverless 中使用的所有资源组合在一起，例如架构、表、用户、数据共享和快照。工作组 计算资源的集合。工作组具有可通过 Redshift Serverless 控制台、AWS 命令行介面 (AWS CLI)，或 Redshift Serverless APIs 配置的网络和安全设置。

要创建您的命名空间和工作组，请参考 使用 Amazon Redshift Serverless 创建数据仓库。在本次练习中，将您的工作组命名为 sandbox，将命名空间命名为 adxdemo。

使用查询编辑器 v2 将客户数据从 Amazon S3 加载

您可以使用查询编辑器 v2 通过网页界面提交查询并向数据仓库加载数据。要配置查询编辑器 v2 以用于您的 AWS 帐户，请参阅 在 Amazon Redshift 中使用查询编辑器 V2 简化和安全加载数据。配置完成后，请按以下步骤操作：

使用以下 SQL 创建数据仓库中 dev 数据库内的 customerdata 架构：

sqlCREATE SCHEMA customerdata

使用以下 SQL DDL 创建您的目标表，以便加载客户地址数据：

sqlCREATE TABLE customerdatacustomeraddresses ( address character varying(256) ENCODE lzo unitnumber character varying(256) ENCODE lzo municipality character varying(256) ENCODE lzo region character varying(256) ENCODE lzo postalcode character varying(256) ENCODE lzo country character varying(256) ENCODE lzo customerid integer ENCODE az64) DISTSTYLE AUTO

加载 addresslistcsv 文件到您刚刚创建的表中。详细说明请参见 在 Amazon Redshift 中简化和安全加载数据使用查询编辑器 V2 。

该文件没有列标题，并且使用管道符号作为分隔符。要获得有关从 Amazon S3 或本地桌面加载数据的更多信息，请参见 将数据加载到数据库。

使用位置服务进行地理编码并增强地址数据

位置服务允许您向应用程序添加位置信息和功能，包括地图、兴趣点、地理编码、路径规划、地理围栏和追踪等能力。

由于我们的数据在 Amazon Redshift 中，因此我们需要使用 SQL 语句访问位置服务 API。数据的每一行都包含一个地址，我们希望使用位置服务 API 对其进行增强和地理标记。Amazon Redshift 允许开发人员使用 SQL SELECT 子句、Python 或 Lambda 创建 UDF。

Lambda 是一种计算服务，让您在无需配置或管理伺服器的情况下运行代码。透过 Lambda UDF，您可以编写包含复杂逻辑的自定义函数，并与第三方组件集成。标量 Lambda UDF 每次调用函数时返回一个结果在此情况下，Lambda 函数为每行接收到数据运行一次。

在本篇文章中，我们编写一个 Lambda 函数，使用位置服务 API 对我们的客户地址进行地理标记和验证。然后，我们将此 Lambda 函数注册为我们的 Redshift 实例的 UDF，让我们能够从 SQL 命令调用此函数。

有关创建位置服务地点索引并创建 Lambda 函数及标量 UDF 的说明，请参见 从 Amazon Redshift 访问 Amazon 位置服务。在本篇文章中，我们使用 ESRI 作为提供者，将地点索引命名为 placeindexredshift。

测试您新函数的代码如下，该代码返回位于华盛顿特区的白宫的坐标：

sqlselect publicfgeocodeaddress(1600 Pennsylvania AveWashingtonDC20500USA)

订阅 AWS Data Exchange 上的人口统计数据

AWS Data Exchange 是一个数据市场，拥有来自超过 300 家供应商的 3500 多个产品，通过文件、API 或 Amazon Redshift 查询直接交付到使用它的数据湖、应用程序、分析和机器学习模型中。

首先，我们需要通过 AWS 身份与访问管理 (IAM) 来给我们的 Redshift 命名空间授权，让其可以访问 AWS Data Exchange 上的订阅。然后，我们可以订阅我们的示例人口统计数据。请完成以下步骤：

在 IAM 控制台，将 AWSDataExchangeSubscriberFullAccess 管理策略添加到您在创建命名空间时分配的 Amazon Redshift 命令访问角色中。在 AWS Data Exchange 控制台，导航至数据集 ACS Sociodemographics (USA Census Block Groups 2019)，该数据集由 CARTO 提供。选择 继续订阅，然后选择 订阅。

订阅配置可能需要几分钟时间。

当您的订阅已就绪，请返回 Redshift Serverless 控制台。在导航窗格中，选择 数据共享。在 订阅 标签中，选择您刚刚订阅的数据共享。在数据共享详细信息页面，选择 从数据共享创建数据库。选择您之前创建的命名空间，并为将存储从您订阅的数据集中共享对象的新数据库提供名称。

在查询编辑器 v2 中，您应看到新创建的数据库以及两个新表：一个存放区块组的多边形，另一个则保存每个区块组的人口统计信息。

使用地理空间查询将地理编码的客户数据与普查数据结合

地理空间数据主要有两种类型：光栅数据和矢量数据。光栅数据以像素网格形式表示，这超出了本篇文章的范畴。矢量数据由顶点、边缘和多边形组成。对于地理空间数据，顶点 以经度和纬度点表示，而 边缘 则是连接成对顶点的连线。把路径视作连接地图上两个交叉口的路径。多边形 是一组顶点，并通过一系列连接边缘形成一个连续的形状。简单矩形是多边形，就像俄亥俄州的州界可以表示为多边形一样。您订阅的 geographyusablockgroup2019 数据集有 220134 行，每行代表单个普查区块组及其地理形状。

小黄鸭加速器

Amazon Redshift 支援使用 GEOMETRY 和 GEOGRAPHY 数据类型 存储和查询基于矢量的空间数据。您可以使用 Redshift SQL 函数进行查询，比如判断一个给定的经度/纬度点是否落在某个多边形例如州或县边界内的操作。在这个数据集中，您可以发现 geographyusablockgroup2019 中的 geom 列的类型是 GEOMETRY。

我们的目标是确定每个地理标记地址所落在的普查区块多边形，以便用已知的普查区块详细信息增强我们的客户记录。请完成以下步骤：

使用我们的 UDF 的地理编码结果构建一个新表：

sqlCREATE TABLE customerdatacustomeraddressesgeocoded AS select address unitnumber municipality region postalcode country customerid publicfgeocodeaddress(address unitnumbermunicipalityregionpostalcodecountry) as geocoderesultFROM customerdatacustomeraddresses

使用以下代码提取不同的地址字段和纬度/经度坐标，并创建一个新表以包含结果：

sqlCREATE TABLE customerdatacustomeraddressespoints ASSELECT customerid geoaddress address unitnumber municipality region postalcode country longitude latitude STSetSRID(STMakePoint(Longitude Latitude)4326) as addresspoint 创建新 geom 列，类型为 POINT，设定新点的 SRID = 4326FROM(select customerid address unitnumber municipality region postalcode country cast(jsonextractpathtext(geocoderesult Label true) as VARCHAR) as geoaddress cast(jsonextractpathtext(geocoderesult Longitude true) as float) as longitude cast(jsonextractpathtext(geocoderesult Latitude true) as float) as latitude 使用 json 函数从 geocoderesult 中提取字段from customerdatacustomeraddressesgeocoded) a

这段代码使用 STPOINT 函数从纬度和经度坐标创建一个名为 addresspoint 的新列，类型为 GEOMETRY 和子类型 POINT。它使用 STSetSRID 地理空间函数将新列的空间参考标识符SRID设置为 4326。

SRID 定义了在评估几何数据时使用的空间参考系统。在连接或比较地理空间数据时，确保它们拥有匹配的 SRID 是非常重要的。您可以使用 STSRID 函数检查现有几何列的 SRID。更多有关 SRID 和 GEOMETRY 数据类型的信息，请参见 在 Amazon Redshift 中查询空间数据。

现在，您的客户地址已被地理编码为几何列中的纬度/经度点，您可以使用 JOIN 确定新点所落在的普查区块形状：

sqlCREATE TABLE customerdatacustomeraddresseswithcensus ASselect c shapesgeoid as censusgroupshape demofrom customerdatacustomeraddressespoints cinner join cartocensusdatacartogeographyusablockgroup2019 shapeson STContains(shapesgeom caddresspoint) 根据地址点是否位于普查区域几何范围内进行表连接inner join cartocensusdatausaacsdemographicssociodemographicsusablockgroup2019yearly2019 demoon demogeoid = shapesgeoid

上述代码创建了一个名为 customeraddresseswithcensus 的新表，该表将客户地址与所属的普查区块及其