Redshift加载JSON格式数据

一、背景

Redshift支持从S3加载CSV格式数据,也可以加载JSON格式数据。本文描述如何从Redshift加载JSON格式数据。

二、数据准备和IAM角色准备

1、复制数据到S3

首先将数据复制到S3存储桶的某个目录内,格式为JSON。

2、创建IAM角色

创建一个角色,使用者选择Redshift,在挂载策略界面,选择AWS托管策略 S3 Readonly,即对所有的桶有读取权限。

如果希望更细节的管控只对特定桶有权限,那么可以先创建一个策略,只对特定的存储桶赋予读取权限,然后在把这个策略挂载到一个角色上。

创建角色完毕后,记录下本角色的ARN,后续将会使用。

三、加载导入数据

1、创建表

位于S3上的原始的JSON数据样本如下。

{"_id":"01001","city":"AGAWAM","loc":[-72.622739,42.070206],"pop":15338,"state":"MA"}
{"_id":"01005","city":"BARRE","loc":[-72.108354,42.409698],"pop":4546,"state":"MA"}
{"_id":"01007","city":"BELCHERTOWN","loc":[-72.410953,42.275103],"pop":10579,"state":"MA"}

在Redshift上执行如下命令,创建表如下。这里简单一些将所有字段选用了字符串类型,实际可灵活调整

create table zips2 (
    _id varchar(16), 
    city varchar(32), 
    loc varchar(32),
    pop varchar(32),
    state varchar(32)
);

创建表成功。

2、加载数据

在Redshift上,执行如下命令从S3加载数据。

copy zips2 from 's3://mongodb-lxy/test02/export.json' 
iam_role 'arn:aws-cn:iam::420029960748:role/lxy-redshift'
format as json 'auto';

请替换其中的S3存储桶为实际的存放路径,替换IAM角色为实际角色的ARN。

执行效果如下。

Warnings:
Load into table 'zips2' completed, 29353 record(s) loaded successfully.

0 rows affected
COPY executed successfully

Execution time: 2.15s

加载数据成功。

3、查询测试

在Redshift上执行如下命令查询数据。

select * from zips2 limit 10;

返回结果如下。

_id   | city         | loc                    | pop   | state
------+--------------+------------------------+-------+------
01001 | AGAWAM       | [-72.622739,42.070206] | 15338 | MA   
01005 | BARRE        | [-72.108354,42.409698] | 4546  | MA   
01007 | BELCHERTOWN  | [-72.410953,42.275103] | 10579 | MA   
01008 | BLANDFORD    | [-72.936114,42.182949] | 1240  | MA   
01002 | CUSHMAN      | [-72.51565,42.377017]  | 36963 | MA   
01011 | CHESTER      | [-72.988761,42.279421] | 1688  | MA   
01010 | BRIMFIELD    | [-72.188455,42.116543] | 3706  | MA   
01012 | CHESTERFIELD | [-72.833309,42.38167]  | 177   | MA   
01013 | CHICOPEE     | [-72.607962,42.162046] | 23396 | MA   
01020 | CHICOPEE     | [-72.576142,42.176443] | 31495 | MA   

由此过程就实现了将JSON文件以结构化方式导入Redshift数据仓库中,并使用SQL做查询。