Redshift创建及导入数据

创建CloudFormation Stack

我们将使用以下CloudFormation来创建必须的资源：

https://pingfan.s3.amazonaws.com/files/immersionserverless.yaml

在CloudFormation中创建一个Stack，使用上面的URL：

image-20231118200244232

所有选项保持默认：

image-20231118200316665

点击创建，可能要等20-30min创建完成。

Query Editor连接Redshift

在Redshift服务中，打开Query Editor V2，然后连接到Serverless：workgroup-xxx集群。密码是

User Name: awsuser
Password: Awsuser123

image-20231119183207567

连接成功后执行以下命令查询用户列表：

select * from pg_user;

能成功返回结果：

image-20231119183307562

导入数据

Redshift 是符合 ANSI SQL 的数据仓库。 我们可以使用熟悉的 CREATE TABLE 语句创建表。

我们从 TPC Benchmark 数据模型创建 8 个表，请复制以下创建表语句并运行它们。 下面给出的是表格的数据模型。

img

在Query Editor中创建表：

DROP TABLE IF EXISTS partsupp;
DROP TABLE IF EXISTS lineitem;
DROP TABLE IF EXISTS supplier;
DROP TABLE IF EXISTS part;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customer;
DROP TABLE IF EXISTS nation;
DROP TABLE IF EXISTS region;

CREATE TABLE region (
  R_REGIONKEY bigint NOT NULL,
  R_NAME varchar(25),
  R_COMMENT varchar(152))
diststyle all;

CREATE TABLE nation (
  N_NATIONKEY bigint NOT NULL,
  N_NAME varchar(25),
  N_REGIONKEY bigint,
  N_COMMENT varchar(152))
diststyle all;

create table customer (
  C_CUSTKEY bigint NOT NULL,
  C_NAME varchar(25),
  C_ADDRESS varchar(40),
  C_NATIONKEY bigint,
  C_PHONE varchar(15),
  C_ACCTBAL decimal(18,4),
  C_MKTSEGMENT varchar(10),
  C_COMMENT varchar(117))
diststyle all;

create table orders (
  O_ORDERKEY bigint NOT NULL,
  O_CUSTKEY bigint,
  O_ORDERSTATUS varchar(1),
  O_TOTALPRICE decimal(18,4),
  O_ORDERDATE Date,
  O_ORDERPRIORITY varchar(15),
  O_CLERK varchar(15),
  O_SHIPPRIORITY Integer,
  O_COMMENT varchar(79))
distkey (O_ORDERKEY)
sortkey (O_ORDERDATE);

create table part (
  P_PARTKEY bigint NOT NULL,
  P_NAME varchar(55),
  P_MFGR  varchar(25),
  P_BRAND varchar(10),
  P_TYPE varchar(25),
  P_SIZE integer,
  P_CONTAINER varchar(10),
  P_RETAILPRICE decimal(18,4),
  P_COMMENT varchar(23))
diststyle all;

create table supplier (
  S_SUPPKEY bigint NOT NULL,
  S_NAME varchar(25),
  S_ADDRESS varchar(40),
  S_NATIONKEY bigint,
  S_PHONE varchar(15),
  S_ACCTBAL decimal(18,4),
  S_COMMENT varchar(101))
diststyle all;                                                              

create table lineitem (
  L_ORDERKEY bigint NOT NULL,
  L_PARTKEY bigint,
  L_SUPPKEY bigint,
  L_LINENUMBER integer NOT NULL,
  L_QUANTITY decimal(18,4),
  L_EXTENDEDPRICE decimal(18,4),
  L_DISCOUNT decimal(18,4),
  L_TAX decimal(18,4),
  L_RETURNFLAG varchar(1),
  L_LINESTATUS varchar(1),
  L_SHIPDATE date,
  L_COMMITDATE date,
  L_RECEIPTDATE date,
  L_SHIPINSTRUCT varchar(25),
  L_SHIPMODE varchar(10),
  L_COMMENT varchar(44))
distkey (L_ORDERKEY)
sortkey (L_RECEIPTDATE);

create table partsupp (
  PS_PARTKEY bigint NOT NULL,
  PS_SUPPKEY bigint NOT NULL,
  PS_AVAILQTY integer,
  PS_SUPPLYCOST decimal(18,4),
  PS_COMMENT varchar(199))
diststyle even;

