Hive query for below data: TABLE1: T_ID R_ID AMT PRIORITY 148431838925 148431838921 0.4 1 148431838925 148431838923 0.2 2 148431838925 148431838924 0.7 1 -------------------------------------------------------------------------------- TABLE2: T_ID R_ID AMT PRIORITY 148431838925 148431838923 9 1 148431838925 148431838928 18 1 I would expect result as sum of 3rd column (AMT) based on below conditions: If table1 column1 and column2 (T_ID and R_ID) values are exist in table 2, we need to take column3 (AMT) value from table1 for SUM If not need to take AMT values from table2. for example 148431838925 148431838923 these id's are exist in table2 so need to take amt value from table1 148431838925 148431838928 for this need to take AMT value from table2. the final result should be based on T_ID T_ID SUM_AMT 148431838925 19.3 Please help me to get a result in hive query. Thanks in advance ... View more

casting problem in parquet file format in hive Earlier i have the datatype for one column as decimal and stored as parquet. Now i changed the datatype to bigint. After changing this, i couldn't able to select the data from the table. it showing error message Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row [Error getting row data with exception java.lang.ClassCastException: org.apache.hadoop.io.LongWritable cannot be cast to org.apache.hadoop.hive.serde2.io.HiveDecimalWritable Please help me on this. Thanks! ... View more

Got Invalid table alias or column reference 'NEW_AGE while using CASE statement in hive. Please find the query below and do the needful, I want to get the result(NEW_AGE) from WHEN condition and again process this results(NEW_AGE) to another CASE statement. SELECT CONSUMER_R , CNTRY_ISO_C , NEW_AGE =(CASE WHEN ( DTBIRTH_Y = '0001-01-01' ) THEN 0 ELSE cast((DATEDIFF(current_date,'0001-01-01')/365) as smallint) END), CASE WHEN NEW_AGE = 0 THEN CAST('99) UNKNOWN' as char(11)) WHEN NEW_AGE BETWEEN 1 AND 18 THEN CAST('01) < 18' as char(11)) WHEN NEW_AGE BETWEEN 18 AND 24 THEN CAST('02) 18 ~ 24' as char(11)) WHEN NEW_AGE BETWEEN 25 AND 29 THEN CAST('03) 25 ~ 29' as char(11)) WHEN NEW_AGE BETWEEN 30 AND 34 THEN CAST('04) 30 ~ 34' as char(11)) WHEN NEW_AGE BETWEEN 35 AND 39 THEN CAST('05) 35 ~ 39' as char(11)) WHEN NEW_AGE BETWEEN 40 AND 44 THEN CAST('06) 40 ~ 44' as char(11)) WHEN NEW_AGE BETWEEN 45 AND 49 THEN CAST('07) 45 ~ 49' as char(11)) WHEN NEW_AGE BETWEEN 50 AND 54 THEN CAST('08) 50 ~ 54' as char(11)) WHEN NEW_AGE BETWEEN 55 AND 59 THEN CAST('09) 55 ~ 59' as char(11)) WHEN NEW_AGE BETWEEN 60 AND 64 THEN CAST('10) 60 ~ 64' as char(11)) WHEN NEW_AGE BETWEEN 65 AND 69 THEN CAST('11) 65 ~ 69' as char(11)) WHEN NEW_AGE BETWEEN 70 AND 74 THEN CAST('12) 70 ~ 74' as char(11)) WHEN NEW_AGE >= 75 THEN cast('13) 75 +' as char(11)) ELSE cast('99) UNKNOWN' as char(11)) END NEW_AGE_RANGE, UPDATE_Y, UPDATE_M FROM datamart_db.M_C_CONSMR_TBL WHERE (coalesce(AGE_R, 0) <> coalesce(NEW_AGE, 0)); ... View more

How to import more than one table from DB2 to hive using sqoop ? Here are my usecase, I have around 100 tables in my DB2 database. i need to import only 5 tables into hive. How could i do that? ... View more

