Hive basic commands

Create a table: hive> Create Table pokes (FOO int, bar string); creates a table called pokes with two columns, the first being an integer and the other a string

Create a new table with the same structure as other hive> Create Table new_table like records;

Create a partition table: hive> Create Table logs (TS bigint, line string) partitioned by (DT string, country string );

Load partition table data: hive> load data local inpath'/home/hadoop/input/hive/Partitions/file1 'into table logs partition (Dt = '2017-01-01 ', country = 'gb ');

Shows the number of partitions in the Table: hive> show partitions logs;

Show all tables: hive> show tables; lists all the tables hive> show tables '. * s ';

Lists all the table that end with's '. The pattern matching follows Java regular expressions. Check out this link for documentation http://java.sun.com/javase/6/docs/api/java/util/regex/Pattern.html

Displays the table structure information hive> describe invites; shows the list of Columns

Update the table name: hive> alter table source Rename to target;

Add a new column hive> alter table invites add columns (new_col2 int comment 'A comment'); Delete table: hive> drop table records; Delete table data, however, you must keep the table's structure definition hive> DFS-RMR/user/hive/warehouse/records;

Load data from a local file: hive> load data local inpath '/home/hadoop/input/ncdc/micro-Tab/sample.txt' overwrite into table records;

Show all functions: hive> show functions;

View function usage: hive> describe function substr;

View arrays, maps, hive> select col1 [0], col2 ['B'], col3.c from complex;

Internal join: hive> select sales. *, things. * from sales join things on (sales. ID = things. ID );

Check the number of mapreduce jobs hive> explain select sales. *, things. * from sales join things on (sales. ID = things. ID) used by hive for a query );

External Connection: hive> select sales. *, things. * From sales left outer join things on (sales. id = things. ID); hive> select sales. *, things. * From sales right outer join things on (sales. id = things. ID); hive> select sales. *, things. * From sales full outer join things on (sales. id = things. ID );

In query: hive is not supported, but you can use left semi join hive> select * from things left semi join sales on (sales. ID = things. ID );

Map connection: hive can put a small table into the memory of each Mapper to perform the connection operation hive> select/* + mapjoin (things) */sales. *, things. * From sales join things on (sales. id = things. ID );

Insert overwrite table .. select: hive> from records2> insert overwrite table stations_by_year Select Year, count (distinct station) group by year> insert overwrite table records_by_year Select Year, count (1) exists in the new table) group by year> insert overwrite table good_records_by_year Select Year, count (1) where temperature! = 9999 and (Quality = 0 or quality = 1 or quality = 4 or quality = 5 or quality = 9) group by year;

Create Table... as select: hive> Create Table target as select col1, col2 from source;

Create a view: hive> Create view valid_records as select * From records2 where temperature! = 9999;

View Details: hive> describe extended valid_records;