我正在运行一个python脚本,但是在输出位置以.txt文件的形式获得了执行ID。有没有一种方法可以让我对它进行注释,并停止将这些ID作为输出。以下是python脚本:
#!/usr/bin/env python3
import boto3
import datetime
# Function for executing athena queries
def run_ddl_query(ddl_query, database, s3_ddl_output):
client = boto3.client('athena')
response = client.start_query_execution(
QueryString=query,
QueryExecutionContext={
'Database': 's3_accesslog'
},
ResultConfiguration={
'OutputLocation': s3_ddl_output,
}
)
print('Execution ID: ' + response['QueryExecutionId'])
return response
def run_query(query, database, s3_output):
client = boto3.client('athena')
# Setup and perform query
response = client.start_query_execution(
QueryString=query,
QueryExecutionContext={
'Database': database
},
ResultConfiguration={
'OutputLocation': s3_output,
}
)
# Athena configuration
s3_ouput = 's3://xxxxx/athenatest/'
database = 's3_accesslog'
table = 'test_output'
# import datetime
year = datetime.date.today()
year = year.strftime('%Y')
month = datetime.date.today()
month = month.strftime('%m')
day = datetime.date.today()
day = day.strftime('%d')
# select bucket
s3_input = 's3://xxxxx/cf-ant-prod/cf-mon-prod/year=%s/month=%s/day=%s' % (year, month, day)
#Athena database and table definition
create_database = "CREATE DATABASE IF NOT EXISTS %s;" % (database)
create_table = \
"""CREATE EXTERNAL TABLE IF NOT EXISTS %s.%s (
`Date` DATE,
Time STRING,
Location STRING,
SCBytes BIGINT,
RequestIP STRING,
Method STRING,
Host STRING,
Uri STRING,
Status INT,
Referrer STRING,
UserAgent STRING,
UriQS STRING,
Cookie STRING,
ResultType STRING,
RequestId STRING,
HostHeader STRING,
Protocol STRING,
CSBytes BIGINT,
TimeTaken FLOAT,
XForwardFor STRING,
SSLProtocol STRING,
SSLCipher STRING,
ResponseResultType STRING,
CSProtocolVersion STRING,
FleStatus STRING,
FleEncryptedFields INT,
CPort INT,
TimeToFirstByte FLOAT,
XEdgeDetailedResult STRING,
ScContent STRING,
ScContentLen BIGINT,
ScRangeStart BIGINT,
ScRangeEnd BIGINT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '%s'
TBLPROPERTIES ('skip.header.line.count' = '2');""" % ( database, table, s3_input )
#Query definitions
query_1 = "SELECT * FROM %s.%s where CAST(status AS VARCHAR) like '404';" % (database, table)
#Execute all queries
queries = [ create_database, create_table, query_1, ]
for q in queries:
print("Executing query: %s" % (q))
res = run_query(q, database, s3_ouput)我尝试注释打印(‘Execution ID:’+response‘’QueryExecutionId‘),当我再次尝试执行脚本时,它没有显示,但我在输出路径中看到了.txt文件。
$ ./Athena_Script_new.py
Executing query: CREATE DATABASE IF NOT EXISTS xxxxxx;
Execution ID: 21c2e158-b774-473c-812a-7ff121a688a2
Executing query: drop table xxxxxx.xxxxxxx;
Execution ID: cd39087a-f18f-4d93-aa71-26c5402cdc7d
Executing query: CREATE EXTERNAL TABLE IF NOT EXISTS xxxxxx.xxxxx (
`Date` DATE,
Time STRING,
Location STRING,
SCBytes BIGINT,
RequestIP STRING,
Method STRING,
Host STRING,
Uri STRING,
Status INT,
Referrer STRING,
UserAgent STRING,
UriQS STRING,
Cookie STRING,
ResultType STRING,
RequestId STRING,
HostHeader STRING,
Protocol STRING,
CSBytes BIGINT,
TimeTaken FLOAT,
XForwardFor STRING,
SSLProtocol STRING,
SSLCipher STRING,
ResponseResultType STRING,
CSProtocolVersion STRING,
FleStatus STRING,
FleEncryptedFields INT,
CPort INT,
TimeToFirstByte FLOAT,
XEdgeDetailedResult STRING,
ScContent STRING,
ScContentLen BIGINT,
ScRangeStart BIGINT,
ScRangeEnd BIGINT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
LOCATION 's3://xxxxxx/xxxxxxx/year=2020/month=09/day=03'
TBLPROPERTIES ('skip.header.line.count' = '2');
Execution ID: d3ed413b-43ed-445e-92bd-e5ef794d2948
Executing query: SELECT * FROM xxxxxx.xxxxxxx where CAST(status AS VARCHAR) like '404';
Execution ID: be2378bd-c10e-4714-9611-978200cc8866

发布于 2020-09-03 17:07:20
负责放入结果的代码是
ResultConfiguration={
'OutputLocation': s3_output,
}响应包含的文件似乎是由亚马逊网络服务硬编码的,无法更改,请参阅AWS Athena, Working with Query Results, Output Files, and Query History。
一旦这个.txt文件出现,你可以手动添加删除它,但我想知道为什么你需要删除这个.txt并保留结果的其他部分。我认为常见的用法是保留或使用并删除整个答案(输出位置中的所有对象)。
发布于 2020-09-03 18:31:11
.txt文件由您在脚本中执行的DDL语句创建。我看到创建这些文本文件的多条DDL语句。对于每个DDL,在配置的OutputLocation中都会生成一个.txt文件。Refer to Identifying Query Output Files了解更多有关这方面的信息。
您无法阻止它们的生成,但您可以简单地让它们在其他位置生成,或者通过在OutputLocation中查找.txt文件来清除它们。
我相信更改要生成的这些文件的位置是一个简单的选择。您需要做的就是再创建一个函数,如下所示。因此,当您想要运行DDL查询时,请改用此函数。
def run_ddl_query(ddl_query, database, s3_ddl_output):
client = boto3.client('athena')
response = client.start_query_execution(
QueryString=query,
QueryExecutionContext={
'Database': 's3_accesslog'
},
ResultConfiguration={
'OutputLocation': s3_ddl_output,
}
)
print('Execution ID: ' + response['QueryExecutionId'])
return response因此,这将在同一存储桶中的另一个位置/文件夹中创建.txt文件,您只需使用类似S3.Client.delete_object的命令即可将其删除
https://stackoverflow.com/questions/63720051
复制相似问题