Hive commands

If you want to list down all tables contains same string in same database then use below command.

hive > show tables like '*tablename*';

Similarly for Database

hive > show databases like '*dbname*';

Remove duplicate (redundancy)

Case: We have multiple columns in table but none of the column or combination of columns  in table provide unique row.

Solution: In that case you can use function called  ROW_NUMBER  and also used ROW_NUMBER with OVER (PARTITION BY )


Step 1:  First you have to generate unique number in subquery with ROW_NUMBER() OVER() as unique_number.

Step 2 : Now you have to generate ROW_NUMBER OVER() ( PARTITION BY unique_number , table_column1,table_column2)

Now from step 2 you will start getting unique row.

Example:

select pos4.*,
              if(pos4.Partner_Tier_Indicator_Code='T1','sell-thru','sell-to') as partner_selling_motion_measure_name,
              if(pos4.Partner_Tier_Indicator_Code='T1','sold to','sold to R2R') as address_type_name
        from
                  (SELECT pos3.*,
                             ROW_NUMBER() OVER (PARTITION BY pos3.rowid,pos3.partner_sales_transaction_date order by pos3.partner_sales_transaction_date  desc ) as rownum
                           FROM
                                 (SELECT
                                       pos2.*,
                                       if((ph.reporting_source_partner_level_3_identifier <> NULL or ph.reporting_source_partner_level_3_identifier <> ''),
                                       ph.reporting_source_partner_level_3_identifier,pos2.partner_sold_to_matched_siebel_row_identifier) as HQ_partner2
                                  FROM
                                      (SELECT  pos1.*,ROW_NUMBER()  over() as rowid
                                            FROM
                                                (SELECT
                                                  pos.*,
                                                  if((ph.reporting_source_partner_level_3_identifier <> NULL or ph.reporting_source_partner_level_3_identifier <> ''),
                                                  ph.reporting_source_partner_level_3_identifier,pos.reporting_partner_siebel_row_identifier) as HQ_partner
                                                FROM xx.pos_weekly_temp pos --xx.Fact_Channel_Point_Of_Sale_Weekly
                                                      LEFT OUTER JOIN  xx.dim_partner_hierarchy  ph
                                                      ON pos.reporting_partner_siebel_row_identifier=ph.reporting_partner_identifier
                                                      WHERE pos.Partner_Tier_Indicator_Code='T1'
                                                      AND   pos.region_code='AMER'
                                                )  pos1
                                  ) pos2
                                        LEFT OUTER JOIN  xx.dim_partner_hierarchy  ph
                                             ON pos2.partner_sold_to_matched_siebel_row_identifier=ph.reporting_partner_identifier
                                             AND pos2.region_code = ph.region_code
                     )  pos3
                            LEFT OUTER JOIN  xx.PAS_REPORTING_PARTNER_REFERENCE_temp rpr
                            ON pos3.HQ_partner2 = rpr.reporting_partner_siebel_row_identifier
                            AND pos3.channel_sub_segment_code = rpr.channel_sub_segment_code
                          ) pos4
                             where pos4.rownum = 1;

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;