bug: [Athena] error when trying to force create a database, that already exists

[anjakefala]

What happened?

I already had a database named mydatabase in my aws athena instance.

I experimented with using force=True, expecting it to drop the existing table, and create a new one. I got an error instead.

My database does contain a table.

What version of ibis are you using?

main branch commit 3d10def

What backend(s) are you using, if any?

Athena

Relevant log output

In [4]: con.create_database('mydatabase', force=True)
Failed to execute query.
Traceback (most recent call last):
  File "/home/anja/anaconda3/envs/ibis-dev/lib/python3.11/site-packages/pyathena/common.py", line 586, in _execute
    query_id = retry_api_call(
               ^^^^^^^^^^^^^^^
  File "/home/anja/anaconda3/envs/ibis-dev/lib/python3.11/site-packages/pyathena/util.py", line 84, in retry_api_call
    return retry(func, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/anja/anaconda3/envs/ibis-dev/lib/python3.11/site-packages/tenacity/__init__.py", line 475, in __call__
    do = self.iter(retry_state=retry_state)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/anja/anaconda3/envs/ibis-dev/lib/python3.11/site-packages/tenacity/__init__.py", line 376, in iter
    result = action(retry_state)
             ^^^^^^^^^^^^^^^^^^^
  File "/home/anja/anaconda3/envs/ibis-dev/lib/python3.11/site-packages/tenacity/__init__.py", line 398, in <lambda>
    self._add_action_func(lambda rs: rs.outcome.result())
                                     ^^^^^^^^^^^^^^^^^^^
  File "/home/anja/anaconda3/envs/ibis-dev/lib/python3.11/concurrent/futures/_base.py", line 449, in result
    return self.__get_result()
           ^^^^^^^^^^^^^^^^^^^
  File "/home/anja/anaconda3/envs/ibis-dev/lib/python3.11/concurrent/futures/_base.py", line 401, in __get_result
    raise self._exception
  File "/home/anja/anaconda3/envs/ibis-dev/lib/python3.11/site-packages/tenacity/__init__.py", line 478, in __call__
    result = fn(*args, **kwargs)
             ^^^^^^^^^^^^^^^^^^^
  File "/home/anja/anaconda3/envs/ibis-dev/lib/python3.11/site-packages/botocore/client.py", line 569, in _api_call
    return self._make_api_call(operation_name, kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/anja/anaconda3/envs/ibis-dev/lib/python3.11/site-packages/botocore/client.py", line 1023, 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:19: mismatched input 'SCHEMA'. Expecting: 'MATERIALIZED', 'MULTI', 'PROTECTED', 'VIEW'
---------------------------------------------------------------------------
InvalidRequestException                   Traceback (most recent call last)
File ~/anaconda3/envs/ibis-dev/lib/python3.11/site-packages/pyathena/common.py:586, in BaseCursor._execute(self, operation, parameters, work_group, s3_staging_dir, cache_size, cache_expiration_time, result_reuse_enable, result_reuse_minutes, paramstyle)
    585 try:
--> 586     query_id = retry_api_call(
    587         self._connection.client.start_query_execution,
    588         config=self._retry_config,
    589         logger=_logger,
    590         **request,
    591     ).get("QueryExecutionId")
    592 except Exception as e:

File ~/anaconda3/envs/ibis-dev/lib/python3.11/site-packages/pyathena/util.py:84, in retry_api_call(func, config, logger, *args, **kwargs)
     69 retry = tenacity.Retrying(
     70     retry=retry_if_exception(
     71         lambda e: getattr(e, "response", {}).get("Error", {}).get("Code") in config.exceptions
   (...)
     82     reraise=True,
     83 )
---> 84 return retry(func, *args, **kwargs)

File ~/anaconda3/envs/ibis-dev/lib/python3.11/site-packages/tenacity/__init__.py:475, in Retrying.__call__(self, fn, *args, **kwargs)
    474 while True:
--> 475     do = self.iter(retry_state=retry_state)
    476     if isinstance(do, DoAttempt):

File ~/anaconda3/envs/ibis-dev/lib/python3.11/site-packages/tenacity/__init__.py:376, in BaseRetrying.iter(self, retry_state)
    375 for action in self.iter_state.actions:
--> 376     result = action(retry_state)
    377 return result

File ~/anaconda3/envs/ibis-dev/lib/python3.11/site-packages/tenacity/__init__.py:398, in BaseRetrying._post_retry_check_actions.<locals>.<lambda>(rs)
    397 if not (self.iter_state.is_explicit_retry or self.iter_state.retry_run_result):
--> 398     self._add_action_func(lambda rs: rs.outcome.result())
    399     return

File ~/anaconda3/envs/ibis-dev/lib/python3.11/concurrent/futures/_base.py:449, in Future.result(self, timeout)
    448 elif self._state == FINISHED:
--> 449     return self.__get_result()
    451 self._condition.wait(timeout)

File ~/anaconda3/envs/ibis-dev/lib/python3.11/concurrent/futures/_base.py:401, in Future.__get_result(self)
    400 try:
--> 401     raise self._exception
    402 finally:
    403     # Break a reference cycle with the exception in self._exception

File ~/anaconda3/envs/ibis-dev/lib/python3.11/site-packages/tenacity/__init__.py:478, in Retrying.__call__(self, fn, *args, **kwargs)
    477 try:
--> 478     result = fn(*args, **kwargs)
    479 except BaseException:  # noqa: B902

File ~/anaconda3/envs/ibis-dev/lib/python3.11/site-packages/botocore/client.py:569, in ClientCreator._create_api_method.<locals>._api_call(self, *args, **kwargs)
    568 # The "self" in this scope is referring to the BaseClient.
--> 569 return self._make_api_call(operation_name, kwargs)

File ~/anaconda3/envs/ibis-dev/lib/python3.11/site-packages/botocore/client.py:1023, in BaseClient._make_api_call(self, operation_name, api_params)
   1022     error_class = self.exceptions.from_code(error_code)
-> 1023     raise error_class(parsed_response, operation_name)
   1024 else:

InvalidRequestException: An error occurred (InvalidRequestException) when calling the StartQueryExecution operation: line 1:19: mismatched input 'SCHEMA'. Expecting: 'MATERIALIZED', 'MULTI', 'PROTECTED', 'VIEW'

The above exception was the direct cause of the following exception:

DatabaseError                             Traceback (most recent call last)
Cell In[4], line 1
----> 1 con.create_database('mydatabase', force=True)

File ~/git/ibis/ibis/backends/athena/__init__.py:453, in Backend.create_database(self, name, catalog, force)
    451 name = sg.table(name, catalog=catalog, quoted=self.compiler.quoted)
    452 sql = sge.Create(this=name, kind="SCHEMA", replace=force)
--> 453 with self._safe_raw_sql(sql, unload=False):
    454     pass

File ~/anaconda3/envs/ibis-dev/lib/python3.11/contextlib.py:137, in _GeneratorContextManager.__enter__(self)
    135 del self.args, self.kwds, self.func
    136 try:
--> 137     return next(self.gen)
    138 except StopIteration:
    139     raise RuntimeError("generator didn't yield") from None

File ~/git/ibis/ibis/backends/athena/__init__.py:291, in Backend._safe_raw_sql(self, query, unload, *args, **kwargs)
    289     query = query.sql(self.dialect)
    290 with self.con.cursor(unload=unload) as cur:
--> 291     yield cur.execute(query, *args, **kwargs)

File ~/anaconda3/envs/ibis-dev/lib/python3.11/site-packages/pyathena/arrow/cursor.py:124, in ArrowCursor.execute(self, operation, parameters, work_group, s3_staging_dir, cache_size, cache_expiration_time, result_reuse_enable, result_reuse_minutes, paramstyle, **kwargs)
    122 else:
    123     unload_location = None
--> 124 self.query_id = self._execute(
    125     operation,
    126     parameters=parameters,
    127     work_group=work_group,
    128     s3_staging_dir=s3_staging_dir,
    129     cache_size=cache_size,
    130     cache_expiration_time=cache_expiration_time,
    131     result_reuse_enable=result_reuse_enable,
    132     result_reuse_minutes=result_reuse_minutes,
    133     paramstyle=paramstyle,
    134 )
    135 query_execution = cast(AthenaQueryExecution, self._poll(self.query_id))
    136 if query_execution.state == AthenaQueryExecution.STATE_SUCCEEDED:

File ~/anaconda3/envs/ibis-dev/lib/python3.11/site-packages/pyathena/common.py:594, in BaseCursor._execute(self, operation, parameters, work_group, s3_staging_dir, cache_size, cache_expiration_time, result_reuse_enable, result_reuse_minutes, paramstyle)
    592     except Exception as e:
    593         _logger.exception("Failed to execute query.")
--> 594         raise DatabaseError(*e.args) from e
    595 return query_id

DatabaseError: An error occurred (InvalidRequestException) when calling the StartQueryExecution operation: line 1:19: mismatched input 'SCHEMA'. Expecting: 'MATERIALIZED', 'MULTI', 'PROTECTED', 'VIEW'

Code of Conduct

• I agree to follow this project's Code of Conduct

[cpcloud]

Oh, this is a syntax error 😅 .

Fix inbound!