Member since
12-29-2015
26
Posts
3
Kudos Received
2
Solutions
My Accepted Solutions
Title | Views | Posted |
---|---|---|
222 | 11-21-2017 07:21 AM | |
2476 | 09-07-2016 03:07 PM |
01-16-2019
06:52 AM
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
Labels:
07-24-2018
01:15 PM
got the above error while select the records from hive table.
... View more
07-24-2018
12:47 PM
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
Labels:
07-15-2018
01:56 PM
Thanks @ Sandeep Nemuri @ rguruvannagari works fine
... View more
07-15-2018
07:01 AM
@Sandeep Nemuri Thanks for your reply. the above sort will give correct output if the directory have only one day files. if the directory have 20180704 files. it sorted output like abcd_1_20180703 abcd_1_20180704 abcd_2_20180703 abcd_2_20180704 abcd_3_20180703 abcd_3_20180704 abcd_4_20180703 abcd_4_20180704 abcd_5_20180703 abcd_5_20180704 abcd_6_20180703 abcd_6_20180704 But i expect abcd_1_20180703 abcd_2_20180703 abcd_3_20180703 abcd_4_20180703 abcd_5_20180703 abcd_6_20180703 abcd_1_20180704 abcd_2_20180704 abcd_3_20180704 abcd_4_20180704 abcd_5_20180704 abcd_6_20180704 Any idea?
... View more
07-13-2018
08:06 AM
How to sort the filename in shell. My files looks like abcd_2_20180703 abcd_4_20180703 abcd_5_20180703 abcd_1_20180703 abcd_3_20180703 abcd_6_20180703 And i expect after the sorting abcd_1_20180703 abcd_2_20180703 abcd_3_20180703 abcd_5_20180703 .. Please help me sort the files. TIA
... View more
- Tags:
- UNIX
11-21-2017
07:21 AM
Here is the solution for that WHEN a1.Col1 = '1' THEN "b1.col2" Else CAST ('' as timestamp) END as Col3 . It will work
... View more
11-20-2017
03:02 PM
How to change the timstamp type to string with the condition of my query has CASE condition like. " WHEN a1.Col1 = '1' THEN "b1.col2" Else "" END as Col3" here Col1 is timestamp and Col3 is String, So when i run the query, got this error .. ""Argument type mismatch '""': The expressions after THEN should have the same type: "timestamp" is expected but "string" is found"". I expect to here, if the condition failed i need to store "" (empty) value. Please help on this. Thanks in advance.
... View more
Labels:
07-18-2017
01:02 PM
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
Labels:
05-18-2017
10:23 AM
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
Labels:
01-20-2017
01:52 PM
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
Labels:
11-23-2016
10:52 AM
sorry here is the link https://community.hortonworks.com/articles/53531/importing-data-from-teradata-into-hive.html
... View more
11-23-2016
10:50 AM
Thanks @zkfs for your quick reply. We have already try this solution with sqoop andJDBC connection. But we faced performance issues. One of our table have 4TB data and it's take arround 4 hours. So We are planned to implement TDCH to import the data. From the below link i have get some info about TDCH but i don't know how to install and using it in HDP 2.4.2. Kindly do the needful.
... View more
11-23-2016
10:01 AM
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
11-07-2016
02:10 PM
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
11-03-2016
06:46 AM
Thanks @Greg Keys. This is what i want 🙂
... View more
11-02-2016
12:53 PM
1 Kudo
I want to run my SH script only from Monday to Friday. How to create oozie job for this case?
... View more
Labels:
10-21-2016
01:58 PM
Thanks for your input @Greg Keys !!! Yes the values has whitespace. So i used to trim the values before CAST. It's working as expected. SELECT CAST(regexp_replace(regexp_replace(TRIM(column1),'\\.',''),',','.') as decimal(12,2)) FROM table_name;
... View more
10-21-2016
01:20 PM
Hive CAST functions return NULL values:For example one of the staging area table column have the data like -21.475,00, -26.609,00, -21.932,47, -17.300,00(String), My expected output would be like in landing area is -21475,00, -26609,00, -21932,47, -17300.00(decimal(12,2). Staging area column's datatype : String Landing area table column's datatype: decimal(12,2). During data movement from staging to Landing area. i have used insert query with select statement like SELECT CAST(regexp_replace(regexp_replace(column1,'\\.',''),',','.') as decimal(12,2)) FROM table_name; the above query return null values. Kindly do the needful. Thanks in advance!!
... View more
Labels:
10-03-2016
10:10 AM
Yess.. Thanks Ayub
... View more
09-26-2016
01:45 PM
How to get the row number for particular values from hive table: For example i have column name with VAX_NUM and one of the values for this column is 0006756423. I want know the row number for this value. Do the needful. Thanks
... View more
Labels:
09-07-2016
03:07 PM
1 Kudo
Thanks bpreachuk, 'field.delim' = '|' also not helping me, But somehow we have fixed the issue with below CSV serde properties : WITH SERDEPROPERTIES (
"separatorChar" = ",",
"quoteChar" = "\"",
"escapeChar" = "\\",
"serialization.encoding"='ISO-8859-1')
LOCATION '/path/' TBLPROPERTIES (
'store.charset'='ISO-8859-1',
'retrieve.charset'='ISO-8859-1',
'skip.header.line.count'='1');
... View more
09-07-2016
01:33 PM
Thanks Jk for your immediate response. After using "ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe’ WITH SERDEPROPERTIES(“serialization.encoding”=’UTF-8′);" solved the spanish character issue. But we have one more column with values like -10,476.53 because
of this column, we had column jumping , this values stored in hive -10 in one column and 476.53 in another column. Do the needful.
... View more