AWS Glue自定义分类器Json路径 [英] AWS Glue Custom Classifiers Json Path

问题描述

我有一组看起来像这样的Json数据文件

I have a set of Json data files that look like this

[
  {"client":"toys",
   "filename":"toy1.csv",
   "file_row_number":1,
   "secondary_db_index":"4050",
   "processed_timestamp":1535004075,
   "processed_datetime":"2018-08-23T06:01:15+0000",
   "entity_id":"4050",
   "entity_name":"4050",
   "is_emailable":false,
   "is_txtable":false,
   "is_loadable":false}
]

我使用以下自定义分类器Json Path创建了一个Glue Crawler

I have created a Glue Crawler with the following custom classifier Json Path

$[*]

Glue返回具有正确标识列的正确架构。

Glue returns the correct schema with the columns correctly identified.

但是，当我在Athena上查询数据时，所有数据都在第一列中，其余列为空。

However, when I query the data on Athena... all the data is landing in the first column and the rest of the columns are empty.

我如何才能根据其列分散数据？

How can I get the data to spread according to their columns?

雅典娜查询图片

谢谢！

推荐答案

这是与Hive相关的问题。我建议两种方法。首先，您可以在雅典娜中使用结构数据类型创建新表，如下所示：

It is a issue connected to Hive. I suggest two approaches. Firstly, you can create new table in Athena with struct data type like this:

CREATE EXTERNAL TABLE `example`(
`row` struct<client:string,filename:string,file_row_number:int,secondary_db_index:string,processed_timestamp:int,processed_datetime:string,entity_id:string,entity_name:string,is_emailable:boolean,is_txtable:boolean,is_loadable:boolean> COMMENT 'from deserializer')
ROW FORMAT SERDE 
'org.openx.data.jsonserde.JsonSerDe' 
STORED AS INPUTFORMAT 
'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://example'
TBLPROPERTIES (
'CrawlerSchemaDeserializerVersion'='1.0', 
'CrawlerSchemaSerializerVersion'='1.0', 
'UPDATED_BY_CRAWLER'='example', 
'averageRecordSize'='271', 
'classification'='json', 
'compressionType'='none', 
'jsonPath'='$[*]', 
'objectCount'='1', 
'recordCount'='1', 
'sizeKey'='271', 
'transient_lastDdlTime'='1535533583', 
'typeOfData'='file')

然后您可以如下运行查询：

And then you can run the query as follows:

SELECT row.client, row.filename, row.file_row_number FROM "example"

第二，您可以重新设计您的json文件，如下所示，然后再次运行Crawler。在此示例中，我使用了每行记录JSON格式。

Secondly, you can re-design your json file as below and then run the Crawler again. In this example I used Single-JSON-Record-Per-Line format.

{"client":"toys","filename":"toy1.csv","file_row_number":1,"secondary_db_index":"4050","processed_timestamp":1535004075,"processed_datetime":"2018-08-23T06:01:15+0000","entity_id":"4050","entity_name":"4050","is_emailable":false,"is_txtable":false,"is_loadable":false},
{"client":"toys2","filename":"toy2.csv","file_row_number":1,"secondary_db_index":"4050","processed_timestamp":1535004075,"processed_datetime":"2018-08-23T06:01:15+0000","entity_id":"4050","entity_name":"4050","is_emailable":false,"is_txtable":false,"is_loadable":false}

这篇关于AWS Glue自定义分类器Json路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！