从 Greenplum 到亚马逊 Redshift 的代码转换：处理数组、日期和正则表达式

Amazon Redshift 是一项完全托管的服务，适用于初创企业、中型企业和大型企业的数据湖、数据分析和数据仓库。全球成千上万的企业使用Amazon Redshift对其数据分析平台进行现代化改造。

Green plum 是一个开源、大规模并行的数据库，用于分析，主要用于本地基础架构。Greenplum 基于 PostgreSQL 数据库引擎。

许多客户发现从 Greenplum 迁移到亚马逊 Redshift 比管理本地 Greenplum 更具吸引力，原因如下：

实现数据湖和数据仓库环境现代化的机会
其他亚马逊云科技服务的好处，例如亚马逊简单存储服务（亚马逊 S3）、亚马逊 CloudWatch、亚马逊 EMR 、亚马逊 SageMaker 等

尽管Greenplum和Amazon Redshift都使用开源的PostgreSQL数据库引擎，但迁移仍然需要大量的计划和手动干预。这篇文章介绍了从Greenplum到Amazon Redshift进行代码转换时的关键功能和注意事项。它侧重于过程、函数和视图的迁移。

解决方案概述

亚马逊云科技数据库迁移服务 (亚马逊云科技 DMS) 和亚马逊云科技架构转换工具 (亚马逊云科技 SCT) 可以将异构数据库迁移中的大部分对象从 Greenplum 迁移到 Amazon Redshift。但是在某些情况下，代码转换团队在 Amazon Redshift 中创建视图、过程和函数时会遇到错误和警告。要解决这种情况，需要手动转换代码。

这些文章重点介绍在从 Greenplum 迁移到亚马逊 Redshift 时如何处理以下问题：

数组
日期和时间戳
正则表达式（正则表达式）

请注意，在这篇文章中，我们使用了 Greenplum 4.3 和亚马逊 Redshift PostgreSQL 8.2。

使用数组函数

从 Greenplum 或 PostgreSQL 迁移到亚马逊 Redshift 时，亚马逊云科技 SCT 不会转换数组函数。开发人员需要大量手动转换这些函数。这篇文章概述了最常见的数组函数：

ARRAY_UPPER
JSON_EXTACT_ARRAY_ELEMENT_TEXT 和 JSON_ARRAY_LENGTH
UNNEST ()
STRING_AGG ()
任意数组 ()

ARRAY_UPPER ()

此函数返回数组的上限。它可以用来从 PostgreSQL 或 Greenplum 中的数组中提取^第 n 个元素。

Greenplum 代码如下所示：

With temp1 as
(
Select 'John' as FirstName, 'Smith' as LastName ,
array['"111-222-3333"','"101-201-3001"','"XXX-YYY-ZZZZ"','NULL'] as PhoneNumbers
union all
Select 'Bob' as FirstName, 'Haris' as LastName ,
array['222-333-4444','201-301-4001','AAA-BBB-CCCC'] as PhoneNumbers
union all
Select 'Mary' as FirstName, 'Jane' as LastName ,
array['333-444-5555','301-401-3001','DDD-EEE-FFFF'] as PhoneNumbers
)
Select Firstname, PhoneNumbers[ARRAY_UPPER(PhoneNumbers,1)]

在 Amazon Redshift 中没有从数组中提取元素的函数；但是，有两个 JSON 函数可用于此目的：

JSON_EXTRACT_ARRAY_ELEMENT_TEXT () — 返回 JSON 字符串最外层数组中的 JSON 数组元素
JSON_ARRAY_LENGTH () — 返回 JSON 字符串外部数组中元素的数量

参见以下代码：

With temp1 as
(
Select 'John' as FirstName, 'Smith' as LastName ,
array['"111-222-3333"','"101-201-3001"','"XXX-YYY-ZZZZ"'] as PhoneNumbers
union all
Select 'Bob' as FirstName, 'Haris' as LastName ,
array['"222-333-4444"','"201-301-4001"','"AAA-BBB-CCCC"'] as PhoneNumbers
union all
Select 'Mary' as FirstName, 'Jane' as LastName ,
array['"333-444-5555"','"301-401-3001"','"DDD-EEE-FFFF"'] as PhoneNumbers
)

Select
FirstName
,('['+array_to_string(phoneNumbers,',')+']') as JSONConvertedField
,JSON_EXTRACT_ARRAY_ELEMENT_TEXT
(
'['+array_to_string(phoneNumbers,',')+']'
,JSON_ARRAY_LENGTH('['+array_to_string(phoneNumbers,',')+']')-1
) as LastElementFromArray
from temp1

