SYSLIB.ExtractRoles

Database
Highlighted

SYSLIB.ExtractRoles

Hi,

when using scoop to go from Hadoop to Teradata, we experience the following error:

 

[je45818@s2s005hn ~]$ sqoop list-tables --connect jdbc:teradata://achameleondev.intapp.eu/database=TTZ_COM_ADMIN,tmode=TERA,charset=UTF8,LOGMECH=LDAP --connection-manager org.apache.sqoop.teradata.TeradataConnManager -username je45405 -P

19/01/15 08:19:55 ERROR manager.CatalogQueryManager: Failed to list tables java.sql.SQLException: [Teradata Database] [TeraJDBC 16.10.00.05] [Error 7504] [SQLState HY000] in UDF/XSP/UDM SYSLIB.ExtractRoles: SQLSTATE U0001: External Role String: incorrect format

 

Any idea what format is expected?

Any documentation available for SYSLIB.ExtractRoles?

 

Many thanks in advance

12 REPLIES 12

Re: SYSLIB.ExtractRoles

BTW: the following works correct:

[je45818@s2s005hn ~]$ sqoop eval -Dhadoop.security.credential.provider.path=jceks://hdfs/user/je45818/teradata.jceks --connect jdbc:teradata://achameleondev.intapp.eu/DATABASE=TTZ_COM_TER,TMODE=TERA,CHARSET=UTF8,LOGMECH=LDAP --connection-manager org.apache.sqoop.teradata.TeradataConnManager --username je45405 -password-alias teradata --query "INSERT INTO TDCH_TEST_CARS (CarID, Brand, Color, Price) VALUES (1, 'Kia', 'Grey', 10000)"
19/01/16 14:13:34 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.6.5.0-292
19/01/16 14:13:36 INFO manager.SqlManager: Using default fetchSize of 1000
19/01/16 14:13:37 INFO tool.EvalSqlTool: 1 row(s) updated.

 

??

Teradata Employee

Re: SYSLIB.ExtractRoles

You have -username in the first post and --username in the second.

Is it a typo ?

Re: SYSLIB.ExtractRoles

Good catch, but indeed a typo, the following also gives the error:

[je45818@s2s005hn ~]$ sqoop list-tables --connect jdbc:teradata://achameleondev.intapp.eu/DATABASE=TTQ_CHAMELEON_TENANTS,TMODE=TERA,CHARSET=UTF8,LOGMECH=LDAP --connection-manager org.apache.sqoop.teradata.TeradataConnManager --username je45405 -P

19/01/16 11:36:21 ERROR sqoop.Sqoop: Got exception running Sqoop:

java.lang.RuntimeException: java.sql.SQLException: [Teradata Database] [TeraJDBC 16.10.00.05] [Error 7504] [SQLState HY000] in UDF/XSP/UDM SYSLIB.ExtractRoles: SQLSTATE U0001: External Role String: incorrect format

 

Teradata Employee

Re: SYSLIB.ExtractRoles

Is it entering the password via the console that makes the difference?

 

Re: SYSLIB.ExtractRoles

No, when we use the password-alias it returns the same result. To give additional information, I enabled the 'verbose' mode which also displays 'debug' information.

 

[je45818@s2s005hn ~]$ sqoop list-tables -Dhadoop.security.credential.provider.path=jceks://hdfs/user/je45818/teradata.jceks --connect jdbc:teradata://achameleondev.intapp.eu/DATABASE=TTQ_CHAMELEON_TENANTS,TMODE=TERA,CHARSET=UTF8,LOGMECH=LDAP --connection-manager org.apache.sqoop.teradata.TeradataConnManager --username je45405 --password-alias teradata --verbose

