【以下的问题经过翻译处理】 在Redshift中创建了一个外部表格,如下所示:
CREATE EXTERNAL TABLE "someschema"."avro_simple_nested" (
"actor_id" varchar(200),
"first_name" varchar(200),
"last_name" varchar(200),
"last_update" bigint,
"version" int,
"debut_film" struct<
"name":varchar(200),
"score":int
>
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
WITH SERDEPROPERTIES (
'avro.schema.literal'='{"namespace": "example.avro",
"type": "record",
"name": "actor",
"fields": [
{"name": "actor_id", "type": "string"},
{"name": "first_name", "type": "string"},
{"name": "last_name", "type": "string"},
{"default": 0, "name": "last_update", "type": "long"},
{"name": "version", "type": "int"},
{"name": "debut_film", "type": {"type":"record", "name":"debut_film_name",
"fields": [
{"name": "name", "type": "string"},
{"name": "score", "type": "int"}
]}}
]
}'
)
STORED AS AVRO
LOCATION 's3://....../..../avro-files-simple-nested/'
如果尝试使用“SELECT * from Table”查询表格,则会出现以下错误:
ERROR: Cannot expand query to include unsupported column type for column "debut_film".
如果尝试查询非结构化的列,能够看到数据。
如果在Athena中检查相同的数据,它显示正常。表格被正确显示并且数据符合预期。
如果查询SVV_EXTERNAL_COLUMNS表格,可以看到表格定义是正确的。
debut_film struct<name:varchar(200),score:int>
是否有使用过针对avro数据的外部表格,并成功在Redshift中查看嵌套数据?