Connecting to AWS Athena through JDBC with PyCharm - fetchSize issue

One thing to consider with regards to fetch size, JDBC, and AWS athena. There appears to be an semi-documented but well known limit of 1000 rows per fetch. I know that the popular PyAthenaJDBC library sets it as their default fetch size. So, that could be part of your issue.

I can produce fetch size errors when I try to fetch more than 1000 rows at a time.

from pyathenajdbc import connect 
conn = connect(s3_staging_dir='s3://SOMEBUCKET/', 
region_name='us-east-1')
cur = conn.cursor()
cur.execute('SELECT * FROM SOMEDATABASE.big_table LIMIT 5000')
results = cur.fetchall()
print len(results)
# Note: The cursor class actually has a setter method to 
#       keep users from setting illegal fetch sizes   
cur._arraysize = 1001 # Set array size one greater than the default
cur.execute('SELECT * FROM athena_test.big_table LIMIT 5000')
results = cur.fetchall() # Generate an error

java.sql.SQLExceptionPyRaisable: java.sql.SQLException: The requested fetchSize is more than the allowed value in Athena. Please reduce the fetchSize and try again. Refer to the Athena documentation for valid fetchSize values.

Potential solutions include:

1. Running the query in the web GUI and then manually downloading the result set
2. Develop the query in the editor/IDE of your choice (DataGrip, the Athena Web GUI, ect) and pass the query string to the Athena through the Python SDK. You can then wait for the query to finish and grab the result set.
3. You execute the query and paginate through the results.
4. If you are calling your SQL from Python (I am inferring that from the PyCharm tag), you can use a library like PyAthenaJDBC which will handle page sizing for you (see the example above).

For many of my Python scripts, I use a workflow similar to the following.

import boto3
import time

sql = 'SELECT * from athena_test.big_table'

database = 'SOMEDATABASE'
bucket_name = 'SOMEBUCKET' 
output_path = '/home/zerodf/temp/somedata.csv'

client = boto3.client('athena')
config = {'OutputLocation': 's3://' + bucket_name + '/',
      'EncryptionConfiguration': {'EncryptionOption': 'SSE_S3'}}

execution_results = client.start_query_execution(QueryString = sql,
                                             QueryExecutionContext =
                                             {'Database': database},
                                             ResultConfiguration = config)

execution_id = str(execution_results[u'QueryExecutionId'])
remote_file = execution_id + '.csv'

while True:
    query_execution_results = client.get_query_execution(QueryExecutionId =
                                                     execution_id)
    if query_execution_results['QueryExecution']['Status']['State'] == u'SUCCEEDED':
        break
    else:
        time.sleep(60)

s3 = boto3.resource('s3')
s3.Bucket(bucket_name).download_file(remote_file, output_path)

Obviously, production code is more complicated.

I think you should set the appropriate value in this setting of DataGrip