如何在AWS Athena分区表2上查询

Question

问题摘要

我还检查了以下链接来修复我的问题，但这对我没有帮助。

如何查询AWS Athena分区表

有4种类型我的日志表中的分区。

• aws_account_id string
• log_type string
• aws_region string
• date string

我尝试了一个简单的 SELECT 查询分区表。但收到以下错误消息。

错误消息

GENERIC_INTERNAL_ERROR: No value present 
This query ran against the "default" database, unless qualified by the query.

我尝试的 SELECT 查询

SELECT * FROM "logs" LIMIT 10;

并且

SELECT * FROM "default"."logs" LIMIT 10;

由于出现有关No value present的错误消息，我检查了分区结果。

SHOW PARTITIONS logs;

结果格式

aws_account_id=${aws_account_id}/log_type=${log_type}/aws_region=${aws_region}/date=${date}

我非常感谢您的帮助。

更多信息

的CREATE TABLE命令

我使用创建表

CREATE EXTERNAL TABLE `logs`(
  `account_id` string, 
  `interface_id` string, 
  `srcaddr` string, 
  `dstaddr` string, 
  `srcport` int, 
  `dstport` int, 
  `traffic_protocol` bigint, 
  `packets` bigint, 
  `bytes` bigint, 
  `start_time` bigint, 
  `end_time` bigint, 
  `traffic_action` string, 
  `log_status` string, 
  `vpc_id` string, 
  `subnet_id` string, 
  `instance_id` string, 
  `tcp_flags` int, 
  `flow_direction` string)
PARTITIONED BY ( 
  `aws_account_id` string, 
  `log_type` string, 
  `aws_region` string, 
  `date` string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ' ' 
WITH SERDEPROPERTIES ( 
  'EXTERNAL'='true', 
  'skip.header.line.count'='1') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://default/path/'
TBLPROPERTIES (
  'last_modified_by'='hadoop', 
  'last_modified_time'='1646821386', 
  'projection.aws_account_id.type'='injected', 
  'projection.aws_region.type'='injected', 
  'projection.date.format'='yyyy/MM/dd', 
  'projection.date.interval'='1', 
  'projection.date.interval.unit'='DAYS', 
  'projection.date.range'='2019/11/27, NOW-1DAYS', 
  'projection.date.type'='date', 
  'projection.enabled'='true', 
  'projection.log_type.type'='string', 
  'projection.log_type.values'='logs', 
  'storage.location.template'='s3://default/logs/${aws_account_id}/${log_type}/${aws_region}/${date}', 
  'transient_lastDdlTime'='1646821386')