Hive Command line common operations (Database operations, table actions)

Database Operations View all Databases

hive> show databases;

working with default databases

hive> Use default;

View database Information

Hive > describe database default;
Ok
Db_name Comment location owner_name owner_type parameters
Default default Hive database Hdfs://hadoop1:8020/user/hive/warehouse public ROLE
Time taken:0.042 seconds, fetched:1 row (s)

show the currently used database

Hive> set hive.cli.print.current.db=true;
Hive (default) >

hive Display column header

Hive (default) > Set hive.cli.print.header=true;
Hive (default) > Desc addressall_2015_07_09;
Ok
Col_name data_type Comment
_C0 string
ADDR1 bigint
ADDR2 bigint
ADDR3 bigint
Time taken:0.182 seconds, Fetched:4 row (s)
Hive (default) > select * from addressall_2015_07_09;
Ok
ADDRESSALL_2015_07_09._C0 ADDRESSALL_2015_07_09.ADDR1 ADDRESSALL_2015_07_09.ADDR2 ADDRESSALL_2015_07_09.ADDR3
2015_07_09 536 488 493
Time taken:10.641 seconds, fetched:1 row (s)

CREATE DATABASE command

Hive (default) > CREATE Database Liguodong;
Ok
Time taken:10.128 seconds

Toggle the current database

Hive (default) > Use Liguodong;
Ok
Time taken:0.031 seconds
Hive (Liguodong) >

Deleting a database

Delete the database when the database is not allowed to delete, if there is data in the database will be error. If you want to ignore the content, then add the Cascade keyword later, ignore the error, delete the database.

hive> DROP DATABASE DbName CASCADE (optional);
hive> DROP DATABASE IF EXISTS DbName CASCADE;

Table Operations See what the current DB table is

Hive> SHOW TABLES in DbName;
Hive (Liguodong) > SHOW TABLES in Liguodong;
Ok
Tab_name
Time taken:0.165 seconds

You can also use regular expressionshive> SHOW TABLES LIKE ‘h*‘;

Hive (default) > SHOW TABLES like 'all ';
Ok
Tab_name
addressall_2015_07_09
Time taken:0.039 seconds, fetched:1 row (s)

Get table-building Statements

Hive (default) > Show create Table address1_2015_07_09;
Ok
Createtab_stmt
CREATE TABLE address1_2015_07_09 (
addr1bigint)
ROW FORMAT SERDE
' Org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe '
STORED as InputFormat
' Org.apache.hadoop.mapred.TextInputFormat '
OutputFormat
' Org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat '
Location
' Hdfs://nameservice1/user/hive/warehouse/address1_2015_07_09 '
Tblproperties (
' Column_stats_accurate ' = ' true ',
' Numfiles ' = ' 1 ',
' NumRows ' = ' 0 ',
' rawdatasize ' = ' 0 ',
' TotalSize ' = ' 4 ',
' Transient_lastddltime ' = ' 1436408451 ')
Time taken:0.11 seconds, fetched:17 row (s)

Create a table

CREATE [temporary] [EXTERNAL] TABLE [IF not EXISTS] [db_name.] TABLE_NAME--(Note:temporary availableinchHive0.14. 0and later) [(Col_name data_type [COMMENT col_comment],...] [COMMENT table_comment] [partitioned by (Col_name data_type [COMMENT col_comment],...)] [CLUSTERED by (Col_name, Col_name,...) [SORTED by (Col_name [asc| DESC],...] into num_buckets buckets] [skewed by (Col_name, Col_name,...)--(note:availableinchHive0.10. 0and later)] on (Col_value, Col_value,...), (Col_value, Col_value,...),...) [STORED as directories] [[ROW FORMAT Row_format] [STORED as File_format] | STORED by' Storage.handler.class.name '[With Serdeproperties (...)]--(note:availableinchHive0.6. 0and later)] [location Hdfs_path] [Tblproperties (Property_name=property_value,...)]--(note:availableinchHive0.6. 0and later) [as select_statement]; --(note:availableinchHive0.5. 0and later; Not supported forExternal tables)

Hive (default) > CREATE TABLE test (id int);
Ok
Time taken:10.143 seconds

Get table-building Statements

Hive (default) > Show create TABLE test;
Ok
Createtab_stmt
CREATE TABLE test (
idint)
ROW FORMAT SERDE
' Org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe '
STORED as InputFormat
' Org.apache.hadoop.mapred.TextInputFormat '
OutputFormat
' Org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat '
Location
' Hdfs://nameservice1/user/hive/warehouse/test '
Tblproperties (
' Transient_lastddltime ' = ' 1436799093 ')
Time taken:0.135 seconds, Fetched:12 row (s)

Create an internal table

Other information obtained from the table

Create an external table

hive (default)> create external table testexttable(              string‘name value‘,              string‘addr value‘              > );OKTime10.172 seconds

create table if not exists testtable(name string comment ‘name value‘,addr string comment ‘addr value‘)row format delimited fields terminated by ‘\t‘ lines terminated by ‘\n‘ stored as textfile;

Loading Data

Hive (default) > Load data local inpath '/liguodong/hivedata/datatest ' overwrite into table testtable;

Hive (default) > Load data local inpath '/liguodong/hivedata/datatest ' into table testtable;
If overwrite is not used, a copy of the data will be copied without overwriting the original data.

Hive (default)>Create External Tableif  notExists Employees (>Namestring,>Salarystring,>SubordinatesArray<string>,>DeductionsMap<string, float>,>Address struct<Streetstring, City:string, State:string, Zip:int>              >)>Row format delimited fields terminated by ' \ t '              >Collection Items terminated by ', '              > MapKeys terminated by ': '              >Lines terminated by ' \ n '              >Stored as Textfile>Location'/liguodong/data/'              >;

Find table Data

Hive>Select* FROM Employees;oktony1338["A1","A2","A3"]        {"K1":1.0,"K2":2.0,"K3":3.0}    {"Street":"S1","City":"S2","State":"S3","Zip":4}mark5453["A4","A5","A6"]        {"K4":4.0,"K5":5.0,"K6":6.0}    {"Street":"S4","City":"S5","State":"S6","Zip":6}ivy323["A7","A8","A9"]        {"K7":7.0,"K8":8.0,"K9":9.0}    {"Street":"S7","City":"S8","State":"S9","Zip":9} TimeTaken:10.204Seconds, fetched:3Row (s) search Tree Group hive>Selectsubordinates[1] FROM employees; Total MapReduce CPU TimeSpent:2Seconds740Msecoka2a5a8 Cha maphive>Selectdeductions["K2"] from Employees;ok2.0NULLNULL TimeTaken:75.812Seconds, fetched:3Row (s) Check structural body hive>SelectAddress.city from Employees; Total MapReduce CPU TimeSpent:2Seconds $Msecoks2s5s8 TimeTaken:75.311Seconds, fetched:3Row (s)

Note: Do select * not execute MapReduce, only one local query.
Instead select 某个字段 , a job is generated and a mapreduce is executed.

select * from employees;select * from employees limit 10;

Delete a table

The internal table is deleted, along with the HDFs stored data, and the external table is deleted, removing only the metadata information from the external table.

hive (default)> drop table testtable;OKTime10.283 secondshive (default)> drop table testexttable;OKTime0.258 seconds

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Hive Command line common operations (Database operations, table actions)