Create the setup
- Create context using admin "dbc" user.
>>> from teradataml import *
>>> create_context(host="myhostname", username="dbc", password="dbc")
- Create required users and grant the required permissions: two users with PERM space and one user with no PERM space.
>>> get_connection().execute("create user alice_views as PERM = 100000000 , PASSWORD = \"alice\"") <sqlalchemy.engine.cursor.LegacyCursorResult object at 0x0000021EAD692630>
>>> get_connection().execute("create user user_with_perm as PERM = 100000000 , PASSWORD = \"alice\"") <sqlalchemy.engine.cursor.LegacyCursorResult object at 0x0000021EAD692A20>
>>> get_connection().execute("create user user_no_perm as PERM = 0 , PASSWORD = \"alice\"") <sqlalchemy.engine.cursor.LegacyCursorResult object at 0x0000021EAD692D68>
- Grant SELECT permission to users 'user_with_perm' and 'user_no_perm' on table 'titanic' in database 'alice'.
>>> get_connection().execute("grant select on alice.titanic to user_no_perm;") <sqlalchemy.engine.cursor.LegacyCursorResult object at 0x0000021EAD6B37B8>
>>> get_connection().execute("grant select on alice.titanic to user_with_perm;") <sqlalchemy.engine.cursor.LegacyCursorResult object at 0x0000021EAD6B3780>
- Grant CREATE VIEW permission to users 'user_with_perm' and 'user_no_perm' on database 'alice_view'.
>>> get_connection().execute("grant create view on alice_views to user_no_perm;") <sqlalchemy.engine.cursor.LegacyCursorResult object at 0x0000021EAD6B3908>
>>> get_connection().execute("grant create view on alice_views to user_with_perm;") <sqlalchemy.engine.cursor.LegacyCursorResult object at 0x0000021EAD6B38D0>
Example 1: Case explaining user with no PERM space
- Create context using 'user_no_perm' user that does not have any perm space and pass "temp_database_name" as 'alice_view', so that internal objects are created in 'alice_views'.
>>> create_context(host="myhostname", username="user_no_perm", password="mypassword", temp_database_name="alice_views") C:\Users\workspace\pyTeradata\teradataml\context\context.py:404: UserWarning: [Teradata][teradataml](TDML_2002) Overwriting an existing context associated with Teradata Vantage C onnection. Most of the operations on any teradataml DataFrames created before this will not work. warnings.warn(Messages.get_message(MessageCodes.OVERWRITE_CONTEXT))
- Create DataFrame using DataFrame.from_query(), this results in error "An owner referenced by user does not have any WITH GRANT OPTION access to alice.titanic."
>>> df = DataFrame.from_query("select * from alice.titanic") Traceback (most recent call last): File "C:\Users\.conda\envs\teradatamla\lib\site-packages\sqlalchemy\engine\base.py", line 1800, in _execute_context cursor, statement, parameters, context File "C:\Users\.conda\envs\teradatamla\lib\site-packages\sqlalchemy\engine\default.py", line 717, in do_execute cursor.execute(statement, parameters) File "C:\Users\.conda\envs\teradatamla\lib\site-packages\teradatasql\__init__.py", line 686, in execute self.executemany (sOperation, None, ignoreErrors) File "C:\Users\.conda\envs\teradatamla\lib\site-packages\teradatasql\__init__.py", line 933, in executemany raise OperationalError (sErr) teradatasql.OperationalError: [Version 17.10.0.16] [Session 3829] [Teradata Database] [Error 3523] An owner referenced by user does not have any WITH GRANT OPTION access to alice.titanic. at gosqldriver/teradatasql.formatError ErrorUtil.go:88 at gosqldriver/teradatasql.(*teradataConnection).formatDatabaseError ErrorUtil.go:216 at gosqldriver/teradatasql.(*teradataConnection).makeChainedDatabaseError ErrorUtil.go:232 at gosqldriver/teradatasql.(*teradataConnection).processErrorParcel TeradataConnection.go:803 at gosqldriver/teradatasql.(*TeradataRows).processResponseBundle TeradataRows.go:2229 at gosqldriver/teradatasql.(*TeradataRows).executeSQLRequest TeradataRows.go:814 at gosqldriver/teradatasql.newTeradataRows TeradataRows.go:673 at gosqldriver/teradatasql.(*teradataStatement).QueryContext TeradataStatement.go:122 at gosqldriver/teradatasql.(*teradataConnection).QueryContext TeradataConnection.go:1304 at database/sql.ctxDriverQuery ctxutil.go:48 at database/sql.(*DB).queryDC.func1 sql.go:1759 at database/sql.withLock sql.go:3437 at database/sql.(*DB).queryDC sql.go:1754 at database/sql.(*Conn).QueryContext sql.go:2013 at main.goCreateRows goside.go:666 at _cgoexp_7cdf4597d74c_goCreateRows _cgo_gotypes.go:340 at runtime.cgocallbackg1 cgocall.go:314 at runtime.cgocallbackg cgocall.go:233 at runtime.cgocallback asm_amd64.s:971 at runtime.goexit asm_amd64.s:1571
- Using 'configure' option from teradataml.
- Set the 'temp_table_database' to 'alice_views' so that internal tables are created in the same.
>>> configure.temp_table_database = "alice_views"
- Set the 'temp_view_database' to 'user_no_perm' so that internal views are created in the user without perm space.
>>> configure.temp_view_database = "user_no_perm"
- Set the 'temp_table_database' to 'alice_views' so that internal tables are created in the same.
- Create DataFrame using DataFrame.from_query(), this works.
>>> df = DataFrame.from_query("select * from alice.titanic")
>>> df SURVIVED pclass name gender age sibsp parch ticket fare cabin embarked PASSENGER 244 0 3 Maenpaa, Mr. Matti Alexanteri male 22.0 0 0 STON/O 2. 3101275 7.1250 None S 101 0 3 Petranec, Miss. Matilda female 28.0 0 0 349245 7.8958 None S 570 1 3 Jonsson, Mr. Carl male 32.0 0 0 350417 7.8542 None S 835 0 3 Allum, Mr. Owen George male 18.0 0 0 2223 8.3000 None S 692 1 3 Karun, Miss. Manca female 4.0 0 1 349256 13.4167 None C 284 1 3 Dorking, Mr. Edward Arthur male 19.0 0 0 A/5. 10482 8.0500 None S 427 1 2 Clarke, Mrs. Charles V (Ada Maria Winfield) female 28.0 1 0 2003 26.0000 None S 305 0 3 Williams, Mr. Howard Hugh "Harry" male NaN 0 0 A/5 2466 8.0500 None S 530 0 2 Hocking, Mr. Richard George male 23.0 2 1 29104 11.5000 None S 265 0 3 Henry, Miss. Delia female NaN 0 0 382649 7.7500 None Q
- Sample few rows from DataFrame.
>>> df.sample(1) SURVIVED pclass name gender age sibsp parch ticket fare cabin embarked sampleid PASSENGER 203 0 3 Johanson, Mr. Jakob Alfred male 34 0 0 3101264 6.4958 None S 1
Example 2: Case explaining user with PERM space
- Create context using 'user_with_perm' user that have perm space and pass "temp_database_name" as 'alice_view', so that internal objects are created in 'alice_views'.
>>> create_context(host="myhostname", username="user_with_perm", password="mypassword", temp_database_name="alice_views") C:\Users\workspace\pyTeradata\teradataml\context\context.py:404: UserWarning: [Teradata][teradataml](TDML_2002) Overwriting an existing context associated with Teradata Vantage C onnection. Most of the operations on any teradataml DataFrames created before this will not work. warnings.warn(Messages.get_message(MessageCodes.OVERWRITE_CONTEXT))
- Create DataFrame using DataFrame.from_query(), this results in error "The user does not have CREATE VIEW access to database user_no_perm."
>>> df = DataFrame.from_query("select * from alice.titanic sample 1") Traceback (most recent call last): File "C:\Users\workspace\pyTeradata\teradataml\dataframe\dataframe.py", line 180, in __init__ UtilFuncs._create_view(self._table_name, self._query) File "C:\Users\workspace\pyTeradata\teradataml\common\utils.py", line 671, in _create_view UtilFuncs._execute_ddl_statement(crt_view) File "C:\Users\workspace\pyTeradata\teradataml\common\utils.py", line 528, in _execute_ddl_statement cursor.execute(ddl_statement) File "C:\Users\.conda\envs\teradatamla\lib\site-packages\teradatasql\__init__.py", line 686, in execute self.executemany (sOperation, None, ignoreErrors) File "C:\Users\.conda\envs\teradatamla\lib\site-packages\teradatasql\__init__.py", line 933, in executemany raise OperationalError (sErr) teradatasql.OperationalError: [Version 17.10.0.16] [Session 3833] [Teradata Database] [Error 3524] The user does not have CREATE VIEW access to database user_no_perm. at gosqldriver/teradatasql.formatError ErrorUtil.go:88 at gosqldriver/teradatasql.(*teradataConnection).formatDatabaseError ErrorUtil.go:216 at gosqldriver/teradatasql.(*teradataConnection).makeChainedDatabaseError ErrorUtil.go:232 at gosqldriver/teradatasql.(*teradataConnection).processErrorParcel TeradataConnection.go:803 at gosqldriver/teradatasql.(*TeradataRows).processResponseBundle TeradataRows.go:2229 at gosqldriver/teradatasql.(*TeradataRows).executeSQLRequest TeradataRows.go:814 at gosqldriver/teradatasql.newTeradataRows TeradataRows.go:673 at gosqldriver/teradatasql.(*teradataStatement).QueryContext TeradataStatement.go:122 at gosqldriver/teradatasql.(*teradataConnection).QueryContext TeradataConnection.go:1304 at database/sql.ctxDriverQuery ctxutil.go:48 at database/sql.(*DB).queryDC.func1 sql.go:1759 at database/sql.withLock sql.go:3437 at database/sql.(*DB).queryDC sql.go:1754 at database/sql.(*Conn).QueryContext sql.go:2013 at main.goCreateRows goside.go:666 at _cgoexp_7cdf4597d74c_goCreateRows _cgo_gotypes.go:340 at runtime.cgocallbackg1 cgocall.go:314 at runtime.cgocallbackg cgocall.go:233 at runtime.cgocallback asm_amd64.s:971 at runtime.goexit asm_amd64.s:1571
- Using 'configure' option from teradataml.
- Set the 'temp_table_database' to 'alice_views' so that internal tables are created in the same.
>>> configure.temp_table_database = "alice_views"
- Set the 'temp_view_database' to 'user_no_perm' so that internal views are created in the user without perm space.
>>> configure.temp_view_database = "user_no_perm"
- Set the 'temp_table_database' to 'alice_views' so that internal tables are created in the same.
- Create DataFrame using DataFrame.from_query(), this results in error " An owner referenced by user does not have any WITH GRANT OPTION access to alice.titanic."
>>> df = DataFrame.from_query("select * from alice.titanic sample 1") Traceback (most recent call last): File "C:\Users\.conda\envs\teradatamla\lib\site-packages\sqlalchemy\engine\base.py", line 1800, in _execute_context cursor, statement, parameters, context File "C:\Users\.conda\envs\teradatamla\lib\site-packages\sqlalchemy\engine\default.py", line 717, in do_execute cursor.execute(statement, parameters) File "C:\Users\.conda\envs\teradatamla\lib\site-packages\teradatasql\__init__.py", line 686, in execute self.executemany (sOperation, None, ignoreErrors) File "C:\Users\.conda\envs\teradatamla\lib\site-packages\teradatasql\__init__.py", line 933, in executemany raise OperationalError (sErr) teradatasql.OperationalError: [Version 17.10.0.16] [Session 3833] [Teradata Database] [Error 3523] An owner referenced by user does not have any WITH GRANT OPTION access to alice.titanic. at gosqldriver/teradatasql.formatError ErrorUtil.go:88 at gosqldriver/teradatasql.(*teradataConnection).formatDatabaseError ErrorUtil.go:216 at gosqldriver/teradatasql.(*teradataConnection).makeChainedDatabaseError ErrorUtil.go:232 at gosqldriver/teradatasql.(*teradataConnection).processErrorParcel TeradataConnection.go:803 at gosqldriver/teradatasql.(*TeradataRows).processResponseBundle TeradataRows.go:2229 at gosqldriver/teradatasql.(*TeradataRows).executeSQLRequest TeradataRows.go:814 at gosqldriver/teradatasql.newTeradataRows TeradataRows.go:673 at gosqldriver/teradatasql.(*teradataStatement).QueryContext TeradataStatement.go:122 at gosqldriver/teradatasql.(*teradataConnection).QueryContext TeradataConnection.go:1304 at database/sql.ctxDriverQuery ctxutil.go:48 at database/sql.(*DB).queryDC.func1 sql.go:1759 at database/sql.withLock sql.go:3437 at database/sql.(*DB).queryDC sql.go:1754 at database/sql.(*Conn).QueryContext sql.go:2013 at main.goCreateRows goside.go:666 at _cgoexp_7cdf4597d74c_goCreateRows _cgo_gotypes.go:340 at runtime.cgocallbackg1 cgocall.go:314 at runtime.cgocallbackg cgocall.go:233 at runtime.cgocallback asm_amd64.s:971 at runtime.goexit asm_amd64.s:1571
- Using 'configure' option from teradataml.
- Set the 'temp_view_database' to 'user_with_perm' so that internal views are created in the same.
>>> configure.temp_view_database = "user_with_perm"
- Set the 'temp_view_database' to 'user_with_perm' so that internal views are created in the same.
- Create DataFrame using DataFrame.from_query(), this works.
>>> df = DataFrame.from_query("select * from alice.titanic sample 1")
>>> df PASSENGER SURVIVED pclass name gender age sibsp parch ticket fare cabin embarked 0 811 0 3 Alexander, Mr. William male 26 0 0 3474 7.8875 None S