1

I am a novice APACHE HIVE user.

I have table A looks like this:

     1                     2         3          4         5
1  #filename-login data    
2  #version:1.4  
3  #id:3
4
5  Counter                 Time      State         Sum      Average
6  1                    2018-10-27   Normal        10         25.4
7  2                    2018-10-28   Sleeping      13         30.1 
8  3                    2018-10-29   Normal        15         87.4

I want to create table B using the table A above. First, I want to get all the information in the table A except the first 4 rows. So the header will be 5th row. 

INSERT INTO tableB
SELECT * FROM tableA 
WHERE rownum>4 and int(Counter)>0

Is this query right? If anyone can give me helpful feedback, I would be very appreciated

danday74
  • 52,471
  • 49
  • 232
  • 283
Audrey
  • 11
  • 1
  • 1
    What are the column names in TableA.Why do you want to store the header in TableB? – nobody Nov 08 '18 at 02:42
  • If it is file header, than recreate table with `tblproperties("skip.header.line.count"="5")` property like in this answer: https://stackoverflow.com/a/51960636/2700344 – leftjoin Nov 08 '18 at 06:07

1 Answers1

0

In This query you just need to cast everything into int in this case you might want to use "unasigned" to get int result so another value that have words infront of it will converted into 0 as I see you always have # infront of your header so this wont be any issue

INSERT INTO tableB
SELECT * FROM tableA 
WHERE cast(username as unsigned) >0
rudy tjhia
  • 20
  • 7
  • Actually I am not the one who made the table A. The thing is I have several tables like table A and header starts from different rows each table. For example, table AA's header starts from 8th row and table AC's header starts from 10th row. However, all the table has Counter column as a first column and that's why I added int(Counter)! Does this make sense? – Audrey Nov 09 '18 at 00:55
  • cast(username as unsigned) sorry I just do some google you can't type cast your column as integer that way use this instead on your where clause "cast(username as unsigned)" – rudy tjhia Nov 12 '18 at 07:07
  • unsigned read as integer on MySQL found that on this thread https://stackoverflow.com/questions/12126991/cast-from-varchar-to-int-mysql – rudy tjhia Nov 12 '18 at 07:14