Using teradata fast export within sqoop command

Question

Having an issue with sqooping from a Teradata database when using the Teradata method "--fast-export", example sqoop query is below


-Dhadoop.security.credential.provider.path=jceks:/PATH/TO/password/password.jcecks

-Dteradata.db.job.data.dictionary.usexviews=false

--connect

jdbc:teradata://DATABASE

--password-alias

password.alias

--username

USER

--connection-manager

org.apache.sqoop.teradata.TeradataConnManager

--fields-terminated-by

'\t'

--lines-terminated-by

'\n'

--null-non-string

''

--null-string

''

--num-mappers

8

--split-by

column3

--target-dir

/THE/TARGET/DIR

--query

SELECT column1,column2,column3 WHERE column3 > '2020-01-01 00:00:00' and column3 <= '2020-01-12 10:41:20' AND $CONDITIONS

--

--method

internal.fastexport

The error I am getting is

Caused by: com.teradata.connector.common.exception.ConnectorException: java.sql.SQLException: [Teradata Database] [TeraJDBC ] [Error 3524] [SQLState 42000] The user does not have CREATE VIEW access to database DATABASE.

I suspect fast export will implement a staging table/view to be temporarily created, and the job under the hood will be ingesting from the temp table. Is this a sqoop mechanism and is it possible to turn it off?

Many thanks

Dan

Possibly a dumb question, but -- is your database actually named DATABASE? If not, it looks like this parameter is not being set / passed properly. — ravioli
Sorry I should have mentioned. I removed the more sensitive information (server details etc. from the question), I've found if I remove the fast export method from the sqoop query then it runs fine but is less performant — DPEZ
I think you should use --output-method instead of --method as in the reference link below. Also use the connection string as per the instruction from this page. docs.cloudera.com/documentation/other/connectors/teradata/1-x/… — yammanuruarun

user3058654 user3058654 · Accepted Answer · 2020-01-15T12:52:38

Fast export does not implement any view to extract data. The view is being created by Sqoop based on --query value. Hence, the user running the job must have CV right granted on the DATABASE.

You can check user's rights on the database by running the below query replacing USER_NAME and DATABASE_NAME by their values in your env.

ACCESS_RIGHT = 'CV' , means CREATE VIEW so leave it as it is.

SELECT * 
FROM dbc.allRoleRights WHERE roleName IN 
(SELECT roleName FROM dbc.roleMembers WHERE grantee = 'USER_NAME')
AND DATABASENAME = 'DATABASE_NAME'
AND ACCESS_RIGHT = 'CV'
ORDER BY 1,2,3,5;

You may need CT (Create table) rights in order to create log table for fast export. This is given by Sqoop parameters --error-table and --error-database

Using teradata fast export within sqoop command

1 Answers