CREATE VIEW and SELECT WITH [non_default_database].[any_view] Issue Solution - CREATE VIEW and SELECT WITH [non_default_database].[any_view] Issue Solution - Teradata Package for Python

Teradata® Package for Python User Guide

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Teradata Package for Python
Release Number
20.00
Published
March 2024
Language
English (United States)
Last Update
2024-04-09
dita:mapPath
nvi1706202040305.ditamap
dita:ditavalPath
plt1683835213376.ditaval
dita:id
rkb1531260709148
Product Category
Teradata Vantage

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"
  • 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"
  • 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"
  • 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