Hive Database - Sqoop Import - Pig useHCatalog - Error


#1

Hi,

This is with reference to the video “Apache Pig - Group the data for one or more pig relations - 02 Group by key” from the HDPCD no Java Playlist.

I’m trying to import the table orders from the mysql db retail_db to hive using sqoop import

  1. Created a database in hive :
    hive> create database pig_demo;
    OK
    Time taken: 3.174 seconds
    hive> show databases;
    OK
    default
    foodmart
    pig_demo
    sqoop_import
    xademo
    Time taken: 0.156 seconds, Fetched: 5 row(s)

  2. I could see the database created under the warehouse directory :
    hive> dfs -ls /apps/hive/warehouse

    ;
    Found 10 items
    drwxr-xr-x - root hadoop 0 2018-02-01 13:24 /apps/hive/warehouse/departments
    drwxr-xr-x - root hadoop 0 2018-02-05 09:38 /apps/hive/warehouse/departments_export
    drwxrwxrwx - hive hadoop 0 2017-11-10 14:59 /apps/hive/warehouse/foodmart.db
    drwxr-xr-x - root hadoop 0 2018-02-01 16:06 /apps/hive/warehouse/order_join
    drwxrwxrwx - root hadoop 0 2018-03-01 14:10 /apps/hive/warehouse/pig_demo.db
    drwxr-xr-x - root hadoop 0 2018-01-24 04:24 /apps/hive/warehouse/retail_stage.db
    drwxrwxrwx - hive hadoop 0 2017-11-10 15:00 /apps/hive/warehouse/sample_07
    drwxrwxrwx - hive hadoop 0 2017-11-10 15:00 /apps/hive/warehouse/sample_08
    drwxrwxrwx - root hadoop 0 2018-02-12 01:02 /apps/hive/warehouse/sqoop_import.db
    drwxrwxrwx - hive hadoop 0 2017-11-10 14:53 /apps/hive/warehouse/xademo.db

  3. Successfully imported the orders table from mysql to hive using sqoop import :

[root@sandbox-hdp ~]# sqoop import -Dmapreduce.job.user.classpath.first=true --connect “jdbc:mysql://sandbox-hdp.hortonworks.com:3306/retail_db” --username=retail_dba --password=hadoop --table orders --as-textfile --outdir java_files --fields-terminated-by “,” --warehouse-dir=/apps/hive/warehouse/pig_demo.db --driver com.mysql.jdbc.Driver -m 2 ;

  1. I was able to see the orders under the file system directory :
    hive> dfs -ls /apps/hive/warehouse/pig_demo.db

    ;
    Found 1 items
    drwxr-xr-x - root hadoop 0 2018-03-01 14:10 /apps/hive/warehouse/pig_demo.db/orders

  2. But in Hive, if I do show tables, the orders table is not getting listed :
    hive> use pig_demo;
    OK
    Time taken: 3.096 seconds
    hive> show tables;
    OK
    Time taken: 0.54 seconds
    hive>

  3. So, when I try to LOAD the table orders in Pig using the useHCatalog option I’m getting the error that the table is not found :

grunt> orders = LOAD ‘pig_demo.orders’ using org.apache.hive.hcatalog.pig.HCatLoader();
2018-03-01 14:19:11,121 [main] INFO org.apache.hive.hcatalog.common.HiveClientCache - Initializing cache: eviction-timeout=120 initial-capacity=50 maximum-capacity=50
2018-03-01 14:19:11,190 [main] INFO hive.metastore - Trying to connect to metastore with URI thrift://sandbox-hdp.hortonworks.com:9083
2018-03-01 14:19:11,254 [main] INFO hive.metastore - Connected to metastore.
2018-03-01 14:19:11,863 [main] ERROR org.apache.pig.tools.grunt.Grunt - ERROR 1115: Table not found : pig_demo.orders table not found
Details at logfile: /root/pig_1519913920833.log

Could someone please help ? Thanks in advance !!