“自动"将数据从S3加载到Redshift表中时，大小写是否重要? [英] Does case matter when 'auto' loading data from S3 into a Redshift table?

问题描述

按照

I am loading data from S3 into Redshift using the COPY command, the gzip flag and the 'auto' format, as per this documentation on loading from S3, this documentation for using the 'auto' format in AWS, and this documentation for addressing compressed files.

我的数据是高度嵌套的JSON格式，并且我创建了redshift表，以使列名与JSON结构的最高级别完全匹配(允许自动"工作).

My data is a highly nested JSON format, and I have created the redshift table such that the column names match exactly the highest level of the JSON structure (which allows 'auto' to work).

例如，我的JSON数据如下:

For instance, my JSON data look like this:

{"timestamp":{"value":"1480536125926814862"},
"Version":{"value":"0.5.0"},
"token":{"timestamp":"1480536122147094466",
  "field1":"A23",
  "field2":"0987adsflhja0",
  "field3":"asd0-fuasjklhf"},
"camelCaseField":{"value":"asdf1234"},
"camelCaseField2":{"value":"asdfasdfasdf1234"},
"sequence":{"value":1}
}

我的表创建语句如下:

CREATE TABLE temp_table (
timestamp varchar(40),
Version varchar(40),
token varchar(500),
camelCaseField varchar(40),
camelCaseField2 varchar(40),
sequence varchar(10));

然后，当我使用以下命令从S3加载表时:

Then when I load the table from S3 using this command:

COPY temp_table FROM 's3://bucket-name/file_name.log.gz' 
credentials '<aws-cred-args>'
json 'auto'
gzip;

它加载数据时没有错误，但是带有camelcase的任何字段均为空，并且只有时间戳，令牌和序列中包含数据.这真的是一个案例问题吗?

It loads the data without error, but any of the fields with camelcase are empty and only timestamp, token and sequence have data in them. Is this really a case problem?

推荐答案

Redshift将所有列名称更改为小写名称(与定义表时使用的大小写无关).因此，在您的情况下，仅会加载仅包含小写字母名称的字段.

Redshift changes all the column names to lower case names (irrespective of what case you use while defining the table). So, in your case only the fields with only lower case names are getting loaded.

这篇关于“自动"将数据从S3加载到Redshift表中时，大小写是否重要?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！