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;