AWS Redshift 频谱错误:“json"处或附近的语法错误 [英] AWS Redshift spectrum ERROR: syntax error at or near "json"

问题描述

我想创建一个使用红移光谱的外部表，文件作为json文件存储在s3中，具有单个对象，其值为对象数组.>

我对外部表的查询

创建外部表 jatinspectrum.extable (enteries array>)存储为 jsonLOCATION 's3://xxxxxxxxxxxxxxx/xxxxxxxxxxxxxx/xxxxxxxxxxx/';

它抛出一个错误 ERROR: syntax error at or near "json";位置:198

只是为了确保我的语法正确，我使用另一个查询将其保存为文本文件

创建外部表 jatinspectrum.extab (enteries array>)存储为文本文件LOCATION 's3://xxxxxxxxxxxxxxx/xxxxxxxxxxxxxx/xxxxxxxxxxx/';

这很有效，但这对我没有帮助，因为文本查询不支持嵌套数据.

解决方案

这对我有用，需要将此 stored as textfile 更改为此

ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'存储为文本文件

https://docs.aws.amazon.com/athena/latest/ug/parsing-JSON.html

I want to create an external table using redshift spectrum, files are stored in s3 as json file having a single object with values as an array of objects.

my query for the external table

CREATE EXTERNAL TABLE jatinspectrum.extable (
enteries array<struct<title:varchar(4000),link:varchar(4000),author:varchar(4000),published_date:timestamp,category:array<varchar(4000)>>>
)
stored as json
LOCATION 's3://xxxxxxxxxxxxxx/xxxxxxxxxxxxxx/xxxxxxxxxxx/';

it throws an error ERROR: syntax error at or near "json" Position: 198

just to make sure that i am syntactically right i saved it as text file using another query

CREATE EXTERNAL TABLE jatinspectrum.extab (
enteries array<struct<title:varchar(4000),link:varchar(4000),author:varchar(4000),published_date:timestamp,category:array<varchar(4000)>>>
)
stored as textfile
LOCATION 's3://xxxxxxxxxxxxxx/xxxxxxxxxxxxxx/xxxxxxxxxxx/';

this worked perfectly but this won't help me since nested data is not supported for text query.

解决方案

This worked for me, need to changes this stored as textfile to this

ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
stored as textfile

https://docs.aws.amazon.com/athena/latest/ug/parsing-JSON.html

这篇关于AWS Redshift 频谱错误:“json"处或附近的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！