Currently, the connector creates a PPI staging table on a staging DB, creates a view and uses the view to import.
You have the option of using a table and use split by value or has option. There is currently an enhancement request that we have logged with Teradata to address this issue as this has permissions requirements and also perm and spool space requirements. You can potentially bypass the the TDCH usage and using the Generic connector connection manager in sqoop by passing the --driver option in the command line. It would run the quest using the standard JDBC driver and value splits like other DBs Please note that this can be significant in terms of time consumption for large imports. Venkat On Wed, Nov 12, 2014 at 7:23 AM, Dhandapani, Karthik <[email protected]> wrote: > Hi Mattie, > > > > Try using –table <TABLE> and –where <FILTER_CONDITION> option in the Sqoop > import statement. > > > > Thanks, > > Karthik > > > > From: Whitmore, Mattie [USA] [mailto:[email protected]] > Sent: Tuesday, November 11, 2014 9:21 PM > To: [email protected] > Subject: Re: Hortonworks Connector for Teradata + Sqoop Query > > > > Hi All, > > I am using the Hortonworks connector for teradata + sqoop. When I import > with a query statment the connector adds in a create view statment. I don't > have permissions to create a view (and I cannot get a connection with said > permissions). Please see below for error and sqoop statment. > > sqoop import --connection-manager > org.apache.sqoop.teradata.TeradataConnManager $CONNECT --query "select * > from $TABLE where LOAD_DT=$LASTDATE AND \$CONDITIONS;" --username $USER > --password $PASS --target-dir $IMPORT_DIR --split-by $SPLITBY > > 14/11/11 15:55:15 INFO manager.SqlManager: Using default fetchSize of 1000 > 14/11/11 15:55:15 INFO tool.CodeGenTool: The connection manager declares > that it self manages mapping between records & fields and rows & columns. > No class will will be generated. > 14/11/11 15:55:15 INFO teradata.TeradataConnManager: Importing from Teradata > query:select * from TABLE where LOAD_DT=cast('2014-07-11' as date format > 'yyyy-mm-dd') AND $CONDITIONS; > 14/11/11 15:55:15 INFO teradata.TeradataSqoopImportHelper: Setting input > file format in TeradataConfiguration to textfile > 14/11/11 15:55:15 INFO teradata.TeradataSqoopImportHelper: Table name to > import null > 14/11/11 15:55:15 INFO teradata.TeradataSqoopImportHelper: Setting job type > in TeradataConfiguration to hdfs > 14/11/11 15:55:15 INFO teradata.TeradataSqoopImportHelper: Setting input > file format in TeradataConfiguration to textfile > 14/11/11 15:55:15 INFO teradata.TeradataSqoopImportHelper: Setting number of > mappers in TeradataConfiguration to 4 > 14/11/11 15:55:15 INFO teradata.TeradataSqoopImportHelper: Setting input > batch size in TeradataConfiguration to 1000 > 14/11/11 15:55:15 INFO teradata.TeradataSqoopImportHelper: Setting split by > column in TeradataConfiguration to ROW_ID > 14/11/11 15:55:15 INFO teradata.TeradataSqoopImportHelper: Setting input > source query in TeradataConfiguration to select * from TABLE where > LOAD_DT=cast('2014-07-11' as date format 'yyyy-mm-dd') AND (1 = 1) ; > 14/11/11 15:55:15 INFO teradata.TeradataSqoopImportHelper: Setting input > separator in TeradataConfiguration to \u002c > 14/11/11 15:55:15 INFO teradata.TeradataSqoopImportHelper: Import query > select * from TABLE where LOAD_DT=cast('2014-07-11' as date format > 'yyyy-mm-dd') AND (1 = 1) ; > > 14/11/11 15:55:15 INFO processor.TeradataInputProcessor: input preprocessor > com.teradata.connector.teradata.processor.TeradataSplitByPartitionProcessor > starts at: 1415750115905 > > 14/11/11 15:55:23 INFO utils.TeradataUtils: the input database product is > Teradata > > 14/11/11 15:55:23 INFO utils.TeradataUtils: the input database version is > 13.10 > > 14/11/11 15:55:23 INFO utils.TeradataUtils: the jdbc driver version is 14.10 > > 14/11/11 15:55:28 INFO processor.TeradataInputProcessor: the teradata > connector for hadoop version is: 1.3.2 > > 14/11/11 15:55:28 INFO processor.TeradataInputProcessor: input jdbc > properties are jdbc:teradata://<redacted> > > 14/11/11 15:58:17 INFO processor.TeradataInputProcessor: input postprocessor > com.teradata.connector.teradata.processor.TeradataSplitByPartitionProcessor > starts at: 1415750297445 > > 14/11/11 15:58:26 INFO processor.TeradataInputProcessor: input postprocessor > com.teradata.connector.teradata.processor.TeradataSplitByPartitionProcessor > ends at: 1415750297445 > > 14/11/11 15:58:26 INFO processor.TeradataInputProcessor: the total elapsed > time of input postprocessor > com.teradata.connector.teradata.processor.TeradataSplitByPartitionProcessor > is: 9s > > 14/11/11 15:58:26 ERROR teradata.TeradataSqoopImportHelper: Exception > running Teradata import job > > com.teradata.connector.common.exception.ConnectorException: > com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata Database] [TeraJDBC > 14.10.00.26] [Error 3524] [SQLState 42000] The user does not have CREATE > VIEW access to database > > at > com.teradata.jdbc.jdbc_4.util.ErrorFactory.makeDatabaseSQLException(ErrorFactory.java:307) > > at > com.teradata.jdbc.jdbc_4.statemachine.ReceiveInitSubState.action(ReceiveInitSubState.java:109) > > at > com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.subStateMachine(StatementReceiveState.java:314) > > at > com.teradata.jdbc.jdbc_4.statemachine.StatementReceiveState.action(StatementReceiveState.java:202) > > at > com.teradata.jdbc.jdbc_4.statemachine.StatementController.runBody(StatementController.java:123) > > at > com.teradata.jdbc.jdbc_4.statemachine.StatementController.run(StatementController.java:114) > > at > com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:384) > > at > com.teradata.jdbc.jdbc_4.TDStatement.executeStatement(TDStatement.java:326) > > at > com.teradata.jdbc.jdbc_4.TDStatement.doNonPrepExecute(TDStatement.java:289) > > at > com.teradata.jdbc.jdbc_4.TDStatement.execute(TDStatement.java:1106) > > at > com.teradata.connector.teradata.db.TeradataConnection.executeDDL(TeradataConnection.java:437) > > at > com.teradata.connector.teradata.db.TeradataConnection.createView(TeradataConnection.java:485) > > at > com.teradata.connector.teradata.processor.TeradataSplitByPartitionProcessor.setupDatabaseEnvironment(TeradataSplitByPartitionProcessor.java:237) > > at > com.teradata.connector.teradata.processor.TeradataInputProcessor.inputPreProcessor(TeradataInputProcessor.java:35) > > at > com.teradata.connector.common.tool.ConnectorJobRunner.runJob(ConnectorJobRunner.java:80) > > at > com.teradata.connector.common.tool.ConnectorJobRunner.runJob(ConnectorJobRunner.java:48) > > at > org.apache.sqoop.teradata.TeradataSqoopImportHelper.runJob(TeradataSqoopImportHelper.java:370) > > at > org.apache.sqoop.teradata.TeradataConnManager.importQuery(TeradataConnManager.java:529) > > at > org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:415) > > at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:506) > > at org.apache.sqoop.Sqoop.run(Sqoop.java:147) > > at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70) > > at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183) > > at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222) > > at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231) > > at org.apache.sqoop.Sqoop.main(Sqoop.java:240) > > > > at > com.teradata.connector.common.tool.ConnectorJobRunner.runJob(ConnectorJobRunner.java:104) > > at > com.teradata.connector.common.tool.ConnectorJobRunner.runJob(ConnectorJobRunner.java:48) > > at > org.apache.sqoop.teradata.TeradataSqoopImportHelper.runJob(TeradataSqoopImportHelper.java:370) > > at > org.apache.sqoop.teradata.TeradataConnManager.importQuery(TeradataConnManager.java:529) > > at > org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:415) > > at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:506) > > at org.apache.sqoop.Sqoop.run(Sqoop.java:147) > > at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70) > > at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183) > > at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222) > > at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231) > > at org.apache.sqoop.Sqoop.main(Sqoop.java:240) > > 14/11/11 15:58:26 INFO teradata.TeradataSqoopImportHelper: Teradata import > job completed with exit code 1 > > 14/11/11 15:58:26 ERROR tool.ImportTool: Encountered IOException running > import job: java.io.IOException: Exception running Teradata import job > > at > org.apache.sqoop.teradata.TeradataSqoopImportHelper.runJob(TeradataSqoopImportHelper.java:373) > > at > org.apache.sqoop.teradata.TeradataConnManager.importQuery(TeradataConnManager.java:529) > > at > org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:415) > > at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:506) > > at org.apache.sqoop.Sqoop.run(Sqoop.java:147) > > at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70) > > at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:183) > > at org.apache.sqoop.Sqoop.runTool(Sqoop.java:222) > > at org.apache.sqoop.Sqoop.runTool(Sqoop.java:231) > > at org.apache.sqoop.Sqoop.main(Sqoop.java:240) > > Any help on this issue would be appreciated. I do not understand why it > would try to create a table when I am asking it to import data. > > Thank you, > > Mattie -- CONFIDENTIALITY NOTICE NOTICE: This message is intended for the use of the individual or entity to which it is addressed and may contain information that is confidential, privileged and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, you are hereby notified that any printing, copying, dissemination, distribution, disclosure or forwarding of this communication is strictly prohibited. If you have received this communication in error, please contact the sender immediately and delete it from your system. Thank You.
