Compare array list with Dataframe column

# If you have array list column of dataframe and need to check or compare  another element of same dataframe then you can achieve this by using  "expr" function for more details please find below code.

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

from pyspark.sql.functions import expr

df_desc_split=df_trx.withColumn('split_desc',sf.split(sf.col('description'),' '))

df_name_flg=df_desc_split.withColumn("first_name_flag", sf.expr("array_contains(split_desc, FIRSTNAME)"))

.withColumn("middle_name_flag", sf.expr("array_contains(split_desc, MIDDLE)"))

Explanation :

 I have description field containing string like "My name is dheerendra" which I split and keep in array field "split_desc" like ['My','name','is','dheerendra']

Now if I have another column of same dataframe e.g "name" which contain "dheerendra".

|name   |split_desc|

|dheerendra| ['My','name','is','dheerendra']|

If I need to check the existence of 'dheerendra' in  split_desc field then need to use "expr" function along with array_contains functions

df_desc_split.withColumn("first_name_flag", sf.expr("array_contains(split_desc, FIRSTNAME)"))

 Convert Interger or decimal  to Date format

When you have interger or decimal value and you need to change in date format.

(1) Change integer or decimal value to string using CAST function.

(2) Convert into unix_timestamp with format e.g 'YYYYMMDD'

(3) Convert into from_unixtime()

(4) Convert into date_format()  in required format e.g 'yyyy-MM-dd'

Example:

 Date in decimal -  birth_dt = 1956324.0

 

Solution:

SELECT date_format(from_unixtime(unix_timestamp(cast(birth_date as string),'YYYYMMDD')),'yyyy-MM-dd') as birth_dt from dummy

Result:

birth_dt = 1956-01-01

Hive with Parquet file

 File location on hdfs :

/data44/2/part-00000-ddd75d27-e608-4a17-a96d-4d631c71e875-c000.snappy.parquet

Create external table in Hive 

create external table test_paq4(deptid string,name string,id int) stored as parquet location 'hdfs://127.0.0.1:9000/data44/2';

hive> select * from test_paq4;

Note :- If you are using parquet then you can select the sequence of column on random basis e.g id,name,deptid or deptid,name,id 

Only the name of the column should match with Parquet file column. It mean each parquet file contains schema (column names).

Sqoop Commands

 sqoop import --connect "jdbc:mysql://localhost/test" --username "root" --table "t1" --target-dir

input1 --m 1

sqoop import --connect "jdbc:mysql://localhost/db2" --username "root" -

-table "t4" --hive-import -m 1

sqoop import --connect "jdbc:mysql://localhost/db2" --username "root" -

-table "t4" --hive-table bigdb.t12 -m 1

sqoop import --connect "jdbc:mysql://localhost/db2" --username "root" -

-table "t4" --warehouse-dir /user/hive/warehouse/bigdb --m 1

sqoop export --connect "jdbc:mysql://localhost/db2" --username "root" -

-table "t5" --export-dir /user/hive/warehouse/t5

sqoop export --connect "jdbc:mysql://localhost/mydb" --username "root"

--table "total_gender" --export-dir /user/hive/warehouse/gender

sqoop export --connect "jdbc:mysql://localhost/mydb" --username "root"

--password 'root123!' --table "gender" --export-dir

/user/hive/warehouse/bigdb.db/user_gender --input-fields-terminated-by

',' -verbose -m 1

---increamental load sqoop ---

sqoop import --connect "jdbc:mysql://localhost/test" --username "root"

--table "city5" --hive-import -check-column upd_dt --incremental

lastmodified --last-value 2015-05-26 --hive-import --m 1

Update table (Acid)

 Update table in Hive by using ACID properties

Prerequisites :-

(1) Table should be Bucketized.
(2) ORC format
(3) Need to set 2 properties:
     (a) SET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
     (b) SET hive.support.concurrency=true;

