0

I have to create a hive table from data present in oracle tables. I'm doing a sqoop, thereby converting the oracle data into HDFS files. Then I'm creating a hive table on the HDFS files. The sqoop completes successfully and the files also get generated in the HDFS target directory. Then I run the create table script in hive. The tables gets created. But it is an empty table, no data is seen in the hive table.

Has anyone faced a similar problem?

Jonathan
  • 144
  • 4
  • 13
  • Please cross check which HDFS directory you are importing HDFS data. If you are creating hive table then location should be the same. Please provide the imported directory and your `create table` statement – Sandeep Singh Jun 11 '15 at 17:22
  • https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-Loadingfilesintotables – Konstantin V. Salikhov Jun 11 '15 at 18:54
  • Cross Checked the HDFS location and the directory specified in the Create statement. They are exactly the same. Checked the metadata of the created hive table, that too points to the HDFS location. – Jonathan Jun 11 '15 at 19:37
  • Imported Directory: /user/rajendrap/webdata/WPT_Booking/Stage_Web_Prod_Trav_Booking Create Script: CREATE TABLE DEFAULT.STAGE_WEB_PROD_TRAV_BOOKING ( SHIPCODE STRING, PORTCODE STRING, DURATION STRING, SAILDATE STRING, NUM_OF_TRAVELLERS DECIMAL, NUM_OF_BOOKINGS DECIMAL, NUM_OF_BOOKING_WEB DECIMAL ) PARTITIONED BY (RDATE string) LOCATION '/user/rajendrap/webdata/WPT_Booking/Stage_Web_Prod_Trav_Booking'; – Jonathan Jun 11 '15 at 19:41

2 Answers2

1

Hive default delimiter is ctrlA, if you don't specify any delimiter it will take default delimiter. Add below line in your hive script .

row format delimited fields terminated by '\t'

gbharat
  • 276
  • 1
  • 4
0

Your Hive script and your expectation is wrong. You are trying to create a partitioned table on the data that you have already imported, partitions won't work that way. If your query has no partition in it then you can able to see data.

Basically If you want partitioned table , you can't create on the under lying data like you have tried above. If you want hive partition load the data from intermediate table or that sqoop directory to your partitioned table to get Hive partitions.

gbharat
  • 276
  • 1
  • 4
  • Thanks BigData, you were right. I created the hive table without the partition and it worked. Now i face another problem. All the data appear in a single field in the hive table. The hive table is not able to recognize the delimiter in the data present in the HDFS files. In the sqoop I have used the argument [--fields-terminated-by '\t']. Even then this occurs. Can you please shed some light on this? – Jonathan Jun 11 '15 at 21:43