basic hive

1.Give the command to see the indexes on a table.
SHOW INDEX ON table_name

This will list all the indexes created on any of the columns in the table table_name.


2.How do you specify the table creator name when creating a table in Hive?
The TBLPROPERTIES clause is used to add the creator name while creating a table.

The TBLPROPERTIES is added like −

TBLPROPERTIES(‘creator’= ‘Joan’)


3.how you specify the hive transactional properties?
TBLPROPERTIES(‘Transactional’= ‘True’)

4.How will you create hive table?
create table tablename(id int,name string,age int) row format delimited fields terminated by ',';

5.simple scenario for date format conversion

create table mytable (dt_tm string);

insert into mytable values
    ('2/3/2017 23:37')
   ,('2/3/2017 23:37')
   ,('2/3/2017 23:40')
   ,('2/3/2017 23:50')
   ,('2/3/2017 23:51')
   ,('2/3/2017 23:53')
   ,('2/3/2017 23:55')
   ,('2/4/2017 0:08' )
   ,('2/4/2017 0:57' )
;


select  dt_tm
       ,cast(from_unixtime(unix_timestamp(dt_tm,'dd/MM/yyyy HH:mm'),'yyyy-MM-dd 00:00:00') as timestamp)

from    mytable
;


+----------------+---------------------+
| 2/3/2017 23:37 | 2017-03-02 00:00:00 |
| 2/3/2017 23:37 | 2017-03-02 00:00:00 |
| 2/3/2017 23:40 | 2017-03-02 00:00:00 |
| 2/3/2017 23:50 | 2017-03-02 00:00:00 |
| 2/3/2017 23:51 | 2017-03-02 00:00:00 |
| 2/3/2017 23:53 | 2017-03-02 00:00:00 |
| 2/3/2017 23:55 | 2017-03-02 00:00:00 |
| 2/4/2017 0:08  | 2017-04-02 00:00:00 |
| 2/4/2017 0:57  | 2017-04-02 00:00:00 |
| 2/3/2017 23:37 | 2017-03-02 00:00:00 |
| 2/3/2017 23:37 | 2017-03-02 00:00:00 |
| 2/3/2017 23:40 | 2017-03-02 00:00:00 |
| 2/3/2017 23:50 | 2017-03-02 00:00:00 |
| 2/3/2017 23:51 | 2017-03-02 00:00:00 |
| 2/3/2017 23:53 | 2017-03-02 00:00:00 |
| 2/3/2017 23:55 | 2017-03-02 00:00:00 |
| 2/4/2017 0:08  | 2017-04-02 00:00:00 |
| 2/4/2017 0:57  | 2017-04-02 00:00:00 |

+----------------+---------------------+