正念的觀察，讓你又重回事物本身，看清自己的每一個(gè)動(dòng)作、每一個(gè)瞬間，你將明白自己有權(quán)利快樂(lè)而從容地完成每一件事情！

《和繁重的工作一起修行》

一、數(shù)據(jù)類(lèi)型

二、數(shù)據(jù)庫(kù)操作

1.建庫(kù)語(yǔ)法

create (database/schema) [if not exist] database_name  [comment database_comment]  [location hdfs_path]  [with dbproperties (pro_name=pro_val,...)];

2.示例

普通創(chuàng)建hive> create database mydb1;OKTime taken: 2.73 seconds
指定hdfs地址hive> create database mydb2 location '/opt';OKTime taken: 5.211 second
帶有注釋hive> create database mydb3 comment "這是數(shù)據(jù)庫(kù)3";OKTime taken: 2.07 second
帶有屬性hive> create database mydb4    > with dbproperties('pro1'='val1','pro2'='val2');OKTime taken: 2.086 second
如果不存在就創(chuàng)建hive> create schema if not exists mydb2;OKTime taken: 0.017 seconds

3.查看

hive> show databases;OKdefaultmydbmydb1mydb2mydb3mydb4Time taken: 0.019 seconds, Fetched: 6 row(s)
hive> desc database mydb4;OKmydb4           hdfs://mycluster/user/hive/warehouse/mydb4.db   root    USERTime taken: 0.028 seconds, Fetched: 1 row(s)
hive> desc database mydb3;OKmydb3   這是數(shù)據(jù)庫(kù)3     hdfs://mycluster/user/hive/warehouse/mydb3.db   root    USERTime taken: 0.018 seconds, Fetched: 1 row(s)

4.刪庫(kù)語(yǔ)法，默認(rèn)restrict(嚴(yán)格模式)

drop (database/schema) [if exist] database_name[restrict|cascade];

5.刪庫(kù)示例

hive> drop database mydb1;OKTime taken: 2.146 secondshive> show databases;OKdefaultmydbmydb2mydb3mydb4Time taken: 0.011 seconds, Fetched: 5 row(s)

三、創(chuàng)建表

1.建表語(yǔ)法

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 [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]  [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];

2.建表示例

普通建表hive> create table t_user(    > uid int comment 'id',    > uname string comment '姓名',    > sex boolean comment '性別',    > birth date comment '生日',????>?likes?array<string>?comment?'興趣',    > other map<string,string> comment '其他'    > );OKTime taken: 2.342 seconds
指定數(shù)據(jù)在hdfs文件中的分隔符hive> create table t_user1(    >   uid int comment 'id',    >   uname string comment '姓名',    >   sex boolean comment '性別',    >   birth date comment '生日',    >   likes array<string> comment '興趣',    >   other map<string,string> comment '其他'    > )    > row format    > delimited    >  fields terminated by ','    >  collection items terminated by '-'    >  map keys terminated by ':'    >  lines terminated by '\n';OKTime taken: 5.186 seconds
指定存儲(chǔ)格式hive> create table t_user2(    >   uid int comment 'id',    >   uname string comment '姓名',    >   sex boolean comment '性別',    >   birth date comment '生日',    >   likes array<string> comment '興趣',    >   other map<string,string> comment '其他'????>?)    > stored as orc;OKTime taken: 4.439 seconds
創(chuàng)建外部表hive> create external table t_user3(    >   uid int comment 'id',    >   uname string comment '姓名',    >   sex boolean comment '性別',    >   birth date comment '生日',    >   likes array<string> comment '興趣',    >   other map<string,string> comment '其他'    > );OKTime taken: 2.126 seconds
指定hdfs文件hive> create table t_user4(    >   uid int comment 'id',    >   uname string comment '姓名',    >   sex boolean comment '性別',    >   birth date comment '生日',    >   likes array<string> comment '興趣',    >   other map<string,string> comment '其他'    > )    > location '/user/root/user.txt';OKTime taken: 2.108 seconds
創(chuàng)建分區(qū)表hive> create table t_user5(    >   uid int comment 'id',    >   uname string comment '姓名',    >   birth date comment '生日',    >   likes array<string> comment '興趣',    >   other map<string,string> comment '其他'    > )    > partitioned by (sex string comment '性別',age int comment '年齡');OKTime taken: 2.11 seconds

3.表數(shù)據(jù)存儲(chǔ)格式

4.內(nèi)部表和外部表

5.查看

hive> desc formatted t_user1;OK# col_name              data_type               comment
uid                     int                     iduname                   string                  姓名sex                     boolean                 性別birth                   date                    生日likes                   array<string>           興趣other                   map<string,string>      其他
# Detailed Table InformationDatabase:               mydbOwner:                  rootCreateTime:             Mon Jul 05 21:21:01 CST 2021LastAccessTime:         UNKNOWNProtect Mode:           NoneRetention:              0Location:               hdfs://mycluster/user/hive/warehouse/mydb.db/t_user1Table Type:             MANAGED_TABLETable Parameters:        transient_lastDdlTime   1625491261
# Storage InformationSerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDeInputFormat:            org.apache.hadoop.mapred.TextInputFormatOutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormatCompressed:             NoNum Buckets:            -1Bucket Columns:         []Sort Columns:           []Storage Desc Params:        colelction.delim        -        field.delim             ,        line.delim              \n        mapkey.delim            :        serialization.format    ,Time taken: 0.12 seconds, Fetched: 35 row(s)

6.like和as建表

表1和表2表結(jié)構(gòu)一樣，沒(méi)有數(shù)據(jù)create?table?table_name_1?like?table_name_2;
從表2查詢(xún)出col1、col2列的數(shù)據(jù)集，并建立成一個(gè)名為表1的表create table table_name1 as   select col1,col2 from table_name2

至此，hive基礎(chǔ)操作就完成了，至于查詢(xún)語(yǔ)法和常規(guī)的數(shù)據(jù)庫(kù)操作大同小異，歡迎朋友留言指正。。。