I am trying to read CSV file from S3 bucket and create table in Athena through Python. But I am getting below on executing it -
Start of DB Query
{'QueryExecutionId': '9cc82243-4220-47d0-8b63-0aa4f01fd590', 'ResponseMetadata': {'RequestId': '1c74bec6-663a-42ef-b9d1-73c7372eb4e1', 'HTTPStatusCode': 200, 'HTTPHeaders': {'content-type': 'application/x-amz-json-1.1', 'date': 'Thu, 08 Nov 2018 15:37:11 GMT', 'x-amzn-requestid': '1c74bec6-663a-42ef-b9d1-73c7372eb4e1', 'content-length': '59', 'connection': 'keep-alive'}, 'RetryAttempts': 0}}
Start of table creation
Traceback (most recent call last):
File "C:/Users/Doc/PycharmProjects/aws-athena-repo/athena/app.py", line 61, in QueryExecutionContext={'Database': 'athenadb'})
File "C:\Program Files\Python37\lib\site-packages\botocore\client.py", line 320, in _api_call return self._make_api_call(operation_name, kwargs)
File "C:\Program Files\Python37\lib\site-packages\botocore\client.py", line 623, in _make_api_call raise error_class(parsed_response, operation_name)
botocore.errorfactory.InvalidRequestException: An error occurred (InvalidRequestException) when calling the StartQueryExecution operation: line 1:8: no viable alternative at input 'CREATE EXTERNAL'
Here is my code sample --
print("Start of DB Query")
# Create a new database
db_query = 'CREATE DATABASE IF NOT EXISTS athenadb;'
response = client.start_query_execution(
QueryString=db_query,
ResultConfiguration={'OutputLocation': 's3://mybucket'})
print(response)
table_query = '''
CREATE EXTERNAL TABLE IF NOT EXISTS `athenadb.testtable`(
`id` int,
`ident` string,
`type` string,
`name` string,
`latitude_deg` double,
`longitude_deg` double,
`continent` string,
`iso_country` string,
`iso_region` string,
`municipality` string,
`scheduled_service` string,
`gps_code` string,
`iata_code` string,
`local_code` string,
`home_link` string,
`wikipedia_link` string,
`keywords` string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
WITH SERDEPROPERTIES (
'escape.delim'='\\')
STORED AS TEXTFILE
LOCATION 's3://mybucket/folder/' ;'''
print("Start of table creation")
response1 = client.start_query_execution(
QueryString=table_query,
ResultConfiguration={'OutputLocation': 's3://mybucket'},
QueryExecutionContext={'Database': 'athenadb'})
print(response1)
I am not sure if the problem is with ROW FORMAT DELIMITED or else. I think my code is fine.
Detailed steps would be appreciated!
Thanks in adavace!
