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

Database Operations View all Databases

hive> show databases; using the database default

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) to display 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 switch the current database

Hive (default) > Use Liguodong;
Ok
Time taken:0.031 seconds
Hive (Liguodong) > Delete 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 Expressions hive> 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 available in Hive 0.14.0 and 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:available in Hive 0.10.0 and 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:available in Hive 0.6.0 and later) [location Hdfs_path] [Tblproperties (Property_name=property_value,..   .)]   --(Note:available in Hive 0.6.0 and later) [as select_statement]; --(Note:available in Hive 0.5.0 and later; not supported for external tableS 

Hive (default) > CREATE TABLE test (id int);
Ok
Time taken:10.143 seconds get table-building statement

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 (
              > name string Comment ' name value ',
              > addr string com ment ' addr value '
              >);
OK Time
taken:10.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 table if not EXISTS employees (
              > name string,
              > Salary string,
              > S Ubordinates array<string>,
              > Deductions map<string,float>,
              > Address struct<street: String,city:string,state:string,zip:int>
              >)
              > Row format delimited fields terminated by ' \ t '
              > Collection items terminated by ', '
              > Maps keys terminated by ': '
              > Lines terminated by ' \ n '
              > St ORed as Textfile
              > Location '/liguodong/data/'
              >;

Find table Data

Hive> SELECT * FROM Employees;
OK
Tony    1338    ["A1", "A2", "A3"]        {"K1": 1.0, "K2": 2.0, "K3": 3.0}    {"Street": "S1", "City": "S2", " State ":" S3 "," Zip ": 4}
Mark    5453    [" A4 "," A5 "," A6 "]        {" K4 ": 4.0," K5 ": 5.0," K6 ": 6.0}    {" Street ":" S4 "," City ":" S5 "," state ":" S6 "," Zip ": 6}
Ivy     323     [" A7 "," A8 "," A9 "]        {" K7 ": 7.0," K8 ": 8.0," K9 ": 9.0 }    {"Street": "S7", "City": "S8", "state": "S9", "Zip": 9} Time
taken:10.204 seconds, Fetched:3 row (s)

search tree Group
hive> Select Subordinates[1] from  employees;
Total MapReduce CPU time spent:2 seconds 740 msec
OK
A2
a5
A8
Check map
hive> Select deductions["K2"] from  employees;

OK
2.0
null
null time
taken:75.812 seconds, Fetched:3 row (s)

check struct
hive> Select Address.city from  employees;
Total MapReduce CPU time spent:2 seconds msec
OK
S2
S5 s8 time
taken:75.311 seconds, Fetche D:3 Row (s)

NOTE: SELECT * does not execute mapreduce, only one local query.
A SELECT field generates a job that executes the mapreduce.

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;
OK time
taken:10.283 seconds
Hive (default) > drop table testexttable;
OK Time
taken:0.258 seconds

other ways to build a table for hive There is one table that creates another table

(The table structure is copied, and the content is not copied.) ）
CREATE table test3 like test2;
No need to execute MapReduce

hive> desc testtable;
OK
name                    string                  Name value
addr                    string                  addr value time
taken:0.267 seconds, Fetched:2 Row (s)
hive> create table testtablenew like testtable;
OK time
taken:10.323 seconds
hive> desc testtablenew;
OK
name                    string                  Name value
addr                    string                  addr value time
taken:0.135 seconds, Fetched:2 Row (s)

querying from other tables, creating tables

(Copy the table structure and copy the contents.)
CREATE TABLE Test2 as select name,addr from Test1;
Need to execute MapReduce

hive> create table testnewtable as select name,addr from testtable; hive> desc TESTNEWTABL
E OK name string addr string time taken:0.122 seconds, Fetched:2 row (s) hive> sel
ECT * from testnewtable; OK liguodong CD Aobama lsj time taken:10.226 seconds, Fetched:4 row (s)