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
(
addr1
bigint)
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
(
id
int)
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)