def test_execute_sql_statements(self, mock_execute_sql_statement, mock_get_query):
sql = """
-- comment
SET @value = 42;
SELECT @value AS foo;
-- comment
"""
mock_session = mock.MagicMock()
mock_query = mock.MagicMock()
mock_query.database.allow_run_async = False
mock_cursor = mock.MagicMock()
mock_query.database.get_sqla_engine.return_value.raw_connection.return_value.cursor.return_value = (
mock_cursor
)
mock_query.database.db_engine_spec.run_multiple_statements_as_one = False
mock_get_query.return_value = mock_query
execute_sql_statements(
query_id=1,
rendered_query=sql,
return_results=True,
store_results=False,
user_name="admin",
session=mock_session,
start_time=None,
expand_data=False,
log_params=None,
)
mock_execute_sql_statement.assert_has_calls(
[
mock.call(
"SET @value = 42",
mock_query,
"admin",
mock_session,
mock_cursor,
None,
False,
),
mock.call(
"SELECT @value AS foo",
mock_query,
"admin",
mock_session,
mock_cursor,
None,
False,
),
]
)
def test_execute_sql_statements_no_results_backend(
self, mock_execute_sql_statement, mock_get_query
):
sql = """
-- comment
SET @value = 42;
SELECT @value AS foo;
-- comment
"""
mock_session = mock.MagicMock()
mock_query = mock.MagicMock()
mock_query.database.allow_run_async = True
mock_cursor = mock.MagicMock()
mock_query.database.get_sqla_engine.return_value.raw_connection.return_value.cursor.return_value = (
mock_cursor
)
mock_query.database.db_engine_spec.run_multiple_statements_as_one = False
mock_get_query.return_value = mock_query
with pytest.raises(SupersetErrorException) as excinfo:
execute_sql_statements(
query_id=1,
rendered_query=sql,
return_results=True,
store_results=False,
user_name="admin",
session=mock_session,
start_time=None,
expand_data=False,
log_params=None,
)
assert excinfo.value.error == SupersetError(
message="Results backend is not configured.",
error_type=SupersetErrorType.RESULTS_BACKEND_NOT_CONFIGURED_ERROR,
level=ErrorLevel.ERROR,
extra={
"issue_codes": [
{
"code": 1021,
"message": (
"Issue 1021 - Results backend needed for asynchronous "
"queries is not configured."
),
}
]
},
)
def test_non_async_execute(non_async_example_db: Database,
example_query: Query):
"""Test query.tracking_url is attached for Presto and Hive queries"""
result = execute_sql_statements(
example_query.id,
"select 1 as foo;",
store_results=False,
return_results=True,
session=db.session,
start_time=now_as_float(),
expand_data=True,
log_params=dict(),
)
assert result
assert result["query_id"] == example_query.id
assert result["status"] == QueryStatus.SUCCESS
assert result["data"] == [{"foo": 1}]
# should attach apply tracking URL for Presto & Hive
if non_async_example_db.db_engine_spec.engine == "presto":
assert example_query.tracking_url
assert "/ui/query.html?" in example_query.tracking_url
app.config[
"TRACKING_URL_TRANSFORMER"] = lambda url, query: url.replace(
"/ui/query.html?", f"/{query.client_id}/")
assert f"/{example_query.client_id}/" in example_query.tracking_url
app.config["TRACKING_URL_TRANSFORMER"] = lambda url: url + "&foo=bar"
assert example_query.tracking_url.endswith("&foo=bar")
if non_async_example_db.db_engine_spec.engine_name == "hive":
assert example_query.tracking_url_raw
def test_execute_sql_statements_ctas(self, mock_execute_sql_statement,
mock_get_query):
sql = """
-- comment
SET @value = 42;
SELECT @value AS foo;
-- comment
"""
mock_session = mock.MagicMock()
mock_query = mock.MagicMock()
mock_query.database.allow_run_async = False
mock_cursor = mock.MagicMock()
mock_query.database.get_sqla_engine.return_value.raw_connection.return_value.cursor.return_value = (
mock_cursor)
mock_query.database.db_engine_spec.run_multiple_statements_as_one = False
mock_get_query.return_value = mock_query
# set the query to CTAS
mock_query.select_as_cta = True
mock_query.ctas_method = CtasMethod.TABLE
execute_sql_statements(
query_id=1,
rendered_query=sql,
return_results=True,
store_results=False,
session=mock_session,
start_time=None,
expand_data=False,
log_params=None,
)
mock_execute_sql_statement.assert_has_calls([
mock.call(
"SET @value = 42",
mock_query,
mock_session,
mock_cursor,
None,
False,
),
mock.call(
"SELECT @value AS foo",
mock_query,
mock_session,
mock_cursor,
None,
True, # apply_ctas
),
])
# try invalid CTAS
sql = "DROP TABLE my_table"
with pytest.raises(SupersetErrorException) as excinfo:
execute_sql_statements(
query_id=1,
rendered_query=sql,
return_results=True,
store_results=False,
session=mock_session,
start_time=None,
expand_data=False,
log_params=None,
)
assert excinfo.value.error == SupersetError(
message=
"CTAS (create table as select) can only be run with a query where the last statement is a SELECT. Please make sure your query has a SELECT as its last statement. Then, try running your query again.",
error_type=SupersetErrorType.INVALID_CTAS_QUERY_ERROR,
level=ErrorLevel.ERROR,
extra={
"issue_codes": [{
"code":
1023,
"message":
"Issue 1023 - The CTAS (create table as select) doesn't have a SELECT statement at the end. Please make sure your query has a SELECT as its last statement. Then, try running your query again.",
}]
},
)
# try invalid CVAS
mock_query.ctas_method = CtasMethod.VIEW
sql = """
-- comment
SET @value = 42;
SELECT @value AS foo;
-- comment
"""
with pytest.raises(SupersetErrorException) as excinfo:
execute_sql_statements(
query_id=1,
rendered_query=sql,
return_results=True,
store_results=False,
session=mock_session,
start_time=None,
expand_data=False,
log_params=None,
)
assert excinfo.value.error == SupersetError(
message=
"CVAS (create view as select) can only be run with a query with a single SELECT statement. Please make sure your query has only a SELECT statement. Then, try running your query again.",
error_type=SupersetErrorType.INVALID_CVAS_QUERY_ERROR,
level=ErrorLevel.ERROR,
extra={
"issue_codes": [
{
"code":
1024,
"message":
"Issue 1024 - CVAS (create view as select) query has more than one statement.",
},
{
"code":
1025,
"message":
"Issue 1025 - CVAS (create view as select) query is not a SELECT statement.",
},
]
},
)
def test_execute_sql_statements_ctas(
self, mock_execute_sql_statement, mock_get_query
):
sql = """
-- comment
SET @value = 42;
SELECT @value AS foo;
-- comment
"""
mock_session = mock.MagicMock()
mock_query = mock.MagicMock()
mock_query.database.allow_run_async = False
mock_cursor = mock.MagicMock()
mock_query.database.get_sqla_engine.return_value.raw_connection.return_value.cursor.return_value = (
mock_cursor
)
mock_query.database.db_engine_spec.run_multiple_statements_as_one = False
mock_get_query.return_value = mock_query
# set the query to CTAS
mock_query.select_as_cta = True
mock_query.ctas_method = CtasMethod.TABLE
execute_sql_statements(
query_id=1,
rendered_query=sql,
return_results=True,
store_results=False,
user_name="admin",
session=mock_session,
start_time=None,
expand_data=False,
log_params=None,
)
mock_execute_sql_statement.assert_has_calls(
[
mock.call(
"SET @value = 42",
mock_query,
"admin",
mock_session,
mock_cursor,
None,
False,
),
mock.call(
"SELECT @value AS foo",
mock_query,
"admin",
mock_session,
mock_cursor,
None,
True, # apply_ctas
),
]
)
# try invalid CTAS
sql = "DROP TABLE my_table"
with pytest.raises(SqlLabException) as excinfo:
execute_sql_statements(
query_id=1,
rendered_query=sql,
return_results=True,
store_results=False,
user_name="admin",
session=mock_session,
start_time=None,
expand_data=False,
log_params=None,
)
assert str(excinfo.value) == (
"CTAS (create table as select) can only be run with "
"a query where the last statement is a SELECT. Please "
"make sure your query has a SELECT as its last "
"statement. Then, try running your query again."
)
# try invalid CVAS
mock_query.ctas_method = CtasMethod.VIEW
sql = """
-- comment
SET @value = 42;
SELECT @value AS foo;
-- comment
"""
with pytest.raises(SqlLabException) as excinfo:
execute_sql_statements(
query_id=1,
rendered_query=sql,
return_results=True,
store_results=False,
user_name="admin",
session=mock_session,
start_time=None,
expand_data=False,
log_params=None,
)
assert str(excinfo.value) == (
"CVAS (create view as select) can only be run with a "
"query with a single SELECT statement. Please make "
"sure your query has only a SELECT statement. Then, "
"try running your query again."
)
