让我试着解释一下我在前面看到的几个问题。
看起来您想要的输出需要一些数据，这些数据是路径文件位置、设备和传感器的一部分，但是它没有定义为表定义的一部分，只有表定义中的列或虚拟列可用。
几个小文件可能会影响查询的性能（但这不会影响所需的结果）
配置单元分区用于提高查询的性能，避免扫描所有数据。分区指向文件夹，在这种情况下，您试图访问特定的文件
您想要的输出基本上是将一条记录分解成多条记录，这不应该在表定义中处理，可以通过select语句来完成
配置单元分区的命名约定为 partitionname=partitionvalue ，这不是必需的，但如果您希望提前执行命令以根据文件夹结构自动添加分区，则很有用。
这是我将如何解决你的问题，如果你将主要是由传感器或设备查询

更改数据结构

理想的文件夹结构应该是

farm_iot/sensor_data/farm/farm0001/sensor01/1541252701443

至农场/传感器/数据/农场/设备=农场0001/传感器=传感器01/1541252701443

更改表定义

您的表定义应该包含分区位置，以便能够在不使用regex的情况下选择它，并利用它的性能改进（我猜公共查询将按设备或传感器进行过滤）。除此之外，还需要添加作为文件一部分的所有json列

CREATE EXTERNAL TABLE IF NOT EXISTS farm.sensor_data (
  temperature double,
  preassure double,
  humidity double
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
) LOCATION 's3://farm-iot/sensor_data/farm/'
PARTITIONED BY (device string, sensor string)
TBLPROPERTIES ('has_encrypted_data'='false')

查询您的数据

我们缺少时间戳，它本质上是json输入文件名的一部分。我们可以在select语句中使用virtualcolumn包含文件名 INPUT__FILE__NAME 如下所示

select device, sensor, temperature, preassure, humidity, INPUT__FILE__NAME as mytimestamp from farm.sensor_data

如果您想要预压力、温度和湿度以及不同的行，我建议您创建一个包含这三个行的数组并将其分解，使用union all运行3个查询来附加结果应该会非常有效

添加新分区

如果遵循配置单元约定，则可以利用命令msck repair table在包含新设备/传感器后自动添加新分区。在最坏的情况下，如果您想保持您的文件夹结构，您可以添加分区如下

ALTER TABLE test ADD PARTITION (device='farm0001', sensor='sensor01') location 's3://farm_iot/sensor_data/farm/farm0001/sensor01'

注意：新分区不会自动添加，您始终需要添加它们
我尽量增加细节。如果有什么不清楚的，请告诉我。
编辑：如果您的查询主要基于时间序列（例如日期范围），我建议您在日级别添加一个分区（不小于此分区），以提高查询的性能。所以表定义如下

CREATE EXTERNAL TABLE IF NOT EXISTS farm.sensor_data (
  temperature double,
  preassure double,
  humidity double
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
) LOCATION 's3://farm-iot/sensor_data/farm/'
PARTITIONED BY (dt=long, device string, sensor string)
TBLPROPERTIES ('has_encrypted_data'='false')

你的文件夹结构看起来像
农场物联网/传感器数据/农场/dt=20191204/设备=farm0001/传感器=sensor01/1541252701443
作为说明，您不需要为每个新分区修改表，只需将这个分区添加到表中，这基本上就是hive知道新分区是如何创建的。如果您决定使用分区，这是唯一的方法，如果您不使用分区（这将影响性能），那么还有其他一些方法可以让它工作
编辑2：
如果您想保持数据结构的原样而不使用分区，那么可以得到如下预期结果

CREATE EXTERNAL TABLE IF NOT EXISTS yourdb.sensordata (
  temperature double,
  pressure double,
  humidity double
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
) 
LOCATION 's3://farm-iot/sensor_data/farm/'
TBLPROPERTIES ('has_encrypted_data'='false');

SET hive.mapred.supports.subdirectories=TRUE;
SET mapred.input.dir.recursive=TRUE;
select * from yourdb.sensordata;

select 
split(input__file__name, "/")[size(split(input__file__name, "/")) - 1] as ts,
split(input__file__name, "/")[size(split(input__file__name, "/")) - 3] as device,
split(input__file__name, "/")[size(split(input__file__name, "/")) - 2] as sensor,
'temperature' as data_point,
temperature as value
from yourdb.sensordata
union all
select 
split(input__file__name, "/")[size(split(input__file__name, "/")) - 1] as ts,
split(input__file__name, "/")[size(split(input__file__name, "/")) - 3] as device,
split(input__file__name, "/")[size(split(input__file__name, "/")) - 2] as sensor,
'pressure' as data_point,
pressure as value
from yourdb.sensordata
union all
select 
split(input__file__name, "/")[size(split(input__file__name, "/")) - 1] as ts,
split(input__file__name, "/")[size(split(input__file__name, "/")) - 3] as device,
split(input__file__name, "/")[size(split(input__file__name, "/")) - 2] as sensor,
'humidity' as data_point,
humidity as value
from yourdb.sensordata;

如您所见，我从文件路径获得了大部分信息，但是需要设置一些标志来递归地告诉配置单元读取文件夹

ts,device,sensor,_data_point,value
1541252701443,farm0001,sensor01,temperature,14.78
1541252701443,farm0001,sensor01,pressure,961.7
1541252701443,farm0001,sensor01,humidity,68.32

首先非常感谢@hlagos的帮助。
aws athena无法按我需要的方式转换json传感器数据（我们在@hlagos answer的注解中讨论了这一点）。因此，处理这种情况的“最简单”方法是将数据格式从json更改为csv，以便更接近我需要的格式。
我现在将传感器数据以csv格式存储在s3中（每5分钟写入一次数据），并添加了我们讨论的日期和设备分区。
生成的文件夹结构：

farm_iot/sensor_data/farm/day=20181129/device=farm0001/1543535738493

csv文件的数据内容：

sensor01,temperature,2.82
sensor01,pressure,952.83
sensor01,humidity,83.64
sensor02,temperature,2.61
sensor02,pressure,952.74
sensor02,humidity,82.41

aws雅典娜表格定义：

CREATE EXTERNAL TABLE IF NOT EXISTS farm.sensor_data (
  `sensor` string,
  `data_point` string,
  `value` double 
) 
PARTITIONED BY (day string, device string)
ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ','
    ESCAPED BY '\\'
    LINES TERMINATED BY '\n'
LOCATION 's3://farm-iot/sensor_data/farm/'
TBLPROPERTIES ('has_encrypted_data'='false');

我添加的分区是这样的（稍后我将有一个脚本来提前创建分区）：

msck repair table farm.sensor_data

现在我可以查询数据了：

select regexp_extract("$path", '[^/]+$') as timestamp, device, sensor, 
    data_point, value from farm.sensor_data where day='20181104'

Results
    timestamp       device      sensor      data_point  value
1   1541310040278   farm0001    sensor01    temperature 21.61
2   1541310040278   farm0001    sensor01    pressure    643.65
3   1541310040278   farm0001    sensor01    humidity    74.84
4   1541310040278   farm0001    sensor02    temperature 9.14
5   1541310040278   farm0001    sensor02    pressure    956.04
6   1541310040278   farm0001    sensor02    humidity    88.01
7   1541311840309   farm0001    sensor01    temperature 21.61
8   ...