Home Forums Sqoop Importing data from Teradata to Hive‏

This topic contains 4 replies, has 2 voices, and was last updated by  Mahesh Balakrishnan 8 months ago.

  • Creator
    Topic
  • #51313

    Nirmal Kumar
    Participant

    Hello All,

    I am importing data from Teradata 14.0 using Hortonworks Connector for Teradata(*hdp-connector-for-teradata-1.1.1.2.0.6.1-101-distro*).

    Hadoop distro: *Apache Hadoop version 2.3.0* and also on Hadoop 2.1.0.2.0.5.0-67
    Hive Version: 0.12 and 0.11
    Sqoop version: 1.4.4

    I am able to import the Teradata tables to HDFS but CANNOT import the same into Hive Tables.
    Need help regarding the compatibility of the softwares that I am using.

    Here are the commands that I am using with Hortonworks Connector for(*hdp-connector-for-teradata-1.1.1.2.0.6.1-101-distro*):

    Command #1
    rock@rock-Vostro-3560 ~/hadoop/sqoop-1.4.4.bin__hadoop-1.0.0 $ bin/sqoop import -Dteradata.db.input.job.type=hive -Dteradata.db.input.target.table=checkin -Dteradata.db.input.target.table.schema=”first_name string, last_name string, email string, passport string, checkin_date string, checkin_time string, time_zone string, boarding_pass_id string” –verbose –connect jdbc:teradata://192.168.199.129/Database=airlinesuser –connection-manager org.apache.sqoop.teradata.TeradataConnManager –username airlinesuser –password airlinesuser –table checkin
    Warning: /usr/lib/hbase does not exist! HBase imports will fail.
    Please set $HBASE_HOME to the root of your HBase installation.
    Warning: /usr/lib/hcatalog does not exist! HCatalog jobs will fail.
    Please set $HCAT_HOME to the root of your HCatalog installation.
    14/04/08 22:18:22 DEBUG tool.BaseSqoopTool: Enabled debug logging.
    14/04/08 22:18:22 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
    14/04/08 22:18:23 DEBUG sqoop.ConnFactory: Loaded manager factory: com.cloudera.sqoop.manager.DefaultManagerFactory
    14/04/08 22:18:23 INFO manager.SqlManager: Using default fetchSize of 1000
    14/04/08 22:18:23 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/04/08 22:18:23 INFO teradata.TeradataConnManager: Importing from Teradata Table:checkin
    14/04/08 22:18:23 INFO teradata.TeradataSqoopImportJob: Configuring import options
    14/04/08 22:18:23 INFO teradata.TeradataSqoopImportJob: Setting input file format in TeradataConfiguration to textfile
    14/04/08 22:18:23 INFO teradata.TeradataSqoopImportJob: Table name to import checkin
    14/04/08 22:18:23 INFO teradata.TeradataSqoopImportJob: Setting job type in TeradataConfiguration to hdfs
    14/04/08 22:18:23 INFO teradata.TeradataSqoopImportJob: Setting input file format in TeradataConfiguration to textfile
    14/04/08 22:18:23 INFO teradata.TeradataSqoopImportJob: Setting input separator in TeradataConfiguration to \u002c
    14/04/08 22:18:23 ERROR tool.ImportTool: Imported Failed: Can not create a Path from an empty string

    How do I set these:
    export HADOOP_CLASSPATH=$(hcat -classpath)
    export LIB_JARS=$(echo ${HADOOP_CLASSPATH} | sed -e ‘s/::*/,/g’)
    Can I somehow export these manually?

    Thanks
    -Nirmal

Viewing 4 replies - 1 through 4 (of 4 total)