Step: 1 => Create table using TBLPROPERTIES('transactional'=true');


create table testacid(id int, name string, rollno int) clustered by (id) into 4 buckets
stored as ORC tblproperties('transactional'='true') ;


Step : 2 =>  Insert records into table.

insert into testacid values(1,'dh1',121),(2,'dh2',122);

Step : 3 =>  update testacid set name='dheeren' where name='dh2';

Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

Error :   Caused by: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

Solution: This issue is related with Metastore which will done by help of database e,g Derby, MySql etc...

First steps 

Verify hive-site.xml with below properties.

 <name>javax.jdo.option.ConnectionURL</name>

    <value>jdbc:derby:;databaseName=metastore_db;create=true</value>

   

<name>javax.jdo.option.ConnectionDriverName</name>

    <value>org.apache.derby.jdbc.EmbeddedDriver</value>

    

  <name>hive.metastore.warehouse.dir</name>

    <value>hdfs://localhost:9000/user/hive/warehouse</value>

 <property>

    <name>hive.metastore.uris</name>

    Keep it BLANK

Now try again to start HIVE.If you are still getting the same problem.

Please follow below steps:

(1) Remove metastore_db directory if exists

     rm -r metastore_db

(2) Now initiate Schema by command schematool from hive bin directory 

       schematool -initSchema -dbType derby

(3)     IF Schematool complete successfully the please start hive.

         Now you can able to use database in hive.

Extracting and comparing data

To Extract the data in minimum part file use order by clause in Query

Exmaple:

INSERT OVERWRITE LOCAL DIRECTORY '/dataingestion/folder_name/data/ship' row format delimited FIELDS TERMINATED BY '~'
 SELECT
    wkly_agg.col1,
    wkly_agg.col2,
    SUM(wkly_agg.col3) as net_amount
 FROM db_name.POS_AGGR_FLG_TEMP_T1_2 wkly_agg
   WHERE  wkly_agg.aggregate_sales_flag ='Y'
   AND (wkly_agg.flg1 <>'Y' OR wkly_agg.flg2 <> 'N' OR wkly_agg.seller_flag <> 'Y')
  AND wkly_agg.trx_date between '2017-11-01' and '2017-11-30' 
 GROUP BY
  wkly_agg.col1,
  wkly_agg.col2
Order by 1

Output --
On edge node
/dataingestion/folder_name/data/ship/000000_0 

Move this file to windows by winscp or ftp
Now convert the file in .csv format.

Now this file have list of values in  column and paste other column which values you need to compare with this.

Now you need to use Excel function to compare the value.
(1) =MATCH(B3,A:A,0) It return row number of matched value.
(2) =INDEX(A:A,MATCH(B4,A:A,0)) Return the matched values.

A:A column  - Contains the those values which was extracted by Hive query.
B3 column - Contain the value you want to match with list.

Once it done and return correct match drag the formula on the column value which you want to match.

Hive cmds

(1) Getting specific value from String if delimited by some special char like "_ or #"

example: - p_2011

select split(partition_col,'_')[1] from tmp;

Now the value is separated in two array. Now you have to take the position of array and get that value.

In above case I need 2011 that's why I have given [1] becoz array index start from [0].

Alter field delimiter in hive

ALTER TABLE temp SET SERDEPROPERTIES ('field.delim' = '|');

example:

create table  temp(a string,b string,c string) row format delimited fields terminated by ',' stored as textfile;

CREATE TABLE `temp`(
  `a` string)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://hdpdevnn/apps/hive/warehouse/gcw.db/temp'

ALTER TABLE temp SET SERDEPROPERTIES ('field.delim' = '|');

CREATE TABLE `temp`(

  `a` string)

ROW FORMAT DELIMITED

  FIELDS TERMINATED BY '|'

STORED AS INPUTFORMAT

  'org.apache.hadoop.mapred.TextInputFormat'

OUTPUTFORMAT

  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

LOCATION

  'hdfs://hdpdevnn/apps/hive/warehouse/gcw.db/temp'

Hive table showing count after deletion of records

If you have delete the records of  hive external table from HDFS and table is not partitioned table and table is showing count(*) from table then use below properties to update metastore.

set hive.compute.query.using.stats=false;
ANALYZE TABLE table_name COMPUTE STATISTICS;


If your table is partition table then use below command to analyze.

 ANALYZE TABLE table_name partition(id='12',name='xyz') COMPUTE STATISTICS;

Sqoop Export table from oracle to Hive

#export path=/xx/xx/
Table_Name=$1
echo $Table_name
user_id=$2
echo $user_id
password=$3
echo $password
#cd $path
for i in `cat /xx/xx/abc_list.txt`
do
echo ${i}
echo "sqoop import   --connect 'jdbc:oracle:thin:@(DESCRIPTION =(LOAD_BALANCE = yes)(SDU = 32768) (ADDRESS =(PROTOCOL = TCP) (HOST = xxxx.net)  (PORT = 1526)   ) (CONNECT_DATA =      (SERVICE_NAME = xx)))' --username '${user_id}' --password '${password}' --query "select 'all_columns'  from ${Table_Name} partition"(${i})" where \$CONDITIONS " --target-dir /sales/channel/reporting/${Table_Name}/data/${i} --fields-terminated-by '\034' -m 1"
sqoop import   --connect 'jdbc:oracle:thin:@(DESCRIPTION =(LOAD_BALANCE = yes)(SDU = 32768) (ADDRESS =(PROTOCOL = TCP) (HOST = xxxx.com)  (PORT = 1526)   ) (CONNECT_DATA =      (SERVICE_NAME = xxx--username ${user_id} --password ${password} --query "select *  from ${Table_Name} partition(${i}) where \$CONDITIONS " --target-dir /sales/channel/reporting/${Table_Name}/data/${i} --fields-terminated-by '\034' -m 1
if [ $? == 0 ]
then
echo "Partion data data has processed successfully: echo ${i} "
else
echo "Partion data data has not processed successfully: echo ${i} "
echo "Please check the issue. "
fi
done

Sqoop with query===


sqoop import  --connect 'jdbc:oracle:thin:@(DESCRIPTION =
    (enable = broken)
    (ADDRESS =
      (PROTOCOL = TCP)
      (HOST = hostname.xx.xxcorp.net)
      (PORT = 1526)
    )
    (ADDRESS =
      (PROTOCOL = TCP)
      (HOST = hostname.xx.xxcorp.net)
      (PORT = 1526)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = HPIPODSPC)
    )
  )' --username 'xx' --password 'xxx#ahu1vn' --query "select * from db_name.table_name partition(SYS_P297)  where \$CONDITIONS" --fields-terminated-by  '\034'   --target-dir /sales/channel/reporting/folder_name/data/partition_col=SYS_P297  -m 1