I am trying to import data to hive (HDP 2.4) from teradata 14.10 using TDCH. TDCH ver is 1.3.4 as for 1.4.1 the hadoop jar .... command gives invalid jar file error. here is command i am using. export HADOOP_HOME=/usr/hdp/current/hadoop-client/ export HIVE_HOME=/usr/hdp/current/hive-client/ export HCAT_HOME=/usr/hdp/current/hive-webhcat/ export LIB_JARS=/usr/hdp/current/hive-client/lib/avro-1.7.5.jar,/usr/hdp/current/hive-client/conf,/usr/hdp/current/hive-client/lib/antlr-runtime-3.4.jar,/usr/hdp/current/hive-client/lib/commons-dbcp-1.4.jar,/usr/hdp/current/hive-client/lib/commons-pool-1.5.4.jar,/usr/hdp/current/hive-client/lib/datanucleus-api-jdo-4.2.1.jar,/usr/hdp/current/hive-client/lib/datanucleus-core-4.1.6.jar,/usr/hdp/current/hive-client/lib/datanucleus-rdbms-4.1.7.jar,/usr/hdp/current/hive-client/lib/hive-cli-1.2.1000.2.5.3.0-37.jar,/usr/hdp/current/hive-client/lib/jdo-api-3.0.1.jar,/usr/hdp/current/hive-client/lib/hive-exec-1.2.1000.2.5.3.0-37.jar,/usr/hdp/current/hive-client/lib/hive-jdbc-1.2.1000.2.5.3.0-37.jar,/usr/hdp/current/hive-client/lib/hive-metastore-1.2.1000.2.5.3.0-37.jar,/usr/hdp/current/hive-client/lib/hive-metastore.jar,/usr/hdp/current/hive-client/lib/libfb303-0.9.3.jar,/usr/hdp/current/hive-client/lib/libthrift-0.9.3.jar,/usr/hdp/current/hive-server2-hive2/lib/hive-hcatalog-core-2.1.0.2.5.3.0-37.jar export HADOOP_CLASSPATH=${HADOOP_CLASSPATH}:/usr/hdp/current/hive-client/lib/avro-1.7.5.jar,/usr/hdp/current/hive-client/conf,/usr/hdp/current/hive-client/lib/antlr-runtime-3.4.jar,/usr/hdp/current/hive-client/lib/commons-dbcp-1.4.jar,/usr/hdp/current/hive-client/lib/commons-pool-1.5.4.jar,/usr/hdp/current/hive-client/lib/datanucleus-api-jdo-4.2.1.jar,/usr/hdp/current/hive-client/lib/datanucleus-core-4.1.6.jar,/usr/hdp/current/hive-client/lib/datanucleus-rdbms-4.1.7.jar,/usr/hdp/current/hive-client/lib/hive-cli-1.2.1000.2.5.3.0-37.jar,/usr/hdp/current/hive-client/lib/jdo-api-3.0.1.jar,/usr/hdp/current/hive-client/lib/hive-exec-1.2.1000.2.5.3.0-37.jar,/usr/hdp/current/hive-client/lib/hive-jdbc-1.2.1000.2.5.3.0-37.jar,/usr/hdp/current/hive-client/lib/hive-metastore-1.2.1000.2.5.3.0-37.jar,/usr/hdp/current/hive-client/lib/hive-metastore.jar,/usr/hdp/current/hive-client/lib/libfb303-0.9.3.jar,/usr/hdp/current/hive-client/lib/libthrift-0.9.3.jar,/usr/hdp/current/hive-server2-hive2/lib/hive-hcatalog-core-2.1.0.2.5.3.0-37.jar:/usr/hdp/current/hive-client/lib/:/usr/hdp/current/hadoop-client/lib/ hadoop jar /usr/lib/tdch/1.5/lib/teradata-connector-1.5.0.jar com.teradata.connector.common.tool.ConnectorImportTool -libjars $LIB_JARS -url jdbc:teradata://tera.db1 -username *** -password ** -jobtype hive -sourcetable SWIAA03 -nummappers 14 -targettable tdch.SWIAA03 when i run above command. following error is returned java.io.FileNotFoundException: ./hadoop-mapreduce.jobsummary.log (Permission denied) at java.io.FileOutputStream.open0(Native Method) at java.io.FileOutputStream.open(FileOutputStream.java:270) at java.io.FileOutputStream.<init>(FileOutputStream.java:213) at java.io.FileOutputStream.<init>(FileOutputStream.java:133) at org.apache.log4j.FileAppender.setFile(FileAppender.java:294) at org.apache.log4j.RollingFileAppender.setFile(RollingFileAppender.java:207) at org.apache.log4j.FileAppender.activateOptions(FileAppender.java:165) at org.apache.log4j.config.PropertySetter.activate(PropertySetter.java:307) at org.apache.log4j.config.PropertySetter.setProperties(PropertySetter.java:172) at org.apache.log4j.config.PropertySetter.setProperties(PropertySetter.java:104) at org.apache.log4j.PropertyConfigurator.parseAppender(PropertyConfigurator.java:842) at org.apache.log4j.PropertyConfigurator.parseCategory(PropertyConfigurator.java:768) at org.apache.log4j.PropertyConfigurator.parseCatsAndRenderers(PropertyConfigurator.java:672) at org.apache.log4j.PropertyConfigurator.doConfigure(PropertyConfigurator.java:516) at org.apache.log4j.PropertyConfigurator.doConfigure(PropertyConfigurator.java:580) at org.apache.log4j.helpers.OptionConverter.selectAndConfigure(OptionConverter.java:526) at org.apache.log4j.LogManager.<clinit>(LogManager.java:127) at org.slf4j.impl.Log4jLoggerFactory.getLogger(Log4jLoggerFactory.java:64) at org.slf4j.LoggerFactory.getLogger(LoggerFactory.java:285) at org.slf4j.LoggerFactory.getLogger(LoggerFactory.java:305) at org.apache.hadoop.util.RunJar.<clinit>(RunJar.java:54) 17/01/20 08:46:29 INFO tool.ConnectorImportTool: ConnectorImportTool starts at 1484919989308 17/01/20 08:46:29 INFO common.ConnectorPlugin: load plugins in file:/tmp/hadoop-unjar8991125251245146471/teradata.connector.plugins.xml 17/01/20 08:46:29 INFO tool.ConnectorImportTool: java.lang.NoClassDefFoundError: org/apache/hadoop/hive/metastore/api/NoSuchObjectException at com.teradata.connector.common.tool.ConnectorImportTool.processArgs(ConnectorImportTool.java:683) at com.teradata.connector.common.tool.ConnectorImportTool.run(ConnectorImportTool.java:65) at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:76) at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:90) at com.teradata.connector.common.tool.ConnectorImportTool.main(ConnectorImportTool.java:813) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:497) at org.apache.hadoop.util.RunJar.run(RunJar.java:233) at org.apache.hadoop.util.RunJar.main(RunJar.java:148) Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.hive.metastore.api.NoSuchObjectException at java.net.URLClassLoader.findClass(URLClassLoader.java:381) at java.lang.ClassLoader.loadClass(ClassLoader.java:424) at java.lang.ClassLoader.loadClass(ClassLoader.java:357) ... 11 more am i missing something in commadn line or do i need to install any pluging explicity? Thanks ... View more

How to install TDCH for importing data from teradata source to Hive in HDP 2.4.2? and it wolud be help me to if share any guideline to use TDCH. ... View more

Help me to install Standalone mode HDP on CENTOS. I followed HDP installation userguide,but i faced issues on yum install HMC installation. Do the needful. ... View more