19/01/17 08:01:30 DEBUG sqoop.ConnFactory: Loaded manager factory: org.apache.sqoop.manager.oracle.OraOopManagerFactory
19/01/17 08:01:30 DEBUG sqoop.ConnFactory: Loaded manager factory: com.cloudera.sqoop.manager.DefaultManagerFactory
19/01/17 08:01:30 INFO manager.SqlManager: Using default fetchSize of 1000
19/01/17 08:01:30 DEBUG manager.SqlManager: No connection paramenters specified. Using regular API for making connection.
19/01/17 08:01:31 DEBUG teradata.TeradataConnManager: listTablesSQL = SELECT TRIM(TRAILING FROM T.TABLENAME) AS TABLENAME FROM DBC.TABLESVX T WHERE T.DATABASENAME = (SELECT DATABASE) and (T.TABLEKIND IN ('O', 'T'))
19/01/17 08:01:33 ERROR manager.CatalogQueryManager: Failed to list tables
java.sql.SQLException: [Teradata Database] [TeraJDBC 16.10.00.05] [Error 7504] [SQLState HY000] in UDF/XSP/UDM SYSLIB.ExtractRoles: SQLSTATE U0001: External Role String: incorrect format

Teradata Employee

Re: SYSLIB.ExtractRoles

There was an issue with the dbc.TablesVX view in TD15.10 when using directory-based authorization (External Roles).

A possible workaround would be to "eval" essentially the same query but with dbc.TablesV instead.

Or contact Teradata support for assistance with either downgrading to an earlier version of the view (that does not invoke ExtractRoles function) or upgrading the database to a software version that includes a fix.

Re: SYSLIB.ExtractRoles

Thank you for the response.

 

When we try to set the connection parameter 'USEXVIEWS'=OFF, it does not seem to do this?

[je45818@s2s005hn ~]$ sqoop list-tables -Dhadoop.security.credential.provider.path=jceks://hdfs/user/je45818/teradata.jceks --connect jdbc:teradata://achameleondev.intapp.eu/DATABASE=TTZ_COM_TER,TMODE=TERA,CHARSET=UTF8,LOGMECH=LDAP,USEXVIEWS=ON --connection-manager org.apache.sqoop.teradata.TeradataConnManager --username je45405 --password-alias teradata –verbose
19/01/18 08:17:53 DEBUG teradata.TeradataConnManager: listTablesSQL = SELECT TRIM(TRAILING FROM T.TABLENAME) AS TABLENAME FROM DBC.TABLESVX T WHERE T.DATABASENAME = (SELECT DATABASE) and (T.TABLEKIND IN ('O', 'T'))

 

[je45818@s2s005hn ~]$ sqoop list-tables -Dhadoop.security.credential.provider.path=jceks://hdfs/user/je45818/teradata.jceks --connect jdbc:teradata://achameleondev.intapp.eu/DATABASE=TTZ_COM_TER,TMODE=TERA,CHARSET=UTF8,LOGMECH=LDAP,USEXVIEWS=OFF --connection-manager org.apache.sqoop.teradata.TeradataConnManager --username je45405 --password-alias teradata –verbose
19/01/18 08:09:56 DEBUG teradata.TeradataConnManager: listTablesSQL = SELECT TRIM(TRAILING FROM T.TABLENAME) AS TABLENAME FROM DBC.TABLESVX T WHERE T.DATABASENAME = (SELECT DATABASE) and (T.TABLEKIND IN ('O', 'T'))

 

This is also the case when using the 'list-databases' command. There it also keeps using the DBC.DATABASESVX whether we specify USEXVIEWS=ON or OFF. But in this case the command executes succesfully. So the problem is only on the DBC.TABLESVX it seems?

Teradata Employee

Re: SYSLIB.ExtractRoles

Correct. USEXVIEWS=OFF (which is the default) only controls which views the JDBC driver uses "under the covers" when the client program invokes one of the standard metadata API methods. But the custom query text for "list" actions is being generated by the sqoop connector and passed to the driver as a SQL statement. So another potential workaround might be to force use of the generic JDBC connector instead of the Teradata-specific connector (using the --driver option).

Re: SYSLIB.ExtractRoles

Thank you for your post Fred.

 

So if I understand correctly, we are not able to use the Hortonworks Connector for Teradata and thus lose the TDCH functionalities due to this problem? I assume the performance of the JDBC driver method will be much less than the Hortonworks Connector for Teradata?