Sqoop import Error with Oracle

sqoop import   --connect 'jdbc:oracle:thin:@ip_add/dbname' --username 'xxx' --password 'xxxx' --table dbname.table_name  --target-dir /xx/xx/Talend/test/test2    -m 1


 Error:

SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/hdp/2.4.2.0-258/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/hdp/2.4.2.0-258/zookeeper/lib/slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
17/11/02 14:38:25 ERROR manager.SqlManager: Error executing statement: java.sql.SQLException: Invalid Oracle URL specified
java.sql.SQLException: Invalid Oracle URL specified
        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
     

Solution : In case we have to add "thin" with jdbc connection string
Example :- --connect 'jdbc:oracle:thin:@ipadd/dbname'

Again Error:

SLF4J: Found binding in [jar:file:/usr/hdp/2.4.2.0-258/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/hdp/2.4.2.0-258/zookeeper/lib/slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
17/11/02 14:40:55 ERROR manager.SqlManager: Error executing statement: java.sql.SQLException: The Network Adapter could not establish the connection
java.sql.SQLException: The Network Adapter could not establish the connection
        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:480)


Solution:-

 Instead of giving the URLand database name you have to replace whole connection string of oracle with TNSNAME entry of that database of oracle:

Example:

Oracle tnsname entry:

xxx =

  (DESCRIPTION =

    (LOAD_BALANCE = yes)

    (SDU = 32768)

    (ADDRESS =

      (PROTOCOL = TCP)

      (HOST = ip_address)

      (PORT = 1526)

    )

    (CONNECT_DATA =

      (SERVICE_= nnnnnDSI)

    )

  )