从S3中加载数据到表中，COPY 命令可以有效地将大量数据从S3 加载到Redshift 中。 单个 COPY 命令可以将多个文件加载到一个表中，它会自动从提供的 S3 路径中并行加载数据。

如果目标表为空，COPY 命令可以根据列数据类型自动对每一列进行压缩编码，这样就不必担心为每一列选择正确的压缩算法。 为了确保 Redshift 执行压缩分析，我们将在 COPY 命令中将 COMPUPDATE 参数设置为 PRESET。

要将数据加载到从TPC Benchmark 数据模型创建的 8 个表中，执行以下COPY命令：

COPY region FROM 's3://redshift-immersionday-labs/data/region/region.tbl.lzo'
iam_role default
region 'us-west-2' lzop delimiter '|' COMPUPDATE PRESET;

COPY nation FROM 's3://redshift-immersionday-labs/data/nation/nation.tbl.'
iam_role default
region 'us-west-2' lzop delimiter '|' COMPUPDATE PRESET;

copy customer from 's3://redshift-immersionday-labs/data/customer/customer.tbl.'
iam_role default
region 'us-west-2' lzop delimiter '|' COMPUPDATE PRESET;

copy orders from 's3://redshift-immersionday-labs/data/orders/orders.tbl.'
iam_role default
region 'us-west-2' lzop delimiter '|' COMPUPDATE PRESET;

copy part from 's3://redshift-immersionday-labs/data/part/part.tbl.'
iam_role default
region 'us-west-2' lzop delimiter '|' COMPUPDATE PRESET;

copy supplier from 's3://redshift-immersionday-labs/data/supplier/supplier.json' manifest
iam_role default
region 'us-west-2' lzop delimiter '|' COMPUPDATE PRESET;

copy lineitem from 's3://redshift-immersionday-labs/data/lineitem-part/'
iam_role default
region 'us-west-2' gzip delimiter '|' COMPUPDATE PRESET;

copy partsupp from 's3://redshift-immersionday-labs/data/partsupp/partsupp.tbl.'
iam_role default
region 'us-west-2' lzop delimiter '|' COMPUPDATE PRESET;
  • COMPUPDATE PRESET 将使用与列的数据类型相关的Redshift 最佳实践来分配压缩算法，但不分析表中的数据。
  • REGION 表的 COPY 指向特定文件 (region.tbl.lzo)，而其他表的 COPY 则指向多个文件的前缀 (lineitem.tbl.)。
  • SUPPLIER 表的 COPY 指向清单文件 (supplier.json)。 清单文件是一个 JSON 文件，其中列出了要加载的文件及其位置。

加载数据的预估时间：

  • REGION (5 rows) - 2s
  • NATION (25 rows) - 2s
  • CUSTOMER (15M rows) – 2m
  • ORDERS - (76M rows) - 10s
  • PART - (20M rows) - 2m
  • SUPPLIER - (1M rows) - 10s
  • LINEITEM - (303M rows) - 22s
  • PARTSUPPLIER - (80M rows) - 15s

image-20231119183715528

让我们检查几个表的数据条数，以确保数据按预期加载：

select count(*) from region; -- 5
select count(*) from nation; -- 25
select count(*) from orders; -- 76000000

image-20231119183828169

Load数据的troubleshooting

要解决数据加载问题，可以查询 SYS_LOAD_ERROR_DETAIL表。

此外，我们可以验证数据而无需实际加载表，将 NOLOAD 选项与 COPY 命令一起使用，以确保在运行实际数据加载之前先进行检验。

请注意，使用 NOLOAD 选项运行 COPY 比加载数据快得多，因为它只解析文件。

让我们尝试使用具有不匹配列的数据文件加载到CUSTOMER 表：

COPY customer FROM 's3://redshift-immersionday-labs/data/nation/nation.tbl.'
iam_role default
region 'us-west-2' lzop delimiter '|' noload;

会提示报错ERROR: Load into table ‘customer’ failed. Check ‘sys_load_error_detail’ system table for details. ：

image-20231119183910564

检查STL_LOAD_ERROR表：

select * from SYS_LOAD_ERROR_DETAIL;

image-20231119184339888

看到数据类型为 int 的 c_nationkey 列中有一行带有错误消息"Invalid digit, Value 'h', Pos 1"，表明正在尝试将字符加载到整数列中。