Alter partition table

If you have partition table and you want to add new column in table then after adding the new column using ALTER command the value appears NULL in that column then that case you should use CASCADE clause at end of ALTER command.

alter table pos_all_flgs_dh3 add columns(dheeren2 string) CASCADE;

INSERT
INTO pos_all_flgs_dh3 partition
  (
    Region_Code,
    Partner_Tier_Indicator_Code,
    Fiscal_Year_Week_Code
  )
SELECT DISTINCT
  p1.Channel_Sub_Segment_Identifier,
  p1.Source_File_Name,
  if((p1.cross_source_sale_flag <> p2.cross_source_sale_flag),'dheerentest','dheerentest') as dheeren2,
  p1.Region_Code,
  p1.Partner_Tier_Indicator_Code,
  p1.Fiscal_Year_Week_Code
FROM
 pos_4_flg_dh p1 LEFT OUTER JOIN pos_2_flg_dh p2
 ON p1.Partner_Sales_Transaction_Identifier= p2.Partner_Sales_Transaction_Identifier limit 1;

ORC format

hive> create external  table test_pos_ext2(
    > trxno string,
    > trx_dt string,
    > reporter_id  string,
    > buyer_id string
    > ) row format delimited fields terminated by ','
    > LOCATION '/gcw/testing/pos_test_ext/test';
OK
Time taken: 0.153 seconds
hive> select * from test_pos_ext2;
OK
Failed with exception java.io.IOException:org.apache.hadoop.hive.ql.io.FileFormatException: Malformed ORC file hdfs://hdpdevnn/gcw/testing/pos_test_ext/test/pos3.txt. Invalid postscript.

Solutiion:--

Add - STORED AS TEXTFILE in table while creating.

hive> create external  table test_pos_ext2(

    > trxno string,

    > trx_dt string,

    > reporter_id  string,

    > buyer_id string

    > ) row format delimited fields terminated by ','

    > STORED AS TEXTFILE

    > LOCATION '/gcw/testing/pos_test_ext/test';

hive> select * from test_pos_ext2;

OK

9106956188      3/7/2017        3-HWJW-516      3-2SS-2763

9106956189      3/7/2017        3-HWJW-516      3-2SS-2763

Time taken: 0.12 seconds, Fetched: 2 row(s)

Copy Partition table

If you want to copy existing Partition table in Hive from one cluster to another cluster or copy from one database to another database on same cluster.

Suppose you have Table t1 in database testdb and you have load data in partition table from local directory.

create table testdb.t1(a string, b string) row format delimited fields terminated by ',';
load data local inpath '/home/dheerendra/working/Data/data1' overwrite into table testdb.t1;

Insert data in Partition table.

set hive.exec.dynamic.partition.mode=nonstrict;
insert into table testdb.tp2 partition(b) select a,b from testdb.t2;

Copy data from one cluster to another cluster using  Distcp command. I my case cluster is same.

hadoop distcp   /user/hive/warehouse/testdb.db/tp /user/hive/warehouse/db1.db/

When Cluster is different then.

hadoop distcp   hdfs://cluster1/user/hive/warehouse/testdb.db/tp  hdfs://cluster2/user/hive/warehouse/db1.db/

Create same table structure on destination database.

create table db1.tp(a string) partitioned by(b string);

Use MSCK command to add partition with metastore

msck repair table tp;

If you get error like:
Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask
 Take below steps:

set hive.msck.path.validation=ignore;
MSCK REPAIR TABLE table_name;

Hive output to csv format

Move hive output to csv format

 nohup hive -f /home/dheerendra.a.yadav/query/s3.q | sed 's/[\t]/,/g' > /home/dheerendra.a.yadav/query/s6.csv &

Transpose column to row

Transpose or Pivot table or convert column to row


create table twit_seq STORED AS ORC tblproperties ("orc.compress"="NONE") as
select t1.consumer_id,t1.text,t1.rk
from
(SELECT consumer_id,text,postcreationtime1,rank() OVER (partition by consumer_id ORDER BY postcreationtime1 desc ) as rk,
 row_number() over() as cnt
FROM dunami_v1)t1  where t1.rk < 6;


create table customer_tweet_info_dnami STORED AS ORC tblproperties ("orc.compress"="NONE") as
SELECT consumer_id ,
   max(CASE WHEN rk = 1 THEN text else NULL END) AS Tweet_1,
   max(CASE WHEN rk = 2 THEN text else NULL END) AS Tweet_2,
   max(CASE WHEN rk = 3 THEN text else NULL END) AS Tweet_3,
   max(CASE WHEN rk = 4 THEN text else NULL END) AS Tweet_4,
   max(CASE WHEN rk = 5 THEN text else NULL END) AS Tweet_5
FROM twit_seq  group by consumer_id;

week and rank function

Calculate weeks

select abs(floor(datediff(timestamp(from_unixtime(UNIX_TIMESTAMP(trim('01/04/16 00:00'),'MM/dd/yy hh:mm'))),postcreationtime1)/7)) as wk
from dunami_twitter_visual_data_v1 limit 100;


Use rank function to get value in descending order

create table twit_seq STORED AS ORC tblproperties ("orc.compress"="NONE") as
select t1.consumer_id,t1.text
from
(SELECT consumer_id,text,postcreationtime1,rank() OVER (partition by consumer_id ORDER BY postcreationtime1 desc ) as rk
FROM dunami_v1)t1  where t1.rk < 6;

Hive Optimization properties

set hive.vectorized.execution.enabled = true;
set hive.vectorized.execution.reduce.enabled = true;
set hive.cbo.enable=true;
set hive.compute.query.using.stats=true;
set hive.stats.fetch.column.stats=true;
set hive.stats.fetch.partition.stats=true;
set mapreduce.job.mappers=1000;
set mapreduce.job.reducers=1000;
 SET hive.exec.parallel=true;
-----------------------------------------------------------

set hive.vectorized.groupby.maxentries=10240;
set hive.vectorized.groupby.flush.percent=0.1;
set hive.cbo.enable=true;
set hive.compute.query.using.stats=true;
set hive.stats.fetch.column.stats=true;
set hive.stats.fetch.partition.stats=true;
set hive.cli.print.header = true;


Working with Tez and getting issue like tez.TezProcessor|: java.lang.OutOfMemoryError: Java heap space

Then set the properties on query level so that container size increase for that particular query.

SET hive.tez.java.opts=-Xmx8192m -Xms4096m

SET hive.tez.container.size=4026

Go through link -
https://discuss.pivotal.io/hc/en-us/articles/226239948-Hive-query-with-TEZ-engine-failed-with-exception-java-lang-OutOfMemoryError-Java-heap-space

Hive query output generate empty files along with other files then use below properties.
set hive.merge.tezfiles=true;
set hive.merge.mapredfiles=true;
set hive.merge.mapfiles=true;
set hive.merge.orcfile.stripe.level=true;

=================================================

Optimization for ORC files

set hive.vectorized.execution.enabled=true;
set hive.vectorized.execution.reduce.enabled = true;
set hive.vectorized.execution.reduce.groupby.enabled = true;
set hive.exec.parallel=true;
SET hive.tez.java.opts=-Xmx8192m -Xms4096m;
SET hive.tez.container.size=8192;
set hive.exec.parallel=true;