Now in sqoop cmd you have to make the change accordingly.

sqoop import   --connect 'jdbc:oracle:thin:@(DESCRIPTION =(LOAD_BALANCE = yes)(SDU = 32768) (ADDRESS =(PROTOCOL = TCP) (HOST = ip_addr)  (PORT = 1526)   ) (CONNECT_DATA =      (SERVICE_NAME = HPIPODSI)))' --username 'xxx' --password 'xxxx' --table dbname.table  --target-dir /apps/hive/warehouse/test001.db --escaped-by '\"' --enclosed-by '\"'  --fields-terminated-by '|'  -m 1

Again you are getting error.

LF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]

17/11/02 14:45:33 INFO manager.OracleManager: Time zone has been set to GMT

17/11/02 14:45:33 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM "AMSPODS_PUB"."end_cust_id" t WHERE 1=0

17/11/02 14:45:33 ERROR manager.SqlManager: Error executing statement: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)

        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)

Solution:

Use uppercase for Oracle Username,Database name and Table name 

Example :

sqoop import   --connect 'jdbc:oracle:thin:@(DESCRIPTION =(LOAD_BALANCE = yes)(SDU = 32768) (ADDRESS =(PROTOCOL = TCP) (HOST = ip_address)  (PORT = 1526)   ) (CONNECT_DATA =      (SERVICE_NAME = xx)))' --username 'xx' --password '#' --table dbname.END_CUST_ID  --target-dir /apps/hive/warehouse/test001.db --escaped-by '\"' --enclosed-by '\"'  --fields-terminated-by '|'  -m 1

Now data has successfully imported.

Sqoop importing data

Link
http://hortonworks.com/hadoop-tutorial/loading-data-into-the-hortonworks-sandbox/
http://thinkbiganalytics.com/hadoop_nosql_services/freestone-framework/
CREATE USER 'user1'@'localhost' IDENTIFIED BY PASSWORD 'pass1';

sqoop import --connect "jdbc:mysql://localhost/test" --username "root" --table "t1" --target-dir
input1 --m 1
sqoop import --connect "jdbc:mysql://localhost/db2" --username "root" -
-table "t4" --hive-import -m 1

sqoop import --connect "jdbc:mysql://localhost/db2" --username "root" -
-table "t4" --hive-table bigdb.t12 -m 1
sqoop import --connect "jdbc:mysql://localhost/db2" --username "root" -
-table "t4" --warehouse-dir /user/hive/warehouse/bigdb --m 1

-----Import Using query—
bin/sqoop import --connect
jdbc:teradata://dnedwt.edc.cingular.net/edwdb --driver
com.teradata.jdbc.TeraDriver --username dy662t --password Dhiru+12 -
-query "select UserName , AccountName, UserOrProfile from
DBC.AccountInfoV where \$CONDITIONS" --hive-import --hive-table
tada_ipub.TMP_billing --split-by UserName --target-dir
/user/hive/warehouse/test_tmp1 -verbose
sqoop export --connect "jdbc:mysql://localhost/db2" --username "root" -
-table "t5" --export-dir /user/hive/warehouse/t5
sqoop export --connect "jdbc:mysql://localhost/mydb" --username "root"
--table "total_gender" --export-dir /user/hive/warehouse/gender
sqoop export --connect "jdbc:mysql://localhost/mydb" --username "root"
--password 'root123!' --table "gender" --export-dir
/user/hive/warehouse/bigdb.db/user_gender --input-fields-terminated-by
',' -verbose -m 1


---increamental load sqoop ---
sqoop import --connect "jdbc:mysql://localhost/test" --username "root"
--table "city5" --hive-import -check-column upd_dt --incremental
lastmodified --last-value 2015-05-26 --hive-import


Sqoop the data directly in externall table
-----------------

(1)    Create external table in hive.(Attached  sample DDL)

Example - dist_daily_temp2

(2)    Run Sqoop command and keep the path of “--target-dir” same as location of external table.

