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." )