UNNEST ()

UNNEST () 是 PostgreSQL 的系统函数，用于处理半结构化数据、将数组扩展或数组组合为一组行。引入它是为了提高数千条记录的数据库性能，用于插入、更新和删除。

您可以将 UNNEST () 用于基本数组、多个数组和多个不同长度的数组。

一些用于取消嵌套数组的亚马逊 Redshift 函数是 s plit_part 、json_extract_path_text、json_array_length 和 j son_extract_array_element_text。

在 Greenplum 中，UNNEST 函数用于将数组扩展为一组行：

Select ‘A’,unnest(array([1,2])

输出
A 1
A 2

with temp1 as
(
Select 'John' as FirstName, 'Smith' as LastName ,
'111-222-3333' as Mobilephone,'101-201-3001' as HomePhone
union all
Select 'Bob' as FirstName, 'Haris' as LastName ,
'222-333-4444' as Mobilephone,'201-301-4001' as HomePhone
union all
Select 'Mary' as FirstName, 'Jane' as LastName ,
'333-444-5555' as Mobilephone,'301-401-3001' as HomePhone
)

select
FirstName
,LastName
,unnest(array[‘Mobile’::text,’HomePhone’::text]) as PhoneType
,unnest(array[MobilePhone::text,HomePhone::text]) as PhoneNumber
from
temp1
order by 1,2,3

亚马逊 Redshift 不支持 UNNEST 功能；你可以使用以下解决方法：

with temp1 as
(
Select 'John' as FirstName, 'Smith' as LastName ,
'111-222-3333' as Mobilephone,'101-201-3001' as HomePhone
union all
Select 'Bob' as FirstName, 'Haris' as LastName ,
'222-333-4444' as Mobilephone,'201-301-4001' as HomePhone
union all
Select 'Mary' as FirstName, 'Jane' as LastName ,
'333-444-5555' as Mobilephone,'301-401-3001' as HomePhone
),
ns as
(
Select row_number() over(order by 1) as n from pg_tables
)

Select
FirstName
,LastName
,split_part('Mobile,Home',',',ns.n::int) as PhoneType
,split_part(MobilePhone|| '&&' || HomePhone, '&&', ns.n::int) as PhoneNumber
from
temp1, ns
where
ns.n<=regexp_count('Mobile,Home',',')+1
order by 1,2,3

When the element of array is in the form of array itself, use the JSON_EXTRACT_ARRAY_ELEMENT_TEXT() function and JSON_ARRAY_LENGTH:

with ns as
(
Select row_number() over(order by 1) as n from pg_tables
)

Select JSON_EXTRACT_ARRAY_ELEMENT_TEXT('["arrayelement1","arrayelement2"]',ns.n-1)
from ns
where
ns.n<=JSON_ARRAY_LENGTH('["arrayelement1","arrayelement2"]')

STRING_AGG ()

STRING_AGG () 函数是一个聚合函数，用于连接字符串列表并在它们之间放置分隔符。该函数不在字符串的末尾添加分隔符。参见以下代码：

STRING_AGG ( expression, separator [order_by_clause] )

Greenplum 代码如下所示：

with temp1 as
(
Select 'Finance'::text as Dept, 'John'::text as FirstName, 'Smith'::text as LastName
union all
Select 'Finance'::text as Dept, 'John'::text as FirstName, 'Doe'::text as LastName
union all
Select 'Finance'::text as Dept, 'Mary'::text as FirstName, 'Jane'::text as LastName
union all
Select 'Marketing'::text as Dept, 'Bob'::text as FirstName, 'Smith'::text as LastName
union all
Select 'Marketing'::text as Dept, 'Steve'::text as FirstName, 'Smith'::text as LastName
union all
Select 'Account'::text as Dept, 'Phil'::text as FirstName, 'Adams'::text as LastName
union all
Select 'Account'::text as Dept, 'Jim'::text as FirstName, 'Smith'::text as LastName
)

Select dept,STRING_AGG(FirstName||' '||LastName,' ; ') as Employees from temp1 group by dept order by 1

STRING_AGG () 函数的等效亚马逊 Redshift 是 LISTAGG ()。此聚合函数根据 ORDER BY 表达式对该组的行进行排序，然后将值连接成单个字符串：

LISTAGG(expression, separator [order_by_clause])

参见以下代码：

Create temporary Table temp1 as
(
Select 'Finance'::text as Dept, 'John'::text as FirstName, 'Smith'::text as LastName
union all
Select 'Finance'::text as Dept, 'John'::text as FirstName, 'Doe'::text as LastName
union all
Select 'Finance'::text as Dept, 'Mary'::text as FirstName, 'Jane'::text as LastName
union all
Select 'Marketing'::text as Dept, 'Bob'::text as FirstName, 'Smith'::text as LastName
union all
Select 'Marketing'::text as Dept, 'Steve'::text as FirstName, 'Smith'::text as LastName
union all
Select 'Account'::text as Dept, 'Phil'::text as FirstName, 'Adams'::text as LastName
union all
Select 'Account'::text as Dept, 'Jim'::text as FirstName, 'Smith'::text as LastName
)

Select dept,LISTAGG(FirstName||' '||LastName,' ; ') as Employees from temp1
group by dept
order by 1

任意数组 ()

PostgreSQL ANY ARRAY () 函数计算左边的表达式并将其与数组中的每个元素进行比较：

Select * from temp1 where DeptName = ANY ARRAY('10-F','20-F','30-F')

在亚马逊 Redshift 中，可以使用 IN 运算符进行评估：

Select * from temp1 where DeptName IN ('10-F','20-F','30-F')

使用日期函数

在本节中，我们将讨论如何计算 Greenplum 的 date_part 和亚马逊 Redshift 的 datediff 之间的差异。

当应用程序需要计算 Greenplum 的日期子字段之间的差异时，它会使用函数 date_part ，该函数允许您检索子字段，例如年、月、周和日。在以下示例查询中，我们通过计算原始日期和 e co_date 之间的差异来计算完成天数。

为了计算日期的子字段之间的差异，Amazon Redshift 使用了 datediff 函数。以下查询显示了如何将完成天数计算为 eco _date 和 orginated_dat e 之间的差异的示例。 DATEDIFF 确定两个表达式之间交叉的日期部分边界的数量。

我们将 Greenplum 和 Amazon Redshift 查询进行了如下比较：

年份差异

以下 Greenplum 查询返回的是 2009-01-01 到 2009-12-31 之间的 1 年：

SELECT date_part(‘year’, TIMESTAMP ‘2009-01-01’) - date_part(‘year’, 2008-12-31’) as year;

以下亚马逊 Redshift 查询返回了 2009-01-01 到 2009-12-31 之间的 1 年：

SELECT datediff (year, ‘2008-12-31’ , ‘2009-01-01’ ) as year;

按月的差异

以下 Greenplum 查询返回 2009-01-01 到 2008-12-31 之间的 1 个月：

SELECT (date_part(‘year’, ‘2009-01-01’ :: date) - date_part(‘year’, ‘2008-12-31’ :: date)) * 12 +<br />(date_part(‘month’, ‘2009-01-01’) - date_part(‘month’, ‘2008-12-31’ :: date)) as month;

以下亚马逊 Redshift 查询返回了 2009-01-01 到 2008-12-31 之间的 1 个月：

SELECT datediff( month, ‘2008-12-31’ , ‘2009-01-01’ ) as month;

每周差异

以下 Greenplum 查询返回 2009-01-01 到 2009-12-31 之间的 0 周：

SELECT date_part(‘week’, timestamp ‘2009-01-01’ ) - date_part(‘week’, timestamp ‘2008-12-31’) as week;

以下亚马逊 Redshift 查询返回 2009-01-01 到 2009-12-31 之间的 0 周：

SELECT datediff( week, ‘2008-12-31’ , ‘2009-01-01’ ) as week;

每日差异

以下 Greenplum 查询返回 1 天：

SELECT date_part ('day', '2009-01-01 24:00:00' :: timestamp - '2008-12-31 24:00:00 :: timestamp) as day;

以下亚马逊 Redshift 查询返回 1 天：

SELECT datediff (day, ‘2008-12-31’, ‘2009-01-01’) as day;

每小时的差异

以下 Greenplum 查询返回 1 小时：

SELECT date_part(‘hour’, ‘2009-01-01 22:56:10’ :: timestamp - ‘2008-12-31 21:54:55' :: timestamp)

以下亚马逊 Redshift 查询返回 1 小时：

SELECT datediff (hour, ‘2009-01-01 21:56:10’, ‘2009-01-01’ ) as hour;

以分钟为单位的差异

以下 Greenplum 查询返回 3 分钟：

SELECT date_part(‘minute’, ‘2009-01-01 22:56:10’ :: timestamp - ‘2009-01-01 21:53:10’ :: timestamp) as minutes;

以下亚马逊 Redshift 查询返回 1 分钟：

SELECT datediff(minute, ‘2009-01-01 21:56:10’, ‘2009-01-01 21:57:55’) as minute;

以秒为单位的差值

以下 Greenplum 查询返回 40 秒：

SELECT date_part(‘second’, ‘2009-01-01 22:56:50’ :: timestamp - ‘2009-01-01 21:53:10’ : : timestamp) as seconds;

以下亚马逊 Redshift 查询返回 45 秒：

SELECT datediff(second, ‘2009-01-01 21:56:10’, ‘2009-01-01- 21:56:55’) as seconds;

现在让我们来看看我们如何使用 Amazon Redshift 以秒为单位计算天数和周数。

以下 Amazon Redshift 查询显示 2 天：

SELECT datediff(second, ‘2008-12-30 21:56:10’, ‘2009-01-01- 21:56:55’)/(60*60*24) as days;

以下亚马逊 Redshift 查询显示 9 周：

SELECT datediff(second, ‘2008-10-30 21:56:10’, ‘2009-01-01- 21:56:55’)/(60*60*24*7) as weeks;

对于 Greenplum，日期子字段需要使用单引号，而对于 Amazon Redshift，我们可以使用不带引号的年份、月、周、日、分、秒等日期子字段。对于 Greenplum，我们必须将子字段从一部分减到另一部分，而对于 Amazon Redshift，我们可以使用逗号将两个日期分开。

从日期中提取 ISOYEAR

ISOYEAR 8601 是一个以周为单位的年份。它从包含1月4日的一周的星期一开始。因此，对于1月初或12月下旬的日期，ISO年份可能与格里高利年份不同。国际标准化组织年度有 52 或 53 个完整星期（364 或 371 天）。额外的一周称为闰周；有这样一周的年份被称为闰年。

以下 Greenplum 查询显示了 2020 年 ISOYEAR：

SELECT extract (ISOYEAR from ‘2019-12-30’ :: date) as ISOYEARS;

以下亚马逊 Redshift 查询显示了 2020 年 ISOYEAR：

SELECT to_char(‘2019-12-30’ :: date, ‘IYYYY’) as ISOYEARS;

生成_series的函数 ()

Greenplum 采用了 PostgreSQL 函数 gen er ate_series ()。但是，在从表中检索记录时，g enerate_series 函数在 Amazon Redshift 中的工作方式有所不同，因为它是一个仅限领导节点的函数。

要在 Amazon Redshift 中显示一系列数字，请在领导节点上运行以下查询。在此示例中，它显示 10 行，编号为 1—10：

SELECT generate_series(1,10);

要显示给定日期的一系列天，请使用以下查询。它从给定日期中提取日期并减去 1，以显示一系列从 0—6 的数字：

SELECT generate_series(0, extract(day from date ‘2009-01-07’) :: int -1);

但是对于从表中获取记录、联接另一个表的行以及在计算节点处理数据的查询，它不起作用，并且会生成一条带有 Invalid Operation 的错误消息。以下代码是适用于 Greenplum 但在 Amazon Redshift 上失败的 SQL 语句的示例：

SELECT column_1,
FROM table_1t1
JOIN table_2 t2
ON t2.code = t1.code
CROSS JOIN generate_series(1,12) gen(fiscal_month)
WHERE condition_1

对于 Amazon Redshift，解决方案是创建一个表来存储序列数据，然后按如下方式重写代码：

SELECT column1,
FROM table_t1 t1
JOIN table_t2 t2
ON t2.code = t1.code
CROSS JOIN (select “number” as fiscal_month FROM table_t3 WHERE “number”<=12) gen
WHERE condition_1

使用正则表达式（正则表达式函数）

亚马逊 Redshift 和 Greenplum 都支持三种模式匹配条件：

像
类似于
POSIX 运算符

在这篇文章中，我们不会详细讨论所有这些模式匹配。相反，我们将讨论 Amazon Redshift 不支持的一些正则表达式函数和正则表达式转义字符。

regexp_split_to_Table 函数

regex_split_to_Table 函数使用 POSIX 正则表达式模式作为分隔符来拆分字符串。

此函数的语法如下：

Regexp_split_to_table(string,pattern [,flags])

对于 Greenplum，我们使用以下查询：

select regexp_split_to_table ('bat,cat,hat',’\,’) as regexp_split_table_GP

对于亚马逊 Redshift，必须使用亚马逊 Redshift sp lit_part 函数来转换 regexp_split_to_t able 函数：

SELECT column1,
FROM table_t1 t1
JOIN table_t2 t2
ON t2.code = t1.code
CROSS JOIN (select “number” as fiscal_month FROM table_t3 WHERE “number”<=12) gen
WHERE condition_1

另一种将 regexp_split_t able 转换为表的方法如下：

SELECT column1,
FROM table_t1 t1
JOIN table_t2 t2
ON t2.code = t1.code
CROSS JOIN (select “number” as fiscal_month FROM table_t3 WHERE “number”<=12) gen
WHERE condition_1

来自正则表达式的子字符串

子字符串（来自正则表达式模式的字符串）提取与传递的模式相匹配的子字符串或值。如果没有匹配项，则返回 null。有关更多信息，请参阅模式匹配。

我们在 Greenplum 中使用以下代码：

create temp table data1 ( col1 varchar );
insert into data1 values ('hellohowareyou 12\687687abcd');
select substring( col1 from '[A-Za-z]+$') from data1;
from data1

我们可以使用 regexp_substr 函数将这段代码转换为亚马逊 Redshift。它通过搜索正则表达式模式返回从字符串中提取的字符。语法如下所示：

REGEXP_SUBSTR ( source_string, pattern [, position [, occurrence [, parameters ] ] ] )

select regexp_substr( col1, '[A-Za-z]+$') as substring_from_rs from data1

转换正则表达式转义符时的要点

Postgres 转义字符 E 在亚马逊 Redshift 中不起作用。此外，亚马逊 Redshift 不支持以下 Greenplum 正则表达式限制：

\ m — 仅匹配单词的开头
\ y — 仅匹配单词的开头或结尾

对于亚马逊 Redshift，请改用\\ < and\\ > 或 [[:<:]] and [[:>:]]。

在 Greenplum 上使用以下代码：

select col1,
case
when (col1) ~ E '\\m[0-9]{2}[A-Z]{1}[0-9]{1}' then
regexp_replace(col1, E '([0-9]{2})([A-Z]{1})([0-9]{1})',E '\\2')
else 'nothing'
end as regex_test
from temp1123

使用以下代码购买亚马逊 Redshift：

select col1,
case
when (col1) ~ '\\<[0-9]{2}[A-Z]{1}[0-9]{1}>\\' then
regexp_replace(col1,'([0-9]{2})([A-Z]{1})([0-9]{1})','\\2')
else 'nothing'
end as regex_test
from temp1123

或者

select col1,
case
when (col1) ~ '[[:<:]][0-9]{2}[A-Z]{1}[0-9]{1}[[:>:]]' then
regexp_replace(col1,'([0-9]{2})([A-Z]{1})([0-9]{1}) (.*)','\\2')
else 'nothing'
end as regex_test
from temp1123

结论

要将异构数据库从 Greenplum 迁移到 Amazon Redshift，您可以使用亚马逊云科技 DMS 和亚马逊云科技 SCT 迁移大部分数据库对象，例如表、视图、存储过程和函数。

在某些情况下，一个函数用于源环境，而目标环境不支持相同的函数。在这种情况下，需要手动转换才能生成相同的结果集并完成数据库迁移。

在某些情况下，事实证明，使用目标环境支持的新窗口功能可以更有效地处理千兆字节的数据。

这篇文章包括几种需要手动转换代码的情况，这也提高了代码效率并提高了查询效率。

如果您有任何问题或建议，请分享您的反馈。

作者简介

贾格里特·什雷斯塔 是亚马逊网络服务（亚马逊云科技）的数据库顾问。他是一名数据库专家，帮助客户将其本地数据库工作负载迁移到亚马逊云科技并提供技术指导。

伊什瓦尔·阿迪卡里 是亚马逊网络服务（亚马逊云科技）的数据库顾问。他与客户紧密合作，以实现其数据库和应用程序基础架构的现代化。他的重点领域是将关系数据库从本地数据中心迁移到亚马逊云科技 Cloud。

Shrenik Pare kh 在亚马逊网络服务 (亚马逊云科技) 担任数据库顾问。他在数据库迁移评估、数据库迁移、使用亚马逊云科技云数据库服务使用专用数据库对数据库环境进行现代化改造方面拥有专业知识。他还专注于用于数据分析的亚马逊云科技 Web 服务。在业余时间，他喜欢远足、瑜伽和其他户外活动。

Santhosh Meenhallimat h 是亚马逊云科技的数据架构师。他致力于构建分析解决方案、构建数据湖以及将数据库迁移到亚马逊云科技。

亚马逊云科技精选博客