sqoop import   --connect 'jdbc:oracle:thin:@(DESCRIPTION =(LOAD_BALANCE = yes)(SDU = 32768) (ADDRESS =(PROTOCOL = TCP) (HOST = ipadddress)  (PORT = 1526)   ) (CONNECT_DATA =      (SERVICE_NAME = xxxI)))' --username 'AMSPODS_PUB' --password 'xxx' --table PUB_FCT_DSTRB_SLS_DAILY  --fields-terminated-by  '\034'  --target-dir /sales/channel/Talend/test/test2  -m 1

(3)    All the columns in sample table in string format to get the data properly from oracle later on we will change data type.

Update Hive table using CASE clause

I have taken 2 table  "temp_add_1" and "temp_add_2".

Table temp_add_1  have 11 records and temp_add_2 have 13 records ( new records and updated columns)

Records of  table temp_add_1  with old records


5000000196      KAPLAN UNIVERSITY       332 FRONT ST S  STE 501 LA CROSSE       WI      54601
5000000206      GLATFELTER      475 S PAINT ST  STOREROOM RECEIVING     CHILLICOTHE     OH      45601
5000000209      METHODIST HEALTH SYSTEM 1441 N BECKLEY AVE              DALLAS  TX      75203
5000000220      WINDWOOD FARM   4857 WINDWOOD FARM RD           AWENDAW SC      294295951
5000239439      D & H DISTRIBUTING      185 COWETA INDUSTRIAL PAR               NEWNAN  GA      30265   US

Records of table temp_add_2 with new rows.

5000000209      METHODIST HEALTH SYSTEM 1441 N BECKLEY AVE              DALLAS  TX      75203

5000000220      WINDWOOD FARM   4857 WINDWOOD FARM RD           AWENDAW SC      294295951

5000000281      MARCO ST CLOUD OFFICE   4510 HEATHERWOOD RD             ST CLOUD        MN      56301

5000000294      RJ REYNOLDS TOBACCO     1001 REYNOLDS BLVD      BLDG 603-5 DOCK 21      WINSTON SALEM   NC      27105

5000239439      D & H DISTRIBUTING      185 COWETA INDUSTRIAL PARKWAY           NEWNAN  GA      30265   US

Use below command to update the records in temp_add_1. 

I am using CASE clause with FULL OUTER JOIN

insert overwrite table sales_channel.temp_add_1

select 

case 

    when t1.address_identifier = t2.address_identifier then t1.address_identifier else t2.address_identifier end as address_identifier,

case

    when t1.customer_name = t2.customer_name then t1.customer_name else t2.customer_name end as customer_name,

case

    when t1.address1_description = t2.address1_description then t1.address1_description else t2.address1_description end as address1_description,

case

    when t1.address2_description = t2.address2_description then t1.address2_description else t2.address1_description end as address2_description,

case

    when t1.city_name = t2.city_name then t1.city_name else t2.city_name end as city_name,

case

    when t1.state_name = t2.state_name then t1.state_name else t2.state_name end as state_name,

case

    when t1.zip_code = t2.zip_code then t1.zip_code else t2.zip_code end as zip_code,

case

    when t1.iso_country_code = t2.iso_country_code then t1.iso_country_code else t2.iso_country_code end as iso_country_code

from sales_channel.temp_add_1 t1 FULL OUTER JOIN sales_channel.temp_add_2 t2

ON t1.address_identifier = t2.address_identifier;

Result:-

OK

5000000209      METHODIST HEALTH SYSTEM 1441 N BECKLEY AVE              DALLAS  TX      75203

5000000220      WINDWOOD FARM   4857 WINDWOOD FARM RD           AWENDAW SC      294295951

5000000281      MARCO ST CLOUD OFFICE   4510 HEATHERWOOD RD     4510 HEATHERWOOD RD     ST CLOUD        MN      56301

5000000294      RJ REYNOLDS TOBACCO     1001 REYNOLDS BLVD      1001 REYNOLDS BLVD      WINSTON SALEM   NC      27105

5000239439      D & H DISTRIBUTING      185 COWETA INDUSTRIAL PARKWAY           NEWNAN  GA      30265   US

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 &