The topic ‘Importing data from Teradata to Hive‏’ is closed to new replies.

  • Author
    Replies
  • #52151

    Hi Nirmal,

    The option where you have specified datatype char and varchar was introduced in Hive 12 and Hive 13. For Hive 11, there is only String as datatype. It seems the exception is caused because of this “Exception in thread “main” java.lang.NoSuchMethodError: com.cloudera.sqoop.SqoopOptions.getHCatTableName()Ljava/lang/String”

    -Dteradata.db.input.target.table.schema=”R_REGIONKEY int, R_NAME char, R_COMMENT varchar”

    Refer below link and when you search for ‘String Types’, you would see the same that I have commented above.

    https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-ColumnTypes

    You would also need MapReduceYarn on the cluster since Sqoop would use MapReduce/Yarn to perform the import from teradata to hive.

    -Mahesh

    Collapse
    #52105

    Nirmal Kumar
    Participant

    I am using the latest HDP connector available on Hortonworks site: hdp-connector-for-teradata-1.1.1.2.0.6.1-101-distro.tar.gz
    Here are my environment variables:
    nirmal@nirmal-Vostro-3560 ~/hadoop/sqoop-1.4.3.bin__hadoop-1.0.0 $ echo $HADOOP_HOME
    /home/nirmal/hadoop/hadoop-2.1.0.2.0.5.0-67
    nirmal@nirmal-Vostro-3560 ~/hadoop/sqoop-1.4.3.bin__hadoop-1.0.0 $ echo $HIVE_HOME
    /home/nirmal/hadoop/hive-0.11.0
    nirmal@nirmal-Vostro-3560 ~/hadoop/sqoop-1.4.3.bin__hadoop-1.0.0 $ echo $LIBJARS
    /home/nirmal/hadoop/hive-0.11.0/lib/hive-metastore-0.11.0.jar,/home/nirmal/hadoop/hive-0.11.0/lib/hive-exec-0.11.0.jar
    nirmal@nirmal-Vostro-3560 ~/hadoop/sqoop-1.4.3.bin__hadoop-1.0.0 $

    If I use the following versions of the s/w:
    Apache hadoop-2.2.0
    Apache hive-0.11.0
    Apache sqoop-1.4.3
    Hortonworks Connector for Teradata : hdp-connector-for-teradata-1.1.1.2.0.6.1-101-distro.tar.gz
    Teradata 14.0

    Will that work?

    Thanks,
    -Nirmal

    Collapse
    #52104

    Nirmal Kumar
    Participant

    Hi Mahesh,

    I tried using your command but getting the following error:
    sqoop import -libjars $LIBJARS -Dteradata.db.input.target.table=TestImport -Dteradata.db.input.target.table.schema=”R_REGIONKEY int, R_NAME char, R_COMMENT varchar” -Dteradata.db.input.split.by.column=COL1 –connection-manager org.apache.sqoop.teradata.TeradataConnManager –connect jdbc:teradata://192.168.199.130/Database=retail –table REGION –columns “R_REGIONKEY, R_NAME,R_COMMENT” –hive-import –username dbc –password dbc –create-hive-table –hive-table ‘TestImport’
    14/04/22 17:07:54 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
    14/04/22 17:07:54 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output. You can override
    14/04/22 17:07:54 INFO tool.BaseSqoopTool: delimiters with –fields-terminated-by, etc.
    14/04/22 17:07:54 INFO manager.SqlManager: Using default fetchSize of 1000
    14/04/22 17:07:54 INFO tool.CodeGenTool: Beginning code generation
    14/04/22 17:07:56 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM REGION AS t WHERE 1=0
    14/04/22 17:07:56 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /home/nirmal/hadoop/hadoop-2.1.0.2.0.5.0-67
    Note: /tmp/sqoop-nirmal/compile/8c9ee7a0519a6e2ea0955b6396509d60/REGION.java uses or overrides a deprecated API.
    Note: Recompile with -Xlint:deprecation for details.
    14/04/22 17:07:59 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-nirmal/compile/8c9ee7a0519a6e2ea0955b6396509d60/REGION.jar
    14/04/22 17:07:59 INFO teradata.TeradataConnManager: Importing from Teradata Table:REGION
    14/04/22 17:07:59 INFO teradata.TeradataSqoopImportJob: Configuring import options
    14/04/22 17:07:59 INFO teradata.TeradataSqoopImportJob: Setting input file format in TeradataConfiguration to textfile
    14/04/22 17:07:59 INFO teradata.TeradataSqoopImportJob: Table name to import REGION
    Exception in thread “main” java.lang.NoSuchMethodError: com.cloudera.sqoop.SqoopOptions.getHCatTableName()Ljava/lang/String;
    at org.apache.sqoop.teradata.TeradataSqoopImportJob.setTDConfigFromSqoopOptions(TeradataSqoopImportJob.java:131)
    at org.apache.sqoop.teradata.TeradataSqoopImportJob.configureOptions(TeradataSqoopImportJob.java:428)
    at org.apache.sqoop.teradata.TeradataConnManager.importTable(TeradataConnManager.java:74)
    at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:403)
    at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:476)
    at org.apache.sqoop.Sqoop.run(Sqoop.java:145)
    at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)
    at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:181)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:220)
    at org.apache.sqoop.Sqoop.runTool(Sqoop.java:229)
    at org.apache.sqoop.Sqoop.main(Sqoop.java:238)

    I’m using plain vanilla Sqoop-1.4.3 & Hive-0.11.0 downloaded from Apache site with HDP 2.1.0.2.0.5.0-67.
    Can I use Apache 2.x rather than HDP here to test? Will that work?

    -Nirmal

    Collapse
    #51643

    Hi Nirmal,

    I have done importing data from Teradata to Hive using the below command:

    sqoop import -libjars $LIB_JARS -Dteradata.db.input.target.table=TestImport -Dteradata.db.input.target.table.schema=”COL1 int, COL2 int” -Dteradata.db.input.split.by.column=COL1 –connection-manager org.apache.sqoop.teradata.TeradataConnManager –connect jdbc:teradata://<server host>/Database=<DatabaseName> –table Test –columns “COL1, COL2″ –hive-import –username username –password password –create-hive-table –hive-table ‘TestImport’

    Can you please use the above syntax to see if this helps with your import of data from Teradata to Hive

    -Mahesh

    Collapse
Viewing 4 replies - 1 through 4 (of 4 total)