sqoop-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Venkat Ranganathan <vranganat...@hortonworks.com>
Subject Re: Hortonworks Connector for Teradata + Sqoop Query
Date Wed, 12 Nov 2014 15:51:28 GMT
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
<Karthik.Dhandapani@cvscaremark.com> wrote:
> Hi Mattie,
>
>
>
> Try using –table <TABLE> and –where <FILTER_CONDITION> option in the
Sqoop
> import statement.
>
>
>
> Thanks,
>
> Karthik
>
>
>
> From: Whitmore, Mattie [USA] [mailto:Whitmore_Mattie@bah.com]
> Sent: Tuesday, November 11, 2014 9:21 PM
> To: user@sqoop.apache.org
> 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:&nbsp; 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.

Mime
View raw message